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 [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org