MySQL Bugs: #74354: REPLACE INTO affected

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

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.

Hello Ulf Wendel,

Thank you for the bug report and test case.

Thanks,
Umesh

// 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)

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)

Bug #102940 marked as duplicate of this one