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

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

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Sergei Dubov <sdubov(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2251: NOT IN clause is not working correctly
Date: 2006-02-11 15:13:19
Message-ID: 20060211070808.V53497@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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.

In response to

pgsql-bugs by date

Next:From: Magnus HaganderDate: 2006-02-11 15:45:27
Subject: Re: BUG #2244: silent installation to set password never expires
Previous:From: Sergei DubovDate: 2006-02-10 23:48:25
Subject: BUG #2251: NOT IN clause is not working correctly

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