MySQL Bugs: #3319: incorrect group_by behaviour with subqueries

Bug #3319	incorrect group_by behaviour with subqueries
Submitted:	29 Mar 2004 1:00	Modified:	5 Apr 2004 3:24
Reporter:	Oleksandr Byelkin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1	OS:	Any (all)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
incorrect group_by behaviour with subqueries (see example) 
 
(I added this bug report to prevent forgeting our e-mail exchange) 

How to repeat:
 create table t1 (a int, c int); 
+ insert into t1 values (1, 1), (1, 2), (2, 3), (2, 4); 
+ create table t2 (a int, c int); 
+ insert into t2 values (1, 1), (1, 2), (2, 3), (2, 4); 
+ select group_concat(c order by (select group_concat(c order by a) from t2 
where a=t1.a)) from t1; 
+ group_concat(c order by (select group_concat(c order by a) from t2 where 
a=t1.a)) 
+ 4,3,2,1 
+ select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as ord 
from t1 order by ord; 
+ a     c       ord 
+ 1     1       2,1 
+ 1     2       2,1 
+ 2     3       4,3 
+ 2     4       4,3 
 

Suggested fix:
move gorder_list to SELECT_LEX

A subquery returns blob as a pointer. When group_concat stores values for sort, it stores same pointer for all records.

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:

Fix will be in 4.1.2.

GROUP_CONCAT() has still the limitation that it can't handle BLOB/TEXT fields together with DISTINCT or ORDER BY. I have documented this in the known bugs section in the manual.

One way to go around this limitation is to use MID() to get the string length shorter than 256 bytes.