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

[19 Sep 2008 16:56] Gavin Towey
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
[19 Sep 2008 16:57] Gavin Towey
forgot to include the table structure and data I was using:

create table t1 ( val int );
insert into t1 VALUES (1), (2), (3);
[19 Sep 2008 17:06] Gavin Towey
also forgot to note:

This bug doesn't exist in 5.0.51a
[19 Sep 2008 17:45] MySQL Verification Team
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)
[29 Dec 2008 19:14] Igor Babaev
Gleb, please make an analysis of this problem and place it in dusgdb.
[17 May 2011 9:21] Boris Jansen
Same problem exists in Version 5.5.10
[17 May 2011 9:21] Boris Jansen
Same problem exists in Version 5.5.11
[22 Jun 2011 22:23] J Jorgenson
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   |
[29 Mar 2012 10:37] Boris Jansen
Any Update for this case?
[12 Mar 2015 1:11] Chad Wallace
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.
[10 Feb 2018 16:42] Roy Lyseng
Posted by developer:
 
Fixed in 5.7.22 and up.