Unnecessary Table Join in Native Many-to-Many Query generated from JPQL
-----------------------------------------------------------------------
Key: OPENJPA-2041
URL: https://issues.apache.org/jira/browse/OPENJPA-2041
Project: OpenJPA
Issue Type: Bug
Components: query
Affects Versions: 2.1.1
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)
Reporter: bernard
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)
)
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
