Re: Strange query problem...

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(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