We're updating the issue view to help you get more done. 

Add new Query Hint to avoid passing DISTINCT from entity queries to SQL statements

Description

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 as explained in this post.

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.

Environment

None

Status

Assignee

Vlad Mihalcea

Reporter

Vlad Mihalcea

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

5.2.1

Priority

Minor