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

Unexpected subquery behaviour

From: Ian Barwick <barwick(at)gmail(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Unexpected subquery behaviour
Date: 2004-07-26 23:15:11
Message-ID: 1d581afe0407261615d6e9cbf@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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".

test=> SELECT version();
                                       version
-------------------------------------------------------------------------------------
 PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.3.3 (SuSE Linux)
(1 row)

test=> CREATE TABLE foo (id INT PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
test=> CREATE TABLE bar (bar_id INT, foo_id INT REFERENCES foo(id));
CREATE TABLE
test=> INSERT into foo values(1);
INSERT 7493530 1
test=> INSERT into foo values(2);
INSERT 7493531 1
test=> INSERT into bar values(2,1);
INSERT 7493532 1
test=> SELECT * FROM foo WHERE id IN (SELECT id FROM bar);
 id
----
  1
  2
(2 rows)
test=> EXPLAIN SELECT * FROM foo WHERE id IN (SELECT id FROM bar);
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..2.04 rows=1 width=4)
   Filter: (subplan)
   SubPlan
     ->  Seq Scan on bar  (cost=0.00..1.01 rows=1 width=0)
(4 rows)
test=> SELECT id FROM bar;
ERROR:  column "id" does not exist
test=> SELECT * FROM foo WHERE id IN (SELECT bar.id FROM bar);
ERROR:  column bar.id does not exist
test=> ALTER TABLE bar RENAME foo_id TO id;
ALTER TABLE
test=> SELECT * FROM foo WHERE id IN (SELECT id FROM bar);
 id
----
  1
(1 row)

Is this known behaviour, and is there a rationale behind it?

Ian Barwick
barwick(at)gmail(dot)com

Responses

pgsql-hackers by date

Next:From: Stephan SzaboDate: 2004-07-26 23:32:33
Subject: Re: Unexpected subquery behaviour
Previous:From: Gaetano MendolaDate: 2004-07-26 22:06:49
Subject: Re: 7.5 backend crash

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