I've hit an unhandled exception when writing a query, and I'm curious if anyone here has seen the same thing. This is not a timeout issue – the error happens almost immediately, so it's not a query efficiency issue. The following query causes an "Internal Salesforce.com Error":
(Note that End_Client__c
is an Account lookup on Opportunity, and accountIds
is a Set<Id>
of parents of both Account and End Client)
[
SELECT Name
FROM Account
WHERE Id != :opp.AccountId
AND Id != :opp.End_Client__c
AND (
Id IN :accountIds
OR ParentId IN :accountIds
OR Parent.ParentId IN :accountIds
OR Parent.Parent.ParentId IN :accountIds
OR Parent.Parent.Parent.ParentId IN :accountIds
)
ORDER By Name
]
However, it is fixed if I reduce the number of items in the OR chain to 2, regardless of which items I select. This means the merge fields are not the issue causing the error. The following works successfully:
[
SELECT Name
FROM Account
WHERE Id != :opp.AccountId
AND Id != :opp.End_Client__c
AND (
OR Parent.Parent.ParentId IN :accountIds
OR Parent.Parent.Parent.ParentId IN :accountIds
)
ORDER By Name
]
I was fortunately able to find a workaround, which has left me even more confused. I changed the query to less efficient logic. The query below evaluates properly, which suggests to me that the query parser is failing.
[
SELECT Name
FROM Account
WHERE (Id != :opp.AccountId
AND Id != :opp.End_Client__c
AND Id IN :accountIds)
OR
(Id != :opp.AccountId
AND Id != :opp.End_Client__c
AND ParentId IN :accountIds)
OR
(Id != :opp.AccountId
AND Id != :opp.End_Client__c
AND Parent.ParentId IN :accountIds)
OR
(Id != :opp.AccountId
AND Id != :opp.End_Client__c
AND Parent.Parent.ParentId IN :accountIds)
OR
(Id != :opp.AccountId
AND Id != :opp.End_Client__c
AND Parent.Parent.Parent.ParentId IN :accountIds)
ORDER By Name
]
Can anyone shed some light on what is going on here?
Best Answer
After a bit of investigation with explicitly stated IDs, I think I found the unique issue that causes the error, and it can be handled more elegantly than my workaround.
Upon further investigation, the error is only happening when
accountIds
contains onlyopp.AccountId
.This fails:
This succeeds:
And this also succeeds:
So the it appears the parser has a hard time dealing with the contradictory inclusion statements. I can create a second set to eliminate the issue.
One last thing - this succeeds:
But this fails:
So it appears that in order to cause the error, there needs to be both the contradictory statement and at least 2 other inclusion statements within the OR.