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

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

From: "Patrick Narkinsky" <patrick(at)narkinsky(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2334: WHERE IN (SUBSELECT) fails when column is null
Date: 2006-03-17 21:07:23
Message-ID: 200603172107.k2HL7N88009095@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      2334
Logged by:          Patrick Narkinsky
Email address:      patrick(at)narkinsky(dot)com
PostgreSQL version: 8.1.3
Operating system:   Mac OS X
Description:        WHERE IN (SUBSELECT) fails when column is null
Details: 

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);

On postgresql, it works as expected when a_id has a non-null value.  I'm not
expert enough on SQL to say which is wrong, but it appears to me that the
SQLite behavior makes a lot more sense.

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2006-03-18 04:52:58
Subject: Re: BUG #2330: ECPGlib: Wrong error code in case of a duplicate key violation
Previous:From: kevin barnesDate: 2006-03-17 20:53:59
Subject: BUG #2333: dropdb ignores the database name argument

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