MySQL Bugs: #104773: Ordering by null values of group

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:	None
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

Description:
`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:
CREATE DATABASE fzh;
use test;
CREATE TABLE c (
	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');
INSERT INTO c VALUES (4, 0,'');

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....   ?

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 ....

so what are the sorting rules for group_concat?

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.