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:
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).