MySQL Bugs: #80215: ORDER BY with GROUP BY no longer working on MySQL 5.7.10

Bug #80215	ORDER BY with GROUP BY no longer working on MySQL 5.7.10
Submitted:	1 Feb 2016 4:33	Modified:	1 Feb 2016 17:07
Reporter:	Thiago Thiago	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.7.10	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any
Tags:	GROUP BY, order by

Description:
I have many cases where I have a list where I need the last record for each group o items. On MySQL 5.5 and 5.6 worked well, but on version 5.7.10 (also teste on 5.7.8) just stop working giving the wrong order result.

How to repeat:
Imagine table "login" having column "id" as auto increment

On version 5.5 give the folowing result:
Server version: 5.5.37-0ubuntu0.12.04.1-log (Ubuntu)
 mysql> SELECT id FROM (SELECT id from login ORDER BY id DESC) tabtemp GROUP BY 1=1;
+------+
| id   |
+------+
| 2030 |
+------+

mysql> explain SELECT id FROM (SELECT id from login ORDER BY id DESC) tabtemp GROUP BY 1=1;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL | 1553 |             |
|  2 | DERIVED     | login      | index | NULL          | PRIMARY | 4       | NULL | 1553 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+

=========================================================-
But on version 5.7.10 the query ignores the ORDER BY

mysql> SELECT tabtemp.id FROM (SELECT id FROM (SELECT id from login ORDER BY id DESC) tabtemp2) tabtemp GROUP BY 1=1;
+----+
| id |
+----+
|  1 |
+----+

mysql> explain SELECT id FROM (SELECT id from login ORDER BY id DESC) tabtemp GROUP BY 1=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | login | NULL       | index | NULL          | PRIMARY | 4       | NULL | 1457 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

Suggested fix:
No idea.

Testing more I realize that the table tabtemp is giving the result on the same order both in MySQL 5.5 and 5.7 if I remove "GROUP BY". So the problem start happen when adding the GROUP BY.

mysql> SELECT id FROM (SELECT id from login ORDER BY id DESC) tabtemp LIMIT 3;
+------+
| id   |
+------+
| 2030 |
| 2029 |
| 2028 |
+------+

Same as Bug #80131

Solved adding GROUP BY together with ORDER BY
 SELECT tabtemp.id FROM (SELECT id FROM (SELECT id from login GROUP BY id ORDER BY id DESC) tabtemp2) tabtemp GROUP BY 1=1;

Hopping devoppers would review that part, seems more people are having the same issue, once they migrate to the new version the software will start giving wrong results. It will take long till realize that something is wrong once it will not give any error or warning.

Ops, ignore last comment. Thats is the right SELECT
SELECT id FROM (SELECT id from login GROUP BY id ORDER BY id DESC) tabtemp GROUP BY 1=1;

Thank you for the bug report. Please see:

https://bugs.mysql.com/bug.php?id=80131