Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@openjpa.apache.org
Date: Tue, 4 Feb 2014 21:12:10 +0000 (UTC)
From: "ASF subversion and git services (JIRA)" <jira@apache.org>
To: dev@openjpa.apache.org
Message-ID: <JIRA.12665556.1377539789989.36651.1391548330430@arcas>
In-Reply-To: <JIRA.12665556.1377539789989@arcas>
References: <JIRA.12665556.1377539789989@arcas>
Subject: [jira] [Commented] (OPENJPA-2423) Isolation level is not working
 properly on DB2 for JPQL queries with nested sub-queries.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/OPENJPA-2423?page=3Dcom.atlassi=
an.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D13=
891208#comment-13891208 ]=20

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

Commit 1564495 from [~fyrewyld] in branch 'openjpa/branches/2.3.x'
[ https://svn.apache.org/r1564495 ]

OPENJPA-2423: Added null-check to DB2Dictionary update

> Isolation level is not working properly on DB2 for JPQL queries with nest=
ed 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 =E2=80=9CWITH UR=E2=80=9D suffix t=
o both nested sub-query as well as outer main query. As per SQL syntax, it =
should be adding =E2=80=9CWITH UR=E2=80=9D only at outer main query level.=
=20
> =20
> -- Query Hints used:
> query.setHint("openjpa.FetchPlan.Isolation", "read-uncommitted");
> query.setHint("openjpa.FetchPlan.ReadLockMode", "READ");
> =20
> -- JPQL Query:
> SELECT m FROM AnEntity m WHERE m.id.memberIdTd =3D :memberIdTd AND m.id.e=
ntryTimestamp IN
>       (SELECT max(b.id.entryTimestamp) FROM AnEntity b WHERE b.id.memberI=
dTd =3D :memberIdTd)
> =20
> -- Generated Query:
> SELECT t0.ENTRY_TIMESTAMP, t0.MEMBER_ID_TD, t0.CREATED_BY, t0.DEPT_CD,  t=
0.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 =3D ? AND t0.ENTRY_TIMESTAMP IN
>    ( SELECT MAX(t1.ENTRY_TIMESTAMP) FROM DBA.ANENTITY t1
>       WHERE ( t1.MEMBER_ID_TD =3D ?
>             ) FOR READ ONLY WITH UR
>    )
> ) optimize for 1 row FOR READ ONLY WITH UR
> =20
> I'm attaching a patch, named subqueryURclause.patch.txt, created by Pinak=
i Poddar.
> Thanks,
> Heath Thomann


--
This message was sent by Atlassian JIRA
(v6.1.5#6160)