[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-892) HQL parser does not resolve alias in ORDER BY clause

[Kevin Kelley (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=33002#action_33002 ] 

Kevin Kelley commented on HHH-892:
----------------------------------

I've hit a wall with this issue.  I have a lengthy query (generated by Hibernate 3.3.1 and Annotations 3.4.0) with a bunch of joins that fails (on DB2 v9.1) when trying to order by a particular column.  The generated SQL uses the real table name and column name.

  ... order by T_TMPLT_ELMNT_PRXY.SEQUENCE_NUM asc

If I comment out the @OrderBy annotation it runs fine. I then manually ran the SQL, with the order by clause, and replaced the table name with the table alias (this ran fine)

  ... order by elements0_1_.SEQUENCE_NUM asc

And finally I ran the same query with just the column alias in the order by clause and this also ran fine.

  ... order by SEQUENCE13_79_3_ asc

This appears to be a bug in hibernate as the SQL is entirely generated by hibernate (based on standard annotations) and will run fine is using the aliases.  It does seem strange that using the FQN of the column doesn't work from the database perspective.

Since I can't seem to sort the objects directly on the database, I'll use a TreeSet to reorder the objects based on the sequence number; it works, it's just not nearly as effecient.  I would be ecstatic if there was a fix for this.

> HQL parser does not resolve alias in ORDER  BY clause
> -----------------------------------------------------
>
>                 Key: HHH-892
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-892
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: query-hql
>    Affects Versions: 3.0.5
>         Environment: Hibernate 3.0.5, MySQL, Tomcat
>            Reporter: Guido Laures
>            Priority: Minor
>
> When using an alias for an ORDER BY clause this is not always correctly resolved. Example:
> SELECT SUM(A.x) AS mySum FROM MyClass AS A GROUP BY A.y ORDER BY mySum
> does not work because "mySum" is not resolved in the ORDER BY clause which results in an exception telling that mySum is an unknown column.
> Workaround (not to say "hack") is using:
> SELECT SUM(A.x) AS mySum FROM MyClass AS A GROUP BY A.y ORDER BY col_0_0_

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira