Обсуждение: Re : BUG #2251: NOT IN clause is not working correctly

The following bug has been logged online:

Bug reference:      2251
Logged by:          Sergei Dubov
Email address:      sdubov ( at ) gmail ( dot ) com
PostgreSQL version: 8.1.2
Operating system:   Windows XP
Description:        NOT IN clause is not working correctly
Details:

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.

Serge.

Hi

I tested this exampe. It works fine in Solaris platform (postgres 8.1.2
released recently by sun)
Try the same example in some other version. If this is true, the changes
need to be done for a particular version of postgres..

Have a nice time
Dhanaraj

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.

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

On Sun, Feb 26, 2006 at 07:38:32PM +0530, Dhanaraj wrote:
> I tested this exampe. It works fine in Solaris platform (postgres 8.1.2
> released recently by sun)
> Try the same example in some other version. If this is true, the changes
> need to be done for a particular version of postgres..

Sorry, I overlooked that this message was a reply because the
original wasn't quoted.  The behavior is data-dependent -- if you
add some NULLs as in the example I just sent then you should be
able to reproduce it on any platform in any version of PostgreSQL
(at least any modern version).  According to past discussion it's
per the spec.

--
Michael Fuhr