System.QueryException: Aggregate query does not support queryMore()

Question

I am trying to deploy around 80 Components which include Apex Class,Trigger and Visualforce Pages. But it is failing due to below exception,

MyTrigger: execution of BeforeInsert caused by: System.QueryException: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch Trigg...

All test classes are run as a part of deployement process hence few of the test classes are making this trigger fire causing error. As its a change set deployment error, could not get the complete error statement. Also my trigger includes below code.

if(trigger.isBefore & trigger.isInsert) {
        for(AggregateResult ar : [select ABC__c, Name, max(Revision__c) maxRev from XYZ__c 
                                    where ABC__c in :oppMap.keySet()
                                    group by ABC__c, Name order by ABC__c, Name]) {
            decimal maxRev = Integer.valueOf(ar.get('maxRev'));
            string oppAppKey = String.valueOf(ar.get('ABC__c')) + String.valueOf(ar.get('Name'));
            lrMap.put(oppAppKey, maxRev);
        }
    }

Let me know how to fix this. Should i try using limit or is there any other solution?

Answer 1

I updated my code as below and it worked for me.

 Integer recordCount = [select count() from XYZ__c where ABC__c in :oppMap.keySet()];
            Integer sizeBlock = recordCount/2000 + (math.mod(recordCount,2000)!=0?1:0) ;
            Set<Id> setAppIds = new Set<Id>(); 

            //Iterate aggregate result with blocks of 2000 to avoid exception
            for(Integer i=0;i<sizeBlock;i++){            
                for(AggregateResult ar : [select id,ABC__c, Name, max(Revision__c) maxRev from XYZ__c
                                            where ABC__c in :oppMap.keySet() and id not in :setAppIds 
                                            group by id,ABC__c, Name order by ABC__c, Name limit 2000]) {
                    decimal maxRev = Integer.valueOf(ar.get('maxRev'));
                    string oppAppKey = String.valueOf(ar.get('ABC__c')) + String.valueOf(ar.get('Name'));
                    lrMap.put(oppAppKey, maxRev);
                    setAppIds.add(Id.valueOf(String.valueOf(ar.get('id'))));
                }
            }

courtesy :- @Peter Knolle

Answer 2

All my searches for that error message indicated it was occurring during batch Apex. Is it possible this trigger if firing during batch processing?

If so, a short term fix might be to exclude the query in batch context using System.isBatch().

---

In the Nonprofit Stater Pack the same error message was recorded as an issue. The corresponding checkin/revision 483 showed the SOQL query changing from:

for (sobject r : Database.query(soqlStatement)){
    // ...
}

To (edited to show just the important parts):

//generic object list for database query results
//resolves aggregrate querymore issue
list<sobject> objectList = database.query(soqlStatement);
        
for (sobject r : objectList ) {
    // ...
}

So you might try:

List<sObject> results = [select ABC__c, Name, max(Revision__c) maxRev from XYZ__c 
                                where ABC__c in :oppMap.keySet()
                                group by ABC__c, Name order by ABC__c, Name];
for(sObject ar : results ) {
        decimal maxRev = Integer.valueOf(ar.get('maxRev'));
        string oppAppKey = String.valueOf(ar.get('ABC__c')) + String.valueOf(ar.get('Name'));
        lrMap.put(oppAppKey, maxRev);
}

---

As per comment from @peter-knolle to the official documentation:

Note that queries including an aggregate function don't support queryMore. A runtime exception occurs if you use a query containing an aggregate function that returns more than 2000 rows in a for loop. Source: SOQL For Loops Versus Standard SOQL Queries

So it is the combination of the SOQL for loop syntax and more than 2000 aggregate results that is causing the error.

Answer 3

Since you are already using in your SOQL statement a GROUP BY and ORDER BY clause, an alternative approach is to add LIMIT and OFFSET:

Integer batchSize = 2000;
Integer nrOfRecordsProcessed = 0;

Integer rowsReturned = batchSize;
while (rowsReturned == batchSize) {
  rowsReturned = 0;
  for(AggregateResult ar : [select ABC__c, Name, max(Revision__c) maxRev from XYZ__c 
                            where ABC__c in :oppMap.keySet()
                            group by ABC__c, Name order by ABC__c, Name 
                            LIMIT :batchSize OFFSET :nrOfRecordsProcessed]) {
    decimal maxRev = Integer.valueOf(ar.get('maxRev'));
    string oppAppKey = String.valueOf(ar.get('ABC__c')) + String.valueOf(ar.get('Name'));
    lrMap.put(oppAppKey, maxRev);
    rowsReturned++;
  }
  nrOfRecordsProcessed = nrOfRecordsProcessed + rowsReturned;
}

This way you don't need to modify the query with a different grouping criteria and an additional condition. Also, you don't need the additional Set and neither the first SOQL to get the COUNT().

Answer 4

Implementing Iterable interface instead of Query Locator in batch class.

global class AggregateResultIterator Implements Iterator<AggregateResult>{ 

AggregateResult [] results {get;set;} 
Integer index {get;set;} 

global AggregateResultIterator(){ 
String query = 'select Id, Max(ModifiedDate) from Campaign GROUP BY Id '; 
results = Database.query(query); 
} 

global boolean hasNext(){ 
return results !=null && !results.isEmpty() && index < results.size(); 
} 

global AggregateResult next(){ 
return results[index++]; 
} 
}