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
From 97a05e683d0b85dfac23e977bfd81da938b2f927 Mon Sep 17 00:00:00 2001
From: Rui Zhao <[email protected]>
Date: Sat, 21 Nov 2026 23:11:00 +0800
Subject: [PATCH v1 1/2] Fix cube containment checks for omitted coordinates
A lower-dimensional cube has zeroes in its omitted coordinates, but
cube_contains_v0() ignores the extra dimensions of the containing cube.
This makes (1) and (1,0) give different answers to the same <@ test.
GiST's internal overlap check does account for those zeroes, so the
query can also give different results with and without a GiST index.
Require each extra dimension of the containing cube to include zero.
Correct the existing <@ and @> expectations, check a box below zero,
and reuse the one-dimensional point in the GiST scan tests.
---
contrib/cube/cube.c | 10 +++++++++-
contrib/cube/expected/cube.out | 16 ++++++++++++++--
contrib/cube/sql/cube.sql | 4 ++++
3 files changed, 27 insertions(+), 3 deletions(-)
diff --git a/contrib/cube/cube.c b/contrib/cube/cube.c
index 28d48549b36..431d0458cc9 100644
--- a/contrib/cube/cube.c
+++ b/contrib/cube/cube.c
@@ -1151,7 +1151,7 @@ cube_contains_v0(NDBOX *a, NDBOX *b)
}
}
- /* Can't care less about the excess dimensions of (a), if any */
+ /* Compare the dimensions present in both cubes. */
for (i = 0; i < Min(DIM(a), DIM(b)); i++)
{
if (Min(LL_COORD(a, i), UR_COORD(a, i)) >
@@ -1162,6 +1162,14 @@ cube_contains_v0(NDBOX *a, NDBOX *b)
return false;
}
+ /* The dimensions absent from b are zero. */
+ for (; i < DIM(a); i++)
+ {
+ if (Min(LL_COORD(a, i), UR_COORD(a, i)) > 0 ||
+ Max(LL_COORD(a, i), UR_COORD(a, i)) < 0)
+ return false;
+ }
+
return true;
}
diff --git a/contrib/cube/expected/cube.out b/contrib/cube/expected/cube.out
index 47787c50bd9..acbeb08c6cf 100644
--- a/contrib/cube/expected/cube.out
+++ b/contrib/cube/expected/cube.out
@@ -773,7 +773,7 @@ SELECT '0,0,0'::cube <@ '0,0,0'::cube AS bool;
SELECT '0,0'::cube <@ '0,0,1'::cube AS bool;
bool
------
- t
+ f
(1 row)
SELECT '0,0,0'::cube <@ '0,0,1'::cube AS bool;
@@ -782,6 +782,12 @@ SELECT '0,0,0'::cube <@ '0,0,1'::cube AS bool;
f
(1 row)
+SELECT '0,0'::cube <@ '(0,0,-1),(0,0,-2)'::cube AS bool;
+ bool
+------
+ f
+(1 row)
+
SELECT '1,0,0'::cube <@ '0,0,1'::cube AS bool;
bool
------
@@ -866,7 +872,7 @@ SELECT '0,0,0'::cube @> '0,0,0'::cube AS bool;
SELECT '0,0,1'::cube @> '0,0'::cube AS bool;
bool
------
- t
+ f
(1 row)
SELECT '0,0,1'::cube @> '0,0,0'::cube AS bool;
@@ -1583,6 +1589,12 @@ RESET enable_bitmapscan;
-- Test kNN
INSERT INTO test_cube VALUES ('(1,1)'), ('(100000)'), ('(0, 100000)'); -- Some corner cases
SET enable_seqscan = false;
+-- The one-dimensional point (100000) has zero in its second dimension.
+SELECT c FROM test_cube WHERE c <@ '(100000,1)';
+ c
+---
+(0 rows)
+
-- Test different metrics
SET extra_float_digits = 0;
SELECT *, c <-> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <-> '(100, 100),(500, 500)'::cube LIMIT 5;
diff --git a/contrib/cube/sql/cube.sql b/contrib/cube/sql/cube.sql
index eec90d21ee3..ef9271658de 100644
--- a/contrib/cube/sql/cube.sql
+++ b/contrib/cube/sql/cube.sql
@@ -210,6 +210,7 @@ SELECT '0'::cube <@ '0'::cube AS bool;
SELECT '0,0,0'::cube <@ '0,0,0'::cube AS bool;
SELECT '0,0'::cube <@ '0,0,1'::cube AS bool;
SELECT '0,0,0'::cube <@ '0,0,1'::cube AS bool;
+SELECT '0,0'::cube <@ '(0,0,-1),(0,0,-2)'::cube AS bool;
SELECT '1,0,0'::cube <@ '0,0,1'::cube AS bool;
SELECT '(1,0,0),(0,0,1)'::cube <@ '(1,0,0),(0,0,1)'::cube AS bool;
SELECT '(1,0,0),(0,0,1)'::cube <@ '(-1,-1,-1),(1,1,1)'::cube AS bool;
@@ -402,6 +403,9 @@ RESET enable_bitmapscan;
INSERT INTO test_cube VALUES ('(1,1)'), ('(100000)'), ('(0, 100000)'); -- Some corner cases
SET enable_seqscan = false;
+-- The one-dimensional point (100000) has zero in its second dimension.
+SELECT c FROM test_cube WHERE c <@ '(100000,1)';
+
-- Test different metrics
SET extra_float_digits = 0;
SELECT *, c <-> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <-> '(100, 100),(500, 500)'::cube LIMIT 5;
base-commit: 892cfb4cbfe0e37cc492f0e750e80cfa6394d26d
From 7f2d19cc38597cc5369ce81d6a48407c107b9bff Mon Sep 17 00:00:00 2001
From: Rui Zhao <[email protected]>
Date: Sat, 21 Nov 2026 22:40:29 +0800
Subject: [PATCH v1 2/2] Fix cube B-tree ordering for values of different
dimensions
cube_cmp_v0() compared all common upper bounds before looking at the
remaining lower bounds. As a result, comparing a pair of cubes could
give priority to a different bound depending on the dimensions of the
other cube. Three finite values can then satisfy A < B, B < C and C < A,
and a B-tree scan can miss rows.
Compare all lower bounds before any upper bounds, padding missing
coordinates with zero in both passes. Compare dimensions last to keep
(1) distinct from (1,0). Use PostgreSQL float8 ordering for each bound.
Existing cube B-tree indexes whose order changes need to be rebuilt.
Changing the comparator also requires considering partition keys that
use the old ordering.
---
contrib/cube/cube.c | 98 ++++++++++------------------------
contrib/cube/expected/cube.out | 12 +++--
contrib/cube/sql/cube.sql | 6 ++-
doc/src/sgml/cube.sgml | 6 ++-
4 files changed, 46 insertions(+), 76 deletions(-)
diff --git a/contrib/cube/cube.c b/contrib/cube/cube.c
index 431d0458cc9..17add7fb5e2 100644
--- a/contrib/cube/cube.c
+++ b/contrib/cube/cube.c
@@ -12,6 +12,7 @@
#include "access/gist.h"
#include "access/stratnum.h"
+#include "common/int.h"
#include "cubedata.h"
#include "libpq/pqformat.h"
#include "utils/array.h"
@@ -937,87 +938,46 @@ rt_cube_size(NDBOX *a, double *size)
}
/*
- * make up a metric in which one box will be 'lower' than the other
- * -- this can be useful for sorting and to determine uniqueness
+ * Sort by all lower coordinates, then all upper coordinates. Treat a
+ * missing coordinate as zero in both passes. Comparing the upper bounds
+ * of the common dimensions before the remaining lower bounds would make
+ * the order depend on the dimensionality of the other cube.
*/
-int32
-cube_cmp_v0(NDBOX *a, NDBOX *b)
+static inline double
+cube_sort_coord(NDBOX *cube, int i, bool upper)
{
- int i;
- int dim;
+ if (i >= DIM(cube))
+ return 0.0;
- dim = Min(DIM(a), DIM(b));
+ if (upper)
+ return float8_max(LL_COORD(cube, i), UR_COORD(cube, i));
+ return float8_min(LL_COORD(cube, i), UR_COORD(cube, i));
+}
- /* compare the common dimensions */
- for (i = 0; i < dim; i++)
- {
- if (Min(LL_COORD(a, i), UR_COORD(a, i)) >
- Min(LL_COORD(b, i), UR_COORD(b, i)))
- return 1;
- if (Min(LL_COORD(a, i), UR_COORD(a, i)) <
- Min(LL_COORD(b, i), UR_COORD(b, i)))
- return -1;
- }
- for (i = 0; i < dim; i++)
- {
- if (Max(LL_COORD(a, i), UR_COORD(a, i)) >
- Max(LL_COORD(b, i), UR_COORD(b, i)))
- return 1;
- if (Max(LL_COORD(a, i), UR_COORD(a, i)) <
- Max(LL_COORD(b, i), UR_COORD(b, i)))
- return -1;
- }
+int32
+cube_cmp_v0(NDBOX *a, NDBOX *b)
+{
+ int ndims = Max(DIM(a), DIM(b));
- /* compare extra dimensions to zero */
- if (DIM(a) > DIM(b))
+ for (int i = 0; i < ndims; i++)
{
- for (i = dim; i < DIM(a); i++)
- {
- if (Min(LL_COORD(a, i), UR_COORD(a, i)) > 0)
- return 1;
- if (Min(LL_COORD(a, i), UR_COORD(a, i)) < 0)
- return -1;
- }
- for (i = dim; i < DIM(a); i++)
- {
- if (Max(LL_COORD(a, i), UR_COORD(a, i)) > 0)
- return 1;
- if (Max(LL_COORD(a, i), UR_COORD(a, i)) < 0)
- return -1;
- }
+ int cmp = float8_cmp_internal(cube_sort_coord(a, i, false),
+ cube_sort_coord(b, i, false));
- /*
- * if all common dimensions are equal, the cube with more dimensions
- * wins
- */
- return 1;
+ if (cmp != 0)
+ return cmp;
}
- if (DIM(a) < DIM(b))
+ for (int i = 0; i < ndims; i++)
{
- for (i = dim; i < DIM(b); i++)
- {
- if (Min(LL_COORD(b, i), UR_COORD(b, i)) > 0)
- return -1;
- if (Min(LL_COORD(b, i), UR_COORD(b, i)) < 0)
- return 1;
- }
- for (i = dim; i < DIM(b); i++)
- {
- if (Max(LL_COORD(b, i), UR_COORD(b, i)) > 0)
- return -1;
- if (Max(LL_COORD(b, i), UR_COORD(b, i)) < 0)
- return 1;
- }
+ int cmp = float8_cmp_internal(cube_sort_coord(a, i, true),
+ cube_sort_coord(b, i, true));
- /*
- * if all common dimensions are equal, the cube with more dimensions
- * wins
- */
- return -1;
+ if (cmp != 0)
+ return cmp;
}
- /* They're really equal */
- return 0;
+ /* Preserve the dimension count as the final tie-breaker. */
+ return pg_cmp_u32(DIM(a), DIM(b));
}
Datum
diff --git a/contrib/cube/expected/cube.out b/contrib/cube/expected/cube.out
index acbeb08c6cf..0ed9f74deb5 100644
--- a/contrib/cube/expected/cube.out
+++ b/contrib/cube/expected/cube.out
@@ -639,10 +639,14 @@ SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool
f
(1 row)
-SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool;
- bool
-------
- t
+-- Check that the ordering of these cubes stays transitive with a third cube.
+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 | t
(1 row)
SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,1),(3,1,0,0,0)'::cube AS bool;
diff --git a/contrib/cube/sql/cube.sql b/contrib/cube/sql/cube.sql
index ef9271658de..c3fd3886672 100644
--- a/contrib/cube/sql/cube.sql
+++ b/contrib/cube/sql/cube.sql
@@ -177,7 +177,11 @@ SELECT '1,1'::cube > '1,2'::cube AS bool;
SELECT '1,1'::cube < '1,2'::cube AS bool;
SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool;
-SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,0),(3,1,0,0,1)'::cube AS bool;
+-- Check that the ordering of these cubes stays transitive with a third cube.
+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;
SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,1),(3,1,0,0,0)'::cube AS bool;
SELECT '(2,0),(3,1)'::cube < '(2,0,0,0,1),(3,1,0,0,0)'::cube AS bool;
SELECT '(2,0),(3,1)'::cube > '(2,0,0,0,0),(3,1,0,0,0)'::cube AS bool;
diff --git a/doc/src/sgml/cube.sgml b/doc/src/sgml/cube.sgml
index a11c0cbd767..0c0fc160c70 100644
--- a/doc/src/sgml/cube.sgml
+++ b/doc/src/sgml/cube.sgml
@@ -226,8 +226,10 @@
In addition to the above operators, the usual comparison
operators shown in <xref linkend="functions-comparison-op-table"/> are
available for type <type>cube</type>. These
- operators first compare the first coordinates, and if those are equal,
- compare the second coordinates, etc. They exist mainly to support the
+ operators compare the lower bounds in dimension order, followed by
+ the upper bounds in the same order. Missing bounds are taken as zero.
+ If all bounds compare equal, the cube with fewer dimensions sorts first.
+ They exist mainly to support the
b-tree index operator class for <type>cube</type>, which can be useful for
example if you would like a UNIQUE constraint on a <type>cube</type> column.
Otherwise, this ordering is not of much practical use.