Bug #241 UPDATE ... ORDER BY does not work with InnoDB
Submitted: 7 Apr 2003 17:24 Modified: 23 Apr 2003 2:27
Reporter: Mark Mentovai Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.0.12 OS:sparc-sun-solaris2.8
Assigned to: Michael Widenius

[7 Apr 2003 17:24] Mark Mentovai
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;
[22 Apr 2003 10:41] Heikki Tuuri
The code in sql_update.cc seems to ignore the sorted file for all table types. Also for MyISAM tables

UPDATE ... ORDER BY ... LIMIT 1

updates the rows in an arbitrary order, not in the order specified in ORDER BY. See my email sent to MySQL AB mailing lists a few days ago.

I modified 4.0.13 so that InnoDB now updates some rows, but the general bug that the order is not right remains.
[23 Apr 2003 2:27] Michael Widenius
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

Fix will appear in 4.0.13 and 4.1.1