MySQL Bugs: #3319: incorrect group_by behaviour with subqueries

Bug #3319	incorrect group_by behaviour with subqueries
Submitted:	29 Mar 2004 3:00	Modified:	5 Apr 2004 5:24
Reporter:	Oleksandr Byelkin
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	4.1	OS:	Any (all)
Assigned to:	Bugs System	Target Version:

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.