[FIXED] Spring - Can't set Pageable to less than total result size

Issue

I have a weird situation where if I set the page size to less than the total size of the results, it errors out.

In my repo, have a giant query enclosed in a WITH to keep things simple, so paging should happen there (I also renamed some fields inside the quotes, and jpa can't find them unless they are nested), like so:

repo:

String QRY_DATA = "WITH result AS (<ton of code>) SELECT * FROM result";

@Query(nativeQuery = true, value = QRY_DATA)
Page<IBusinessDataDto> getData(UUID userId, Pageable pageable);

service:

...
Pageable pageable = PageRequest.of(page - 1, pageSize, Sort.Direction.ASC, "businessId");
Page<IBusinessDataDto> test = repository.getData(userId, pageable);
...

I have tested the query outside of the code, it works correctly and returns 2 results, if I set 'pageSize' to 3, it works perfectly in the code, but if I set it to 2 or 1, I get an error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "FROM"

I tried printing the flyway SQL, and I get the following:

Hibernate: WITH result AS (...) SELECT * FROM result order by businessId asc limit ?
Hibernate: WITH result AS (...) SELECT * FROM result
2021-11-11 15:58:48.108  WARN 17088 --- [nio-8091-exec-7] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42601
2021-11-11 15:58:48.108 ERROR 17088 --- [nio-8091-exec-7] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near "FROM"
  Position: 1006

I have no idea why that would matter or what the problem is, please advise.

Solution

Paging works by issuing a count query, which is missing in your code.

String QRY_DATA = "WITH result AS (<ton of code>) SELECT * FROM result";
String QRY_DATA_COUNT = "WITH result AS (<ton of code>) SELECT COUNT(*) FROM result";

@Query(nativeQuery = true, value = QRY_DATA, countQuery = QRY_DATA_COUNT)
Page<IBusinessDataDto> getData(UUID userId, Pageable pageable);

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query.native

Answered By - Andreas