Batch Apex & First error: Attempt to de-reference a null object !

I stumbled upon a strange Batch Apex issue today. My batch job was going well in few SFDC orgs; after deploying the same to a new org it stopped working completely. I just see, “First error: Attempt to de-reference a null object” in debug and apex logs.  After adding a lot of debug statements in both start() and execute() method, I found that “execute(Database.BatchableContext BC, List<Sobject> scope)” is never called after creating QueryLocator in start().

So, finally after searching on dev forums found this solution; it says don’t query NULL columns/fields in initial SOQL i.e. the soql string given to QueryLocator in start(). This really fixed my problem.

So to put it all together in a clean way. We usually have batch jobs written in following manner

global class MyCoolBatch implements Database.Batchable<Sobject>{
     
  global Database.QueryLocator start(Database.BatchableContext BC){
    String query = 'Select X, Y, Z From Contact WHERE ...';
    Database.Querylocator qr = Database.getQueryLocator(query);
    return qr;
  }
  
  global void execute(Database.BatchableContext BC, List<Sobject> scope){
    // process the records in scope, as per biz logic
  }
  
  global void finish(Database.BatchableContext BC){}
}

Here we are querying fields X, Y and Z from Contact; in case any of X,Y or Z are NULL in any records, you will face the “First error: Attempt to de-reference a null object” issue. Solutions to this problem are

1. Add null checks in SOQL, for fields that could go NULL for ex. X != null. This might not be possible, as this will for sure change the records returned from the SOQL.
2. Don’t query the fields that could possibly be NULL in start() method, re-query those in “execute(Database.BatchableContext BC, List<Sobject> scope)” method. This should be safer, as execute() will always be working on smaller subset of records for ex. 200 records.

I used the later option i.e. don’t query NULLABLE fields in start() & re-query those fields in execute() method, this fixed the problem !