MySQL Bugs: #39766: Physical order of rows in table alters update behavior of unique index

Bug #39766	Physical order of rows in table alters update behavior of unique index
Submitted:	1 Oct 2008 2:31	Modified:	23 Oct 2008 23:04
Reporter:	John Miller	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S2 (Serious)
Version:	5.0.60-log, 5.0.66a, 5.1.28	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
See bug #18913 for initial report.  Confirmed in 5.0.60 for Heap storage engine.  No Heap storage engine category so putting report back in General.

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.

How to repeat:
drop table if exists t;
create table t (i int not null) engine heap;
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) engine heap;
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;

Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.66a-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists t;
Query OK, 0 rows affected (0.06 sec)

mysql> create table t (i int not null) engine heap;
Query OK, 0 rows affected (0.05 sec)

mysql> create unique index IX_t on t(i);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t (i) values (2), (1);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update t set i = i + 1;  #succeeds
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t (i int not null) engine heap;
Query OK, 0 rows affected (0.06 sec)

mysql> create unique index IX_t on t(i);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t (i) values (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update t set i = i + 1;  #fails
ERROR 1062 (23000): Duplicate entry '2' for key 1

The workaround is:

mysql> update t set i = i + 1 order by i desc;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

This is the expected documented behavior:

"Suppose that a table t contains a column id  that has a unique index.
The following statement could fail with a duplicate-key
error, depending on the order in which rows are updated:
UPDATE t SET id = id + 1;
For example, if the table contains 1 and 2 in the id
column and 1 is updated to 2 before 2 is updated to 3,
an error occurs."
http://dev.mysql.com/doc/refman/5.0/en/update.html