| Bug #77994 | function relace('xxx','yyy', c1) returns 'xxx' when column c1 is null | ||
|---|---|---|---|
| Submitted: | 8 Aug 2015 10:09 | Modified: | 26 Nov 2019 21:35 |
| Reporter: | Su Dylan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| 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 16:52]
MySQL Verification Team
Hello Su Dylan, Thank you for the report and test case. Thanks, Umesh
[8 Aug 2015 16:52]
MySQL Verification Team
// 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)
[26 Nov 2019 21:35]
Roy Lyseng
Posted by developer: Fixed in 8.0
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.