MySQL Bugs: #54443: group_concat() silently truncates my result

Bug #54443	group_concat() silently truncates my result
Submitted:	11 Jun 2010 19:35	Modified:	11 Jun 2010 21:00
Reporter:	Christopher Dickinson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	group_concat, group_concat_max_len

Description:
Hello,
I was struggling with a bug in our php application today that was caused by the default setting (1024) for group_concat_max_len. If group_concat_max_len is not set, mysql will silently cut away whatever surpasses the memory limit defined by default. If we were to compare this to php, it would be as is php when reaching the memory limit defined for scripts in php.ini would stop in the middle of what was being executed (like filling an array, for example), and just return whatever it had been able to calculate until that point. Well, thank God php doesn't do that! In stead, it gives you an error message that helps you understand that what you are trying to execute needs more memory than currently allowed. This is sensible behavior and saves the programmer lots of time wasted on debugging data falsified by the machine. MySQL could save hundreds of thousands of accumulated hours of useless wasted debugging time by adopting the same behavior for its own memory limits - in stead of silently falsifying the result of a query, and yet keeping it long enough to make it hard to detect that something's missing (I was storing/caching ID's for future query performance based on the cached values).

How to repeat:
Confirm that group_concat_max_len is not set or is set to 1024 bytes/chars.

Perform a query similar to this one on a joined table that would normally return more than 1024 bytes/chars in the group_concatenated string:

select parent.parent, CONVERT(group_concat(child.child_id ORDER BY 1) USING utf8)
from child
join parent ON parent.parent_id = child.fk_parent_id
group by parent.parent_id

confirm that the group_concatenated string is chopped off after 1024 chars.

Suggested fix:
Generate a warning when the maximum is reached. You will help the programmer quickly identify what is wrong in stead of silently letting him trust the result received and used by the application.

it should generate a warning like this;

mysql> select group_concat(a) from t1;
+-----------------+
| group_concat(a) |
+-----------------+
| aaaa            |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1260 | 1 line(s) were cut by GROUP_CONCAT() |
+---------+------+--------------------------------------+
1 row in set (0.00 sec)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please check warnings in PHP application.

I had the same problem and it took me about 3 hours to debug.  This is mostly because I was looking for a fault in my own code and not external code like MySql.  An exception or warning would be useful here.