12

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.

Improve this question
3
  • There is a 10,000 rows limit. How many results do you get without any LIMIT or OFFSET clauses? 390?
    – David Gillen
    Commented Aug 12, 2012 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
    Commented Aug 12, 2012 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
    Commented Aug 12, 2012 at 18:06

4 Answers 4

Reset to default
11

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.

Improve this answer
4
  • To back this up, I've used a LIMIT 30000 before and it worked.
    – ca_peterson
    Commented Aug 12, 2012 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
    Commented Aug 12, 2012 at 20:03
  • 1
    Sounds like a SF bug.
    – Peter Knolle
    Commented Aug 12, 2012 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
    Commented Sep 25, 2012 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?

Improve this answer
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
    Commented Aug 12, 2012 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
    Commented Aug 12, 2012 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
    Commented Aug 12, 2012 at 18:57
3

As of 08/2021, it appears that the Salesforce API validates the limit with int8 datatype. The largest possible number for that is 2,147,483,647.

Any number larger than that will return a Malformed Request Error:

Response content: [{'message': ' FROM LIMIT 10000000000\n ^\nERROR at Row:1:Column:5929\nInvalid Integer: 10000000000', 'errorCode': 'MALFORMED_QUERY'}]"

Improve this answer
4
  • Side note: it would make more sense for validation to use uint8 since limits will never include negative numbers...
    – ybressler_simon
    Commented Aug 27, 2021 at 18:47
  • 2
    you mean uint32, no? uint8 only goes to 255. Aside from that, I believe there's still a 50,000,000 actual row limit enforced by the API (e.g. Batchable can't process more than 50m rows at once).
    – sfdcfox ♦
    Commented Aug 27, 2021 at 19:06
  • Yes. I was incorrectly translating from bytes to bits. Whoopsies. @sfdcfox
    – ybressler_simon
    Commented Aug 27, 2021 at 19:38
  • And interesting. Would make sense that there's some layer of load balancing on top of the underlying database engine which validates & coerces queries. (LB likely coerces large numbers to 5M.)
    – ybressler_simon
    Commented Aug 27, 2021 at 19:40
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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .