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.

Seeing the same problem right now and could not find a proper solution. Added a bounty to the question, hoping someone will look into it and can explain why the column in the case needs to be in the GROUP BY (thus, removing the purpose of the count entirely). — Frame91, Aug 15 '19 at 21:50
Running the exact same query in H2 directly seems to succeed... so it is probably a JPA bug? — Frame91, Aug 15 '19 at 22:24
for sure your problem in SQL because you have error: `org.h2.jdbc.JdbcSQLException: Column "TESTENTITY0_.VALUE" must be in the GROUP BY list` — daggett, Aug 16 '19 at 09:56
i believe it's because you parametrized the case statement. could you try with sub-query? `select user, case_statement, sum(value) as sum_value from ( 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 , value from test ) as t1 group by user, case_statement` — daggett, Aug 16 '19 at 10:22
As an alternative to what @daggett suggests, try using `cb.literal('0-2')`, `cb.literal('3-4')`, `cb.literal('5+')` instead — crizzis, Aug 16 '19 at 13:57
@daggett I see the same problem with any parameter I specify in my case when statement. Even though I'm grouping by the 'output' of the case when statement, it still complains that it needs the parameters in the GROUP BY statement. Noteworthy: If you generate the SQL from it before calling getResultList, and executing it manually in the DB, it works just fine. — Frame91, Aug 16 '19 at 15:08
`def caseExpr = cb.selectCase()....alias('myCase')` and `cq.groupBy([userGet, "myCase"])` — daggett, Aug 16 '19 at 17:03

Mike Feustel · Answer 1 · 2019-08-23T17:05:38.440

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();

Thank you for your suggestions. Can you explain why the query as is works fine in H2 with the same database-connection settings? I have no problems grouping by a statement that is in the SELECT statement without having to declare all attributes from that given statement in my group.. — Frame91, Aug 17 '19 at 04:06
@Frame91 Indeed, this behavior seems strange. In my opinion, it should not work in any case, except with tolerant MySQL system. Could it be possible that connection settings, such as the SQL mode, are overwritten during the application start? Did you check the logs at loglevel debug? — Mike Feustel, Aug 17 '19 at 08:46
@Frame91 Yes, I see. With the given example, I found the exception is raised by the _h2 driver_ in the `org.h2.expression.ExpressionColumn` class, which is checking the _group by restrictions_ mentioned above. I also checked the h2 console, which shows, the h2 db is tolerant about this violation. To sum up, to achieve compatibility, you have to set an alias for the case statement or create a subquery like stated at the issue you mentioned. — Mike Feustel, Aug 18 '19 at 10:55
subqueries in FROM statements is not supported in the criteria api... and I was trying to alias the case statement, but it will result in the same exception — Frame91, Aug 19 '19 at 23:15
@Frame91 Subqueries are possible by using `getSelection()` as stated [here](https://stackoverflow.com/questions/4662336/subquery-in-select-clause-with-jpa-criteria-api). I've tested this, but unfortunately, resulting in a "group by exception". Assigning and using an alias for the subselect statement does not work too. So, for now, I suggest to don't use the Criteria API at all and to build a `NativeQuery` like described by _Vlad Mihalcea_. It is merely a workaround regarding the _Criteria api_, but at least a working solution. — Mike Feustel, Aug 20 '19 at 19:00
thanks, that will work! Would mark your answer as approved if I could, but I am not the author of the question — Frame91, Aug 24 '19 at 07:17

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

1 Answers1