From noreply at atlassian.com Sun May  6 23:34:10 2012
Content-Type: multipart/mixed; boundary="===============2299143803495641505=="
MIME-Version: 1.0
From: William Rose (JIRA) <noreply at atlassian.com>
To: hibernate-issues at lists.jboss.org
Subject: [hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HHH-7043)
 SQLServer2005Dialect should not use alias in the ORDER BY clause when paging
Date: Sun, 06 May 2012 22:34:09 -0500
Message-ID: <16101602.264.1336361649610.JavaMail.j2ee-onjira@hibernate.onjira.com>
In-Reply-To: 32225353.6361.1328762469891.JavaMail.j2ee-onjira@hibernate.onjira.com

--===============2299143803495641505==
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


    [ https://hibernate.onjira.com/browse/HHH-7043?page=3Dcom.atlassian.jir=
a.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D46545#co=
mment-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 quer=
y 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 gro=
up 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 a=
lternative above it would work.


      was (Author: wrose):
    I think the issue here is slightly different, in that the query templat=
e used for pagination could be better constructed. The current query templa=
te 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 quer=
y 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 pag=
ing
> -------------------------------------------------------------------------=
---
>
>                 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.co=
m/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 can=
not 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

       =20

--===============2299143803495641505==--