Re: Unexpected subquery behaviour - Mailing list pgsql-hackers

From Ian Barwick
Subject Re: Unexpected subquery behaviour
Date
Msg-id 1d581afe040726164033ed0448@mail.gmail.com
Whole thread Raw
In response to Re: Unexpected subquery behaviour  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
List pgsql-hackers
On Mon, 26 Jul 2004 16:32:33 -0700 (PDT), Stephan Szabo
<sszabo@megazone.bigpanda.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@gmail.com


pgsql-hackers by date:

Previous
From: Andreas Joseph Krogh
Date:
Subject: Re: Unexpected subquery behaviour
Next
From: Ian Barwick
Date:
Subject: Re: Unexpected subquery behaviour