Aggregate Query gives 50,001 row limit error, but works in Workbench. Why?

Question

I am using a SOQL aggregate query to sum various fields and group the results by business:

e.g.

SELECT Name, SUM(IsAccepted) accepted, SUM(IsMember) member
FROM Account
WHERE CreatedDate = LAST_MONTH
    AND (IsAccepted = 1 OR IsMember = 1)
GROUP BY Name

If I run this query in the Workbench application (or the Developer Console), I get a result back (pretty quickly) and yet if I use the query in a visualforce controller, I get a:

Too many query rows: 50001

exception.

How is Workbecnch and the Developer Console) able to return the query without error?

Answer 1

When you are using a Visualforce controller, the query is running in a Visualforce context, and so will be limited to 50,000 returned rows of data. Using the Query Editor or the Workbench, you are accessing the API directly. If you are not a system administrator, you will be subject to non-selectivity errors, but as a system administrator, you can query up to 50,000,000 rows at once. This means that the Query Editor and Workbench are not bound by the limits of a controller. You should also receive an error if you attempted to run your query in an Execute Anonymous window:

 System.Debug([SELECT Name, SUM(IsAccepted) accepted, SUM(IsMember) member
               FROM   Account
               WHERE  CreatedDate = LAST_MONTH
                 AND (IsAccepted = 1 OR IsMember = 1)
               GROUP BY Name]);

To summarize, the API does not have the same limits as Apex Code.