Paged queries with GROUP BY clauses return the incorrect total count when there is only 1 result [DATAJPA-945]

[spring-projects-issues]

David Linfield opened DATAJPA-945 and commented

For example, if we have a data model in which one company has many products, and we wish to get pages of results listing the company name and the count of the number of products each company has as follows:

@Query(value = "select new uk.co.example.models.CompanyWithProductCount(c.id, c.name, cast(count(p) as integer)) " +
    "from Company c join c.products as p " +
    "group by c.id ")
Page<CompanyWithProductCount> getCompaniesWithProductCount(Pageable pageable);

The query performed to count the total number of results simply performs a count query with a GROUP BY clause, hence effectively returning a list of COUNT(p) for each Company. Where there are multiple companies there seems to be some special case handling where the total returned the the PageImpl object is actually the number of rows returned by this query (the correct answer), however when there is only one company returned, the total returned to the PageImpl object is actually the value returned by the COUNT query (i.e. the number of products that company has).

This bug is often concealed, because PageImpl uses the number of items in the Content of the Page if the supplied total is less than the page size provided. So to recreate in the case above you there must be more than pageSize Products associated with the Company

---

Affects: 1.10.1 (Hopper SR1)

0 votes, 6 watchers

[spring-projects-issues]

Paul Warren commented

For what it is worth I was able to reproduce this on the 1.10.x tagged code by adding this test to JpaQueryExecutionUnitTests:-

    /**
     * @see DATAJPA-945
     */
    @Test
    public void pagedExecutionShouldReturnCorrectTotalForCountQueriesThatContainOnlyASingleResult() throws Exception {

        Parameters<?, ?> parameters = new DefaultParameters(getClass().getMethod("sampleMethod", Pageable.class));
        when(jpaQuery.createCountQuery(Mockito.any(Object[].class))).thenReturn(countQuery);
        when(jpaQuery.createQuery(Mockito.any(Object[].class))).thenReturn(query);
        when(countQuery.getResultList()).thenReturn(Arrays.asList(3L));

        PagedExecution execution = new PagedExecution(parameters);
        Object result = execution.doExecute(jpaQuery, new Object[] { new PageRequest(0, 2) });

        assertThat(result, is(instanceOf(Page.class)));
        assertThat(((Page) result).getTotalElements(), is(1L));
    }

And ultimately I think it was due to this line of code:- https://github.com/spring-projects/spring-data-jpa/blob/1.10.x/src/main/java/org/springframework/data/jpa/repository/query/JpaQueryExecution.java#L184

i.e. if the number of rows returned by the count query is 1 then use the count, not the number of rows as the total (which ultimately becomes the pages total elements.

Now, I can still reproduce it on master but only when the page size is 1. Whilst this is, technically a bug I think, I am not sure who would ever use a page size of one

[spring-projects-issues]

victorlevasseur commented

Oliver Drotbohm This issue still exists in 2.0.4 and forces me to get an unpaged result in order to have all the results

[jack5505]

Hello community can I work on this task I only first time to want to in open source project thank you for your feedback