[hibernate-issues] [Hibernate-JIRA] Created: (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).
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira