Skip site navigation (1) Skip section navigation (2)

Re: Unexpected subquery behaviour

From: Ian Barwick <barwick(at)gmail(dot)com>
To: Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Unexpected subquery behaviour
Date: 2004-07-26 23:42:43
Message-ID: 1d581afe040726164236a380ff@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2004-07-27 00:37:13
Subject: Re: 7.5 backend crash
Previous:From: Ian BarwickDate: 2004-07-26 23:40:32
Subject: Re: Unexpected subquery behaviour

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group