Hibernate 6 / Wrong order by and group by generated with MappedSuperclass and Generics

[smalbequi]

Hi,

You can find the unit test here GitHub - smalbequi/hibernate-test-case-bug-orderby: Templates and examples to report issues to Hibernate

With Hibernate 6 and org.hibernate.dialect.H2Dialect dialect, the following query generates a wrong order
by:

select  
	c.id as clientId, 
	c.name as clientName, 
	t.code as typeCode, 
	g.id as generationId, 
	sum(e.balance) 
	
from Card e  
	
inner join e.generation g  
inner join g.type t  
inner join t.client c  

group by clientId, typeCode, generationId  

order by clientName, typeCode, generationId

Generated SQL visible through log:

select
    t1_0.client_id c0,
    c2_0.name c1,
    g1_0.type_code c2,
    c1_0.generation_id c3,
    sum(c1_0.balance) c4
from
    Card c1_0
join
    Generation g1_0
        on g1_0.id=c1_0.generation_id
join
    CardType t1_0
        on t1_0.code=g1_0.type_code
join
    Client c2_0
        on c2_0.id=t1_0.client_id
group by
    c0,
    c2,
    c3
order by
    2,
    3,
    4

With Hibernate 6 and org.hibernate.dialect.PostgresPlusDialect dialect, the query generates a wrong order by and a wrong group by:

select
    t1_0.client_id,
    c2_0.name,
    g1_0.type_code,
    c1_0.generation_id,
    sum(c1_0.balance) 
from
    Card c1_0 
join
    Generation g1_0 
        on g1_0.id=c1_0.generation_id 
join
    CardType t1_0 
        on t1_0.code=g1_0.type_code 
join
    Client c2_0 
        on c2_0.id=t1_0.client_id 
group by
    1,
    3,
    4 
order by
    2,
    3,
    4

[beikov]

What makes you think this is wrong? As far as I checked, the translation is correct.

[smalbequi]

What does “group by 1, 3, 4” mean? Will the query produce the excepted result?

Hibernate 5 generates the SQL below which makes more sense to me:

select
	client3_.id as col_0_0_,
			client3_.name as col_1_0_,
	cardtype2_.code as col_2_0_,
			generation1_.id as col_3_0_,
	sum(card0_.balance) as col_4_0_
from
	Card card0_
inner join
	Generation generation1_
	on card0_.generation_id=generation1_.id
inner join
	CardType cardtype2_
	on generation1_.type_code=cardtype2_.code
inner join
	Client client3_
	on cardtype2_.client_id=client3_.id
group by
	col_0_0_ ,
	col_2_0_ ,
	col_3_0_
order by
	col_1_0_, 
	col_2_0_,
	col_3_0_

[beikov]

Just like in the order by clause, certain database versions support grouping by select item (1 based) positions. We use positions to avoid the need to introduce aliases if possible, but the semantics are the same.

[smalbequi]

I really think that there is an issue with Hibernate 6 and this kind of request.

If now I don’t use any alias, the generated SQL is still wrong and PostgreSQL refuses to execute it.

For the query:

    select
        c.id,
        c.name,
        t.code,
        g.id,
        sum(e.balance)
    from Card e
    inner join e.generation g
    inner join g.type t
    inner join t.client c
    group by c.id, t.code, g.id
    order by c.name, t.code, g.id

The generated SQL is:

    select
        t1_0.client_id,
        c2_0.name,
        g1_0.type_code,
        c1_0.generation_id,
        sum(c1_0.balance) 
    from
        Card c1_0 
    join
        Generation g1_0 
            on g1_0.id=c1_0.generation_id 
    join
        CardType t1_0 
            on t1_0.code=g1_0.type_code 
    join
        Client c2_0 
            on c2_0.id=t1_0.client_id 
    group by
        t1_0.client_id,
        g1_0.type_code,
        c1_0.generation_id 
    order by
        c2_0.name,
        g1_0.type_code,
        c1_0.generation_id

h2 accepts the query but Postgresql doesn’t and raises the following error:

[42803] ERROR: column "c2_0.name," must appear in the GROUP BY clause or be used in an aggregate function Position : 31

The ‘group by’ should be:

group by c2_0.id, t1_0.code, g1_0.id

Btw, when we group by id, all columns that appear in the select clause don’t have to appear in the ‘group by’ clause.

The ‘order by’ has a similar issue.

With Hibernate 5 the generated SQL is just perfect:

    select
        client3_.id as col_0_0_,
        client3_.name as col_1_0_,
        cardtype2_.code as col_2_0_,
        generation1_.id as col_3_0_,
        sum(card0_.balance) as col_4_0_ 
    from
        Card card0_ 
    inner join
        Generation generation1_ 
            on card0_.generation_id=generation1_.id 
    inner join
        CardType cardtype2_ 
            on generation1_.type_code=cardtype2_.code 
    inner join
        Client client3_ 
            on cardtype2_.client_id=client3_.id 
    group by
        client3_.id ,
        cardtype2_.code ,
        generation1_.id 
    order by
        client3_.name,
        cardtype2_.code,
        generation1_.id

[smalbequi]

Thanks.

So if positions work, that’s fine.

But I do have an issue with the generated SQL with PostgreSQL.
H2 accepts but PostgreSQL doesn’t.

See my previous comment please

[beikov]

Which version of PostgreSQL are you using? It should be able to figure out equivalence due to the use of inner joins, so I assume that there might be a bug in the PostgreSQL version you are using.

[smalbequi]

11.14 (Debian 11.14-0+deb10u1)

[smalbequi]

I did a test with the latest PostgreSQL docker image, created tables and ran the query with psql

psql (15.2 (Debian 15.2-1.pgdg110+1))
Type "help" for help.

postgres=# select
        t1_0.client_id,
        c2_0.name,
        g1_0.type_code,
        c1_0.generation_id,
        sum(c1_0.balance) 
    from
        Card c1_0 
    join
        Generation g1_0 
            on g1_0.id=c1_0.generation_id 
    join
        CardType t1_0 
            on t1_0.code=g1_0.type_code 
    join
        Client c2_0 
            on c2_0.id=t1_0.client_id 
    group by
        t1_0.client_id,
        g1_0.type_code,
        c1_0.generation_id 
    order by
        c2_0.name,
        g1_0.type_code,
        c1_0.generation_id;
ERROR:  column "c2_0.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:         c2_0.name,
                ^
postgres=# 

[beikov]

Ohh, right. Usually, for aggregate queries, databases require that every non-aggregate expression that appears in SELECT, ORDER BY, HAVING, must also appear in GROUP BY. There is a functional dependency analysis that allows to omit certain columns of a table, when a unique key for that table was already mentioned in the group by clause, but I guess that one does not yet take equivalence classes into account.

I found a stack exchange post about something similar and here you can find an explanation about functional dependency analysis: SQL GROUP BY and Functional Dependencies: A Very Useful Feature – Java, SQL and jOOQ.

Can you please try out if Hibernate 6.2.0.CR2 fixes your issue, and if not, create an issue in the issue tracker(https://hibernate.atlassian.net) with a test case(hibernate-test-case-templates/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub) that reproduces the issue?

[smalbequi]

Same issue with Hibernate 6.2.0.CR2
I’ll create an issue in the issue tracker.

Thank you for your help

[smalbequi]

I have created the issue [HHH-16189] - Hibernate JIRA

[smalbequi]

Hi, despite the ticket HHH-16189 has been stated “Fixed” I am still facing the same issue with version 6.2.1.Final. The shared unit test produce the same wrong “group by” and “order by” clauses

[beikov]

Can you please create a new JIRA issue for this? You are saying that the test in HHH-16189 wrong order by and group by generated by dreab8 · Pull Request #6237 · hibernate/hibernate-orm · GitHub is not reproducing the same problem as the test you wrote?

[dreab8]

Hi @smalbequi I have just tried your test with Hibernate 6.2.1.Final and PostgresSQL and it passes, I commented out the // Assertions.fail("Wrong group by and wrong order by generated (see log)");

The generated SQL is

select
        c2_0.id,
        c2_0.name,
        t1_0.code,
        g1_0.id,
        sum(c1_0.balance) 
    from
        Card c1_0 
    join
        Generation g1_0 
            on g1_0.id=c1_0.generation_id 
    join
        CardType t1_0 
            on t1_0.code=g1_0.type_code 
    join
        Client c2_0 
            on c2_0.id=t1_0.client_id 
    group by
        c2_0.id,
        t1_0.code,
        g1_0.id 
    order by
        c2_0.name,
        t1_0.code,
        g1_0.id

[smalbequi]

I’m sorry, I forgot to do a “Maven reload project” in IntelliJ after I updated the Hibernate dependency.

Now it’s perfect. The problem is solved. Sorry for the extra time spent.

Thanks a lot.

[dreab8]

np, happy the problem has been solved