[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-6385) Order by avg() does not use function registered by dialect (e.g. AvgWithArgumentCastFunction)

Order by avg() does not use function registered by dialect (e.g. AvgWithArgumentCastFunction) --------------------------------------------------------------------------------------------- Key: HHH-6385 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6385 Project: Hibernate Core Issue Type: Bug Affects Versions: 3.6.5 Environment: Java 6, JPA 2, HsqlDB 2 Reporter: Robin Sander Priority: Minor Some dialects register a special function for the aggregate function 'avg'. For example, HSQLDialect calls {{registerFunction("avg", new AvgWithArgumentCastFunction("double"))}} which is used if {{avg}} appears in a select statement but not if {{avg}} appears in an {{order by}} statement which leads to wrong results. For exampe, the following query {code} SELECT v.target.id, count(v), avg(v.rating) FROM Vote v group by v.target.id having count(v) >= 3 ORDER BY avg(v.rating) desc, count(v) desc {code} gets translated into {code} select vote0_.target as col_0_0_, count(vote0_.id) as col_1_0_, avg(cast(vote0_.rating as double)) as col_2_0_ from vote vote0_ group by vote0_.target having count(vote0_.id)>=3 order by avg(vote0_.rating) desc, count(vote0_.id) desc {code} So the workaround is to use an explicit cast in the order by clause: {code} SELECT v.target.id, count(v), avg(v.rating) FROM Vote v group by v.target.id having count(v) >= 3 ORDER BY avg(cast(v.rating as double)) desc, count(v) desc {code} This gets translated into the correct SQL query: {code} select vote0_.target as col_0_0_, count(vote0_.id) as col_1_0_, avg(cast(vote0_.rating as double)) as col_2_0_ from vote vote0_ group by vote0_.target having count(vote0_.id)>=3 order by avg(cast(vote0_.rating as double)) desc, count(vote0_.id) desc {code} While this workaround is pretty ease, I've stumbled across this bug while using a JPA criteria query for the same purpose. With the criteria query I had to use {{cb.avg(vote.get(Vote_.rating).as(Double.TYPE))}} as a workaround but unfortunately this led to a NPE in {{org.hibernate.dialect.function.CastFunction.render(CastFunction.java:56)}} (maybe because of HHH-6024).