Re: BUG #15604: NOT IN condition incorrectly returns False

Hi Andrew,
Thanks a lot for fast and informative response!
You're totally right.
We both feel embarrassed and amused at the same time. This is so nice of
you to educate us.

On Tue, Jan 22, 2019 at 10:57 PM Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:

> >>>>> "PG" == PG Bug reporting form <noreply(at)postgresql(dot)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(at)romanovsky(dot)org
> PG> PostgreSQL version: 10.6
> PG> Operating system: linux Red Hat 4.8.3-9
> PG> Description:
>
> PG> Zhijiang Li <zl256(at)cornell(dot)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)
>