Thread: BUG #4250: subquery in query

The following bug has been logged online:

Bug reference:      4250
Logged by:          Wojciech Tylek
Email address:      wat@wat.net.pl
PostgreSQL version: 8.1,8.2,8.3
Operating system:   Linux
Description:        subquery in query
Details:

There are two example tables:

create table a (
  a_id serial primary key,
  sth varchar
  -- anything else
);

create table b (
  b_id serial primary key,
  a_id int references a(a_id),
  sth int
  --anything else
);

The question is:
Why the query like:

select * from b where a_id in (select a_id from a where b_id in (10,20));

does not return error?
The table "a" does not have b_id!
And for example a query (which is a subquery in above): select a_id from a
where b_id in (10,20);
returns ERROR:  column "b_id" does not exist.

>>> "Wojciech Tylek" <wat@wat.net.pl> wrote:

> Why the query like:
>
> select * from b where a_id in (select a_id from a where b_id in
(10,20));
>
> does not return error?
> The table "a" does not have b_id!

This is a correlated subquery.  A previous list post on the topic:

http://archives.postgresql.org/pgsql-general/2007-06/msg01659.php

I hope this helps.

-Kevin