Re: Strange query problem...

Adding in "where id_time_rec is not null" does solve the problem. SQL server
appears to be the only one that natively says "yeah, he doesn't care about
those."

I'd argue, now that I'm understanding it, that the query is doing what I
asked. Just not what I wanted. :)

-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Wednesday, January 28, 2009 12:47 PM
To: Scott Whitney
Cc: 'Hoover, Jeffrey'; 'Kevin Grittner'; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Strange query problem...

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