System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop

It was a surprise for me. It's fair to say I wasn't expecting that. The next code block will throw the exception when a number of child records greater or equals to 200.

for (Account a : [SELECT Id, (SELECT Id FROM Contacts) FROM Account]) {
    System.debug(p.Contacts.size()); // throw exception			
}

Here is the way how you can avoid this exception, just use for loop to calculate a number of children. It’s not graceful, but it works.

for (Account a : [SELECT Id, (SELECT Id FROM Contacts) FROM Account]) {
    Integer numberOfContacts = 0;
    for (Contact c : a.Contacts) {
        numberOfContacts++;
    }
    System.debug(numberOfContacts);		
}

Useful links:

• 'Aggregate query has too many rows for direct assignment, use FOR loop' error