2

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);
smaudi
  • 83
  • 7
  • This is hard to believe. There is probably some subtle "spelling" difference between the two queries, that you are overlooking. Does hibernate not pass on any location information about where in the query the error is occuring/detected? – jjanes Feb 02 '20 at 20:20
  • "position 81" is the only clue – smaudi Feb 03 '20 at 14:49

1 Answers1

1

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));
smaudi
  • 83
  • 7