When use specification with group by pagination is broken

[pkuzmenkov]

Hi, guys, faced the same issue. Unfortunately, this "feature" totally breaks the pagination.
The problem is when I'm using 'groupBy' than a number of returned elements is much less than the totalElements and as a result totalPages is wrong too.

List<Long> totals = query.getResultList();

in my case totals looks like

col_0_0_

    2
    2
    1
    1

(4 rows)
So, the content will have only 4 elements.

But in the end, total will be 6
for (Long element : totals) { total += element == null ? 0 : element; }

Originally posted by @pkuzmenkov in #1034 (comment)

My specification is a little bit complicated, so I can't use distinct(true).
Generated query looks like this
select
count(presentati0_.id) as col_0_0_
from
presentation presentati0_
left outer join
presentation_label labels1_
on presentati0_.id=labels1_.presentation_id
left outer join
label label2_
on labels1_.label_id=label2_.id
left outer join
presentation_label labels3_
on presentati0_.id=labels3_.presentation_id
left outer join
label label4_
on labels3_.label_id=label4_.id cross
join
users user5_
where
presentati0_.user_id=user5_.id
and to_tsvector(coalesce(presentati0_.title, '')) || to_tsvector(coalesce(user5_.name, '')) || to_tsvector(coalesce(presentati0_.description, '')) || to_tsvector(coalesce(label4_.title, '')) || to_tsvector(coalesce(presentati0_.speaker_name, '')) @@ to_tsquery(?)=true
group by
presentati0_.id ,
user5_.name

[schauder]

Specifications are intended as abstractions over WHERE clauses.

Therefore we are not considering this.

[pkuzmenkov]

Sorry, but it is very confusing. When the result has only 4 records, but total shows 6. From my point of view it doesn't matter at all how I've constructed the query.
I'm talking about this code:

spring-data-jpa/src/main/java/org/springframework/data/jpa/repository/support/SimpleJpaRepository.java

Line 586 in 3b78bbf

List<S> content = total > pageable.getOffset() ? query.getResultList() : Collections.<S> emptyList();

In this case, the total could be much more than content.size

[robin850]

Hi,

Chiming in just to give my two cents ; I can totally understand a "Yup, but we won't fix this" answer, that's fine. :-)

Actually, it really feels like I'm missing something or the Specification interface is a bit misleading. If the purpose of this interface is solely to provide WHERE clauses, why the contract doesn't reflect that ?

At least, the return type of the method should be something like a list of a class that can be then translated to a where clause. Or maybe method parameters should be something like a wrapping class around JPA classes but that'd be read-only.

Also, it really feels like this kind of thing doesn't fit with the fact that specifications are considered abstractions over WHERE clauses if their behavior depends partially on the SELECT one.

I can understand that you don't want to go down a rabbit hole where you'd have to manage all parts of the query but the ORDER BY clause is considered as well.

A warning at runtime or a substantial disclaimer in the documentation might be great as well.

Anyway, thanks for your time !

[degr]

Specifications are intended as abstractions over WHERE clauses.
Therefore we are not considering this.

terrible response. It is high-level api, which allow to use group by without any warning or error, but returning wrong results.

It sounds like "you can use specification to obtain Pageable object, except it contains "group by", because it may return invalid totalCountOfElement. But no worry, it is how it intended to be, if you are doing "group by" and got incorrect results, it is not our mistake, it is your architecture lame".

Provide some kind of workaround, or throw warning at least

[RuhulxD]

It's frustrating that query is actually giving a wrong result.
@schauder if you are not allowing this to be fixed then please add it the documentation and do not allow people to add group by or any other criteria to the query specification at first place.
Now we've to look out for some workaround to fix those bug. 👎🏻

[ankitpec72]

Same for us. The count query with group by clause does not work. @schauder please fix this issue for all