Bug #18913 Physical order of rows in table alters update behavior of unique index
Submitted: 9 Apr 2006 2:01 Modified: 17 Jan 2014 15:39
Reporter: John Miller Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.0.21-BK, 5.0.19,5.7.4 OS:Any
Assigned to: CPU Architecture:Any

[9 Apr 2006 2:01] John Miller 
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;
[9 Apr 2006 14:19] Baron Schwartz 
I can duplicate on Gentoo/AMD64 5.0.19-log.
[11 Apr 2006 15:21] Valeriy Kravchuk 
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.
[17 Apr 2006 1:42] Igor Babaev 
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.
[30 May 2006 17:14] Paul DuBois 
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.
[30 Sep 2008 14:57] Konstantin Osipov 
This report is for MyISAM. The bug needs to be fixed separately in each storage engine.