CUBE seems a bit confused about ORDER BY

Hi,

I've noticed this suspicious behavior of "cube" data type with ORDER BY,
which I believe is a bug in the extension (or the GiST index support).
The following example comes directly from regression tests added by
33bd250f (so CC Teodor and Stas, who are mentioned in the commit).

This query should produce results with ordering "ascending by 2nd
coordinate or upper right corner". To make it clear, I've added the
"c~>4" expression to the query, otherwise it's right from the test.

test=# SELECT c~>4 "c~>4", * FROM test_cube ORDER BY c~>4 LIMIT 15;
c~>4 | c
------+---------------------------
50 | (30333, 50),(30273, 6)
75 | (43301, 75),(43227, 43)
142 | (19650, 142),(19630, 51)
160 | (2424, 160),(2424, 81)
171 | (3449, 171),(3354, 108)
155 | (18037, 155),(17941, 109)
208 | (28511, 208),(28479, 114)
217 | (19946, 217),(19941, 118)
191 | (16906, 191),(16816, 139)
187 | (759, 187),(662, 163)
266 | (22684, 266),(22656, 181)
255 | (24423, 255),(24360, 213)
249 | (45989, 249),(45910, 222)
377 | (11399, 377),(11360, 294)
389 | (12162, 389),(12103, 309)
(15 rows)

As you can see, it's not actually sorted by the c~>4 coordinate (but by
c~>2, which it the last number).

Moreover, disabling index scans fixes the ordering:

test=# set enable_indexscan = off;
SET
test=# SELECT c~>4, * FROM test_cube ORDER BY c~>4 LIMIT 15; --
ascending by 2nd coordinate or upper right corner
?column? | c
----------+---------------------------
50 | (30333, 50),(30273, 6)
75 | (43301, 75),(43227, 43)
142 | (19650, 142),(19630, 51)
155 | (18037, 155),(17941, 109)
160 | (2424, 160),(2424, 81)
171 | (3449, 171),(3354, 108)
187 | (759, 187),(662, 163)
191 | (16906, 191),(16816, 139)
208 | (28511, 208),(28479, 114)
217 | (19946, 217),(19941, 118)
249 | (45989, 249),(45910, 222)
255 | (24423, 255),(24360, 213)
266 | (22684, 266),(22656, 181)
367 | (31018, 367),(30946, 333)
377 | (11399, 377),(11360, 294)
(15 rows)

Seems like a bug somewhere in gist_cube_ops, I guess?

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services