Bug 749589 - criteria API - a Criteria w/ an unlimited PageControl, as well as one or more fetches, returns duplicate results
Summary: criteria API - a Criteria w/ an unlimited PageControl, as well as one or more...
Keywords:
Status: CLOSED WORKSFORME
Alias: None
Product: RHQ Project
Classification: Other
Component: Core Server
Version: 4.1
Hardware: Unspecified
OS: Unspecified
medium
high
Target Milestone: ---
: JON 3.1.1
Assignee: RHQ Project Maintainer
QA Contact: Mike Foley
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2011-10-27 15:46 UTC by Ian Springer
Modified: 2013-08-06 00:41 UTC (History)
3 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2012-08-20 13:13:10 UTC
Embargoed:


Attachments (Terms of Use)


Links
System ID Private Priority Status Summary Last Updated
Red Hat Bugzilla 620603 0 unspecified CLOSED investigate root cause of "firstResult/maxResults specified with collection fetch; applying in memory!" 2021-02-22 00:41:40 UTC

Internal Links: 620603

Description Ian Springer 2011-10-27 15:46:11 UTC
As an example, when I do:

RoleCriteria roleCriteria = new RoleCriteria();
roleCriteria.addFilterId(2); // "All Resources" role
roleCriteria.setPageControl(PageControl.getUnlimitedInstance()); // disable paging
roleCriteria.fetchSubjects(true);
roleCriteria.fetchResourceGroups(true);
roleCriteria.fetchLdapGroups(true);
PageList<Role> roles = RoleManageBean.findRolesByCriteria(pageControl);

the returns list of roles contains 14 identical/redundant Roles, whereas running the same code with a page control of PageControl.getExplicitPageControl(0, 50) returns a single Role as would be expected.

The generated JPQL for the above call to findRolesByCriteria() is:

SELECT role
FROM Role role
LEFT JOIN FETCH role.permissions
LEFT JOIN FETCH role.resourceGroups
LEFT JOIN FETCH role.subjects
LEFT JOIN FETCH role.ldapGroups
WHERE ( role.id = 2 )

And the SQL that Hibernate generates from this is:

select role0_.ID as ID93_0_, resourcegr3_.ID as ID20_1_, subject5_.ID as ID113_2_, ldapgroups6_.ID as ID22_3_, role0_.NAME as NAME93_0_, role0_.DESCRIPTION as DESCRIPT3_93_0_, role0_.FSYSTEM as FSYSTEM93_0_, permission1_.ROLE_ID as ROLE1_0__, permission1_.operation as operation0__, resourcegr3_.NAME as NAME20_1_, resourcegr3_.DESCRIPTION as DESCRIPT3_20_1_, resourcegr3_.CTIME as CTIME20_1_, resourcegr3_.MTIME as MTIME20_1_, resourcegr3_.MODIFIED_BY as MODIFIED6_20_1_, resourcegr3_.GROUP_DEFINITION_ID as GROUP13_20_1_, resourcegr3_.GROUP_BY as GROUP7_20_1_, resourcegr3_.RECURSIVE as RECURSIVE20_1_, resourcegr3_.CATEGORY as CATEGORY20_1_, resourcegr3_.RESOURCE_TYPE_ID as RESOURCE15_20_1_, resourcegr3_.SUBJECT_ID as SUBJECT14_20_1_, resourcegr3_.CLUSTER_KEY as CLUSTER10_20_1_, resourcegr3_.CLUSTER_RESOURCE_GROUP_ID as CLUSTER12_20_1_, resourcegr3_.AUTO_GROUP_PARENT_RESOURCE_ID as AUTO16_20_1_, resourcegr3_.visible as visible20_1_, resourcegr2_.ROLE_ID as ROLE2_1__, resourcegr2_.RESOURCE_GROUP_ID as RESOURCE1_1__, subject5_.NAME as NAME113_2_, subject5_.FIRST_NAME as FIRST3_113_2_, subject5_.LAST_NAME as LAST4_113_2_, subject5_.EMAIL_ADDRESS as EMAIL5_113_2_, subject5_.SMS_ADDRESS as SMS6_113_2_, subject5_.PHONE_NUMBER as PHONE7_113_2_, subject5_.DEPARTMENT as DEPARTMENT113_2_, subject5_.FACTIVE as FACTIVE113_2_, subject5_.FSYSTEM as FSYSTEM113_2_, subject5_.CONFIGURATION_ID as CONFIGU11_113_2_, subjects4_.ROLE_ID as ROLE2_2__, subjects4_.SUBJECT_ID as SUBJECT1_2__, ldapgroups6_.ROLE_ID as ROLE3_22_3_, ldapgroups6_.LDAP_GROUP_NAME as LDAP2_22_3_, ldapgroups6_.ROLE_ID as ROLE3_3__, ldapgroups6_.ID as ID3__ from RHQ_ROLE role0_ left outer join RHQ_PERMISSION permission1_ on role0_.ID=permission1_.ROLE_ID left outer join RHQ_ROLE_RESOURCE_GROUP_MAP resourcegr2_ on role0_.ID=resourcegr2_.ROLE_ID left outer join RHQ_RESOURCE_GROUP resourcegr3_ on resourcegr2_.RESOURCE_GROUP_ID=resourcegr3_.ID left outer join RHQ_SUBJECT_ROLE_MAP subjects4_ on role0_.ID=subjects4_.ROLE_ID left outer join RHQ_SUBJECT subject5_ on subjects4_.SUBJECT_ID=subject5_.ID left outer join RHQ_ROLE_LDAP_GROUP ldapgroups6_ on role0_.ID=ldapgroups6_.ROLE_ID where role0_.ID=2

Interestingly, whether or not paging is enabled, Hibernate generates the exact same SQL. And when executed manually, the SQL returns 14 identical results. The only notable difference in the Server log when findRolesByCriteria() is called is that when paging is enabled, Hibernate logs:

WARN  [org.hibernate.hql.ast.QueryTranslatorImpl]
firstResult/maxResults specified with collection fetch; applying in memory!

This warning generally shows up when a query with both join fetches and paging is executed (see http://java.dzone.com/articles/hibernate-tuning-queries-using?page=0,0 for details). So my guess is that the Hibernate code that does the in-memory paging also has the side effect of filtering out any duplicate results, which would explain why the same query run without paging returns the duplicate results.

One potential workaround for this would be to do something like this in CriteriaQueryRunner.getCollection():

  Query query = queryGenerator.getQuery(this.entityManager);
  List<T> results = query.getResultList();
  PageControl pageControl = CriteriaQueryGenerator.getPageControl(this.criteria);
  // We could further optimize by also checking in this if statement if the criteria specifies one or more fetches.
  if (pageControl.getPageSize() == PageControl.SIZE_UNLIMITED) {
     return new LinkedHashSet<T>(results); 
  } else {
     return results;
  }

Wrapping the returned list of results in a LinkedHashSet would remove duplicate results, preserving the list's ordering.

Comment 1 Charles Crouch 2012-03-08 21:40:54 UTC
Assigning for *consideration* for the JON3.1.0 release, it would be good to finally to get rid of the Hibernate warnings, if possible, and also make the results returned the same, regardless of whether pagination is enabled.

Comment 2 Charles Crouch 2012-05-21 19:16:09 UTC
As per triage, targeting at JON3.1.1

Comment 3 Jay Shaughnessy 2012-06-05 20:21:53 UTC
What do you have in inventory to make this happen?  I can't seem to reproduce.

Comment 4 Charles Crouch 2012-08-20 13:13:10 UTC
Closing based on Jay's comment #3. If anyone see's this again please reopen and include a description of the inventory.


Note You need to log in before you can comment on or make changes to this bug.