MySQL Bugs: #79937: Incorrect record updated using ON DUPLICATE KEY UPDATE processing

Bug #79937	Incorrect record updated using ON DUPLICATE KEY UPDATE processing
Submitted:	12 Jan 2016 15:42	Modified:	13 Jan 2016 12:15
Reporter:	Katherine Walker	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	On duplicate key zero primary key

Description:
If attempting to update an existing row using INSERT / ON DUPLICATE KEY UPDATE.., using a UNIQUE key when the table also has a numeric primary key (not auto increment) the row is not always updated.

If a row exists with a primary key of 0 then the specified row is not updated, and instead the row with a primary key of 0 is updated. If no row exists with a primary key of 0 then it works perfectly.

I do note that the manual pages say :-

"In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes."

but in this case it is explicit which row is to be updated.

How to repeat:
The issue can be demonstrated as follows:-

CREATE TABLE fred
(
    id int(11) NOT NULL,    
    code varchar(32) NOT NULL,
    stock int(5) NOT NULL,
    is_active tinyint(1) NOT NULL DEFAULT '1',
    PRIMARY KEY (id),
    UNIQUE KEY code (code)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO fred VALUES
(0, 'a', 123, 1),
(2, 'b', 234, 1),
(3, 'c', 345, 1);

With this data the following insert should update the 2nd row (primary key 2, unique index on code of b ), but instead it updates the 1st row with the primary key of zero and the unique index on code of a .

INSERT  INTO fred (code,stock,is_active) VALUES ('b',46,68) ON DUPLICATE KEY UPDATE stock = VALUES(stock), is_active = VALUES(is_active);

Hello Katherine,

Thank you for the report and test case.
Imho this is not a bug and expected behavior.

-- 5.6.28

drop table if exists fred;
CREATE TABLE fred
(
    id int(11) NOT NULL,    
    code varchar(32) NOT NULL,
    stock int(5) NOT NULL,
    is_active tinyint(1) NOT NULL DEFAULT '1',
    PRIMARY KEY (id),
    UNIQUE KEY code (code)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO fred VALUES
(0, 'a', 123, 1),
(2, 'b', 234, 1),
(3, 'c', 345, 1);

INSERT  INTO fred (code,stock,is_active) VALUES ('b',46,68) ON DUPLICATE KEY UPDATE stock = VALUES(stock), is_active = VALUES(is_active);

Imho, Field 'id' doesn't have a default value, and for numeric types, the default is 0(with strict mode, above statement will fail - ERROR 1364 (HY000): Field 'id' doesn't have a default value). So, here id=0, code='b' which matches 2 rows, and at the end only one row is updated. Hence, In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

mysql> select * from fred;
+----+------+-------+-----------+
| id | code | stock | is_active |
+----+------+-------+-----------+
|  0 | a    |    46 |        68 |
|  2 | b    |   234 |         1 |
|  3 | c    |   345 |         1 |
+----+------+-------+-----------+
3 rows in set (0.00 sec)

-- if id is included, and doesn't exists then 'b' that would cause a duplicate value in a UNIQUE index.

drop table if exists fred;
CREATE TABLE fred
(
    id int(11) NOT NULL,    
    code varchar(32) NOT NULL,
    stock int(5) NOT NULL,
    is_active tinyint(1) NOT NULL DEFAULT '1',
    PRIMARY KEY (id),
    UNIQUE KEY code (code)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO fred VALUES
(1, 'a', 123, 1),
(2, 'b', 234, 1),
(3, 'c', 345, 1);

INSERT  INTO fred (code,stock,is_active) VALUES ('b',46,68) ON DUPLICATE KEY UPDATE stock = VALUES(stock), is_active = VALUES(is_active);

mysql> select * from fred;
+----+------+-------+-----------+
| id | code | stock | is_active |
+----+------+-------+-----------+
|  1 | a    |   123 |         1 |
|  2 | b    |    46 |        68 |
|  3 | c    |   345 |         1 |
+----+------+-------+-----------+
3 rows in set (0.00 sec)

Thanks,
Umesh

Thank you for your investigation.

While I understand your reasoning I struggle to accept that a defaulted value on an INSERT should take priority over a specified value when matching an existing record.