HQL pagination returns same result on different pages - with SQL We have the following query in the server (using sql):

Query query = this.entityManager.createQuery(
           "SELECT type, id, date, amount FROM Transaction ORDER BY type ASC");


The type field is not unique.

In order to implement pagination, we use the following code:

query.setFirstResult(currentPage * pageSize).setMaxResults(pageSize);


Meaning that if we want a page size of 10, and we have 15 results overall, the client sends currentPage = 0, pageSize = 10 to display the first page, and currentPage = 1, pageSize = 10 to display the second page.

It seems that in cases where the total results > page size we get the same results on different pages, and some result lines are not displayed at all (on any page).
Our guess is that this issue has to do with the fact that the sort field is not unique, because if we use the following query, it does not occur:

"SELECT type, id, date, amount FROM Transaction ORDER BY type ASC, id ASC"


Does anyone know why this happens? And how to solve it? (Other that just adding the id as a sort field as well).
If you do not specify any order, by SQL definition databases can return the rows ordered any way they want.

You make it order by nonunique values so each "group" with the same value can be returned in any order - usually this "random" ordering is quite stable but that is mostly incidental as your case shows.

The workaround you found is a good one, use it - if your main ordering is by non-unique column, add a unique column to make the ordering well defined. Usually the ID/PK column is a good one to add, but any other unique column is 
good enough from definition standpoint, only the PK may have some performance benefit in many case (InnoDB implicit clustering as an example) but really what is important is that the combination of columns used for ordering is unique, so even adding a non-unique column may be enough if the resulting ordering is unique.