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

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

The following bug has been logged on the website:

Bug reference: 16945
Logged by: Glenn Widener
Email address: glennwidener@gmail.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

On Mon, Mar 29, 2021 at 10:10 AM Pantelis Theodosiou <ypercube@gmail.com>
wrote:

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

...

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:

...

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)

Even better in my opinion if Hibernate instead "where value in (null)" as a
workaround for several DB's not handling "where value in ()", use

"where false" instead of "where value in ()"
"where true" instead of "where value not in ()"