Inconsistency with error “Aggregate query has too many rows for direct assignment”

Question

I have a Box__c record with 100 Doors__r, 104 Motors__r, and 0 Wheels__r.

ISSUE #1

I am getting the error

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

with the following code:

string q = 'select Id, ' +
           '(select id from Doors__r), ' +
           '(select id from Wheels__r), ' +
           '(select id from Motors__r) ' +
           'from Box__c where id =\'a0XX000000T5fnZZZS\'';

Database.QueryLocator locator = Database.getQueryLocator(q);
Database.QueryLocatorIterator it = locator.iterator();

while (it.hasNext())
{
    SObject l = it.next();
    l.getSObjects('Doors__r'); // error!
}

Why is it complaining about aggregate query when there is none?

ISSUE #2

The error goes away when the Wheels__r sub-query is removed. Why?

ISSUE #3

If I remove the QueryLocator the error goes away:

SObject[] ls = Database.query(q);

for (SObject l : ls)
{
    l.getSObjects('Doors__r');
}

Why is that?

ISSUE #4

I am not able to replicate these issues in a different org (using different objects but same record counts). What could be the reason?

Answer 1

Salesforce, from time to time, uses some not-so-clear wording and terminology. A perfect example is "Aggregate" queries. You might think that an "Aggregate" query is one that uses Aggregate functions like COUNT() or MIN() along with GROUP BY.

That's completely logical (heck, you even get an AggregateResult[] from a query that uses Aggregate functions), but incorrect (or, at least, incomplete).

In Salesforce's world, the "Aggregate" query in the error that you're getting is a subquery, something like a left outer join (parent-child subquery) or a semijoin (subquery in the WHERE clause).

Unfortunately, there isn't any official documentation that I can find that explains exactly why this happens.

Probably the best documentation I can find that is related to this issue is in two pages Understanding Query Results and SOQL for loops. From the first page (emphasis mine):

Subquery results are like regular query results in that you might need to use queryMore() to retrieve all the records if there are many children. For example, if you issue a query on accounts that includes a subquery, your client application must handle results from the subquery as well:

1. Perform the query on Account.
2. Iterate over the account QueryResult with queryMore().
3. For each account object, retrieve the contacts QueryResult.
4. Iterate over the child contacts, using queryMore() on each contact's QueryResult.

From the second page:

You might get a QueryException in a SOQL for loop with the message Aggregate query has too many rows for direct assignment, use FOR loop. This exception is sometimes thrown when accessing a large set of child records (200 or more) of a retrieved sObject inside the loop, or when getting the size of such a record set. For example, the query in the following SOQL for loop retrieves child contacts for a particular account. If this account contains more than 200 child contacts, the statements in the for loop cause an exception.

for (Account acct : [SELECT Id, Name, (SELECT Id, Name FROM Contacts)
                FROM Account WHERE Id IN ('<ID value>')]) {
    List<Contact> contactList = acct.Contacts; // Causes an error
    Integer count = acct.Contacts.size(); // Causes an error
}

So the error likely has something to do with chunking the retrieval of records returned by a query and queryMore(). Saying anything beyond that would probably be speculation.

Yours is the first situation that I've seen where this error has popped up having 200+ child records spread across more than one child SObject.

I can't give a satisfactory answer to your 2nd, 3rd, and 4th issues. The workaround for this error is to use a for() loop, as suggested. That would probably look like this in your case:

while (it.hasNext())
{
    SObject l = it.next();
    for(SObject child :l.getSObjects('Doors__r'))
    {
        // add child records to a collection, or perform
        //   some other computation/logic, one-by-one in here.
    }
}

Answer 2

Generally when I see an error of this type, I try to pull out the sub-queries and form top level queries. Also, you may have some luck modifying for or while loops as you have shown in this case.

I think you might over over this Apex Governor limit: For loop list batch size 200

It also states here that: The list of children cannot be used in anyway except in a for loop.