You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "ASF subversion and git services (JIRA)" <ji...@apache.org> on 2014/02/04 21:34:16 UTC
[jira] [Commented] (OPENJPA-2423) Isolation level is not working
properly on DB2 for JPQL queries with nested sub-queries.
[ https://issues.apache.org/jira/browse/OPENJPA-2423?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13891168#comment-13891168 ]
ASF subversion and git services commented on OPENJPA-2423:
----------------------------------------------------------
Commit 1564471 from [~fyrewyld] in branch 'openjpa/branches/2.2.x'
[ https://svn.apache.org/r1564471 ]
OPENJPA-2423: Added null-check to DB2Dictionary update
> 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.1.1, 2.2.2, 2.2.1.1, 2.3.0
> Reporter: Heath Thomann
> Assignee: Heath Thomann
> Priority: Critical
> Fix For: 2.1.2, 2.2.1.1, 2.2.3, 2.3.0
>
> Attachments: subqueryURclause.patch.txt
>
>
> 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 was sent by Atlassian JIRA
(v6.1.5#6160)