Weird limit exception retrieving 18 rows :?

Question

I'm repeatedly getting following known exception:

Too many query rows: 50001 
An unexpected error has occurred. Your development organization has been notified.

The thing here is that my query is retrieving only 18 rows, so my jaw fall to the floor instantly.

This is my query:

SELECT Date__c, Country__c FROM Insight2__c GROUP BY Date__c, Country__c ORDER BY Date__c ASC

This are the log main lines, check the 2nd line to verify there is only 18 rows retrieved:

16:24:46.0 (110288675)|SOQL_EXECUTE_BEGIN|[6]|Aggregations:0|SELECT Date__c, Country__c FROM Insight2__c GROUP BY Date__c, Country__c ORDER BY Date__c ASC NULLS FIRST
16:24:46.0 (380090845)|SOQL_EXECUTE_END|[6]|Rows:18
16:24:46.0 (380142615)|EXCEPTION_THROWN|[6]|System.LimitException: Too many query rows: 50001
16:24:46.0 (380413663)|HEAP_ALLOCATE|[6]|Bytes:30

This is the screenshot of the developer console query editor with the exact same query and the above mentioned 18 rows:

This is my code:

public static List<DateInfo> GetStruct() {      
    Map<String, DateInfo> temp = new Map<String, DateInfo>();

    List<AggregateResult> insights = [  SELECT 
                                            Date__c,
                                            Country__c
                                        FROM Insight2__c
                                        GROUP BY 
                                            Date__c,
                                            Country__c
                                        ORDER BY Date__c];

    for(AggregateResult inv: insights){
        Date d = (Date)inv.get('Date__c');
        String country = (String)inv.get('Country__c');

        if(temp.get(country) == null) {
            Set<String> months = new Set<String>();
            months = GetPeriodsByDate(d, months);

            DateInfo dInfo = new DateInfo(country);
            dInfo.Add(d.year(), months);

            temp.put(country, dInfo);
        }
        else {
            DateInfo dInfo = temp.get(country);

            Set<String> months = dInfo.GetInfo(d.year());

            if(months == null)
                months = new Set<String>();

            months = GetPeriodsByDate(d, months);

            dInfo.Add(d.year(), months);
        }
    }

    return temp.values();
}

Thanks in advance!

Answer 1

In aggregate queries, all of the records that produced the aggregate result count against you, even though only a few "rows" are actually returned. If you have more than 50,000 rows of data in your Insight2__c object, you'll get this exception. Also note that 50,000 rows is the total for the entire transaction. You can just as easily generate this error by doing something like this:

Contact[] contact1 = [SELECT Id FROM Contact LIMIT 49999];
Account[] account1 = [SELECT Id FROM Account LIMIT 2];

For example, I can run this code in my developer org that has almost no data:

AggregateResult[] results = [select ownerid from account group by ownerid];
System.debug(LoggingLevel.ERROR, results.size());

And the logs will show:

15:20:23:000 LIMIT_USAGE_FOR_NS   Number of SOQL queries: 1 out of 100
15:20:23:000 LIMIT_USAGE_FOR_NS   Number of query rows: 16 out of 50000

Even though the query returned these values:

15:20:23:009 USER_DEBUG [2]|ERROR|2

Answer 2

The total number of rows which were accessed on the number of AggregatedRows are returned so if 1 of your AggregatedRows touches more than 1 more you hit the limit

Queries that include aggregate functions are subject to the same governor limits as other SOQL queries for the total number of records returned. This limit includes any records included in the aggregation, not just the number of rows returned by the query. If you encounter this limit, you should add a condition to the WHERE clause to reduce the amount of records processed by the query.

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm