Skip site navigation (1) Skip section navigation (2)

Re: BUG #5122: Subqueries - inner select statement bug

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Muris Pucic <trax(at)multicom(dot)ba>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5122: Subqueries - inner select statement bug
Date: 2009-10-16 13:49:54
Message-ID: 4AD87A02.2000406@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2009-10-16 13:51:12
Subject: Re: BUG #5122: Subqueries - inner select statement bug
Previous:From: Thom BrownDate: 2009-10-16 13:48:11
Subject: Re: vacuumdb error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group