function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
GoForceGoGoForceGo 

Why am I getting Governer Limits message for this query?

I have these number of records in the database for the following queries:

 

Total Parent__c records = 2800

Total Child__c records  = 77,800

 

On an average, each parent has around 30 childs.

 

When I run the following piece of code, I get a governor limit error (query row). Note that I get retrieving only 10 parent records! I still get this error. I  debug log says that only 177 records out of allowed 10,000 were retrieved.  Seems like there is some issue with Childs__r.size() statement. 

 

 

 

for (Parent__c p: [select Name,
(select Name from Childs__r)
from Parent__c limit 20] {
i++;
System.debug('i is ' + i);
System.debug('Name is ' +p.Name);
Double count =p.Childs__r.size();
System.debug('Child Count is ' + count);


}

 

System Log says:

 

 

12:44:31 ERROR - Evaluation error: System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop

 

Resource usage for namespace: (default)

Number of SOQL queries: 1 out of 100

Number of query rows: 177 out of 10000

Number of SOSL queries: 0 out of 20

Number of DML statements: 0 out of 100

Number of DML rows: 0 out of 10000

Number of script statements: 51 out of 200000 

 

 

When I write a differenr query where I get 1100 Parents, the query ran for 6 minutes and and I loop through every child of it,  I am able to go through 29678 records (i=29678).  The debug log says that that number of query rows is 1267...records...what's going on? 

 

 

for (Parent__c p: [select Name,
(select Name from Childs__r)
from Parent__c limit 1100] {

for (Child__c c:p.Childs__r) {

i++;
System.Debug(‘Child Name is ‘ + c.Name);
}

}

System.debug('i is ' + i);

 

 Debug Log:

 

Resource usage for namespace: (default)

Number of SOQL queries: 1 out of 100

Number of query rows: 1267 out of 10000

Number of SOSL queries: 0 out of 20

Number of DML statements: 0 out of 100

Number of DML rows: 0 out of 10000

Number of script statements: 59362 out of 200000

Maximum heap size: 938 out of 1000000 

 

 

 

 

Message Edited by GoForceGo on 03-02-2009 12:47 PM
Message Edited by GoForceGo on 03-02-2009 12:47 PM
Message Edited by GoForceGo on 03-02-2009 12:48 PM
BritishBoyinDCBritishBoyinDC

Might be something to do with this - in the section on Execution Limits

 

2 In a SOQL query with parent-childrelationship sub-queries, each parent-child relationship counts asan additional query. These types of queries have a limit of threetimes the number for top-level queries. The row counts from theserelationship queries contribute to the row counts of the overall scriptexecution. 

GoForceGoGoForceGo

Thanks. I am aware of this line.

 

This line would mean that I have 300 child queries I can do (3 times 100, which is limit for parents) and that child records must be included in the 10k maximum record retrieved count.

 

However, it still doesn't explain. In my first code, i am retrieving only 20 parent records, 600 child records. It fails.And it says i am retrieving 177 records. 

 

In my second code, I am retriving 1100 parents, 28K child records and it works. And it says i am retriving 1267 records.

 

 

 

 

HarmpieHarmpie

I am having the same issue. In my case 11 Accounts, with a total of roughly 1180 contacts.

 

The code below breaks, with the same error (aggregate query ...):

for(Account[] allAccounts : [SELECT Id,Name, (Select Id,Geboorteplaats__c, Geboorte_datum__c, EHV_Nummer__c, Cursistennummer__c,Name,Account.Name,Account.Id FROM Contacts) FROM Account WHERE Id IN :allOppAccountIds OR ParentId IN :allOppAccountIds]) { for(Account a : allAccounts) { if(a.contacts.size()>0) { List<WrappedContact> cList = new List<WrappedContact>(); Integer count = 0; for(Contact c : a.contacts) { WrappedContact aWrappedContact = new WrappedContact(c,regDeelnemers.containsKey(c.Id),regDeelnemers.get(c.Id)); cList.add(aWrappedContact); count++; } accountToContacts.put(a.Id,cList.clone()); } } } }

 

 

 

While this code, works fine, but just runs for more then 1 minute before records are presented:

 

for(Account[] allAccounts : [SELECT Id,Name, (Select Id,Geboorteplaats__c, Geboorte_datum__c, EHV_Nummer__c, Cursistennummer__c,Name,Account.Name,Account.Id FROM Contacts) FROM Account WHERE Id IN :allOppAccountIds OR ParentId IN :allOppAccountIds]) { for(Account a : allAccounts) { //if(a.contacts.size()>0) { List<WrappedContact> cList = new List<WrappedContact>(); Integer count = 0; for(Contact c : a.contacts) { WrappedContact aWrappedContact = new WrappedContact(c,regDeelnemers.containsKey(c.Id),regDeelnemers.get(c.Id)); cList.add(aWrappedContact); count++; } accountToContacts.put(a.Id,cList.clone()); //} } } }

 

 

Seems that the size() method on a relational query list is somehow bugged?

Message Edited by Harmpie on 05-15-2009 07:56 AM
GoForceGoGoForceGo

Yes it does seem buggy. i think as a workaround, if you need to know the size, you can just copy members to another

list by using .addAll function in a list and just look at its size.

 

Relational lists are strange. In once case, I wasn't able to just use "update xxx.yyy__r" where yyy__r is a list.

 

 

TehNrdTehNrd
Anyone go to support or get a definitive answer on this? I'm getting the same thing when I try to use the size() method on child records with batch apex.
TehNrdTehNrd
Of course this is a pain to reproduce as when I try to do this with one account it works, but in batch it fails. Here is what I have done to get around this bug.

//Original, this would fail if Contacts.size() was large, something over a 1000 but wasn't able to nail down exact number if(a.Contacts.size() > 0){ accts.add(a); } //Here is my workaround try{ if(a.Contacts[0] != null){ accts.add(a); } }catch(exception e){}

 


GoForceGoGoForceGo

I don't have an update...except that just use the workaround!

 

you can just copy members to another list by using .addAll function in a list and just look at its size

 

 

Force2b_MikeForce2b_Mike

Actually, I was still getting the same error using the a.Contacts[0] type syntax. It looks like it was solved by doing the following:

 

 

for (Contact c : a.Contacts) {
  ... actions ...
}

If Contacts contains no rows then nothing gets executed. I still wrap it in a try/catch block to be on the safe side.

 

It would be nice if SalesForce would respond to this one.

 

Mike

 

dmchengdmcheng

Just a followup that this type of error is still occurring with relationship queries in batches.  The error thrown is "Aggregate query has too many rows for direct assignment."  Very annoying because the maximum number of child records returned for one parent in my situation is only 41.

KunisettyKunisetty

I ran in to the same issue with one of my client's orgs.

 

Code was working fine for several months and suddenly started showing this error for one particular account where the number of child records are 198. For all other accounts - including for an account where there are 174 child records - the code working fine without an error.

 

I think the issue is not based on the number of child records. Will try to play on this little further and keep you all guys posted in the next day or two!

Force2b_MikeForce2b_Mike

I'm still getting this error as well. If you've been unable to replicate this, I'd be happy to do a remote session to review my code and see the error in action.

 

Best Regards,

 

Mike

nathanael.mnathanael.m

Same issue as well. Query returns 5 records and each record has ~40 child records.

@login.ax974@login.ax974

Hi TehNrd,

 

Did you get the error in batch even if you do the below:

 

List<Contact> newList = new List<Contact>();

newList.addAll(a.Contacts);

 

if(newList.size() > 0)

{

 accts.add(a);

}

 

Please let me know as i as well got the same error in batch and have tried to fix it in this way as suggested in the post.

 

Thanks.

goomycgoomyc
Hello all, We had the same issue and Salesforce support answered as follows: -> I got an update form Tier3 where they informed that when an inner query returns more than 199 records then we get "Aggregate query has too many rows for direct assignment, use FOR loop" error. <- So it seems to be related to the number of records being returned from the nested SOQL query. I have limited the number of returned child record to 198 for testing purposes and the Exception wasn't thrown anymore. The only way to solve it is to avoid nested queries, i.e. this is what Tier3 suggested as workaround. Happy reworking of your code! Cheers Sascha
Force2b_MikeForce2b_Mike

Thank you for the update. It's almost like confirmation that I'm not crazy.

 

I wonder if they consider that a bug and will ever fix it?

skausskaus

Interesting , nested query isn't limited to a specific no.. When my parent query returns 1 and nested query returns 10 , I see this error. Its really relative to the parent query rows. Can't even use a simple fix of adding a limit. Only way out is to remove nested query alltogather. Not happy !!

akallioWileyakallioWiley

I'm running into this error, too. And the explanation offered by Tier 3 above doesn't quite add up because I, like other people on this board, am getting the error for queries that return less than 199 child records.

 

 

@login.ax974@login.ax974