Re: Strange query problem... - Mailing list pgsql-admin

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@jcvi.org]
> Sent: Wednesday, January 28, 2009 12:31 PM
> To: Scott Whitney; Kevin Grittner; pgsql-admin@postgresql.org
> Subject: RE: [ADMIN] 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@journyx.com]
> Sent: Wednesday, January 28, 2009 1:22 PM
> To: Hoover, Jeffrey; 'Kevin Grittner'; pgsql-admin@postgresql.org
> Subject: RE: [ADMIN] Strange query problem...
>
> So, you're sayin' I ain't crazy? :)
>
> -----Original Message-----
> From: Hoover, Jeffrey [mailto:jhoover@jcvi.org]
> Sent: Wednesday, January 28, 2009 12:18 PM
> To: Kevin Grittner; Scott Whitney; pgsql-admin@postgresql.org
> Subject: RE: [ADMIN] 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@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Kevin Grittner
> Sent: Wednesday, January 28, 2009 1:05 PM
> To: Scott Whitney; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Strange query problem...
>
> >>> "Scott Whitney" <swhitney@journyx.com> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997