Re: [GENERAL] Strange behavior on non-existent field in subselect?

[Ken Tanzer]

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




begin:vcard
fn:Kenneth Tanzer
n:Tanzer;Kenneth
org:Downtown Emergency Service Center;Information Services
adr:;;515 Third Avenue;Seattle;WA;98104;USA
email;internet:[EMAIL PROTECTED]
title:Director of Information Services
tel;work:(206) 464-1570 x 3061
tel;fax:(206) 624-4196
x-mozilla-html:TRUE
url:http://www.desc.org
version:2.1
end:vcard


---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Strange behavior on non-existent field in subselect?

[Ken Tanzer]

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)


begin:vcard
fn:Kenneth Tanzer
n:Tanzer;Kenneth
org:Downtown Emergency Service Center;Information Services
adr:;;515 Third Avenue;Seattle;WA;98104;USA
email;internet:[EMAIL PROTECTED]
title:Director of Information Services
tel;work:(206) 464-1570 x 3061
tel;fax:(206) 624-4196
x-mozilla-html:TRUE
url:http://www.desc.org
version:2.1
end:vcard


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Strange behavior on non-existent field in subselect?

[Ragnar]

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



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match