[hibernate-dev] DISTINCT keyword in JPQL

[Vlad Mihalcea]

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