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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2004-07-26 23:32:33 | Re: Unexpected subquery behaviour |
Previous Message | Gaetano Mendola | 2004-07-26 22:06:49 | Re: 7.5 backend crash |