Thread: BUG #16945: where value in (null) set results inconsistent
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 ")"
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 ()"