pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19793: Range containment with empty/unbounded constant range returns true/false for NULL element
3+ messages / 2 participants
[nested] [flat]
* BUG #19793: Range containment with empty/unbounded constant range returns true/false for NULL element
@ 2026-10-23 18:53 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2026-10-23 18:53 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: venu2805k@gmail.com
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19793: Range containment with empty/unbounded constant range returns true/false for NULL element
@ 2026-10-23 18:57 Venu K <venu2805k@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Venu K @ 2026-10-23 18:57 UTC (permalink / raw)
To: venu2805k@gmail.com; pgsql-bugs@lists.postgresql.org
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.
>
>
>
Attachments: download all (2 files, .tar.gz)
v1-0001-range-containment-null-REL_17_18.patch (application/octet-stream, 5.9K) download
From d360c08862891e6fbcb1634be911366ea21590df Mon Sep 17 00:00:00 2001
From: Venu Kummari <venu2805k@gmail.com>
Date: Fri, 23 Oct 2026 18:39:45 +0000
Subject: [PATCH] Don't reduce range containment with a nullable element to a
constant
The planner support functions for elem <@ range and range @> elem
replace the call with a constant when the range is a constant that is
empty (false) or unbounded on both sides (true). But both operators are
strict, so a NULL element must yield NULL. The constant is wrong
wherever NULL and false are distinguished, e.g. in the select list,
under NOT, or in IS NULL tests:
SELECT c, '(,)'::int4range @> c, NOT ('empty'::int4range @> c)
FROM (VALUES (NULL::int), (1)) v(c);
returned true and true for the NULL row instead of NULL and NULL.
Only reduce to a constant when the element is known not to be NULL.
expr_is_nonnullable() is not available to callers outside the planner
on this branch, so only a non-null Const element is treated as known
non-null here.
Bug introduced by 075df6b20. Back-patch to v17.
---
src/backend/utils/adt/rangetypes.c | 19 ++++++++------
src/test/regress/expected/rangetypes.out | 33 +++++++++++++++++++-----
src/test/regress/sql/rangetypes.sql | 10 +++++++
3 files changed, 48 insertions(+), 14 deletions(-)
diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index 66cc0ac..cd73f3b 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -2867,15 +2867,18 @@ find_simplified_clause(PlannerInfo *root, Expr *rangeExpr, Expr *elemExpr)
range_deserialize(rangetypcache, range, &lower, &upper, &empty);
- if (empty)
- {
- /* if the range is empty, then there can be no matches */
- return makeBoolConst(false, false);
- }
- else if (lower.infinite && upper.infinite)
+ if (empty || (lower.infinite && upper.infinite))
{
- /* the range has infinite bounds, so it matches everything */
- return makeBoolConst(true, false);
+ /*
+ * If the range is empty there can be no matches, and if both bounds
+ * are infinite it matches everything. But the operators are strict,
+ * so a NULL element yields NULL, not false or true. The constant
+ * result is therefore only correct if the element can't be NULL;
+ * otherwise leave the expression alone.
+ */
+ if (!IsA(elemExpr, Const) || ((Const *) elemExpr)->constisnull)
+ return NULL;
+ return makeBoolConst(!empty, false);
}
else
{
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index 435c730..f13faf6 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1855,19 +1855,19 @@ DETAIL: A result of type anyrange requires at least one input of type anyrange
-- empty range
explain (verbose, costs off)
select current_date <@ daterange 'empty';
- QUERY PLAN
------------------
+ QUERY PLAN
+------------------------------------------------
Result
- Output: false
+ Output: (CURRENT_DATE <@ 'empty'::daterange)
(2 rows)
-- unbounded range
explain (verbose, costs off)
select current_date <@ daterange(NULL, NULL);
- QUERY PLAN
-----------------
+ QUERY PLAN
+----------------------------------------------
Result
- Output: true
+ Output: (CURRENT_DATE <@ '(,)'::daterange)
(2 rows)
-- only lower bound present
@@ -1941,6 +1941,27 @@ select daterange('2002-09-25'::date, 'Infinity', '[]') @> current_date;
Output: ((CURRENT_DATE >= '09-25-2002'::date) AND (CURRENT_DATE <= 'infinity'::date))
(2 rows)
+-- A NULL element must give NULL, so these can't be reduced to constants
+-- unless the element is known not to be NULL
+explain (verbose, costs off)
+select c <@ int4range(NULL, NULL), int4range(NULL, NULL) @> c,
+ c <@ 'empty'::int4range, 'empty'::int4range @> c
+from (values (NULL::int), (1)) v(c);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Values Scan on "*VALUES*"
+ Output: ("*VALUES*".column1 <@ '(,)'::int4range), ('(,)'::int4range @> "*VALUES*".column1), ("*VALUES*".column1 <@ 'empty'::int4range), ('empty'::int4range @> "*VALUES*".column1)
+(2 rows)
+
+select c <@ int4range(NULL, NULL), int4range(NULL, NULL) @> c,
+ c <@ 'empty'::int4range, not ('empty'::int4range @> c)
+from (values (NULL::int), (1)) v(c);
+ ?column? | ?column? | ?column? | ?column?
+----------+----------+----------+----------
+ | | |
+ t | t | f | t
+(2 rows)
+
-- Check that volatile cases are not optimized
explain (verbose, costs off)
select now() <@ tstzrange('2024-01-20 00:00', '2024-01-21 00:00');
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index bad1b5a..7aba924 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -687,6 +687,16 @@ select daterange('-Infinity', '1997-04-10'::date, '()') @> current_date;
explain (verbose, costs off)
select daterange('2002-09-25'::date, 'Infinity', '[]') @> current_date;
+-- A NULL element must give NULL, so these can't be reduced to constants
+-- unless the element is known not to be NULL
+explain (verbose, costs off)
+select c <@ int4range(NULL, NULL), int4range(NULL, NULL) @> c,
+ c <@ 'empty'::int4range, 'empty'::int4range @> c
+from (values (NULL::int), (1)) v(c);
+select c <@ int4range(NULL, NULL), int4range(NULL, NULL) @> c,
+ c <@ 'empty'::int4range, not ('empty'::int4range @> c)
+from (values (NULL::int), (1)) v(c);
+
-- Check that volatile cases are not optimized
explain (verbose, costs off)
select now() <@ tstzrange('2024-01-20 00:00', '2024-01-21 00:00');
--
2.43.0
v1-0001-range-containment-null-master.patch (application/octet-stream, 6.3K) download
From 1019bda2339adc00d5555bacd14a161051eb28eb Mon Sep 17 00:00:00 2001
From: Venu Kummari <venu2805k@gmail.com>
Date: Fri, 23 Oct 2026 18:41:59 +0000
Subject: [PATCH v1] Don't reduce range containment with a nullable element to
a constant
The planner support functions for elem <@ range and range @> elem
replace the call with a constant when the range is a constant that is
empty (false) or unbounded on both sides (true). But both operators are
strict, so a NULL element must yield NULL. The constant is wrong
wherever NULL and false are distinguished, e.g. in the select list,
under NOT, or in IS NULL tests:
SELECT c, '(,)'::int4range @> c, NOT ('empty'::int4range @> c)
FROM (VALUES (NULL::int), (1)) v(c);
returned true and true for the NULL row instead of NULL and NULL.
Only reduce to a constant when expr_is_nonnullable() proves that the
element can't be NULL.
Bug introduced by 075df6b20. Back-patch to v17.
---
src/backend/utils/adt/rangetypes.c | 19 ++++++-----
src/test/regress/expected/rangetypes.out | 42 ++++++++++++++++++++----
src/test/regress/sql/rangetypes.sql | 13 ++++++++
3 files changed, 60 insertions(+), 14 deletions(-)
diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index 84d71761c1..557972a7d1 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -3037,15 +3037,18 @@ find_simplified_clause(PlannerInfo *root, Expr *rangeExpr, Expr *elemExpr)
range_deserialize(rangetypcache, range, &lower, &upper, &empty);
- if (empty)
- {
- /* if the range is empty, then there can be no matches */
- return makeBoolConst(false, false);
- }
- else if (lower.infinite && upper.infinite)
+ if (empty || (lower.infinite && upper.infinite))
{
- /* the range has infinite bounds, so it matches everything */
- return makeBoolConst(true, false);
+ /*
+ * If the range is empty there can be no matches, and if both bounds
+ * are infinite it matches everything. But the operators are strict,
+ * so a NULL element yields NULL, not false or true. The constant
+ * result is therefore only correct if the element can't be NULL;
+ * otherwise leave the expression alone.
+ */
+ if (!expr_is_nonnullable(root, elemExpr, NOTNULL_SOURCE_HASHTABLE))
+ return NULL;
+ return makeBoolConst(!empty, false);
}
else
{
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index fb1bb30b7e..7927f2bd76 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1912,19 +1912,19 @@ DETAIL: A result of type anyrange requires at least one input of type anyrange
-- empty range
explain (verbose, costs off)
select current_date <@ daterange 'empty';
- QUERY PLAN
------------------
+ QUERY PLAN
+------------------------------------------------
Result
- Output: false
+ Output: (CURRENT_DATE <@ 'empty'::daterange)
(2 rows)
-- unbounded range
explain (verbose, costs off)
select current_date <@ daterange(NULL, NULL);
- QUERY PLAN
-----------------
+ QUERY PLAN
+----------------------------------------------
Result
- Output: true
+ Output: (CURRENT_DATE <@ '(,)'::daterange)
(2 rows)
-- only lower bound present
@@ -1998,6 +1998,36 @@ select daterange('2002-09-25'::date, 'Infinity', '[]') @> current_date;
Output: ((CURRENT_DATE >= '09-25-2002'::date) AND (CURRENT_DATE <= 'infinity'::date))
(2 rows)
+-- A NULL element must give NULL, so these can't be reduced to constants
+-- unless the element is known not to be NULL
+explain (verbose, costs off)
+select c <@ int4range(NULL, NULL), int4range(NULL, NULL) @> c,
+ c <@ 'empty'::int4range, 'empty'::int4range @> c
+from (values (NULL::int), (1)) v(c);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Values Scan on "*VALUES*"
+ Output: ("*VALUES*".column1 <@ '(,)'::int4range), ('(,)'::int4range @> "*VALUES*".column1), ("*VALUES*".column1 <@ 'empty'::int4range), ('empty'::int4range @> "*VALUES*".column1)
+(2 rows)
+
+select c <@ int4range(NULL, NULL), int4range(NULL, NULL) @> c,
+ c <@ 'empty'::int4range, not ('empty'::int4range @> c)
+from (values (NULL::int), (1)) v(c);
+ ?column? | ?column? | ?column? | ?column?
+----------+----------+----------+----------
+ | | |
+ t | t | f | t
+(2 rows)
+
+explain (verbose, costs off)
+select coalesce(c, 0) <@ int4range(NULL, NULL), 'empty'::int4range @> coalesce(c, 0)
+from (values (NULL::int), (1)) v(c);
+ QUERY PLAN
+---------------------------
+ Values Scan on "*VALUES*"
+ Output: true, false
+(2 rows)
+
-- Check that volatile cases are not optimized
explain (verbose, costs off)
select now() <@ tstzrange('2024-01-20 00:00', '2024-01-21 00:00');
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index e4062c3e6a..cd1081ca83 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -697,6 +697,19 @@ select daterange('-Infinity', '1997-04-10'::date, '()') @> current_date;
explain (verbose, costs off)
select daterange('2002-09-25'::date, 'Infinity', '[]') @> current_date;
+-- A NULL element must give NULL, so these can't be reduced to constants
+-- unless the element is known not to be NULL
+explain (verbose, costs off)
+select c <@ int4range(NULL, NULL), int4range(NULL, NULL) @> c,
+ c <@ 'empty'::int4range, 'empty'::int4range @> c
+from (values (NULL::int), (1)) v(c);
+select c <@ int4range(NULL, NULL), int4range(NULL, NULL) @> c,
+ c <@ 'empty'::int4range, not ('empty'::int4range @> c)
+from (values (NULL::int), (1)) v(c);
+explain (verbose, costs off)
+select coalesce(c, 0) <@ int4range(NULL, NULL), 'empty'::int4range @> coalesce(c, 0)
+from (values (NULL::int), (1)) v(c);
+
-- Check that volatile cases are not optimized
explain (verbose, costs off)
select now() <@ tstzrange('2024-01-20 00:00', '2024-01-21 00:00');
--
2.43.0
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19793: Range containment with empty/unbounded constant range returns true/false for NULL element
@ 2026-10-30 05:40 Venu K <venu2805k@gmail.com>
parent: Venu K <venu2805k@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Venu K @ 2026-10-30 05:40 UTC (permalink / raw)
To: venu2805k@gmail.com; pgsql-bugs@lists.postgresql.org; +Cc: tgl@sss.pgh.pa.us
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@gmail.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@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.
>>
>>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-10-30 05:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-23 18:53 BUG #19793: Range containment with empty/unbounded constant range returns true/false for NULL element PG Bug reporting form <noreply@postgresql.org>
2026-10-23 18:57 ` Venu K <venu2805k@gmail.com>
2026-10-30 05:40 ` Venu K <venu2805k@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