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

[Mike Q (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5860?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=39731#action_39731 ] 

Mike Q commented on HHH-5860:
-----------------------------

After some more investigation I believe I have narrowed the problem down to part of the sql-gen.g.

{noformat}
// The from-clause piece is all goofed up.  Currently, nodes of type FROM_FRAGMENT
// and JOIN_FRAGMENT can occur at any level in the FromClause sub-tree. We really
// should come back and clean this up at some point; which I think will require
// a post-HqlSqlWalker phase to "re-align" the FromElements in a more sensible
// manner.
from
	: #(f:FROM { out(" from "); }
		(fromTable)* )
	;

fromTable
	// Write the table node (from fragment) and all the join fragments associated with it.
	: #( a:FROM_FRAGMENT  { out(a); } (tableJoin [ a ])* { fromFragmentSeparator(a); } )
	| #( b:JOIN_FRAGMENT  { out(b); } (tableJoin [ b ])* { fromFragmentSeparator(b); } )
	;

tableJoin [ AST parent ]
	: #( c:JOIN_FRAGMENT { out(" "); out(c); } (tableJoin [ c ] )* )
	| #( d:FROM_FRAGMENT { nestedFromFragment(d,parent); } (tableJoin [ d ] )* )
	;
{noformat}

I'm not sure exactly which piece is wrong.  The comment at the top may be indicative tho as it tallys with my initial though that there was something slightly wrong with the organisation of the from/join nodes in the resulting HQL AST.

But anyways I've debugged the SqlGenerator for the failing example and found the the evaluation order of the nodes is

- From Element (TimeSeriesPeriod)
- Nested From Element (MeterPointTimeSeries)
- Join Element (MeterPointTimeSeries -> MeterPoint)
- Join Element (TimeSeriesPeriod -> TimeSeriesQuality)

when the order needs to be

- From Element (TimeSeriesPeriod)
- Join Element (TimeSeriesPeriod -> TimeSeriesQuality)
- Nested From Element (MeterPointTimeSeries)
- Join Element (MeterPointTimeSeries -> MeterPoint)

This is why swapping the select clause items round works because the AST comes out slightly differently and the SQL rendering is correct.


> 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