| Bug #79470 | w/ Collate = utf8_bin, replace('xxx','yyy',c1(NULL)) gives 'xxx' | ||
|---|---|---|---|
| Submitted: | 1 Dec 2015 8:26 | Modified: | 28 Nov 2019 22:24 |
| Reporter: | Su Dylan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.7.8, 5.7.9, 5.6.29, 5.5.48 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[1 Dec 2015 8:39]
Su Dylan
An easier way to recreate:
select replace('1919500423', 'usa' collate 'utf8_bin', NULL), replace('1919500423', 'usa' collate 'utf8_general_ci', NULL);
mysql> select replace('1919500423', 'usa' collate 'utf8_bin', NULL), replace('1919500423', 'usa' collate 'utf8_general_ci', NULL);
+-------------------------------------------------------+--------------------------------------------------------------+
| replace('1919500423', 'usa' collate 'utf8_bin', NULL) | replace('1919500423', 'usa' collate 'utf8_general_ci', NULL) |
+-------------------------------------------------------+--------------------------------------------------------------+
| 1919500423 | NULL |
+-------------------------------------------------------+--------------------------------------------------------------+
1 row in set (0.00 sec)
[1 Dec 2015 9:14]
MySQL Verification Team
Hello Su Dylan, Thank you for the report and test case. Thanks, Umesh
[28 Nov 2019 22:24]
Roy Lyseng
Posted by developer: Fixed in 8.0.18
Description: Output: ======= mysql> drop table if exists t1; create table t1(Query OK, 0 rows affected (0.00 sec) mysql> create table t1(c1 char(20)) CHARSET = utf8,Collate = utf8_bin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values(NULL); Query OK, 1 row affected (0.00 sec) ctmysql> select c1, replace('xxx','yyy',c1) from t1; +------+-------------------------+ | c1 | replace('xxx','yyy',c1) | +------+-------------------------+ | NULL | xxx | +------+-------------------------+ 1 row in set (0.00 sec) mysql> mysql> drop table if exists t1; le t1(c1 chQuery OK, 0 rows affected (0.00 sec) mysql> create table t1(c1 char(20)) CHARSET = utf8,Collate = utf8_general_ci; into t1 values(NULL); select c1, replace('xxx','yyy',c1) froQuery OK, 0 rows affected (0.01 sec) mysql> insert into t1 values(NULL); Query OK, 1 row affected (0.00 sec) mysql> select c1, replace('xxx','yyy',c1) from t1; +------+-------------------------+ | c1 | replace('xxx','yyy',c1) | +------+-------------------------+ | NULL | NULL | +------+-------------------------+ 1 row in set (0.00 sec) Problem: ======== With CHARSET = utf8,Collate = utf8_bin, replace('xxx','yyy',c1(NULL)) gives 'xxx', where NULL is expected, just as the result with "Collate = utf8_general_ci". How to repeat: drop table if exists t1; create table t1(c1 char(20)) CHARSET = utf8,Collate = utf8_bin; insert into t1 values(NULL); select c1, replace('xxx','yyy',c1) from t1; drop table if exists t1; create table t1(c1 char(20)) CHARSET = utf8,Collate = utf8_general_ci; insert into t1 values(NULL); select c1, replace('xxx','yyy',c1) from t1; Suggested fix: With CHARSET = utf8,Collate = utf8_bin, replace('xxx','yyy',c1(NULL)) gives 'xxx', where NULL is expected, just as the result with "Collate = utf8_general_ci".