Skip site navigation (1) Skip section navigation (2)

Re: Re : BUG #2251: NOT IN clause is not working correctly

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
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:45:22
Message-ID: 20060226073725.V6359@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Sun, 26 Feb 2006, Dhanaraj wrote:

> I have two tables, let's say A and B.
>
> B is a child of a in one to many relationship. A contains records that are
> not referenced by B.
>
> 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.
>
> Thanks a lot for your consideration of this bug.

This may not be a bug if t2.A_id contains NULLs because not in and except
handle them differently and return different results by spec.

Specifically, something like
 1 NOT IN (values (NULL)) is unknown
while
 select 1 except select NULL returns a row with 1.

The first is because IN is based on equality, and 1 = NULL is unknown.
The second is because it uses distinctness (or more precisely duplicate
which is itself defined in terms of distinctness), and 1 IS DISTINCT FROM
NULL is true.

If you're getting platform dependant results on the same (non-textual)
data, it would be helpful to make a complete script that others can run.

In response to

pgsql-bugs by date

Next:From: Michael FuhrDate: 2006-02-26 15:58:36
Subject: Re: Re : BUG #2251: NOT IN clause is not working correctly
Previous:From: DhanarajDate: 2006-02-26 14:08:32
Subject: Re : BUG #2251: NOT IN clause is not working correctly

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group