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

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