Known Issue in DAG: Using a query with AND-OR combination for parallelization is resulting in an error

Issue Description

On running a query, for a job that is to be run in parallel, that has an AND-OR combination in the WHERE clause, the system is displaying the following error: "unexpected token: AND (clcommon)."

Workaround

You need to add parentheses around the condition, such as an OR condition, in the WHERE clause of the query.

Example

Let us understand the issue and the workaround with the help of an example.

Let's say we have the following query created in a job that is to be run in parallel:

SELECT column1 FROM Table where Condition1 OR Condition2

where Condition1 and Conditon2 itself can have many conditions in it, such as the following:

Condition1: column2 = 1
Condition2: column3 = 5

When this query is passed to DAG, DAG will find the field on which this job is going to run in parallel. Let us say that it finds the field, loan__job_thread_count__c, on which the job is going to run parallely. It then adds this in the query as follows:

SELECT column1 FROM table WHERE column2 = 1 OR column3 = 5 AND loan__job_thread_count__c = 1

As observed in the preceding query, the WHERE clause of this query has both the AND and OR conditions due to which the compiler is not able to process, and the system throws an error.

Thus, the AND condition added at the end results in an error being thrown by the system.

To fix this, the OR condition of the WHERE clause is put in parenthesis as follows:

SELECT column1 FROM table WHERE (column2 = 1 OR column3 = 5) AND loan__job_thread_count__c= 1

Even after this fix, the system threw an error for a specific job that had a more complicated WHERE clause with an inner subquery such as the following:

SELECT column1
FROM table
WHERE 
    (column2 = 1 OR 
    column3 = 5 AND 
    column4 in (SELECT column5 from table2)
    ) 
    AND 
    loan__job_thread_count__c= 1

Salesforce limits the usage of a nested query to one level only. But the preceding query seems like a nested query with two levels (a query within a query) due to the parenthesis that was introduced as a fix. However, if you remove the parenthesis, you would encounter the original exception. Hence, this remains as an issue in the system.

So, as a workaround, you need to manually add the parenthesis when there is a condition such as an OR in the WHERE clause as follows:

SELECT column1 FROM table WHERE (column2 = 1 OR column3 = 5)