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
