order by attribute of joined entity with distinct: Order by expression must be in the result list JdbcSQLSyntaxErrorException SQLGrammarException

Description

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 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 Foo {
@OneToMany
private List<Bar> bars;
@OneToOne
private User user;

@Entity
public class User {
private String name;
}
...
// query with distinct and orderBy producing the error
CriteriaQuery<Foo> query = cb.createQuery(Foo.class);
query.distinct(true);
Root<Foo> from = query.from(Foo.class);
query.where(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 "USER3_.NAME" muss in diesem Fall im Resultat vorkommen
Order by expression "USER3_.NAME" must be in the result list in this case; SQL statement:
select distinct foo0_.id as id1_1_, foo0_.user_id as user_id2_1_ from Foo foo0_ inner join Foo_Bar bars1_ on foo0_.id=bars1_.Foo_id inner join Bar bar2_ on bars1_.bars_id=bar2_.id inner join User user3_ on foo0_.user_id=user3_.id where bar2_.id=42 order by 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)

Environment

None

Status

Assignee

Unassigned

Reporter

Sven Reinhardt

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Feedback Requested

2019/06/18

Components

Affects versions

5.4.0
5.3.10

Priority

Blocker
Configure