Re: "large" IN/NOT IN subqueries result in query returning wrong data

elein <elein(at)varlena(dot)com> writes:
> In 8.0 we get:

> elein=# select 1 in (NULL, 1, 2);
> ?column?
> ----------
> t
> (1 row)

> elein=# select 3 not in (NULL, 1, 2);
> ?column?
> ----------

> (1 row)

> For consistency, either both should return NULL or
> both return true/false.

The above behavior is correct per spec. Feel free to argue its
consistency with the SQL committee ;-)

Note that the above are not inverses because you changed the lefthand
input. You do get consistent results when you just add or omit NOT:

regression=# select 1 not in (NULL, 1, 2);
?column?
----------
f
(1 row)

regression=# select 3 in (NULL, 1, 2);
?column?
----------

(1 row)

regards, tom lane