How to group by Case statement using JPA Criteria API / Hibernate

Question

I am trying to perform a query like the following, with selecting by a case statement and grouping by the same case statement..

Select USER, 
  (CASE
    WHEN value between 0 AND 2 then '0-2'
    WHEN value between 3 AND 4 then '3-4'
    ELSE '5+'
  END) as CASE_STATEMENT ,
SUM(value)
.....
Group by user, CASE_STATEMENT

using JPA 2.0 Criteria API, with Hibernate.

My test case looks like ...

    CriteriaBuilder cb = em.getCriteriaBuilder()
    CriteriaQuery cq = cb.createQuery(Tuple)
    def root = cq.from(TestEntity)
    def userGet = root.get('user')
    def valueGet = root.get('value')
    def caseExpr =
            cb.selectCase()
                .when(cb.between(valueGet, 0, 2), '0-2')
                .when(cb.between(valueGet, 3, 4), '3-4')
                .otherwise('5+')
    def sumExpr = cb.sum(valueGet)

    cq.multiselect([userGet, caseExpr, sumExpr])
    cq.groupBy([userGet, caseExpr])
    log(typedQuery.unwrap(Query).queryString)
    List<Tuple> tuples = typedQuery.getResultList()

The log statement of the queryString reads

SELECT generatedAlias0.USER, 
   CASE 
     WHEN generatedAlias0.value BETWEEN 0 AND 2 THEN Cast(:param0 AS STRING) 
     WHEN generatedAlias0.value BETWEEN 3 AND 4 THEN Cast(:param1 AS STRING) 
     ELSE Cast(:param2 AS STRING) 
   END, 
   Sum(generatedAlias0.value) 
FROM   test AS generatedAlias0 
GROUP  BY generatedAlias0.USER, 
      CASE 
        WHEN generatedAlias0.value BETWEEN 0 AND 2 THEN Cast( 
        :param3 AS STRING) 
        WHEN generatedAlias0.value BETWEEN 3 AND 4 THEN Cast( 
        :param4 AS STRING) 
        ELSE Cast(:param5 AS STRING) 
      END 

When calling the typedQuery.getResultList(), I get the following error statement

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not extract ResultSet

Caused by: org.h2.jdbc.JdbcSQLException: Column "TESTENTITY0_.VALUE" must be in the GROUP BY list; SQL statement:

select testentity0_.user as col_0_0_, case when testentity0_.value between 0 and 2 then cast(? as varchar(255)) when testentity0_.value between 3 and 4 then cast(? as varchar(255)) else cast(? as varchar(255)) end as col_1_0_, sum(testentity0_.value) as col_2_0_ from test testentity0_ group by testentity0_.user , case when testentity0_.value between 0 and 2 then cast(? as varchar(255)) when testentity0_.value between 3 and 4 then cast(? as varchar(255)) else cast(? as varchar(255)) end [90016-194]

Is there something wrong with the way I am trying to group by the Expression? I have also tried grouping by alias names, and by number literals (1, 2)

Is there another way I can go about structuring the SQL to get the same results?

Thanks.

Answer 1

As the exception message suggests, the problem is related to the Group By statement at DBMS level. See: https://www.percona.com/blog/2019/05/13/solve-query-failures-regarding-only_full_group_by-sql-mode/

To solve the error, you must either

• Set the Group By Mode of the underlying DBMS to a less restrictive level (MySQL allows to disable only-full-group-by, but H2 does not (you may try setting MODE=MYSQL in jdbc connection string)

  or (better)

• Add all columns that are part of the select statement to the GROUP BY statement or to an aggregate function as described above.

  You should be able to build a nested query which fulfills the GROUP BY RESTRICTIONS.

  For the rescue, there are some (maybe DBMS specific) aggregate functions (at least in MySQL). To trick JPA and Hibernate to understand these, there are several ways to achieve this, as described at https://vladmihalcea.com/hibernate-sql-function-jpql-criteria-api-query/ and https://vladmihalcea.com/the-jpa-entitymanager-createnativequery-is-a-magic-wand/

Edit

In contrast and addition to the statement above, the findings after discussion below are:

• The exception is raised by the h2 driver in the org.h2.expression.ExpressionColumn class, while it's verifying the query syntax
• The solution requires setting and referencing an alias in the query (at the case statement or subquery), which is currently not possible in Criteria API (see column aliases usually can't be referenced in the query itself)
• A workaround would be creating of a NativeQuery like this:

List<Tuple> tuples = em.createNativeQuery(
"SELECT generatedAlias0.USER, " +
"   CASE " +
"     WHEN generatedAlias0.value BETWEEN 0 AND 2 THEN Cast(:param0 AS VARCHAR) " +
"     WHEN generatedAlias0.value BETWEEN 3 AND 4 THEN Cast(:param1 AS VARCHAR) " +
"     ELSE Cast(:param2 AS VARCHAR) " +
"   END c, " +
"   Sum(generatedAlias0.value) as sumvalue " +
"FROM test AS generatedAlias0 " +
"GROUP  BY generatedAlias0.USER, c "
)
.setParameter("param0", "0-2")
.setParameter("param1", "3-4")
.setParameter("param2", "5+")
.getResultList();