[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2803) Subqueries.propertyIn creates wrong SQL for the where clause. Where clause is on ID and not on named property - resulting in wrong resultset.

[Philip Saville (JIRA)]

Subqueries.propertyIn creates wrong SQL for the where clause. Where clause is on ID and not on named property - resulting in wrong resultset.
---------------------------------------------------------------------------------------------------------------------------------------------

                 Key: HHH-2803
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2803
             Project: Hibernate3
          Issue Type: Bug
          Components: core
    Affects Versions: 3.2.4.sp1
         Environment: Hibernate 3 (Hibernate-Version: 3.2.4.sp1)
MySql 4,.1 MSSQL 2000
            Reporter: Philip Saville


class User {
...
private List<Role> roles;
...
@ManyToMany(...)
@JoinTable(...)
public List<Role> getRoles() {...{
}

DetachedCriteria subQuery = DetachedCriteria.forClass(Role.class);
subQuery.add(Restrictions.in("id", values)); // a list of know user roles to search on
// work around for bug:993
subQuery.setProjection(Projections.property("id"));

DetachedCriteria users = DetachedCriteria.forClass(User.class);
users.add(Subqueries.propertyIn("roles", subQuery));

This results in:
select this_.ID as ID17_1_, this_.LOCK_VERSION as LOCK2_17_1_, this_.GIVEN_NAME as GIVEN6_17_1_, this_.PASSWORD as PASSWORD17_1_, this_.SURNAME as SURNAME17_1_, this_.USER_NAME as USER12_17_1_, roles2_.USER_ID as USER1_3_, role3_.ID as ROLE2_3_, role3_.ID as ID18_0_, role3_.LOCK_VERSION as LOCK2_18_0_, role3_.DESCRIPTION as DESCRIPT3_18_0_, role3_.LEVEL as LEVEL18_0_, role3_.NAME as NAME18_0_ from T_USER this_ left outer join USER_ROLE roles2_ on this_.ID=roles2_.USER_ID left outer join ROLE role3_ on roles2_.ROLE_ID=role3_.ID where this_.ID = (select this0__.ID as y0_ from ROLE this0__ where this0__.ID in (?)) order by this_.USER_NAME asc)

Note - the where clause is on User.ID not, User.roles...


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