MySQL Bugs: #45780: CONCAT(CASE(numeric)) returns wrong length

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

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).

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.

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)

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 >

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

http://lists.mysql.com/commits/78156 is Ok to push.