Re: NOT IN queries

On 1 Apr 2002, Nic Ferrier wrote:

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

Assuming that some of those inserts were supposed to be in t2, you're
misunderstanding how NULLs work. Because there's a NULL in the output
of the subselect, NOT IN is never going to return rows and this is
correct.

The transformations by the spec start out:
RVC NOT IN IPV => NOT (RVC IN IPV) => NOT (RVC =ANY IPV)

The result of RVC =ANY IPV is derived from the application of
= to each row in IPV. If = is true for at least one row RT
of IPV then RVC =ANY IPV is true. If IPV is empty or if =
is false for each row RT of IPV then RVC =ANY IPV is false.
If neither of those cases hold, it's unknown. Since
anything = NULL returns unknown, not false, the last case
is the one that holds. You then NOT the unknown and get
unknown back. Where clauses don't return rows where the
condition is unknown, so you won't get any rows back.