pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 125atishprak@gmail.com
Subject: BUG #19855: GiST index on multirange misses rows for <@ with a multirange query
Date: Mon, 09 Nov 2026 12:55:21 +0000
Message-ID: <19855-58321b0115af92ba@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19855
Logged by:          Atish Prakash
Email address:      125atishprak@gmail.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.







view thread (2+ messages)  latest in thread

Message-ID: <19855-58321b0115af92ba@postgresql.org>
Permalink:  ../19855-58321b0115af92ba@postgresql.org/
Also on:    postgresql.org/message-id/19855-58321b0115af92ba@postgresql.org

 · 

reply

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, 125atishprak@gmail.com
  Subject: Re: BUG #19855: GiST index on multirange misses rows for <@ with a multirange query
  In-Reply-To: <19855-58321b0115af92ba@postgresql.org>
  References: <19855-58321b0115af92ba@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