Обсуждение: BUG #5122: Subqueries - inner select statement bug

The following bug has been logged online:

Bug reference:      5122
Logged by:          Muris Pucic
Email address:      trax@multicom.ba
PostgreSQL version: 8.2
Operating system:   Vista
Description:        Subqueries - inner select statement bug
Details:

Hi,

If we have two simple tables:

TABLE1
------
id
first_name

TABLE2
------
id
last_name

##############
EXAMPLE QUERY:
##############

-- The query below works, even though there is no column "first_name" in
TABLE2. This should return an error but it does not, it returns all rows
from TABLE1. This query should not evaluate correctly even when aliases are
not used because it can be misleading.

SELECT * FROM TABLE1 WHERE first_name IN (SELECT first_name FROM TABLE2)


-- The query below works as expected

SELECT * FROM TABLE1 WHERE first_name IN (SELECT last_name FROM TABLE2)


Regards,
Muris

www.elektronika.ba

Muris Pucic wrote:
> -- The query below works, even though there is no column "first_name" in
> TABLE2. This should return an error but it does not, it returns all rows
> from TABLE1. This query should not evaluate correctly even when aliases are
> not used because it can be misleading.
>
> SELECT * FROM TABLE1 WHERE first_name IN (SELECT first_name FROM TABLE2)

Nope, it's working as expected. The first_name in the subquery is
referring to the first_name column in the outer query. While it looks
strange in a context like that, it's not an error. You wouldn't be able
to write correlated subqueries otherwise, e.g:

SELECT * FROM TABLE1 WHERE EXISTS (SELECT 1 FROM TABLE2 WHERE first_name
= last_name)

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

"Muris Pucic" <trax@multicom.ba> writes:
> -- The query below works, even though there is no column "first_name" in
> TABLE2. This should return an error but it does not, it returns all rows
> from TABLE1. This query should not evaluate correctly even when aliases are
> not used because it can be misleading.

> SELECT * FROM TABLE1 WHERE first_name IN (SELECT first_name FROM TABLE2)

This is not a bug, it's a perfectly legal outer reference.  It is
required to work that way by the SQL standard.  If you think it's
a bad design, take it up with the ISO standards committee.

            regards, tom lane

Muris Pucic wrote:

> -- The query below works, even though there is no column "first_name" in
> TABLE2. This should return an error but it does not, it returns all rows
> from TABLE1. This query should not evaluate correctly even when aliases are
> not used because it can be misleading.
>
> SELECT * FROM TABLE1 WHERE first_name IN (SELECT first_name FROM TABLE2)

This is per spec -- first_name inside the subselect refers to the outer
TABLE1.  Misleading, yes.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.