P1-[4.0 bug hunting]-[Aggregate Fcuntion]-group_concat incompatible with MySQL

[sre-bot]

Bug Hunter issue tidb-challenge-program/bug-hunting-issue#78

---

Bug Report

1. What did you do?

drop table if exists t1, t2;

create table t1 (a int, b int);
create table t2 (m int, n int);
insert into t1 values (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
insert into t2 values (1,11), (2,22), (3,32), (4,44), (4,44);
set @@sql_mode='TRADITIONAL';

select count(*) c, a,
 ( select group_concat(count(a)) from t2 where m = a )
 from t1 group by a;

2. What did you expect to see?

mysql> select count(*) c, a,
    ->  ( select group_concat(count(a)) from t2 where m = a )
    ->  from t1 group by a;
+---+------+-------------------------------------------------------+
| c | a    | ( select group_concat(count(a)) from t2 where m = a ) |
+---+------+-------------------------------------------------------+
| 2 |    2 | 2                                                     |
| 3 |    3 | 3                                                     |
| 1 |    4 | 1,1                                                   |
+---+------+-------------------------------------------------------+
3 rows in set (0.00 sec)

3. What did you see instead?

mysql> select count(*) c, a,
    ->  ( select group_concat(count(a)) from t2 where m = a )
    ->  from t1 group by a;
ERROR 1111 (HY000): Invalid use of group function

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

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)

Reproducible on master branch:

commit 2efab88a59499ea3934c8c1eba7b695cf24b9d29 (HEAD -> master, origin/master, origin/HEAD)
Author: Wenxuan <hi@breeswish.org>
Date:   Fri May 15 21:53:32 2020 +0800

[dyzsr]

/assign

[dyzsr]

/assign

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions