SOQL query causes internal Salesforce error

Question

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?

Answer 1

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 only opp.AccountId.

This fails:

[
    SELECT Name
    FROM Account
    WHERE Id != '0014000000SQyVGAA1'
        AND Id != null
        AND (
            Id IN ('0014000000SQyVGAA1')
            OR ParentId IN ('0014000000SQyVGAA1')
            OR Parent.ParentId IN ('0014000000SQyVGAA1')
            OR Parent.Parent.ParentId IN ('0014000000SQyVGAA1')
            OR Parent.Parent.Parent.ParentId IN ('0014000000SQyVGAA1')
        )
    ORDER By Name
]

This succeeds:

[
    SELECT Name
    FROM Account
    WHERE Id != '0014000000SQyVGAA1'
        AND Id != null
        AND (
            ParentId IN ('0014000000SQyVGAA1')
            OR Parent.ParentId IN ('0014000000SQyVGAA1')
            OR Parent.Parent.ParentId IN ('0014000000SQyVGAA1')
            OR Parent.Parent.Parent.ParentId IN ('0014000000SQyVGAA1')
        )
    ORDER By Name
]

And this also succeeds:

[
    SELECT Name
    FROM Account
    WHERE Id != '0014000000SQyVGAA1'
        AND Id != null
        AND (
            Id IN ('0014000000VqDVWAA3','0014000000SQyVGAA1')
            OR ParentId IN ('0014000000VqDVWAA3','0014000000SQyVGAA1')
            OR Parent.ParentId IN ('0014000000VqDVWAA3','0014000000SQyVGAA1')
            OR Parent.Parent.ParentId IN ('0014000000VqDVWAA3','0014000000SQyVGAA1')
            OR Parent.Parent.Parent.ParentId IN ('0014000000VqDVWAA3','0014000000SQyVGAA1')
        )
    ORDER By Name
]

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:

[
    SELECT Name
    FROM Account
    WHERE Id != '0014000000SQyVGAA1'
        AND Id != null
        AND (
            Id IN ('0014000000SQyVGAA1')
            OR Parent.ParentId IN ('0014000000SQyVGAA1')
        )
    ORDER By Name
]

But this fails:

[
    SELECT Name
    FROM Account
    WHERE Id != '0014000000SQyVGAA1'
        AND Id != null
        AND (
            Id IN ('0014000000SQyVGAA1')
            OR ParentId IN ('0014000000SQyVGAA1')
            OR Parent.ParentId IN ('0014000000SQyVGAA1')
        )
    ORDER By Name
]

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.

Answer 2

Anecdotally, This query runs fine (via the developer consoles query editor) on one of my sandboxes:

SELECT Name, ID
FROM Account
WHERE Id != ''
    AND Id != ''
    AND (
        Id IN ('')
        OR ParentId IN ('')
        OR Parent.ParentId IN ('')
        OR Parent.Parent.ParentId IN ('')
        OR Parent.Parent.Parent.ParentId IN ('')
    )
ORDER By Name

Using static id lists also works fine:

SELECT Name, ID
FROM Account
WHERE Id != '001U000100qh0xJIAQ'
    AND Id != '001U000100qh0xJIAQ'
    AND (
        Id IN ('001U000000ASKzzIAH')
        OR ParentId IN ('001U000000ASKzzIAH')
        OR Parent.ParentId IN ('001U000000ASKzzIAH')
        OR Parent.Parent.ParentId IN ('001U000000ASKzzIAH')
        OR Parent.Parent.Parent.ParentId IN ('001U000000ASKzzIAH')
    )
ORDER By Name

Heres some code I used in the developer consoles execute anonymous code window:

Id someId = '001U000100qh0xJIAQ';
Id someOtherId = '001U000700qh0xJIAQ';

Set<Id> accountIds = new Map<Id, Account>([SELECT Id FROM Account LIMIT 1000]).keySet();

System.debug([
    SELECT Name
    FROM Account
    WHERE Id != :someId
        AND Id != :someOtherId
        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
]);

Even trying to look for 1000 items in 4 separate clauses I can't repo your error.

---

Heres the results of running some queries on my org:

SELECT Name
FROM Account
WHERE Id != '0014000000SQyVGAA1'
    AND Id != null
    AND (
        Id IN ('0014000000SQyVGAA1')
    )
ORDER By Name

No Results, but no crash.

SELECT Name
FROM Account
WHERE Id != '0014000000SQyVGAA1'
    AND Id != null
    AND (
        Id IN ('0014000000SQyVGAA1')
        OR Parent.Parent.ParentId IN ('0014000000SQyVGAA1')
        OR Parent.Parent.Parent.ParentId IN ('0014000000SQyVGAA1')
    )
ORDER By Name

[object Object]: An unexpected error occurred. Please include this ErrorId if you contact support: 1038235189-47553 (730406045)

SELECT Name
FROM Account
WHERE Id != '0014000000SQyVGAA1'
    AND Id != null
    AND (
        Id IN ('0014000000SQyVGAA1')
        OR Parent.Parent.Parent.ParentId IN ('0014000000SQyVGAA1')
    )
ORDER By Name

No results

SELECT Name
FROM Account
WHERE Id != '0014000000SQyVGAA1'
    AND Id != null
    AND (
        Id IN ('0014000000SQyVGAA1')
        OR Parent.Parent.ParentId IN ('0014000000SQyVGAA1')
    )
ORDER By Name

No results.

I have no idea why exactly it fails but it seems like adding that 3rd clause to the AND causes the failure. If it was just the ID != '' && Id == '' issue, I'd expect the first one to fail.