Обсуждение: Unexpected subquery behaviour
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
On Tue, 27 Jul 2004, 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". Unfortunately, as far as we can tell, the spec allows subselects to contain references to outer columns and that those can be done without explicitly referencing the outer table. As such, the above is effectively equivalent toSELECT * FROM foo WHERE foo.id IN (SELECT foo.id FROM bar) in the case where foo has an id column and bar does not.
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@officenet.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 | | ------------------------+---------------------------------------------+
On Mon, 26 Jul 2004 16:32:33 -0700 (PDT), Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: > On Tue, 27 Jul 2004, 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". The foreign key is not relevant, I just realized. > Unfortunately, as far as we can tell, the spec allows subselects to > contain references to outer columns and that those can be done without > explicitly referencing the outer table. > > As such, the above is effectively equivalent to > SELECT * FROM foo WHERE foo.id IN (SELECT foo.id FROM bar) > in the case where foo has an id column and bar does not. Aha, interesting to know, though it looks somewhat odd. The reason I came up with this is because I was referencing the wrong column, which happened to exist in the outer table, which was producing unexpected results. Thanks Ian Barwick barwick@gmail.com
On Tue, 27 Jul 2004 01:33:44 +0200, Andreas Joseph Krogh <andreak@officenet.no> wrote: > 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 yes, I had that further down in the original example: > > test=> SELECT * FROM foo WHERE id IN (SELECT bar.id FROM bar); > > ERROR: column bar.id does not exist Ian Barwick barwick@gmail.com