0

We have a public custom settings Batch_Settings__c of type Hierarchy (Organisation level) and it has a date/time field called "Last_Run__c".

The field Last_Run__c is null, but SOQL filtered on this date still returns data. We have this issue both when executing SOQL from Execute Anonymous script or from batch apex.

API version: 46

enter image description here

Refer to this sample script to illustrate this issue.

Batch_Settings__c settings =  Batch_Settings__c.getOrgDefaults();
system.debug('settings.Last_Run__c = '+ settings.Last_Run__c); //settings.Last_Run__c = null
List<Contact> contactList = [select id, name from contact 
                         where LastModifiedDate >= :settings.Last_Run__c limit 5];                        
system.debug('contactList.size= '+contactList.size()); //size=5
system.debug('contactList= '+contactList);

Can anyone explain why this is happening ?

1

The SOQL statement works well as for your expectation. The condition is not restrictive because if "Last Run" is null the condition "where LastModifiedDate >= :settings.Last_Run__c" is always true for each record. This behaviour is not documented anywhere (I searched a lot...).

What is well documented is the behaviour of the comparison operator agaist NULL value for DateTime data type in the Apex code as explained here and it works in the opposite way returning always false.

I suggest you to check the value of the "Last_Run__c" field before the SOQL statement and perform the expected behavior in case of Null value.

  • Thanks for your answer, but do you have any link to official SF documentation to support this explanation ? if you run this SOQL select id, name from Contact where LastModifiedDate >= null limit 5 in query editor (outside of apex) then it generates an error ERROR at Row:1:Column:36 invalid operator. so am wondering why different behavior from apex – Shamina Aug 27 '19 at 8:34
  • Thank you very much for your feedback, actually there is no documentation for the behavior of the "where" condition, posted by you, in a SOQL statement with binding variables. My opinion is that it's possible to compare a Datetime field with null value (with the >,<,>=,<= operators) only with binding variable also within an Apex transaction otherwise you receive a runtime error (as mentioned by you). Probably this is not the expected behavior but a not managed use case. – Fabio Palladino Aug 27 '19 at 13:01
  • I reached out to support and unfortunately I could not get a clear answer but they also informed that this behavior is not well documented. But based on your research this statement makes sense : "A non-null String or ID value is always greater than a null value" so this explains why the SOQL query returns result in apex. – Shamina Oct 1 '19 at 6:50

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.