[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1400) Using formula-based property causes invalid SQL code for children "subselect" query
Golubev Dmitriy (JIRA)
noreply at atlassian.com
Mon Aug 15 06:16:03 EDT 2011
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1400?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=43233#comment-43233 ]
Golubev Dmitriy commented on HHH-1400:
--------------------------------------
the problem is not fixed (3.6.6)
i have several entities with hierarchy
1. Project
2. Measure
3. Activity
where i used formula on time status field via attribute @Formula.
formula is complex sql, which contains "case when" with logical expressions and "exist select from".
when measure object is loaded by usual detachedcriteria activities are loaded by collection initialization
in SubselectFetch where condition is formed by subtring using " from ". but in sql i have several "select from ". and sql is trimmed with error conditions.
so initial sql which is processed in SubselectFetch constructor (unnecessary fields are skipped)
"select (
case
when
(
this_.mctmea_status in ( 'IN_CREATION' ) and
this_.mctmea_plannedstartdate is not null and
trunc(this_.mctmea_plannedstartdate) < trunc(sysdate)
)
then 'ATTENTION'
when
(
this_.mctmea_status in ('IN_CREATION', 'STARTED', 'IN_PROGRESS') and
this_.mctmea_plannedenddate is not null and
trunc(this_.mctmea_plannedenddate) < trunc(sysdate)
)
then 'ATTENTION'
when
(
exists
(
select mcta.id from mct_activity mcta
where
(
mcta.mctact_status = 'IN_CREATION' and
mcta.mctact_planstartdate is not null and
trunc(mcta.mctact_planstartdate) < trunc(sysdate)
)
and mcta.FKMCTACT_MEA_ID = this_.id
)
)
then 'ATTENTION'
when
(
exists
(
select mcta.id from mct_activity mcta
where
(
mcta.mctact_status in ('IN_CREATION', 'STARTED', 'IN_PROGRESS') and
mcta.mctact_planenddate is not null and
trunc(mcta.mctact_planenddate) < trunc(sysdate)
)
and mcta.FKMCTACT_MEA_ID = this_.id
)
)
then 'ATTENTION'
when
(
this_.mctmea_status in ( 'IN_CREATION' ) and
this_.mctmea_plannedstartdate is not null and
trunc(this_.mctmea_plannedstartdate)=trunc(sysdate)
)
then 'WARNING'
when
(
this_.mctmea_status in ('STARTED', 'IN_PROGRESS') and
this_.mctmea_plannedenddate is not null and
trunc(this_.mctmea_plannedenddate) = trunc(sysdate)
)
then 'WARNING'
when
(
exists
(
select mcta.id from mct_activity mcta
where
(
mcta.mctact_status = 'IN_CREATION' and
mcta.mctact_planstartdate is not null and
trunc(mcta.mctact_planstartdate)=trunc(sysdate)
)
and mcta.FKMCTACT_MEA_ID = this_.id
)
)
then 'WARNING'
when
(
exists
(
select mcta.id from mct_activity mcta
where
(
mcta.mctact_status in ('STARTED', 'IN_PROGRESS') and
mcta.mctact_planenddate is not null and
trunc(mcta.mctact_planenddate)=trunc(sysdate)
)
and mcta.FKMCTACT_MEA_ID = this_.id
)
)
then 'WARNING'
else 'OK'
end
) as formula8_7_ from DGVSRM.MCT_MEASURE this_ left outer join DGVSRM.MCT_MFOCUS mctmainfoc2_ on this_.FKMCTMEA_MFOCUS_ID=mctmainfoc2_.ID left outer join DGVSRM.UIMLString uimultilan3_ on mctmainfoc2_.MCTMF_MLNAME=uimultilan3_.ID left outer join DGVSRM.MCT_MATLEVEL mctmaturit4_ on this_.FKMCTMEA_MATLEVEL_ID=mctmaturit4_.ID left outer join DGVSRM.UIMLString uimultilan5_ on mctmaturit4_.MCTML_MLDESCRIPTION=uimultilan5_.ID left outer join DGVSRM.UIMLString uimultilan6_ on mctmaturit4_.MCTML_MLNAME=uimultilan6_.ID inner join DGVSRM.MCT_TYPE mcttype7_ on this_.FKMCTMEA_TYPE_ID=mcttype7_.ID left outer join DGVSRM.NOTIFICATION_BUNDLE notificati8_ on mcttype7_.FKMCTTYP_NBUNDLE_ID=notificati8_.ID where this_.FKMCTMEA_PROJ_ID=? and this_.deleteFlag=?"
result queryString is
"
from mct_activity mcta
where
(
mcta.mctact_status = 'IN_CREATION' and
mcta.mctact_planstartdate is not null and
trunc(mcta.mctact_planstartdate) < trunc(sysdate)
)
and mcta.FKMCTACT_MEA_ID = this_.id
)
)
then 'ATTENTION'
when
(
exists
(
select mcta.id from mct_activity mcta
where
(
mcta.mctact_status in ('IN_CREATION', 'STARTED', 'IN_PROGRESS') and
mcta.mctact_planenddate is not null and
trunc(mcta.mctact_planenddate) < trunc(sysdate)
)
and mcta.FKMCTACT_MEA_ID = this_.id
)
)
then 'ATTENTION'
when
(
this_.mctmea_status in ( 'IN_CREATION' ) and
this_.mctmea_plannedstartdate is not null and
trunc(this_.mctmea_plannedstartdate)=trunc(sysdate)
)
then 'WARNING'
when
(
this_.mctmea_status in ('STARTED', 'IN_PROGRESS') and
this_.mctmea_plannedenddate is not null and
trunc(this_.mctmea_plannedenddate) = trunc(sysdate)
)
then 'WARNING'
when
(
exists
(
select mcta.id from mct_activity mcta
where
(
mcta.mctact_status = 'IN_CREATION' and
mcta.mctact_planstartdate is not null and
trunc(mcta.mctact_planstartdate)=trunc(sysdate)
)
and mcta.FKMCTACT_MEA_ID = this_.id
)
)
then 'WARNING'
when
(
exists
(
select mcta.id from mct_activity mcta
where
(
mcta.mctact_status in ('STARTED', 'IN_PROGRESS') and
mcta.mctact_planenddate is not null and
trunc(mcta.mctact_planenddate)=trunc(sysdate)
)
and mcta.FKMCTACT_MEA_ID = this_.id
)
)
then 'WARNING'
else 'OK'
end
) as formula8_7_ from DGVSRM.MCT_MEASURE this_ left outer join DGVSRM.MCT_MFOCUS mctmainfoc2_ on this_.FKMCTMEA_MFOCUS_ID=mctmainfoc2_.ID left outer join DGVSRM.UIMLString uimultilan3_ on mctmainfoc2_.MCTMF_MLNAME=uimultilan3_.ID left outer join DGVSRM.MCT_MATLEVEL mctmaturit4_ on this_.FKMCTMEA_MATLEVEL_ID=mctmaturit4_.ID left outer join DGVSRM.UIMLString uimultilan5_ on mctmaturit4_.MCTML_MLDESCRIPTION=uimultilan5_.ID left outer join DGVSRM.UIMLString uimultilan6_ on mctmaturit4_.MCTML_MLNAME=uimultilan6_.ID inner join DGVSRM.MCT_TYPE mcttype7_ on this_.FKMCTMEA_TYPE_ID=mcttype7_.ID left outer join DGVSRM.NOTIFICATION_BUNDLE notificati8_ on mcttype7_.FKMCTTYP_NBUNDLE_ID=notificati8_.ID where this_.FKMCTMEA_PROJ_ID=? and this_.deleteFlag=?"
please provide the solution
> Using formula-based property causes invalid SQL code for children "subselect" query
> -----------------------------------------------------------------------------------
>
> Key: HHH-1400
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1400
> Project: Hibernate Core
> Issue Type: Bug
> Components: core
> Affects Versions: 3.1 rc3
> Environment: Hibernate 3.0.5, Hibernate 3.1rc3
> Oracle 8i
> Reporter: Chris Rogers
> Attachments: SubselectFetch.java, SubselectFormulaBug.zip, subselectformula.zip
>
>
> I have simple one-to-many relationship, mapped as set:
> <class name="Parent">
> <set name="children" lazy="false" fetch="subselect">
> <key column="PARENT_OID"/>
> <one-to-many class="Child"/>
> </set>
> </class>
> this works fine, constructing subselect SQL which looks like (e.g. for HQL query "from Parent"):
> select <child fields> from <child table> where child.PARENT_OID in (select this_.OID from PARENT this_)
> (simplified)
> However, when adding a formula-based property into Parent:
> <property name="myFormulaField" formula="(complex_select )"/>
> Now SQL becomes:
> select <child fields> from <child table> where PARENT_OID in (complex_select) as formula0_1_, <some parent fields> from PARENT this_)
> This SQL fails because of incorrect grammar (it also seems that backet is missing).
> This is something weird, because subselect fetching only needs Parent's identity column, not any other properties. And I don't think it should be affected by Parent's formula-based properties.
> I can provide more details if necessary, I stripped out all extra mapping/SQL stuff because it seems to be irrelevant here.
> This bug appeared in 3.0.5 later I've downloaded 3.1rc3 and it also fails.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list