6:06 a.m.
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira