+ Start a Discussion
Arthur Almeida 12Arthur Almeida 12 

Omission Problem in Query Salesforce

When I execute this query
List<AggregateResult> query = [
    SELECT Max(Id) Id, AccountId, Max(CustomField__c) 
    FROM Asset WHERE Status = 'X' 
    AND (CustomField__c = 'Z' 
    OR CustomField__c = 'Y') 
    GROUP BY AccountId LIMIT 1000 
];

The first time, happen an error:
Internal Salesforce.com Error

But the second time, I executed this query, the result with success 

User-added image

Why does this happen?
Best Answer chosen by Arthur Almeida 12
Arthur Almeida 12Arthur Almeida 12
I solved my problem using 3 techniques
 
1 - Query inside loop: I followed this question: Query Causes Salesforce Internal Error (https://developer.salesforce.com/forums/?id=906F000000091y6IAA) because after my query I had a for loop, so I put my query inside this loop, according to the documentation of the Salesforce Working with Very Large SOQL Queries (https://developer.salesforce.com/docs/atlas.en-us.238.0.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm):
 
"Your SOQL query sometimes returns so many sObjects that the limit on heap size is exceeded and an error occurs. To resolve, use a SOQL query for loop instead, since it can process multiple batches of records by using internal calls to query and queryMore."
 
The implementation was like this:
for (AggregateResult ar : [SELECT Max(Id) Id, AccountId, Max(CustomField__c) 
                                         FROM Asset WHERE Status = 'X' 
                                         AND (CustomField__c = 'Z' OR CustomField__c = 'Y') 
                                         GROUP BY AccountId LIMIT 1000]) { 
    //logic here 
}
 
unfortunately only this change did not work and the error persisted, well, the first time I execute my query happens an error, but the second time this query, the result with success, so o implemented a second technique:
 
2 - Self Test: I writed a test class that only executes this large query, I scheduled the test class to run 5 minutes before my automation run, I did following this documentation: Scheduling Run All Apex Tests and emailing results (https://www.forcetree.com/2019/12/scheduling-run-all-apex-tests-and.html)
 
The implementation was like this:
global class SelfTestSchedulable implements Schedulable { 
    global void execute(SchedulableContext ctx){ 
        List<ApexClass> testClasses = [SELECT Id FROM ApexClass WHERE Name = 'TestMyLargeQuery']; 
        List<ApexTestQueueItem> queueItems = new List<ApexTestQueueItem>(); 
        for (ApexClass testClass : testClasses) { 
            queueItems.add(new ApexTestQueueItem(ApexClassId=testClass.Id)); 
        } 
        insert queueItems; 
    } 
}
 
unfortunately only this change did not work and the error persisted, so implemented a third technique:
 
3 - Moved the query to inside of the Batch class: Contextualizing, my query stayed inside of the Job, the job pass the results of the query via constructor to the batch because the Batch classes do not support Aggregate in the function Database.getQueryLocator see this documentation: Batch Apex error 'Aggregate query does not support queryMore' (https://help.salesforce.com/s/articleView?language=en_US&type=1&id=000333422), so I chose to put the query in the job, but I had an insight of putting my query inside of the Batch.
 
The implementation was like this:
public Database.QueryLocator start(Database.BatchableContext bc) { 
    List<Id> assetIds = new List<Id>(); 
    for (AggregateResult ar : [SELECT Max(Id) Id, AccountId, Max(CustomField__c) 
                               FROM Asset WHERE Status = 'X' 
                               AND (CustomField__c = 'Z' OR CustomField__c = 'Y') 
                               GROUP BY AccountId LIMIT 1000]) { 
        Id id = (Id)ar.get('Id'); 
        assetIds.add(id); 
    }  
    return Database.getQueryLocator('SELECT Id, AccountId FROM Asset WHERE Id in :assets '+assetIds); 
}

And my problem was solved.

All Answers

Abdul KhatriAbdul Khatri
Hi Arthur,

I have never encountered such issue.

Is this happening everytime, I mean is it very consistent that first time you always get that error and run success every subsquent time?

I can't think of anything other than this that it would have happened at that point of time for some reason within salesforce but not expecting to be happening all the time as I don't see any issus with the SOQL.
Antoninus AugustusAntoninus Augustus
Hi Arthur,
You might be getting the error because you're using the system field 'ID' in the aggregate MAX function. I recommend you remove it and add it to the GROUP BY clause instead.

Check out the docs for more information:
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions.htm



 
Arthur Almeida 12Arthur Almeida 12
@Leamsi Escribano
I do not understand, how would it be my query?
Arthur Almeida 12Arthur Almeida 12
Today, I executed this query in the organizer query plugin, and, first time, this was the result:

User-added image

the second time, this was the result:

User-added image
Arthur Almeida 12Arthur Almeida 12
I solved my problem using 3 techniques
 
1 - Query inside loop: I followed this question: Query Causes Salesforce Internal Error (https://developer.salesforce.com/forums/?id=906F000000091y6IAA) because after my query I had a for loop, so I put my query inside this loop, according to the documentation of the Salesforce Working with Very Large SOQL Queries (https://developer.salesforce.com/docs/atlas.en-us.238.0.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm):
 
"Your SOQL query sometimes returns so many sObjects that the limit on heap size is exceeded and an error occurs. To resolve, use a SOQL query for loop instead, since it can process multiple batches of records by using internal calls to query and queryMore."
 
The implementation was like this:
for (AggregateResult ar : [SELECT Max(Id) Id, AccountId, Max(CustomField__c) 
                                         FROM Asset WHERE Status = 'X' 
                                         AND (CustomField__c = 'Z' OR CustomField__c = 'Y') 
                                         GROUP BY AccountId LIMIT 1000]) { 
    //logic here 
}
 
unfortunately only this change did not work and the error persisted, well, the first time I execute my query happens an error, but the second time this query, the result with success, so o implemented a second technique:
 
2 - Self Test: I writed a test class that only executes this large query, I scheduled the test class to run 5 minutes before my automation run, I did following this documentation: Scheduling Run All Apex Tests and emailing results (https://www.forcetree.com/2019/12/scheduling-run-all-apex-tests-and.html)
 
The implementation was like this:
global class SelfTestSchedulable implements Schedulable { 
    global void execute(SchedulableContext ctx){ 
        List<ApexClass> testClasses = [SELECT Id FROM ApexClass WHERE Name = 'TestMyLargeQuery']; 
        List<ApexTestQueueItem> queueItems = new List<ApexTestQueueItem>(); 
        for (ApexClass testClass : testClasses) { 
            queueItems.add(new ApexTestQueueItem(ApexClassId=testClass.Id)); 
        } 
        insert queueItems; 
    } 
}
 
unfortunately only this change did not work and the error persisted, so implemented a third technique:
 
3 - Moved the query to inside of the Batch class: Contextualizing, my query stayed inside of the Job, the job pass the results of the query via constructor to the batch because the Batch classes do not support Aggregate in the function Database.getQueryLocator see this documentation: Batch Apex error 'Aggregate query does not support queryMore' (https://help.salesforce.com/s/articleView?language=en_US&type=1&id=000333422), so I chose to put the query in the job, but I had an insight of putting my query inside of the Batch.
 
The implementation was like this:
public Database.QueryLocator start(Database.BatchableContext bc) { 
    List<Id> assetIds = new List<Id>(); 
    for (AggregateResult ar : [SELECT Max(Id) Id, AccountId, Max(CustomField__c) 
                               FROM Asset WHERE Status = 'X' 
                               AND (CustomField__c = 'Z' OR CustomField__c = 'Y') 
                               GROUP BY AccountId LIMIT 1000]) { 
        Id id = (Id)ar.get('Id'); 
        assetIds.add(id); 
    }  
    return Database.getQueryLocator('SELECT Id, AccountId FROM Asset WHERE Id in :assets '+assetIds); 
}

And my problem was solved.
This was selected as the best answer