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.