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

Incorrect SQL generated when use both left join with unrelated entity and implicit join to another entity in select-clause

    Details

    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Suitable for new contributors:
      Yes, likely
    • Last commented by a user?:
      true
    • Sprint:

      Description

      For hql queries containig both explicit join with unrelated entity in from clause and implicit join with related entity (via path expression e1.e2.field) in select clause invalid SQL is generated.

      simple test is here: https://github.com/stasal/hibernate-tests and in attached documents

      Example:
      HQL: select a.c.d, b.e from A a left join B b on b.z = a.z
      From-clause for generated SQL will look something like following:

      from A a0_, C c2_ 
        left outer join B b1_ 
          on (b1_.z =a0_.z)
      

      In SQL-99 comma has lesser precedence then join, that is why database tries to execute left join at first (for A and B) but the left side of join is just C entity (not (A, C) or (A cross join C)) that is why such SQL is not executed (RDBMS couldn't find the join column in C entity because it is in A entity which is not yet participating in From - it should be joined after "left join" operation).

      The issue is related to:
      https://hibernate.atlassian.net/browse/HHH-1480
      https://hibernate.atlassian.net/browse/HHH-5352

      To genereate correct SQL we need to substitute comma ", " with "cross join". In other words - use org.hibernate.dialect.Dialect#getCrossJoinSeparator method while generating SQL. But now there is a hardcoded ", " in method org.hibernate.hql.internal.ast.SqlGenerator#nestedFromFragment

        Attachments

          Issue links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: