6

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.

  • I'd expect to see this sort of behavior from the SOAP API query() and corresponding QueryResult. The result there includes a queryLocator to page through further results that weren't returned in the initial response. Can you try the same SOQL queries against the SOAP API to see if you get the same number of records back in the initial QueryResult? – Daniel Ballinger Jul 3 '17 at 21:27
  • You could also check the query plan being returned from each query in the developer console. – Daniel Ballinger Jul 3 '17 at 21:29
  • 1
    Another possible workaround, try the SOQL Query in a for loop as documented in Working with Very Large SOQL Queries. – Daniel Ballinger Jul 3 '17 at 21:31
  • Sure, I will check through SOAP API and post my findings. Using a for loop did solve the issue for now. Thanks Daniel – Santanu Halder Jul 4 '17 at 17:37
2

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.

0

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

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

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.