SQLServer getLimitString() refactoring

Saturday, 2 June 2012

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

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006