[hibernate-issues] [Hibernate-JIRA] Created: (JPA-30) jpa
jpa
----
Key: JPA-30
URL: https://hibernate.onjira.com/browse/JPA-30
Project: Java Persistence API
Issue Type: Story
Affects Versions: 1.0.0
Reporter: xavier flamant
Hi,
I posted this issue on hibernate forum and had no satisfying answer. I post it on jira
because the hibernate log for the request doesn't seem to fit the original request. It
may be a bug
The complete query QUERY1 is
query = "SELECT a " +
"FROM eu.eclinica.cdisc.odm.ODMAnnotation a " +
"WHERE ([color=#FF8000](a.subjectData.clinicalData.study.oID = 1069 and a.seqNum =
1)[/color] or [color=#0040FF](a.studyEventData.subjectData.clinicalData.study.oID = 1069
and a.seqNum = 1)[/color])"
it gives me 0 result
The query QUERY2 with the first subQuery is
query = "SELECT a " +
"FROM eu.eclinica.cdisc.odm.ODMAnnotation a " +
"WHERE ([color=#FF8000](a.subjectData.clinicalData.study.oID = 1069 and a.seqNum =
1)[/color] "
it gives me 1 result Result1
The query QUERY3 with the second subQuery is
query = "SELECT a " +
"FROM eu.eclinica.cdisc.odm.ODMAnnotation a " +
"WHERE [color=#0040FF](a.studyEventData.subjectData.clinicalData.study.oID = 1069 and
a.seqNum = 1)[/color]"
it gives me 1 other result Result2
the problem is that the first query QUERY1 should give me 2 results (the result of QUERY2
PLUS the result of QUERY3) as there is a OR clause
I debuged the real request made on the database
[code]
2012-03-08 14:11:37 SQL [DEBUG] select odmannotat0_.oID as oID7_, odmannotat0_.ID as ID7_,
odmannotat0_.COMMENTID as COMMENTID7_, odmannotat0_.ANNOTATION_CREDT as ANNOTATION3_7_,
odmannotat0_.ANNOTATION_DUEDT as ANNOTATION4_7_, odmannotat0_.FORMDATAID as FORMDATAID7_,
odmannotat0_.ITEMDATAID as ITEMDATAID7_, odmannotat0_.ITEMGROUPDATAID as ITEMGRO10_7_,
odmannotat0_.SEQNUM as SEQNUM7_, odmannotat0_.EVENTDATAID as EVENTDA11_7_,
odmannotat0_.SUBJECTDATAID as SUBJECT12_7_, odmannotat0_.TRANSACTIONTYPE as TRANSACT6_7_
from Annotation odmannotat0_, SubjectData odmsubject1_, ClinicalData odmclinica2_,
StudyEventData odmstudyev3_, SubjectData odmsubject4_, ClinicalData odmclinica5_ where
odmannotat0_.SUBJECTDATAID=odmsubject1_.oID and
odmsubject1_.CLINICALDATAID=odmclinica2_.oID and odmannotat0_.EVENTDATAID=odmstudyev3_.oID
and odmstudyev3_.SUBJECTDATAID=odmsubject4_.oID and
odmsubject4_.CLINICALDATAID=odmclinica5_.oID and (odmclinica2_.STUDYID=? and
odmannotat0_.SEQNUM=1 or odmclinica5_.STUDYID=? and odmannotat0_.SEQNUM=1)
[/code]
To summarize, the request made on the database is the following (I use there my proper
coding)
query = "SELECT a " +
"FROM eu.eclinica.cdisc.odm.ODMAnnotation a " +
"WHERE [color=#FF8000]a.subjectData.clinicalData c1[/color] AND
[color=#0040FF]a.studyEventData.subjectData.clinicalData c2[/color] and (c1.study.oID =
1069 and a.seqNum = 1or c2.study.oID = 1069 and a.seqNum = 1)"
I really don't see why the first part is "a.subjectData.clinicalData c1 AND
a.studyEventData.subjectData.clinicalData c2"
as these two clause should be used with an "OR". Obviously, seeing the request
that is sent to the database it is normal that there is no Results
So what is wrong with the QUERY1 ?
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira