[OPENJPA-2041] Unnecessary Table Join in Native Many-to-Many Query generated from JPQL

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.1.1
• Component/s: query
• 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)
• Assignee: Unassigned
• Reporter: bernard
• Created: 19/Aug/11 09:36
• Updated: 19/Aug/11 13:55

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)
)