[Hibernate-JIRA] Commented: (HHH-1400) Using formula-based property causes invalid SQL code for children "subselect" query
5:16 a.m.
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-1400?page=c...
]
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
4879
days inactive
4879
days old
hibernate-issues@lists.jboss.org
0 comments
1 participants
participants (1)
-
Golubev Dmitriy (JIRA)