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
TehNrdTehNrd 

Spring 11 - Batch Apex - System.QueryException: Inline query has too many rows

I think the Spring '11 release changed the behavior of some batch apex classes depending on how queries are used. I am now getting the following exception in a batch apex class that I haven't touched in over a year:

 

System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop

 

From what I can tell this started happening after the Spring 11 update.

 

Here is the simple code to reproduce the issue:

 

global class BatchJob implements Database.Batchable<sObject>{

	public string query = 'select Id, Name, (Select Id from Contacts) from Account';

	global Database.QueryLocator start(Database.BatchableContext bc){
		return Database.getQueryLocator(query); 
	}
	
	global void execute(Database.BatchableContext bc, List<sObject> objects){
	 	List<Account> accts = new List<Account>();
	 	for(sObject s : objects){
	 		Account a = (Account)s;
	 		system.debug('Throw error...');
	 		system.debug(a.Contacts); //This will throw a 'silent' System.QueryException
	 	}
	}
	 
	global void finish(Database.BatchableContext bc){
		system.debug('All done.');	 
	}
}

And here is the test class:

 

@isTest
private class BatchBugTEST {

    static testMethod void myUnitTest() {
        Account acct = new Account(Name = 'test');
        insert acct;
        
	//create some contacts
	List<Contact> cons = new List<Contact>();
		
	Integer count = 0;
	for(Integer i = 0; i < 200; i++){
		cons.add(new Contact(AccountId = acct.Id, LastName = 'test' + i));
	}
	insert cons;
	system.debug(cons.size());
		
	//Setup batch job       
        BatchJob job = new BatchJob();
	job.query += ' where Id = \'' + acct.Id + '\'';
		 
	Test.startTest();
	Database.executeBatch(job);
	Test.stopTest();
    }
}

What is most concerning about this is the System.QueryException is "silent". What I mean by this is that the code keeps running as if no exception occurred. Usually when a system exception occurs everything comes to a screeching halt and errors are displayed as test failures on the results page. In the case the code continues and the only way to see the error is to locate it in the debug logs.

 

The only reason I caught this error was good unit tests with assertions. If I didn't have these I would have had no idea this class stopped working correctly. I will be submitting a support ticket but if anyone from salesforce can run the code above on a Winter 11 instance I would be interested what the results are.

 

-Jason

 

 

 

 

tmatthiesentmatthiesen

Jason,

 

Can you enable support access.  I can't seem to repro on my Spring '11 org.

 

thanks,

 

Taggart 

TehNrdTehNrd

Access granted. Case #: 04791001

TehNrdTehNrd

And just to reiterate the test will not fail but if you look at logs there is indeed a System.Exception occurring.

Bhawani SharmaBhawani Sharma

Yes I can also see the exception, but when I try he follwing code :

 

List<Account> listAccount = [select Id, Name, (Select Id from Contacts) from Account];
for(Account acct : listAccount)
System.debug(acct.Contacts);

 

in System log , It doesn't give me any error. Very Strange  .

 

Cory CowgillCory Cowgill

I have also come across this error with some of my Batch Apex and Relationship queries that had been working fine before last weekends update. I'm interested in hearing the resolution.

tmatthiesentmatthiesen

Support is looking at this - please stand by.

tmatthiesentmatthiesen

In Spring '11, some changes to QueryLocatorIterator started passing the job's batch size (default 200) to QueryLocator.querymore() rather than the fixed value 1000.  Code that worked provided the inner query returned 999 or fewer records now only works with 199 or fewer. This is why a default scope, where 200 records are returned, now hits an exception.  We have recorded a bug and working to release this in a coming patch.

TehNrdTehNrd

Thanks for the update.

 

prettynerdprettynerd

can anybody help me?? my batch apex does not execute even though there are only 3 records to be processed.. my code is a bit long and i somehow think that pasting the log would be enough.. but please let me know if i needed to paste my code..

 

03:22:07.077|CODE_UNIT_STARTED|[EXTERNAL]|01pR0000000ADDq|OpptySalesNotificationBatch
03:22:07.093|METHOD_ENTRY|[15]|BatchableContextImpl.BatchableContextImpl()
03:22:07.093|METHOD_EXIT|[15]|BatchableContextImpl
03:22:07.094|METHOD_ENTRY|[6]|System.debug(ANY)
03:22:07.094|USER_DEBUG|[6]|DEBUG|SELECT Amount, OwnerId FROM Opportunity WHERE (StageName = 'Closed Won Uninstalled') AND (RecordType.Name = 'Standard Sale' OR RecordType.Name = 'Br Admin Sale') AND (Account.RecordType.Name = 'TP Account Record Type') AND (Sale_Type__c = 'New Business' OR Sale_Type__c = 'Additions') AND (Type = 'Contract') AND (CloseDate = YESTERDAY) LIMIT 100
03:22:07.094|METHOD_EXIT|[6]|System.debug(ANY)
03:22:07.094|METHOD_ENTRY|[7]|Database.getQueryLocator(String)
03:22:07.095|SOQL_EXECUTE_BEGIN|[7]|Aggregations:0|SELECT Amount, OwnerId FROM Opportunity 
03:22:07.145|SOQL_EXECUTE_END|[7]|Rows:3
03:22:07.145|METHOD_EXIT|[7]|Database.getQueryLocator(String)03:22:08.021|CUMULATIVE_LIMIT_USAGE
03:22:08.021|LIMIT_USAGE_FOR_NS|(default)|
  Number of SOQL queries: 0 out of 200
  Number of query rows: 0 out of 50000
  Number of SOSL queries: 0 out of 20
  Number of DML statements: 0 out of 150
  Number of DML rows: 0 out of 10000
  Number of script statements: 2 out of 1000000

 

as compared to my other batch apex - which is working fine, the one above lacks the QueryLocatorIterator method as below:

 

11:45:48.825|CODE_UNIT_STARTED|[EXTERNAL]|01p80000000HjjS|UpdateOppPrimaryContactRoleJob
11:45:48.848|METHOD_ENTRY|[15]|BatchableContextImpl.BatchableContextImpl()
11:45:48.848|METHOD_EXIT|[15]|BatchableContextImpl
11:45:48.849|METHOD_ENTRY|[20]|Database.getQueryLocator(String)
11:45:48.850|SOQL_EXECUTE_BEGIN|[20]|Aggregations:0|SELECT IsPrimary, OpportunityId, Contact.Email FROM OpportunityContactRole 
11:45:48.969|SOQL_EXECUTE_END|[20]|Rows:50
11:45:48.969|METHOD_EXIT|[20]|Database.getQueryLocator(String)
11:45:48.986|METHOD_ENTRY|[7]|QueryLocatorIterator.QueryLocatorIterator()
11:45:48.986|METHOD_EXIT|[7]|QueryLocatorIterator
11:45:47.236|CUMULATIVE_LIMIT_USAGE
11:45:47.236|LIMIT_USAGE_FOR_NS|(default)|
  Number of SOQL queries: 0 out of 200
  Number of query rows: 0 out of 50000
  Number of SOSL queries: 0 out of 20

 

am i missing something?? i built the working piece November 2010 and the not working piece just today..

 

any help would be much appreciated.. ^_^

p1 force developerp1 force developer

I am also running into same issue.

 

I have a delete page which validates all the related records(child record) then deletes the child records first then the header record.

 

In the main query it has 6 sub queries.

 

When I try it with few records for all 6 child objects, It works fins. But when there are several records (e.g. 50+) then it is giving me invalid query locator error.

 

I am running it through a visuaforce page not a batch process.

 

does anyone got any ideas how to fix it.

 

I thought by summer'11 this issue must have fixed.

 

Thanks

 

prettynerdprettynerd

6 subqueries ?!?!?! :smileysurprised: hehe.. the solution to my problem needed me to use the Iterable method of the Batchable interface since I was building a batch class.. the reason you got that error is because you have a complex query that QueryLocator object can't handle.. there is an interface called Iterable so you might want to try to check on that.. hope im helping.. ^_^

pdopdo

Was this ever resolved?  I am still getting this in my production org, but not in the Sandbox...

stollmeyerastollmeyera

I don't think this was ever fixed.  I am getting the same error as @prettynerd and I am only updating two records.

pdopdo

Well, with the next update, I hope it is fixed, in the meantime I am limiting my query to 199.

stollmeyerastollmeyera

What's really weird about it is my queury is only running on three or so records when this error is throwing.  There were some other posts mentioning the same thing, but no resolution ever came.  I guess we can only cross our fingers for the Winter release, or whenever the next bug patch is...

homershomers

This seems to be still an issue in Winter '12

irlrobinsirlrobins

Yes, I'm seeing the same behaviour in my batch job in Winter 12. I'm trying to see if passing a score parameter to Database.executeBatch will make a difference.

prettynerdprettynerd

Hi,

 

Try using:

 

    global Iterable<sObject> start (Database.BatchableContext bc){
    	return Database.query(query);
    }

 

 instead of:

 

    global Database.Querylocator start (Database.BatchableContext bc){
        return Database.getQuerylocator(query);
    }

 

this worked for me.. ^_^

Kumar Vikash 9Kumar Vikash 9
Wow.. an 8-year-old solution finally got me through. This issue literally gave me a headache that too on a Friday evening. Thanks a bunch, @prettynerd. Now I can enjoy my Friday night beer in peace.