MySQL Bugs: #56364: Different result with LIMIT 0,99 and ORDER BY in the same query

Bug #56364	Different result with LIMIT 0,99 and ORDER BY in the same query
Submitted:	30 Aug 2010 12:52	Modified:	16 Oct 2010 13:16
Reporter:	kinza saber	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.40	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	40

Description:
The Query of the form :

SELECT DISTINCT A.name AS A_name,B.name AS B_name,
CONCAT('compute-',CAST(C.num AS CHAR),'-',
CAST(d.num AS CHAR)) AS properties 
FROM (A,B,C,D) 
WHERE A.name IN ("Pattern") 
AND A.num = B.A_num
AND B.num = C.B_num
AND C.num = D.C_num
ORDER BY B.name ASC LIMIT 0,99 

the query is executed many time with differents values of LIMIT it means that we have :
first query LIMIT 0,99 
second query LIMIT 99,99
third query LIMIT 198,99
.......
until we have rows.

the problem is that the queries executed from 0,99 until 394,99 must give in total 1925 rows but they return a smaller result. Without LIMIT the query give 1925. 

How to repeat:
join any 3 tables of more than 1000 rows and use order by and limit 0,99 in the same query you will have missig result

What exact version of MySQL server, 5.1.x, do you use? Please, upload dump of that tables that demonstrate the problem for you.

the mysql server version is 5.1.40.

Please try latest released version 5.1.50 if still you get the same results please provide a complete test case (table dump file, queries and results). Thank you in advance.

The same result is gived when I use the the server 5.1.50 
the result is the same. there is the same problem. I will provide a dump file and the differents results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".