Hi Łukasz, does there already exist a pull-request or patch in regard so that it's possible to take a look at how it's implemented and test it?
Beside of this, in my opinion we should pay attention to not get SQLServer2005Dialect#getLimitString method to complex. Already now there are lots of string-operations executed when transforming the original query. And you know: the more complex a function becomes, the more likely it becomes affect to further bugs. In my opinion on rather complex queries (like the one of Quincy Leung) the user should issue directly a native-sql for his concrete database as we cannot expect the dialect coping with everything imaginable. best regards Guenther D. -----Original Message----- From: hibernate-dev-boun...@lists.jboss.org [mailto:hibernate-dev-boun...@lists.jboss.org] On Behalf Of Lukasz Antoniak Sent: Saturday, June 02, 2012 1:56 PM To: hibernate-dev@lists.jboss.org Subject: [hibernate-dev] SQLServer getLimitString() refactoring Hello all, Release 4.1.4 contained few fixes to SQLServer2005Dialect#getLimitString(String, boolean) method. The main JIRA issue (which has links to other related tickets) is HHH-7019. Quincy Leung added recently a comment about moving formula expressions to GROUP BY which causes query to fail. In case of SELECT DISTINCT usage in the original query, Hibernate moves all expressions from SELECT clause to GROUP BY, because otherwise adding ROW_NUMBER() function produces larger result set (an extra, unique column is added to DISTINCT expression; ticket HHH-5715). Quincy Leung suggested to omit formula expressions in GROUP BY clause, but I am not sure whether in every case GROUP BY applied to the subset of selected columns would work similarly to original DISTINCT. I have taken a step back and tried to modify original SQL query as little as possible to enable paging. Assumed original query: SELECT DISTINCT tab1.col1 col1 , tab1.col2 col2 , ( SELECT tab2.col3 FROM tmp_tab2 tab2 WHERE tab2.col1 = tab1.col1 ) col3 FROM tmp_tab1 tab1 WHERE tab1.col1 >= 1 ORDER BY tab1.col1; Applying limit expression: WITH query AS ( SELECT inner_query.* , ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ FROM ( SELECT DISTINCT TOP ${offset + last} tab1.col1 col1 , tab1.col2 col2 , ( SELECT tab2.col3 FROM tmp_tab2 tab2 WHERE tab2.col1 = tab1.col1 ) col3 FROM tmp_tab1 tab1 WHERE tab1.col1 >= 1 ORDER BY tab1.col1 ) inner_query ) SELECT * FROM query WHERE __hibernate_row_nr__ >= ${offset} AND __hibernate_row_nr__ < ${offset + last}; I have enclosed original query with "SELECT * FROM (...) inner_query" expression and put "ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__" outside. This forced me only to add TOP expression when original query contains ORDER BY clause to prevent error: "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.". Moreover, TOP might add performance hit, because it selects only first N rows from the original query. I strongly believe that this refactoring would resolve SQLServer's limit string errors but wanted to consult this modification with the community. Feel free to comment and post cases where proposed solution fails. Regards, Lukasz Antoniak _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
