MySQL Bugs: #100822: the result of group_concat(field1 order by field2) is unexpect.

Bug #100822	the result of group_concat(field1 order by field2) is unexpect.
Submitted:	12 Sep 2020 14:39	Modified:	24 Apr 2021 1:42
Reporter:	GONGTUI FU	Email Updates:
Status:	Verified	Impact on me:	None
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

Description:
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?

Thank you for the bug report.

fix group_concat order by field that include NULL and empty string bug

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

This bug(100822) reported at Sep 2020 doesn't be fixed in 8.0.24 version, I fix it.

fix group_concat order by field that include NULL and empty string bug

Attachment: group_concat.txt (text/plain), 941 bytes.