MySQL Bugs: #67580: group_concat() limited to 64kb on compress()'ed fields when order by is used

Bug #67580	group_concat() limited to 64kb on compress()'ed fields when order by is used
Submitted:	13 Nov 2012 21:57	Modified:	13 Feb 2018 22:53
Reporter:	Aleksander Kamenik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.28, 5.5.30, 5.1.67, 5.0.97	OS:	Linux (sles11)
Assigned to:		CPU Architecture:	Any
Tags:	64, compress(), group_concat(), limit, order by, uncompress()

Description:
When using group_concat() to add together compress()ed fields which are ordered, the result is limited to 64kb.

As if the result is cast to a blob, even though the fields added may be mediumblobs.

Removing compress() and uncompress() from the SELECT fixes this. You only get the 64kb limit when using group_concat() + uncompress() + ORDER BY.

We saw the same problem with mysql 5.0.96 and on InnoDB. 32bit as well as 64bit linux/mysql. We have a workaround with subqueries, though that's not really a proper solution.

How to repeat:
First create random file to fill a mediumblob field later:
> dd if=/dev/urandom of=/tmp/random.1mb count=1024 bs=1024

Execute following SQL script to create test table, load one row and query twice, with and without ORDER BY.

max_allowed_packet and group_concat_max_len are set to 1GB, as they would otherwise be the limit for group_concat().

#### script start ####
SET GLOBAL max_allowed_packet=1073741824;
SET group_concat_max_len=1073741824;

DROP TABLE IF EXISTS test_concat;

CREATE TABLE `test_concat` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `test_blob` mediumblob NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

INSERT INTO test_concat (test_blob) VALUES (compress(load_file('/tmp/random.1mb')));

SELECT GROUP_CONCAT(uncompress(test_blob) ORDER BY id SEPARATOR '') FROM test_concat WHERE id=1 INTO OUTFILE '/tmp/result1';
SELECT GROUP_CONCAT(uncompress(test_blob) SEPARATOR '') FROM test_concat WHERE id=1 INTO OUTFILE '/tmp/result2';
#### EOF ####

You'll get this:
> ll /tmp/result?
-rw-rw-rw- 1 mysql mysql   66564 Nov 13 23:33 /tmp/result1
-rw-rw-rw- 1 mysql mysql 1065078 Nov 13 23:33 /tmp/result2

But both should be the same bigger size. The first ~64kb are identical.

Thank you for the report.

This was fixed in version 5.6. Please upgrade.

1. So you don't fix the current stable version (5.5)? I don't think upgrading to a development version in production is such a good idea.

2. Why is status set to "Can't repeat" if you acknowledge the bug and say it's fixed in 5.6?

Can't the fix be backported?

There is no bug fix for this, but we introduced many new optimizer features in version 5.6. I believe fix of this bug is result of these improvements.

I can set this bug to "Verified in 5.5 and earlier versions", although I am not sure if these improvements could be easily backported.

But if you have Oracle support contract you can open a SR and request backport.

Posted by developer:
 
Fixed in 5.6, according to comments.