Error with distinct query + order by aggregate function on an association key
Description
I recently tried to migrate to Hibernate 6.5.2 from 5.6 and encountered an issue which I narrowed down to 6.5.1. Seeing the nature of this task, I believe that it is related to
We are also using the Postgres dialect (as described in ) although that does not seem to matter for the test case I have developed. I am facing an issue with HQL queries that:
contain a left join with an any-to-one relationship;
a select statement with an aggregate function on the primary key of the joined entity; and
an order by statement on the same primary key of the joined entity
Namely, assuming the following (simplified) entity classes:
writing an HQL query such as:
gets the select statement translated to the following SQL:
Note that Hibernate rewrites the select statement replacing b.id(the PK of the joined entity) with a.typeb_id (the foreign key in the primary entity) which now takes the query to in an inconsistent state since the order by fields no longer appear in the select clause leading to the following error in Postgres:
and the following error in H2 and the test cases I have prepared:
It is possible to circumvent this by adding b.id to the group by clause and not use the MIN aggregate function, but that sounds like a workaround more than a fix as I do not see a reason for the aforementioned case not to be supported.
The only workaround I have found for now is to stick to 6.5.0.
Reproduction test cases can be found in the comments.
Thank you for reporting the issue and providing a reproducer. In 6.5.0 this used to work as we always rendered the target table mapping for explicit joins, but found even that could be problematic in some cases.
Handling all different cases of group by / order by including functions is not easy, especially since different DBs have different support for functional dependency, and requires some drawbacks in terms of processing during query interpretation. We will see what we can do in this case
In the meantime, a valid workaround for this is assigning an explicit alias to the aggregate function selection and ordering by that:
You can even simply order by the output column number, like:
Ivaylo Mitrev May 30, 2024 at 3:34 PM
Tests cases:
Changing hibernate-core’s version to 6.5.0.Final makes the test pass (as does any other version between 5.6 and 6.5.1 that I have tested with.
I recently tried to migrate to Hibernate 6.5.2 from 5.6 and encountered an issue which I narrowed down to 6.5.1. Seeing the nature of this task, I believe that it is related to
We are also using the Postgres dialect (as described in ) although that does not seem to matter for the test case I have developed. I am facing an issue with HQL queries that:
contain a left join with an any-to-one relationship;
a select statement with an aggregate function on the primary key of the joined entity; and
an order by statement on the same primary key of the joined entity
Namely, assuming the following (simplified) entity classes:
writing an HQL query such as:
gets the
selectstatement translated to the following SQL:Note that Hibernate rewrites the select statement replacing
b.id(the PK of the joined entity) witha.typeb_id(the foreign key in the primary entity) which now takes the query to in an inconsistent state since theorder byfields no longer appear in theselectclause leading to the following error in Postgres:and the following error in H2 and the test cases I have prepared:
It is possible to circumvent this by adding
b.idto the group by clause and not use theMINaggregate function, but that sounds like a workaround more than a fix as I do not see a reason for the aforementioned case not to be supported.The only workaround I have found for now is to stick to 6.5.0.
Reproduction test cases can be found in the comments.