[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2383) Generated SQL using setMaxResults/setFirstResult duplicate entities in the first page and subsequent pages in Oracle.

[Scott Stewart (JIRA)]

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

Scott Stewart commented on HHH-2383:
------------------------------------

I think I have the same problem as this and it has to do with generated SQL for the paging using the rownum keyword when ordering by a column that has many of the same values.  The issue is described rather well here: http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html.  Look near the bottom for the 'Pagination with ROWNUM' section.  To quote: '...the query cannot do it deterministically—the same sort order is not assured from run to run of the query...' when the values of the column are the same for several 'pages'.

Suggestions for 2 possiblities: 
1. Simply document it in the books and JavaDocs for setMaxResults() and setFirstResult() or 
2. Modify the generated SQL to always include an additional ordering of 'rowid' per the example in the above link.

> Generated SQL using setMaxResults/setFirstResult duplicate entities in the first page and subsequent pages in Oracle.
> ---------------------------------------------------------------------------------------------------------------------
>
>                 Key: HHH-2383
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2383
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: query-hql
>    Affects Versions: 3.2.2
>         Environment: Hibernate 3.2.2 GA, Oracle 10
>            Reporter: Carlos Eugênio P. da Purificação
>         Attachments: test.zip
>
>   Original Estimate: 4 days
>  Remaining Estimate: 4 days
>
> The generated SQL using setMaxResults/setFirstResult duplicate entities in the first page and subsequent pages.

-- 
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