Why does it say my SOQL for loop returned 50,001 records when it broke the loop after 20k?

Question

I have a SOQL for loop wherein the query sometimes returns more rows than the limit of 50,000. The code block executes once per record returned by the query, and in that code block is an if statement that breaks the loop if a certain value reaches 20,000. The log shows that the loop was broken during the 10,000th iteration, as I expected, but it also said that the query returned 50,001 rows.

I thought SOQL for loops query in batches of 200. Shouldn't it not have returned the rest of the rows yet? Shouldn't it only count the 10,000 (or 10,200 at the worst) against my limit?

Answer 1

Edit: Actually, the "Rows" line will show one more than the remaining limit available for the current transaction, but the actual value counted against you will be 200 more than when you break.

Here's demonstration code:

Integer counter = 0;
for(Account[] records: [SELECT Id FROM Account]) {
    counter += records.size();
    if(counter>=20000) {
        break;
    }
}
counter = 0;
for(Account[] records: [SELECT Id FROM Account]) {
    counter += records.size();
    if(counter>=20000) {
        break;
    }
}

Shuffling through the debug logs, I note that:

10:58:41:200 SOQL_EXECUTE_END [2]|Rows:50001
10:58:45:389 SOQL_EXECUTE_END [9]|Rows:29801

However, when I look at the cumulative usage section, I see that:

10:58:38:000 LIMIT_USAGE_FOR_NS   Number of query rows: 40400 out of 50000 ******* CLOSE TO LIMIT

I actually wasn't aware that this was a feature, so it's good to know.