count method in JpaSpecificationExecutor ignores GROUP BY and returns wrong result. [DATAJPA-1573]

[spring-projects-issues]

Dimitri opened DATAJPA-1573 and commented

I have this code:

public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder builder){ 
... 
query.groupBy(root.get("id")); 
... 
}

Method

Repository.count(specification)

ignores GROUP BY and returns wrong result, while

Repository.findAll(specification).size()

returns right result.

This issue looks similiar to this problem: https://jira.spring.io/browse/DATAJPA-656

---

Affects: 2.1.9 (Lovelace SR9)

Attachments:

• datajpa-1573.zip (89.10 kB)

[spring-projects-issues]

Jens Schauder commented

Specifications aren't really intended for arbitrary manipulation of the query.
Instead they should just provide the where clause of a query.

In any case could you provide a reproducer showing the described behaviour?

If it is fixable as easy as DATAJPA-656 I still might consider it

[spring-projects-issues]

Dimitri commented

I made a small maven project that shows the problem. [^datajpa-1573.zip]

[vkrylosov]

Hello.
I met the same problem.

Method count of JpaSpecificationExecutor (SimpleJpaRepository) return wrong value.

@Override
public long count(@Nullable Specification<T> spec) {
    return executeCountQuery(getCountQuery(spec, getDomainClass()));
}

private static long executeCountQuery(TypedQuery<Long> query) {
    Assert.notNull(query, "TypedQuery must not be null!");
    List<Long> totals = query.getResultList();
    long total = 0L;
    for (Long element : totals) {
        total += element == null ? 0 : element;
    }
    return total;
}

If query doesn't contain grouping then totals will be list with 1 element - selected rows count.
But if query will have grouping then totals will have N elements with grouping rows count.
Actually I cannot imagen situation when we need sum of totals element.

For correct count method we need use list size - count of selected rows in original select.
I.e. something like:

List<Long> totals = new ArrayList<Long>();
if (totals.size() > 1) {
    return (long) totals.size();
 else  if (totals.size() == 0) {
    return 0L;
} else return totals.get(0);

Or we should change SQL generation for rows count if used "group by" in query...

May I expect a fix soon?