If an entity is requested by a distinct query and the result should be ordered by an attribute of a joined entity e.g. of an OneToMany relation the query created by hibernate does not contain the order attribute in the result list.
Databases like postgres and h2 can not process this query. The reason why is decribed in places like this:
https://github.com/h2database/h2database/issues/408#issuecomment-262641613
This functionality is essential for many cases where complex data is filtered and ordered in paged tables with complex queries.
Without distinct there is no problem, as soon as distinct is necessary and can't be avoided the query fails.
Example (also provided as testcase):
@Entity
public class Task {
@OneToMany
private List<User> user;
}
@Entity
public class User {
private String name;
}
...
// query with distinct an orderBy producing error
Root<Task> from = query.from(Task.class);
query.distinct(true);
query.orderBy(cb.asc(from.join("user").get("name")));
entityManager.createQuery(query).getResultList();
Caused by: org.h2.jdbc.JdbcSQLException: Sortier-Ausdruck "USER4_.NAME" muss in diesem Fall im Resultat vorkommen
Order by expression "USER4_.NAME" must be in the result list in this case; SQL statement:
select distinct process0_.id as id1_0_ from Process process0_ inner join Process_Task tasks1_ on process0_.id=tasks1_.Process_id inner join Task task2_ on tasks1_.tasks_id=task2_.id inner join Task_User user3_ on task2_.id=user3_.Task_id inner join User user4_ on user3_.user_id=user4_.id order by user4_.name asc [90068-176]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
at org.h2.message.DbException.get(DbException.java:178)
at org.h2.message.DbException.get(DbException.java:154)
at org.h2.command.dml.Query.initOrder(Query.java:434)
at org.h2.command.dml.Select.init(Select.java:747)
|
|
