We're updating the issue view to help you get more done. 

order by is being ignored when using CriteriaBuilder

Description

consider the following piece of code:

1 2 3 4 5 6 7 8 9 try(Session session = sessionFactory.openSession()) { CriteriaBuilder cb = session.getCriteriaBuilder(); CriteriaQuery<Airport> cq = cb.createQuery(Airport.class); Root<Airport> root = cq.from(Airport.class); cq.select(root).orderBy(cb.asc(root.get("name"))); List<Airport> result = session.createQuery(cq).setMaxResults(1).getResultList(); }

it generates the following SQL, which is wrong:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 select a1_0.ident, a1_0.continent, a1_0.elevation_ft, a1_0.gps_code, a1_0.home_link, a1_0.iata_code, a1_0.id, a1_0.iso_country, a1_0.iso_region, a1_0.keywords, a1_0.latitude_deg, a1_0.local_code, a1_0.longitude_deg, a1_0.municipality, a1_0.name, a1_0.scheduled_service, a1_0.type, a1_0.wikipedia_link from airport as a1_0

moreover, if I change the code as follows:

1 2 3 4 5 6 7 8 9 try(Session session = sessionFactory.openSession()) { CriteriaBuilder cb = session.getCriteriaBuilder(); CriteriaQuery<Long> cq = cb.createQuery(Long.class); Root<Airport> root = cq.from(Airport.class); cq.select(root.get("id")).orderBy(cb.asc(root.get("name"))); List<Long> result = session.createQuery(cq).setMaxResults(1).getResultList(); }

I get cast exception

1 2 3 4 5 6 7 8 9 10 11 Caused by: java.lang.ClassCastException: class org.hibernate.query.criteria.spi.SingularPathBasic cannot be cast to class org.hibernate.query.sqm.tree.select.SqmSelectableNode (org.hibernate.query.criteria.spi.SingularPathBasic and org.hibernate.query.sqm.tree.select.SqmSelectableNode are in unnamed module of loader 'app') at org.hibernate.query.criteria.sqm.CriteriaQueryToSqmTransformer.visitSelectClause(CriteriaQueryToSqmTransformer.java:178) ~[hibernate-core-6.0.0.Alpha2.jar:6.0.0.Alpha2] at org.hibernate.query.criteria.sqm.CriteriaQueryToSqmTransformer.visitQueryStructure(CriteriaQueryToSqmTransformer.java:103) ~[hibernate-core-6.0.0.Alpha2.jar:6.0.0.Alpha2] at org.hibernate.query.criteria.sqm.CriteriaQueryToSqmTransformer.visitRootQuery(CriteriaQueryToSqmTransformer.java:88) ~[hibernate-core-6.0.0.Alpha2.jar:6.0.0.Alpha2] at org.hibernate.query.criteria.sqm.CriteriaQueryToSqmTransformer.transform(CriteriaQueryToSqmTransformer.java:57) ~[hibernate-core-6.0.0.Alpha2.jar:6.0.0.Alpha2] at org.hibernate.query.sqm.produce.internal.SemanticQueryProducerImpl.interpret(SemanticQueryProducerImpl.java:72) ~[hibernate-core-6.0.0.Alpha2.jar:6.0.0.Alpha2] at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:3151) ~[hibernate-core-6.0.0.Alpha2.jar:6.0.0.Alpha2] at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:183) ~[hibernate-core-6.0.0.Alpha2.jar:6.0.0.Alpha2] at com.apulbere.hibernate6perf.Hibernate6perfApplication.run(Hibernate6perfApplication.java:38) ~[classes/:na] at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:820) ~[spring-boot-2.2.0.BUILD-SNAPSHOT.jar:2.2.0.BUILD-SNAPSHOT] ... 5 common frames omitted

Note that running the above code with a local build from wip/6.0 branch produces a different error:

1 2 3 Caused by: java.lang.NullPointerException: null at org.hibernate.query.sqm.tree.select.SqmSelectStatement.orderBy(SqmSelectStatement.java:138) ~[hibernate-core-6.0.0-SNAPSHOT.jar:6.0.0-SNAPSHOT] at org.hibernate.query.sqm.tree.select.SqmSelectStatement.orderBy(SqmSelectStatement.java:33) ~[hibernate-core-6.0.0-SNAPSHOT.jar:6.0.0-SNAPSHOT]

and below is a new test case for org.hibernate.orm.test.query.criteria.BasicCriteriaExecutionTests which fails now

1 2 3 4 5 6 7 8 9 10 11 12 13 @Test public void testExecutingBasicCriteriaQueryOrderBy() { final HibernateCriteriaBuilder criteriaBuilder = sessionFactory().getQueryEngine().getCriteriaBuilder(); final JpaCriteriaQuery<Object> criteria = criteriaBuilder.createQuery(); final JpaRoot<BasicEntity> root = criteria.from( BasicEntity.class ); criteria.select( root ); criteria.orderBy(criteriaBuilder.asc(root.get("id"))); sessionFactoryScope().inSession( session -> session.createQuery( criteria ).list() ); }

Environment

None

Status

Assignee

Unassigned

Reporter

Adrian Pulbere

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

Yes, likely

Requires Release Note

None

Pull Request

None

backportDecision

None

Worked in

5.4.3

Components

Affects versions

6.0.0.Alpha2

Priority

Major