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

Unnecessary Table Join in Native Many-to-Many Query generated from JPQL

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 2.1.1
    • Fix Version/s: None
    • Component/s: query
    • Labels:
      None
    • Environment:
      java version "1.6.0_25"
      Java(TM) SE Runtime Environment (build 1.6.0_25-b06)
      Java HotSpot(TM) Client VM (build 20.0-b11, mixed mode, sharing)

      Description

      A rather simple JPQL query produces an unnecessary table join.
      Please refer to the attached testcase (NetBeans project).

      JPQL:

      select
      object(emp)
      from
      Employee emp
      , in(emp.projects) proj
      where
      proj.id = :projectId

      The generated native SQL is:

      SELECT
      t0.id
      , t0.name
      FROM
      Employee t0
      INNER JOIN Employee_Project t1 ON t0.id = t1.EMPLOYEE_ID
      INNER JOIN Project t2 ON t1.PROJECTS_ID = t2.id
      WHERE (
      t1.PROJECTS_ID = ?)

      The generated SQL should be like the following, in other words the project table should not be included.

      SELECT
      t1.ID
      , t1.NAME
      FROM
      , EMPLOYEE_PROJECT t2
      , EMPLOYEE t1
      WHERE (
      (t2.projects_ID = ?)
      AND ((t2.Employee_ID = t1.ID)
      )

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              bht@actrix.gen.nz bernard
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: