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

[Gail Badner (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-892?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Gail Badner updated HHH-892:
----------------------------

    Attachment: HHH-892.patch

I'm attaching a patch of my first attempt at a fix.

It replaces aliases for HQL select expressions that are used in ORDER BY clauses with the actual SQL column aliases that Hibernate generated.

For example:

    HQL: select c.name as myname FROM org.hibernate.test.jpa.Item c ORDER BY myname

results in Hibernate generating:

    SQL: select item0_.NAME as col_0_0_ from EJB3_ITEM item0_ order by col_0_0_

>From discussions with Steve Ebersole, I understand that this may not work for all dialects, so something will need to be added to Dialect to indicate how aliases for HQL select expressions should be replaced.

The other possibilities are to replace the alias for an HQL select expression with:
1) the position(s) of the SQL select expressions that correspond with the HQL select expression alias (e.g., select item0_.NAME as col_0_0_ from EJB3_ITEM item0_ order by 1)
2) the actual SQL columns that correspond to the aliased HQL select expression (this probably will not work for all dialects).

The fix in the patch will not work reliably if a position, rather than an alias is used in the HQL ORDER BY clause. That's because the position in the HQL will simply "fall through" to the generated SQL. Since HQL select expressions can result in multiple SQL expressions, the position of an HQL expression could translate to a different (or multiple) positions in the generated SQL.

Does anyone know about a dialect that should use positions instead of column aliases in the ORDER BY clause?

Any help working out these details would be most appreciated. I'd really like to get this fixed as well.

Thanks,
Gail

> 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.5.4, 3.6.0.Beta1
>         Environment: Hibernate 3.0.5, MySQL, Tomcat
>            Reporter: Guido Laures
>            Assignee: Gail Badner
>             Fix For: 3.5.x, 3.6.x
>
>         Attachments: HHH-892.patch, HibernateHavingAliasTest.java
>
>
> 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