Hibernate duplicates results in a Spring JPA query only when Pageable = unpaged

Viewed 112

I have a curious Hibernate case running Spring Boot on a JPA repository, where depending on if my Pageable instance is unpaged, I get duplicate results or not...

I construct my Pageable object like this:

Pageable pageable;
if (size > 0) {
    pageable = PageRequest.of(page, size, sort);
} else {
    pageable = Pageable.unpaged()
}

For requests that do have a size, so they are paged, I get my objects back nicely. For Unpaged requests the resulting objects are duplicated.

The underlying query is a bit complex with some fetch joins and a count query for performance:

@Query(value = "select etv from EventTriggerVersion etv "
        + "inner join fetch etv.eventTrigger et "
        + "left join fetch etv.eventRules rules "
        + "left join fetch rules.conditionSets conditionSets "
        + "left join fetch conditionSets.conditions conditions "
        + "left join fetch conditionSets.dayTimeConditions dayTimeConditions "
        + "left join fetch conditions.leftOperand leftOperand "
        + "left join fetch conditions.rightOperand rightOperand "
        + "left join fetch leftOperand.leftOperand leftLeftOperand "
        + "left join fetch leftOperand.rightOperand leftRightOperand "
        + "left join fetch rightOperand.leftOperand rightLeftOperand "
        + "left join fetch rightOperand.rightOperand rightRightOperand "
        + "where etv.latestVersion = true" + " and et.project.id = :projectId "
        + "and (:skipSearch = true or lower(et.name) like lower(concat('%',cast(:search as java.lang.String),'%'))) "
        + "and (cast(:changedAfter as java.util.Date) is null or etv.createdDate >= :changedAfter)",
        countQuery = "select count(etv) from EventTriggerVersion etv "
            + "inner join etv.eventTrigger et "
            + "where etv.latestVersion = true" + " and et.project.id = :projectId "
            + "and (:skipSearch = true or lower(et.name) like lower(concat('%',cast(:search as java.lang.String),'%'))) "
            + "and (cast(:changedAfter as java.util.Date) is null or etv.createdDate >= :changedAfter)"
    )
    Page<EventTriggerVersion> findLatestVersionsInProject(@Param("projectId") UUID projectId,
        @Param("skipSearch") Boolean skipSearch, @Param("search") String search, @Param("changedAfter") Date changedAfter,
        Pageable pageable);

Now I CAN fix this with a distinct in the query and setting @QueryHints(value = @QueryHint(name = HINT_PASS_DISTINCT_THROUGH, value = "false")) but that might not be ideal for performance and I am just curious why this would be necessary only for unpaged pageables.

0 Answers
Related