Bug #99257 Inconsistent output and wrong ORDER BY Sorting for query with LIMIT
Submitted: 14 Apr 14:25 Modified: 15 Apr 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 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 14:27] lalit Choudhary
data_set file

Attachment: data_set.tar.xz (application/x-xz, text), 2.86 MiB.

[15 Apr 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 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 12:08] MySQL Verification Team
Thank you, Valeriy ....

This will be marked as a regression bug .....