Hibernate version: 3.0.5
Name and version of the database you are using: Derby, Postgres, SQLServer
We're using Hibernate 3.0.5 and need to support different database platforms (presently Derby, Postgres and SQL Server). Ideally this should require no code change. We've had great success with Hibernate, but have found some issues with reporting aggregate queries.
We want to perform some reporting queries to calculate some statistics involving averages.
e.g. the HQL might look something like this
Code:
select
field, avg(int_field)
from
ClassName
group by field
However the field we're averaging is an integer column, and it seems that the type returned is DB dependent and usually results in an integer. On Derby and SQLServer an Integer is returned, on Postgres a Float is returned. However we need to get a consistent result (preferably always a float or double). With some experimentation, we found that with Derby we can change the select to be something like:
Code:
select field, avg(double(int_field))
And it works as expected, however this function is supported in Derby but not other databases. If I use the above
syntax on other databases, I receive the following error:
Code:
java.lang.NullPointerException
Stack Trace:
org.hibernate.dialect.Dialect$2.getReturnType(Dialect.java:70)
org.hibernate.hql.ast.SessionFactoryHelper.findFunctionReturnType(SessionFactoryHelper.java:191)
org.hibernate.hql.ast.AggregateNode.getDataType(AggregateNode.java:19)
org.hibernate.hql.ast.SelectClause.initializeExplicitSelectClause(SelectClause.java:134)
org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:440)
org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:351)
org.hibernate.hql.antlr.HqlSqlBaseWalker.afterQuery(HqlSqlBaseWalker.java:126)
org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:471)
org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:201)
org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:151)
org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:189)
org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:130)
org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
org.hibernate.impl.SessionImpl.list(SessionImpl.java:834)
org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
....
If I try to use a DB specific method of casting values (e.g. avg(convert(int_field as real)) or avg(cast(int_field as double)), then I get the same exception as above.
It seems that the HQL parser does not understand what type should be returned in this situation.
So the question is ... how do we write HQL to get a consistent datatype (preferably a float or double) from and "avg()" aggregate function?
Any advice would be greatly appreciated!!
Matt Doran
Login
FAQ
Search