BUG #19855: GiST index on multirange misses rows for <@ with a multirange query

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: 125atishprak(at)gmail(dot)com
Subject: BUG #19855: GiST index on multirange misses rows for <@ with a multirange query
Date: 2026-11-09 12:55:21
Message-ID: 19855-58321b0115af92ba@postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 19855
Logged by: Atish Prakash
Email address: 125atishprak(at)gmail(dot)com
PostgreSQL version: 18.6
Operating system: Linux x86_64
Description:

A GiST index on a multirange column returns too few rows for "column <@
multirange"
when the stored value has a gap. No error is raised.

CREATE TABLE m(c int4multirange);
INSERT INTO m VALUES ('{[1,2),[5,9)}');

SELECT count(*) FROM m WHERE c <@ '{[1,2),[5,9)}'; -- 1
SELECT count(*) FROM m WHERE c <@ '{[0,3),[4,10)}'; -- 1

CREATE INDEX ON m USING gist (c);
SET enable_seqscan = off;

SELECT count(*) FROM m WHERE c <@ '{[1,2),[5,9)}'; -- 0, expected 1
SELECT count(*) FROM m WHERE c <@ '{[0,3),[4,10)}'; -- 0, expected 1

The first query asks whether a value is contained by itself. An index scan
and a
bitmap scan both give the wrong answer.

Cause: multirange_gist_compress() stores the union range with the gaps
filled in, so
the key here is [1,9), and multirange_gist_consistent() sets recheck = true
because
the key is lossy. At leaf level, RANGESTRAT_CONTAINED_BY with a multirange
query calls
range_gist_consistent_leaf_multirange(), which calls
multirange_contains_range_internal(query, key). [1,9) is not contained by
the query
because of the query's gap, so the entry is rejected before the recheck can
run.

That helper is written for exact keys and is also used by
range_gist_consistent(),
where the check is right. Range columns are not affected.

A possible patch is attached (0001-fix-multirange-gist-contained-by.patch).
In the
multirange path only, it rejects an entry only if the key falls outside the
union of
the query. An empty key stands for an empty multirange, so it is kept. With
it, index
and sequential scans agreed on all 120 operator and query pairs I compared
on a
5,000-row table, range columns still gave correct results, and all 239 core
regression
tests pass on master. It adds no new test.

Existing indexes do not need a rebuild for this fix, since only the search
changes.

Found by comparing index and sequential scans across the operator classes
listed in
the catalog. I reproduced and checked the result myself.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2026-11-09 12:57:00 BUG #19856: GiST index on tsquery misses empty tsquery rows for <@
Previous Message PG Bug reporting form 2026-11-09 12:53:06 BUG #19854: GiST distance ordering fails on NaN coordinates, Assert in computeDistance