Bug #88814 wrong distinct + order by behavior in 5.7

• Submitted: 7 Dec 2017 13:11
• Modified: 31 Dec 2017 0:12
• Reporter: Ioannis Cherouvim
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.7.18
• OS: Ubuntu (16 LTS)
• CPU Architecture: Any

[7 Dec 2017 13:11] Ioannis Cherouvim

Description:
For the supplied database dump (see steps to repro) the query with distinct + order by (see steps to repro) returns wrong order:
nid     title   uid     uid     name
1               1       1       joe
2               1       1       joe
3               1       1       joe
4               1       1       joe
5               1       1       joe

If I do any of the following, the order turns back to correct:
- remove title from the db
- remove title from query
- change title size from title varchar(196) to title varchar(195)

How to repeat:
Save https://pastebin.com/raw/7iG1rNuz (mysqldump) locally and execute:

echo "create database test123" | mysql -u root -p
mysql -u root -p test123 < /vagrant/db/database.sql
echo "select distinct * from node left join users on node.uid = users.uid order by node.nid desc limit 5" | mysql -u root -p test123

[7 Dec 2017 13:12] Ioannis Cherouvim

database.sql

Attachment: database.sql (application/octet-stream, text), 254.90 KiB.

[7 Dec 2017 13:16] Ioannis Cherouvim

my.cnf

Attachment: my.cnf (application/octet-stream, text), 1.05 KiB.

[7 Dec 2017 21:23] MySQL Verification Team

Thank you for the bug report. Please print here your real result and the expected one. Thanks.

[7 Dec 2017 22:21] MySQL Verification Team

See https://bugs.mysql.com/bug.php?id=33087. Thanks.

[18 Dec 2017 8:44] Ioannis Cherouvim

Returns:

nid     title   uid     uid     name
1               1       1       joe
2               1       1       joe
3               1       1       joe
4               1       1       joe
5               1       1       joe

Expected:

nid     title   uid     uid     name
56215           1       1       joe
56214           1       1       joe
56213           1       1       joe
56212           1       1       joe
56211           1       1       joe

[31 Dec 2017 0:12] MySQL Verification Team

Thank you for the feedback. Dup0liacte of already mentioned bug: https://bugs.mysql.com/bug.php?id=33087. Thanks.