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?