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
