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

[Nate (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-892?page=comments#action_25956 ] 

Nate commented on HHH-892:
--------------------------

The workaround mentioned above no longer works (at least not with 3.2.x).  I tried it and I get an exception saying "org.hibernate.util.JDBCExceptionReporter  - ERROR: column "col_1_0_" does not exist" yet when I look at the SQL generated, that SHOULD be a valid column:

[INFO] [talledLocalContainer] QUERY = select point.id, (acos(cos(radians(:latitude))*cos(radians(:longitude))*cos(radians(point.latitude))*cos(radians(point.longitude))+cos(radians(:latitude))*sin(radians(:longitude))*cos(radians(point.latitude))*sin(radians(point.longitude))+sin(radians(:latitude))*sin(radians(point.latitude)))*3963.189) as distance from com.waypointdepot.domain.Waypoint point where col_1_0_ <= :maxDistance order by col_1_0_
[INFO] [talledLocalContainer] Hibernate: 
[INFO] [talledLocalContainer]     select
[INFO] [talledLocalContainer]         waypoint0_.id as col_0_0_,
[INFO] [talledLocalContainer]         acos(cos(radians(?))*cos(radians(?))*cos(radians(waypoint0_.latitude))*cos(radians(waypoint0_.longitude))+cos(radians(?))*sin(radians(?))*cos(radians(waypoint0_.latitude))*sin(radians(waypoint0_.longitude))+sin(radians(?))*sin(radians(waypoint0_.latitude)))*3963.189 as col_1_0_ 
[INFO] [talledLocalContainer]     from
[INFO] [talledLocalContainer]         waypoint waypoint0_ 
[INFO] [talledLocalContainer]     where
[INFO] [talledLocalContainer]         col_1_0_<=? 
[INFO] [talledLocalContainer]     order by
[INFO] [talledLocalContainer]         col_1_0_
[INFO] [talledLocalContainer] 15096 [http-8080-Processor24] WARN  org.hibernate.util.JDBCExceptionReporter  - SQL Error: 0, SQLState: 42703
[INFO] [talledLocalContainer] 15096 [http-8080-Processor24] ERROR org.hibernate.util.JDBCExceptionReporter  - ERROR: column "col_1_0_" does not exist


I cannot believe this is classified as a new feature request.  This is clearly broken.

> HQL parser does not resolve alias in ORDER  BY clause
> -----------------------------------------------------
>
>          Key: HHH-892
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-892
>      Project: Hibernate3
>         Type: New Feature

>     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