FindAll by Pageable the countQuery was missing condition

[smokeInCloud060201]

I am using SpringBoot-Starter-Data-JPA-Version: 3.3.3. When I use Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable), I will get 2 queries. The first query is correct, but the second query, with it's use to count total elements was missing a condition..

The first query SQL result:

 select
        distinct w1_0.id,
        w1_0.auth0_role,
        w1_0.contract_id,
        w1_0.contractor_admin_id,
        w1_0.contractor_name,
        w1_0.contractor_project_manager_id,
        w1_0.contractor_safety_personnel_id,
        w1_0.cover_sppo_id,
        w1_0.cover_supervisor_id,
        w1_0.created_at,
        w1_0.created_by,
        w1_0.description,
        w1_0.end_work_date_time,
        w1_0.is_deleted,
        w1_0.is_on_premise,
        w1_0.latitude,
        w1_0.location,
        w1_0.longitude,
        w1_0.section_id,
        w1_0.sppo_id,
        w1_0.start_work_date_time,
        w1_0.status,
        w1_0.supervisor_id,
        w1_0.updated_at,
        w1_0.zone 
    from
        worksite w1_0 
    where
        (
            w1_0.is_deleted = false
        ) 
        and w1_0.status in (?, ?, ?, ?) 
        and (
            w1_0.contract_id in ((select
                distinct cs3_0.contract_id 
            from
                contract_sppo cs3_0 
            where
                cs3_0.sppo_id=?)) 
            or w1_0.sppo_id=? 
            or w1_0.cover_sppo_id=?
        ) 
    order by
        w1_0.created_at desc 
    offset
        ? rows 
    fetch
        first ? rows only

The second query, use to count total elements result:

select
       distinct count(distinct w1_0.id) 
   from
       worksite w1_0 
   where
       (
           w1_0.is_deleted = false
       ) 
       and (
           w1_0.contract_id in ((select
               distinct cs1_0.contract_id 
           from
               contract_sppo cs1_0 
           where
               cs1_0.sppo_id=?)) 
           or w1_0.sppo_id=? 
           or w1_0.cover_sppo_id=?
       )

The condition and w1_0.status in (?, ?, ?, ?) was missing in the countQuery
So when I try to use findAll() to pageable will get incorrect result.

[christophstrobl]

Thank you @smokeInCloud060201 for getting in touch. Please take the time to provide a complete minimal sample (something that we can unzip or git clone, build, and deploy) that reproduces the problem.

[smokeInCloud060201]

Hi @christophstrobl. I understand why. I don't override the toPredicate() method

@Override
    public Predicate toPredicate(Root<E> root,
                                 CriteriaQuery<?> criteriaQuery,
                                 CriteriaBuilder builder) {}

I do

  Specification<Entity> specs = ((root, query, criteriaBuilder) -> {
                    ...
                    return predicates;
                };

and use this specs to findAll. That is reason. But have a little bit strange that's not at all Entity was missing. I use it for some Entity class but only one was missing.

[smokeInCloud060201]

And I also get a new issue. I don't know should I create new issue or tell it in hear.
That's Hibernate issue. In Hibernate 6.x.x we can not use same Join, Path,... with same CriteriaQuery for more than 1 query.
Meaning for each query we need to rebuild the Predicate, Joining,... or copy it.
And you know when use JPA pageable feature, we need to run 2 queries, one to get records one to get total elements.
So, we will get the the error likes:

Already registered a copy: SqmSingularJoin

or something like that.
I see many people got same issue with me when upgrade from SpringBoot 2.x.x to SpringBoot 3.x.x

I fixed that by use Hibernate feature instead JPA now, but I don't know if you already fix that or have plan to do that

[mp911de]

Still waiting for a functional sample.

[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.