Bug #104144 REPLACE INTO removes the row

• Submitted: 29 Jun 2021 12:14
• Modified: 29 Jun 2021 12:39
• Reporter: Владислав Сокол
• Status: Verified
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.25, 5.7.34, 5.6.51
• OS: Any
• 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