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

On Mon, Mar 29, 2021 at 10:10 AM Pantelis Theodosiou <ypercube(at)gmail(dot)com>
wrote:

>
>
> On Mon, Mar 29, 2021 at 8:41 AM PG Bug reporting form <
> noreply(at)postgresql(dot)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 ()"