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

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.