pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16945: where value in (null) set results inconsistent
3+ messages / 2 participants
[nested] [flat]
* BUG #16945: where value in (null) set results inconsistent
@ 2021-03-28 21:44 PG Bug reporting form <noreply@postgresql.org>
2021-03-29 09:10 ` Re: BUG #16945: where value in (null) set results inconsistent Pantelis Theodosiou <ypercube@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2021-03-28 21:44 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: glennwidener@gmail.com
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 ")"
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16945: where value in (null) set results inconsistent
2021-03-28 21:44 BUG #16945: where value in (null) set results inconsistent PG Bug reporting form <noreply@postgresql.org>
@ 2021-03-29 09:10 ` Pantelis Theodosiou <ypercube@gmail.com>
2021-03-29 09:17 ` Re: BUG #16945: where value in (null) set results inconsistent Pantelis Theodosiou <ypercube@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Pantelis Theodosiou @ 2021-03-29 09:10 UTC (permalink / raw)
To: glennwidener@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16945: where value in (null) set results inconsistent
2021-03-28 21:44 BUG #16945: where value in (null) set results inconsistent PG Bug reporting form <noreply@postgresql.org>
2021-03-29 09:10 ` Re: BUG #16945: where value in (null) set results inconsistent Pantelis Theodosiou <ypercube@gmail.com>
@ 2021-03-29 09:17 ` Pantelis Theodosiou <ypercube@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Pantelis Theodosiou @ 2021-03-29 09:17 UTC (permalink / raw)
To: glennwidener@gmail.com; pgsql-bugs@lists.postgresql.org
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 ()"
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-03-29 09:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-28 21:44 BUG #16945: where value in (null) set results inconsistent PG Bug reporting form <noreply@postgresql.org>
2021-03-29 09:10 ` Pantelis Theodosiou <ypercube@gmail.com>
2021-03-29 09:17 ` Pantelis Theodosiou <ypercube@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox