Bug #78536 group_concat returns incorrect result
Submitted: 24 Sep 2015 6:50 Modified: 8 May 2018 13:11
Reporter: Su Dylan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.6.22, 5.7.8 OS:Any
Assigned to: CPU Architecture:Any

[24 Sep 2015 6:50] Su Dylan
Description:
Output:
=======
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(c1 int,c2 int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1,NULL),(-2,-2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+------+
| c1   | c2   |
+------+------+
|    1 | NULL |
|   -2 |   -2 |
+------+------+
2 rows in set (0.00 sec)

mysql> select c1 from t1 order by c2;
+------+
| c1   |
+------+
|    1 |
|   -2 |
+------+
2 rows in set (0.00 sec)

mysql> select group_concat( c1 order by c2) from t1;
+-------------------------------+
| group_concat( c1 order by c2) |
+-------------------------------+
| -2,1                          |
+-------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.22-log |
+------------+
1 row in set (0.00 sec)

Problem:
"1,-2" should be returned by group_concat.

How to repeat:
drop table if exists t1;
create table t1(c1 int,c2 int);
insert into t1 values(1,NULL),(-2,-2);
select * from t1;
select c1 from t1 order by c2;
select group_concat( c1 order by c2) from t1;

Suggested fix:
"1,-2" should be returned by group_concat.
[8 Oct 2015 15:34] Sinisa Milivojevic
Thank you for reporting this issue, but it is not a bug.

You can not predict the comparison of NULL with any other value, including another NULL. This is also very nicely explained in our manual, specifically in chapter "C". 

Not a bug.
[9 Oct 2015 9:03] Su Dylan
Hi,

From the menual,
https://dev.mysql.com/doc/refman/5.6/en/working-with-null.html

the following words are presented:
=========
When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.
=========

From this description, the order by result with NULL and non-NULL value is defined and fixed.
[19 Oct 2015 7:26] Su Dylan
According to the document, this problem is a serious bug. Therefore this issue is reopened. It is also recreated with 5.7.8.

Please help to double check with development team if this is really "working as design".

=====
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t1(c1 int,c2 int);
er by c2;
select group_concat( c1 order by c2) from t1;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t1 values(1,NULL),(-2,-2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+------+
| c1   | c2   |
+------+------+
|    1 | NULL |
|   -2 |   -2 |
+------+------+
2 rows in set (0.01 sec)

mysql> select c1 from t1 order by c2;
+------+
| c1   |
+------+
|    1 |
|   -2 |
+------+
2 rows in set (0.00 sec)

mysql> select group_concat( c1 order by c2) from t1;
+-------------------------------+
| group_concat( c1 order by c2) |
+-------------------------------+
| -2,1                          |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)
[8 Jan 2016 11:19] Su Dylan
Is there any progress on this issue?

Obviously, the outer "order by c2" after from clause the the order by in group_concat give different sequence of data.
[8 May 2018 13:11] Sinisa Milivojevic
HI,

I still can not see where is the problem. As the default for the ORDER BY is ascending, NULLs come first, which is exactly how it works in your query.