Uploaded image for project: 'OpenJPA'
  1. OpenJPA
  2. OPENJPA-2423

Isolation level is not working properly on DB2 for JPQL queries with nested sub-queries.

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.1.1, 2.2.2, 2.2.1.1, 2.3.0
    • Fix Version/s: 2.1.2, 2.2.1.1, 2.2.3, 2.3.0
    • Component/s: query
    • Labels:
      None
    • Patch Info:
      Patch Available

      Description

      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

        Activity

        Hide
        jira-bot ASF subversion and git services added a comment -

        Commit 1564537 from Jody Grassel in branch 'openjpa/trunk'
        [ https://svn.apache.org/r1564537 ]

        OPENJPA-2423: Added null-check to DB2Dictionary update

        Show
        jira-bot ASF subversion and git services added a comment - Commit 1564537 from Jody Grassel in branch 'openjpa/trunk' [ https://svn.apache.org/r1564537 ] OPENJPA-2423 : Added null-check to DB2Dictionary update
        Hide
        jira-bot ASF subversion and git services added a comment -

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

        OPENJPA-2423: Added null-check to DB2Dictionary update

        Show
        jira-bot ASF subversion and git services added a comment - Commit 1564495 from Jody Grassel in branch 'openjpa/branches/2.3.x' [ https://svn.apache.org/r1564495 ] OPENJPA-2423 : Added null-check to DB2Dictionary update
        Hide
        jira-bot ASF subversion and git services added a comment -

        Commit 1564471 from Jody Grassel in branch 'openjpa/branches/2.2.x'
        [ https://svn.apache.org/r1564471 ]

        OPENJPA-2423: Added null-check to DB2Dictionary update

        Show
        jira-bot ASF subversion and git services added a comment - Commit 1564471 from Jody Grassel in branch 'openjpa/branches/2.2.x' [ https://svn.apache.org/r1564471 ] OPENJPA-2423 : Added null-check to DB2Dictionary update
        Hide
        jira-bot ASF subversion and git services added a comment -

        Commit 1517843 from Heath Thomann 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.

        Show
        jira-bot ASF subversion and git services added a comment - Commit 1517843 from Heath Thomann 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.
        Hide
        jira-bot ASF subversion and git services added a comment -

        Commit 1517841 from Heath Thomann in branch 'openjpa/branches/2.2.x'
        [ https://svn.apache.org/r1517841 ]

        OPENJPA-2423: Isolation level is not working properly on DB2 for JPQL queries with nested sub-queries - committed to 2.2.x the patch/fix provided by Pinaki Poddar.

        Show
        jira-bot ASF subversion and git services added a comment - Commit 1517841 from Heath Thomann in branch 'openjpa/branches/2.2.x' [ https://svn.apache.org/r1517841 ] OPENJPA-2423 : Isolation level is not working properly on DB2 for JPQL queries with nested sub-queries - committed to 2.2.x the patch/fix provided by Pinaki Poddar.
        Hide
        jira-bot ASF subversion and git services added a comment -

        Commit 1517839 from Heath Thomann in branch 'openjpa/branches/2.2.1.x'
        [ https://svn.apache.org/r1517839 ]

        OPENJPA-2423: Isolation level is not working properly on DB2 for JPQL queries with nested sub-queries - committed to 2.2.1.x the patch/fix provided by Pinaki Poddar.

        Show
        jira-bot ASF subversion and git services added a comment - Commit 1517839 from Heath Thomann in branch 'openjpa/branches/2.2.1.x' [ https://svn.apache.org/r1517839 ] OPENJPA-2423 : Isolation level is not working properly on DB2 for JPQL queries with nested sub-queries - committed to 2.2.1.x the patch/fix provided by Pinaki Poddar.
        Hide
        jira-bot ASF subversion and git services added a comment -

        Commit 1517838 from Heath Thomann in branch 'openjpa/branches/2.1.x'
        [ https://svn.apache.org/r1517838 ]

        OPENJPA-2423: Isolation level is not working properly on DB2 for JPQL queries with nested sub-queries - committed to 2.1.x the patch/fix provided by Pinaki Poddar.

        Show
        jira-bot ASF subversion and git services added a comment - Commit 1517838 from Heath Thomann in branch 'openjpa/branches/2.1.x' [ https://svn.apache.org/r1517838 ] OPENJPA-2423 : Isolation level is not working properly on DB2 for JPQL queries with nested sub-queries - committed to 2.1.x the patch/fix provided by Pinaki Poddar.
        Hide
        jpaheath Heath Thomann added a comment -

        Attaching a patch created by Pinaki Poddar.

        Thanks,

        Heath Thomann

        Show
        jpaheath Heath Thomann added a comment - Attaching a patch created by Pinaki Poddar. Thanks, Heath Thomann

          People

          • Assignee:
            jpaheath Heath Thomann
            Reporter:
            jpaheath Heath Thomann
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development