HQL - StandardAnsiSqlAggregationFunctions$SumFunction.determineJdbcTypeCode

Question

I want to create a List of new objects of TotalDTO. TotalDTO has fields that are sums and counts of OrderDTO.total with various conditions. OrderDTO is mapped to database while TotalDTO is not. I'm working with EntityManager:

@PersistenceContext
private EntityManager entityManager;

public List listOfTotal(LocalDate from, LocalDate to)
{   
    String hql = "SELECT new com.CompanyName.DTO.TotalDTO(o.date, COALESCE(SUM(IF(o.platform = 'China', o.total, 0)), 0),"
            + " COALESCE(SUM(IF(o.platform != 'China', o.total, 0)), 0),"
            + " COUNT(o.orderId),"
            + " COALESCE(SUM(IF(o.platform = 'coupons', o.total, 0)), 0),"
            + " COALESCE(SUM(o.total), 0))"
            + " FROM OrderDTO o WHERE (o.date BETWEEN :from AND :to) GROUP BY o.date ORDER BY o.date";
    
    Query query = entityManager.createQuery(hql);
    
    query.setParameter("from", from);
    query.setParameter("to", to);

    List listOfTotal = (List)query.getResultList();
    
    return listOfTotal;

My TotalDTO Constructor:

public TotalDTO(LocalDate date, BigDecimal china, BigDecimal spent, long totalOrders, BigDecimal totalCoupons,
        BigDecimal totalSum)
{
    this.date = date;
    this.china = china;
    this.spent = spent;
    this.totalOrders = totalOrders;
    this.totalPoints = totalCoupons;
    this.total = totalSum;
}

I receive this error:

java.lang.NullPointerException: null
    at org.hibernate.dialect.function.StandardAnsiSqlAggregationFunctions$SumFunction.determineJdbcTypeCode(StandardAnsiSqlAggregationFunctions.java:200) ~[hibernate-core-5.4.30.Final.jar:5.4.30.Final]
    at org.hibernate.dialect.function.StandardAnsiSqlAggregationFunctions$SumFunction.getReturnType(StandardAnsiSqlAggregationFunctions.java:158) ~[hibernate-core-5.4.30.Final.jar:5.4.30.Final]
    at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.findFunctionReturnType(SessionFactoryHelper.java:430) ~[hibernate-core-5.4.30.Final.jar:5.4.30.Final]
    at org.hibernate.hql.internal.ast.tree.AggregateNode.getDataType(AggregateNode.java:68) ~[hibernate-core-5.4.30.Final.jar:5.4.30.Final]
...

This query works in SQL but here in Hibernate not.

I've read this: New Object with HQL - NPE on StandardAnsiSqlAggregationFunctions, determineJdbcTypeCode But person in this post had different problem if I understood correctly. But it's connected to mine.

UPDATE: Exception is thrown in class org.hibernate.dialect.function.StandardAnsiSqlAggregationFunctions in this method:

protected final int determineJdbcTypeCode(Type type, Mapping mapping) throws QueryException 
{
    try
    {
        final int[] jdbcTypeCodes = type.sqlTypes(mapping);
        if ( jdbcTypeCodes.length != 1 )
        {
            throw new QueryException("multiple-column type in sum()");
        }
        return jdbcTypeCodes[0];
    }
    catch ( MappingException me )
    {
        throw new QueryException(me);
    }
}

Here Type type is null, I guess that is not ok.

Update2: I've also tried to cast

String hql = "SELECT new com.CompanyName.DTO.TotalDTO(o.date, CAST( COALESCE(SUM(IF(o.platform = 'China', o.total, 0)), 0) AS decimal ),"
        + " CAST(COALESCE(SUM(IF(o.platform != 'China', o.total, 0)), 0) AS decimal ),"
        + " CAST(COUNT(o.orderId) AS long),"
        + " CAST (COALESCE(SUM(IF(o.platform = 'coupons', o.total, 0)), 0) AS decimal ),"
        + " CAST(COALESCE(SUM(o.total), 0) AS decimal) )"
        + " FROM OrderDTO o WHERE (o.date BETWEEN :from AND :to) GROUP BY o.date ORDER BY o.date";

I was testing this further and I noticed that exception is happening only when I'm using IF statement, but I need IF statements. IF works in SQL, does HQL works differently?

user_14517741 · Answer

It seems that problem is not with the constructor. Whenever I use IF statement, I get this error. When I use IF in MySQL it works, but not here. So I changed to CASE:

        String hql = "SELECT new com.CompanyName.DTO.TotalDTO(o.date,"
                + " COALESCE(SUM(CASE WHEN o.platform = 'China' THEN o.total ELSE 0 END), 0), "
                + " COALESCE(SUM(CASE WHEN o.platform != 'China' THEN o.total ELSE 0 END), 0), "
                + " COUNT(o.orderId), "
                + " COALESCE(SUM(CASE WHEN o.platform = 'coupons' THEN o.total ELSE 0 END), 0),"
                + " COALESCE(SUM(o.total), 0)) "
+ " FROM OrderDTO o WHERE (o.date BETWEEN :from AND :to) GROUP BY o.date ORDER BY o.date";

HQL - StandardAnsiSqlAggregationFunctions$SumFunction.determineJdbcTypeCode

1 Answers