MySQL Bugs: #74757: Order by and limit not working as expected

Bug #74757	Order by and limit not working as expected
Submitted:	9 Nov 2014 20:12	Modified:	11 Dec 2014 19:11
Reporter:	Kamlesh Sharma	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.33	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I am using Sakila sample database and trying to execute following query using groovy:

select film_id as filmId, title, description, release_year as releaseYear, 
language_id as languageId, rental_duration as rentalDuration, rental_rate as rentalRate,length, replacement_cost as replacementCost from film  ORDER BY title desc LIMIT 20 OFFSET 180

It seems order by clause is not working, I should get records from id 820 to 801, but I am getting from id 181 to 200. I get proper result in Query browser but not from groovy SQL.

How to repeat:
Create sakila db. Execute quesru using groovy and print resultset using sql each row like:

db.eachRow(sqlStr) { row -> 
  films << row.toRowResult()
}

println films

Hi Kamlesh,

Thank you for this bug report.

This problem may be related with how groovy handles the query string before sending it to the Connector/J driver. We need more detail to be able to figure it out so, could you please turn on the general query log (http://dev.mysql.com/doc/refman/5.1/en/query-log.html) in your MySQL server and monitor how the query is really being sent to the server?

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".