Bug #77994 function relace('xxx','yyy', c1) returns 'xxx' when column c1 is null
Submitted: 8 Aug 2015 10:09 Modified: 8 Aug 2015 16:52
Reporter: Su Dylan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.22, 5.5.46, 5.6.26, 5.7.9 OS:Any
Assigned to: CPU Architecture:Any

[8 Aug 2015 10:09] Su Dylan
Description:
scenario 1;
mysql> drop table if exists t1; create table t1(a int primary key, b char(20));
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select a , b , replace('xxx','yyy',b) from t1;
+---+------+------------------------+
| a | b    | replace('xxx','yyy',b) |
+---+------+------------------------+
| 1 | NULL | NULL                   |
+---+------+------------------------+
1 row in set (0.00 sec)

mysql> select version(),database();
+------------+------------+
| version()  | database() |
+------------+------------+
| 5.6.22-log | test       |
+------------+------------+
1 row in set (0.00 sec)

mysql> show create database test;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

Scenario 2:
mysql> drop table if exists t1; create table t1(a int primary key, b char(20));
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select a , b , replace('xxx','yyy',b) from t1;
+---+------+------------------------+
| a | b    | replace('xxx','yyy',b) |
+---+------+------------------------+
| 1 | NULL | xxx                    |
+---+------+------------------------+
1 row in set (0.00 sec)

mysql> select version(),database();
+------------+------------+
| version()  | database() |
+------------+------------+
| 5.6.22-log | test_052   |
+------------+------------+
1 row in set (0.00 sec)

mysql> show create database test_052;
+----------+------------------------------------------------------------------------------------+
| Database | Create Database                                                                    |
+----------+------------------------------------------------------------------------------------+
| test_052 | CREATE DATABASE `test_052` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
+----------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
drop table if exists t1; create table t1(a int primary key, b char(20));
insert into t1 values(1,NULL);
select a , b , replace('xxx','yyy',b) from t1;
select version(),database();

Suggested fix:
replace(str1, str2, str3) should return null when any of the params is null.
[8 Aug 2015 16:52] Umesh Shastry
Hello Su Dylan,

Thank you for the report and test case.

Thanks,
Umesh
[8 Aug 2015 16:52] Umesh Shastry
// 5.5.46

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.46    |
+-----------+
1 row in set (0.00 sec)

mysql> drop table if exists t1; create table t1(a int primary key, b char(20));
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select a , b , replace('xxx','yyy',b) from t1; -- returns xxx
+---+------+------------------------+
| a | b    | replace('xxx','yyy',b) |
+---+------+------------------------+
| 1 | NULL | xxx                    |
+---+------+------------------------+
1 row in set (0.00 sec)

mysql> select a , b , replace('xxx','yyy',NULL) from t1; -- returns null
+---+------+---------------------------+
| a | b    | replace('xxx','yyy',NULL) |
+---+------+---------------------------+
| 1 | NULL | NULL                      |
+---+------+---------------------------+
1 row in set (0.00 sec)

// 5.6.26

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.26-log |
+------------+
1 row in set (0.00 sec)

mysql> drop table if exists t1; create table t1(a int primary key, b char(20));
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1,NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select a , b , replace('xxx','yyy',b) from t1; -- returns xxx
+---+------+------------------------+
| a | b    | replace('xxx','yyy',b) |
+---+------+------------------------+
| 1 | NULL | xxx                    |
+---+------+------------------------+
1 row in set (0.00 sec)

mysql> select a , b , replace('xxx','yyy',NULL) from t1; -- returns null
+---+------+---------------------------+
| a | b    | replace('xxx','yyy',NULL) |
+---+------+---------------------------+
| 1 | NULL | NULL                      |
+---+------+---------------------------+
1 row in set (0.00 sec)

// 5.7.9

mysql> select version();
+--------------------------------------+
| version()                            |
+--------------------------------------+
| 5.7.9-enterprise-commercial-advanced |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t1; create table t1(a int primary key, b char(20));
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select a , b , replace('xxx','yyy',b) from t1; -- returns xxx
+---+------+------------------------+
| a | b    | replace('xxx','yyy',b) |
+---+------+------------------------+
| 1 | NULL | xxx                    |
+---+------+------------------------+
1 row in set (0.00 sec)

mysql> select a , b , replace('xxx','yyy',NULL) from t1; -- returns null
+---+------+---------------------------+
| a | b    | replace('xxx','yyy',NULL) |
+---+------+---------------------------+
| 1 | NULL | NULL                      |
+---+------+---------------------------+
1 row in set (0.00 sec)

postgres=# select version();
                                                    version                                                    
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.0 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
(1 row)

postgres=# select replace('abcdefabcdef', 'cd', NULL);
 replace 
---------
 
(1 row)