MySQL Bugs: #39543: Group by with case and lpad inside concat truncates values

Bug #39543	Group by with case and lpad inside concat truncates values
Submitted:	19 Sep 2008 16:56	Modified:	10 Feb 2018 16:42
Reporter:	Gavin Towey	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.67/5.1/6.0	OS:	Linux (debian 4.0)
Assigned to:		CPU Architecture:	Any
Tags:	case, concat, GROUP BY, lpad, regression

Description:
using a group by in a statement that involves creating values with concat and a case statement and lpad causes incorrect values to be computed in the field list.

How to repeat:
mysql>  select concat(case when 1=1 then 5 else 6 end, '-', lpad(val,3,'0')) as tier from t1 group by 1;
+------+
| tier |
+------+
| 5-00 | 
+------+
1 row in set (0.00 sec)

When not using GROUP BY values are computed correctly:

mysql>  select concat(case when 1=1 then 5 else 6 end, '-', lpad(val,3,'0')) as tier from t1;           
+-------+
| tier  |
+-------+
| 5-001 | 
| 5-002 | 
| 5-003 | 
+-------+
3 rows in set (0.00 sec)

or replacing the case statement with an IF function produces the correct results:

mysql> select concat( IF ( 1=1 , 5 , 6) , '-', lpad(val,3,'0')) as tier from t1; 
+-------+
| tier  |
+-------+
| 5-001 | 
| 5-002 | 
| 5-003 | 
+-------+

Suggested fix:
unknown

forgot to include the table structure and data I was using:

create table t1 ( val int );
insert into t1 VALUES (1), (2), (3);

also forgot to note:

This bug doesn't exist in 5.0.51a

Thank you for the bug report. Verified as described.

mysql 5.0 > select concat(case when 1=1 then 5 else 6 end, '-', lpad(val,3,'0')) as tier from
    -> t1 group by 1;
+------+
| tier |
+------+
| 5-00 |
+------+
1 row in set (0.00 sec)

mysql 5.0 >  select concat(case when 1=1 then 5 else 6 end, '-', lpad(val,3,'0')) as tier from
    -> t1;
+-------+
| tier  |
+-------+
| 5-001 |
| 5-002 |
| 5-003 |
+-------+
3 rows in set (0.00 sec)

Server version: 5.0.41-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1 ( val int );
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t1 VALUES (1), (2), (3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select concat(case when 1=1 then 5 else 6 end, '-', lpad(val,3,'0')) as tier from
    -> t1 group by 1;
+-------+
| tier  |
+-------+
| 5-001 |
| 5-002 |
| 5-003 |
+-------+
3 rows in set (0.03 sec)

Gleb, please make an analysis of this problem and place it in dusgdb.

Same problem exists in Version 5.5.10

Same problem exists in Version 5.5.11

Using MySQL 5.1.49sp1

My scenario was very similar, to the truncated output from concat:
  SELECT fieldA, RIGHT(MIN(
    CONCAT(
      CASE IFNULL(field1,'N')
        WHEN 'N' THEN 1
        WHEN 'Y' THEN 2
        WHEN 'G' THEN 3
        ELSE 4 END 
      , ' - '
      , IFNULL( field1, 'N')
      )),2) AS min_fld
  FROM my_table
  GROUP BY fieldA

The output had the last character clipped: 
| fieldA | min_fld |
+--------+---------+
| asdf   | 1 -     |
| asdf   | 2 -     |
| asdf   | 3 -     |

With the last character missing.
By adding a single space ' ' as the 4th parameter to concat()
  SELECT fieldA, RIGHT(MIN(
     CONCAT(
       CASE IFNULL(field1,'N')
         WHEN 'N' THEN 1
         WHEN 'Y' THEN 2
         WHEN 'G' THEN 3
         ELSE 4 END 
       , ' - '
       , IFNULL( field1, 'N')
       , ' '  -- extra space that is clipped
     )),2)
  FROM my_table
  GROUP BY fieldA

I got valid data:
| fieldA | min_fld |
+--------+---------+
| asdf   | 1 - N   |
| asdf   | 2 - Y   |
| asdf   | 3 - G   |

Any Update for this case?

I found that if you add RTRIM as well, after adding an extra space with CONCAT( ..., ' ' ), it seems to come out proper:

mysql> select rtrim(concat(case when 1=1 then 5 else 6 end, '-', lpad(val,3,'0'),' ')) as tier from t1 group by 1;
+-------+
| tier  |
+-------+
| 5-001 |
| 5-002 |
| 5-003 |
+-------+
3 rows in set (0.00 sec)

Of course, that's only an option if you don't have trailing spaces in your original value that you want to retain.

Posted by developer:
 
Fixed in 5.7.22 and up.