Bug #18281 group_concat changes charset to binary
Submitted: 16 Mar 2006 14:31 Modified: 8 Apr 2006 12:49
Reporter: Ronald Weiss Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.19/5.1.8BK/4.1BK OS:Microsoft Windows (winXP/Linux Suse)
Assigned to: Sergei Glukhov CPU Architecture:Any

[16 Mar 2006 14:31] Ronald Weiss
Description:
When using ORDER BY inside GROUP_CONCAT, and a non-string column is involved in the ORDER BY clause, the result's character set is binary. Without the ORDER BY clause, or when ordering by a string column, the resulting character set is as it should be.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.19-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE t (c1 VARCHAR(10), c2 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT CHARSET(GROUP_CONCAT(c1)) FROM t;
+---------------------------+
| CHARSET(GROUP_CONCAT(c1)) |
+---------------------------+
| utf8                      |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT CHARSET(GROUP_CONCAT(c1 ORDER BY c2)) FROM t;
+---------------------------------------+
| CHARSET(GROUP_CONCAT(c1 ORDER BY c2)) |
+---------------------------------------+
| binary                                |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%char%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | utf8                                      |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8                                      |
| character_set_server     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | D:\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)
[16 Mar 2006 15:11] Valeriy Kravchuk
Thank you for a bug report. Looks like this should be fixed by fix to bug #14169, that is even documented (http://dev.mysql.com/doc/refman/5.0/en/news-5-0-19.html):

"The result type of the GROUP_CONCAT()  function is now VARCHAR only if the the value of the group_concat_max_len system variable is less than or equal to 512. Otherwise, this funciton returns a BLOB. (Bug #14169)"

This is not true:

mysql> CREATE TABLE t (c1 VARCHAR(10), c2 INT);
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT CHARSET(GROUP_CONCAT(c1)) FROM t;
+---------------------------+
| CHARSET(GROUP_CONCAT(c1)) |
+---------------------------+
| utf8                      |
+---------------------------+
1 row in set (0.02 sec)

mysql> SELECT CHARSET(GROUP_CONCAT(c1 ORDER BY c2)) FROM t;
+---------------------------------------+
| CHARSET(GROUP_CONCAT(c1 ORDER BY c2)) |
+---------------------------------------+
| binary                                |
+---------------------------------------+
1 row in set (0.02 sec)

mysql> show variables like 'group_concat%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 1024  |
+----------------------+-------+
1 row in set (0.00 sec)

Let's change it:

mysql> set group_concat_max_len=100;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CHARSET(GROUP_CONCAT(c1 ORDER BY c2)) FROM t;
+---------------------------------------+
| CHARSET(GROUP_CONCAT(c1 ORDER BY c2)) |
+---------------------------------------+
| binary                                |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> set global group_concat_max_len=100;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CHARSET(GROUP_CONCAT(c1 ORDER BY c2)) FROM t;
+---------------------------------------+
| CHARSET(GROUP_CONCAT(c1 ORDER BY c2)) |
+---------------------------------------+
| binary                                |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'group_concat%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 100   |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> SELECT CHARSET(GROUP_CONCAT(c1)) FROM t;
+---------------------------+
| CHARSET(GROUP_CONCAT(c1)) |
+---------------------------+
| utf8                      |
+---------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19-nt |
+-----------+
1 row in set (0.00 sec)

So, that server variable's value does not really matter in this case.
[18 Mar 2006 8:53] Reggie Burnett
Gluh

Ask Bar to be one of your reviewers on this one.  Thanks
[4 Apr 2006 13:08] 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/commits/4445
[4 Apr 2006 13:44] Alexander Barkov
The fix is Ok to push.
[7 Apr 2006 8:14] 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/commits/4599
[7 Apr 2006 8:21] Sergei Glukhov
Fixed in 5.0.21
[8 Apr 2006 12:49] Jon Stephens
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:

Documented bugfix in 5.0.21 changelog. Closed.
[3 Feb 2010 16:06] Bob Florian
I think this is still a bug.

Steps to reproduce:

CREATE TABLE `test1` (
  `id` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)

insert into test1 values (null);
insert into test1 values (null);
insert into test1 values (null);
insert into test1 values (null);
insert into test1 values (null);

select charset(group_concat(id)) from test1;

binary

select charset(group_concat(cast(id as char))) from test1;

utf8

MySQL Version I am running is 5.0.83 (Microsoft Windows)
[3 Feb 2010 16:23] Alexander Barkov
Hello Bob,
thanks for the info!
This problem is going to be fixed soon (most likely in version 5.5).

This is the task description:
http://forge.mysql.com/worklog/task.php?id=2649
[3 Feb 2010 16:30] Bob Florian
ok, thanks!