Duplicate results when sorting using a Spring-Data pageable object on a JPA repository

Question

I have a rest-api that returns a list of users when called. The API uses the org.springframework.data.domain.Pageable to paginate and sort the results. This works by simply passing the pageable to the JPA repository, which then returns the desired page.

For some reason, when sorting by the first name, there is a chance that a duplicate entry will appear, but only if multiple entries have the same first name. However, this never happens when sorting by lastName. Both are simply strings in the entity, there is no discernable difference besides the property name.

Have any of you ever encountered this and if so, how did you fix it?

EDIT: To clarify, there is basically no logic of mine between the controller and the repository. I just pass the pageable through and return the results.

EDIT 2: Solved! Interesting titbit: The reason why the issue was only occurring when sorting by the first name was that only there were there always records that appeared on page 1 and 2, regardless which way the entries were sorted. Lucky me that our testers used that specific test data, or I might have never noticed.

Answer 1

Yes, I have seen that: a record can appear on, say, page 1 and then again on page 2.

This is an issue at the database level. For example 10 items per page and items at positions 10 and 11 have the same value for the property then it can be random which one appears in which position in each resultset.

Therefore apply a secondary sort on a unique property - the database ID for example - in order to ensure consistent ordering across requests.