From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: venu2805k@gmail.com
Subject: BUG #19793: Range containment with empty/unbounded constant range returns true/false for NULL element
Date: Fri, 23 Oct 2026 18:53:54 +0000
Message-ID: <19793-16d3a1bf33dff0fb@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, venu2805k@gmail.com
Subject: Re: BUG #19793: Range containment with empty/unbounded constant range returns true/false for NULL element
In-Reply-To: <19793-16d3a1bf33dff0fb@postgresql.org>
References: <19793-16d3a1bf33dff0fb@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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