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
ORDER BY Industry clause is applied consistently, but there appears to be an invisible, additional
, Id tacked on when I add the
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.
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?