Summary
Some queries fail during planning because of a defect in the query planning algorithm which causes an infinite loop. The user is presented with the error message "Job was canceled because the query is too complex. Please simplify the query." To troubleshoot this problem, check the application logs using the job ID for a longer message with an error stack, and compare this to the one shown below. If they are very similar, a session-level support key, planner.enhanced_filter_join_pushdown, can be disabled to get the query to succeed. The defect is fixed in Dremio Software 24.3.1, 24.3.4 and 25.0.0
Reported Issue
After making a relatively minor change to a query's SQL it fails during query planning with the error "Job was canceled because the query is too complex. Please simplify the query."
The complete error stack for the failed job can be found in the Dremio application logs (server.log) for the coordinator(s):
[190c8ca8-bfd7-f563-6f4c-5368aecf9700/0:foreman-planning] INFO c.d.exec.planner.MaxNodesListener - User Error Occurred [ErrorId: e80f90ed-a9bf-4b1d-a7ce-761fd5ecc1f9]
com.dremio.common.exceptions.UserException: Job was canceled because the query is too complex. Please simplify the query.
at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:926)
at com.dremio.exec.planner.MaxNodesListener.relEquivalenceFound(MaxNodesListener.java:43)
at org.apache.calcite.plan.MulticastRelOptListener.relEquivalenceFound(MulticastRelOptListener.java:54)
at org.apache.calcite.plan.AbstractRelOptPlanner.notifyEquivalence(AbstractRelOptPlanner.java:398)
at org.apache.calcite.plan.hep.HepPlanner.updateVertex(HepPlanner.java:910)
at org.apache.calcite.plan.hep.HepPlanner.addRelToGraph(HepPlanner.java:837)
at org.apache.calcite.plan.hep.HepPlanner.applyTransformationResults(HepPlanner.java:745)
at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:563)
at org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:419)
at org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:284)
at org.apache.calcite.plan.hep.HepInstruction$RuleCollection.execute(HepInstruction.java:74)
at org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:215)
at org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:202)
at com.dremio.exec.planner.DremioHepPlanner.findBestExp(DremioHepPlanner.java:75)
at com.dremio.exec.planner.sql.handlers.PlannerUtil.lambda$transform$0(PlannerUtil.java:147)
at com.dremio.exec.planner.sql.handlers.PlannerUtil.doTransform(PlannerUtil.java:204)
at com.dremio.exec.planner.sql.handlers.PlannerUtil.transform(PlannerUtil.java:193)
at com.dremio.exec.planner.sql.handlers.DrelTransformer.convertToDrel(DrelTransformer.java:141)
at com.dremio.exec.planner.sql.handlers.DrelTransformer.convertToDrel(DrelTransformer.java:102)
at com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan(NormalHandler.java:87)
at com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan(HandlerToExec.java:59)
at com.dremio.exec.work.foreman.AttemptManager.plan(AttemptManager.java:561)
at com.dremio.exec.work.foreman.AttemptManager.lambda$run$4(AttemptManager.java:458)
at com.dremio.service.commandpool.ReleasableBoundCommandPool.lambda$getWrappedCommand$3(ReleasableBoundCommandPool.java:140)
at com.dremio.service.commandpool.CommandWrapper.run(CommandWrapper.java:70)
at com.dremio.context.RequestContext.run(RequestContext.java:109)
at com.dremio.common.concurrent.ContextMigratingExecutorService.lambda$decorate$4(ContextMigratingExecutorService.java:227)
at com.dremio.common.concurrent.ContextMigratingExecutorService$ComparableRunnable.run(ContextMigratingExecutorService.java:207)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)Relevant Versions
The defect has been reported in Dremio Software 24.3.1, 24.3.4 and 25.0.0, but may effect more versions up to those with the fix identified below in "Steps to Resolve".
Troubleshooting Steps
If you encounter the error, identify the job ID and search the Dremio coordinator application logs for this ID. You should find an error message very similar to the one shown above. There may be differences in the code line numbers shown in the stack.
Cause
Despite the error message, the problem is not caused by an overly complex query. Rather, a bug was introduced while making an improvement to the way joins are planned, that causes an infinite loop in one of the query planning phases (DX-91532, DX-92283).
Steps to Resolve
The defect in the query planner has been fixed in the 24.3.8+, 25.0.5+, 25.1.0+ releases, but you can workaround the problem by setting a support key at the session level, before executing the query:
ALTER SESSION SET "planner.enhanced_filter_join_pushdown"=false;
In some instances of the problem, you may also need to enable a query planning "guardrail":
ALTER SESSION SET "planner.enhanced_filter_join_guardrail"=true;