Why can I loop over the result of a SOQL query as if it is a list of lists?

Question

I just found a construct similar to the following in a colleague's code, which I didn't expect to even compile:

for (List<Lead> leads : [ 
    SELECT Id FROM Lead LIMIT 1000
])
{
    for (Lead lead : leads) {
        // do some stuff
    }
}

You can paste the code above into an Execute Anonymous window and it will execute without error.

What's going on here? As far as I know, [SELECT Id FROM Lead LIMIT 1000] should return a List<Lead>, but above we're looping over it as if it returned a List<List<Lead>>.

The only documentation I can find about the [SELECT ...] syntax being able to return anything other than a list of sObjects is at https://developer.salesforce.com/trailhead/apex_database/apex_database_soql, where it's noted that including LIMIT 1 in the query will cause it to return a single sObject instead of a List:

Account oneAccountOnly = [SELECT Name,Phone FROM Account LIMIT 1];

but that doesn't explain how my colleague's code can work.

Is the fact that this construct works a bug, or intentional behaviour? If it's intentional, is it documented anywhere, what's it for, and how does Apex decide how to split the results of the SOQL query into the multiple sub-Lists that my colleague is looping over?

Answer 1

This is a documented behavior; the syntax is mentioned briefly in the docs on For Loops and more detail on how it behaves is given in the docs on SOQL For Loops. It looks weird, but it works.

When the platform detects a SOQL for loop like this, the underlying platform uses a feature called a QueryLocator, which uses the underlying cursor feature in the Oracle database.

In some regards it is a hold over. At one point in time there was a maximum List size in Apex governor limit. The coding construct helped developers work around it.

Later the List size governor limit was removed, but this feature remains.

Initially upon removal of the list size governor limit, many developers concluded that this feature was unnecessary. However I know of developers who did performance profiling and found that in larger queries the QueryLocator behavior had performance benefits over just iterating over all of the records.

It's also useful for avoiding hitting the heap size governor limit, and for that reason Salesforce recommend using this syntax for queries that return many results:

SOQL for loops differ from standard SOQL statements because of the method they use to retrieve sObjects. ... SOQL for loops retrieve all sObjects, using efficient chunking with calls to the query and queryMore methods of the SOAP API. Developers should always use a SOQL for loop to process query results that return many records, to avoid the limit on heap size.

When iterating, the outer for loop uses a maximum batch size of 200.

Answer 2

Please see the SOQL For Loops article. The construct with the list variable executes in a - potentially - few batches. I would say that it's some kind of exception to Apex grammar, which might seem a bit inconsistent, but is very convenient syntactic sugar.