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 ToOne relation the query created by hibernate does not contain the order attribute in the result list.
The distinct is necessary because there is an other ToMany relation.
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 Foo {
@OneToMany
private List< User Bar > bars;
@OneToOne
private User user;
}
@Entity
public class User {
private String name;
}
...
// query with distinct and orderBy producing the error
Root< Task Foo > from = query.from( Task Foo .class);
query. distinct where ( true cb.equal(from.join("bars" ) .get("value"), 42)) ;
query.orderBy(cb.asc(from.join("user").get("name")));
List<Foo> resultList = entityManager.createQuery(query).getResultList();
Caused by: org.h2.jdbc.JdbcSQLException: Sortier-Ausdruck " USER4_ USER3_ .NAME" muss in diesem Fall im Resultat vorkommen
Order by expression " USER4_ USER3_ .NAME" must be in the result list in this case; SQL statement:
select distinct process0_ foo0_ .id as id1_0_ id1_1_, foo0_.user_id as user_id2_1_ from Process process0_ Foo foo0_ inner join Process_Task tasks1_ Foo_Bar bars1_ on process0_ foo0_ .id= tasks1_ bars1_ . Process_id Foo_id inner join Task task2_ Bar bar2_ on tasks1_ bars1_ . tasks_id bars_id = task2_ bar2_ .id inner join Task_User User user3_ on task2_ foo0_ . id user_id =user3_. Task_id inner join User user4_ on user3_ id where bar2_ . user_id=user4_. id =42 order by user4_ user3_ .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)
at org.h2.command.Parser.parseSelect(Parser.java:1668)
at org.h2.command.Parser.parsePrepared(Parser.java:434)
at org.h2.command.Parser.parse(Parser.java:306)
at org.h2.command.Parser.parse(Parser.java:278)
at org.h2.command.Parser.prepareCommand(Parser.java:243)
at org.h2.engine.Session.prepareLocal(Session.java:442)
at org.h2.engine.Session.prepareCommand(Session.java:384)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:276)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)
|
|
