Query for SUM(IFNULL)

akrishmohan · **Joined:** Fri Dec 14, 2007 12:48 am **Posts:** 4

I am trying to execute this query using HQL and it throws a NULL pointer exception

The query is this
<b>
select new list(SUM(IFNULL(customer.count,0))) from Customer customer
</b>

I am using MYSQL as the backend database. And this is the exception I get:

Code:

Caused by: java.lang.NullPointerException
        at org.hibernate.dialect.Dialect$3.getReturnType(Dialect.java:102)
        at org.hibernate.hql.ast.util.SessionFactoryHelper.findFunctionReturnType(SessionFactoryHelper.java:382)
        at org.hibernate.hql.ast.tree.AggregateNode.getDataType(AggregateNode.java:21)
        at org.hibernate.hql.ast.tree.ConstructorNode.resolveConstructorArgumentTypes(ConstructorNode.java:101)
        at org.hibernate.hql.ast.tree.ConstructorNode.prepare(ConstructorNode.java:79)
        at org.hibernate.hql.ast.HqlSqlWalker.processConstructor(HqlSqlWalker.java:840)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:1956)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:1825)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1394)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:553)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
        at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)
        at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
        at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
        at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
        at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
        at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
        at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
        at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
        at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
        at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:92)

Thanks
K

panth13 · **Joined:** Tue Apr 24, 2007 12:53 pm **Posts:** 28

I think that there's a problem with your HQL, what is the 'new list(' supposed to be for? Or more precisely, what are you trying to do in the query?

-B

malm66 · **Joined:** Sat Nov 25, 2006 11:37 am **Posts:** 72

The NPE looks like a bug in Hibernate to me. However, I agree with the previous poster that the new list(...) clause seems strange. As this is an aggregate query it will only return a single row with a single value. Why would you want to construct a custom object for that? I would try if

select SUM(IFNULL(customer.count,0)) from Customer customer

works or if it also throws the NPE?

panth13 · **Joined:** Tue Apr 24, 2007 12:53 pm **Posts:** 28

I think that the NPE is caused by an invalid (or rather unsupported) function being used in the HQL query. IFNULL is a not supported function in any of the MySql Dialect (I checked all those named MySQL...class). It appears to trying to be sum the IFNULL return, but it first has to map it to its return type. Since IFNULL doesn't exist, the value is null and NPE is thrown.

I agree that the NPE might be a bug, but if it's being caused by what I think it's being caused by, then instead of an NPE at this point, you'd get a IllegalArgumentException, or UnsupportedOperation... Not an NPE, but still an exception.

-B

Saeed · **Joined:** Sat Nov 19, 2005 11:54 am **Posts:** 22

I Think you should use
select SUM(NVL(customer.count,0)) from Customer customer

palodan · **Posted:** Wed Dec 26, 2007 9:00 am

Hi,
Use either coalesce () or nullif() for null check.