Formula field sometimes evaluate on null records?

Question

So here's a puzzler... I've created a minimal example in a dev org to help demo this.

Created a formula field on Account noURL__c for which the formula is simply: ISBLANK(Website).

Now let's evaluate the number of records retrieved by 3 different SOQL queries:

SELECT id, Parent.id, noURL__c, Parent.noURL__c 
FROM Account
WHERE noURL__c = true

8 results.

SELECT id, Parent.id, noURL__c, Parent.noURL__c 
FROM Account
WHERE Parent.noURL__c = true

1 result.

SELECT id, Parent.id, noURL__c, Parent.noURL__c 
FROM Account
WHERE noURL__c = true OR Parent.noURL__c = true

18 results?!?!? This is of course nonsensical. The number of results for A OR B cannot be greater than the sum of the number of results for A + for B. So what's going on?

When I examine those 18 I get 8 results where the noURL__c is true, and 1 result where the parent's noURL__c is true. The remaining 9 results are records that noURL__c = false and have no parent. Huh?!?

Now let's try one more thing. Modify the formula for noURL__c to: !ISBLANK(ID) && ISBLANK(Website). Refresh the 3rd SOQL query... boom. Only 9 results now.

It would seem that in certain SOQL WHERE clauses, Salesforce is evaluating formula fields for null related records. The parent is null, which means all its fields are null, which means that the parent's ISBLANK(Website) evaluates to true. It's not actually returning as true in the query result, but the WHERE selectivity is behaving as such. When I add the !ISBLANK(ID), it now evaluates false for null parents.

Surely this has to be a bug?

Answer 1

This seems like a bug, because the behavior is not consistent across operators. I created two Account formulas, ID_Null__c and Always_False__c, to test this (defined as ID = null and false, respectively):

select ID from Account where Always_False__c = true or Parent.ID_Null__c = true - returns Accounts with null parent

select ID from Account where Always_False__c = false and Parent.ID_Null__c = true - returns no Accounts

I've tried swapping their order and adding other fields, and the behavior seems to be that if the parent lookup part of the where clause is part of an and expression first, it will return false for that expression - probably a short-circuit optimization from the optimizer. On the other hand, it seems to evaluate the formula against null fields if the lookup relationship is part of an or expression.

Answer 2

This is not a bug.

First of all, Boolean can never be null, it will return false.

Secondly, both the conditions are not same

• (WHERE ParentId != null AND Parent.noURL__c = true)

• (WHERE Parent.noURL__c = true)

In the first WHERE condition it checks existence of Parent record and then actual field values. Here both the conditions to be satisfied to return the result.

In the 2nd WHERE condition if parent is blank, eventually Parent.noURL__c is null and here returns as expected.

If I try to visualize the logic with small apex snippet, it is exactly behaving like this.

if(obj != null && obj.no_url__c == true)
{
    System.debug('no_url__c is true');
}
else
{
    System.debug('no_url__c is false');
}

Now, the else condition satisfies either of obj == null OR obj.no_url__c == false

Update based on comments

Following conditions respective returns results

• WHERE noURL__c = true
• WHERE Parent.noURL__c = true

But, when you club the conditions like this:

• WHERE noURL__c = true OR Parent.noURL__c = true

It will always satisfy all the conditions. So results will include following values:

• noURL__c = true and Parent.noURL__c = false
• noURL__c = false and Parent.noURL__c = true
• noURL__c = true and Parent.noURL__c = true

Hope, it makes sense!

Answer 3

My Case worked its way up to R&D and it's now been added as a Known Issue.

https://success.salesforce.com/issues_view?id=a1p3A000000mCGDQA2

Please, everyone, vote on this issue! Thanks.