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 Grouping	Assignee:	Joseph Marques <jmarques>
Status:	CLOSED NEXTRELEASE	QA Contact:	Pavel Kralik <pkralik>
Severity:	medium	Docs Contact:
Priority:	low
Version:	1.1	CC:	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:	---	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	536002

Description Joseph Marques 2008-10-14 14:53:00 UTC

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 12:26:44 UTC

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 15:15:18 UTC

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 18:05:19 UTC

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 21:20:50 UTC

This bug was previously known as http://jira.rhq-project.org/browse/RHQ-977