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