[SalesForce] SOQL query with Dataloader using List Literal in WHERE Clause

Using inline binding circumvents the normal character restrictions of an SOQL statement; as long as you don't exceed the heap size limit, you can include as many values as you like in the list. Here's an execute anonymous script that will query for 161,448 (non-existent) unique Id values:

id[] idlist = new id[0];
String[] keys = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'.split('');
for(integer a = 0; a < 42; a++) {
    for(integer b = 0; b < 62; b++) {
        for(integer c = 0; c < 62; c++) {
            idlist.add('001000000000'+keys[a]+keys[b]+keys[c]);
        }
    }
}
// Following query will run fine with 161k Id values
Account[] accounts1 = [select id from account where id = :idlist];
// As will this one
Account[] accounts2 = database.query('select id from account where id = :idlist');

This code essentially generates a query that would require 3,500,000 SOQL characters (minimum), yet doesn't run into any particular limits.

Eventually, depending on what you're doing, you'll run into either heap limits, maximum payload size limits, etc.

When practical, sort your results by Id or CreatedDate, and use that as a simple filter instead. You can read more about doing that in this 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.