Bug #98217 the "order by desc" option does not work

• Submitted: 14 Jan 2020 8:41
• Modified: 28 Jan 2020 4:24
• Reporter: shangshang yu
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.19, 8.0.17, 8.0.18
• OS: CentOS
• CPU Architecture: x86
• Tags: regression

[14 Jan 2020 8:41] shangshang yu

Description:
see how to repeat

How to repeat:
mysql> create table t1(a int, b int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(2,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(3,3);
Query OK, 1 row affected (0.00 sec)

mysql> select distinct a from t1 group by a,b order by a desc;
+------+
| a    |
+------+
|    3 |
|    2 |
|    1 |
+------+
3 rows in set (0.00 sec)

mysql> select distinct sql_big_result a from t1 group by a,b order by a desc;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

[14 Jan 2020 9:18] MySQL Verification Team

Hello Shangshang yu,

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

regards,
Umesh

[28 Jan 2020 4:24] Jon Stephens

Documented fix as follows in the MySQL 8.0.20 changelog:

    For a query of the form SELECT DISTINCT ... ORDER BY ..., when
    the ORDER BY was pushed down onto the first table in the join,
    the result was not always sorted in the correct order.

Closed.