MySQL Bugs: #55115: LIMIT with Order By Gives Wrong Result

Bug #55115	LIMIT with Order By Gives Wrong Result
Submitted:	9 Jul 2010 8:22	Modified:	9 Jul 2010 10:48
Reporter:	Abhishek Chikane	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1.39	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	LIMIT+ORDERBY WRONG DATA

Description:
I have myisam table with 102633 records in the table. When I execute the following query it gives correct data.

SELECT *
FROM process_data p
where is_active = 1
and process_id = 6
order by id
limit 0,15;

It fetched records with process_id = 6

But if I use following query it gives wrong data.

SELECT *
FROM process_data p
where is_active = 1
and process_id = 6
order by id desc
limit 0,15;

It fetches records for process_id = 8

This issue is with this much data as with less data the results are correct.

How to repeat:
Create this table 

DROP TABLE IF EXISTS `process_data`;
CREATE TABLE  `process_data` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `PROCESS_ID` int(11) DEFAULT NULL,
  `START_TIME` datetime DEFAULT NULL,
  `END_TIME` datetime DEFAULT NULL,
  `REVIEW` varchar(100) DEFAULT NULL,
  `METRICS` varchar(45) DEFAULT NULL,
  `IS_ACTIVE` tinyint(1) DEFAULT NULL,
  `CREATED_BY` varchar(45) DEFAULT NULL,
  `CREATED_DATE` datetime DEFAULT NULL,
  `MODIFIED_DATE` datetime DEFAULT NULL,
  `MODIFIED_BY` varchar(45) DEFAULT NULL,
  `COL_1` varchar(255) DEFAULT NULL,
  `COL_2` varchar(255) DEFAULT NULL,
  `COL_3` varchar(255) DEFAULT NULL,
  `COL_4` varchar(255) DEFAULT NULL,
  `COL_5` varchar(255) DEFAULT NULL,
  `COL_6` varchar(255) DEFAULT NULL,
  `COL_7` datetime DEFAULT NULL,
  `COL_8` varchar(255) DEFAULT NULL,
  `COL_9` int(11) DEFAULT NULL,
  `COL_10` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `PROCESS_ID` (`PROCESS_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

I don't know if you are ok with the data of so many records. 
If you want I can attach the data.

100000+ rows of data should be OK, if you'll compress the dump. So, please, upload them.

Also, please, specify the exact version used, 5.1.x.

Sorry my server version was 6 community server alpha version. And that was the issue.

As I changed it to stable version. And the issue resolved :-).

Still thanks for the quick reply.

This problem happened only in early 6.0.x version it seems.