Bug #83364 incorrect result with mysql functions
Submitted: 13 Oct 2016 9:03 Modified: 2 Jan 2020 23:10
Reporter: 帅 Bang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7, 5.6.33, 5.7.15 OS:Linux
Assigned to: 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] MySQL Verification Team
Hello Bang,

Thank you for the report and test case.

Thanks,
Umesh
[2 Jan 2020 23:10] Roy Lyseng
Posted by developer:
 
Fixed in 8.0.18