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:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.40 OS:Any
Assigned to: CPU Architecture:Any
Tags: 40

[30 Aug 2010 12:52] kinza saber
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 
WHERE A.name IN ("Pattern") 
AND A.num = B.A_num
AND B.num = C.B_num
AND C.num = D.C_num

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
[30 Aug 2010 13:05] Valeriy Kravchuk
What exact version of MySQL server, 5.1.x, do you use? Please, upload dump of that tables that demonstrate the problem for you.
[1 Sep 2010 13:41] kinza saber
the mysql server version is 5.1.40.
[1 Sep 2010 13:46] MySQL Verification Team
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.
[15 Sep 2010 15:41] kinza saber
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.
[16 Oct 2010 23:00] Bugs System
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".