[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HHH-7001) setMaxResults/offset generates illegal SQL for DB2 mainframe

[Brad Cupit (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-7001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=45267#comment-45267 ] 

Brad Cupit edited comment on HHH-7001 at 1/26/12 3:02 PM:
----------------------------------------------------------

I could also provide a test case, but the test would only fail on DB2 for z/OS, which I suspect isn't available to the Hibernate team.

      was (Author: bradcupit):
    I could also provide a test case, but it would only fail against DB2 for z/OS, which I suspect the Hibernate team doesn't have access to.
  
> setMaxResults/offset generates illegal SQL for DB2 mainframe
> ------------------------------------------------------------
>
>                 Key: HHH-7001
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-7001
>             Project: Hibernate ORM
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 4.0.0.Final
>         Environment: DB2 v9 for z/OS (mainframe)
>            Reporter: Brad Cupit
>              Labels: db2, dialect
>
> the fix for HHH-2176 works for DB2 LUW (Linux/Unix/Windows) but does not work on DB2 for z/OS.
> Specifically {{order of}} is not supported inside over():
> {{select * from ( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_ from ( ...}}
> I worked around this issue by extending the DB2 dialect and changing the above SQL to:
> {{select * from ( select inner2_.*, rownumber() over(order by inner2_.SORT_COLUMN_ALIAS_HERE) as rownumber_ from ( ...}}
> or for cases when the subquery isn't ordered:
> {{select * from ( select inner2_.*, rownumber() over() as rownumber_ from ( ...}}
> Which works for DB2 LUW and DB2 for z/OS.
> I could provide a pull request, but to do this I had to parse the original SQL. *Is the Hibernate team interested in a pull request if the dialect has to parse SQL?*

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira