Re: BUG #15604: NOT IN condition incorrectly returns False - Mailing list pgsql-bugs

>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:

 PG> The following bug has been logged on the website:
 PG> Bug reference:      15604
 PG> Logged by:          Sergey Romanovsky
 PG> Email address:      sergey@romanovsky.org
 PG> PostgreSQL version: 10.6
 PG> Operating system:   linux Red Hat 4.8.3-9
 PG> Description:       

 PG> Zhijiang Li <zl256@cornell.edu> and I found the following bug described
 PG> here: https://github.com/romanovsky/postgres/blob/master/README.md
 PG> # Postgres bug: NOT IN condition incorrectly returns False

Not a bug. This is actually how NOT IN is supposed to work, and it has
nothing to do with hash lookups or instance size (the output is the same
whether a hashed or non-hashed plan is used).

Here is why:

select count(*) from postgres_not_in_bug where request_id is null;
 count
-------
     1
(1 row)

The condition  1 NOT IN (2,NULL)  is equivalent to (1=2) OR (1=NULL),
which evaluates to (false) OR (NULL) which in turn evaluates to NULL.
Since this is not TRUE, the WHERE clause does not accept the row.

When you do NOT IN (select col ...) then the null handling is the same;
if there is _any_ null value in the selected data, then the NOT IN will
never return TRUE (only FALSE or NULL according to whether the value is
found or not).

See also https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_NOT_IN

--
Andrew (irc:RhodiumToad)