[hibernate-issues] [Hibernate-JIRA] Created: (HHH-5257) @JoinTable results in redundant join in the generated SQL

[Sharath Reddy (JIRA)]

@JoinTable results in redundant join in the generated SQL
---------------------------------------------------------

                 Key: HHH-5257
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5257
             Project: Hibernate Core
          Issue Type: Bug
          Components: core
    Affects Versions: 3.5.2
            Reporter: Sharath Reddy
            Priority: Minor
             Fix For: 3.6
         Attachments: testcase.zip

An Account can have several Clients:

@Entity
@Table(name = "CLIENT")
public class Client implements Serializable {
private String street;
private String code;
private String city;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;

@ManyToOne
@JoinTable(name = "CLIENT_ACCOUNT",
joinColumns = {@JoinColumn(name = "FK_CLIENT", referencedColumnName = "ID")},
inverseJoinColumns = {@JoinColumn(name = "FK_ACCOUNT", referencedColumnName = "ID")})
private Account account;
.....
}

@Entity
@Table(name = "ACCOUNT")
public class Account implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private String number;
private double balance;

@OneToMany (fetch = FetchType.EAGER, mappedBy="account")
private Set<Client> holders;
...
}


When we load the Account entity:
Account a1 = (Account) session.load(Account.class, accountId);

We see that there is a redundant join at the end - from the CLIENT table to the CLIENT_ACCOUNT table:

select
account0_.id as id2_1_,
account0_.balance as balance2_1_,
account0_.number as number2_1_,
holders1_.FK_ACCOUNT as FK1_2_3_,
client2_.id as FK2_3_,
client2_.id as id0_0_,
client2_.city as city0_0_,
client2_.code as code0_0_,
client2_.street as street0_0_,
client2_1_.FK_ACCOUNT as FK1_1_0_
from
ACCOUNT account0_
left outer join
CLIENT_ACCOUNT holders1_
on account0_.id=holders1_.FK_ACCOUNT
left outer join
CLIENT client2_
on holders1_.FK_CLIENT=client2_.id
left outer join
CLIENT_ACCOUNT client2_1_
on client2_.id=client2_1_.FK_CLIENT
where
account0_.id=?

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira