| Bug #99257 | Inconsistent output and wrong ORDER BY Sorting for query with LIMIT | ||
|---|---|---|---|
| Submitted: | 14 Apr 2020 14:25 | Modified: | 15 Apr 2020 13:03 |
| Reporter: | lalit Choudhary | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.7.29 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[14 Apr 2020 14:25]
lalit Choudhary
[14 Apr 2020 14:27]
lalit Choudhary
data_set file
Attachment: data_set.tar.xz (application/x-xz, text), 2.86 MiB.
[15 Apr 2020 13:03]
MySQL Verification Team
Hi Mr. Choudhary, Thank you for your bug report. I have repeated your results with latest 5.7: +-----------+-------------------+ | order_id | external_order_id | +-----------+-------------------+ | 555000000 | 671263031366 | | 555000001 | 709922095177 | | 555000002 | 709949980745 | | 555000003 | 710472335433 | | 555000004 | 650890018872 | +-----------+-------------------+ I have also managed to repeat the behaviour in the latest 8.0: +-----------+-------------------+ | order_id | external_order_id | +-----------+-------------------+ | 555292700 | 2004823834659 | | 555292699 | 2087842381870 | | 555292698 | 2168547836040 | | 555292697 | NULL | | 555292696 | NULL | +-----------+-------------------+ I have analysed your data and I am sure that 8.0 behaviour is 100 % correct. Hence, I am verifying this bug as a 5.7-only bug. Thank you for your contribution.
[15 Apr 2020 16:40]
Valeriy Kravchuk
This is a regression in (some 5.7.x, as older MySQL 5.6.27, for example, also gives correct results (at least the same as 8.0.x): openxs@ao756:~/dbs/5.6$ bin/mysql -uroot --socket=/tmp/mysql56.sock test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.27 MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT DISTINCT t1.order_id, t1.external_order_id FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0; +-----------+-------------------+ | order_id | external_order_id | +-----------+-------------------+ | 555292700 | 2004823834659 | | 555292699 | 2087842381870 | | 555292698 | 2168547836040 | | 555292697 | NULL | | 555292696 | NULL | +-----------+-------------------+ 5 rows in set (8,33 sec)
[16 Apr 2020 12:08]
MySQL Verification Team
Thank you, Valeriy .... This will be marked as a regression bug .....
[28 Apr 2021 9:41]
Steffen Böhme
Any news if some when will be worked on this bug? We have at least 2 cluster instances were we cannot do a upgrade to latest 5.7 minor nor to latest 8.0 version because this bug still exists and its not worked on ...!?
[28 Apr 2021 12:36]
MySQL Verification Team
Hi , Just for your information, our team is just processing the bug. We are not involved nor informed in the scheduling of the bug fixing. If you wish to do something regard the priority, it might be helpful to check whether latest 5.7 and 8.0 releases are still plagued by this bug.
[28 Apr 2021 14:43]
Steffen Böhme
Well, I was able to reproduce with the latest version of 5.7 like shown here: root@m974:~# echo "SELECT @@version; SELECT DISTINCT t1.order_id, t1.external_order_id FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0;" | docker exec -i mysql5.7.34 mysql -u root -pspr34dsh testdb mysql: [Warning] Using a password on the command line interface can be insecure. @@version 5.7.34 order_id external_order_id 555000000 671263031366 555000001 709922095177 555000002 709949980745 555000003 710472335433 555000004 650890018872 root@m974: For the 8.0 serie it was my fault, I had wrong in mind, that the bug was even in this release, but it showed, that this works correct in the latest 8.0 release: root@m974:~# echo "SELECT @@version; SELECT DISTINCT t1.order_id, t1.external_order_id FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0;" | docker exec -i mysql8.0.24 mysql -u root -pspr34dsh testdb mysql: [Warning] Using a password on the command line interface can be insecure. @@version 8.0.24 order_id external_order_id 555292700 2004823834659 555292699 2087842381870 555292698 2168547836040 555292697 NULL 555292696 NULL root@m974: So it stays within 5.7.34 and should still get fixed in my opinion.
[28 Apr 2021 14:47]
Steffen Böhme
What I wondered about is, that there is still no assignee for the issue, especially since it is a verified bug ... maybe that is usual for this bug process ... Btw. Would you be so kind and remove the -p values in the last post? Did not notice that I had it in the comment still and would like to get rid of it out of the post ...
[29 Apr 2021 13:11]
MySQL Verification Team
Hi Mr. Bohme, We wrote before that once a bug is verified , it is out of our hands. Hence, all progress (if any) on any verified bug is only recorded in our internal system. Here, after "Verified", you can only see "Closed" or "Not a bug", without any further info.
[30 Apr 2021 7:00]
Steffen Böhme
Hi MySQL Verification Team, I got it that the bug is out of your hand after verification, but I did not realized, that once a bug is verified one will not see any more information about if some when somebody will work on this bug, or if it never will be closed, if there is some progress, if one can hope, the bug will be fixed to fix the bug related problems which lead to reporting this bug ... "Here, after "Verified", you can only see "Closed" or "Not a bug", without any further info." Seems I'm a bit naive if I assume, that I would get further info about a reported and verified bug and its progress ... I did not really take in account, that with verifying the bug will dive up into a internal system and no information will come up about the work on it. For real, this is not understandable for me, since it's the upper bound of intransparency ... and I, as I said, was really kind of naiv and expected to see some kind of progress here. But if this is your workflow then it is like this ... Or is there any other possibility to find the problem/bug elsewhere and subscribe to it or see the current stand?
