[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5860) Sql Join clauses generated in incorrect order when implied joins are created from the select clause

Sql Join clauses generated in incorrect order when implied joins are created from the select clause --------------------------------------------------------------------------------------------------- Key: HHH-5860 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5860 Project: Hibernate Core Issue Type: Bug Components: core Affects Versions: 3.6.0 Reporter: Mike Q Priority: Critical I have the following object model object TimeSeriesPeriod has many-to-one to TimeSeriesQuality has many-to-one to MeterPointTimeSeries object TimeSeriesQuality object MeterPointTimeSeries has many-to-one to MeterPoint object MeterPoint Writing the following HQL generates incorrect SQL. has many-to-one to TimeSeriesQuality {noformat} select t.meterPointTimeSeries.meterPoint, t.timeSeriesQuality from TimeSeriesPeriod t {noformat} The SQL AST generated is as follows. {noformat} \-[SELECT] QueryNode: 'SELECT' querySpaces (MeterPoint,TimeSeriesQuality,MeterPointTimeSeries,TimeSeriesPeriod) +-[SELECT_CLAUSE] SelectClause: '{select clause}' | +-[DOT] DotNode: 'meterpoint2_.id as id222_0_' {propertyName=meterPoint,dereferenceType=EOF,propertyPath=meterPoint,path=t.meterPointTimeSeries.meterPoint,tableAlias=meterpoint2_,className=com.junifer.thor.database.hibernate.objects.MeterPoint,classAlias=null} | | +-[DOT] DotNode: 'timeseries0_.meterPointTimeSeriesFk' {propertyName=meterPointTimeSeries,dereferenceType=EOF,propertyPath=meterPointTimeSeries,path=t.meterPointTimeSeries,tableAlias=meterpoint1_,className=com.junifer.thor.database.hibernate.objects.MeterPointTimeSeries,classAlias=null} | | | +-[ALIAS_REF] IdentNode: 'timeseries0_.id' {alias=t, className=com.junifer.thor.database.hibernate.objects.TimeSeriesPeriod, tableAlias=timeseries0_} | | | \-[IDENT] IdentNode: 'meterPointTimeSeries' {originalText=meterPointTimeSeries} | | \-[IDENT] IdentNode: 'meterPoint' {originalText=meterPoint} | +-[DOT] DotNode: 'timeseries3_.id as id438_1_' {propertyName=timeSeriesQuality,dereferenceType=EOF,propertyPath=timeSeriesQuality,path=t.timeSeriesQuality,tableAlias=timeseries3_,className=com.junifer.thor.database.hibernate.objects.TimeSeriesQuality,classAlias=null} | | +-[ALIAS_REF] IdentNode: 'timeseries0_.id' {alias=t, className=com.junifer.thor.database.hibernate.objects.TimeSeriesPeriod, tableAlias=timeseries0_} | | \-[IDENT] IdentNode: 'timeSeriesQuality' {originalText=timeSeriesQuality} | +-[SQL_TOKEN] SqlFragment: 'meterpoint2_.assetFk as assetFk222_0_, meterpoint2_.deleteFl as deleteFl222_0_, meterpoint2_.measurand as measurand222_0_, meterpoint2_.meterTypeFk as meterTyp7_222_0_, meterpoint2_.partitionId as partitio4_222_0_, meterpoint2_.versionNo as versionNo222_0_' | \-[SQL_TOKEN] SqlFragment: 'timeseries3_.code as code438_1_, timeseries3_.deleteFl as deleteFl438_1_, timeseries3_.iconTblFk as iconTblFk438_1_, timeseries3_.internalKey as internal4_438_1_, timeseries3_.languageKey as language5_438_1_, timeseries3_.orderNo as orderNo438_1_, timeseries3_.partitionId as partitio7_438_1_, timeseries3_.versionNo as versionNo438_1_' +-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=4, fromElements=4, fromElementByClassAlias=[t], fromElementByTableAlias=[meterpoint1_, meterpoint2_, timeseries3_, timeseries0_], fromElementsByPath=[t.timeSeriesQuality, t.meterPointTimeSeries.meterPoint, t.meterPointTimeSeries], collectionJoinFromElementsByPath=[], impliedElements=[]} | \-[FROM_FRAGMENT] FromElement: 'TimeSeriesPeriod timeseries0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=t,role=null,tableName=TimeSeriesPeriod,tableAlias=timeseries0_,origin=null,columns={,className=com.junifer.thor.database.hibernate.objects.TimeSeriesPeriod}} | +-[FROM_FRAGMENT] ImpliedFromElement: 'MeterPointTimeSeries meterpoint1_' ImpliedFromElement{implied,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=MeterPointTimeSeries,tableAlias=meterpoint1_,origin=TimeSeriesPeriod timeseries0_,columns={timeseries0_.meterPointTimeSeriesFk ,className=com.junifer.thor.database.hibernate.objects.MeterPointTimeSeries}} | | \-[JOIN_FRAGMENT] FromElement: 'inner join MeterPoint meterpoint2_ on meterpoint1_.meterPointFk=meterpoint2_.id' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=MeterPoint,tableAlias=meterpoint2_,origin=MeterPointTimeSeries meterpoint1_,columns={meterpoint1_.meterPointFk ,className=com.junifer.thor.database.hibernate.objects.MeterPoint}} | \-[JOIN_FRAGMENT] FromElement: 'inner join TimeSeriesQuality timeseries3_ on timeseries0_.timeSeriesQualityFk=timeseries3_.id' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=null,tableName=TimeSeriesQuality,tableAlias=timeseries3_,origin=TimeSeriesPeriod timeseries0_,columns={timeseries0_.timeSeriesQualityFk ,className=com.junifer.thor.database.hibernate.objects.TimeSeriesQuality}} \-[WHERE] SqlNode: 'WHERE' \-[THETA_JOINS] SqlNode: '{theta joins}' \-[SQL_TOKEN] SqlFragment: 'timeseries0_.meterPointTimeSeriesFk=meterpoint1_.id' {noformat} The SQL generated is as follows {noformat} select meterpoint2_.id as id222_0_, timeseries3_.id as id438_1_, meterpoint2_.assetFk as assetFk222_0_, meterpoint2_.deleteFl as deleteFl222_0_, meterpoint2_.measurand as measurand222_0_, meterpoint2_.meterTypeFk as meterTyp7_222_0_, meterpoint2_.partitionId as partitio4_222_0_, meterpoint2_.versionNo as versionNo222_0_, timeseries3_.code as code438_1_, timeseries3_.deleteFl as deleteFl438_1_, timeseries3_.iconTblFk as iconTblFk438_1_, timeseries3_.internalKey as internal4_438_1_, timeseries3_.languageKey as language5_438_1_, timeseries3_.orderNo as orderNo438_1_, timeseries3_.partitionId as partitio7_438_1_, timeseries3_.versionNo as versionNo438_1_ from TimeSeriesPeriod timeseries0_, MeterPointTimeSeries meterpoint1_ inner join MeterPoint meterpoint2_ on meterpoint1_.meterPointFk=meterpoint2_.id inner join TimeSeriesQuality timeseries3_ on timeseries0_.timeSeriesQualityFk=timeseries3_.id where timeseries0_.meterPointTimeSeriesFk=meterpoint1_.id {noformat} And the error produced is {noformat} The multi-part identifier "timeseries0_.timeSeriesQualityFk" could not be bound. {noformat} The SQL above is wrong because the second inner join should be appearing after the first from statement rather than the second one. Looking at the AST fragment around the joins (some content removed for clarity {noformat} +-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=4, fromElements=4, fromElementByClassAlias=[t], fromElementByTableAlias=[meterpoint1_, meterpoint2_, timeseries3_, timeseries0_], fromElementsByPath=[t.timeSeriesQuality, t.meterPointTimeSeries.meterPoint, t.meterPointTimeSeries], collectionJoinFromElementsByPath=[], impliedElements=[]} | \-[FROM_FRAGMENT] FromElement: 'TimeSeriesPeriod timeseries0_' | +-[FROM_FRAGMENT] ImpliedFromElement: 'MeterPointTimeSeries meterpoint1_' | | \-[JOIN_FRAGMENT] FromElement: 'inner join MeterPoint meterpoint2_ on meterpoint1_.meterPointFk=meterpoint2_.id' | \-[JOIN_FRAGMENT] FromElement: 'inner join TimeSeriesQuality timeseries3_ on timeseries0_.timeSeriesQualityFk=timeseries3_.id' {noformat} Note the second join fragement appears last but is the child of the first from fragment. This looks to be correct. However when the SQL is rendered I'm guessing these nodes are rendered in their order as they appear here rather than rendering the second join fragment under the first from. The tree does look a bit odd as one from fragment is the child of the other but there are unrelated, I would expect both to be children of the main FromClause with one join fragment child each. Reversing the order of the select clause elements works correctly.