Re: Unexpected subquery behaviour

On Mon, 26 Jul 2004 16:32:33 -0700 (PDT), Stephan Szabo
<sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> 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".

The foreign key is not relevant, I just realized.

> 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.

Aha, interesting to know, though it looks somewhat odd. The reason
I came up with this is because I was referencing the wrong column, which
happened to exist in the outer table, which was producing unexpected results.

Thanks

Ian Barwick
barwick(at)gmail(dot)com