function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
TomlToml 

Getting 50001 row limit using CUBE and limit 1000

I have the below query in a class that is the controller for a page.  The query is executed using Database.query(). There is only 15578 quotes in the database. I have also included below the lines from the error log. Why is thsi happing and how do I go aboutl correcting it. Also I am able to run thsi query with no problem in the developer console.

Thanks
Tom

Query
"select day_only(createdDate) createdonDate, count(createdDate) numCreated, sum(TotalPrice) amount, status,  grouping(status) grpStatus  FROM Quote WHERE ContactId != null  group by cube(day_only(createdDate), status) order by day_only(createdDate)  desc limit 1000" 

Error log
14:42:47.097 (97511178)|SYSTEM_METHOD_ENTRY|[177]|Database.query(String)
14:42:47.098 (98616275)|SOQL_EXECUTE_BEGIN|[177]|Aggregations:0|  select day_only(createdDate) createdonDate, count(createdDate) numCreated, sum(TotalPrice) amount, status,  grouping(status) grpStatus  FROM Quote WHERE ContactId != null  group by cube         (day_only(createdDate), status)   order by  day_only(createdDate)  desc limit 1000 
14:42:47.275 (275728038)|SOQL_EXECUTE_END|[177]|Rows:1000
14:42:47.275 (275768494)|EXCEPTION_THROWN|[177]|System.LimitException: Too many query rows: 50001
Best Answer chosen by Toml
pconpcon
The problem is that the aggregate functions do not just occur once.  You have three aggregate functions plus how ever many "rows" the cube method consumes.  Also, the duplicate aggregate functions you are using in the group by and order by parts of the query.

If you run this query in the Developer console's "Execute Anonymous" window and view the log, how many rows are you using there?  Remember, the governor limits for rows is cumulative across the lifetime of the transaction not just a single query.

All Answers

pconpcon
I've not done anything with the group by cube functionality before, but my guess is that you are hitting the same limits that apply to other Aggreate functions [1]
 
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.
 

Because of this, it takes in ALL of your Quotes where ContactId != null (probably more than 5000 rows) and and after it does all of your aggregations (if it hadn't have hit the 5000 limit) it would then limit those results down to 1000 rows.  To not hit this limit, you will need to add additional criteria to your WHERE clause

[1] http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm
TomlToml
Thanks pcon but there are only 15619 quotes in our database, and this same SOQL runs without exception in the developer console with and without the LIMIT clause. You mention 5000 limit but the maximum number of rows returned by SOQL is 50,000. Am I millsing something?

Tom
pconpcon
The problem is that the aggregate functions do not just occur once.  You have three aggregate functions plus how ever many "rows" the cube method consumes.  Also, the duplicate aggregate functions you are using in the group by and order by parts of the query.

If you run this query in the Developer console's "Execute Anonymous" window and view the log, how many rows are you using there?  Remember, the governor limits for rows is cumulative across the lifetime of the transaction not just a single query.
This was selected as the best answer
TomlToml
The "Execute Anonymous" execution gives me the 50001 error. I will do as you suggest and add criteria to the query.

Thanks