I have a query that I'm using to export data:
SELECT c.Id, c.SystemModstamp, ...
FROM Contact c
WHERE c.SystemModstamp > start
AND c.SystemModstamp < end
AND c.Id > string
ORDER BY c.Id ASC
LIMIT 100000
…where 'start' and 'end' are valid timestamps and 'string' is an 18-digit Id. This query runs multiple times, each time setting the 'string' to the highest-value Id from the previous batch.
Most of the time this is fine. However, for a certain (large) slice of data, this query instead returned 99994 records with 0 errors. I know that it didn't simply reach the end of the filtered list, because when I run the next batch (with a new and higher 'string'), that query returns the full 100000.
What could be causing this? I've been running this query in Data Loader while researching, and the results are the same when using Export or Export All. I've been able to reproduce the 99994 records even with slightly-altered queries. It almost seems like there is somehow 'broken' data on this table that's not returned and doesn't throw an error.
Has anyone seen this before?
UPDATE: After a lot of trial-and-error, I've found that excluding all custom fields from this query returns the expected 100000 records. However, including even one custom field (I tested with a simple checkbox, Inactive__c) causes the query to return 99994 records instead. Unfortunately, I have no idea why that is or what it means.
UPDATE 2: I was able to compare the 100000 records with the 99994 records and found the 6 records that make up the difference. Despite being an Admin, I get the 'Insufficient Privileges' message when trying to access the records in UI. However, I can query them in Dev Console (SELECT Id FROM Contact WHERE Id='x'). What could be wrong with these six records that would cause this sort of behavior?
UPDATE 3: Neither of the posted answers match up with what I'm seeing, but since both have received a score of 2, the bounty will be awarded anyway. Unless a better answer comes along before the bounty expires, I'm abandoning this question.
Best Answer
From your update 2 , it's pretty obvious that those 6 records are ghost records and thus they won't be part of your export.
What are ghost records? Ghost records are corrupt records copied during Sandbox refresh for a partial copy or full copy sandbox. The records get corrupt when they are being edited during the sandbox copy process to clone them.
Have a look at this related knowledge article, its close .
Solution: Delete those 6 records using workbench and clone it back from production.
Edit: I found a link for this behaviour, you have to reach out to SF support for the fix.