Aggregate Query has too many rows for direct assignment, use FOR loop

It’s an interesting error message, isn’t it? The implication is that, somewhere, my Apex code is doing an aggregate query, and while it’s returning not enough rows to generate a governor limit error, it is somehow returning enough rows to generate this error.

The only problem is that, in my case, I’m not doing an aggregate query at all. (Or, at least I don’t think I am….) Here’s my query:

select Id, Name, GrandTotal,
       (select id, PricebookEntryId, UnitPrice, Quantity, Discount, TotalPrice
        from QuoteLineItems)
from Quote
where Id = :quoteId

No aggregate functions (COUNT, SUM, etc.), no GROUP BY clause, and no apparent place to use a FOR loop. (In the interest of full disclosure, note that I omitted a bunch of fields from the query, but that’s not relevant to the problem at hand.)
Even worse, the error isn’t happening when I execute my query. The query runs just fine, returning only one Quote, and my code keeps running. But later, well after the query completes successfully, I wind up with this fatal error:

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

According to the debug log, the error isn’t associated with any specific line of code: the error just appears at the end of the debug log, after my query has apparently executed successfully. And, of course, because of the error, the transaction rolls back and my database updates are gone.

Let’s take a step back. My mission is to implement a custom button on the Quote page. When clicked, the button should call an Apex web service method, passing in the Quote Id. The method should clone the given Quote and all of its line items. (Yes, oddly enough, while Salesforce provides a standard “Clone Opportunity and its Products” button, it doesn’t provide the same functionality for Quotes.)

I wrote my button and the Apex method, and it worked just fine. For months, it worked just fine. But then, for one Quote in particular, it failed, with this message. Augh!
Here’s a snippet of the code:

Quote q = [
  select Id, Name, GrandTotal,
         (select id, PricebookEntryId, UnitPrice, Quantity, Discount, TotalPrice
          from QuoteLineItems)
from   Quote
where  Id = :quoteId];

Quote clonedQuote = q.clone(false);
clonedQuote.Name = 'Copy of ' + q.Name;
insert q;

List lstOrigQLIs = q.QuoteLineItems;
List lstClonedQLIs = lstOrigQLIs.deepClone(false);
for (QuoteLineItem qli : lstClonedQLIs) {
  qli.QuoteId = clonedQuoteId;
}
insert lstClonedQLIs;

There was more to the code than this, including try/catch blocks, but you get the gist of it.

After a lot of trial and error, I finally determined that the problem didn’t have anything to do with (what you or I would call) aggregate queries, and the solution wasn’t a FOR loop. The problem was that there were over 250 Quote Line Items for the Quote in question, and Salesforce just doesn’t allow you to work with that many child records in a sub-select. Oh sure, the query will retrieve all of the child records, but at some point, the system decides that it won’t let you work with them, so you get the (not very helpful) error message, “Aggregate query has too many rows for direct assignment, use FOR loop.”

What is an aggregate query?

The SOQL documentation doesn’t explicitly define the term “aggregate query,” but it does describe “aggregate function” by saying, “Aggregate functions include AVG(), COUNT(), MIN(), MAX(), SUM(), and more.” It’s the “and more” that is relevant here.

In the section on “Understanding Relationship Names, Custom Objects, and Custom Fields,” the SOQL documentation describes a query that traverses the parent-to-child relationship as a type of aggregate query. So even though our original query doesn’t use any aggregate functions, it’s considered an “aggregate query” because the sub-select clause traverses the parent-to-child relationship.

The solution is simple (once you understand the problem), and (of course) doesn’t involve any FOR loops. Here’s the revised code, with the changes bolded:

Quote q = [
  select Id, Name, GrandTotal,
  from Quote
  where Id = :quoteId];

Quote clonedQuote = q.clone(false);
clonedQuote.Name = 'Copy of ' + q.Name;
insert q;

List lstOrigQLIs = [
  select id, PricebookEntryId, UnitPrice, Quantity, Discount, TotalPrice
  from QuoteLineItems
  where QuoteId = :quoteId];
List lstClonedQLIs = lstOrigQLIs.deepClone(false);
for (QuoteLineItem qli : lstClonedQLIs) {
  qli.QuoteId = clonedQuoteId;
}
insert lstClonedQLIs;

That’s it. No additional FOR loops. All I did was remove the sub-select from the original query, and instead issue a separate query to get the original line items.

Fortunately, this code is called from a custom button, not a trigger, so it doesn’t have to be bulkified. Bulkifying the new SELECT statement to generate Quote-specific lists of Quote Line Items would be a little more complicated, but (of course) still doable.

While this problem was ultimately solvable, the error message was particularly mystifying. What other mystifying error messages have you seen from Salesforce? Share your favorites in the comments section below, and we’ll see if we can help to de-mystify them too.