Re: BUG #2178: NOT IN command don't work

On Tue, 17 Jan 2006, Daniel Afonso Heisler wrote:

>
> The following bug has been logged online:
>
> Bug reference: 2178
> Logged by: Daniel Afonso Heisler
> Email address: daniel(at)solis(dot)coop(dot)br
> PostgreSQL version: 8.1.X
> Operating system: Linux
> Description: NOT IN command don't work
> Details:
>
> When i run the following query, postgreSQL return TRUE.
> # SELECT true WHERE 1 NOT IN (2,3);
>
> But, when i run the next query, it don't return TRUE
> # SELECT true WHERE 1 NOT IN (2,NULL,3);

This is not a bug, and the above is correct by spec.

select 1 in (NULL,2,3) is null;
- t
select 1 not in (NULL,2,3) is null
- t

IIRC, the short form is:
a NOT IN b => NOT (a IN b) => NOT (a = ANY b)

a = ANY b returns true if a=b returns true for any value in b
a = ANY b returns false if a=b returns false for every value in b
a = ANY b returns NULL otherwise

1 = 2 returns false
1 = NULL returns NULL
1 = 3 returns false

1 IN (2, NULL, 3) = NULL
NOT (1 IN (2,NULL,3)) = NULL
1 NOT IN (2,NULL,3) = NULL