This issue has been documented on the [mailing list|http://lists.jboss.org/pipermail/hibernate-dev/2016-July/015086.html].
Basically, the reason for this enhancement can be summarized as follows:
{quote} 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:
" {quote} The DISTINCT keyword is used to specify that duplicate values must be
eliminated from the query result. " {quote}
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.{quote} |
|