The Setup

This is a "fun" and, to my knowledge, undocumented SOQL feature:

If you add an OFFSET clause to your SOQL query, Salesforce appears to add the Id field to your ORDER BY clause.

According to the OFFSET clause documentation:

We recommend using an ORDER BY clause when you use OFFSET to ensure that the result set ordering is consistent. The row order of a result set that does not have an ORDER BY clause has a stable ordering, however the ordering key is subject to change and should not be relied on.

I am following this advice, but I noticed today that the initial query on one of our pages was getting records back in a different order than subsequent pages, causing some records to be completely omitted from our results. I traced it back to a phantom ORDER BY Id that Salesforce appears to be tacking on to my query. Look at the following examples with and without an OFFSET clause:

Query without an OFFSET returns records in arbitrary order

Query with OFFSET 0 returns record ordered by Id

The ORDER BY Industry clause is applied consistently, but there appears to be an invisible, additional , Id tacked on when I add the OFFSET 0.

When the "page" size is very large, say 1,000 records, and the ORDER BY is on a field with a high number of duplicates, such as a picklist field, and we aren't using OFFSET on the first query, we end up "losing" a LOT of records.

Notice that the lost records are not the result of adding or removing records, thus changing the rows which are "skipped", which the documentation warns about. Rather, the issue is that Salesforce is actually changing the sort order, however slightly, which the documentation does NOT warn us about.

The Question

I'm looking for some sort of confirmation that this is, indeed, expected and that I can plan on this for the future. Basically, if I expect that I'll need to do paging, I MUST include an OFFSET 0 clause in the first query otherwise my user will "lose" data. Is this correct? Have I interpreted these results accurately?

Your Answer

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

Browse other questions tagged or ask your own question.