Bug #97648 | Bug in order by clause in union clause | ||
---|---|---|---|
Submitted: | 14 Nov 2019 20:25 | Modified: | 18 Nov 2019 21:12 |
Reporter: | Andrei Mart | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.7, 8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | union sorting bug |
[14 Nov 2019 20:25]
Andrei Mart
[15 Nov 2019 12:23]
MySQL Verification Team
Hello Mr. Mart, Thank you for your bug report. However, we do need some additional information in order to process your report. First of all, you have described two very different queries. Which one is the one that you are reporting. First or second one ??? Next, SQL standard has changed between 5.6 and 5.7. With the UNION as you have written it, each SELECT node is sorted totally separately and then both results are merged through the distinct filter Next, try UNION ALL and if you get two separately sorted results, displayed one after the other, then this is simply not a bug. Last, if wish that the entire result is ordered, you should use a different syntax, as described in our Reference Manual.
[15 Nov 2019 16:07]
Andrei Mart
Data table file
Attachment: new_table.sql (application/octet-stream, text), 1.63 KiB.
[15 Nov 2019 16:08]
Andrei Mart
sql strategment union, result us bug
Attachment: union_strategment.sql (application/octet-stream, text), 286 bytes.
[15 Nov 2019 16:14]
Andrei Mart
Get files from attachment's and see result - documentation https://dev.mysql.com/doc/refman/5.7/en/union.html (SELECT id, value FROM new_table WHERE id < 5 ORDER BY value asc) UNION (SELECT id, value FROM new_table WHERE id >= 5 ORDER BY value desc); ---------------------- "id" "value" "1" "202" "2" "205" "3" "100" "4" "125" "5" "80" "6" "210" "7" "10" "8" "12" "9" "18" "10" "20" ---------------------- The first set must be sorted in ascending order by value, and the second in descending order by value, as you can see, is incorrect. For reference - in version 5.6 it's worked. Please fix this bug.
[18 Nov 2019 12:49]
MySQL Verification Team
Hi, You have not answered all of my questions, nor have you followed any of my directions. Hence, try first UNION ALL , instead of just UNION ..........
[18 Nov 2019 13:28]
MySQL Verification Team
Hi, I am repeating once again. According to SQL standard, if you use UNION (without ALL) then, after each SELECT node has performed its sorting, then UNION will pass both results through DISTINCT filter and destroy all sorting. Also, you are using node sorting, instead of the sorting for the entire query. Our Reference Manual, contains all of the info. Last, but not least, please do mind your language.
[18 Nov 2019 14:05]
Andrei Mart
(SELECT id, value FROM new_table WHERE id < 5 ORDER BY value asc) UNION ALL (SELECT id, value FROM new_table WHERE id >= 5 ORDER BY value desc); -------------------------------- "id" "value" "1" "202" "2" "205" "3" "100" "4" "125" "5" "80" "6" "210" "7" "10" "8" "12" "9" "18" "10" "20" -------------------------- USE - UNION ALL Sorting error anywhere
[18 Nov 2019 14:11]
MySQL Verification Team
This truly does not look good. Can you upload a dump of that table ... Use "Files" tab ....
[18 Nov 2019 14:38]
Andrei Mart
Dump data file is already loaded - title is Data table file
[18 Nov 2019 16:26]
MySQL Verification Team
Hi, Unfortunately, I have repeated your results with both, latest 5.7 and latest 8.0. Verified as reported.
[18 Nov 2019 18:03]
Andrei Mart
Как я понял вы получили также неверный результат сортировки? What do I do next?
[18 Nov 2019 19:19]
Andrei Mart
what I have to do next?
[18 Nov 2019 21:12]
Andrei Mart
Please note that according to the reference manual document 5.7 https://dev.mysql.com/doc/refman/5.7/en/union.html using the following type of structure (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10); is pointless according to your email dated 18 november,13:28 because as you have stated the sorting results are destroyed by the "distinct" filter. What is the point of sorting nodes the way it is described in the document if without using "UNION ALL" the sorting results will be destroyed by the "distinct" filter. But as you can see even when "UNION ALL" is used the nodes sorting still does not work.
[19 Nov 2019 13:26]
MySQL Verification Team
What do you have to do next ???? Nothing !!! When bug is verified, it is transferred to the Development team, which will make decision on the scheduling of the bug fix. We are not involved in the process, so we can not tell you when is scheduling done and which version / release will get a fix. We simply do not have access to that process, as it is internal matter within Development.