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

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

Attach filesAttach ScreenshotAdd voteVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 2.1.1
    • None
    • query
    • None
    • 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

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            Unassigned Unassigned
            bht@actrix.gen.nz bernard

            Dates

              Created:
              Updated:

              Slack

                Issue deployment