MySQL Bugs: #78536: group_concat returns incorrect result

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

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.

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.

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.

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)

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.

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.