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

On Tue, Dec 27, 2005 at 07:25:40PM -0500, Tom Lane wrote:
> 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 ;-)

Oh, no! Not the 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:
Yes, you are right. I skipped the permutations to get down to the point.

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

Thanks for your clarification.  

~elein
elein@varlena.com