Description:
UPDATE ... ORDER BY does not work properly with InnoDB when the WHERE clause references an indexed column. When ORDER BY is specified, the UPDATE statement will not find any matching rows to update, even though a similar SELECT statement indicates that rows to be updated are present in the table. The correct behavior of UPDATE ... ORDER BY is described in section 6.4.5 of the manual, at http://www.mysql.com/doc/en/UPDATE.html . MyISAM tables function as advertised.
Table Set-Up:
create table innodb (
id int primary key auto_increment,
fk int,
index index_fk (fk)
) type=innodb;
insert into innodb (id) values (null),(null),(null),(null),(null);
+----+------+
| id | fk |
+----+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
+----+------+
Then, execute:
update innodb set fk=69 where fk is null order by id limit 1;
Observed result:
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
+----+------+
| id | fk |
+----+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
+----+------+
Expected result:
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
+----+------+
| id | fk |
+----+------+
| 1 | 69 |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
+----+------+
Workaround: Do not use ORDER BY in UPDATE on InnoDB tables.
How to repeat:
# The following two sets of operations are identical except for the table
# type. The first uses InnoDB and exposes the bug. The second uses MyISAM,
# where the correct behavior is observed.
create table innodb (
id int primary key auto_increment,
fk int,
index index_fk (fk)
) type=innodb;
insert into innodb (id) values (null),(null),(null),(null),(null);
# The WHERE ... ORDER BY ... LIMIT clause functions properly with SELECT.
select * from innodb where fk is null order by id limit 1;
# But it does not function properly with UPDATE, which will not find anything
# to update.
update innodb set fk=69 where fk is null order by id limit 1;
# This should return one row if the update was made as expected.
select * from innodb where fk=69;
# In contrast, it functions properly with a MyISAM table:
create table myisam (
id int primary key auto_increment,
fk int,
index index_fk (fk)
) type=myisam;
insert into myisam (id) values (null),(null),(null),(null),(null);
select * from myisam where fk is null order by id limit 1;
update myisam set fk=69 where fk is null order by id limit 1;
select * from myisam where fk=69;
