Bug #67580 group_concat() limited to 64kb on compress()'ed fields when order by is used
Submitted: 13 Nov 2012 21:57 Modified: 30 Nov 2012 12:40
Reporter: Aleksander Kamenik Email Updates:
Status: Verified 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:
Tags: 64, compress(), group_concat(), limit, order by, uncompress()
Triage: Needs Triage: D3 (Medium)

[13 Nov 2012 21:57] Aleksander Kamenik
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.
[29 Nov 2012 19:18] Sveta Smirnova
Thank you for the report.

This was fixed in version 5.6. Please upgrade.
[30 Nov 2012 11:48] Aleksander Kamenik
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?
[30 Nov 2012 12:40] Sveta Smirnova
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.