Fwd: Fix containment and B-tree ordering for cubes of different dimensions

From: Rui Zhao <zhaorui126(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
Subject: Fwd: Fix containment and B-tree ordering for cubes of different dimensions
Date: 2026-11-21 17:04:26
Message-ID: CAHWVJhH5mkLg+7sBT8dj7O=18gvm8-5364MP-Sefx=o=Fq0pbQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

We got wrong results when comparing cubes of different dimensions in
production. I can reproduce the problem on current master (892cfb4cbfe)
with both containment and B-tree comparisons.

1. Containment ignores a missing coordinate (0001)

The "Defaults" section of doc/src/sgml/cube.sgml says:

In all binary operations on differently-dimensioned cubes,
the lower-dimensional one is assumed to be a Cartesian projection,
i. e., having zeroes in place of coordinates omitted in the string
representation.

On master the following point is reported as inside the box:

SELECT '0,0'::cube <@ '(0,0,-1),(0,0,-2)'::cube AS bool;
bool
------
t
(1 row)

The point's third coordinate is zero. The box runs from -2 to -1 on
that axis, so the point is outside. cube_contains_v0() checks only x
and y here. 0001 also checks that the box contains zero on each axis
missing from the point. The query then returns false.

A plain GiST index on the cube column needs no rebuild for 0001.
Expression and partial indexes need rebuilding if the fix changes an
indexed value or which rows satisfy the index predicate.

2. B-tree comparison is not transitive (0002)

WITH t(a,b,c) AS (VALUES ('(2,0),(3,1)'::cube,
'(2,0,0,0,0),(3,1,0,0,1)'::cube,
'(2,0,1)'::cube))
SELECT a < b AS a_lt_b, b < c AS b_lt_c, a < c AS a_lt_c FROM t;
a_lt_b | b_lt_c | a_lt_c
--------+--------+--------
t | t | f
(1 row)

Here a < b and b < c, so a < c should also be true. When comparing a
with c, cube_cmp_v0() finds that their lower x and y bounds match. It
then puts a after c based on their upper x bounds (3 and 2), before
comparing their lower bounds on the third axis (0 for a, 1 for c).

0002 compares all lower bounds before any upper bounds, treating
missing coordinates as zero. The third axis now puts a before c, and
all three comparisons return true. Existing cube B-tree indexes whose
order changes need a REINDEX.

Regards,
Rui

Attachment Content-Type Size
v1-0001-Fix-cube-containment-checks-for-omitted-coordinates.patch application/x-patch 4.5 KB
v1-0002-Fix-cube-B-tree-ordering-for-values-of-different-dimensions.patch application/x-patch 7.1 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Greg Burd 2026-11-21 17:08:09 Re: ZSTD TOAST compression, and an extensible compression method encoding
Previous Message Rui Zhao 2026-11-21 16:42:47 Re: SSI can miss conflicts between index-only scans and heap writes