4

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?

  • Can you clarify how many account records you are starting with and which ones have Website and ParentID fields populated? – David Cheng Aug 21 '17 at 3:47
  • In my own dev org where I try this: 8 Accounts have Website null and ParentId null. 9 Accounts have Website NOT null and ParentId null. 0 Accounts have Website null and ParentId NOT null. 3 Accounts have Website NOT null and ParentId NOT null. – Charles T Aug 21 '17 at 15:19
  • I have submitted a case to the Partner Forum, #17107803 – Charles T Aug 23 '17 at 18:55
2

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.

  • Thank you so much for the even simpler test case. That is very interesting to see. Any idea what would be the best way to flag Salesforce in on this? – Charles T Aug 21 '17 at 19:43
  • @CharlesT It looks like you have to log a case if it's not on the list of known issues. – IllusiveBrian Aug 21 '17 at 19:49
  • OK thanks. Yeah I tried adding in an AlwaysFalse field as well and I can see what you're seeing. WHERE alwaysfalse__c = true OR Parent.noURL__c = true yields 18 results but WHERE id = null OR Parent.noURL__c = true yields only 1, and WHERE alwaysfalse__c = false AND Parent.noURL__c = true yields only 1. – Charles T Aug 21 '17 at 19:58
0

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!

  • Santanu, I never used NULL in the SOQL query. What I'm reporting here is inconsistent logical behaviour of SOQL. The clauses WHERE noURL__c = true and WHERE Parent.noURL__c = true each separately retrieve result sets of 8 and 1 records respectively. When joined together as WHERE noURL__c = true OR Parent.noURL__c = true the result set should be the union of the first two queries. What I'm seeing is it returns additional results that were not part of either of the first two queries. How is that not a bug? – Charles T Aug 21 '17 at 15:24
  • check my updated answer with justifications – Santanu Boral Aug 21 '17 at 16:45
  • Santanu, when I combine the conditions I am getting results that do not satisfy either of the conditions. The extra 9 results it returns have noURL__c = false and no Parent. These are records that were not returned in either of the separate queries because they did not satisfy the conditions. – Charles T Aug 21 '17 at 17:28
  • individual query and combined query will always be different because of OR condition – Santanu Boral Aug 21 '17 at 17:41
  • Santanu, it is a fundamental feature of logic that if A is false and B is false, A OR B should also be false. If a record is not found by "WHERE A" and it is not found by "WHERE B", it must not be found by "WHERE A OR B". What I am experiencing is a breakdown of this fundamental logic. 9 records which are not returned by either of two WHERE clauses are returned when they are joined with an OR. There is no way of this to be anything but a bug. – Charles T Aug 21 '17 at 19:39
0

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.

  • 1
    In the known issue the soql is trying to compare a Boolean true to the text field id_null. It that correct? If so hard to vote for that since a text field will never equal the Boolean true – Eric Sep 1 '17 at 5:21
  • Yeah I noticed that. The rep paraphrased my instructions and mangled them a little bit. I have already asked the rep to fix it. I assume that if R&D determined it should be posted as a known issue, they must have been able to reproduce it. – Charles T Sep 1 '17 at 13:55
  • Apparently changing the text of the Known Issue requires the rep to contact a whole other team... sigh. Anyway it's a start. – Charles T Sep 5 '17 at 14:41
  • @Eric just to let you know, the wording on the Known Issue is now fixed. – Charles T Sep 7 '17 at 1:52

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.