Bug #14169 ORDER BY changes field type to BLOB for GROUP_CONCAT
Submitted: 20 Oct 2005 9:18 Modified: 14 Apr 2006 18:30
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.13-rc, 5.0.15-BK, 5.0.16-nightly-20051017-debug OS:Linux (Linux, Windows)
Assigned to: Evgeny Potemkin

[20 Oct 2005 9:18] Shane Bester
Description:
When using GROUP_CONCAT() function with group_concat_max_len > 512 then the field type will be BLOB if ORDER BY is used, otherwise it will be VARCHAR.

Thus, the following query col1 will be BLOB:

SELECT name1,GROUP_CONCAT(id1) as col1 FROM t1 GROUP BY name1 ORDER BY id1;

And this col1 will be VARCHAR:

SELECT name1,GROUP_CONCAT(id1) as col1 FROM t1 GROUP BY name1;

How to repeat:
Load the follow SQL to prepare the database:

---
SET GLOBAL group_concat_max_len=1024;

DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id1 INT UNSIGNED, name1 VARCHAR(255)) ENGINE=MyISAM;

INSERT INTO t1(id1,name1) VALUES(1,REPEAT('a',255));
INSERT INTO t1(id1,name1) VALUES(2,REPEAT('b',255));

SELECT name1,GROUP_CONCAT(id1) as col1 FROM t1 GROUP BY name1 ORDER BY id1;
SELECT name1,GROUP_CONCAT(id1) as col1 FROM t1 GROUP BY name1;
---

Then, compile and run the attached .c test case and observe the different field types.

If one does "SET GLOBAL group_concat_max_len=512" or less, then the fields are returned as VARCHAR irrespective of ORDER BY clauses.

Suggested fix:
Not sure.. make the field types consistent.. regardless of whether ORDER BY is used.
[20 Oct 2005 10:25] Valerii Kravchuk
Slightly modified file to verify the bug (versions output added)

Attachment: 14169.c (text/x-csrc), 1.81 KiB.

[20 Oct 2005 10:28] Valerii Kravchuk
Verified (on 5.0.13-rc by Shane) with newer -BK build (ChangeSet@1.2007.4.1, 2005-10-18 18:51:07-07:00, patg@krsna.patg.net) on Linux as described, using the 14169.c file:

[openxs@Fedora 5.0]$ ./14169
Client info: 5.0.15
Server info: 5.0.15

 query A (without ORDER BY)
Row 0
Field Name: name1
Field Value: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaa
Field Type: 253

Field Name: col1
Field Value: 1
Field Type: 253

Row 1
Field Name: name1
Field Value: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbb
Field Type: 253

Field Name: col1
Field Value: 2
Field Type: 253

------
 query B (with ORDER BY)
Row 0
Field Name: name1
Field Value: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaa
Field Type: 253

Field Name: col1
Field Value: 1
Field Type: 252

Row 1
Field Name: name1
Field Value: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbb
Field Type: 253

Field Name: col1
Field Value: 2
Field Type: 252

Finished.
[openxs@Fedora 5.0]$ uname -a
Linux Fedora 2.4.22-1.2115.nptl #1 Wed Oct 29 15:42:51 EST 2003 i686 i686 i386 GNU/Linux
[15 Feb 2006 17:36] Bob Dankert
Is there any progress on this bug?  This is very annoying and causes some issues in some of my software...
[24 Feb 2006 14:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3110
[28 Feb 2006 21:48] Evgeny Potemkin
In a simple queries a result of the GROUP_CONCAT() function was always of 
varchar type.
But if length of GROUP_CONCAT() result is greater than 512 chars and temporary
table is used during select then the result is converted to blob, due to
policy to not to store fields longer than 512 chars in tmp table as varchar
fields.

Fixed in 5.0.19, cset 1.2063.5.1

It should be explicitly noted somewhere in the manual that now GROUP_CONCAT() returns string only if group_concat_max_len <=512. Otherwise it returns blob.
[4 Mar 2006 9:04] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix/behaviour change in 5.0.19 changelog; updated description of GROUP_CONCAT(). Closed.
[12 Apr 2006 19:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4884
[14 Apr 2006 13:58] Evgeny Potemkin
Fixed in 4.1.19
[14 Apr 2006 18:30] Paul Dubois
Made note in 4.1.19 changelog and updated
GROUP_CONCAT() description in 4.1 manual.
[19 Apr 2006 20:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5172
[20 Apr 2006 8:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5192
[20 Apr 2006 9:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5196
[14 Jun 2010 18:40] Paul Dubois
For 4.1, the cutoff is 255 bytes rather than 512 bytes. See sql/unireg.h definition of CONVERT_IF_BIGGER_TO_BLOB.