title: Hint HINT_PASS_DISTINCT_THROUGH reduces the amount of Entities returned per page for a PageRequest down to below the configured page size (PostgreSQL)
body: <p>I'm setting up a JPA Specification based repository implementation that utilizes jpa specifications(constructed based on RSQL filter strings) to filter the results, define result ordering and remove any duplicates via &quot;distinct&quot; that would otherwise be returned due to joined tables. The JPA Specification builder method joins several tables and sets the &quot;distinct&quot; flag:</p>
  <pre><code>final Join&lt;Object, Object&gt; rootJoinedTags = root.join(&quot;tags&quot;, JoinType.LEFT);
  final Join&lt;Object, Object&gt; rootJoinedLocations = root.join(&quot;location&quot;, JoinType.LEFT);
  ...
  query.distinct(true);
  </code></pre>
  <p>To allow sorting by joined table columns, I've applied the &quot;HINT_PASS_DISTINCT_THROUGH&quot; hint to the relevant repository method(otherwise, sorting by joined table columns returns an error along the lines of &quot;sort column must be included in the SELECT DISTINCT query&quot;).</p>
  <pre><code>@QueryHints(value = {
          @QueryHint(name = org.hibernate.jpa.QueryHints.HINT_PASS_DISTINCT_THROUGH, value = &quot;false&quot;)
      })
      Page&lt;SomeEntity&gt; findAll(@Nullable Specification&lt;SomeEntity&gt; spec, Pageable pageable);
  </code></pre>
  <p>The arguments for said repository method are constructed as such:</p>
  <pre><code>final Sort sort = getSort(searchFilter);
          final Specification spec = getSpecificationIfPresent(searchFilter);
          final PageRequest pageRequest = PageRequest.of(searchFilter.getPageNumber(), searchFilter.getLimit(), sort);
  
          return eventRepository.findAll(spec, pageRequest);
  </code></pre>
  <p>After those changes, filtering and sorting seem to work as expected. However, the hint seems to cause &quot;distinct&quot; filtering to be applied after the result page is already constructed, thus reducing the number of returned entities in the page from the configured &quot;size&quot; PageRequest argument, to whatever is left after the duplicates are filtered out. For example, if we'd make a PageRequest with &quot;page=0&quot; and &quot;pageSize=10&quot;, then the resulting Page may return only 5 &quot;SomeEntity&quot; instances, although the database contains way more entries(177 entities to be exact in this case). If I remove the hint, then the returned entities number is correct again.</p>
  <p>Question: is there a way to make the same Specification query setup work with correctly sized Pages(some other hints that might be added to have duplicate filtering performed before the Page object is constructed)? If not, then is there another approach I could use to achieve the required Specification-based filtering, with joined-column sorting and duplicate removal as with &quot;distinct&quot;?</p>
  <p>PS: PostgreSQL is the database behind the application in question</p>