MySQL Bugs: #35856: false unique constraint violation on multiple updates

Bug #35856	false unique constraint violation on multiple updates
Submitted:	5 Apr 2008 23:31	Modified:	16 Oct 2008 4:30
Reporter:	Don Cohen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I'm not sure whether this complies with the sql spec - so far I don't see how to find out.  Perhaps you can tell me that too.
I define a table with a unique column:
 CREATE TABLE testtable(                                                 
     PRIMARY KEY (ID),                                                           
     ID INT(11) AUTO_INCREMENT NOT NULL,                                         
     WBS VARCHAR(150) unique not null                                            
   ) ENGINE=INNODB;
Now insert a few rows:
insert into testtable(wbs) values ('a'),('aa');
And finally try this update:
 update testtable set wbs = concat('a', wbs);
ERROR 1062 (23000): Duplicate entry 'aa' for key 2

Does this meet the spec?  I'd have thought that I'd get a complaint only if the END RESULT of the update caused duplicate entries.  Here the end result should be 'aa' and 'aaa', which does not violate the constraint.  So I expected the update to work.

How to repeat:
do the three steps in description

Suggested fix:
First I want to know whether this is really legal sql behavior.
If not, then I hope someone there knows how to fix it.

This is a known and documented problem in MySQL that will be fixed eventually. Please, read http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html:

"Deviation from SQL standards: Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY  constraints row-by-row. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed."

This is an odd place to document the problem with unique constraints, and for that matter even foreign key constraints.  After all, what is supposed to lead me to look at this if I think I know what a foreign key constraint means?  Is there one place where I can find other such problems so I don't have to be unpleasantly surprised more than once more?
If not I suggest that this would be a valuable addition to the documentation.

Duplicate of Bug #5573.