Hi, In JPQL/HQL, the DISTINCT keyword is meant to avoid returning the same parent entities when JOIN FETCHING parents with child associations. JPA backs it up too:
"The DISTINCT keyword is used to specify that duplicate values must be eliminated from the query result." However, I see that the DISTINCT is applied on the SQL statement as well. Why is it so? Is there any reason why we would want such a behaviour? The problem is that an unnecessary DISTINCT might affect the execution plan: http://use-the-index-luke.com/sql/join/nested-loops-join-n1-problem?langtype=java#orm-join "The distinct keyword in the SQL query is alarming because most databases will actually filter duplicate records. Only a few databases recognize that the primary keys guarantees uniqueness in that case anyway." Should we provide a Query hint like HINT_DISTINCT_SQL which when explicitly set, the JPQL DISTINCT keyword generates a DISTINCT keyword on the SQL statement-level as well. Without the hint set explicitly, we should not pass the DISTINCT to the SQL statement. What do you think? Vlad _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
