The following seems to be a bug in 7.2 (and in 7.1.2) I'm pretty sure
it worked before, certainly it's something I do a lot (but postgresql
isn't the only database I use).
The bug concerns a NOT IN on a list generated by a select. If you
have two tables thus:
create table t1 (id integer, name varchar(20), t2_id integer);
insert into t1 (id, name, t2_id) values (1, 'nic', 2);
insert into t1 (id, name, t2_id) values (2, 'jim', NULL);
create table t2 (id integer, name varchar(20));
insert into t1 (id, name, t2_id) values (1, 'ferrier');
insert into t1 (id, name, t2_id) values (2, 'broadbent');
And now do this query:
select * from t2 where id not in (select t2_id from t1);
then I get a NULL response (ie: no rows returned).
What I SHOULD get is the row from t2 with id == 2;
Nic Ferrier
