Re: BUG #2961: NULL values in subselects force NOT IN to false

On Fri, 2 Feb 2007, Aaron Logue wrote:

> The following bug has been logged online:
>
> Bug reference: 2961
> Logged by: Aaron Logue
> Email address: gyro(at)cryogenius(dot)com
> PostgreSQL version: 8.2.1
> Operating system: Linux (various flavors)
> Description: NULL values in subselects force NOT IN to false
> Details:
>
> SELECT X FROM (SELECT 42 AS X) AS FOO WHERE X NOT IN (7,NULL);
>
> returns 0 rows. Shouldn't "X NOT IN (7,NULL)" be
> true if X is neither 7 nor NULL? Removing the NULL causes the row to be
> returned.

NOT IN with NULLs is defined by spec in a way that most people do not
expect if they aren't thinking about three valued logic.

x NOT IN RVC is effectively NOT(x = ANY RVC).
x = ANY RVC is defined to be true if x = RVCi is true for some RVCi in
RVC.
x = ANY RVC is defined to be false if x = RVCi is false for all RVCi in
RVC.
x = ANY RVC is defined to be unknown otherwise.

x = NULL is defined as unknown, so what you end up with is
x = 7, false
x = NULL, unknown
so, x IN (7, NULL), unknown
so, NOT (x IN (7, NULL)), unknown.