On Mon, 26 Jul 2004 16:32:33 -0700 (PDT), Stephan Szabo
<[EMAIL PROTECTED]> 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
[EMAIL PROTECTED]

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

Reply via email to