Bug #98222 the "order by" option cause wrong result
Submitted: 14 Jan 2020 12:18 Modified: 4 Feb 2020 14:49
Reporter: shangshang yu Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.0.19, 8.0.17, 8.0.18 OS:CentOS
Assigned to: CPU Architecture:x86
Tags: regression

[14 Jan 2020 12:18] shangshang yu
Description:
see how to repeat

How to repeat:
mysql> create table tt(C_FIRST varchar(64) NOT NULL, C_VCHAR varchar(9000) DEFAULT NULL , C_DATA text);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into tt values("aaa","bbb","ccc");
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt values("aaa","bbb","ccc");
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt values("aaa","bbb","ccc");
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt values("aaa","bbbbbb","ccc");
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt values("aaa","bbbbbb","ccc");
Query OK, 1 row affected (0.00 sec)

mysql> insert into tt values("aaa","bbbbbb","ccc");
Query OK, 1 row affected (0.00 sec)

mysql> select distinct c_first,c_data,length(c_vchar),count(*) from tt group by c_first,c_data,length(c_vchar);
+---------+--------+-----------------+----------+
| c_first | c_data | length(c_vchar) | count(*) |
+---------+--------+-----------------+----------+
| aaa     | ccc    |               3 |        3 |
| aaa     | ccc    |               6 |        3 |
+---------+--------+-----------------+----------+
2 rows in set (0.00 sec)

mysql> select distinct c_first,c_data,length(c_vchar),count(*) from tt group by c_first,c_data,length(c_vchar) order by 1,2,3;
+---------+--------+-----------------+----------+
| c_first | c_data | length(c_vchar) | count(*) |
+---------+--------+-----------------+----------+
| aaa     | ccc    |               3 |        3 |
+---------+--------+-----------------+----------+
1 row in set (0.00 sec)
[14 Jan 2020 12:33] MySQL Verification Team
Hello Shangshang yu,

Thank you for the report.
Verified as described with 8.0.19.

regards,
Umesh
[4 Feb 2020 14:49] Jon Stephens
Fixed in MySQL 8.0.20 together with BUG#97628. See same for more information.

Closed.