BUG #5122: Subqueries - inner select statement bug

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.