[hibernate-issues] [Hibernate-JIRA] Created: (JPA-19) Many-to-Many: Unnecessary Table Join in generated SQL

[Bernard (JIRA)]

Many-to-Many: Unnecessary Table Join in generated SQL
-----------------------------------------------------

                 Key: JPA-19
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/JPA-19
             Project: Java Persistence API
          Issue Type: Bug
         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
         Attachments: testcase.zip

A 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 = :project

The generated native SQL is:

    select
        employee0_.id as id2_,
        employee0_.name as name2_ 
    from
        Employee employee0_ 
    inner join
        Employee_Project projects1_ 
            on employee0_.id=projects1_.Employee_id 
    inner join
        Project project2_ 
            on projects1_.projects_id=project2_.id 
    where
        project2_.id=?


The generated SQL should have only one join, in other words the project table should not be included.

Please refer to the attached testcase if this is useful - you need to set up the required libraries.


--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira