MySQL Bugs: #58086: auto_inc in InnoDB have INSERT ... ON DUPLICATE KEY UPDATE != UPDATE

Bug #58086	auto_inc in InnoDB have INSERT ... ON DUPLICATE KEY UPDATE != UPDATE
Submitted:	9 Nov 2010 15:09	Modified:	30 Nov 2011 18:40
Reporter:	Mattias Jonsson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	John Russell	CPU Architecture:	Any

Description:
According to http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html 
'INSERT ... ON DUPLICATE KEY UPDATE' should have the same effect as 'UPDATE', but for auto_increment and InnoDB that is not true:
INSERT ... does update the internal auto_increment value, but UPDATE does not (see bug#54165)

How to repeat:
CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB;
INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = 0;
INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = 4;
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1;
a
4
5 <--------------------------------- is '2' below
TRUNCATE TABLE t1;
INSERT INTO t1 SET a = 1;
UPDATE t1 SET a = 4 WHERE a = 1;
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1;
a
2 <--------------------------------- is '5' above
4

As I understand:
http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html
INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = 4;
should have identical effect as
UPDATE t1 SET a = 4 WHERE a = 1;
which it does not have.

Suggested fix:
Since bug#54165 seems to be a doc's bug, I set this as a doc's bug too.

Technical background:
ha_innobase::get_auto_increment() is called from handler::update_auto_increment which is called from ha_innobase::write_row() in the INSERT step, which generates a new auto_increment value in InnoDB, therefore it is not the same as an UPDATE statement (according to bug#38839 it is intentional that UPDATE does not change the engines auto_increment value).

I would rather see that bug#54165 was fixed to also update the auto_increment value on UPDATE, rather than just document this as is.

Verified with 5.1.52.

Changing the example to say the effects are "similar" and adding this wording after the example:

(The effects are not identical for an InnoDB table where a is an
auto-increment column. With an auto-increment column, an INSERT
statement increases the auto-increment value but UPDATE does not.)