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:
None 
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
Description:
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;

Above query with ORDER BY and LIMIT resulting into,
1. Inconsistent output between versions
2. Order By sorting is wrong

How to repeat:
Steps to reproduce:

1. Load data_set.sql

2. Run the following query:
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;

Example:
mysql 5.7.29
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 |
+-----------+-------------------+
| 555000000 | 671263031366      |
| 555000001 | 709922095177      |
| 555000002 | 709949980745      |
| 555000003 | 710472335433      |
| 555000004 | 650890018872      |
+-----------+-------------------+
5 rows in set (1.22 sec)

MySQL 8.0.19

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 (0.00 sec)

So here we have 2 issues when using order by with limit,
1. Inconsistent output between versions
2. Order By sorting is wrong, "ORDER BY t1.order_id DESC" results for the  order_id column is  ASC and not DESC.

Suggested fix:
The result should be consistent across versions and ORDER BY should sort in the given order.
[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 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 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 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 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 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 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?