Issues in migrating from Hibernate 5.3.3 to 5.3.4

Hibernate ORM
1 
Following is my domain model

    @Entity
    @Table(schema="XXX" ,name="YYY")
    public class User{
       @OneToMany(cascade = CascadeType.ALL)
       @JoinColumn(name="USER_KEY" , referencedColumnName="USER_KEY")
       private  List<UserArea> area;
    }
 
    @Entity
    public class UserArea{
        @Id
        @Column(name="USER_KEY")
        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);
criteriaquery.select(user);
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 *.

@Override
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?

Thx

2

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 (https://hibernate.atlassian.net/) with a reproducer for this? You can workaround this by configuring an extended Oracle12cDialect that returns the previous limit handler.

3

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 {
@Override
public LimitHandler getLimitHandler() {
return SQL2008StandardLimitHandler.INSTANCE;
}
}

and introducing this in the application.properties
spring.jpa.properties.hibernate.dialect=<>.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.

Thx

4

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