Uploaded image for project: 'Hibernate ORM'
  1. HHH-10965

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


    • Type: New Feature
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 5.2.1
    • Fix Version/s: 5.2.2
    • Component/s: None
    • Labels:
    • Last commented by a user?:
    • Sprint:


      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




            • Votes:
              0 Vote for this issue
              3 Start watching this issue


              • Created: