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

Tue, 27 Jul 2004 01:15:11 +0200
Ian Barwick <barwick@gmail.com>
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.


Mon, 26 Jul 2004 16:32:33 -0700 (PDT)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: 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 <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 |                                             |
------------------------+---------------------------------------------+
Tue, 27 Jul 2004 01:33:44 +0200
Andreas Joseph Krogh <andreak@officenet.no>
Re: Unexpected subquery behaviour
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

Tue, 27 Jul 2004 01:40:32 +0200
Ian Barwick <barwick@gmail.com>
Re: Unexpected subquery behaviour
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

Tue, 27 Jul 2004 01:42:43 +0200
Ian Barwick <barwick@gmail.com>
Re: Unexpected subquery behaviour