12

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

Improve this question
13
  • 2
    I am not experiencing this, that seems odd. – Andy Hitchings Jun 22 '16 at 11:38
  • 2
    Can you add screen shot of it with debug log? – AtulRajguru9 Jun 22 '16 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 '16 at 13:30
  • 1
    Sounds like a platform bug to me . – Mohith Shrivastava Jun 22 '16 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. – AtulRajguru9 Jun 22 '16 at 15:39
2

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.

Improve this answer
2

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

Improve this answer
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 '16 at 18:38
  • @PhilRymek totally agree! – Basti Sep 23 '16 at 8:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.