We're updating the issue view to help you get more done. 

jpa

Description

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 ?

Environment

None

Status

Assignee

Unassigned

Reporter

xavier flamant

Labels

None

Worked in

None

Feedback Requested

None

Feedback Requested By

None

backPortable

None

Community Help Wanted

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

backportReEvaluate

None

Affects versions

1.0.0

Priority

Major