Bug #78005 replace('xxx','',b) when column b is NULL returns 'xxx'

• Submitted: 10 Aug 2015 7:16
• Modified: 26 Nov 2019 21:40
• Reporter: Su Dylan
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6.22, 5.6.26, 5.7.9
• OS: Any
• CPU Architecture: Any

[10 Aug 2015 7:16] Su Dylan

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

insert into t1 values(1,NULL);
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 a , b , replace('xxx','',b) from t1;
+---+------+---------------------+
| a | b    | replace('xxx','',b) |
+---+------+---------------------+
| 1 | NULL | xxx                 |
+---+------+---------------------+
1 row in set (0.00 sec)

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

mysql> select database(), version();
+------------+------------+
| database() | version()  |
+------------+------------+
| test       | 5.6.22-log |
+------------+------------+
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)

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

Suggested fix:
replace('xxx','',b) when column b is NULL should returns null.

[10 Aug 2015 9:02] MySQL Verification Team

Hello Su Dylan,

Thank you for the report and test case.

Thanks,
Umesh

[26 Nov 2019 21:40] Roy Lyseng

Duplicate of 77994