Wow. This just boggles my mind, but there it is. Here's Oracle: SQL> select i from a1; I -------------------- one two three four five SQL> select i from a2; I -------------------- two four SQL> select i from a1 where i not in (select i from a2); no rows selected Or, if you want the exact test: SQL> select * from a1 where i not in (select i from a2); no rows selected SQL> select * from a1 where i not in (select coalesce(i,'') 2 from a2); no rows selected -----Original Message----- From: Hoover, Jeffrey [mailto:jhoover@xxxxxxxx] Sent: Wednesday, January 28, 2009 12:31 PM To: Scott Whitney; Kevin Grittner; pgsql-admin@xxxxxxxxxxxxxx Subject: RE: Strange query problem... How do other databases handle this? I tried it in SQLite and I get different behavior (see below). Can someone try it in Oracle? In MySQL? In Sybase? If postgres is alone in this interpretation would the community consider revising the postgres interpretation? sqlite> select * from a1; one three five two four sqlite> select * from a2; 2|two 4|four 0| sqlite> select * from a1 where i not in (select i from a2); one three five sqlite> -----Original Message----- From: Scott Whitney [mailto:swhitney@xxxxxxxxxxx] Sent: Wednesday, January 28, 2009 1:22 PM To: Hoover, Jeffrey; 'Kevin Grittner'; pgsql-admin@xxxxxxxxxxxxxx Subject: RE: Strange query problem... So, you're sayin' I ain't crazy? :) -----Original Message----- From: Hoover, Jeffrey [mailto:jhoover@xxxxxxxx] Sent: Wednesday, January 28, 2009 12:18 PM To: Kevin Grittner; Scott Whitney; pgsql-admin@xxxxxxxxxxxxxx Subject: RE: Strange query problem... Wow! I would never have expected that behavior, but heres the proof: cameradb_dev=# select * from a1; i ------- one three five two four (5 rows) cameradb_dev=# select * from a2; j | i ---+------ 0 | 2 | two 4 | four (3 rows) cameradb_dev=# select * from a1 where i not in (select i from a2); i --- (0 rows) cameradb_dev=# select * from a1 where i not in (select coalesce(i,'') from a2); i ------- one three five (3 rows) cameradb_dev=# -----Original Message----- From: pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Kevin Grittner Sent: Wednesday, January 28, 2009 1:05 PM To: Scott Whitney; pgsql-admin@xxxxxxxxxxxxxx Subject: Re: Strange query problem... >>> "Scott Whitney" <swhitney@xxxxxxxxxxx> wrote: > Um. How is this possible? > mydb=# select * from time_recs where id_time_rec not in (select > id_time_rec from punch_time_recs); > (0 rows) > Table "public.punch_time_recs" > Column | Type | Modifiers > -------------------+------------------------+--------------------------- ---- > id_time_rec | character varying(38) | The column in punch_time_recs is null capable. Try using NOT EXISTS. The SQL spec requires the NOT IN to be the equivalent of a "not equals" test for all entries, and you can't say that any given value is not equal to NULL, since NULL can mean that there is a value but you don't know it. The semantics of NOT EXISTS are subtly different here -- it means there aren't any rows known to have the value. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
