MySQL Bugs: #55744: GROUP_CONCAT + CASE + ucs return garbage

Bug #55744	GROUP_CONCAT + CASE + ucs return garbage
Submitted:	4 Aug 2010 12:52	Modified:	16 Nov 2010 2:27
Reporter:	Alexander Barkov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Any
Assigned to:	Alexander Barkov	CPU Architecture:	Any
Tags:	regression

Description:
GROUP_CONCAT(CASE WHEN numeric_column THEN numeric_column ELSE '' END)
return bad results.

How to repeat:
SET NAMES utf8;
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (a MEDIUMINT NULL) ENGINE=MYISAM;
INSERT INTO t1 VALUES (1234567);
SET character_set_connection=ucs2;
SELECT GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) FROM t1;       

-> 

GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END)
ㄲ㌴㔶\0

Suggested fix:
The expected result is "1234567".

Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.99-m4-debug-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1> USE `test`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql 5.1> SET NAMES utf8;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1> DROP TABLE IF EXISTS t1, t2;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql 5.1> CREATE TABLE t1 (a MEDIUMINT NULL) ENGINE=MYISAM;
Query OK, 0 rows affected (0.10 sec)

mysql 5.1> INSERT INTO t1 VALUES (1234567);
Query OK, 1 row affected (0.00 sec)

mysql 5.1> SET character_set_connection=ucs2;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1> SELECT GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) FROM t1;       
+----------------------------------------------+
| GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) |
+----------------------------------------------+
| ㄲ㌴㔶                                    |
+----------------------------------------------+
1 row in set (0.09 sec)

The patch http://lists.mysql.com/commits/115019 was comitted here in a mistake.

Please ignore.

Another example demonstrating this problem:

DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (a MEDIUMINT NULL);
INSERT INTO t1 VALUES (1234567);
SET character_set_connection=ucs2;
CREATE TABLE t2 AS SELECT CASE WHEN a THEN a ELSE '' END FROM t1;
SHOW WARNINGS;
SELECT * FROM t2;

+---------+------+---------------------------------------------------------------------+
| Level   | Code | Message                                                             |
+---------+------+---------------------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'CASE WHEN a THEN a ELSE '' END' at row 1 |
+---------+------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------+
| CASE WHEN a THEN a ELSE '' END |
+--------------------------------+
| ㄲ㌴㔶                         |
+--------------------------------+
1 row in set (0.00 sec)

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/119522

3283 Alexander Barkov	2010-09-30
      Bug#55744 GROUP_CONCAT + CASE + ucs return garbage
      
      Problem: CASE didn't work with a mixture of different character
      sets in THEN/ELSE in some cases.
      This happened because after character set aggregation
      newly created Item_func_conv_charset items corresponding
      to THEN/ELSE arguments were not put back to args[] array.
      
      Fix:
      put all Item_func_conv_charset back to args[].
      
      
        @ mysql-test/include/ctype_numconv.inc
        @ mysql-test/r/ctype_ucs.result
        Adding tests
      
        @ sql/item_cmpfunc.cc
        Put "agg" back to args[] after character set aggregation.

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/119940

3092 Alexander Barkov	2010-10-05
      Bug#55744 GROUP_CONCAT + CASE + ucs return garbage
      
      Problem: CASE didn't work with a mixture of different character
      sets in THEN/ELSE in some cases.
      This happened because after character set aggregation
      newly created Item_func_conv_charset items corresponding
      to THEN/ELSE arguments were not put back to args[] array.
      
      Fix:
      put all Item_func_conv_charset back to args[].
      
      
        @ mysql-test/include/ctype_numconv.inc
        @ mysql-test/r/ctype_ucs.result
        Adding tests
      
        @ sql/item_cmpfunc.cc
        Put "agg" back to args[] after character set aggregation.

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/120120

3094 Alexander Barkov	2010-10-06
      Bug#55744 GROUP_CONCAT + CASE + ucs return garbage
      
      Problem: CASE didn't work with a mixture of different character
      sets in THEN/ELSE in some cases.
      This happened because after character set aggregation
      newly created Item_func_conv_charset items corresponding
      to THEN/ELSE arguments were not put back to args[] array.
      
      Fix:
      put all Item_func_conv_charset back to args[].
      
      
        @ mysql-test/include/ctype_numconv.inc
        @ mysql-test/r/ctype_ucs.result
        Adding tests
      
        @ sql/item_cmpfunc.cc
        Put "agg" back to args[] after character set aggregation.

Pushed into mysql-5.5-bugfixing [5.5.7-rc]
Pushed into mysql-trunk-merge   [5.6.1-m4]

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/120385

3226 Alexander Barkov	2010-10-08
      The fix for 
       Bug#55744 GROUP_CONCAT + CASE + ucs return garbage
      revealed problems in how character set aggregation
      code works with prepared statements.
      
      This patch fixes (hopefully) the problems.

Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)

Noted in 5.5.7 changelog.

CASE expressions with a mix of operands in different character sets
sometimes returned incorrect results.