MySQL Bugs: #4315: GROUP_CONCAT with ORDER BY returns strange results for TEXT fields

Bug #4315	GROUP_CONCAT with ORDER BY returns strange results for TEXT fields
Submitted:	28 Jun 2004 20:07	Modified:	10 Oct 2004 8:41
Reporter:	Jason Carter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.4	OS:	MacOS (Mac OS 10.3.5)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
In MySQL 4.1.2, any attempt to use GROUP_CONCAT with a field of any of the TEXT field types (i.e. BLOB, TEXT, LONGTEXT, etc.) results in errant result sets being returned.  For example:

mysql> select * from test1;
+------+---------------------+
| id   | memo                |
+------+---------------------+
| 1000 | From Outer Space    |
| 2000 | From Bozo the clown |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT id,GROUP_CONCAT(memo ORDER BY memo SEPARATOR ', ') as memo FROM test1 GROUP BY id;
+------+---------------------+
| id   | memo                |
+------+---------------------+
| 1000 | From Bozo the cl    |
| 2000 | From Bozo the clown |
+------+---------------------+
2 rows in set (0.00 sec)

How to repeat:
Execute the following queries in MySQL 4.1.2-alpha:

create database test_2;
use test_2;
create table test1(id int(11),memo text);
insert into test1 values(1000,'From Outer Space'),(2000,'From Bozo the clown');
SELECT * FROM test1;
SELECT id,GROUP_CONCAT(memo ORDER BY memo SEPARATOR ', ') FROM test1 GROUP BY id;
drop table test1;
drop database test_2;

Update: This only applies to queries that use the ORDER BY clause in the GROUP_CONCAT function.  For example, the following query works fine.

mysql> SELECT id,GROUP_CONCAT(memo) FROM test1 GROUP BY id;
+------+---------------------+
| id   | GROUP_CONCAT(memo)  |
+------+---------------------+
| 1000 | From Outer Space    |
| 2000 | From Bozo the clown |
+------+---------------------+
2 rows in set (0.01 sec)

Verified against 4.1.3/Linux, thank you.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int, b TEXT );
INSERT INTO t1 VALUES (1,'First Row'), (2,'Second Row');
SELECT GROUP_CONCAT(b ORDER BY b) FROM t1 GROUP BY a;
DROP TABLE t1;

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:

Error was found and fixed. The problem was with values of blobs in internal buffer.

drop table if exists t1;
create table t1 (i int, t text);
insert into t1 values (1, "1");
insert into t1 values (1, "22");
insert into t1 values (1, "333");
insert into t1 values (1, "4444");
insert into t1 values (1, "55555");
select group_concat(t order by t) from t1 group by i; 

+----------------------------+
| group_concat(t order by t) |
+----------------------------+
| 5,55,555,5555,55555        |
+----------------------------+
1 row in set (0.00 sec)

Verified Sergei's example on 4.1.4.  This bug persists.

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