Bug #10201 group_concat returns string with binary collation
Submitted: 27 Apr 2005 12:55 Modified: 26 Jul 2005 19:56
Reporter: Marek Sklenar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1.10 OS:NT
Assigned to: Alexander Barkov CPU Architecture:Any

[27 Apr 2005 12:55] Marek Sklenar 
Description:
If I retrieve group_concat on column which has cp1250_general_ci collation, I always get binary collation. Why? String function should return string with collation same as arguments have.

How to repeat:
create table zk1 (a varchar(255)) engine=innodb default character set cp1250 collate cp1250_general_ci;

insert into zk1 values ('xxx');

select collation(a) from zk1;

+-------------------+
| collation(a)      |
+-------------------+
| cp1250_general_ci |
+-------------------+
# as expected

select collation(group_concat(a)) from zk1;

+----------------------------+
| collation(group_concat(a)) |
+----------------------------+
| binary                     |
+----------------------------+
# should be cp1250_general_ci
[27 Apr 2005 13:09] MySQL Verification Team 
Verified with 4.1.11.
[6 Jun 2005 10:48] Bugs System 
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/internals/25634
[26 Jul 2005 9:51] Bugs System 
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/internals/27587
[26 Jul 2005 10:02] Alexander Barkov 
Fixed in 4.1.14 and 5.0.11
[26 Jul 2005 19:56] Paul DuBois 
Noted in 4.1.14, 5.0.11 changelogs.
[24 Jul 2007 17:49] Marc Gutt 
Strange problem for me with GROUP_CONCAT():

Receiving COLLATION returns "utf8_general_ci". This is correct since 4.1.11. I'm using 4.1.22.

But reading data with GROUP_CONCAT´() seems to be BINARY as Marek reported before.

My Query:
******************************************
SELECT u.username, GROUP_CONCAT( \'<a href="./?p=groups&gid=\', g.uid, \'">\', g.group_name, \'</a>\' ORDER BY g.group_name SEPARATOR \', \' ) group_name
FROM users u
LEFT JOIN (groups ug, users g) ON u.uid = ug.uid AND ug.gid = g.uid
GROUP BY u.uid
******************************************

If username contains a german umlaut it returns it correctly. But with german umlauts in group_name and using GROUP_CONCAT() I receive:
******************************************
ü
******************************************

If I read group_name without GROUP_CONCAT() I receive it correct, too.

Does it depend on this bug?

regards