Re: BUG #16945: where value in (null) set results inconsistent

On Mon, Mar 29, 2021 at 8:41 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 16945
> Logged by: Glenn Widener
> Email address: glennwidener(at)gmail(dot)com
> PostgreSQL version: 11.5
> Operating system: Windows 10
> Description:
>
> Pursuant to a fix in Hibernate, that uses "where value in (null)" as a
> workaround for several DB's not handling "where value in ()":
> https://hibernate.atlassian.net/browse/HHH-8091
>
> Consider this inconsistency:
> test-db=> select id from project_type_variants where id in (0);
> id
> ----
> (0 rows)
>
> test-db=> select id from project_type_variants where id in (null);
> id
> ----
> (0 rows)
>
> test-db=> select id from project_type_variants where id not in (0);
> id
> ----
> 1
> 2
> 3
> (3 rows)
>
> test-db=> select id from project_type_variants where id not in (null);
> id
> ----
> (0 rows)
>
> Needs to work consistently, because (null) is a required workaround for:
>
> test-db=> select id from project_type_variants where id in ();
> ERROR: syntax error at or near ")"
>

There is no inconsistency, this is how comparison to null works

where id in (null)

is NOT equivalent to (if that was allowed) checking if id is in with the
empty set:

where id in ()

The expression `id in (null)` is equivalent to `id = null` which is always
null (or more accurately Uknown) and where unknown will have the same
result as where false.

Workarounds:

where id in (select null::int where false)
where id not in (select null::int where false)

or avoiding the cast:

where id in (select id from project_type_variants where false)
where id not in (select id from project_type_variants where false)

Regards
Pantelis Theodosiou