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@gmail.com
