Details

    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Last commented by a user?:
      true

      Description

      Problem:
      When a WITH condition for a LEFT OUTER JOIN of a map relation, which is mapped with a collection table, filters results by KEY the resulting query is wrong in my opinion.

      HQL:

      SELECT 
      	d.id 
      FROM 
      	Document d 
      LEFT JOIN 
      	d.contacts c 
      		WITH KEY(c) = 1
      

      Generated SQL:

      select
      	document0_.id as col_0_0_ 
      from
      	Document document0_ 
      left outer join
      	Document_Person contacts1_ 
      		on document0_.id=contacts1_.Document_id 
      left outer join
      	Person person2_ 
      		on contacts1_.contacts_id=person2_.id 
      		and (
      			contacts1_.position=1
      		)
      

      Hibernate 4.2.7.SP1 and below moved the condition "contacts1_.position=1" into the collection table join.
      Hibernate 4.2.8.Final+ always puts the condition in the second join.

      Solution:
      If such a WITH condition occurs, an additional where-condition has to be added. The where-condition has to be like ALIAS_FOR_VALUE_TABLE.ID IS NOT NULL where ALIAS_FOR_VALUE_TABLE is the name of the table, which contains the values of the map.

      Corrected generated SQL:

      select
      	document0_.id as col_0_0_ 
      from
      	Document document0_ 
      left outer join
      	Document_Person contacts1_ 
      		on document0_.id=contacts1_.Document_id 
      left outer join
      	Person person2_ 
      		on contacts1_.contacts_id=person2_.id 
      		and (
      			contacts1_.position=1
      		)
      where
      	person2_.id IS NOT NULL
      

      To summarize, this where-condition, only has to be added if

      • an on-condition contains a KEY expression for the joined relation
      • in a left outer join
      • of indexed(map- or list-attribute)-attributes
      • where the map-key-column or order-column is located in a collection table

        Attachments

          Issue links

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: