[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HHH-6849) SQLServer2005Dialect changes the order of selected columns when using pagination

[ https://hibernate.onjira.com/browse/HHH-6849?page=com.atlassian.jira.plug... ] 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 -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira