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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-hackers by date

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