Strange behavior on non-existent field in subselect?
We're a little puzzled by this (apparently) strange behavior, and would
be curious to know what you folks make of it. Thanks.
Ken
CREATE TABLE foo (
foo_field integer );
CREATE TABLE par(
par_field integer );
SELECT VERSION();
SELECT foo_field FROM par;
SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par);
INSERT INTO foo VALUES (1);
SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par);
INSERT INTO par VALUES (1);
SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par);
/* One row for every foo record, provided at least one record in par */
Which (for us) yields the following output:
Chasers=> \i strangefield.sql
CREATE TABLE
CREATE TABLE
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
3.4.4 20050721 (Red Hat 3.4.4-2)
(1 row)
psql:strangefield.sql:11: ERROR: column "foo_field" does not exist
foo_field
-----------
(0 rows)
INSERT 0 1
foo_field
-----------
(0 rows)
INSERT 0 1
foo_field
-----------
1
(1 row)
On �ri, 2006-10-17 at 15:58 -0700, Ken Tanzer wrote:
We're a little puzzled by this (apparently) strange behavior, and would
be curious to know what you folks make of it. Thanks.
not sure exactly what you are referring to, but:
(rearranged quotes to group output with SQL)
SELECT foo_field FROM par;
psql:strangefield.sql:11: ERROR: column "foo_field" does not exist
hopefully, no mystery here.
SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par);
if par is empty, then this SELECT will return 0 rows,
otherwise it is equivalent to SELECT foo_field from foo
foo_field
-----------
(0 rows)
foo is empty, so no rows returned
INSERT INTO foo VALUES (1);
SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par);
foo_field
-----------
(0 rows)
par is empty, so the IN operator fails for the foo row
INSERT INTO par VALUES (1);
SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par);
foo_field
-----------
1
(1 row)
when par contains at least one row, the subselect will
return foo_field once per row of par.
the IN operator will ignore duplicates, so the result
is the same for any number of rows in par greater than 0
gnari
Thanks for the response Ragnar. I would have expected this query to
fail, since the sub-query doesn't work by itself:
SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par);
But it obviously doesn't. So does that subselect implicitly read as:
IN (SELECT foo_field FROM par,foo);
Thanks for your help!
Ken