pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19855: GiST index on multirange misses rows for <@ with a multirange query
2+ messages / 2 participants
[nested] [flat]

* BUG #19855: GiST index on multirange misses rows for <@ with a multirange query
@ 2026-11-09 12:55  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2026-11-09 12:55 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: 125atishprak@gmail.com

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.







^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #19855: GiST index on multirange misses rows for <@ with a multirange query
@ 2026-11-09 12:59  Atisss hh <125atishprak@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Atisss hh @ 2026-11-09 12:59 UTC (permalink / raw)
  To: 125atishprak@gmail.com; pgsql-bugs@lists.postgresql.org

On Mon, 9 Nov 2026 at 18:26, PG Bug reporting form <noreply@postgresql.org>
wrote:

> 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.
>
>
>

Attachments:

0001-fix-multirange-gist-contained-by.patch (text/x-patch, 1.4K) download diff --git a/src/backend/utils/adt/rangetypes_gist.c b/src/backend/utils/adt/rangetypes_gist.c index 1a01a8f4c3..5abf7be535 100644 --- a/src/backend/utils/adt/rangetypes_gist.c +++ b/src/backend/utils/adt/rangetypes_gist.c @@ -295,8 +295,31 @@ multirange_gist_consistent(PG_FUNCTION_ARGS) if (GIST_LEAF(entry)) { if (!OidIsValid(subtype) || subtype == ANYMULTIRANGEOID) - result = range_gist_consistent_leaf_multirange(typcache, strategy, key, - DatumGetMultirangeTypeP(query)); + { + MultirangeType *mr = DatumGetMultirangeTypeP(query); + + if (strategy == RANGESTRAT_CONTAINED_BY) + { + /* + * The key is the union of the indexed multirange, with its + * gaps filled in, so it need not be contained by the query + * even when the indexed value is. We can only reject the + * entry if the key goes outside the query's own union. An + * empty key stands for an empty multirange, which is + * contained by anything. + */ + if (RangeIsEmpty(key)) + result = true; + else if (MultirangeIsEmpty(mr)) + result = false; + else + result = range_contains_internal(typcache, + multirange_get_union_range(typcache, mr), + key); + } + else + result = range_gist_consistent_leaf_multirange(typcache, strategy, key, mr); + } else if (subtype == ANYRANGEOID) result = range_gist_consistent_leaf_range(typcache, strategy, key, DatumGetRangeTypeP(query));
^ permalink raw reply [nested|flat] 2+ messages in thread


end of thread, other threads:[~2026-11-09 12:59 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-09 12:55 BUG #19855: GiST index on multirange misses rows for <@ with a multirange query PG Bug reporting form <noreply@postgresql.org>
2026-11-09 12:59 ` Atisss hh <125atishprak@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