MySQL Bugs: #27387: Error checking is incorrect in INSERT ... ON DUPLICATE KEY UPDATE

Bug #27387	Error checking is incorrect in INSERT ... ON DUPLICATE KEY UPDATE
Submitted:	23 Mar 2007 4:08	Modified:	30 Mar 2007 17:06
Reporter:	John David Duncan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	All 5.0 and 5.1, 4.1, 5.2	OS:	Any (All)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
In INSERT ... ON DUPLICATE KEY UPDATE, in the case of a duplicate key, the whole statement fails in some cases when it should succeed.

How to repeat:
# Create a table with a not-null column
create table t ( a int not null primary key , b char(10) , c int not null );

# Insert some data 
insert into t values (1, "red" , 13 );
insert into t values (2, "green", 5);

# This statement works correctly.
insert into t values (1,  "blue", 4 ) on duplicate key update b = "blue" ;

# Notice that the update statement (not the insert) was applied.  (c is still equal to 13):
select * from t where a = 1;

# This statement (correctly) fails because of the non-null constraint:
insert into t values (3, "blue", null) on duplicate key update b = "blue";

# Here is the bug.  This update should not fail.
insert into t values (2, "blue", null) on duplicate key update b = "blue";

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of Bug #27210

No, this is not a duplicate of #27210.   This is a very different bug which is present in all versions of mysql and with all storage engines.

Thank you for the additional comment.

Verified as described.

All versions are affected.

This is definitely not a bug.

The statement is called INSERT ... ON DUPLICATE KEY UPDATE.

Its semantic is:
1.  try to insert a row.
2. if the insert operation returns an error "DUPLICATE KEY" perform update.

Apparently the customer needs a different semantics:
1. try to insert a row.
2. if the insert operation returns ANY errorperform update.