[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-4121) DB2 Projection with paging and ordering generates illegal SQL. sqlerror -206

[Nick Wiedenbrück (JIRA)]

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

Nick Wiedenbrück commented on HHH-4121:
---------------------------------------

We worked around this issue by subclassing DB2Dialect and modifying the getRowNumber() method. For paging queries DB2Dialect generates SQL like the following:

select * from (select rownumber() over(order by y5_ asc) ... this_.foo as y5_ ... order by y5_ asc

So, the goal is to replace over(order by y5_ asc) by over(order by this_.foo asc). We implemented getRowNumber() in a way that first parses the sql string for alias definitions like this_.foo as y5_ and then replaces the aliases in the over() clause by their column names.

Of course, this is just a workaround and I can't tell if this works in all cases. At least, it worked for us so far.


> DB2 Projection with paging and ordering generates illegal SQL. sqlerror -206
> ----------------------------------------------------------------------------
>
>                 Key: HHH-4121
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4121
>             Project: Hibernate Core
>          Issue Type: Bug
>    Affects Versions: 3.2.6
>            Reporter: Nick Wiedenbrück
>
> We're using DB2 8.1 and we have a projection query with paging and sorting. The generated SQL looks like this:
> select * from ( select rownumber() over(order by y5_ asc) as rownumber_, [...] , this_.beginn as y5_,  [...] 
> from [...]
> order by y5_ asc ) as temp_ where rownumber_ <= ?
> This results in an error, because the query uses the alias in the over() part. If instead this_.beginn would be used it would possibly work.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira