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

[29 Mar 2004 1:00] Oleksandr Byelkin
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
[31 Mar 2004 16:01] Vasily Kishkin
A subquery returns blob as a pointer. When group_concat stores values for sort, it stores same pointer for all records.
[5 Apr 2004 3:24] Michael Widenius
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.