duplicate records fetched while using findall method with specification and payable

[nagraj321]

I'm seeing some weird issue while working with findAll() method with the specification and pageable arguments. If I set the lower page i.e. 0 size i.e. 10 then I don't have any issues. but if I set the page i.e. 0 and size to 30 I'm getting the duplicate records.

e.g.

ID	NAME
1	test
2	test
3	test
4	test
5	test
6	test
7	test
8	test
9	test
1	test
2	test
3	test
4	test
11	test
6	test
7	test
18	test

I have the following in the database.

ID	NAME
1	test
2	test
3	test
4	test
5	test
6	test
7	test
8	test
9	test
10	test
11	test
12	test
13	test
14	test
15	test
16	test
17	test
18	test

@Entity
@Table(name = "table_name")
@JsonIgnoreProperties(ignoreUnknown = true)
@DynamicUpdate
public class Emp implements Persistable<String> {

    @Id
    @Column(name = "emp_id", nullable = false, columnDefinition = "BINARY(16)")
    public String empId;
    
    @Column(name = "name");
    public String name;
    // other fields
    
    //setters and getters
    
}

@Repository
public interface EmpRepository extends JpaRepository<Emp, String> {
    Page<Emp> findAll(Specification<Emp> specification, Pageable page);
}

public class EmpSpecification {
    public static Specification<Emp> nameEquals(String name) {
        return (root, query, builder) -> name == null ? null : builder.equal(root.get("name"), name);
    }
}

in the service class we are trying to fetch data using the following code:

Specification<Emp> specifications = Specification.where(EmpSpecification.nameEquals(name)));
details = consolidatedTradeSummaryRepository.findAll(specifications, page);

http://localhost:8080/emp?name=test&page=0&size=10
http://localhost:8080/emp?name=test&page=0&size=20

Spring boot version : 2.2.2.RELEASE

[AnghelLeonard]

Thank you for the comment. Well, most probably the issue is caused by a missing explicit sort. Try to add sort by ID (I assume this is a primary key), since the "name" has duplicates. I hope this helps.

[nagraj321]

Thanks for your response. I have tried by providing the sort option but still getting the same response.

[AnghelLeonard]

Ok, I'll look into it. Please, tell me what database vendor you used for this test?

[nagraj321]

I have used mysql database.

[danieal07]

Thanks for reporting this issue.

The duplicates you're seeing are likely due to the lack of a deterministic and unique sort in your Pageable request. When sorting only by non-unique fields like name, MySQL doesn’t guarantee consistent row order across pages, which can lead to duplicates.

To fix this, make sure to sort by a unique field such as empId:

PageRequest.of(page, size, Sort.by("empId").ascending());

If needed, combine it with other fields:

Sort.by("name").ascending().and(Sort.by("empId").ascending());