[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Incorrect SQL created by DynamicQL when using left-joins

[cniermei]

I am getting an SQLException from the SQL generated by a DynamicQL statement. I did not got this error until I added left-joins to my jbosscmp-jdbc.xml. I will try to explain as best I can the circumstances which have led to this error.

I am using Jboss 4.0.3SP1 connecting to a PostgreSQL 8.0.4 server. 

I have a DynamicQL query, which can look something like this:
DYNAMIC-QL: SELECT DISTINCT Object (r) 
  | FROM     Recording r 
  | WHERE    1=1  
  | AND      r.phoneCall.dateOfCall >= ?1  
  | AND      r.phoneCall.dateOfCall <= ?2  
  | AND      r.phoneCall.startTime >= ?3  
  | AND      r.phoneCall.callCentre.key = ?4  
  | ORDER BY r.phoneCall.key, r.timestampSequence
It can take over 5 minutes to get the results and populate classes with the values from the recording table and all of its CMR fields, so I'm trying to use read-ahead on-find and left-joins to see if that speeds it up. The read ahead is:
                <read-ahead>
  |                     <strategy>on-find</strategy>
  |                     <page-size>4</page-size>
  |                     <eager-load-group>*</eager-load-group>
  |                     <left-join cmr-field="question" eager-load-group="*"/>
  |                     <left-join cmr-field="phoneCall" eager-load-group="*">
  |                         <left-join cmr-field="interviewerEmp" eager-load-group="*">
  |                             <left-join cmr-field="dept" eager-load-group="*">
  |                                 <left-join cmr-field="parentDept" eager-load-group="*"/>
  |                             </left-join>
  |                             <left-join cmr-field="title" eager-load-group="*"/>
  |                         </left-join>
  |                         <left-join cmr-field="versionStatus" eager-load-group="*"/>
  |                         <left-join cmr-field="version" eager-load-group="tss">
  |                             <left-join cmr-field="study" eager-load-group="*"/>
  |                         </left-join>
  |                         <left-join cmr-field="dashCase" eager-load-group="*">
  |                             <left-join cmr-field="form" eager-load-group="*">
  |                                 <left-join cmr-field="studyDetail" eager-load-group="*">
  |                                     <left-join cmr-field="study" eager-load-group="*">
  |                                     </left-join>
  |                                 </left-join>
  |                             </left-join>
  |                         </left-join>
  |                         <left-join cmr-field="callStatus" eager-load-group="*"/>
  |                     </left-join>
  |                     <left-join cmr-field="monitoringEmp" eager-load-group="*">
  |                         <left-join cmr-field="dept" eager-load-group="*">
  |                             <left-join cmr-field="parentDept" eager-load-group="*"/>
  |                         </left-join>
  |                         <left-join cmr-field="title" eager-load-group="*"/>
  |                     </left-join>
  |                 </read-ahead>
  | 
The SQL generated by this query fails, appearantly because of the strange way it combines left outer joins and WHERE clauses to join the classes together.
In the FROM clause:
FROM dash.recording t0_r, 
  | dash.phone_call t2_r_phoneCall, 
  | call_centre_view t19_r_phoneCall_callCentre LEFT OUTER JOIN dash.question t1_r_question ON t0_r.question_id=t1_r_question.id 
  | LEFT OUTER JOIN...
In there WHERE clause:
...AND t0_r.phone_call_id=t2_r_phoneCall.id 
  | AND t2_r_phoneCall.call_centre_id=t19_r_phoneCall_callCentre.id))
  | AND t0_r.phone_call_id=t2_r_phoneCall.id
The resulting error is:
anonymous wrote : javax.ejb.FinderException: Find failed: java.sql.SQLException: ERROR: relation "t0_r" does not exist

Is there something wrong with the way I'm setting up the left-joins, or might this be a bug in the sql generation code?

Thanks in advance for any help,
Chris Niermeier

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3964660#3964660

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3964660