P1-[4.0 Bug Hunting]-[Incompatible with MySQL]-No warning for using `group by asc/desc` and wrong results returned

[zhangysh1995]

Bug Report

1. What did you do?

mysql> create table t0 (id int );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t0 values (0), (1), (2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t0 group by id asc;
+------+
| id   |
+------+
|    2 |
|    0 |
|    1 |
+------+
3 rows in set (0.00 sec)

2. What did you expect to see?

mysql> select * from t0 group by id asc;
+------+
| id   |
+------+
|    0 |
|    1 |
|    2 |
+------+
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message
                            |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'GROUP BY with ASC/DESC' is deprecated and will be removed in a future release. Please use GROUP BY ... ORDER BY ... ASC/DESC instead |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3. What did you see instead?

No warnings.

4. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

Reproducible on master branch:

commit 8369ffd500f3fb235d8b584ac4298b2e59d8db55 (HEAD -> master, origin/master, origin/HEAD)
Author: Soup <ilovesoup@gmail.com>
Date:   Tue May 26 15:38:29 2020 +0800

mysql> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: None
Edition: None
Git Commit Hash: None
Git Branch: None
UTC Build Time: None
GoVersion: go1.13.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[zhangysh1995]

It is even more weird on master branch:

mysql> select * from t0 group by id asc;
+------+
| id   |
+------+
|    2 |
|    0 |
|    1 |
+------+
3 rows in set (0.00 sec)

mysql> select * from t0 group by id desc;
+------+
| id   |
+------+
|    1 |
|    0 |
|    2 |
+------+
3 rows in set (0.00 sec)

In MySQL, it produces correct order:

mysql> select * from t0 group by id asc;
+------+
| id   |
+------+
|    0 |
|    1 |
|    2 |
+------+
3 rows in set, 1 warning (0.00 sec)

mysql> select * from t0 group by id desc;
+------+
| id   |
+------+
|    2 |
|    1 |
|    0 |
+------+
3 rows in set, 1 warning (0.00 sec)

[shuke987]

/bug P1