On Wed, 2009-01-28 at 12:38 -0600, Scott Whitney wrote: > Wow. This just boggles my mind, but there it is. Here's Oracle: > This looks like a NULL vs '' issue. Am I wrong? Joshua D. Drake > 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 > > > -- PostgreSQL - XMPP: jdrake@xxxxxxxxxxxxxxxxxxxxx Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
