MySQL Bugs: #3599: DELETE with WHERE, ORDER and LIMIT doesn't work as intended

Bug #3599	DELETE with WHERE, ORDER and LIMIT doesn't work as intended
Submitted:	29 Apr 2004 12:57	Modified:	1 May 2004 1:44
Reporter:	Barnaby Puttick	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.1-alpha	OS:	FreeBSD (FreeBSD 5.1-RELEASE)
Assigned to:		CPU Architecture:	Any

Description:
I've seen this bug reported in #1024 for 4.0.12. I have also repeated the test data supplied and produced the same unexpected results.

To recap:
Doing a DELETE with WHERE, ORDER BY and LIMIT will delete in the order of insertion and not the ORDER BY. However without the WHERE works as expected.

How to repeat:

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `id2` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `id2` (`id2`)
) TYPE=MyISAM DEFAULT CHARSET=latin1 ;

INSERT INTO test SET id2=0;
INSERT INTO test SET id2=0;
DELETE FROM test WHERE id=1;
INSERT INTO test SET id2=0;
INSERT INTO test SET id2=0;

SELECT * FROM test;

+----+------+
| id | id2  |
+----+------+
|  3 |    0 |
|  2 |    0 |
|  4 |    0 |
+----+------+

# All OK so far

DELETE FROM test WHERE id2 = 0 ORDER BY id LIMIT 1;
SELECT * FROM test;
+----+------+
| id | id2  |
+----+------+
|  2 |    0 |
|  4 |    0 |
+----+------+

#id 2 should have been deleted not 3

I tested with 4.1.1a and I wasn't able to repeat:

mysql> SELECT * FROM test;
+----+------+
| id | id2  |
+----+------+
|  3 |    0 |
|  2 |    0 |
|  4 |    0 |
+----+------+
3 rows in set (0.06 sec)

mysql> DELETE FROM test WHERE id2 = 0 ORDER BY id LIMIT 1;
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM test;
+----+------+
| id | id2  |
+----+------+
|  3 |    0 |
|  4 |    0 |
+----+------+
2 rows in set (0.00 sec)

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

4.1.1 was released on Dec 4 2003; this bug was fixed in 4.0 on Dec 10, and the fix was merged into 4.1 on Dec 11th.

It will be available in 4.1.2.

Oops.  I selected the wrong comment.  4.1.2 is not available for download yet, except in source form from our BitKeeper repository.  See the manual for details on testing out the latest source code:

http://dev.mysql.com/doc/mysql/en/Installing_source_tree.html