Thread: 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@gmail.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.

On Fri, 10 Feb 2006, Sergei Dubov 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.

This may not be a bug, since these two queries will do different things if
there are any nulls in B.A_id according to spec AFAIK. The short form is
that rvc NOT IN (<subselect returning nulls>) will not return true.