Hi! On Fri, Oct 20, 2017 at 12:52 AM, Tomas Vondra <tomas.von...@2ndquadrant.com > wrote:
> 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? > +1, that definitely looks like a bug. Thank you for reporting! I'll take a look on it in couple days. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
