[hibernate-issues] [Hibernate-JIRA] Created: (JPA-30) jpa

[xavier flamant (JIRA)]

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