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

[Robin Sander (JIRA)]

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).


-- 
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