Re: Unexpected subquery behaviour

On Tue, 27 Jul 2004 01:33:44 +0200, Andreas Joseph Krogh
<andreak@officenet.no> wrote:
> On Tuesday 27 July 2004 01:15, 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".
> [snip]
> > test=> SELECT * FROM foo WHERE id IN (SELECT id FROM bar);
> >  id
> > ----
> >   1
> >   2
> > (2 rows)
> 
> This, however, does not work:
> andreak=# SELECT * FROM foo WHERE id IN (SELECT b.id FROM bar b);
> ERROR:  column b.id does not exist

yes, I had that further down in the original example:

> > test=> SELECT * FROM foo WHERE id IN (SELECT bar.id FROM bar);
> > ERROR:  column bar.id does not exist

Ian Barwick
barwick@gmail.com