The following bug has been logged online: Bug reference: 2334 Logged by: Patrick Narkinsky Email address: [EMAIL PROTECTED] 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. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
