The JPQL or Criteria API DISTINCT is ignored unless FETCH is also supplied
Description
Assuming we have 1 parent Post entity with 5 PostComment child entities:
Post post = new Post();
post.setId(1L);
post.setTitle("High-Performance Java Persistence");
entityManager.persist(post);
for (long i = 0; i < 5; i++) {
PostComment comment = new PostComment();
comment.setId(i + 1);
comment.setReview("Best book on Java Persistence!");
post.addComment(comment);
}
When running this JPQL query:
List<Post> posts = entityManager.createQuery(
"select distinct p " +
"from Post as p " +
"inner join p.comments as pc " +
"where pc.review like :review", Post.class)
.setParameter("review", "Best book %")
.setHint(QueryHints.HINT_PASS_DISTINCT_THROUGH, false)
.getResultList();
assertSame(1, posts.size());
The assert will fail as 5 Post entity references (pointing to the same first-level cache entity entry) are returned, defeating the purpose of DISTINCT.
The problem is due to the containsCollectionFetches check in QueryTranslatorImpl:
Assuming we have 1 parent
Postentity with 5PostCommentchild entities:Post post = new Post(); post.setId(1L); post.setTitle("High-Performance Java Persistence"); entityManager.persist(post); for (long i = 0; i < 5; i++) { PostComment comment = new PostComment(); comment.setId(i + 1); comment.setReview("Best book on Java Persistence!"); post.addComment(comment); }When running this JPQL query:
List<Post> posts = entityManager.createQuery( "select distinct p " + "from Post as p " + "inner join p.comments as pc " + "where pc.review like :review", Post.class) .setParameter("review", "Best book %") .setHint(QueryHints.HINT_PASS_DISTINCT_THROUGH, false) .getResultList(); assertSame(1, posts.size());The assert will fail as 5
Postentity references (pointing to the same first-level cache entity entry) are returned, defeating the purpose of DISTINCT.The problem is due to the
containsCollectionFetchescheck inQueryTranslatorImpl:final boolean needsDistincting = ( query.getSelectClause().isDistinct() || getEntityGraphQueryHint() != null || hasLimit ) && containsCollectionFetches();That check ignores DISTINCT unless FETCH is also applied.