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