OFFSET with long text area field - undocumented?

Question

I have a custom object with 4 long text fields, all with max length 131,072 characters.

I have more than 200 records in my org of type CustomObject__c. Now,

Scenario 1:

[Select LongText1__c, LongText2__c, LongText3__c, LongText4__c 
from CustomObject__c limit 200 offset 0]

This query in Apex returns me around 30 records.

Scenario 2:

I change the max limit of all the long text fields to 32000 characters.

[Select LongText1__c, LongText2__c, LongText3__c, LongText4__c 
from CustomObject__c limit 200 offset 0]

This query in Apex returns me around 125 records.

Scenario 3: I remove one field from the query:

[Select LongText1__c, LongText2__c, LongText3__c 
from CustomObject__c limit 200 offset 0]

This query in Apex returns me around 175 records.

Scenario 4:

Removing one more field from the query:

[Select LongText1__c, LongText2__c 
from CustomObject__c limit 200 offset 0]

This query in Apex returns me around 200 records.

Scenario 5:

This is crucial one, if I remove the offset clause from the query with ALL four long text fields with max 131,072 characters,

[Select LongText1__c, LongText2__c, LongText3__c, LongText4__c 
from CustomObject__c limit 200]

This returns all 200 records.

So I assume there is character limit on the returned rows for the long text area fields. I could not find anything documented about this behavior in the Apex guide.

I would like to get some more light on this behavior from the experts.

Answer 1

As per my comments, one possible work around is to use the SOQL query in for loop as documented in Working with Very Large SOQL Queries.

E.g.

for (CustomObject__c a : [Select LongText1__c, LongText2__c, LongText3__c, LongText4__c 
                  from CustomObject__c limit 200 offset 0]) {
    // Your code without DML statements here
}

I suspect it is something to do with the QueryResult paging that you would typically see with the SOAP API query() method. Of course, I could be way off. And it doesn't explain why removing the offset returns all 200 expected results.

Answer 2

it worked when you run the soql in a for loop.

For(objApi obj : [your soql here]){
   //logic here
}