Bug #104144 REPLACE INTO removes the row
Submitted: 29 Jun 2021 12:14 Modified: 29 Jun 2021 12:39
Reporter: Владислав Сокол Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0.25, 5.7.34, 5.6.51 OS:Any
Assigned to: CPU Architecture:Any
Tags: replace delete remove

[29 Jun 2021 12:14] Владислав Сокол
Description:
REPLACE INTO statement may delete rows if there is more than one duplicate violation.

Like documentation claims:

MySQL uses the following algorithm for REPLACE (and LOAD DATA ... REPLACE):
1. Try to insert the new row into the table
2. While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
 2a. Delete from the table the conflicting row that has the duplicate key value
 2b. Try again to insert the new row into the table

The problem - if on the stage (2b)=>(1) the duplicate violation occures again due to another unique constraint then one more row is deleted according to (2a).

So REPLACE INTO may decrease total table rows amount.

How to repeat:
CREATE TABLE test (id INT PRIMARY KEY, val INT UNIQUE);
INSERT INTO test VALUES (1,1), (2,2);
SELECT * FROM test;  -- Output 1
REPLACE INTO test (id, val) VALUES (2,1);
SELECT * FROM test;  -- Output 2

Output 1:

id	val
1	1
2	2

Output 2:

id	val
2	1

Fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a01b0903440278711e8577a75cf11973
[29 Jun 2021 12:39] MySQL Verification Team
Hello Владислав Сокол,

Thank you for the report and feedback.

regards,
Umesh