Re: BUG #19793: Range containment with empty/unbounded constant range returns true/false for NULL element
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
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:
Show quoted text
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.
Import Notes
Reply to msg id not found: 19793-16d3a1bf33dff0fb@postgresql.orgReference msg id not found: 19793-16d3a1bf33dff0fb@postgresql.org