Duplicate results when paging data.
Description
Attachments
Activity
![](https://secure.gravatar.com/avatar/0a81b8b5d21688d12a820dd0516853fd?d=https%3A%2F%2Favatar-management--avatars.us-west-2.prod.public.atl-paas.net%2Finitials%2FCB-4.png)
Christian Beikov March 1, 2017 at 6:34 PM
No, it's actually pretty standard to sort by something unique like an id to have deterministic ordering so just do it that way. Just make sure in your REST service that you also add an `Order` for the id if it is missing.
![](https://secure.gravatar.com/avatar/f7bb6066b6e0816c56ef8c9b66c4a53a?d=https%3A%2F%2Favatar-management--avatars.us-west-2.prod.public.atl-paas.net%2Finitials%2FRD-2.png)
Ryan Driskill March 1, 2017 at 5:59 PM
The way we are using it, the subquery actually returns all columns. We expose a RESTful service that allows paging and sorting on that data via Spring Data JPA. Any number of columns can be sorted on. When the UI sorts by fiscal year in descending order while also paging, the user sees the same data for a number of pages.
I would not expect that Hibernate would additionally sort by id automatically. With the proposed solution, that would not be necessary. Are there some concerns about implementing the proposed solution?
![](https://secure.gravatar.com/avatar/0a81b8b5d21688d12a820dd0516853fd?d=https%3A%2F%2Favatar-management--avatars.us-west-2.prod.public.atl-paas.net%2Finitials%2FCB-4.png)
Christian Beikov March 1, 2017 at 6:38 AM
Well since this is a scalar query, you have to think in terms of a scalar query result, so this can happen. What would you expect? That Hibernate automatically adds an order by element for the id behind the scenes?
![](https://secure.gravatar.com/avatar/f7bb6066b6e0816c56ef8c9b66c4a53a?d=https%3A%2F%2Favatar-management--avatars.us-west-2.prod.public.atl-paas.net%2Finitials%2FRD-2.png)
Ryan Driskill March 1, 2017 at 4:59 AM
Thanks Christian for your help. What your saying makes sense at the Oracle level. When using Hibernate to paginate data though, I would not expect duplicate results per page. The solution that I provided eliminates the need for having to order by additional columns.
![](https://secure.gravatar.com/avatar/0a81b8b5d21688d12a820dd0516853fd?d=https%3A%2F%2Favatar-management--avatars.us-west-2.prod.public.atl-paas.net%2Finitials%2FCB-4.png)
Christian Beikov March 1, 2017 at 12:30 AM
You have many different rows for the same FISCAL_YEAR and Oracle can order rows however it wants as long as all rows with FISCAL_YEAR 2016 come before FISCAL_YEAR 2015. If you additionally ORDER BY the ID, you get deterministic behavior and the pagination should work as expected.
When using Hibernate to query while paging and sorting the data, some pages have the same data. The issue appears to be in the hibernate-core class org.hibernate.dialect.Oracle9iDialect where it defines a AbstractLimitHandler. The default limit handler has a processSql() method that handles paging a query.
The create.sql attachment will create the tables and populate them with data. The description.md attachment provides detailed instructions on how to reproduce. The drop.sql attachment will drop the created tables. The Oracle10gDialectCustom.java solves the duplicate page data.