Bug description

I created test application with custom repository with the help of spring boot v3.0.1 and org.springframework.boot:spring-boot-starter-data-jpa. With findAll function in repository I want to load related entities with entity graph. When I call this method I get single SQL request to DB with offset and fetch at the end. It lead to getting wrong result from db because with entity graph I get a cartesian product fom DB as a result. For example I can get different totalElements result for same data in DB.

@Repository
interface ClientRepository : JpaRepository<ClientEntity, Long> {

    @EntityGraph(attributePaths = ["accounts", "deposits"])
    override fun findAll(pageable: Pageable): Page<ClientEntity>
}

Client entity class

@Entity
@Table(name = "CLIENT")
class ClientEntity(

    @Id
    @Column(name = "ID")
    var id: Long? = null,

    @Embedded
    var clientName: ClientName? = null,

    @Embedded
    var clientAddress: Address? = null,

    @OneToMany(mappedBy = "client")
    var accounts: Set<AccountEntity> = mutableSetOf(),

    @OneToMany(mappedBy = "client")
    var deposits: Set<DepositEntity> = mutableSetOf()
)

Expected behavior

Got that result with spring boot 2.7.7 r.r.b.c.g.n.v.BankDemoNProblemController : getClientList(page: 0, size: 3, extracting_strategy: EXTRACTING_STRATEGY_ENTITY_GRAPH) WARN 94791 --- [atcher-worker-1] o.h.h.internal.ast.QueryTranslatorImpl : HHH000104: firstResult/maxResults specified with collection fetch; applying in memory! Hibernate: select cliententi0_.id as id1_2_0_, deposits1_.id as id1_3_1_, accounts2_.id as id1_0_2_, cliententi0_.address_city as address_2_2_0_, cliententi0_.address_flat as address_3_2_0_, cliententi0_.address_house as address_4_2_0_, cliententi0_.address_street as address_5_2_0_, cliententi0_.client_first_name as client_f6_2_0_, cliententi0_.client_last_name as client_l7_2_0_, cliententi0_.client_middle_name as client_m8_2_0_, deposits1_.amount as amount2_3_1_, deposits1_.client_id as client_i4_3_1_, deposits1_.rate as rate3_3_1_, deposits1_.client_id as client_i4_3_0__, deposits1_.id as id1_3_0__, accounts2_.amount as amount2_0_2_, accounts2_.client_id as client_i4_0_2_, accounts2_.number as number3_0_2_, accounts2_.client_id as client_i4_0_1__, accounts2_.id as id1_0_1__ from client cliententi0_ left outer join deposit deposits1_ on cliententi0_.id=deposits1_.client_id left outer join account accounts2_ on cliententi0_.id=accounts2_.client_id Hibernate: select count(cliententi0_.id) as col_0_0_ from client cliententi0_

You can see that SQLrequest presented above without offset ? rows fetch first ? rows only As a result I get right total elements for findAll(pageable: Pageable) with different pageable parameters. test4 test3 You can see above the same total count for page 1 and 2

Actual behavior

Got that result with spring boot 3.0.1 r.r.b.c.g.n.v.BankDemoNProblemController : getClientList(page: 0, size: 3, extracting_strategy: EXTRACTING_STRATEGY_ENTITY_GRAPH) Hibernate: select c1_0.id,a1_0.client_id,a1_0.id,a1_0.amount,a1_0.number,c1_0.address_city,c1_0.address_flat,c1_0.address_house,c1_0.address_street,c1_0.client_first_name,c1_0.client_last_name,c1_0.client_middle_name,d1_0.client_id,d1_0.id,d1_0.amount,d1_0.rate from client c1_0 left join account a1_0 on c1_0.id=a1_0.client_id left join deposit d1_0 on c1_0.id=d1_0.client_id offset ? rows fetch first ? rows only

As a result I get wrong total elements for findAll(pageable: Pageable) with different pageable parameters. test2 test1 You can see different total count for page 1 and page 2

I use

Kotlin - 1.7.21 Spring-boot - 2.7.7 and 3.0.1 Spring dependency management - 1.1.0

Comment From: Bives89

The pagination is being applied after the entity graph has been applied, which could be one reason why the incorrect number of elements is returned. This may occur if the entity graph is used with the findAll method and the pagination is then applied to the set that results.

You can try setting the @EntityGraph annotation's queryHints property to @QueryHint(name = "org.hibernate.fetchSize", value = "0") to resolve this problem. By turning off the fetch size hint for the query, pagination shouldn't be applied after the entity graph has been applied.

You could also try using the JpaRepository.

An example and a pageable object can both be specified as arguments in the findAll(Example, Pageable) method. This method will apply the pagination before the entity graph is applied, which should give you the correct result.

It's also worth noting that you may need to specify the fetch property of the @OneToMany annotations on your ClientEntity class to FetchType.LAZY in order to avoid loading all the related entities in a single query.

Comment From: arvgord

The pagination is being applied after the entity graph has been applied, which could be one reason why the incorrect number of elements is returned. This may occur if the entity graph is used with the findAll method and the pagination is then applied to the set that results. You can try setting the entitygraph annotation's queryHints property to @QueryHint(name = "org.hibernate.fetchSize", value = "0") to resolve this problem. By turning off the fetch size hint for the query, pagination shouldn't be applied after the entity graph has been applied.

With spring spring boot 2.7.7 I got right total entities and content size for three Clients in a DB. I got WARN 94791 --- [atcher-worker-1] o.h.h.internal.ast.QueryTranslatorImpl : HHH000104: firstResult/maxResults specified with collection fetch; applying in memory! it's not very efficient but it works.

You could also try using the JpaRepository.

With code in the example I already use JpaRepository.

An example and a pageable object can both be specified as arguments in the findAll(Example, Pageable) method. This method will apply the pagination before the entity graph is applied, which should give you the correct result.

I want to get correct result with findAll(pageable: Pageable).

It's also worth noting that you may need to specify the fetch property of the OneToMany annotations on your ClientEntity class to FetchType.LAZY in order to avoid loading all the related entities in a single query.

As you may know, OneToMany annotation uses the FetchType.LAZY by default.

Comment From: wilkinsona

Thanks for the report but this behaviour is out of Spring Boot's control. Pagination is a Spring Data feature. I suspect the described behaviour is due to either a change in Spring Data or a change in Hibernate. I'd recommend opening a Spring Data JPA issue with a complete yet minimal sample that reproduces the problem.

Comment From: thejeff77

@arvgord did you log an issue with the spring data team?