Allow pagination of distinct queries

[Mobe91]

trafficstars

Description

If we have a paginated query that uses DISTINCT, we fail with:

Cannot paginate a DISTINCT query

The check for this is:

if (selectManager.isDistinct()) {
    throw new IllegalStateException("Cannot paginate a DISTINCT query");
}

But actually, this is too restrictive. As long as no JOIN FETCH occurs (i.e. no members of joined associations are added to the select items) and the query is DISTINCT, it is fine. For example, the following query would be okay but fails in our case:

select distinct o1_0.id
from service_order o1_0
         join order_item i1_0 on o1_0.id = i1_0.order_id
where o1_0.tenant_id = ? offset ? rows fetch first ? rows only;

If JOIN FETCHing happens we would need to resort to an ID query.

This relates a bit to issues like https://github.com/Blazebit/blaze-persistence/issues/1770 where users may be tempted to make the paginated query distinct in order to prevent duplicate results.

This is also problematic because the same distinct query will be accepted by a "native" Spring Data JPA repsoitory, but not using the Blaze-Persistence integration. Using entity-view aware Spring Data JPA repositories as a drop-in replacement for native Spring Data JPA is hindered by this.

Expected behavior

A distinct query can be paginated.

Actual behavior

Paginating a distinct query is currently not supported.

Environment

Version: 1.6.14