select case with H2 database asking to group by column

Question

When I use HQL to query my database, this requests works fine and return the good data :

    select
    case 
        when activityjp0_.status<>'CLOSED' 
        and (activityjp0_.user_id is null) then 'UNASSIGNED' 
        else case 
            when activityjp0_.status<>'CLOSED' 
            and activityjp0_.deadline<CURRENT_DATE then 'OVERDUE' 
            else case 
                when activityjp0_.status='PENDING' then 'IN_PROGRESS' 
                else activityjp0_.status 
            end 
        end 
    end as col_0_0_,
    count(distinct activityjp0_.id) as col_1_0_ 
from
    activities activityjp0_ 
group by
    case 
        when activityjp0_.status<>'CLOSED' 
        and (activityjp0_.user_id is null) then 'UNASSIGNED' 
        else case 
            when activityjp0_.status<>'CLOSED' 
            and activityjp0_.deadline<CURRENT_DATE then 'OVERDUE' 
            else case 
                when activityjp0_.status='PENDING' then 'IN_PROGRESS' 
                else activityjp0_.status 
            end 
        end 
    end

When I use Criteria API to generate the SAME request, it gives :

    select
    case 
        when activityjp0_.status<>? 
        and (activityjp0_.user_id is null) then 'UNASSIGNED' 
        else case 
            when activityjp0_.status<>? 
            and activityjp0_.deadline<? then 'OVERDUE' 
            else case 
                when activityjp0_.status=? then 'IN_PROGRESS' 
                else activityjp0_.status 
            end 
        end 
    end as col_0_0_,
    count(distinct activityjp0_.id) as col_1_0_ 
from
    activities activityjp0_ 
group by
    case 
        when activityjp0_.status<>? 
        and (activityjp0_.user_id is null) then 'UNASSIGNED' 
        else case 
            when activityjp0_.status<>? 
            and activityjp0_.deadline<? then 'OVERDUE' 
            else case 
                when activityjp0_.status=? then 'IN_PROGRESS' 
                else activityjp0_.status 
            end 
        end 
    end

BUT this result in a JDBC error claiming :

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "ACTIVITYJP0_.USER_ID" must be in the GROUP BY list; SQL statement:

select case when activityjp0_.status<>? and (activityjp0_.user_id is null) then 'UNASSIGNED' else case when activityjp0_.status<>? and activityjp0_.deadline? and (activityjp0_.user_id is null) then 'UNASSIGNED' else case when activityjp0_.status<>? and activityjp0_.deadline

If I replace the variables in the same generated query, and run it against my database (in h2 console) it runs with no error

How is this possible ?

Answer 1

You have two very similar expressions:

    case 
        when activityjp0_.status<>? 
        and (activityjp0_.user_id is null) then 'UNASSIGNED' 
        else case 
            when activityjp0_.status<>? 
            and activityjp0_.deadline<? then 'OVERDUE' 
            else case 
                when activityjp0_.status=? then 'IN_PROGRESS' 
                else activityjp0_.status 
            end 
        end 
    end

But actually they aren't the same, because parameters (?) are all different; you have 4 parameters in the first expression and 4 additional parameters in the second one. You should use group by col_0_0_ instead of group by case … end or you can use the same indexed (?1, ?2, …) parameters in both expressions.