BTW {{org.hibernate.dialect.pagination.SQLServer2005LimitHandler.getProcessedSql()}}, as its javadoc says, produces queries like
{code}
WITH query AS (
SELECT inner_query.*
, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__
FROM ( original_query_with_top_if_order_by_present_and_all_aliased_columns ) inner_query
)
{code}
According to SQL Server's docs for [ROW_NUMBER|http://technet.microsoft.com/en-us/library/ms186734.aspx]
{quote}
There is no guarantee that the rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution unless the following conditions are true.
\[...]
Values of the ORDER BY columns are unique.
\[...]
{quote}
However, {{ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP)}} does not satisfy this condition. If i misunderstood the SQL Server docs and this is correct usage or {{ROW_NUMBER()}}, then {{CURRENT_TIMESTAMP}} could be replaced with constant expression (e.g. {{1}}) -- not to give an impression that there is anything sorted there.
On the other hand, if I understood right the docs, the correct usage of {{ROW_NUMBER()}} would be to _move_ the original {{order by}} clause into {{ROW_NUMBER() OVER (ORDER BY ...)}}.
If such a change was applied, then -- as I side effect , -- the "{{SQLServerException: A column has been specified more than once}}" error would not appear, as SQL Server is more permissive when it comes to {{ROW_NUMBER() OVER (ORDER BY ...)}} contents.
|
