14

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

13
  • 2
    I am not experiencing this, that seems odd. Jun 22, 2016 at 11:38
  • 2
    Can you add screen shot of it with debug log? Jun 22, 2016 at 11:46
  • 3
    adding a new required=true field onto an existing database arguably calls for a data migration exercise to populate the pre-existing records with some non-null value
    – cropredy
    Jun 22, 2016 at 13:30
  • 1
    Sounds like a platform bug to me . Jun 22, 2016 at 14:25
  • 1
    May be this is issue in query optimization algorithm which does not considered this scenario. I have seen slimier issue with query not returning proper data due to corrupted index. I would you to log a case with SFDC support. Jun 22, 2016 at 15:39

3 Answers 3

4

Salesforce support confirmed this behaviour and they are willing to fix it. Therefore they asked me to create the following Idea. Please promote. Thank you!

https://success.salesforce.com/ideaView?id=0873A000000lEPMQA2

A while ago someone posted a workaround below that issue:

[SELECT Id FROM MySObject__c WHERE RequiredLookup__r.Id = null]
2
  • 1
    Strange to me this would require an idea to get enough up-votes to get fixed. Seems like it is a defect, not a change or new functionality.
    – Phil Rymek
    Sep 22, 2016 at 18:38
  • @PhilRymek totally agree!
    – itsmebasti
    Sep 23, 2016 at 8:59
3

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.

1

Please refer to sfdcfox's answer to this question:

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.

You must log in to answer this question.

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