Apex Code Development You need to sign in to do that
Don't have an account?
gpervukhin SOQL parser problem?
Hi, everybody. I want to share my thoughts with you about the problem that I met with SOQL parser.
Here is the query that I tried to execute:
database.query('SELECT Id FROM Task WHERE ((AccountId IN (SELECT Id FROM Account WHERE Name like \'%test%\')) AND (ReminderDateTime > 2013-03-01T00:00:00Z)) AND (Status != \'Completed\')');
as a result I've got the following exception:
System.QueryException: Semi join sub-selects are only allowed at the top level WHERE expressions and not in nested WHERE expressions.
Let's analyze the query:
C1 = AccountId IN (SELECT Id FROM Account WHERE Name like \'%test%\')
C2 = ReminderDateTime > 2013-03-01T00:00:00Z
C3 = Status != \'Completed\'
Result:
... Where ((C1) AND (C2)) AND (C3)
Let's do another similar test. Everything works, if I reorganize the query in this way:
database.query('SELECT Id FROM Task WHERE (AccountId IN (SELECT Id FROM Account WHERE Name like \'%test%\')) AND (ReminderDateTime > 2013-03-01T00:00:00Z) AND (Status != \'Completed\')');
Analysis:
... Where (C1) AND (C2) AND (C3)
So, I can't see a real problem in the first query.
p.s.1: I do not exclude that this could be my error.
p.s.2: I will be very grateful for your answers, but please do not suggest to remove brackets, because this query is generated in code, and please suggest me to reorganize the query only if it's really incorrect.
The SOQL layer doesn't optimize the query; it leaves that for the SQL optimizer. So, nested parenthesis creates nested filters. It doesn't matter that SQL will optimize away the extra layers, because the parser will prevent it from ever reaching the database layer. That prevents a class of confusing conditions, such as ((X) AND (Y)) AND (Z) works but ((X) OR (Y)) AND Z doesn't (you can't remove the nesting and get the same execution path assumption). Therefore, remove the nesting unless you need it. They are not technically the same, because the SOQL parser is not a query optimizer.
sfdcfox, thank you for your reply.
It's very strange that SOQL parser doesn't optimize the query.
I run the above query and i got the same error.
By looking at the error, i just removed extra round brackets after where clause and the query gets executed.
database.query('SELECT Id FROM Task WHERE (AccountId IN (SELECT Id FROM Account WHERE Name like \'%test%\')) AND (ReminderDateTime > 2013-03-01T00:00:00Z) AND (Status != \'Completed\' )');
In any case, thank you for interest in this rather old thread.