[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-7043) SQLServer2005Dialect should not use alias in the ORDER BY clause when paging
[
https://hibernate.onjira.com/browse/HHH-7043?page=com.atlassian.jira.plug...
]
William Rose commented on HHH-7043:
-----------------------------------
I think the issue here is slightly different, in that the query template used for
pagination could be better constructed. The current query template is:
{code:sql}
WITH query AS (
select ROW_NUMBER() OVER(<<order by clause>>) AS __hibernate_row_nr__
, <<query columns>>
from <<table spec>>
<<rest of query>>
)
SELECT *
FROM query
WHERE __hibernate_row_nr__ BETWEEN ? AND ?
{code}
Because the ROW_NUMBER() function is at the same level as the original query expressions,
it cannot refer to their aliased names. If another level is introduced, it can:
{code:sql}
WITH q1 AS (
select <<query columns>>
from <<table spec>>
<<rest of query>>
), q2 AS (
SELECT ROW_NUMBER() OVER(<<order by clause>>) AS __hibernate_row_nr__
, *
FROM q1
)
SELECT *
FROM q2
WHERE __hibernate_row_nr__ BETWEEN ? AND ?
{code}
SQLServer2005Dialect should not use alias in the ORDER BY clause when
paging
----------------------------------------------------------------------------
Key: HHH-7043
URL: https://hibernate.onjira.com/browse/HHH-7043
Project: Hibernate ORM
Issue Type: Bug
Reporter: George Gastaldi
Assignee: Steve Ebersole
Using alias in the OVER() statement is wrong, as
http://msdn.microsoft.com/en-us/library/ms189461.aspx states that:
{quote}
When used in the context of a ranking window function, <ORDER BY Clause> can only
refer to columns made available by the FROM clause. An integer cannot be specified to
represent the position of the name or alias of a column in the select list. <ORDER BY
Clause> cannot be used with aggregate window functions.
{quote}
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira