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 5:08	Modified:	30 Mar 2007 19:06
Reporter:	John David Duncan
Status:	Not a Bug
Category:	Server: General	Severity:	S2 (Serious)
Version:	All 5.0 and 5.1, 4.1, 5.2	OS:	Any (All)
Assigned to:	Evgeny Potemkin	Target Version:

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.