From: Scott Whitney <swhitney@journyx.com>
To: 'Hoover, Jeffrey' <jhoover@jcvi.org>
To: 'Kevin Grittner' <Kevin.Grittner@wicourts.gov>
To: pgsql-admin@postgresql.org
Subject: Re: Strange query problem...
Date: Wed, 28 Jan 2009 12:38:34 -0600
Message-ID: <20090128183715.A03677E4E79@mail.int.journyx.com> (raw)
In-Reply-To: <3EC2264E4927524DB3F1C0425831CCA54748B4@EXCHANGE.TIGR.ORG>
Wow. This just boggles my mind, but there it is. Here's Oracle:
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: swhitney@journyx.com, jhoover@jcvi.org, Kevin.Grittner@wicourts.gov
Subject: Re: Strange query problem...
In-Reply-To: <20090128183715.A03677E4E79@mail.int.journyx.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox