What is the concept of GROUP BY implicit sorting in MySQL? The main reason is that other RDBMS do not have such a concept. If you have not carefully understood the concept, you will feel a little confused about it. Let's take a look at the introduction of the official document first:

The chapter ". 2.1.14 ORDER BY Optimization" in the official MySQL 5.7 Reference Manual is described as follows:

GROUP BY implicitly sorts by default (that is, in the absence of ASC or DESC designators for GROUP BY columns). However, relying on implicit GROUP BY sorting (that is, sorting in the absence of ASC or DESC designators) or explicit sorting for GROUP BY (that is, by using explicit ASC or DESC designators for GROUP BY columns) is deprecated. To produce a given sort order, provide an ORDER BY clause.

By default, GROUP BY is implicitly sorted (that is, the ASC or DESC indicator for the GROUP BY column is missing). However, it is not recommended to rely on implicit GROUP BY sorting (i.e., sorting without ASC or DESC indicators) or explicit sorting of GROUP BY (i.e., by using explicit ASC or DESC indicators on GROUP BY columns). To generate a given sort ORDER, provide an ORDER BY clause.

Starting from MySQL 8.0, the GROUP BY field no longer supports implicit sorting. The chapter "8.2.1.16 ORDER BY Optimization" in the official document MySQL 8.0 Reference Manual is described as follows:

Previously (MySQL 5.7 and lower), GROUP BY sorted implicitly under certain conditions. In MySQL 8.0, that no longer occurs, so specifying ORDER BY NULL at the end to suppress implicit sorting (as was done previously) is no longer necessary. However, query results may differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.

Let's take a look at MySQL's GROUP BY sorted implicitly. We use the example in the blog "Removal of implicit and explicit sorting for GROUP BY".

The following experimental environment is MySQL 5.6.41 ()

mysql> select version() from dual;
+------------+
| version()  |
+------------+
| 5.6.41-log |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t (id INTEGER,  cnt INTEGER);
Query OK, 0 rows affected (0.04 sec)
 
mysql> INSERT INTO t VALUES (4,1),(3,2),(1,4),(2,2),(1,1),(1,5),(2,6),(2,1),(1,3),(3,4),(4,5),(3,6);
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

MySQL implicitly sorts the results of GROUP BY here (that is, in the absence of the ASC or DESC indicator of the GROUP BY column).

mysql> SELECT id, SUM(cnt) FROM t GROUP BY id; --GROUP BY Implicit sorting
+------+----------+
| id   | SUM(cnt) |
+------+----------+
|    1 |       13 |
|    2 |        9 |
|    3 |       12 |
|    4 |        6 |
+------+----------+
4 rows in set (0.00 sec)

MySQL also supports explicit sorting using GROUP BY (that is, by using an explicit ASC or DESC indicator on the GROUP BY column)

mysql> SELECT id, SUM(cnt) FROM t GROUP BY id DESC;  --GROUP BY Explicit sorting
+------+----------+
| id   | SUM(cnt) |
+------+----------+
|    4 |        6 |
|    3 |       12 |
|    2 |        9 |
|    1 |       13 |
+------+----------+
4 rows in set (0.00 sec)

Since MySQL 8.0, MySQL no longer supports implicit or display sorting of GROUP BY, as shown below:

The following experimental environment is MySQL 8.0.18

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.18    |
+-----------+
1 row in set (0.00 sec)
 
mysql> CREATE TABLE t (id INTEGER,  cnt INTEGER);
Query OK, 0 rows affected (0.39 sec)
 
mysql> INSERT INTO t VALUES (4,1),(3,2),(1,4),(2,2),(1,1),(1,5),(2,6),(2,1),(1,3),(3,4),(4,5),(3,6);
Query OK, 12 rows affected (0.10 sec)
Records: 12  Duplicates: 0  Warnings: 0
 
mysql> SELECT id, SUM(cnt) FROM t GROUP BY id;
+------+----------+
| id   | SUM(cnt) |
+------+----------+
|    4 |        6 |
|    3 |       12 |
|    1 |       13 |
|    2 |        9 |
+------+----------+
4 rows in set (0.00 sec)
 
mysql> SELECT id, SUM(cnt) FROM t GROUP BY id DESC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC' at line 1

As shown above, GROUP BY implicit sorting is not supported. In MySQL 8.0, the above test example is unordered. If GROUP BY displays sorting, an error will be reported directly. Therefore, if a database is migrated and upgraded from MySQL 5.7 or earlier to MySQL 8, you need to pay special attention to this problem. The correct approach should be GROUP BY... ORDER BY. As follows:

mysql>  SELECT id, SUM(cnt) FROM t GROUP BY id  ORDER BY id;
+------+----------+
| id   | SUM(cnt) |
+------+----------+
|    1 |       13 |
|    2 |        9 |
|    3 |       12 |
|    4 |        6 |
+------+----------+
4 rows in set (0.00 sec)

The explicit sorting of GROUP BY is deleted in MySQL 8.0.13. As for why MySQL 8.0 no longer supports implicit sorting and explicit sorting for GROUP BY, this article "Removal of implicit and explicit sorting for GROUP BY" has been introduced in detail, so I won't add more details here.

reference material:
https://mysqlserverteam.com/removal-of-implicit-and-explicit-sorting-for-group-by/