[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HHH-6849) SQLServer2005Dialect changes the order of selected columns when using pagination
Piotr Findeisen (JIRA)
noreply at atlassian.com
Mon May 14 07:09:11 EDT 2012
[ https://hibernate.onjira.com/browse/HHH-6849?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=46643#comment-46643 ]
Piotr Findeisen edited comment on HHH-6849 at 5/14/12 6:08 AM:
---------------------------------------------------------------
The fix applied is not applicable to all use cases. Consider JPQL
{code}
SELECT u.id, (SELECT COUNT(role.id) FROM Role role where ...) FROM User u
{code}
it will be converted into SQL like (
{code}
select user0_.userid as col_0_0_, (select count(role1_.roleid) from roles role1_) as col_1_0_ from SECUSER_UR user0_
{code}
Before HHH-6849 the {{row_number}} function was inserted just after first select like this
{code}
select <row_number_function>, user0_.userid as col_0_0_, (select count(role1_.roleid) from roles role1_) as col_1_0_ from SECUSER_UR user0_
{code}
and it yielded proper results to the application. After HHH-6849 the {{row_number}} function is inserted in the inner query, making the resulting SQL pretty invalid:
{code}
select user0_.userid as col_0_0_, (select count(role1_.roleid), <row_number_function> from roles role1_) as col_1_0_ from SECUSER_UR user0_
{code}
was (Author: piofinsy):
The fix applied is not applicable to all use cases. Consider JPQL
{code}
SELECT u.id, (SELECT COUNT(role.id) FROM Role role where ...) FROM User u
{code}
it will be converted into SQL like (
{code}
select user0_.userid as col_0_0_, (select count(role1_.roleid) from roles role1_) as col_1_0_ from SECUSER_UR user0_
{code}
Before HHH-6849 the {{row_number}} function was inserted just after first select like this
> SQLServer2005Dialect changes the order of selected columns when using pagination
> --------------------------------------------------------------------------------
>
> Key: HHH-6849
> URL: https://hibernate.onjira.com/browse/HHH-6849
> Project: Hibernate ORM
> Issue Type: Bug
> Components: core
> Affects Versions: 4.0.0.CR6
> Reporter: Hardy Ferentschik
> Assignee: Hardy Ferentschik
> Labels: dialect, sqlserver
> Fix For: 4.0.0.CR7
>
>
> The _SQLServer2005Dialect_ uses _ROW_NUMBER()_ to generate a limit string. However, it prepend the row number column instead of appending it (or not returning it at all). This let for example _PaginationTest#testLimitOffset_ fail.
> See also HHH-2655.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list