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:
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
SELECT id, Parent.id, noURL__c, Parent.noURL__c FROM Account WHERE Parent.noURL__c = true
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
!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?