[hibernate-issues] [Hibernate-JIRA] Created: (HHH-5455) withClause dublicates in SQL during many-to-many association relation

[Alexey Zhukov (JIRA)]

withClause dublicates in SQL during many-to-many association relation
---------------------------------------------------------------------

                 Key: HHH-5455
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5455
             Project: Hibernate Core
          Issue Type: Bug
    Affects Versions: 3.6.0.Beta2, 3.5.0-Final
            Reporter: Alexey Zhukov


{code:title=Test1.java}
@Entity
@Table(name = "FA_TEST1")
public class Test1 {
    private Long id;

    @Column(name = "FA_TEST1_ID")
    @Id
    public Long getId() { return id;}

    public void setId(Long id) { this.id = id; }

    private Set < Test2 > test2s;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "FA_TEST1_TEST2",
            joinColumns = @JoinColumn(name = "TEST1_ID"),
            inverseJoinColumns = @JoinColumn(name = "TEST2_ID")
    )
    @ForeignKey(name = "FA_TEST1_ID_FK", inverseName = "FA_TEST2_ID_FK")
    public Set<Test2> getTest2s() { return test2s; }

    public void setTest2s(Set<Test2> test2s) { this.test2s = test2s; }
}
{code}

{code:title=Test2.java}
@Entity
@Table(name = "FA_TEST2")
public class Test2 {
    private Long id;

    @Column(name = "FA_TEST2_ID")
    @Id
    public Long getId() { return id; }

    public void setId(Long id) { this.id = id; }
}
{code}

Sample criteria java-code ...
{code:title=Sample criteria code}
Test1 test1 = (Test1) session.createCriteria(Test1.class).
        createAlias("test2s", "test2s",
                CriteriaSpecification.LEFT_JOIN,
                Restrictions.ge("id", 5L)).
        add(Restrictions.idEq(1L)).uniqueResult();
{code}

... produces following SQL-code:
{code:SQL}
select
        this_.FA_TEST1_ID as FA1_0_1_,
        test2s3_.TEST1_ID as TEST1_0_3_,
        test2s1_.FA_TEST2_ID as TEST2_3_,
        test2s1_.FA_TEST2_ID as FA1_1_0_ 
    from
        eisrsn2.FA_TEST1 this_ 
    left outer join
        eisrsn2.FA_TEST1_TEST2 test2s3_ 
            on this_.FA_TEST1_ID=test2s3_.TEST1_ID 
            and (
                test2s1_.FA_TEST2_ID>=? 
            ) 
    left outer join
        eisrsn2.FA_TEST2 test2s1_ 
            on test2s3_.TEST2_ID=test2s1_.FA_TEST2_ID 
            and (
                test2s1_.FA_TEST2_ID>=? 
            ) 
    where
        this_.FA_TEST1_ID = ?
{code}

Following part sould be mentioned once:
{code}
        and (
                test2s1_.FA_TEST2_ID>=? 
        )
{code}

[Similar issue|http://opensource.atlassian.com/projects/hibernate/browse/HHH-5264]

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