[Hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1781) Criteria query returns duplicate results when root class contains eagerly fetched many-to-many collection

[Emmanuel B. (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1781?page=comments#action_23164 ] 

Emmanuel Bernard commented on HHH-1781:
---------------------------------------

Criteria c = s.createCriteria(Reservation.class)
         .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) 
...

> Criteria query returns duplicate results when root class contains eagerly fetched many-to-many collection
> ---------------------------------------------------------------------------------------------------------
>
>          Key: HHH-1781
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1781
>      Project: Hibernate3
>         Type: Bug

>   Components: query-criteria
>     Versions: 3.2.0.cr2
>  Environment: Hibernate 3.2 post CR2, Oracle 10g
>     Reporter: Adrian Sampaleanu

>
>
> The following query returns multiple UserImpl instances when the "ids" collection holds just one identifier:
> return session.createCriteria(UserImpl.class)
>                     .add(Restrictions.in("id", ids))
>                     .list();
> Sniffing the generated SQL I get:
> SELECT 
>     this_.ID                            as ID46_3_, 
>     this_.version                       as version46_3_, 
>     this_.locale                        as locale46_3_, 
>     this_.username                      as username46_3_, 
>     this_.ENABLED                       as ENABLED46_3_, 
>     this_.ORGANIZATION_ID               as ORGANIZ12_46_3_, 
>     this_.firstname                     as firstname46_3_, 
>     this_.lastname                      as lastname46_3_, 
>     this_.EMAIL                         as EMAIL46_3_, 
>     this_.OTA_LOGIN                     as OTA9_46_3_, 
>     this_.OTA_PASSWORD                  as OTA10_46_3_, 
>     this_.PASSWORD                      as PASSWORD46_3_, 
>     groups2_.USER_ID                    as USER1_5_, 
>     groupimpl3_.id                      as GROUP2_5_, 
>     groupimpl3_.id                      as id43_0_, 
>     groupimpl3_.name                    as name43_0_, 
>     groupimpl3_.version                 as version43_0_, 
>     organizati4_.id                     as id37_1_, 
>     organizati4_.name                   as name37_1_, 
>     organizati4_.version                as version37_1_, 
>     organizati4_.CURRENCY               as CURRENCY37_1_, 
>     organizati4_.enabled                as enabled37_1_, 
>     organizati4_.CODE                   as CODE37_1_, 
>     organizati4_.PARENT_ORGANIZATION_ID as PARENT14_37_1_, 
>     organizati4_.PKG_FILENAME_PATTERN   as PKG8_37_1_, 
>     organizati4_.SHORT_CODE             as SHORT9_37_1_, 
>     organizati4_.WEBHOST_NAME_PREFIX    as WEBHOST10_37_1_, 
>     organizati4_.WEBHOST_NAME           as WEBHOST11_37_1_, 
>     organizati4_.PRODUCT_FEATURES       as PRODUCT12_37_1_, 
>     organizati4_.ORGANIZATION_TYPE      as ORGANIZ13_37_1_, 
>     organizati4_.TYPE                   as TYPE37_1_, 
>     organizati5_.id                     as id37_2_, 
>     organizati5_.name                   as name37_2_, 
>     organizati5_.version                as version37_2_, 
>     organizati5_.CURRENCY               as CURRENCY37_2_, 
>     organizati5_.enabled                as enabled37_2_, 
>     organizati5_.CODE                   as CODE37_2_, 
>     organizati5_.PARENT_ORGANIZATION_ID as PARENT14_37_2_, 
>     organizati5_.PKG_FILENAME_PATTERN   as PKG8_37_2_, 
>     organizati5_.SHORT_CODE             as SHORT9_37_2_, 
>     organizati5_.WEBHOST_NAME_PREFIX    as WEBHOST10_37_2_, 
>     organizati5_.WEBHOST_NAME           as WEBHOST11_37_2_, 
>     organizati5_.PRODUCT_FEATURES       as PRODUCT12_37_2_, 
>     organizati5_.ORGANIZATION_TYPE      as ORGANIZ13_37_2_, 
>     organizati5_.TYPE                   as TYPE37_2_ 
> FROM 
>     rnadarajah.USERS this_ 
> LEFT OUTER JOIN 
>     rnadarajah.USER_GROUPS groups2_ 
>     ON 
>     this_.ID= groups2_.USER_ID 
> LEFT OUTER JOIN 
>     rnadarajah.GROUPS groupimpl3_ 
>     ON 
>     groups2_.GROUP_ID= groupimpl3_.id 
> LEFT OUTER JOIN 
>     rnadarajah.ORGANIZATIONS organizati4_ 
>     ON 
>     this_.ORGANIZATION_ID= organizati4_.id 
> LEFT OUTER JOIN 
>     rnadarajah.ORGANIZATIONS organizati5_ 
>     ON 
>     organizati4_.PARENT_ORGANIZATION_ID= organizati5_.id 
> WHERE 
>     this_.ID in (:1)
> When substituting the appropriate ID, a row is returned for every group the user is part of. This makes sense since the mapping to the groups (and join) table is specified as:
>     @ManyToMany(targetEntity = GroupImpl.class, fetch = FetchType.EAGER)
>     @JoinTable(	name = "USER_GROUPS",
>     			joinColumns = @JoinColumn(name = "USER_ID"),
>     			inverseJoinColumns = @JoinColumn(name = "GROUP_ID"))
>     public Set<Group> getGroups()
>     {
>         return _groups;
>     }
> So, the collection is eagerly fetched as specified, but it looks like Hibernate doesn't return a distinct root entity as it should. I'm pretty sure this was working correctly in Hibernate 3.2 CR1 (for sure it was working with some prior version of 3.2), but I can't be sure since we switched from XDoclet to annotations for specifying the mapping of this particular entity at the same time as upgrading and maybe that's where the problem lies. The collection mapping used to be specified as following with XDoclet:
>     /**
>      * @hibernate.set table = "USER_GROUPS"
>      * @hibernate.collection-key column = "USER_ID"
>      * @hibernate.collection-key-column name = "USER_ID"
>      *                                  length = "32"
>      * @hibernate.collection-many-to-many class = "com.xxx.common.model.user.GroupImpl"
>      *                                    column = "GROUP_ID"
>      */
> Using the following, I get only one user as expected, for the single ID supplied:
>                 String queryString = "from UserImpl where id in (:userIds)";
>                 Query query = session.createQuery(queryString);
>                 query.setParameterList("userIds", ids);
>                 return query.list();
> I can do this as a work around, but I'd like to know if this is a problem in Hibernate 3.2 or with something we're doing.

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