openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Heath Thomann (JIRA)" <j...@apache.org>
Subject [jira] [Created] (OPENJPA-2423) Isolation level is not working properly on DB2 for JPQL queries with nested sub-queries.
Date Mon, 26 Aug 2013 17:57:51 GMT
Heath Thomann created OPENJPA-2423:
--------------------------------------

             Summary: Isolation level is not working properly on DB2 for JPQL queries with
nested sub-queries.
                 Key: OPENJPA-2423
                 URL: https://issues.apache.org/jira/browse/OPENJPA-2423
             Project: OpenJPA
          Issue Type: Bug
          Components: query
    Affects Versions: 2.2.2, 2.1.1, 2.3.0, 2.2.1.1
            Reporter: Heath Thomann
            Assignee: Heath Thomann
            Priority: Critical


Isolation level hint is not properly working on DB2 for JPQL queries with nested sub-queries.
It is generating incorrect query.  Please see example below. You will notice that it is adding
“WITH UR” suffix to both nested sub-query as well as outer main query. As per SQL syntax,
it should be adding “WITH UR” only at outer main query level. 
 
-- Query Hints used:
query.setHint("openjpa.FetchPlan.Isolation", "read-uncommitted");
query.setHint("openjpa.FetchPlan.ReadLockMode", "READ");
 

-- JPQL Query:
SELECT m FROM AnEntity m WHERE m.id.memberIdTd = :memberIdTd AND m.id.entryTimestamp IN
      (SELECT max(b.id.entryTimestamp) FROM AnEntity b WHERE b.id.memberIdTd = :memberIdTd)
 

-- Generated Query:
SELECT t0.ENTRY_TIMESTAMP, t0.MEMBER_ID_TD, t0.CREATED_BY, t0.DEPT_CD,  t0.EVENT_CLASS,t0.EVENT_DT,t0.EVENT_PRIORITY_IND,t0.EVENT_REMARKS,
t0.EVENT_STATUS,t0.EVENT_TYPE,t0.LAST_MOD_DATE,t0.LAST_MOD_USER,t0.LEGAL_COUNSEL_IND,t0.SYSTEM_CD
FROM DBA.ANENTITY t0
WHERE
( t0.MEMBER_ID_TD = ? AND t0.ENTRY_TIMESTAMP IN
   ( SELECT MAX(t1.ENTRY_TIMESTAMP) FROM DBA.ANENTITY t1
      WHERE ( t1.MEMBER_ID_TD = ?
            ) FOR READ ONLY WITH UR
   )
) optimize for 1 row FOR READ ONLY WITH UR
 

I'm attaching a patch, named subqueryURclause.patch.txt, created by Pinaki Poddar.

Thanks,

Heath Thomann

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message