Bug #74354 REPLACE INTO affected_rows wrong
Submitted: 13 Oct 2014 10:44 Modified: 13 Oct 2014 14:19
Reporter: Ulf Wendel Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.7.5, 8.0.23, 5.7.33 OS:Any
Assigned to: CPU Architecture:Any

[13 Oct 2014 10:44] Ulf Wendel
Description:
Affects and client reading affected rows including HTTP Plugin for MySQL. 

The manual claims:

"The REPLACE statement returns a count to indicate the number of rows affected. This is the sum of the rows deleted and inserted. If the count is 1 for a single-row REPLACE, a row was inserted and no rows were deleted. If the count is greater than 1, one or more old rows were deleted before the new row was inserted. It is possible for a single row to replace more than one old row if the table contains multiple unique indexes and the new row duplicates values for different old rows in different unique indexes. ", http://dev.mysql.com/doc/refman/5.7/en/replace.html

This only works for the example given in the manual. A minimal change to the table structure changes the affected rows value and hence the value becomes useless to any consumer as the consumer cannot distinguish between update and insert. 

This works:

mysql> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0,01 sec)

ERROR: 
No query specified

mysql> delete from test;
Query OK, 0 rows affected (0,01 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 1 row affected (0,01 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 2 rows affected (0,01 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 2 rows affected (0,04 sec)

How to repeat:
This is wrong:

mysql> alter table test drop ts;
Query OK, 0 rows affected (0,18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> delete from test;
Query OK, 1 row affected (0,01 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0,01 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 1 row affected (0,02 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 1 row affected (0,01 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 1 row affected (0,02 sec)

Affected rows should be 2 according to the manual. However, it is not.

Seemingly affected rows depends on the actual table structure.
[13 Oct 2014 14:19] MySQL Verification Team
Hello Ulf Wendel,

Thank you for the bug report and test case.

Thanks,
Umesh
[13 Oct 2014 14:20] MySQL Verification Team
// 5.1.73, 5.5.41, 5.6.22 and 5.7.6 - affected

mysql> use test
Database changed
mysql>
mysql> CREATE TABLE `test` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `data` varchar(64) DEFAULT NULL,
    ->   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.11 sec)

mysql> delete from test;
Query OK, 0 rows affected (0.00 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 1 row affected (0.07 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 2 rows affected (0.07 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 2 rows affected (0.06 sec)

mysql> alter table test drop ts;
Query OK, 1 row affected (0.24 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> delete from test;
Query OK, 1 row affected (0.07 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 1 row affected (0.07 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 1 row affected (0.07 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 1 row affected (0.20 sec)
[14 Oct 2014 9:55] Andrey Hristov
The bug seems to be in InnoDB. MyISAM behaves correctly.

mysql> CREATE TABLE `test` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `data` varchar(64) DEFAULT NULL,   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   PRIMARY KEY (`id`) ) ENGINE=myisam AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0,00 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 1 row affected (0,00 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 2 rows affected (0,00 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 2 rows affected (0,00 sec)

mysql> alter table test drop ts; 
Query OK, 1 row affected (0,00 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
mysql> delete from test; 
Query OK, 1 row affected (0,00 sec)  
 
mysql> replace into test(id, data) values (1, 'a');
Query OK, 1 row affected (0,00 sec)  
 
mysql> replace into test(id, data) values (1, 'a');
Query OK, 2 rows affected (0,00 sec) 
 
mysql> replace into test(id, data) values (1, 'a');
Query OK, 2 rows affected (0,01 sec) 
   
mysql> show create table test; 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table   |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (  
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `data` varchar(64) DEFAULT NULL,   
  PRIMARY KEY (`id`) 
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> alter table test engine=innodb;
Query OK, 1 row affected (0,02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> delete from test;
Query OK, 1 row affected (0,00 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 1 row affected (0,00 sec)

mysql> replace into test(id, data) values (1, 'a');
Query OK, 1 row affected (0,00 sec)
[12 Mar 6:06] MySQL Verification Team
Bug #102940 marked as duplicate of this one