MySQL Bugs: #18281: group_concat changes charset to binary

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:	Windows (winXP/Linux Suse)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

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)

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.

Gluh

Ask Bar to be one of your reviewers on this one.  Thanks

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

The fix is Ok to push.

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

Fixed in 5.0.21

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.

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)

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

ok, thanks!