Bug #1024 DELETE with WHERE, ORDER and LIMIT doesn't work as intended

• Submitted: 10 Aug 2003 15:47
• Modified: 10 Dec 2003 6:52
• Reporter: Paul Pogonyshev
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.12
• OS: GNU/Linux (Red Hat 8.0)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[10 Aug 2003 15:47] Paul Pogonyshev

Description:
When a DELETE operator has WHERE, ORDER BY and LIMIT clauses, it doesn't work as 
intended.  ORDER BY seems to be ignored and it deletes records in internal order (as they are 
stored). 

How to repeat:
Here is a sequence of commands that reproduces the bug (at least here): 
 
mysql> CREATE TABLE test(id int auto_increment primary key, id2 int, index(id2)); 
mysql> INSERT INTO test SET id2=0; 
mysql> INSERT INTO test SET id2=0; 
mysql> INSERT INTO test SET id2=0; 
mysql> DELETE FROM test WHERE id=1; 
mysql> INSERT INTO test SET id2=0; 
mysql> SELECT * FROM test; 
+----+------+ 
| id | id2  | 
+----+------+ 
|  4 |    0 | 
|  2 |    0 | 
|  3 |    0 | 
+----+------+ 
3 rows in set (0.00 sec) 
 
mysql> DELETE FROM test WHERE id2 = 0 ORDER BY id LIMIT 1; 
mysql> # should have deleted WHERE id=2 
mysql> SELECT * FROM test; 
+----+------+ 
| id | id2  | 
+----+------+ 
|  2 |    0 | 
|  3 |    0 | 
+----+------+ 
2 rows in set (0.00 sec)

[11 Aug 2003 3:13] Alexander Keremidarski

Just to add that without WHERE clause it works as expected.
With user provided test case:

DELETE FROM test ORDER BY id LIMIT 1;

Correctly deletes row with id=2

[10 Dec 2003 6:52] Michael Widenius

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in 4.0.17