Re: BUG #19793: Range containment with empty/unbounded constant range returns true/false for NULL element

Hi Tom,

A gentle follow-up on this report and the patches I sent earlier in
this thread.

For a constant range that is empty or unbounded on both sides,
find_simplified_clause() (from your commit 075df6b208) folds
"elem <@ range" to constant true/false even when the element is NULL,
where the strict operators should return NULL. The patches (master and
REL_17/18) fold only when the element is provably non-null, and
include regression tests.

I'd appreciate any feedback, in particular on whether I should extend
expr_is_nonnullable() to recognize CURRENT_DATE and the other
date/time SQLValueFunctions as never NULL.

Thanks,
Venu Kummari

On Sat, Oct 24, 2026 at 12:27 AM Venu K <venu2805k(at)gmail(dot)com> wrote:
>
> Attached is a patch for master that only reduces the expression to a
> constant when expr_is_nonnullable() proves the element non-null, plus
> regression tests. make check passes.
>
> The second patch is for REL_18_STABLE and REL_17_STABLE, where
> expr_is_nonnullable() is static and Var-only; it only treats a
> non-null Const element as known non-null. make check passes on both.
>
> One side effect: "current_date <@ daterange 'empty'" and
> "current_date <@ daterange(NULL, NULL)" are no longer folded to
> false/true, because expr_is_nonnullable() doesn't know CURRENT_DATE is
> never NULL. Teaching it about the date/time SQLValueFunctions would
> keep those folded, if that seems worthwhile.
>
> On Sat, Oct 24, 2026 at 12:24 AM PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference: 19793
>> Logged by: Venu Kummari
>> Email address: venu2805k(at)gmail(dot)com
>> PostgreSQL version: 18.6
>> Operating system: Ubuntu 24.04, x86_64
>> Description:
>>
>> Since PostgreSQL 17, "elem <@ range" and "range @> elem" return true or
>> false instead of NULL when the element is NULL and the range is a
>> constant that is empty or unbounded on both sides. This gives wrong
>> query results, including in WHERE clauses:
>>
>> CREATE TEMP TABLE bookings(id int, booked_on date);
>> INSERT INTO bookings VALUES (1, '2026-01-05'), (2, NULL);
>> SELECT id FROM bookings WHERE booked_on <@ daterange(NULL, NULL);
>>
>> returns ids 1 and 2; only 1 is expected (PostgreSQL 16 returns only 1).
>> The same happens with PREPARE ... WHERE booked_on <@ daterange($1, $2)
>> executed with NULL, NULL.
>>
>> More directly:
>>
>> SELECT c, '(,)'::int4range @> c, NOT ('empty'::int4range @> c)
>> FROM (VALUES (NULL::int), (1)) v(c);
>>
>> returns t and t for the NULL row; both should be NULL, since
>> range_contains_elem and elem_contained_by_range are strict. With a
>> sub-select around the range, which blocks the planner simplification,
>> the result is NULL. EXPLAIN VERBOSE shows the expression replaced by
>> the constant true.
>>
>> Reproduced on 17.11, 18.6 and master (126976a). Not reproducible on 16.
>>
>> Cause: find_simplified_clause() in src/backend/utils/adt/rangetypes.c,
>> added in commit 075df6b20, returns makeBoolConst(false) for an empty
>> constant range and makeBoolConst(true) for an unbounded one without
>> considering that the element may be NULL.
>>
>> I have a patch for master and a back-branch version for 17/18, with
>> regression tests; I will send them as a reply to this report.
>>
>> Found with a metamorphic (equivalent-query) fuzzer.
>>
>>