Issues in migrating from Hibernate 5.3.3 to 5.3.4

Following is my domain model

    @Table(schema="XXX" ,name="YYY")
    public class User{
       @OneToMany(cascade = CascadeType.ALL)
       @JoinColumn(name="USER_KEY" , referencedColumnName="USER_KEY")
       private  List<UserArea> area;
    public class UserArea{
        private int userKey;
        private String userAreaName;


In my DAO , I have the following code
Pageable pageable = PageRequest.of(pageNumber, pageSize);
//building a criteria
CriteriaBuilder criteriabuilder = entitymanager.getCriteriaBuilder();
CriteriaQuery criteriaquery = criteriabuilder.createQuery();

Root<User> user = criteriaquery.from(User.class);

Join<User, UserFunctionalArea> functionalArea = user.join("area", JoinType.LEFT);;
TypedQuery<User> createQuery = entitymanager.createQuery(criteriaquery);
List<User> content = createQuery.setFirstResult((int) pageable.getOffset()).setMaxResults(pageable.getPageSize()).getResultList();

In 5.3.3 Hibernate-code i am getting the following SQL

select << user attributes >> from USER user0_ left outer join AREA functional1_ on user0_.USER_KEY=functional1_.USER_KEY fetch first ? rows only

whereas in Hibernate 5.3.4 i am getting the following SQL

select * from ( select << user attributes >> from USER user0_ left outer join AREA functional1_ on user0_.USER_KEY=functional1_.USER_KEY ) where rownum <= ?

On further debugging found that the Oracle12cDialect has changed from 5.3.3 to 5.3.4 to hava a different limit handler (Oracle9i$). The limit handler (SQL2008StandardLimitHandler) did not add the extra select * on the outside until 5.3.3 whereas all the others are adding the extra select *.

public LimitHandler getLimitHandler() {  // only in 5.3.3
    return SQL2008StandardLimitHandler.INSTANCE;

This is the root cause of the extra select * from outer select.

From 5.3.4 onwards, I am not able to sort on the Area->userAreaName as it complains that its not part of outer query which started this whole problem. In 5.3.4 i am having the following query

select * from ( select distinct << user attr >> from user user0_ left outer join area functional1_ on user0_.user_key=functional1_.user_key ) order by upper(functional2_.userAreaName) desc, upper(user0_.last_nm_prfrd) asc ) where rownum <= 10

which gives the error
2021-09-01 14:45:43.641 [http-nio-8080-exec-2] WARN o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions(137) - SQL Error: 1791, SQLState: 42000 2021-09-01 14:45:43.642 [http-nio-8080-exec-2] ERROR o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions(142) - ORA-01791: not a SELECTed expression

Before 5.3.4 i never got the extra select * from outer SQL select query.

Has anyone come across this problem ?
What can we do to fix it?
Any way in which we can configure the limithandler in the property files?


Did you try with 5.3.22.Final already? If the problem is still there, could you please create an issue on our issue tracker ( with a reproducer for this? You can workaround this by configuring an extended Oracle12cDialect that returns the previous limit handler.

Thx for the prompt reply.
I am using spring boot 2.4.3 which transiently brings hibernate 5.4.28.Final which still doesnt’ solve the problem. After introducing this

public class MyOracle12cDialect extends Oracle12cDialect {
public LimitHandler getLimitHandler() {
return SQL2008StandardLimitHandler.INSTANCE;

and introducing this in the<>.MyOracle12cDialect

the select is coming in the right place and I am able to sort on the onetomany property though . I will create a bug for the same as well.


Thanks for confirming and creating an issue. Please note that this is caused by HHH-14649 and HHH-14624