Spring application using Pageable always returns the same result from page 2

Question

I'm using spring to develop an application using sqlite as a database, but I'm having a problem implementing pagination in the repository's findAll(Pageable pageable) method.

For this implementation I'm using Pageable, and the problem is that, from the second page onwards, the result is always the same.

After some debugging I noticed that in the sql output the LIMIT and OFFSET clauses are switched. That is, imagining that I want 1 result per page, the LIMIT and OFFSET values are like this:

Page 1:

page=0 | size=1 -> LIMIT 1 OFFSET 0

Page 2:

page=0 | size=1 -> LIMIT 1 OFFSET 1

Page 3

page=0 | size=1 -> LIMIT 2 OFFSET 1

That is, the values of LIMIT and OFFSET are switched, as for the case of page 3 it should be: LIMIT 1 OFFSET 2

Does anyone know why this happens, and any possible solutions?

Answer 1

I already managed to solve the problem. The problem was in the use of a custom sql dialect that had a problem. I changed the implementation to use a maven dependency that handles sql dialect and it solved the problem.

Thank you