function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
MiddhaMiddha 

System.Exception: retrieve id limit reached

I am trying to query records and getting error : System.Exception: retrieve id limit reached.

 

What does this mean and how can this be resolved?

 

Suggestions please? 

imuinoimuino
Theres is a limit of id's you can retrieve in each query call it is 2000. So you may need to limit the call or if you need to retrieve al the ids you have to make more than one call.
hemmhemm

WIth the API this is not a problem because the pattern is query...loop..queryMore...loop..queryMore...etc.

 

Apex doesn't have the concept of gathering things in batches (that I know of).  Shouldn't this be built into the platform so that we don't get this error? Or have some pattern that allows use to do the queryMore concept in Apex. Does something like that exist?  I am getting this error now too.

 

 

SuperfellSuperfell

The equivilent of query/queryMore in apex is

 

for (Account [] accs : [select id,.... from account]) {

// do something with this batch of accounts.

}

 

I think the original poster will need to post some code for anyone to give a more useful answer.

hemmhemm

Is this supposed to an API only error?  I am receiving this error in Apex now.  

 

I am using Dynamic Apex, but that should work like any other Apex. Right?

 

Simon, are you under the impression that this is an API-only issue?

hemmhemm

I did some research and see there are limits in Apex.  


In my tests, the Accounts table has 27000+ records in it.  I also created a custom object called Test__c and I have a lookup field on Accounts to it.  I added a record to Test__c for each Account and then linked the Account to the corresponding Test__c records via a lookup field.  Here are my results.

list <account> a = [select id from account]; - System.Exception: Too many query rows: 10001
list <account> a = [select id from account limit 10000]; - Success
list <account> a = [select id, test__r.name from account limit 10000]; - System.Exception: retrieve id limit reached
list <account> a = [select id, test__r.name from account limit 2001]; - System.Exception: retrieve id limit reached
list <account> a = [select id, test__r.name from account limit 2000]; - Success


 

  • list <account> a = [select id from account]; - System.Exception: Too many query rows: 10001
  • list <account> a = [select id from account limit 10000]; - Success
  • list <account> a = [select id, createdby.name from account limit 10000]; - Success
  • list <account> a = [select id, test__r.name from account limit 10000]; - System.Exception: retrieve id limit reached
  • list <account> a = [select id, test__r.name from account limit 2001]; - System.Exception: retrieve id limit reached
  • list <account> a = [select id, test__r.name from account limit 2000]; - Success
Conclusion
  • Apex has a hard limit of 10,000 rows to a query
  • If you join in a standard object (e.g. User), you still have 10,000 record limit
  • If you join in a custom object (e.g. Test__c), you now have a 2,000 record limit
Is this by design or a bug?

 

    Mike LeachMike Leach

    I think Simon's point is that iterating over a large query collection using a for loop is the Apex equivalent of API queryMore, so that batch size is automatically managed in the iterator.

    hemmhemm

    I hear ya.  

     

    My point is that with the API and query...queryMore, you could query 100,000 records all day long (barring timeouts on your web server).  Apex has introduced some hard limits that never existed before.  Additionally, the limits are different depending upon whether or not you are linking in a custom object (just discovered that part).

     

    Perhaps I hijacked this thread a bit and turned it into a new topic.  I didn't originally intend that.

    SuperfellSuperfell

    That's odd. I think the different between cases 3 & 4 are related to the number of different FK values you have, rather than custom vs standard objects.

     

    For cases 4 & 5, you don't see any errors running the same query via the Web Services API ?

    hemmhemm

     


    SimonF wrote:

    That's odd. I think the different between cases 3 & 4 are related to the number of different FK values you have, rather than custom vs standard objects.

     

    For cases 4 & 5, you don't see any errors running the same query via the Web Services API ?


     

    Ya know, I haven't tried it over the API.  My API comment was on past experience, but I am almost certain I've done really large queries over the API and it's been okay because the batch size was only 200.

     

    Out of this whole thread, the thing that gets me is the difference in limits when a custom object is added as a joined field and how the same does not occur when I join to a standard object.  That custom Test__c object only has the system fields (+ Name) on it.  It's as simple as a custom object gets.

     

    I do understand that governor limits exist in Apex, but I would hope that, at least, the query size limit would be 10,000 across the board and that the limit wouldn't go down to 2,000 in certain circumstances.  Obviously, no limit would be ideal.

     

    Not sure if it's a fair test, but using Eclipse to query, via the API I am able to do all those queries without error.

    SuperfellSuperfell

    This seems like it might be an apex bug, i'll talk to the apex folks.

    Ms. KnightMs. Knight

    I ran into this error today and was wondering if there was any news on when this will be fixed.

     

    Thanks for the update!

     

     

     

    Cheers,

    Jenn

     

    hemmhemm

    Simon, is this getting resolved soon (Summer 10 perhaps)?

    richardvanhook.ax712richardvanhook.ax712

    This issue is very discouraging not just because it's an issue, but also because the issue can't be caught as an Exception.  The following code snippet will reproduce the issue but astoundingly the try/catch is ignored!  Dude, not allowing developers to catch any and all exceptions is bad, bad, bad and it seems to be something I'm noticing more and more.  It's fracturing my confidence in the platform. 

     

    try{
        List<OpportunityLineItem> records = [select id,Opportunity.AccountID from OpportunityLineItem limit 10000];
        for(SObject record : records){
            record.get('id');
        }
    }catch(Exception e){
        //this catch is ignored!!!
        System.debug(e.getMessage());
    }

    Vivek ViswanathanVivek Viswanathan

    You can use

     

     

    for(XYZ x : [select id from XYZ ])

    {

     

     

    }

     

    or

    for(List<XYZ> x : [select id from XYZ])

    {

     

     

    }

    hemmhemm

    Any update on when Salesforce might be fixing this bug?

    Avatar_ReiterAvatar_Reiter

    I got this error too.    :-(

     

    Roth2842Roth2842

    Is there any word on if this is intended or accidental, and if the latter, if it will be fixed?

    znithchhunznithchhun

    Thx for your advice  :) with it i can keep my work going