Bug #44726 LIMIT caused wrong row in result
Submitted: 7 May 2009 18:16 Modified: 7 May 2009 18:52
Reporter: Anton Makarenko Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.51-nt, 5.0.67 FreeBSD OS:Any
Assigned to: CPU Architecture:Any

[7 May 2009 18:16] Anton Makarenko 
Description:
Adding LIMIT 1 to a simple select query returns wrong row.

Reproduced on MySQL 5.0.51-nt, 5.0.67 FreeBSD.
Works fine on MySQL 4.1

Modifying table index in the following way - helps:
ALTER TABLE `admin_passwords`
  ADD INDEX `IDX_USER_ID` (`password_id`,`user_id`),
  DROP INDEX `IDX_USER_ID`;

Switching to MyISAM engine - also helps.

How to repeat:
See table dump attached in private comments.

SELECT * FROM admin_passwords WHERE user_id = 4 ORDER BY password_id DESC LIMIT 1

result:
4;4;"e7d0b0a69ab88b58374b0ec596877753d849afc3c36764f910292d029b111b51";12465550;1238779036

expected:
10;4;"48eb1ba3d26ef3ec28be88db25710a29bf795d80ece9ac9676575e3ba473cd35";1249490442;1241714442
[7 May 2009 18:42] MySQL Verification Team 
Thank you for the bug report. Your server version is quite older i.e: I couldn't repeat with current source server. Please upgrade to latest released version or wait for the next release. Thanks in advance.

mysql 5.0 > SELECT * FROM admin_passwords WHERE user_id = 4 ORDER BY password_id DESC LIMIT 1\G
*************************** 1. row ***************************
  password_id: 10
      user_id: 4
password_hash: 48eb1ba3d26ef3ec28be88db25710a29bf795d80ece9ac9676575e3ba473cd35
      expires: 1249490442
 last_updated: 1241714442
1 row in set (0.00 sec)

mysql 5.0 > show variables like "%version%";
+-------------------------+-------------------------------+
| Variable_name           | Value                         |
+-------------------------+-------------------------------+
| protocol_version        | 10                            |
| version                 | 5.0.82-Win X64 revno 2780-log |
| version_comment         | Source distribution           |
| version_compile_machine | unknown                       |
| version_compile_os      | Win64                         |
+-------------------------+-------------------------------+
5 rows in set (0.00 sec)

mysql 5.0 >
[7 May 2009 18:52] Anton Makarenko 
Upgraded to 5.0.81-community-nt: issue disappeared.