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(at)officenet(dot)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 | |
------------------------+---------------------------------------------+