Bug #45780 CONCAT(CASE(numeric)) returns wrong length
Submitted: 26 Jun 2009 8:44 Modified: 7 Jul 2009 15:22
Reporter: Alexander Barkov Email Updates:
Status: Patch pending Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1.35 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[26 Jun 2009 8:44] Alexander Barkov
Description:
CONCAT(CASE(numeric)) returns wrong length.

How to repeat:
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.35-debug |
+--------------+
1 row in set (0.00 sec)

mysql> create table t1 as select concat(case when 1 then 2 else 3 end) as c1;
Query OK, 1 row affected, 1 warning (0.44 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'c1' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                    |
+-------+-------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` binary(0) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The created column c1 has length 0. 

Suggested fix:
Fix CASE to return length=1 and create a column of type binary(1).
[27 Jun 2009 0:24] MySQL Verification Team
Hi,

Are you able to repeat the test case with current source server?

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.37-Win X64-log Source distribution

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

mysql 5.1 >use d1
Database changed
mysql 5.1 >select version();
+--------------------+
| version()          |
+--------------------+
| 5.1.37-Win X64-log |
+--------------------+
1 row in set (0.00 sec)

mysql 5.1 >create table t1 as select concat(case when 1 then 2 else 3 end) as c1;
ERROR 1406 (22001): Data too long for column 'c1' at row 1

Thanks in advance.
[29 Jun 2009 6:33] Alexander Barkov
Hi Miguel,

yes, I was able to reproduce it with the latest 5.1.37-bugteam

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

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

mysql> create table t1 as select concat(case when 1 then 2 else 3 end)
    -> as c1;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'c1' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                    |
+-------+-------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` binary(0) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[1 Jul 2009 15:18] MySQL Verification Team
Thank you for the bug report.

mysql 5.1 >create table t1 as select concat(case when 1 then 2 else 3 end) as c1;
Query OK, 1 row affected, 1 warning (0.22 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql 5.1 >show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` binary(0) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql 5.1 >
[7 Jul 2009 15:22] 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/78156

3007 Alexey Botchkov	2009-07-07
      Bug#45780      CONCAT(CASE(numeric)) returns wrong length
         it's more natural to calculate the precision of the result with the
         functions like Item::decimal_precision() or Item::decimal_int_part()
         than with the indirect methods from the max_length
      
      per-file comments:
        mysql-test/r/func_concat.result
      Bug#45780      CONCAT(CASE(numeric)) returns wrong length
         test result
      
        mysql-test/t/func_concat.test
      Bug#45780      CONCAT(CASE(numeric)) returns wrong length
          test case
      
        sql/item_cmpfunc.cc
      Bug#45780      CONCAT(CASE(numeric)) returns wrong length
         use Item::decimal_int_part() to calculate the precision of the result
[8 Jul 2009 11:12] Alexander Barkov
http://lists.mysql.com/commits/78156 is Ok to push.