MySQL Bugs: #28190: ON DUPLICATE KEY UPDATE updates the record even if values are identical

Bug #28190	ON DUPLICATE KEY UPDATE updates the record even if values are identical
Submitted:	2 May 2007 1:43	Modified:	25 Jul 2007 22:12
Reporter:	Jehan Bing	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.37,5.1BK	OS:	Linux (Fedora Core release 4 (Stentz))
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The documentation says that INSERT...ON DUPLICATE KEY UPDATE... and UPDATE have identical effect if there is a duplicated key.
However, if the update actually leaves the record unchanged, the record is still updated and trigger the ON UPDATE.

This happens with both InnoDB and MyISAM.

How to repeat:
mysql> CREATE TABLE `test` (`a` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `b` INTEGER NOT NULL, `c` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY(`a`));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test (a,b) VALUES (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+---+---+---------------------+
| a | b | c                   |
+---+---+---------------------+
| 1 | 2 | 2007-05-01 18:22:47 |
+---+---+---------------------+
1 row in set (0.00 sec)

## note the value for `c`

mysql> UPDATE test SET b=2 WHERE a=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

## no row was affected...

mysql> SELECT * FROM test;
+---+---+---------------------+
| a | b | c                   |
+---+---+---------------------+
| 1 | 2 | 2007-05-01 18:22:47 |
+---+---+---------------------+
1 row in set (0.00 sec)

## ...and `c` is unchanged

mysql> INSERT INTO test (a,b) VALUES (1,2) ON DUPLICATE KEY UPDATE b=2;
Query OK, 2 rows affected (0.00 sec)

## 2 rows affected (i.e. there was an update)...

mysql> SELECT * FROM test;
+---+---+---------------------+
| a | b | c                   |
+---+---+---------------------+
| 1 | 2 | 2007-05-01 18:23:10 |
+---+---+---------------------+
1 row in set (0.01 sec)

## ...and now `c` is changed

Suggested fix:
INSERT...ON DUPLICATE KEY UPDATE should return "0 rows affected" and `c` should not have been changed.

Thank you for the bug report.

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

"If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect: 

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;"

Duplicate of the bug#29310.

This bug is not a duplicate of bug 29310.

Bug 29310 is about a column with "ON UPDATE" being modified when using UPDATE. It applies only to MySQL 5.1

This bug is about the column being modified when using INSERT...ON DUPLICATE KEY and applies to MySQL 5.0 and 5.1 (maybe even earlier for all I know)

So while having similar symptoms, they are triggered in different ways. But I guess it's still possible that the fix for bug 29310 can also fix this one. Unfortunately, I don't have the resources to actually test that hypothesis.