Spring PagingAndSorting repository returning incosistent values

Question

I have a problem with consistency, specifically with returning values when calling a page repository method.
I have a controller method, that has parameters: Long conferenceId, String query, Pageable pageable.

@GetMapping("/api/conferences/{conferenceId}/submissions")
public Page<SubmissionDetailDto> getPagedSubmissionsByConferenceId(
    @PathVariable("conferenceId") Long conferenceId,
    @RequestParam(required = false) Optional<String> query,
    Pageable pageable
) {
    System.out.println("Get paged submissions by conferenceId, by query, was called, " + conferenceId + ", " + query);
    return submissionService.getPagedSubmissionsByConferenceIdSearchBySurname(conferenceId, query, pageable);
}

From the controller I call a service method to return results based on these parameters.

public Page<SubmissionDetailDto> getPagedSubmissionsByConferenceIdSearchBySurname(Long conferenceId, Optional<String> query, Pageable pageable) {
    String searchQuery = query.isPresent() ? query.get() : "";
    return (Strings.isBlank(searchQuery)) ?
        mapToPagedSubmissionDetail(submissionPageRepository.searchByConferenceIdOrderByOrderNumber(conferenceId, pageable)) :
        mapToPagedSubmissionDetail(submissionPageRepository.findByConferenceIdAndContactsSurnameStartsWithIgnoreCase(conferenceId, searchQuery, pageable));
}

The service method calls a PagingAndSortingRepository method to return some values based on these parameters (id, query, Pageable) or only a searchByConferenceIdOrderByOrderNumber method.

@Repository
public interface SubmissionPageRepository extends PagingAndSortingRepository<SubmissionEntity, UUID> {
    Page<SubmissionEntity> searchByConferenceIdOrderByOrderNumber(Long conferenceId, Pageable pageable);
    Page<SubmissionEntity> findByConferenceIdAndContactsSurnameStartsWithIgnoreCase(Long conferenceId, String query, Pageable pageable);
}

The error is not quite obvious, but when I tried to search (by query): "B", the final number of elements comes to number, smaller than if i search by "Be", how could this be possible? The error disappears, when I increase the size of the pageSize from 10 (default by frontend) to 100, then the final number of elements is right.

I tried to call the method from Postman with page: 0, size: 10, sort: orderNumber,ASC and query: "B" And the numberOfElements was smaller, if i changed the query to "Be"

I tried to increase the pageSize from 10 to 100 and it worked, but i would like to have 10 results per page and not 100.

I think, that it could be because of the way the PagingAndSortingRepository handles the entities.

Postman pictures:

Postman GET request with query B

Postman GET request with query Be

Cause:

When lurking around the database, i deleted some of the contacts, so that a 1 submission has only 1 contact, and the result set is now correct. The implementation of the repository could be looking only at the number of submissions with join table, that equals to the pageSize, pageSize: 10 -> repository looks only at first 10 rows in a join table between submission and contacts.

Now, how could this be fixed?

FIX

I had to add GROUP BY clause to the SQL code, that was being sent to the database, because the generated SQL code, by JPA, was not working properly with this setup.

Answer 1

I added a GROUP BY to an existing clause, as a custom query, that was being sent to the database, that fixed this problem.