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
