Warm tip: This article is reproduced from stackoverflow.com, please click
hibernate postgresql criteriaquery

Hibernate only error: "Column must appear in the GROUP BY clause or be used in an aggregate function

发布于 2020-04-11 22:02:44

I have this query generated by hibernate (printed on console) and it works fine if directly executed throwg pgAdmin4:

select
        my_entity0_.status as col_0_0_,
        calc_sub_status(
            my_entity0_.status,
            my_entity0_.sub_status
        ) as col_1_0_,
        count(my_entity0_.id) as col_2_0_ 
    from
        demand my_entity0_
    group by
        my_entity0_.status ,
        calc_sub_status(
            my_entity0_.status,
            my_entity0_.sub_status
        )

But in my application (using Spring Boot and Hibernate) I got this exception:

SQL Error: 0, SQLState: 42803
ERROR: column "my_entity0_.sub_status" must appear in the GROUP BY clause or be used in an aggregate function

What can be done if the same query is working in pgAdmin, but not via jdbc?

PS: calc_sub_status is a custom function, receives 2 strings and returns a string.

PS2: criteria code:

cq.multiselect(status, calcSubStatus, cb.count(root))
                .groupBy(status, calcSubStatus);
Questioner
smaudi
Viewed
122
smaudi 2020-02-03 22:13

I was able to skip this hibernate-only error changing the query to:

select
        my_entity0_.status as col_0_0_,
        calc_sub_status(
            my_entity0_.status,
            my_entity0_.sub_status
        ) as col_1_0_,
        count(my_entity0_.id) as col_2_0_ 
    from
        demand my_entity0_
    group by
        my_entity0_.status ,
        2

The difference is in group by clause, where I'm grouping by "2", the position of the result, instead of repeating the function call.

In pgAdmin both ways works, but in hibernate only this alternative works.

The criteria function to archieve this is cb.literal({position}):

cq.multiselect(status, calcSubStatus, cb.count(root))
                .groupBy(status, cb.literal(2));