openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF subversion and git services (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (OPENJPA-2423) Isolation level is not working properly on DB2 for JPQL queries with nested sub-queries.
Date Tue, 27 Aug 2013 15:06:54 GMT

    [ https://issues.apache.org/jira/browse/OPENJPA-2423?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13751340#comment-13751340
] 

ASF subversion and git services commented on OPENJPA-2423:
----------------------------------------------------------

Commit 1517843 from [~jpaheath] in branch 'openjpa/trunk'
[ https://svn.apache.org/r1517843 ]

OPENJPA-2423: Isolation level is not working properly on DB2 for JPQL queries with nested
sub-queries - committed to trunk the patch/fix provided by Pinaki Poddar.
                
> 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.3.0, 2.2.2, 2.2.1.1
>            Reporter: Heath Thomann
>            Assignee: Heath Thomann
>            Priority: Critical
>         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 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