Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.8
-
None
-
None
-
CentOS 7.9, Docker
Description
The ORDER BY clause in the GROUP_CONCAT function allows you to specify the order, but if you add DISTINCT, the order will not be as expected.
MariaDB [relief]> select version();
|
+-------------------------------------+
|
| version() |
|
+-------------------------------------+
|
| 10.5.8-MariaDB-1:10.5.8+maria~focal |
|
+-------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [relief]> CREATE OR REPLACE TABLE table1 ( id int auto_increment primary key, kind text, year int, ranking int );
|
Query OK, 0 rows affected (0.014 sec)
|
|
|
MariaDB [relief]> INSERT INTO table1 (kind, year, ranking) VALUES ('a', 2019, 1), ('a', 2018, 2), ('a', 2017, 1);
|
Query OK, 3 rows affected (0.007 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [relief]> INSERT INTO table1 (kind, year, ranking) VALUES ('b', 2019, 6)
|
-> ;
|
Query OK, 1 row affected (0.001 sec)
|
|
|
MariaDB [relief]> SELECT * FROM table1;
|
+----+------+------+---------+
|
| id | kind | year | ranking |
|
+----+------+------+---------+
|
| 1 | a | 2019 | 1 |
|
| 2 | a | 2018 | 2 |
|
| 3 | a | 2017 | 1 |
|
| 4 | b | 2019 | 6 |
|
+----+------+------+---------+
|
4 rows in set (0.000 sec)
|
|
Here, I concatted the `ranking` in the order of `year`.
MariaDB [relief]> SELECT GROUP_CONCAT(ranking ORDER BY year) FROM table1 GROUP BY kind;
|
+-------------------------------------+
|
| GROUP_CONCAT(ranking ORDER BY year) |
|
+-------------------------------------+
|
| 1,2,1 |
|
| 6 |
|
+-------------------------------------+
|
2 rows in set (0.001 sec)
|
|
|
MariaDB [relief]>
|
Then I added DISTINCT.
The expected result is '1,2'.
MariaDB [relief]> SELECT GROUP_CONCAT(DISTINCT ranking ORDER BY year) FROM table1 GROUP BY kind;
|
+----------------------------------------------+
|
| GROUP_CONCAT(DISTINCT ranking ORDER BY year) |
|
+----------------------------------------------+
|
| 2,1 |
|
| 6 |
|
+----------------------------------------------+
|
2 rows in set (0.001 sec)
|
|
|
MariaDB [relief]>
|
I tried the same thing with MySQL 5.7, and got the same result.
Is it possible that the order is not kept when DISTINCT is added to the GROUP_CONCAT function?