Bug #73028 Unexpected result with nested CONCAT, SUBSTR, and REPLACE
Submitted: 17 Jun 2014 18:27 Modified: 19 Nov 2019 22:07
Reporter: Devin Withers Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.6.19 OS:Linux (x86_64)
Assigned to: CPU Architecture:Any

[17 Jun 2014 18:27] Devin Withers
Description:
When building a string with CONCAT, another CONCAT inside the first, and a SUBSTR and REPLACE inside that, an unexpected result is returned. The result of the SUBSTR seems to be being pulled from the string being built by the inner CONCAT. The simplest case I could narrow it down to is below.

I tested it on all the servers I had access to, and confirmed that it occurs in 5.6.19, 5.6.14, and 5.5.34. It didn't occur on the 5.0.37 server I tested it on.

How to repeat:
SELECT CONCAT('Product=',CONCAT('XYZ01','_',SUBSTR(REPLACE('ABCDEF','CD','ZZ'),2)));

Expected Result: Product=XYZ01_BZZEF
Actual Result: Product=XYZ01_YZ01_
[17 Jun 2014 18:47] MySQL Verification Team
Thank you for the bug report. 5.0 not affected 5.1+ affected.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.20-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > SELECT CONCAT('Product=',CONCAT('XYZ01','_',SUBSTR(REPLACE('ABCDEF','CD','ZZ'),2)));
+------------------------------------------------------------------------------+
| CONCAT('Product=',CONCAT('XYZ01','_',SUBSTR(REPLACE('ABCDEF','CD','ZZ'),2))) |
+------------------------------------------------------------------------------+
| Product=XYZ01_YZ01_                                                          |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.6 > exit
Bye

C:\dbs>net start mysqld51
The MySQLD51 service is starting.
The MySQLD51 service was started successfully.

C:\dbs>51

C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.74-Win X64-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 > SELECT CONCAT('Product=',CONCAT('XYZ01','_',SUBSTR(REPLACE('ABCDEF','CD','ZZ'),2)));
+------------------------------------------------------------------------------+
| CONCAT('Product=',CONCAT('XYZ01','_',SUBSTR(REPLACE('ABCDEF','CD','ZZ'),2))) |
+------------------------------------------------------------------------------+
| Product=XYZ01_YZ01_                                                          |
+------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql 5.1 > exit
Bye

C:\dbs>net start mysqld57
The mysqld57 service is starting..
The mysqld57 service was started successfully.

C:\dbs>57

C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 --prompt="mysql 5.7 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.5-m15 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > SELECT CONCAT('Product=',CONCAT('XYZ01','_',SUBSTR(REPLACE('ABCDEF','CD','ZZ'),2)));
+------------------------------------------------------------------------------+
| CONCAT('Product=',CONCAT('XYZ01','_',SUBSTR(REPLACE('ABCDEF','CD','ZZ'),2))) |
+------------------------------------------------------------------------------+
| Product=XYZ01_YZ01_                                                          |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.7 > exit
Bye

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.38-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > SELECT CONCAT('Product=',CONCAT('XYZ01','_',SUBSTR(REPLACE('ABCDEF','CD','ZZ'),2)));
+------------------------------------------------------------------------------+
| CONCAT('Product=',CONCAT('XYZ01','_',SUBSTR(REPLACE('ABCDEF','CD','ZZ'),2))) |
+------------------------------------------------------------------------------+
| Product=XYZ01_YZ01_                                                          |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

C:\dbs>net start mysqld50
The MySQLD50 service is starting.
The MySQLD50 service was started successfully.

C:\dbs>50

C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.97-Win X64 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > SELECT CONCAT('Product=',CONCAT('XYZ01','_',SUBSTR(REPLACE('ABCDEF','CD','ZZ'),2)));
+------------------------------------------------------------------------------+
| CONCAT('Product=',CONCAT('XYZ01','_',SUBSTR(REPLACE('ABCDEF','CD','ZZ'),2))) |
+------------------------------------------------------------------------------+
| Product=XYZ01_BZZEF                                                          |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.0 >
[17 Jun 2014 19:29] MySQL Verification Team
this bug was introduced in 5.1.23.
[19 Nov 2019 22:07] Roy Lyseng
Posted by developer:
 
Fixed in 5.7.29 and later.