MySQL Bugs: #64290: INSERT ON DUPLICATE KEY UPDATE can't work with primary key value equals zero

Bug #64290	INSERT ON DUPLICATE KEY UPDATE can't work with primary key value equals zero
Submitted:	10 Feb 2012 6:24	Modified:	10 Feb 2012 15:41
Reporter:	xu qing	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.15	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	insert on duplicate key update

Description:
I had a table only include 2 fields id and name. id is primary key and auto_increment, name is char field.
In this table have a record like this: id = 0,name = 'test'. 
I use follow sql to execute on this table.

INSERT INTO test (id,name) VALUES (0,'test') ON DUPLICATE KEY UPDATE name='test';

After I execute this query and check the data, a new row inserted,name = 'test' but id is not 0.

How to repeat:
Follow these commands:

CREATE TABLE `test` (`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(100) NOT NULL) ENGINE = Innodb;

INSERT INTO `test` (`id`,`name`) VALUES (1,'test');
INSERT INTO `test` (`id`,`name`) VALUES (2,'test1');
INSERT INTO `test` (`id`,`name`) VALUES (3,'test2');
UPDATE `test` SET `id`=0 WHERE `id`=1;

INSERT INTO `test` (`id`,`name`) VALUES (0,'test3') ON DUPLICATE KEY UPDATE name='test3';

You can find the data like this
id                  name
____________  ____________________
0             test
2             test1
3             test2
4             test3

But the result I wanted is follow:
id                  name
____________  ____________________
0             test3
2             test1
3             test2

Suggested fix:
Don't use 0 as a value in auto_increment column. or not set the column to auto_increment.

Please, send the output of:

select @@sql_mode;

from your environment.

The result of SELECT @@SQL_MODE;
mysql> select @@sql_mode;                                                       
+------------+                                                                  
| @@sql_mode |                                                                  
+------------+                                                                  
|            |                                                                  
+------------+                                                                  
1 row in set (0.00 sec)

Please, execute:

set session sql_mode='NO_AUTO_VALUE_ON_ZERO';

and then try your test again. Read http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_no_auto_value_on_zero for the details.

It works.

Still a bug IMHO.  An existing row is specified to be updated, but it is not.

Peter
(not a MySQL person)

Depending on SQL mode value 0 inserted into auto_increment column either generates new row with next auto incremented value every time, or inserts row with value 0 literally. I do not see any bug here with both cases.