Bug #104773 Ordering by null values of group_concat is unexpected
Submitted: 31 Aug 2021 3:21 Modified: 2 Sep 2021 6:20
Reporter: river fang Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: bug, group_concat, null, order by

[31 Aug 2021 3:21] river fang
`When using ORDER BY, NULL values are presented first, or last if you specify DESC to sort in descending order.
from https://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html#:~:text=When%20using%20ORD.... 

however, the order_by null values are not the minimal values as the following example.

How to repeat:
use test;
	col_int int,
	col_year int,
    col_str char(4)

INSERT INTO c VALUES (1, null,null);
INSERT INTO c VALUES (2, 1,'1');
INSERT INTO c VALUES (3, -1,'-1');

SELECT group_concat(col_int order by col_year) FROM c;
mysql:    3,4,1,2 [order: -1,0,null,1]
expected: 1,3,4,2 [order:null,-1,0,1]

why mysql does not take `null` as the minimal value as the reference https://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html#:~:text=When%20using%20ORD....   ?
[31 Aug 2021 13:41] MySQL Verification Team
Hi Mr. fang,

Thank you for your bug report.

The text that you have so nicely quoted to us is for the ordering of the entire result set. It does not apply to group_concat() function at all. 

This feature was not designed for the ordering that is like the one that sorts entire result set.

However, we find your report to be a very good feature request. Why not make the same behaviour for the group_concat() as well. ......

Verified as a feature request ....
[2 Sep 2021 6:20] river fang
so what are the sorting rules for group_concat?
[2 Sep 2021 11:58] MySQL Verification Team
Those are dependent on the types used, of course. NULL is not handled in the same manner as in sorting rows.

This is so because group_concat() is not covered by any SQL standard, so we can determine sorting order in a manner that suits us best. This is one of the reasons for which your report is a feature request.