| This issue has been documented on the mailing list. Basically, the reason for this enhancement can be summarized as follows:
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.
|