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

[Noel Trout (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5915?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=41907#action_41907 ] 

Noel Trout commented on HHH-5915:
---------------------------------

I would object to the closing of the issue.  The fact that it turns out that setting firstResult and maxResults on the query is not the root cause is irrelevant.  It is still a Hibernate bug.  This is programmed to the public javax.persistence API that Hibernate supports.  The code was production code working in Hibernate 3.6, and failing in Hibernate 3.6.1.  It is a Hibernate bug.

> 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.jspa?mode=hide&requestId=11206
> 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));

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira