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

[Robin Sander (JIRA)]

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

Robin Sander commented on HHH-6385:
-----------------------------------

I forgot to mention that the NPE occured when PostgreSQLDialect was used. I know that Postgres does the cast automatically (so no AvgWithArgumentCastFunction involved here) but now I had the situation that for testing (=hsqdlDB) the query needs an additional cast while in production (=postgres) the addional cast led to a NPE, bummer!

> 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