The following very simple example works as expected:
Code:
SELECT user.id, user.name FROM User AS user ORDER BY user.id
However, if I introduce a column alias in the SELECT clause and try and use it in the ORDER BY clause, it fails:
Code:
SELECT user.id AS userId FROM User AS user ORDER BY userId
When I look at the SQL that has been generated, Hibernate changes the userId alias in the SELECT to some internal identifier (col_1_0_), but does not use the identifier in the ORDER BY clause, leaving the database to try and ORDER BY the original userId alias that no longer exists in the query.
Given that the parser allows the query, I've assumed that it is legitimate to alias the column like this, although I've not seen this documented in the Hibernate manual. Obviously, this is a silly example - in real life, I have a sub-query as one of the columns in the select, and have to alias it in order to sort by it in the order-by clause.
Should I be able to do this? If so, I'll isolate this into a test case and submit to Jira; at the moment all of this is entagled in my application.
FYI, the fairly unpleasant workarounds are to either use the internal identifier in the ORDER BY clause:
Code:
SELECT user.id AS userId FROM User AS user ORDER BY col_1_0_
Or revert to SQL.
I am using Hibernate 3.1.3, and MySQL 5.
Thanks in advance for any help, Alex.
Login
FAQ
Search
