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

Worked in

5.4.3

Suitable for new contributors

Yes, likely

Components

Affects versions

6.0.0.Alpha2

Priority

Major