MySQL Bugs: #41090: ORDER BY truncates GROUP

Bug #41090	ORDER BY truncates GROUP_CONCAT result
Submitted:	28 Nov 2008 12:17	Modified:	16 Oct 2011 22:39
Reporter:	Jesper Hermansen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.41-community-nt, 5.0.70, 5.1.30, 6.0.7	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	group_concat, order by

Description:
When doing a group_concat and sorting all rows, some of the content is removed from the concated string.

How to repeat:
A simple select without any tables will do:
SELECT id, LENGTH(GROUP_CONCAT(body)) AS l FROM
(SELECT
  'a' AS id,
  REPEAT('foo bar', 100) AS body
UNION ALL
SELECT
  'a' AS id,
  REPEAT('bla bla', 100) AS body
UNION ALL
SELECT
  'b' AS id,
  'last' AS body) t1
GROUP BY
  id
ORDER BY l DESC;

This gives me:
id,  l
=========
'a', 341
'b', 4

Now remove the ORDER BY:
SELECT id, LENGTH(GROUP_CONCAT(body)) AS l FROM
(SELECT
  'a' AS id,
  repeat('foo bar', 100) AS body
UNION ALL
SELECT
  'a' AS id,
  repeat('bla bla', 100) AS body
UNION ALL
SELECT
  'b' AS id,
  'last' AS body) t1
GROUP BY
  id;

id,  l
=========
'a', 1024
'b', 4

Which is correct because of the group_concat_max_len variable.

Thank you for the bug report. Could you please try with latest released version.
Thanks in advance.

    ->   REPEAT('bla bla', 100) AS body
    -> UNION ALL
    -> SELECT
    ->   'b' AS id,
    ->   'last' AS body) t1
    -> GROUP BY
    ->   id
    -> ORDER BY l DESC;
+----+------+
| id | l    |
+----+------+
| a  | 1024 |
| b  |    4 |
+----+------+
2 rows in set, 1 warning (0.20 sec)

mysql 5.0 > SELECT id, LENGTH(GROUP_CONCAT(body)) AS l FROM
    -> (SELECT
    ->   'a' AS id,
    ->   repeat('foo bar', 100) AS body
    -> UNION ALL
    -> SELECT
    ->   'a' AS id,
    ->   repeat('bla bla', 100) AS body
    -> UNION ALL
    -> SELECT
    ->   'b' AS id,
    ->   'last' AS body) t1
    -> GROUP BY
    ->   id;
+----+------+
| id | l    |
+----+------+
| a  | 1024 |
| b  |    4 |
+----+------+
2 rows in set, 1 warning (0.02 sec)

mysql 5.0 > show variables like "%version%"
    -> ;
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.0.74-nt-debug-log |
| version_comment         | Source distribution |
| version_compile_machine | ia32                |
| version_compile_os      | Win32               |
+-------------------------+---------------------+
5 rows in set (0.03 sec)

Verified just as described:

mysql> SELECT id, LENGTH(GROUP_CONCAT(body)) AS l FROM
    -> (SELECT
    ->   'a' AS id,
    ->   REPEAT('foo bar', 100) AS body
    -> UNION ALL
    -> SELECT
    ->   'a' AS id,
    ->   REPEAT('bla bla', 100) AS body
    -> UNION ALL
    -> SELECT
    ->   'b' AS id,
    ->   'last' AS body) t1
    -> GROUP BY
    ->   id
    -> ORDER BY l DESC;
+----+------+
| id | l    |
+----+------+
| a  |  341 |
| b  |    4 |
+----+------+
2 rows in set, 1 warning (0.31 sec)

mysql> SELECT id, LENGTH(GROUP_CONCAT(body)) AS l FROM
    -> (SELECT
    ->   'a' AS id,
    ->   repeat('foo bar', 100) AS body
    -> UNION ALL
    -> SELECT
    ->   'a' AS id,
    ->   repeat('bla bla', 100) AS body
    -> UNION ALL
    -> SELECT
    ->   'b' AS id,
    ->   'last' AS body) t1
    -> GROUP BY
    ->   id;
+----+------+
| id | l    |
+----+------+
| a  | 1024 |
| b  |    4 |
+----+------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1260
Message: 1 line(s) were cut by GROUP_CONCAT()
1 row in set (0.01 sec)

mysql> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 5.0.70-enterprise-gpl-nt-log |
+------------------------------+
1 row in set (0.00 sec)

mysql> show session variables like 'group_con%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 1024  |
+----------------------+-------+
1 row in set (0.06 sec)

EXPLAIN shows that temporary table is used in the frist case. This is the only difference.

Same results with 5.1.30 and 6.0.7.

I don't know if this is any help, but when running the query from commandline, I get the correct result:
mysql> SELECT id, LENGTH(GROUP_CONCAT(body)) AS l FROM
    -> (SELECT
    ->   'a' AS id,
    ->   REPEAT('foo bar', 100) AS body
    -> UNION ALL
    -> SELECT
    ->   'a' AS id,
    ->   REPEAT('bla bla', 100) AS body
    -> UNION ALL
    -> SELECT
    ->   'b' AS id,
    ->   'last' AS body) t1
    -> GROUP BY
    ->   id
    -> ORDER BY l DESC;
+----+------+
| id | l    |
+----+------+
| a  | 1024 |
| b  |    4 |
+----+------+
2 rows in set, 1 warning (0.00 sec)

mysql> show variables like '%version%';
+-------------------------+-------------------------------+
| Variable_name           | Value                         |
+-------------------------+-------------------------------+
| protocol_version        | 10                            |
| version                 | 5.0.67-community-nt           |
| version_comment         | MySQL Community Edition (GPL) |
| version_compile_machine | ia32                          |
| version_compile_os      | Win32                         |
+-------------------------+-------------------------------+
5 rows in set (0.00 sec)

When running from MySQL Query Browser, the result is:
+----+-----+
| id | l   |
+----+-----+
| a  | 341 |
| b  |   4 |
+----+-----+

Noted in 5.5.18, 5.6.4 changelogs.

A query that selected a GROUP_CONCAT() function result could return
different values depending on whether an ORDER BY of the function
result was present.