[HACKERS] CUBE seems a bit confused about ORDER BY

Поиск
Список
Период
Сортировка
От Tomas Vondra
Тема [HACKERS] CUBE seems a bit confused about ORDER BY
Дата
Msg-id a9657f6a-b497-36ff-e569-482a2c7e3292@2ndquadrant.com
обсуждение исходный текст
Ответы Re: [HACKERS] CUBE seems a bit confused about ORDER BY  (Alexander Korotkov <a.korotkov@postgrespro.ru>)
Список pgsql-hackers
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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Vik Fearing
Дата:
Сообщение: Re: [HACKERS] Queuing all tables for analyze after recovery
Следующее
От: Alexander Korotkov
Дата:
Сообщение: Re: [HACKERS] CUBE seems a bit confused about ORDER BY