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@solis.coop.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
