MySQL Bugs: #14169: ORDER BY changes field type to BLOB for GROUP

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	CPU Architecture:	Any

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.

Slightly modified file to verify the bug (versions output added)

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

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

Is there any progress on this bug?  This is very annoying and causes some issues in some of my software...

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

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.

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.

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

Fixed in 4.1.19

Made note in 4.1.19 changelog and updated
GROUP_CONCAT() description in 4.1 manual.

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

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

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

For 4.1, the cutoff is 255 bytes rather than 512 bytes. See sql/unireg.h definition of CONVERT_IF_BIGGER_TO_BLOB.