MySQL Bugs: #68360: Duplicate entry for key while updating

Bug #68360	Duplicate entry for key while updating
Submitted:	13 Feb 2013 10:36	Modified:	15 Feb 2013 18:00
Reporter:	Andreas Mller	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.28, 5.0.97, 5.1.69, 5.5.31, 5.7.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	key violation update

Description:
Updating multiple rows in one single statement fails with key violation if temporary a key value is douplicated even if there is no key violation at the end of this statement.

If suggest the same is happening within a multi statement transaction.

Because it is a single statement (or a single transaction) temporary states should not result in failing. The example below works on mssql or oracle without any problems.

How to repeat:
+----+-----------+------------+-------------+-------------+-------------+
| id | id_parent | sort_order | some_data   | other_data  | more_data   |
+----+-----------+------------+-------------+-------------+-------------+
| 1  |         1 |          1 | lorem ipsum | lorem ipsum | lorem ipsum |
| 2  |         1 |          2 | lorem ipsum | lorem ipsum | lorem ipsum |
| 3  |         1 |          3 | lorem ipsum | lorem ipsum | lorem ipsum |
+----+-----------+------------+-------------+-------------+-------------+

unique key on (ip_parent,sort_order)

UPDATE table SET sort_order=sort_order+1 WHERE id_parent=1;

Suggested fix:
Check key violations only at the end of a single statement/transaction.

Workaround: ... ORDER BY sort_order DESC

MySQL does not defer any constraint checks until commit, everything is checked immediately, and for unique keys i don't even thing deferring the check makes sense ... is there any other DB product out there that works this way?

For foreign key constraints the limitation is documented on 

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

" In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT. "

As described it works i.e. with ms-sql or oracle.

The workaround works for me but I think it is a violation of the sql idea. A single statement should work if the result is correct.

Thank you for the reasonable feature request.

Test case:

create table t1(f1 int unique, f2 int);
insert into t1 values (1,1), (2,1), (3,1);
select * from t1;
update t1 set f1 = f1 + 1 where f2 = 1;
select * from t1;

PostgreSQL works like MySQL:

test=# update t1 set f1 = f1 + 1 where f2 = 1;
ERROR:  duplicate key value violates unique constraint "t1_f1_key"