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

BUG #2251: NOT IN clause is not working correctly

From: "Sergei Dubov" <sdubov(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2251: NOT IN clause is not working correctly
Date: 2006-02-10 23:48:25
Message-ID: 20060210234825.9F457F0B04@svr2.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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.

Responses

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2006-02-11 15:13:19
Subject: Re: BUG #2251: NOT IN clause is not working correctly
Previous:From: Tom LaneDate: 2006-02-10 22:35:29
Subject: Re: overlapping rules can let you break referential integrity

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