Incorrect results when using EntityGraph with Paging

[zfilipova]

Hi!
An issue I am not able to find a solution for, so any help will be highly appreciated:

I am using Spring Boot v3.0.5 with Hibernate 6.1.7. I am trying to use pagination when loading the list of entities together with their related entities in the same query by using EntityGraph. The list of entities is loaded with a single DB call, but the pagination returns different numbers for the total number of elements. The same behavior applies to the case when a many-to-many relationship is set up using an entity for the mapping table.

The request /api/roles?size=25 returns proper number for totalElements:

{
    "content": [
      // entities here
    ],
 "pageable": {
        "sort": {
            "empty": true,
            "sorted": false,
            "unsorted": true
        },
        "offset": 0,
        "pageSize": 25,
        "pageNumber": 0,
        "unpaged": false,
        "paged": true
    },
    "last": false,
    "totalPages": 26,
    "totalElements": 628,
    "size": 25,
    "number": 0,
    "sort": {
        "empty": true,
        "sorted": false,
        "unsorted": true
    },
    "first": true,
    "numberOfElements": 25,
    "empty": false
}

When requesting /api/roles?size=50, the result is:

{
    "content": [
      // entities here
    ],
 "pageable": {
        "sort": {
            "empty": true,
            "sorted": false,
            "unsorted": true
        },
        "offset": 0,
        "pageSize": 50,
        "pageNumber": 0,
        "unpaged": false,
        "paged": true
    },
    "last": true,
    "totalPages": 1,
    "totalElements": 42,
    "size": 50,
    "number": 0,
    "sort": {
        "empty": true,
        "sorted": false,
        "unsorted": true
    },
    "first": true,
    "numberOfElements": 42,
    "empty": false
}

The repository:

public interface RoleRepository extends JpaRepository<Role, String>, JpaSpecificationExecutor<Role> {
    @EntityGraph(attributePaths = {"families"}, type = EntityGraph.EntityGraphType.LOAD)
    Page<Role> findAll(Specification<Role> spec, Pageable pageable);
}

The entity:

@Entity
@Data
public class Role implements Serializable {
    @Serial
    private static final long serialVersionUID = 1L;
    @Id
    private String name;

// other properties, getters and setters omitted for brevity

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "role_family_mapping",
            joinColumns = @JoinColumn(name = "role_type_name", referencedColumnName = "roleTypeName"),
            inverseJoinColumns = @JoinColumn(name = "role_family_name",
                    referencedColumnName = "roleFamilyName"))
    private Set<Family> families;

[mp911de]

Can you provide a minimal sample reproducing the problem using Spring Data JPA-only? A code base that contains the entity, repository, and a test that loads and verifies the outcome should be sufficient.

You could also upgrade to Hibernate 6.2 to see whether the issue persists.

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

[zfilipova]

Below is a minimal sample. The test should_paginate_properly_single_family will pass successfully, but the other will fail:
org.opentest4j.AssertionFailedError: expected: <100> but was: <28>
The issue is obviously related with the number of entitites in the families set - if more than one, than the issue is observed.

Entity:

@Entity
@Table(name = "role_types")
@Data
public class Role implements Serializable {
    @Serial
    private static final long serialVersionUID = 1L;
    @Id
    @Column(name = "roleTypeName")
    private String name;
    private LocalDateTime startDate;
    private LocalDateTime endDate;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "role_family_mapping",
            joinColumns = @JoinColumn(name = "role_type_name", referencedColumnName = "roleTypeName"),
            inverseJoinColumns = @JoinColumn(name = "role_family_name",
                    referencedColumnName = "roleFamilyName"))
    private Set<Family> families;
}

Repository:

@Repository
public interface RoleRepository extends JpaRepository<Role, String>, JpaSpecificationExecutor<Role> {
    @EntityGraph(attributePaths = {"families"}, type = EntityGraph.EntityGraphType.LOAD)
    Page<Role> findAll(Specification<Role> spec, Pageable pageable);
}

Filter:

public class RoleFilter {

    public static Specification<Role> filterBy(FilterDto filter) {
        return where(filterByEffectiveDate(filter.getEffectiveDate()));
    }

    private static Specification<Role> filterByEffectiveDate(LocalDateTime effectiveDate) {
        return (role, query, builder) -> builder.and(
                builder.lessThan(role.get("startDate"), effectiveDate),
                builder.or(builder.isNull(role.get("endDate")), builder.greaterThan(role.get("endDate"), effectiveDate))
        );
    }
}

FilterDto:

@Data
public class FilterDto {
    @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME)
    private LocalDateTime effectiveDate;
    private String search;

    public LocalDateTime getEffectiveDate() {
        return effectiveDate != null ? effectiveDate : LocalDateTime.of(LocalDate.now(), LocalTime.now());
    }
}

Tests:

@RunWith(SpringRunner.class)
@DataJpaTest
class RoleRepositoryTest {
    @Autowired
    TestEntityManager entityManager;
    @Autowired
    RoleRepository sut;
    private Family family1;
    private Family family2;
    private LocalDateTime referenceNow;
    private Specification<Role> spec;
    @BeforeEach
    public void setUp() {

        referenceNow = LocalDateTime.now();
        FilterDto filter = new FilterDto();
        filter.setEffectiveDate(referenceNow);
        spec = RoleFilter.filterBy(filter);

        // Setting roleFamily1
        family1 = new Family();
        family1.setName("FamilyOne");
        family1.setCreationUser("FOUNDATION");
        family1 = entityManager.persistAndFlush(family1);

        // Setting roleFamily2
        family2 = new Family();
        family2.setName("FamilyTwo");
        family2.setCreationUser("FOUNDATION");
        family2 = entityManager.persistAndFlush(family2);

    }
    @Test
    void should_paginate_properly_multiple_families() {
        LocalDateTime tenDaysAgo = referenceNow.minusDays(10);
        for(int i = 0; i < 100; i++) {
            Role entity = new Role();
            entity.setName("Role " + i);
            entity.setStartDate(tenDaysAgo);
            if (i > 24) {
                entity.setFamilies(Set.of(family1, family2));
            }
            else {
                entity.setFamilies(Set.of(family1));
            }
            entity = entityManager.persistAndFlush(entity);
        }

        PageRequest firstPageRequest = PageRequest.of(0, 10);
        var firstPage = sut.findAll(spec, firstPageRequest);
        PageRequest fifthPageRequest = PageRequest.of(2, 10);
        var thirdPage = sut.findAll(spec, fifthPageRequest);

        assertEquals(firstPage.getTotalElements(), thirdPage.getTotalElements());
    }
    @Test
    void should_paginate_properly_single_family() {
        LocalDateTime tenDaysAgo = referenceNow.minusDays(10);
        for(int i = 0; i < 100; i++) {
            Role entity = new Role();
            entity.setName("Role " + i);
            entity.setStartDate(tenDaysAgo);
            entity.setFamilies(Set.of(family1));
            entity = entityManager.persistAndFlush(entity);
        }

        PageRequest firstPageRequest = PageRequest.of(0, 10);
        var firstPage = sut.findAll(spec, firstPageRequest);
        PageRequest fifthPageRequest = PageRequest.of(2, 10);
        var thirdPage = sut.findAll(spec, fifthPageRequest);

        assertEquals(firstPage.getTotalElements(), thirdPage.getTotalElements());
    }
}

[mp911de]

With Hibernate 6.2.5 I'm no longer able to reproduce the issue. Seems that Hibernate has fixed something.

[deepak-s-s]

@mp911de can you specify the spring version as well. I am trying with Hibernate 6.2.5-Final and i am still getting the issue. and cannot see any solution for this issue as of now.