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 ?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
3
  • 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 Commented Aug 27, 2019 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. Commented Aug 27, 2019 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. Commented Oct 1, 2019 at 6:50

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.