+ Start a Discussion
DevGarethmdDevGarethmd 

Aggregate query has too many rows for direct assignment use for loop

I have an issue with using subqueries and am trying to establish whether this is a bug or not. Basically if I have a subquery then I can't directly access the list if the subquery contains more than 200 records a QueryException is thrown (Aggregate query has too many rows for direct assignment use for loop). If there are less than 200 records everything works fine. To illustrate the point the following code will fail when acct.contacts.size() is called

 

Account accToTest = new Account(name = 'testAccount');
insert accToTest;

list<Contact> contacts = new list<Contact>();

for(integer x=0;x<200;x++){
contacts.add(new Contact(AccountId = accToTest.id,
firstName = 'test' + x.format(),
lastName = 'name'));
}
insert contacts;

Test.StartTest();

for(Account acct : [select id,
name,
(select firstName from Contacts)
from Account
where id =: accToTest.id]){
system.debug(acct.contacts.size());
}

Test.StopTest();

I know that there are workarounds by iterating through the contacts list but this seems to be more expensive generating additional script statements. I have also seen a  Post from earlier in the year that described a similar problem, but was isolated to batch classes, whereas this appears to be more general.

 

Can anyone tell me if this is designed behaviour or if this is a bug in the platform.

 

Thanks

Gareth