SOQL ignores : WHERE RequiredLookup__c = null

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)
screen1

switch requirement off and run it again

2)
screen2

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

Leave a Comment