Bug #87835 the combination of SELECT DISTINCT and ORDER BY give wrong result for some data

• Submitted: 22 Sep 2017 8:19
• Modified: 22 Sep 2017 10:27
• Reporter: Lin Lu
• Status: Verified
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 5.7.19
• OS: Ubuntu (Ubuntu 16.04.2 LTS)
• CPU Architecture: Any
• Tags: distinct, order by

[22 Sep 2017 8:19] Lin Lu

Description:
the test data is submitted as a MySQL dump file.

the test sql is
===========================
SELECT
  DISTINCT item.id, user.name AS user_name, user.name2 AS user_name2
FROM
 item INNER JOIN user ON item.user_id     =  user.id
ORDER BY item.id DESC
LIMIT 3
===========================

for 5.7.19, the result returned is actually as item.id ASC, not desc. the results are as follows.
+-------+-----------------+------------------+
| id    | user_name       | user_name2       |
+-------+-----------------+------------------+
| 10000 | user_name_10000 | user_name2_10000 |
| 10001 | user_name_10000 | user_name2_10000 |
| 10002 | user_name_10000 | user_name2_10000 |
+-------+-----------------+------------------+
3 rows in set (0.04 sec)

but for older versions such as 5.5.52 it just works as expected. 

also if remove user_name or user_name2 from SELECT, it just works fine.
only when select all 3 columns( item.id, name, name2 ) give the strange result.

my mysql version info as follows:

--------------
show variables like '%version%'
--------------

+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.19                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1           |
| version                 | 5.7.19-0ubuntu0.16.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+
8 rows in set (0.01 sec)

How to repeat:
just as Description

[22 Sep 2017 8:20] Lin Lu

test data for Bug #87835

Attachment: mysql57_bug.dump.bz2 (application/x-bzip2, text), 21.25 KiB.

[22 Sep 2017 8:23] Lin Lu

also
if you delete even just 1 row from either item or user table, 
everything would works fine.

the problem just happend for the exact test data and sql

[22 Sep 2017 10:27] MySQL Verification Team

Thank you for the bug report and test case. Only 5.7 server version affected by this bug.

[19 Dec 2018 1:33] Jesper wisborg Krogh

Posted by developer:
 
Reproduces in 5.7.24. For me using the included test case, I get the correct result when I change to use MyISAM for the on disk internal temporary tables:

SET GLOBAL internal_tmp_disk_storage_engine = MyISAM;

The test case does not reproduce in 8.0.13 (even with the 5.7 settings for internal_tmp_disk_storage_engine and internal_tmp_mem_storage_engine).

[19 Dec 2018 1:41] Jesper wisborg Krogh

Posted by developer:
 
Another workaround is to use GROUP BY instead of DISTINCT.