contrib/cube: NaN coordinates break the btree equivalence relation, so UNIQUE rejects distinct values and ORDER BY is unstable

Hello, contrib/cube compares coordinates with the plain C operators ">" and "<", applied to the results of the Min() and Max() macros. None of these is NaN-aware. PostgreSQL's own convention is in src/include/utils/float.h, where float8_lt(), float8_gt() and float8_eq() give NaN a defined place in a total order, with the comment that "the important thing is to have a consistent sort order". contrib/cube/cube.c does not use those helpers anywhere. The effect is that two cubes compare equal whenever they differ only in dimensions where one of them holds NaN. In one dimension that means a cube at NaN compares equal to every other cube. In more dimensions a differing finite coordinate still separates them: -- 1-D: equal select cube(array['NaN'::float8]) = cube(array[0::float8]); -- t -- 2-D differing only in the NaN dimension: equal select cube(array['NaN'::float8,5]) = cube(array[1::float8,5]); -- t -- 2-D differing in a finite dimension as well: NOT equal select cube(array['NaN'::float8,5]) = cube(array[1::float8,3]); -- f So "=" is not transitive, and cube_ops is the default btree operator class for cube (pg_opclass.opcdefault = t), covering <, <=, =, >, >=. doc/src/sgml/btree.sgml states the requirement this breaks: "There are some basic assumptions that a btree operator family must satisfy: An = operator must be an equivalence relation; that is, for all non-null values A, B, C of the data type: ... if A = B and B = C, then A = C (transitive law)" NaN is not null, so there is no exemption. The failure is direct: select cube_cmp(cube(array['NaN'::float8]), cube(array[0::float8])), -- 0 cube_cmp(cube(array['NaN'::float8]), cube(array[1::float8])), -- 0 cube_cmp(cube(array[0::float8]), cube(array[1::float8])); -- -1 Three consequences follow. All were measured on PostgreSQL 15.19 with contrib/cube 1.5. The same code is on master: contrib/cube/cube.c, blob 28d48549b36646003e10b92d8b6422449c2de9aa, with zero uses of float8_lt, float8_gt, float8_eq, float8_le or float8_ge. 1. A UNIQUE CONSTRAINT REJECTS DISTINCT VALUES, CLAIMING THEY ALREADY EXIST This is the use case the documentation gives for these operators. From doc/src/sgml/cube.sgml: "These operators first compare the first coordinates, and if those are equal, compare the second coordinates, etc. They exist mainly to support the b-tree index operator class for cube, which can be useful for example if you would like a UNIQUE constraint on a cube column." create table u1 (c cube unique); insert into u1 values (cube(array['NaN'::float8])); -- INSERT 0 1 insert into u1 values (cube(array[0::float8])); ERROR: duplicate key value violates unique constraint "u1_c_key" DETAIL: Key (c)=((0)) already exists. insert into u1 values (cube(array[1::float8])); ERROR: duplicate key value violates unique constraint "u1_c_key" DETAIL: Key (c)=((1)) already exists. -- the table holds only (NaN) (0) does not already exist. One NaN row blocks every later distinct value in that dimension, and the error message states something untrue. The matched control, identical sequence on float8, accepts all three: create table u2 (v float8 unique); insert into u2 values ('NaN'), (0), (1); -- INSERT 0 3 select string_agg(v::text, ' ' order by v) from u2; -- 0 1 NaN This is data rejection rather than a wrong answer, so it is the consequence I would put first. 2. ORDER BY RETURNS A DIFFERENT RESULT FOR THE SAME SET OF VALUES The same four values, two insertion orders, no index: create table a (c cube); create table b (c cube); insert into a values (cube(array[0::float8])), (cube(array['NaN'::float8])), (cube(array[1::float8])), (cube(array[2::float8])); insert into b values (cube(array[2::float8])), (cube(array[1::float8])), (cube(array['NaN'::float8])), (cube(array[0::float8])); select string_agg(c::text, ' ' order by c) from a; -- (0) (NaN) (1) (2) select string_agg(c::text, ' ' order by c) from b; -- (1) (2) (NaN) (0) The second result is not in sorted order: (0) follows (2). Both plans are sequential scans; the output depends on physical row order, which is what an inconsistent comparator gives. The same two insertion orders on a float8 column both give 0 1 2 NaN. 3. AN INDEX SCAN AND A SEQUENTIAL SCAN RETURN DIFFERENT ROW COUNTS create table t (id int, c cube); insert into t select g, case when g % 3 = 0 then cube(array['NaN'::float8]) else cube(array[g::float8]) end from generate_series(1, 2000) g; create index on t using btree (c); analyze t; -- 666 of the 2000 rows hold NaN set enable_seqscan=on; set enable_indexscan=off; set enable_bitmapscan=off; select count(*) from t where c = cube(array[1001::float8]); -- 667 set enable_seqscan=off; set enable_indexscan=on; set enable_bitmapscan=on; select count(*) from t where c = cube(array[1001::float8]); -- 1 The sequential arm returns 667 for every probe value I tried: the one intended match plus all 666 NaN rows. The index arm returns 1 or 2 depending on where the descent lands (1 for probes 250, 1001 and 1999; 2 for probes 7, 500 and 1000). Neither count is correct. Under the float8 convention the answer is 1, which is what the control below returns. I am not claiming the index lost rows it should have had, and I am not claiming the index is corrupt: both plans are consistent with the comparator they were given, and the comparator is the defect. The point is only that the two plans disagree. The disagreement is not plan-specific or build-order-specific. It reproduces with Index Only Scan, plain Index Scan and Bitmap Heap Scan, with the index created before the data as well as after, and after REINDEX. The matched control, the identical harness on a float8 column: create table ctl (id int, v float8); insert into ctl select g, case when g % 3 = 0 then 'NaN'::float8 else g::float8 end from generate_series(1, 2000) g; create index on ctl using btree (v); analyze ctl; set enable_seqscan=on; set enable_indexscan=off; set enable_bitmapscan=off; select count(*) from ctl where v = 1001::float8; -- 1 set enable_seqscan=off; set enable_indexscan=on; set enable_bitmapscan=on; select count(*) from ctl where v = 1001::float8; -- 1 Both arms agree, so the difference in section 3 belongs to cube rather than to the harness. The results in the opening section need no privilege beyond connecting and running a query. They reproduce for a role with no superuser attribute, no CREATE privilege on the database and no CREATE on schema public. THE CODE contrib/cube/cube.c, cube_cmp_v0: 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; } Both comparisons are false when either side is NaN, so this loop and the Max() loop that follows it both fall through, and the function returns 0. I am deliberately not giving a count of affected call sites. I wrote two sweeps for it and they disagreed, so I do not trust either number enough to put it in front of you. The claim I will stand behind is the one you can check in a single command: contrib/cube/cube.c contains no use of float8_lt, float8_gt, float8_eq, float8_le or float8_ge, on master or on REL_15_19. The same NaN-blind Min() and Max() uses also appear in cube_union_v0, cube_inter, cube_contains_v0, cube_overlap_v0, cube_ll_coord and cube_ur_coord, which is why &&, @>, <@, cube_union and cube_inter give answers of the same kind. PRECEDENT - The same class was fixed in btree_gist four months ago, and that commit message states this report's argument in upstream's own words. Commit 7d3448961da3f8cb5c78b9d58c5e03b6bff53364, 2026-07-01, "btree_gist: fix NaN handling in float4/float8 opclasses": "The float4 and float8 btree_gist opclasses compared keys with raw C operators (==, <, >). IEEE 754 makes every comparison involving NaN false, so GiST disagreed with the regular float comparison operators and with the btree opclass, which uses float[4|8]_cmp_internal() (so that all NaNs are equal and NaN sorts after every non-NaN value)." It also says: "users who have NaN entries in a btree_gist index would be well advised to reindex it." Section 3 is the cube form of the same hazard. It touched contrib/btree_gist only, all ten files. cube was not changed. That fix came from two reports on this list, #19501 and #19524. I read both threads in full. Neither mentions cube and neither names any other contrib module as still affected, so I believe this is a distinct report rather than a follow-up to those. - The commit that gave cube its current float8 input and output behaviour said at the time that the NaN work was incomplete. Commit f31a931fade868d788ef4480c59753a2d5059246, 2016-09-27, "Improve contrib/cube's handling of zero-D cubes, infinities, and NaNs", Tom Lane, reviewed by Amul Sul: "As in that commit, I don't pretend that this patch fixes all insane corner-case behaviors that may exist for NaNs, but it's a step forward." - A current pgsql-hackers thread documents the same root cause from the GiST side. In "Re: Improve cube GiST page splits" (message-id 179073303931.1126.9241560630784497637.pgcf@coridan.postgresql.org, 2026-09-30), Rustam Allakov writes, in one paragraph: "The bounding interval and cube_coord_low()/cube_coord_high() use the plain Min()/Max() macros. With NaN they give different answers depending on argument order: Min(x, NaN) is NaN, but Min(NaN, y) is y. [...] gist_box_picksplit() builds its bounding box with adjustBox(), which compares using float8_lt()/float8_gt(), and the equivalent insert into a gist-indexed box column works on master." (The elision is three sentences about the page-split patch's division by zero.) That thread is about an error an unmerged page-split patch introduces on data master accepts. This report is about the shipped default btree opclass on master accepting data and then mishandling it. Same root cause family, different defect. I am happy for this to be folded into that thread instead if you would rather keep it in one place. For completeness on the "is this intended" question: doc/src/sgml/cube.sgml and contrib/cube/CHANGES contain no mention of NaN, cube.c contains no isnan and no comment claiming the comparison is undefined for NaN, and the regression suite's only NaN case is the I/O round trip at sql/cube.sql:27. No test asserts a NaN comparison result. ONE NOTE ON A FIX Replacing ">" and "<" with float8_gt() and float8_lt() is not sufficient on its own. Min() and Max() are plain macros and are NaN-blind too, so for a non-point cube the operand is already wrong before the comparison runs. A fix needs to address the macro uses as well as the operators. I have not sent a patch, because the choice between a NaN-aware local helper and a rewrite of the Min()/Max() uses is a style call for the maintainers. I am happy to write and test one in whichever shape you prefer, including the regression cases. Regards, Apex Security Research