Community
Participate
Working Groups
Build Identifier: version: Eclipse Persistence Services - 2.0.0.v20091127-r5931 A rather simple JPQL query produces an unnecessary table join. The generated native SQL is: SELECT t1.ID , t1.NAME FROM PROJECT t0 , EMPLOYEE_PROJECT t2 , EMPLOYEE t1 WHERE ( (t0.ID = ?) AND ((t2.Employee_ID = t1.ID) AND (t0.ID = t2.projects_ID)) ) The generated SQL should be like the following: SELECT t1.ID , t1.NAME FROM # PROJECT t0 , EMPLOYEE_PROJECT t2 , EMPLOYEE t1 WHERE ( (t2.projects_ID = ?) AND ((t2.Employee_ID = t1.ID) ) Reproducible: Always Steps to Reproduce: Please refer to the attached testcase (NetBeans project).
Created attachment 157965 [details] NetBeans project in zip file
What does the actual SQL look like? I assume it includes a join between the project table and the employee_project table? Are you asking us to optimize that out?
Tom, Yes, I am suggesting to remove the Poject table. It appears to be quite common that EclipseLink unnecessarily joins a table only to use its primary key in other joins.
Changing to enhancement.
Fixing in trunk / 2.2 I asume the JPQL query was something like: "Select e from Employee e join e.projects p where p.id = :id" Please confirm?
Created attachment 177337 [details] patch to optimize out join
Fixed in EclipseLink 2.2
Hmm, this caused a test failure, and looking deeper into the issue, this is more difficult than I thought. Reverting fix, and reopenning bug. Issue is that the join to the join table is still required and must be normalized once, so querying on project.id and project.name would get very difficult to normalize.
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
