[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2113) Incorrect join ordering with filters and JOINED inheritance

[Emmanuel Bernard (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2113?page=comments#action_24653 ] 

Emmanuel Bernard commented on HHH-2113:
---------------------------------------

Hi Can you submit a simple running test case (wo the associations)? This will speed up the resolution.

> Incorrect join ordering with filters and JOINED inheritance
> -----------------------------------------------------------
>
>          Key: HHH-2113
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2113
>      Project: Hibernate3
>         Type: Bug

>     Versions: 3.2.0.cr4
>  Environment: Hibernate 3.2.0 CR4, MySQL 4.1.20
>     Reporter: Barney Boisvert

>
>
> I've a TextBlock object that extends AbstractContentObject (and has myriad other associations illustrated here, but irrelevant) and when doing a polymorphic query for all AbstractContentObjects Hibernate runs this SQL (note the line with the trailing asterisks):
>     select
>         textblockv0_.id as id21_5_,
>         textblockv0_.createTimestamp as createTi2_21_5_,
>         textblockv0_.hibernateVersionId as hibernat3_21_5_,
>         textblockv0_.lastUpdateTimestamp as lastUpda4_21_5_,
>         textblockv0_.lastVersionNumber as lastVers5_21_5_,
>         textblockv0_.activeVersionId as activeVe6_21_5_,
>         textblockv1_.id as id20_0_,
>         textblockv1_.createTimestamp as createTi2_20_0_,
>         textblockv1_.hibernateVersionId as hibernat3_20_0_,
>         textblockv1_.lastUpdateTimestamp as lastUpda4_20_0_,
>         textblockv1_.versionNumber as versionN5_20_0_,
>         textblockv1_.frozen as frozen20_0_,
>         textblockv1_.commitMessage as commitMe7_20_0_,
>         textblockv1_.title as title20_0_,
>         textblockv1_.summary as summary20_0_,
>         textblockv1_.text as text20_0_,
>         textblockv1_.versionHistoryId as version11_20_0_,
>         textblockv2_.id as id21_1_,
>         textblockv2_.createTimestamp as createTi2_21_1_,
>         textblockv2_.hibernateVersionId as hibernat3_21_1_,
>         textblockv2_.lastUpdateTimestamp as lastUpda4_21_1_,
>         textblockv2_.lastVersionNumber as lastVers5_21_1_,
>         textblockv2_.activeVersionId as activeVe6_21_1_,
>         textblock3_.id as id18_2_,
>         textblock3_1_.createTimestamp as createTi2_18_2_,
>         textblock3_1_.hibernateVersionId as hibernat3_18_2_,
>         textblock3_1_.siteId as siteId18_2_,
>         textblock3_1_.orderCol as orderCol18_2_,
>         textblock3_1_.folderId as folderId18_2_,
>         textblock3_.versionHistoryId as versionH2_19_2_,
>         site4_.id as id1_3_,
>         site4_.createTimestamp as createTi2_1_3_,
>         site4_.hibernateVersionId as hibernat3_1_3_,
>         site4_.lastUpdateTimestamp as lastUpda4_1_3_,
>         site4_.name as name1_3_,
>         site4_.parentId as parentId1_3_,
>         site4_.timeZone as timeZone1_3_,
>         site4_.familyId as familyId1_3_,
>         site4_.externalSiteAccessControl as external7_1_3_,
>         site4_.canonicalDomainId as canonic10_1_3_,
>         folder5_.id as id17_4_,
>         folder5_.createTimestamp as createTi2_17_4_,
>         folder5_.hibernateVersionId as hibernat3_17_4_,
>         folder5_.lastUpdateTimestamp as lastUpda4_17_4_,
>         folder5_.leftCol as leftCol17_4_,
>         folder5_.rightCol as rightCol17_4_,
>         folder5_.siteId as siteId17_4_,
>         folder5_.title as title17_4_ 
>     from
>         TextBlockVersionHistory textblockv0_ 
>     left outer join
>         TextBlockVersion textblockv1_ 
>             on textblockv0_.activeVersionId=textblockv1_.id 
>     left outer join
>         TextBlockVersionHistory textblockv2_ 
>             on textblockv1_.versionHistoryId=textblockv2_.id 
>     left outer join
>         TextBlock textblock3_ 
>             on textblockv0_.id=textblock3_.versionHistoryId 
>             and ? = textblock3_1_.siteId -- **************************************************
>     left outer join
>         AbstractContentObject textblock3_1_ 
>             on textblock3_.id=textblock3_1_.id 
>     left outer join
>         Site site4_ 
>             on textblock3_1_.siteId=site4_.id 
>     left outer join
>         Folder folder5_ 
>             on textblock3_1_.folderId=folder5_.id 
>     where
>         textblockv0_.id=?
> The highlighted condition is applied via a filter on AbstractContentObject:
>     @javax.persistence.Entity
>     @Inheritance(strategy = InheritanceType.JOINED)
>     @Filter(name = "siteVisibility", condition = ":siteId = siteId")
>     @Table(uniqueConstraints = @UniqueConstraint( columnNames = { "folderId", "order"}))
>     public abstract class AbstractContentObject
> However, the actual SQL condition is supplied to the wrong LEFT OUTER JOIN.  It should be supplied against the AbstractContentObject JOIN that immediately follows the condition.  I.e. move the condition down three lines, and it should work.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira