Bug #100822 the result of group_concat(field1 order by field2) is unexpect.
Submitted: 12 Sep 2020 14:39 Modified: 24 Apr 1:42
Reporter: GONGTUI FU Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0/5.7/5.6 OS:Red Hat (7.4)
Assigned to: CPU Architecture:Any
Tags: empty string, group_concat, null, order by

[12 Sep 2020 14:39] GONGTUI FU
Hi, guys:
I might find out a bug about GROUP_CONCAT function.
Assume a field named field2 include NULL and empty string, when GROUP_CONCAT function use ORDER BY field2 clause, the result is unexpect.
MySQL takes NULL as the minimum value, NULL is less than empty string, So NULL should be in front. However GROUP_CONCAT takes NULL and empty string as the same,this results in an incorrect result set.

How to repeat:
create table t (id int, name varchar(10));
insert into t values (1, ''), (2, NULL), (3, ''), (4, NULL);

select * from t order by name;
|  id  | name |
|    2 | NULL | 
|    4 | NULL | 
|    1 |      |
|    3 |      |

select group_concat(id order by name) as field1 from t;
|           field1         |
| 4,3,2,1                  |

I think it should be 2,4,1,3

Suggested fix:
I debug source code about group_concat function that sorts using temporary tables and red black trees, it takes NULL and empty string as the same.
I think they are different.Can you distinguish them?
[12 Sep 2020 16:24] MySQL Verification Team
Thank you for the bug report.
[24 Apr 1:36] GONGTUI FU
fix group_concat order by field that include NULL and empty string bug

Attachment: mysql-bug-prefix-100822.txt (text/plain), 941 bytes.

[24 Apr 1:42] GONGTUI FU
This bug(100822) reported at Sep 2020 doesn't be fixed in 8.0.24 version, I fix it.