Problem when using Sort.by

[oungsi2000]

this is my whole code
https://github.com/oungsi2000/jump-to-springboot

When I use JpaRepository with Sort.by and Pageable, I got some mismatch with this.

com.ll.jumptospringboot.domain.Answer.AnswerService.getList

public Page<Answer> getList(Question question, int page, String sortBy) {
        List<Sort.Order> sorts = new ArrayList<>();
        if (Objects.equals(sortBy, "mostVoted")) {
            sorts.add(Sort.Order.desc("voter"));
            sorts.add(Sort.Order.desc("createDate"));
            Pageable pageable = PageRequest.of(page,10, Sort.by(sorts));
            return this.answerRepository.findAllByQuestion(question, pageable);
        } else {
            sorts.add(Sort.Order.desc("createDate"));
            Pageable pageable = PageRequest.of(page,10, Sort.by(sorts));
            return this.answerRepository.findAllByQuestion(question, pageable);
        }
    }

com.ll.jumptospringboot.domain.Answer.Answer

public class Answer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(columnDefinition = "TEXT")
    private String content;

    @Column(length = 200)
    private String title;

    private LocalDateTime createDate;

    @ManyToOne
    private Question question;

    @ManyToOne
    private SiteUser author;

    private LocalDateTime modifyDate;

    @ManyToMany
    Set<SiteUser> voter;
}

when using 'sorts.add(Sort.Order.desc("voter"));', If there are two or more voters, this.a
nswerRepository.findAllByQuestion(question, pageable); -> this doesn't get all Answers, instead gets pagination - 1 Answers

this is the result when the code above 'if (Objects.equals(sortBy, "mostVoted"))~~' executed
asdfad

this is the result when the code above 'else~~' executed
스크린샷 2024-12-18 오후 8 39 05

both counts should be same because the count means "All Answers" and it gets from same DB, same Hibernate query.
the difference between those is only 'sorts.add(Sort.Order.desc("voter"));' and attribute voter is @manytomany.
It is more strange that when the Entity Answer gets only one voter attribute, it works perfectly(both count is same) but when it gets two or more voter attribute, it just returns 9, not all of All Answers.

Please Help
If my English is Bad, I'm sorry. If you need extra explanation, Please notify me.

[christophstrobl]

Thank you for getting in touch. It would be great if you could take the time to reduce the linked sample application to a minimum. So just the repository, a failing testcase and if possible something using plain JPA without the spring bits to see if it is a spring or a persistence provider problem. Ideally using Testcontainers so that we can unzip or git clone, build, and deploy the reproducer.

[oungsi2000]

sorry for late reply, I was so busy..
I made minimum spring app testing JpaRepository.
when the parameter of sort.by is set instance of java.utils.Set, it goes wrong
please check

in the test code, method t4 is the checking method, and t1, t2, t3 is for generating database entity.
https://github.com/oungsi2000/JpaTest

[christophstrobl]

@oungsi2000 Thank you for providing the sample.
In the case of requesting page 0 the query only returns 2 entities which is less than the requested 5. In such a case spring-data does not initiate a total count. Running the same requesting page 1 instead gives back the requested 5 elements, triggering the extra count.
Looking at the SQL generated by the persistence provider the only difference between the two executions is the limit clause at the end which is limit ? (failing) vs limit ?,? (working) binding 5 for limit and 5, 5 for limit & offset.

select tem1_0.id,tem1_0.test_entity_one_id
from test_entity_many tem1_0
    left join test_entity_many_voter v1_0 on tem1_0.id=v1_0.test_entity_many_id
where tem1_0.test_entity_one_id=?
order by v1_0.voter_id desc,tem1_0.id
limit ?,?

You may enable logging to see it by setting:

logging.level.org.hibernate.SQL=debug
logging.level.org.hibernate.orm.jdbc.bind=trace

Since swapping the database to eg. HSQL works fine for both PageRequests it seems you're running into an issue that is outside of the control of spring-data-jpa.