Strange behavior on non-existent field in subselect?

From: Ken Tanzer <ktanzer(at)desc(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Strange behavior on non-existent field in subselect?
Date: 2006-10-17 22:58:58
Message-ID: 45356032.602@desc.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Attachment Content-Type Size
ktanzer.vcf text/x-vcard 342 bytes

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ragnar 2006-10-17 23:56:36 Re: Strange behavior on non-existent field in subselect?
Previous Message Alexander Staubo 2006-10-17 22:33:00 Re: not so sequential sequences