Re: [HACKERS] Unexpected subquery behaviour
On Mon, 26 Jul 2004 16:32:33 -0700 (PDT), Stephan Szabo <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Unexpected subquery behaviour
On Tue, 27 Jul 2004 01:33:44 +0200, Andreas Joseph Krogh <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Unexpected subquery behaviour
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 <[EMAIL PROTECTED]> 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 | | +-+ pgp69RNqmy7ba.pgp Description: PGP signature
Re: [HACKERS] Unexpected subquery behaviour
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 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. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
