From: | Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru> |
---|---|
To: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Stas Kelvich <stas(dot)kelvich(at)gmail(dot)com>, Teodor Sigaev <teodor(at)sigaev(dot)ru> |
Subject: | Re: CUBE seems a bit confused about ORDER BY |
Date: | 2017-10-19 22:01:51 |
Message-ID: | CAPpHfds6XXSv7CYg5YFDsZS+WPjgvNhDPbOBr3S3BYPjj1UnLA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi!
On Fri, Oct 20, 2017 at 12:52 AM, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Pryzby | 2017-10-20 00:01:27 | per-sesson errors after interrupting CLUSTER pg_attrdef |
Previous Message | Tomas Vondra | 2017-10-19 21:52:34 | CUBE seems a bit confused about ORDER BY |