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