Bug #18913 Physical order of rows in table alters update behavior of unique index

Description:
The physical order of rows in a table determines whether an update causes an error or succeed when a unique index exists.  In one order the update succeeds.  In the other it causes "Duplicate entry '...' for key ...".  I would not expect the physical order of the rows to affect the behavior in any way.  It should either always fail or always succeed.

Also tested and failed in MySQL 4.1.18 on FreeBSD 4.10-r6 and 5.0.15-nt on WindowXP Pro SP2.

Also test with table engines MyISAM, InnoDB, and Heap.

How to repeat:
drop table if exists t;
create table t (i int not null);
create unique index IX_t on t(i);

insert into t (i) values (2), (1);
update t set i = i + 1;  #succeeds

drop table t;
create table t (i int not null);
create unique index IX_t on t(i);

insert into t (i) values (1), (2);
update t set i = i + 1;  #fails

drop table t;

I can duplicate on Gentoo/AMD64 5.0.19-log.

Thank you for a bug report. Verified just as described on 5.0.21-BK (ChangeSet@1.2148.1.1, 2006-04-10 14:30:00-07:00) on Linux. Same results for both MyISAM and InnoDB storage engines.

we are not compatible with the standard here.
Standards requires performing updates on the initial state of the table.
We don't do it. That's why an update of a row may conflict with an
existing one w.r.t. the primary key constraint and result in a failure of
transaction.

The workaround is to add ORDER BY i DESC to the UPDATE
statement to force the rows to be updated in non-conflicting
order.  I'll add a note about this to the UPDATE page of the
manual.

This report is for MyISAM. The bug needs to be fixed separately in each storage engine.