[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2760) Criteria Query shows too few results with ResultTransformer (DISTINCT_ROOT_ENTITY) when using MAX RESULTS and Criteria.list() on Entity with ManyToMany join

Criteria Query shows too few results with ResultTransformer (DISTINCT_ROOT_ENTITY) when using MAX RESULTS and Criteria.list() on Entity with ManyToMany join ------------------------------------------------------------------------------------------------------------------------------------------------------------ Key: HHH-2760 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2760 Project: Hibernate3 Issue Type: Bug Environment: Hibernate 3 (Hibernate-Version: 3.2.4.sp1) MySql 4,.1 MSSQL 2000 Reporter: Philip Saville When there is a ManyToMany join and using the criteria.list method for retrieving data, not all rows are retrieved when using maxResults (and join is set to EAGER fetch) Role id 1 = USER id 2 = MANAGER id 3 = ADMIN User id 1 = User --> Role.id1 id 2 = Manager --> Role.id1, Role.id2 id 3 = Admin --> Role.id1, Role.id2, Role.id3 criteria = Criteria.forClass(User.class); criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) criteria.addOrder(Order.asc("id")) criteria.setMaxResults(2) List list = criteria.list(); Should see both User and Manager (expect to see all roles for each user). Now reverse the order on id: criteria.addOrder(Order.desc("id")) The resulting list will only show Admin, since the max-results restriction on the fetch is calculated before the eager fetch of collections has rehydrated the entity object. My test case actually was maxResults = 10, and I had many more users with differing numbers of Roles associated. Another strange case (probably related): As an attempted work-around, I tried using criteria.scroll(). The ScrollableResults object was properly transformed to the ROOT_ENTITY, but I'm not sure if I should do the "DISTINCT" part when retrieving from the result set or not, so I actually got duplicate Users in the resulting list. When using this work around, if you order by User.name (i.e. admin, manager, user), the Admin objects (duplicated) do not have the Roles properly set (each User object had only USER role set - but Admin did show 3 times). If you reverse the oder so Admin is not the first User in the list, then Admin's roles are populated correctly (all three roles show in each of the 3 Admin objects). The final hurdle which makes this problem more difficult to fix is the pagination of results. If you are using "setMaxResults", you are probably also using "setFirstResult". So the correct page offset must be found when first de-duping. I think this problem is specific to the DistinctRootEntityResultTransformer but could also be an issue with other transformers. However, the transformer is not responsible for the maxResults result set limitation....