Re: Unexpected subquery behaviour

On Tuesday 27 July 2004 01:15, Ian Barwick wrote:
> Apologies if this has been covered previously.
>
> Given a statement like this:
>   SELECT * FROM foo WHERE id IN (SELECT id FROM bar)
> I would expect it to fail if "bar" does not have a column "id". The
> test case below (tested in 7.4.3 and 7.4.1) shows this statement
> will however appear succeed, but produce a cartesian join (?) if "bar"
> contains a foreign key referencing "foo.id".
[snip]
> test=> SELECT * FROM foo WHERE id IN (SELECT id FROM bar);
>  id
> ----
>   1
>   2
> (2 rows)

This, however, does not work:
andreak=# SELECT * FROM foo WHERE id IN (SELECT b.id FROM bar b);
ERROR:  column b.id does not exist

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS            | - a tool should do one job, and do it well. |
Hoffsveien 17           |                                             |
PO. Box 425 Skøyen      |                                             |
0213 Oslo               |                                             |
NORWAY                  |                                             |
Phone : +47 22 13 01 00 |                                             |
Direct: +47 22 13 10 03 |                                             |
Mobile: +47 909  56 963 |                                             |
------------------------+---------------------------------------------+