10

I have 535 Contact records in my Developer account. I am finding that the following query:

SELECT Id,FirstName,Lastname FROM Contact LIMIT 205 OFFSET 190

returns 200 records from Database.query(), while this query:

SELECT Id,FirstName,Lastname FROM Contact LIMIT 199 OFFSET 190

returns 199. I suspect there is an implicit 200 maximum on the value for LIMIT, but I can't find it documented anywhere, and no error is raised when I use these higher values.

3
  • There is a 10,000 rows limit. How many results do you get without any LIMIT or OFFSET clauses? 390? – David Gillen Aug 12 '12 at 16:05
  • I believe 10,000 is the maximum number of rows that can be returned in a SOQL query. My question is different: is there a maximum value of the LIMIT parameter in a query? It seems (from my experiment) that values greater than 200 are silently disregarded. Without LIMIT or OFFSET 535 rows are returned, as expected. – Jeff Trull Aug 12 '12 at 16:28
  • 1
    I just discovered that if I use OFFSET without LIMIT, the number of records returned is also limited to 200. I then tried the experiment of using LIMIT without OFFSET, and this time I got 205 records! So this issue appears to be related to the new OFFSET feature. – Jeff Trull Aug 12 '12 at 18:06
10

The LIMIT clause has no limit in and of itself. It's limited to the context in which it's used. If it's used in Apex code it's limited to the total governor limit for SOQL rows, which is currently 50,000. If it's used in a query via the Web Service API then there is no limit.

The issue you're running into appears solely to be an issue with using the OFFSET clause and LIMIT together.

4
  • To back this up, I've used a LIMIT 30000 before and it worked. – ca_peterson Aug 12 '12 at 19:14
  • This seems like the right answer. In fact, I can remove LIMIT entirely and I'll still get only 200 records - but only if I use OFFSET. This is not documented anywhere that I can see. – Jeff Trull Aug 12 '12 at 20:03
  • 1
    Sounds like a SF bug. – Peter Knolle Aug 12 '12 at 20:36
  • I seem to remember reading in the release notes that 200 was the max # that OFFSET could return, but I can't find this anywhere in the SOQL Reference docs. – zachelrath Sep 25 '12 at 13:56
5

http://www.salesforce.com/us/developer/docs/dbcom_soql_sosl/index_Left.htm#StartTopic=Content/sforce_api_calls_sosl_limit.htm?SearchType=Stem

The optional LIMIT clause allows you to specify the maximum number of rows returned in the text query, up to 200. If unspecified, then the default is 200, which is the largest number of rows that can be returned.

That link is for SOSL, but maybe the same applies for SOQL?

3
  • 1
    It's strange that they don't document this for SOQL (or flag an error when you exceed it) but I suspect you are right. – Jeff Trull Aug 12 '12 at 17:31
  • 1
    I'm surprised it is 200 because I've had the following error message when querying EntitySubscriptions as a non system administrator user which would lead me to believe that LIMIT's limit would be 1000 (at least), because in this case the system tells you to make sure it is no more than 1000: "System.QueryException: Implementation restriction: EntitySubscription only allows security evaluation for non-admin users when LIMIT is specified and at most 1000" – Peter Knolle Aug 12 '12 at 17:37
  • 1
    LIMIT is only limited to the total governor limit for SOQL rows, which is 50,000 currently if used within Apex. If you're using the web service API there is no limit for the LIMIT clause. – E.J. Wilburn Aug 12 '12 at 18:57
1

When using the offset clause the query will only return the first batch of records in the resultset at most. In this case, it looks like the batch size is 200. To get additional records, reexecute the query with a larger offset value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.