jpql complex query with order by

Question

I have 2 Entities (this is just a simplified example, not the real entities):

@Entity
@Table(name = "entityA")
public class EntityA {
    @ManyToOne(cascade = CascadeType.ALL)
    EntityB start;
    @ManyToOne(cascade = CascadeType.ALL)
    EntityB stop;
    ...
}


@Entity
@Table(name = "entityB")
public class EntityB {
    public String name; 
    ...
}

Hibernate crashes when I try to write a query with an order by:

select t.start from EntityA t
where 
...
order by t.start.name

It does not allow me to put t.start.name, if I substitute it with t.start, it works perfectly. Does anyone know why it behaves in this way?

EDIT: This is the query translated by hibernate:

SELECT DISTINCT entityB1_.uuid AS uuid2021_, ..
  entityB1_.name               AS name2021_,
...
FROM entityA entityA0_
INNER JOIN entityB entityB1_
ON entityA0_.stop_uuid=entityB1_.uuid,
  entityB entityB2_,
  entityB entityB3_
WHERE entityA0_.start_uuid    =entityB2_.uuid
AND entityA0_.start_uuid=entityB3_.uuid
AND entityA0_.qn        =?
AND entityB2_.lifeCycle            ='READY'
AND entityB3_.lifeCycle            ='READY'
ORDER BY entityB2_.name

As you can see in the order by it puts "entityB2" instead of "entityB1", which is the selected field. In this way it gives me this exception:

Caused by: java.sql.SQLSyntaxErrorException: ORA-01791: not a SELECTed expression
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
~[ojdbc7.jar:12.1.0.1.0] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) ~[ojdbc7.jar:12.1.0.1.0] at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017) ~[ojdbc7.jar:12.1.0.1.0] at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655) ~[ojdbc7.jar:12.1.0.1.0] at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249) ~[ojdbc7.jar:12.1.0.1.0] at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566) ~[ojdbc7.jar:12.1.0.1.0] at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215) ~[ojdbc7.jar:12.1.0.1.0] at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58) ~[ojdbc7.jar:12.1.0.1.0] at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776) ~[ojdbc7.jar:12.1.0.1.0] at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897) ~[ojdbc7.jar:12.1.0.1.0] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034) ~[ojdbc7.jar:12.1.0.1.0] at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820) ~[ojdbc7.jar:12.1.0.1.0] at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867) ~[ojdbc7.jar:12.1.0.1.0] at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502) ~[ojdbc7.jar:12.1.0.1.0] at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:141) ~[weblogic.server.merged.jar:12.1.3.0.0] at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) ~[hibernate-core-3.5.6-Final.jar:3.5.6-Final] at org.hibernate.loader.Loader.getResultSet(Loader.java:1869) ~[hibernate-core-3.5.6-Final.jar:3.5.6-Final] at org.hibernate.loader.Loader.doQuery(Loader.java:718) ~[hibernate-core-3.5.6-Final.jar:3.5.6-Final] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270) ~[hibernate-core-3.5.6-Final.jar:3.5.6-Final] at org.hibernate.loader.Loader.doList(Loader.java:2449) ~[hibernate-core-3.5.6-Final.jar:3.5.6-Final]

"t.start.name" is perfectly valid JPQL, and I navigate 1-1 relations in the result clause in my queries with a different JPA provider no problem. Define "crashes" — Neil Stockton

Neil Stockton Neil Stockton · Accepted Answer · 2015-04-28T09:07:08

Try something like this, to see if this stops Hibernate adding joins unnecessarily

SELECT s FROM EntityA t JOIN t.start s ORDER BY s.name

jpql complex query with order by

1 Answers