Bug #101199 the result of replace function is inconsistent under different collations

• Submitted: 16 Oct 2020 2:59
• Modified: 16 Oct 2020 11:58
• Reporter: Peter Gu
• Status: Can't repeat
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.28
• OS: Windows
• CPU Architecture: Any
• Tags: collation, FUNCTION, REPLACE

[16 Oct 2020 2:59] Peter Gu

Description:
  We find that the result of replace function is inconsistent under different collations.
  For example:
  SQL:
  select user_name,replace(user_name,' ',null) as replace_name from temp_test;
  Data:
  only 1 row, user_name = Peter
  expect result: Peter

  case 1
    user_name  collation = utf8mb4_general_ci
    result: null
  case 2
    user_name  collation = utf8mb4_bin
    result: Peter 

How to repeat:
  drop table if exists temp_test;

  CREATE TABLE `temp_test` (
  `rec_id` int(11) NOT NULL,
  `user_name` varchar(45) COLLATE utf8mb4_bin DEFAULT NULL,
  `tel` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`rec_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

  insert into temp_test values(1,"Peter",'13888888888');commit;

  select user_name,replace(user_name,' ',null) as replace_name,tel,replace(tel,' ',null) as replace_tel from temp_test

[16 Oct 2020 11:58] MySQL Verification Team

Hi Mr. Gu,

Thank you for your bug report.

However, we are not able to repeat it.

This is the results that we get when the table uses collation utf8mb4_bin and utfmb4_general_ci.

Let us note what is wrong in it:

user_name	replace_name	tel	replace_tel
Peter	NULL	13888888888	NULL
user_name	replace_name	tel	replace_tel
Peter	NULL	13888888888	NULL

Seems that you are not using our latest releases.