Re: Unexpected subquery behaviour

On Tue, 27 Jul 2004 01:33:44 +0200, Andreas Joseph Krogh
<andreak(at)officenet(dot)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(at)gmail(dot)com