MySQL Bugs: #95096: INSERT INTO ... ON DUPLICATE KEY UPDATE returns wrong number of rows updated

Bug #95096	INSERT INTO ... ON DUPLICATE KEY UPDATE returns wrong number of rows updated
Submitted:	24 Apr 2019 1:27	Modified:	24 Apr 2019 6:03
Reporter:	Sangamesh Bellad	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:

INSERT INTO ... ON DUPLICATE KEY UPDATE returns wrong number of rows updated when UPDATE HAPPENS instead of INSERT.

How to repeat:
CREATE TABLE test.`job_status_test` (
  `run_id` char(16) NOT NULL,
  `app` varchar(45) NOT NULL,
  `status` varchar(45) NOT NULL,
  `meta` varchar(1024) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO  test.job_status_test(`run_id`, `app`, `status`, `meta`) VALUES('101','app1','STARTED','')
ON DUPLICATE KEY UPDATE `status`='STARTED' ,  `updated`= CURRENT_TIMESTAMP()

1 row(s) affected

INSERT INTO  test.job_status_test(`run_id`, `app`, `status`, `meta`) VALUES('101','app1','STARTED','')
ON DUPLICATE KEY UPDATE `status`='STARTED' ,  `updated`= CURRENT_TIMESTAMP()

2 row(s) affected

NOTE: Only 1 row was updated in second statement.

Hi Sangamesh,

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

Quoting from manual "With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values"

thanks,
Umesh