From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Dhanaraj <Dhanaraj(dot)M(at)Sun(dot)COM> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Re : BUG #2251: NOT IN clause is not working correctly |
Date: | 2006-02-26 15:58:36 |
Message-ID: | 20060226155836.GA85643@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Sun, Feb 26, 2006 at 07:38:32PM +0530, Dhanaraj wrote:
> I am running a query:
>
> select * from A t1 where t1.id not in (select t2.A_id from B t2);
>
> It returns 0 rows.
>
> Now I run
> (select t1.id from A t1) except (select t2.A_id from B t2);
>
> And now Postgres correctly returns records from A that are not referenced by
> B.
Table B probably has some NULL values for A_id, so the first query's
NOT IN expression returns NULL instead of true because it's
indeterminate whether t1.id is in the set (NULL means unknown).
Here's an example:
CREATE TABLE a (id integer PRIMARY KEY);
CREATE TABLE b (a_id integer REFERENCES a);
INSERT INTO a VALUES (1);
INSERT INTO a VALUES (2);
INSERT INTO b VALUES (1);
INSERT INTO b VALUES (NULL);
SELECT * FROM a WHERE id NOT IN (SELECT a_id FROM B);
id
----
(0 rows)
SELECT * FROM a WHERE id NOT IN (SELECT a_id FROM b WHERE a_id IS NOT NULL);
id
----
2
(1 row)
According to past discussion this behavior is per the SQL specification.
Search the list archives for more information.
--
Michael Fuhr
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2006-02-26 16:07:13 | Re: Re : BUG #2251: NOT IN clause is not working correctly |
Previous Message | Stephan Szabo | 2006-02-26 15:45:22 | Re: Re : BUG #2251: NOT IN clause is not working correctly |