Re: Strange query problem...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



So, you're sayin' I ain't crazy? :) 

-----Original Message-----
From: Hoover, Jeffrey [mailto:jhoover@xxxxxxxx] 
Sent: Wednesday, January 28, 2009 12:18 PM
To: Kevin Grittner; Scott Whitney; pgsql-admin@xxxxxxxxxxxxxx
Subject: RE:  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@xxxxxxxxxxxxxx
[mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Kevin Grittner
Sent: Wednesday, January 28, 2009 1:05 PM
To: Scott Whitney; pgsql-admin@xxxxxxxxxxxxxx
Subject: Re:  Strange query problem...

>>> "Scott Whitney" <swhitney@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


-- 
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux