Hibernate’s unique list result handling seems to be in violation of the JPA specification.

JPA spec 3.1, Chapter “6.5.11. Specifying the Select List” states:

The distinct method of the CriteriaQuery interface is used to specify that duplicate values must be
eliminated from the query result. If the distinct method is not used or distinct(false) is invoked on the
criteria query object, duplicate values are not eliminated. When distinct(true) is used, and the select
items include embeddable objects or map entry results, the elimination of duplicates is undefined.

However, this is not the case in version 6.2.2, as the list results are always made unique, even if the SQL query returns multiple non-unique rows, so the behaviour is always like distinct=true, even if distinct is set to false.

This is especially problematic for paging like Spring Data JPA’s implementation, as this uses the same criteria with a limit to load the page and with a count projection to load the total size. Due to the returned results being made distinct by hibernate, the numbers do not match and can lead to weird results, e.g. the page might have less entries than the selected page size, the total count does not match the count of the list results.

Hibernate should not make list results unique by default if distinct is set to false, instead it should return a list item for each row delivered by the SQL statement.

Will provide a test case.