Bug 536648 (RHQ-977)

Summary: groupby expressions that return more than 1000 unique results in SQLGrammarException
Product: [Other] RHQ Project Reporter: Joseph Marques <jmarques>
Component: Resource GroupingAssignee: Joseph Marques <jmarques>
Status: CLOSED NEXTRELEASE QA Contact: Pavel Kralik <pkralik>
Severity: medium Docs Contact:
Priority: low    
Version: 1.1CC: mvecera
Target Milestone: ---Keywords: SubBug
Target Release: ---   
Hardware: All   
OS: All   
URL: http://jira.rhq-project.org/browse/RHQ-977
Whiteboard:
Fixed In Version: 1.2 Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---
Bug Depends On:    
Bug Blocks: 536002    

Description Joseph Marques 2008-10-14 10:53:00 EDT
looking at the server log:

ERROR [org.rhq.enterprise.gui.definition.group.EditGroupDefinitionGeneralPropertiesUIBean] There was a problem calculating the results: javax.ejb.EJBTransaction... 	
WARN SQL Error: 1795, SQLState: 42000 	
ERROR [org.hibernate.util.JDBCExceptionReporter] ORA-01795: maximum number of expressions in a list is 1000

this can easily be refactored to work with arbitrarily large result lists, but the priority probably isn't all that high.  dynagroups have been out for 4 months now, and not a single customer has hit this issue - a good thing  ; )

Comment 1 Joseph Marques 2008-12-25 07:26:44 EST
rev2547 -  fix helper method used by group definition processing mechanism to subvert ORA-01795 issue during addition and/or removal of more than 1000 numbers of members to the dynagroup;
Comment 2 Joseph Marques 2009-01-08 10:15:18 EST
to test this, generate a large inventory.  then use the expression:

groupby resource.type.plugin

calculate these results.  if any of the resource groups that are created as a result have more than 1000 members, then this bug has been verified fixed.  

if you don't have more than 1000 members in any groups, add more resources to your inventory, and then recalculate.  repeat this step until you've achieved the needed size.
Comment 3 Pavel Kralik 2009-01-12 13:05:19 EST
I populate a postgres db with 1000 additional tables, took it into inventory and recalculated an expression. Total number of members in the Postgres group was 1118. It recalculated all the groups without an error.

RHEL5.2, x86_64, PostgreSQL 8.1.11 
Comment 4 Red Hat Bugzilla 2009-11-10 16:20:50 EST
This bug was previously known as http://jira.rhq-project.org/browse/RHQ-977