Skip to content

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

Open
@spring-projects-issues

Description

@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

Metadata

Metadata

Assignees

Labels

in: query-parserEverything related to parsing JPQL or SQLtype: bugA general bug

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions