TiDB group_concat bug: order by desc substatement don't work

[vkingnew]

TiDB Version:
mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v2.1.5-1-g0bd6b1b
Git Commit Hash: 0bd6b1b
Git Branch: release-2.1
UTC Build Time: 2019-02-28 08:17:03
GoVersion: go version go1.11.2 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.00 sec)

create table t(id bigint not null auto_increment primary key,country varchar(20),city_id int,city_name varchar(20));
insert into t(province,city_id,city_name)values('china',0,'wuhan'),('china',1,'xiangyang'),('china',2,'yicang'),('china',3,'shenzhen'),('china',4,'beijing');

statement1:rigth.
mysql> select country,group_concat(city_id) IDS,group_concat(city_name) NAMES from t group by country\G
*************************** 1. row ***************************
country: china
IDS: 0,1,2,3,4
NAMES: wuhan,xiangyang,yicang,shenzhen,beijing
1 row in set (0.02 sec)

statement 2:error
mysql> select country,group_concat(city_id order by city_id) IDS,group_concat(city_name order by city_id desc) NAMES from t group by country\G
*************************** 1. row ***************************
country: china
IDS: 0,1,2,3,4
NAMES: wuhan,xiangyang,yicang,shenzhen,beijing
1 row in set (0.02 sec)

statement 3:error
mysql> select country,group_concat(city_id order by city_id desc)IDS,group_concat(city_name order by city_id desc) NAMES from t group by country\G
*************************** 1. row ***************************
country: china
IDS: 0,1,2,3,4
NAMES: wuhan,xiangyang,yicang,shenzhen,beijing
1 row in set (1.27 sec)

MySQL 5.7.24 run statement2 and statement 3:
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.24-log |
+------------+
1 row in set (0.00 sec)

mysql> select country,group_concat(city_id order by city_id) IDS,group_concat(city_name order by city_id desc) NAMES from t group by country\G
*************************** 1. row ***************************
country: china
IDS: 0,1,2,3,4
NAMES: beijing,shenzhen,yicang,xiangyang,wuhan
1 row in set (0.00 sec)

[XuHuaiyu]

Hi, @vkingnew
This is a known problem, TiDB may not support it in near future.

[XuHuaiyu]

duplicate with #6838

[vkingnew]

ok，it can implements by substatement like this ：：

select group_concat(city_id SEPARATOR '--') city_id from (select * from t order by city_id desc) t;
+---------------+
| city_id |
+---------------+
| 4--3--2--1--0 |
+---------------+

[XuHuaiyu]

It's not safe.
The order cannot be promised when the size of the input data is large enough.

[vkingnew]

yes,the performance is a questions when have to much data.
Do you have a plan to implements the functions like MySQL in the future?

[XuHuaiyu]

Of course, we'll support it.

[zz-jason]

Hi @vkingnew, I'm going to close this issue since it's duplicated with #6838. You can watch that issue to get the progress on this feature. Feel free to ask if you encountered any other problem.