Bug #102940 affected rows of replace statement seems to be not proper
Submitted: 12 Mar 2021 1:38 Modified: 12 Mar 2021 6:06
Reporter: Brian Yue (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:MySQL8.0.22 OS:Any (rhel-7.4)
Assigned to: CPU Architecture:Any (intel x86-64)

[12 Mar 2021 1:38] Brian Yue
Description:
Hello, dear verification team:
  I created a table with UK, and I inserted one record into the table. Then I tested replace staments, and I made sure that the PRIMARY KEY of the only record is not changed. But as a result, I found that sometimes I got 1 rows affected from the replace statement, and sometimes 2 rows affected. The problem is that, I have only 1 record in the table.
  However, it's the implementation of replace_stmt that leads to this inconsistency, but I think it's not proper because I have only 1 record in the table. For replace_stmt, it will act as a insert if there is no same record, and act as a update if there is a record with same key. No matter I execute an insert_stmt or update_stmt for 1 record, I will always get at most 1 rows affected.
  Sql statements are placed in `How to repear` section.

How to repeat:
mysql> create database dbagent;
Query OK, 1 row affected (0.01 sec)

mysql> use dbagent
Database changed

mysql> create table heartbeat(serverid bigint primary key, hb_time timestamp);                                                                      Query OK, 0 rows affected (0.74 sec)

mysql> insert into dbagent.heartbeat values (1, "2021-03-12 09:18:37");
Query OK, 1 row affected (0.01 sec)

mysql> replace into dbagent.heartbeat values (1, "2021-03-12 09:18:37");
Query OK, 1 row affected (0.00 sec)

mysql> replace into dbagent.heartbeat values (1, "2021-03-12 09:18:37");
Query OK, 1 row affected (0.01 sec)

mysql> replace into dbagent.heartbeat values (1, "2021-03-12 09:18:38");
Query OK, 2 rows affected (0.00 sec)

mysql> replace into dbagent.heartbeat values (1, "2021-03-12 09:18:39");
Query OK, 2 rows affected (0.00 sec)
[12 Mar 2021 6:06] MySQL Verification Team
Hello Brian Yue,

Thank you for the report and test case.
Imho this is duplicate of Bug #74354, please see Bug #74354 	

regards,
Umesh