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

JPQL interpret wrongly for inner join table (without mapped relation)

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.1.0
    • Fix Version/s: 2.1.2, 2.2.1.1, 2.2.3, 2.4.1
    • Component/s: sql
    • Labels:
      None
    • Environment:
      WebSphere v8.0

      Description

      I found the following issue after a few months of production of my client.

      I write a java batch job to process some account data for my client.

      And I found out that the JPQL interpret wrong randomly and it is not always having the same result for the same of data and code.

      JPQL statement:
      SELECT B
      FROM EntityA_PO A, EntityB_PO B
      WHERE A.deletingDateTime IS NULL and A.statusCode= :app and A.someId in (:ids)
      and B.deletingDateTime IS NULL and B.statusCode= :app2 and A.headerId = B.headerId
      ORDER BY B.someId

      translated to unexpected SQL:

      SELECT t1.HEADER_ID, t1.SOME_ID
      FROM EntityB t0 JOIN EntityB t1 ON (1 = 1)
      WHERE (t0.DELETING_DATE_TIME IS NULL AND t0.STATUS_CODE = ? AND t0.SOME_ID IN
      AND t1.DELETING_DATE_TIME IS NULL AND t1.STATUS_CODE = ? AND t1.HEADER_ID = t0.HEADER_ID)
      ORDER BY t1.SOME_ID ASC

      the correct translated SQL:

      SELECT t1.HEADER_ID, t1.SOME_ID
      FROM EntityA t0 JOIN EntityB t1 ON (1 = 1)
      WHERE (t0.DELETING_DATE_TIME IS NULL AND t0.STATUS_CODE = ? AND t0.SOME_ID IN
      AND t1.DELETING_DATE_TIME IS NULL AND t1.STATUS_CODE = ? AND t1.HEADER_ID = t0.HEADER_ID)
      ORDER BY t1.SOME_ID ASC

      Detail:
      The two EntityA_PO and EntityB_PO do not have any mapped relation. They are joined by a column called headerId in where clause only.

      The problem and correct generated native SQL is different by one is to=Entity A , and other is to=Entity B.

      I don't know why the wrong one joining Entity B itself without any Entity A involvement. This situation seems to be existed randomly. I can't catch the pattern of it.

      If there is any fix or workaround for this case, please let me know. Thanks

        Attachments

        1. schema_and_testing_sql.txt
          7 kB
          Heath Thomann
        2. OPENJPA-2539-2.1.x.test
          16 kB
          Heath Thomann
        3. OPENJPA-2539-2.1.x_v2.test
          17 kB
          Heath Thomann
        4. OPENJPA-2539-2.1.x_v3.test
          17 kB
          Heath Thomann

          Activity

            People

            • Assignee:
              jpaheath Heath Thomann
              Reporter:
              zechs Benson So
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: