7

It appears that querying null values for required lookup fields always returns 0 results, even if some records have no value in the required field (e.g. records created before the field was made required).

Scenario: There exist 7 MyObj__c records that have a null MyParent__c lookup field.

BEFORE requiring MyParent__c:

The query SELECT Id FROM MyObj__c WHERE MyParent__c = NULL returns 7 results (Expected)

AFTER requiring MyParent__c:

The query SELECT Id FROM MyObj__c WHERE MyParent__c = NULL returns 0 results (Unexpected)

If I revert the field by unchecking Required for MyParent__c, the query goes back to returning the 7 expected rows.

Shall I submit this as a bug, or am I missing something?

2
  • FYI Formula Fields are your friend when trying to work around any shortcoming in SOQL. You could create a checkbox formula to evaluate when this required lookup is blank, and filtering on that would likely work.
    – Adrian Larson
    Commented May 19, 2020 at 22:06
  • 1
    Thanks, Adrian, that's a good general tip. I had other ways around in this case; just bummed about how I've been doing data quality queries wrong all these years, having assumed that null filters on required fields would help surface missing data AFTER making the field required... (e.g. making field required first, for go-forward data integrity, and afterwards turning eye toward "legacy" to query/analyze and fix) Commented May 19, 2020 at 22:21

1 Answer 1

8

This occurs because of an optimization during the "query planning" phase of a query. The query optimizer removes null values from filters on required fields. This optimization allows Salesforce to use standard indexes instead of a full table scan on those fields, which can improve performance by many orders of magnitude on large tables. If Salesforce fixed this "bug," it would drastically harm the performance of virtually every single customer everywhere, and cause non-selectivity errors unnecessarily in many large orgs, just to catch an edge case scenario. As an administrator, you must make sure that your data is up-to-date before you make a field required, or you will run in to this sort of problem.

2
  • 1
    Thanks, makes perfect sense! I was going to add "indexing" as a tag on this question for that very reason. Is this documented somewhere, or how did you determine this? I'm not able to see anything when examining the Query Plan for this simple query from the Dev Console Commented May 19, 2020 at 22:04
  • @Bow-chicawow-ers I remember raising a case for this many years ago. I don't know of a good document for it, but I do know it's by design.
    – sfdcfox
    Commented May 19, 2020 at 22:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .