Tuesday, 24 September, 2013
Trouble with MySQL’s lower() function
If you are using MySQL 5.6.x with Hibernate and your query results don’t show up in the expected order it is possible that you run into this MySQL bug.
Today we ran into this issue with GORM (which uses Hibernate). A simple findAll() returned the results in invalid order:
List users = User.findAll([max: 10, offset: 0, sort: "name", order: "asc"]
Other than expected the resulting list of users was not ordered by the users name.
When looking at the query generated by hibernate we saw the following order by clause:
... order by lower(this_.name) asc limit ?
After testing this query in different variations against different MySQL versions, we noticed that the lower() function does not work as intended in MySQL 5.6.11. Shortly after that, we found the bug ticket mentioned above.
