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