Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

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)

Attachments

2

Activity

Show:

Sven Reinhardt July 8, 2019 at 10:46 AM

A simple

from.fetch("user");

is’t enougth, the fetch join has to be usesd in the query instead of the join and must be casted:

query.orderBy(cb.asc(((Join<?, ?>) from.fetch("user")).get("name")));

instead of:

query.orderBy(cb.asc(from.join("user").get("name")));

It would be rather nice if hibernate could do the resulting select on the field if the query is distinct and has an order by joined attribute. So other abstractions like spring data, which add joins by path for sorting in pages could rely on it

Christian Beikov July 7, 2019 at 9:21 AM

You need to fetch join the user association to make this work

Details

Assignee

Reporter

Components

Affects versions

Priority

Created June 11, 2019 at 1:57 PM
Updated July 8, 2019 at 10:46 AM