The following bug has been logged online:
Bug reference: 2334
Logged by: Patrick Narkinsky
Email address: patrick@narkinsky.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.