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

[Brad Cupit (JIRA)]

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


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