[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5915) Bug with MSSQL Paging released in 3.6.1

Bug with MSSQL Paging released in 3.6.1 --------------------------------------- Key: HHH-5915 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5915 Project: Hibernate Core Issue Type: Bug Components: core, entity-manager Affects Versions: 3.6.1 Environment: Hibernate 3.6.1, MSSSQL 2008 R2, JPA 2 Reporter: Noel Trout Assignee: Strong Liu Fix For: 3.6.3, 4.0.0.Alpha3 Attachments: MSSQLPagingTest.java, org.hibernate.ejb.test.query.MSSQLPagingTest-output.txt, org.hibernate.ejb.test.query.MSSQLPagingTest.txt, README.txt This is related to all the changes in 3.6.1 for MSSQL (specifically paging). http://opensource.atlassian.com/projects/hibernate/secure/IssueNavigator.... When using the new paging, it is no longer possible to create an entity manager criteria query (javax.persistence.criteria) that takes parameters in the ORDER BY portion of the statement at the same time as the WHERE portion has parameters. Hibernate does not throw an exception, but the parameters are bound incorrectly so that when the query is sent to MSSQL a grammar exception is thrown. This is related in some way to how the dialect is transforming the query for the MSSQL Common Table Expression (CTE). Literal values still work in the ORDER BY portion. To reproduce, just create query an entity, ensuring there are parameters in both the ORDER BY and WHERE portions. Example WHERE from Our Code: criteriaQuery.where(builder.or( criteriaBuilder.equal(aircraftRoot.get(AircraftEntityImpl_.status), AircraftStatus.PAX_ACTIVE), criteriaBuilder.equal(aircraftRoot.get(AircraftEntityImpl_.status), AircraftStatus.PAX_CARGO_ACTIVE) )); Example ORDER BY from our code: // Broken as of 3.6.1, parameter bound - enum class with EnhancedUserType final Case<Object> wyvernCase = builder.selectCase(); wyvernCase.when(builder.isNull(wyvernStatusPath), WyvernOperatorStatus.NONE); wyvernCase.otherwise(wyvernStatusPath); criteriaQuery.orderBy(criteriaBuilder.desc(wyvernCase)); // Working with 3.6.1, integer literal constant final Case<Object> wyvernCase = builder.selectCase(); wyvernCase.when(builder.isNull(wyvernStatusPath), builder.literal(WyvernOperatorStatus.NONE.getCode())); wyvernCase.otherwise(wyvernStatusPath); criteriaQuery.orderBy(criteriaBuilder.desc(wyvernCase));