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

[Mike Q (JIRA)]

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.

-- 
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