One of my users reported today that they were unable to display a page for a specific ‘company’. Upon investigation, the error was ‘Invalid Query Locator’ and was in a place in the code where such an error was unexpected.
Here was the cause and solution
Foo_c is the parent object,
Bar__c is the child.
Foo__c f = [select id, (select id, name, fld1__c, fld2__c, fld3__c from Bars__r order by name asc) from Foo__c where id = :someId]; for (Bar__c b: f.bars__r) res.add(new MyInnerClass(b));
After the 60th
Bar__c, the code threw an exception ‘Invalid Query Locator’.
So, why did this happen?
- I knew from experience that the record in question had more than 60 children and that I had successfully viewed them in the past.
- So, it must have been something introduced
- I verified via SOQL in Workbench that there were in fact 70 children – and I could retrieve them all in Workbench
I noted that others had this issue and even SFDC Help had a specific knowledge article on this subject although it didn’t seem to apply to my use case as I was neither taking too long nor had any intermediate queries whilst processing the list of children
I recalled that I had added to the subquery some additional fields. And, those fields were of type
Text Area (long). In this case, 32768 long even though most rows had less than 25 characters and in the use case in question, those fields were all
null. I also noted that the class was written a long time ago and was at V16.0.
Looking for the easy way out, I tried updating the class version to V33.0. This had no effect.
Then, I tried to see if the field length of the Text Area (long) fields made a difference, even if there was no data that came anywhere close to the limits. So, I reduced the field sizes down to 4096 from 32768. This had some effect as I could process and display all 70 children without error. However, when I changed the test example to have 200 children, the
Invalid Query Selector error recurred. Clearly, there is some calculation by SFDC as to when it will return the
queryMore attribute based on the expected size of the SOQL result payload.
I rewrote the code to the following by eliminating the subquery:
Foo__c f = [select id from Foo__c where id = :someId]; for (Bar__c b: [select id, name, fld1__c, fld2__c, fld3__c from Bars__r where foo__c =: f.id order by name asc) res.add(new MyInnerClass(b));
Now there is no subquery and when tested with 2000 children, there were no errors. Of course, it took two queries to resolve instead of one but as this was a VF controller doing only those queries, it was an easy penalty to accept.