Re: "large" IN/NOT IN subqueries result in query returning wrong data - Mailing list pgsql-sql

elein <elein@varlena.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