Bug #115830 `Replace into` may be invalid when functional key is unique

• Submitted: 14 Aug 2:24
• Modified: 14 Aug 6:04
• Reporter: huahua xu
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.39, 8.4.2, 9.0.1
• OS: Any
• CPU Architecture: Any

[14 Aug 2:24] huahua xu

Description:
The `repalce into` statment maybe do not work when the table has unique and functional key. But it would be ok after create another unique and functional key on it.

How to repeat:
mysql> create table test2uk(c1 int,c2 int,c3 int,c4 int);

mysql> create unique index iii on test2uk((c2+c3));

mysql> create unique index iii1 on test2uk(c4);

mysql> insert into test2uk values(1,1,1,1), (2,1,2,2);

mysql>  replace into test2uk values(3,2,0,2);
ERROR 1062 (23000): Duplicate entry '2' for key 'test2uk.iii1'

mysql> create unique index iii2 on test2uk((c1+1));

mysql> replace into test2uk values(3,2,0,2);
Query OK, 3 rows affected (0.01 sec)

[14 Aug 6:04] MySQL Verification Team

Hello huahua xu,

Thank you for the report and test case.

regards,
Umesh