Re: Unexpected subquery behaviour

On Tue, 27 Jul 2004, 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".

Unfortunately, as far as we can tell, the spec allows subselects to
contain references to outer columns and that those can be done without
explicitly referencing the outer table.

As such, the above is effectively equivalent to
SELECT * FROM foo WHERE foo.id IN (SELECT foo.id FROM bar)
in the case where foo has an id column and bar does not.