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

Re: BUG #2334: WHERE IN (SUBSELECT) fails when column is null

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Patrick Narkinsky <patrick(at)narkinsky(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2334: WHERE IN (SUBSELECT) fails when column is null
Date: 2006-03-19 00:13:49
Message-ID: 20060318161050.V82070@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Fri, 17 Mar 2006, Patrick Narkinsky wrote:

> This may be expected behavior, but it certainly doesn't seem right to me,
> and it works as expected in sqlite.
>
> The database is as follows:
>
> BEGIN TRANSACTION;
> create table a (
> id integer,
> text varchar(20)
> );
> INSERT INTO a VALUES(0,'test');
> INSERT INTO a VALUES(1,'test2');
> create table b (
> id integer,
> a_id integer);
> INSERT INTO b VALUES(0,NULL);
> INSERT INTO b VALUES(1,NULL);
> INSERT INTO b VALUES(2,NULL);
> COMMIT;
>
> The following query returns everything in a in sqlite, but returns nothing
> in postgresql:
>
> select * from a where a.id not in (select a_id from b);

AFAICS, our behavior follows SQL.

a NOT IN b is NOT(a IN b)
IN is defined in terms of = ANY.
a =ANY (b) is basically (by my reading of 8.8 anyway):
 True if a = bi for some bi in b
 False if b is empty or a <> bi for all bi in b
 Unknown otherwise
Since a <> NULL returns unknown, the second one won't come up, so the
whole expression won't ever be true after the negation.  It might be false
or it might be unknown.

In response to

Responses

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2006-03-19 02:10:22
Subject: Re: BUG #2333: dropdb ignores the database name argument
Previous:From: Neil ConwayDate: 2006-03-18 22:11:56
Subject: Re: [PATCHES] Bonjour registration on Intel Macs is broken

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