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);