MySQL Bugs: #35605: ORDER BY `...` DESC LIMIT 1 return wrong row

Bug #35605	ORDER BY `...` DESC LIMIT 1 return wrong row
Submitted:	27 Mar 2008 13:40	Modified:	31 Oct 2008 22:47
Reporter:	Christian Meisinger	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.1.23-ndb-6.3.10-telco	OS:	Linux (SuSE 9.3)
Assigned to:		CPU Architecture:	Any
Tags:	DESC, limit, order

Description:
the query
SELECT `id` FROM `projects_history` WHERE `project_id` = 22 ORDER BY `id` DESC;
returns the correct row as first row.
BUT
SELECT `id` FROM `projects_history` WHERE `project_id` = 22 ORDER BY `id` DESC LIMIT 1;
returns a different row.

How to repeat:
table creation info and data in my attachment...

SELECT `id` FROM `projects_history` WHERE `project_id` = 22 ORDER BY `id` DESC;
SELECT `id` FROM `projects_history` WHERE `project_id` = 22 ORDER BY `id` DESC LIMIT 1;

Table data for `projects_history`

Attachment: projects_history.sql (text/plain), 221.69 KiB.

Thank you for the bug report. I wasn't able to repeat the behavior reported with latest source server, so I assume it was fixed somehow since version reported.
So please wait for next release or comment here if the below result it isn't the
expected one. Thanks in advance.

mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  | 
| version                 | 5.1.24-rc-debug     | 
| version_comment         | Source distribution | 
| version_compile_machine | i686                | 
| version_compile_os      | pc-linux-gnu        | 
+-------------------------+---------------------+
5 rows in set (0.03 sec)

mysql> SELECT `id` FROM `projects_history` WHERE `project_id` = 22 ORDER BY `id` DESC;
+------+
| id   |
+------+
| 2373 | 
<cut>
| 1540 | 
+------+
122 rows in set (0.01 sec)

mysql> SELECT `id` FROM `projects_history` WHERE `project_id` = 22 ORDER BY `id` DESC LIMIT 1;
+------+
| id   |
+------+
| 2373 | 
+------+
1 row in set (0.01 sec)

mysql>

Updated with 5.1.24-rc from bk repository -> works now :)
thx.

Just to complete this.

I tested with 5.1.23-ndb-6.3.11-telco and it's fixed there.

Duplicate of bug #37830 which is fixed in 5.0.68, 5.1.28 and 6.0.7.