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