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

Re: Unexpected subquery behaviour

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

-- 
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS            | - a tool should do one job, and do it well. |
Hoffsveien 17           |                                             |
PO. Box 425 Skøyen      |                                             |
0213 Oslo               |                                             |
NORWAY                  |                                             |
Phone : +47 22 13 01 00 |                                             |
Direct: +47 22 13 10 03 |                                             |
Mobile: +47 909  56 963 |                                             |
------------------------+---------------------------------------------+

In response to

Responses

pgsql-hackers by date

Next:From: Ian BarwickDate: 2004-07-26 23:40:32
Subject: Re: Unexpected subquery behaviour
Previous:From: Gavin SherryDate: 2004-07-26 23:33:17
Subject: Re: WARNING: buffer refcount leak

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