Bug #83364 incorrect result with mysql functions

• Submitted: 13 Oct 2016 9:03
• Modified: 13 Oct 2016 9:36
• Reporter: 帅 Bang
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7, 5.6.33, 5.7.15
• OS: Linux
• CPU Architecture: Any
• Tags: regression

[13 Oct 2016 9:03] 帅 Bang

Description:
mysql5.7> select  CONCAT("h",  LOWER( SUBSTR( REPEAT( TRIM( SUBSTR( SUBSTR( DATE_FORMAT( COALESCE( CONCAT( CAST( 2015 + 4      + 2 AS CHAR(20) ) , '-06-15 01:00:00.000003') , CONCAT( CAST( 2015 + 2 + 2 AS CHAR(20) )     , '-06-15 01:00:00.000003') , CONCAT( CAST( 2015 + 3 + 1 AS CHAR(20) ) , '-06-19 01:00:00.000003') ) , CONCAT( '%l' , '%i' ) ) , 2 ) , 1 , 20) ) , 7 + 1 ) , 6 ) ) ) ;

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| h00000 00000  //an unexpected space here !!!                                                                                                                                                                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

A little messy. Yeah. 

BTW, this bug does not exist in mysql5.6

mysql5.6> select  CONCAT("h",  LOWER( SUBSTR( REPEAT( TRIM( SUBSTR( SUBSTR( DATE_FORMAT( COALESCE( CONCAT( CAST( 2015 + 4      + 2 AS CHAR(20) ) , '-06-15 01:00:00.000003') , CONCAT( CAST( 2015 + 2 + 2 AS CHAR(20) )     , '-06-15 01:00:00.000003') , CONCAT( CAST( 2015 + 3 + 1 AS CHAR(20) ) , '-06-19 01:00:00.000003') ) , CONCAT( '%l' , '%i' ) ) , 2 ) , 1 , 20      ) ) , 7 + 1 ) , 6 ) ) ) ;
Field   1:  `CONCAT("h",  LOWER( SUBSTR( REPEAT( TRIM( SUBSTR( SUBSTR( DATE_FORMAT( COALESCE( CONCAT( CAST( 2015 + 4      + 2 AS CHAR(20) ) , '-06-15 01:00:00.000003') , CONCAT( CAST( 2015 + 2 + 2 AS CHAR(20) )     , '-06-15 01:00:00.000003') , CONCAT( CAST( 2015 + 3 +`

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| h00000000000   //correct. no space here.                                                                                                                                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
select  CONCAT("h",  LOWER( SUBSTR( REPEAT( TRIM( SUBSTR( SUBSTR( DATE_FORMAT( COALESCE( CONCAT( CAST( 2015 + 4      + 2 AS CHAR(20) ) , '-06-15 01:00:00.000003') , CONCAT( CAST( 2015 + 2 + 2 AS CHAR(20) )     , '-06-15 01:00:00.000003') , CONCAT( CAST( 2015 + 3 + 1 AS CHAR(20) ) , '-06-19 01:00:00.000003') ) , CONCAT( '%l' , '%i' ) ) , 2 ) , 1 , 20      ) ) , 7 + 1 ) , 6 ) ) ) ;

Suggested fix:
h00000000000 rather than h00000 00000 is returned

[13 Oct 2016 9:36] Umesh Shastry

Hello Bang,

Thank you for the report and test case.

Thanks,
Umesh