[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HHH-7043) SQLServer2005Dialect should not use alias in the ORDER BY clause when paging

[William Rose (JIRA)]

    [ https://hibernate.onjira.com/browse/HHH-7043?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=46545#comment-46545 ] 

William Rose edited comment on HHH-7043 at 5/6/12 10:33 PM:
------------------------------------------------------------

An alternative query template might help. 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}

The drawback to this query template is that if people order by column names that are not aliased and are qualified (e.g. a.col1 instead of col1), then it won't work for them.

I ran into this issue when trying to use fetch limits on a query with a group by:

{code:sql}
SELECT widget_code
     , MAX(purchase_date) AS last_purchase_date
FROM widget_purchases
GROUP BY widget_code
ORDER BY last_purchase_date DESC
{code}

If this is wrapped in the default query template it will not work: in the alternative above it would work.


      was (Author: wrose):
    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