MySQL Bugs: #18281: group_concat changes charset to binary

Bug #18281	group_concat changes charset to binary
Submitted:	16 Mar 2006 15:31	Modified:	8 Apr 2006 14:49
Reporter:	Ronald Weiss
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.19/5.1.8BK/4.1BK	OS:	Microsoft Windows (winXP/Linux Suse)
Assigned to:	Sergey Gluhov	Target Version:

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.