I just figured out, that SOQL ignores null checks on required fields. It seems to expect that required fields can never be null. In my case it’s a new field, so a bunch of records actually have null values.
System.debug([SELECT Id FROM MySObject__c WHERE RequiredLookup__c = null]);does not give any results.
But as soon as I set the field to NOT required I get my results.
Any one else experienced this?
Any simple workaround?
I don’t want to query all records and do a manual check.
Edit:
Since some of you don’t belive it, I reproduced it with a lookup to Account, see screenshots with debugs:
1)
switch requirement off and run it again
2)
Answer
I was indeed able to replicate this, but it’s worth noting that this applies only to required relationship fields. That is, if you instead made a null filter against a new, required text field then the SOQL query would return the expected rows-I believe this is what led to the initial confusion.
As for a simple workaround you can create a new formula field with a checkbox return type that is simply set to ISBLANK(RequiredLookup__c) and then change your SOQL filter to return only records where this field is true.
Attribution
Source : Link , Question Author : Basti , Answer Author : Scott Covert