You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I found that TiDB already supports the json_arrayagg function, which can replace the old group_concat function in simple scenarios. But for scenes with sorting requirements, the json_arrayagg function cannot be satisfied.
CREATETABLEtest (
id INT
);
INSERT INTO test (id) VALUES (3);
INSERT INTO test (id) VALUES (1);
INSERT INTO test (id) VALUES (2);
Use group_concat with order by
select group_concat(id order by id asc) as str from test group by id div 2;
+------+
| str |
+------+
| 2,3 |
| 1 |
+------+
TiDB json_arrayagg
select json_arrayagg(id order by id asc) as arr from test group by id div 2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line1 column 29 near "order by id asc) as arr from test group by id div 2 "
without order by:
select json_arrayagg(id) as arr from test group by id div 2 ;
+--------+
| arr |
+--------+
| [1] |
| [3, 2] |
+--------+--the order of the results is wrong
MariaDB 10.5
select json_arrayagg(id order by id asc) as arr from test group by id div 2;
+------+
| arr |
+------+
| [2,3] |
| [1] |
+------+
The text was updated successfully, but these errors were encountered:
I believe that TiDB is trying to be compatible with MySQL, not MariaDB?
As I was doing some investigation on implementing the missing JSON_VALUE built-in function for TiDB, I noticed there's no formal way for TiDB to handle optional clauses in function parameters at both parser and AST level. In your case, neither MySQL 8.0 or TiDB cares about the optional ORDER BY clauses for JSON_ARRAYAGG at parser level. MariaDB, Oracle and DB2 support that, but the same thing just doesn't hold true for MySQL or TiDB.
It might not be the most ideal thing to say, but given that to support this feature means to find a normalized way to build AST of function parameters with clauses, and the current TiDB behavior IS aligned with MySQL 8.0, perhaps the better way is to push MySQL a little bit?
Feature Request
I found that TiDB already supports the json_arrayagg function, which can replace the old
group_concat
function in simple scenarios. But for scenes with sorting requirements, thejson_arrayagg
function cannot be satisfied.Use group_concat with order by
TiDB json_arrayagg
without order by:
MariaDB 10.5
The text was updated successfully, but these errors were encountered: