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

Поиск
Список
Период
Сортировка
Искать

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

От:
Venu K <venu2805k@gmail.com>
Дата:
Вложения:
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@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      19793
Logged by:          Venu Kummari
Email address:      venu2805k@gmail.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.


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

От:
Venu K <venu2805k@gmail.com>
Дата:
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  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  wrote:
>>
>> The following bug has been logged on the website:
>>
>> Bug reference:      19793
>> Logged by:          Venu Kummari
>> Email address:      venu2805k@gmail.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.
>>
>>


FAQ