Why am I *not* hitting a governor limit?

Question

I have an account that has 626,482 opportunities spread over 1,368 owners.

In the Developer Console if I run the following Apex

    List<SObject> recs = [select sum(amount), ownerid from opportunity group by ownerid];  

I get the error:

    System.LimitException: Too many query rows: 50001: AnonymousBlock: line 1, column 1

which I expect.

Still in the Developer Console if I now go to the Query Editor and run the SOQL

    select sum(amount), ownerid from opportunity group by ownerid

it runs and returns back the 1,368 records.

No governor limit hit.

In case the Developer Console has a special "I'm Salesforce" meaning if I run this Javascript on a VisualForce page

    function query() {
        jQuery.ajax('/services/data/v29.0/query/?q=' + encodeURIComponent('select sum(amount), ownerid from opportunity group by ownerid'), {
            type: 'GET',
            contentType: 'application/json',
            beforeSend: function(xhr) {
                // Set the OAuth header from the session ID
                xhr.setRequestHeader('Authorization', 'Bearer {!$Api.Session_ID}');
            },
            processData: false,
            dataType: 'json',
            success: function(response) {
                alert(response.records.length);
            },

            error: function(jqXHR, textStatus, errorThrown) {
              // Oops - what went wrong?
              alert(jqXHR.status + ': ' + errorThrown);
            }
         });
   }

it also works without hitting any governor limit. I'm obviously missing which governor limit is being applied. Can someone point it out?

Answer 1

The API isn't the same as Apex Code. In the API, you've a hard limit of 50,000,000 rows as an administrator, and up to approximately 1,000,000 rows as a non-administrator (because of sharing calculations, depending on the object and the object's row count). The query editor is an API call, not an Apex Code call, and not subject to the limits of Apex Code. Specifying read-only on an Apex Class grants a new limit of 1,000,000 rows, but prohibits DML operations. Additionally, specifying read-only on the Visualforce page grants higher loop/collection limits, but restricts DML operations. Coincidentally, you can call the REST API through Apex Code to skirt around these limitations, assuming the running user has API access. The developer console uses executeAnonymous for the Execute Apex Code window, but uses the normal REST API for the query editor, which explains the difference in behavior.

Answer 2

I think that the difference between the 3 query examples mentioned so far is that 2 of them are running in Read-Only context, and 1 is not:

Read Only Mode:

• REST API Query: /services/data/v29.0/query/?q=select+Id+from+Account
• Developer Console SOQL Query Editor: select sum(amount), ownerid from opportunity group by ownerid

NON-Read-Only Mode

• Anonymous Apex

The REST API Query and Developer Console Query Endpoints are being run in Read-Only mode, which makes sense because there is no possibility of, within the same server-side transaction, performing DML on the records returned by the queries. Granted, the REST API query does not return to you all of the records that match the query --- it provides a nextRecordsURL for you to go to instead to "get more" --- but the query itself is in "read-only mode".

Anonymous Apex, on the other hand, is never run in read-only mode, because there's the possibility of you having and running DML within the same Anonymous Apex transaction in which you perform SOQL. Therefore in your case, your SOQL query there is subject to the 50k records Governor Limit.

In Read-Only Mode, you're not subject to the 50k records limit --- you can run unrestricted queries against the database. Of course, based on the initiating context, you may not be able to actually interact with all records that match the query --- but your query "considers" all of these records.

What this means for your Aggregate query is this: in Anonymous Apex, your Aggregate Query hits the governor limit because there are more than 50k records for it to "consider" when performing the aggregation. Although your AggregateResult should produce 1,368 AggregateResult records, it's the 626,482 Opportunities that causes you to exceed the 50,000 record limit.

But from within a Read-Only / unrestricted query context, such as the Dev Console Query Editor, or a Visualforce Page with readonly=true, or a JavaScript Remoting method with the @ReadOnly annotation set, your Aggregate query can "consider" all 626,482 Opportunities, without hitting the 50k limit.