ESQL: LookupJoin fails to push down limits #117698
Description
Running the basic csv test
basicOnTheCoordinator
required_capability: join_lookup_v2
FROM employees
| SORT emp_no
| LIMIT 3
| EVAL language_code = languages
| LOOKUP JOIN languages_lookup ON language_code
| KEEP emp_no, language_code, language_name
;
but removing the LIMIT 3 and turning the EVAL into more than a mere renaming
FROM employees
| SORT emp_no
| EVAL language_code = languages::keyword
| LOOKUP JOIN languages_lookup ON language_code
| KEEP emp_no, language_code, language_name
;
leads to a query where the OrderBy and the Limit are not combined into a TopN anymore - we end up with an OrderExec that cannot be planned:
error:
root_cause:
- type: "esql_illegal_argument_exception"
reason: "unknown physical plan node [OrderExec]"
stack_trace: "org.elasticsearch.xpack.esql.EsqlIllegalArgumentException: unknown\
\ physical plan node [OrderExec]\n\tat org.elasticsearch.xpack.esql.planner.LocalExecutionPlanner.plan(LocalExecutionPlanner.java:243)\n\
\tat org.elasticsearch.xpack.esql.planner.LocalExecutionPlanner.planLookupJoin(LocalExecutionPlanner.java:567)\n\
\tat org.elasticsearch.xpack.esql.planner.LocalExecutionPlanner.plan(LocalExecutionPlanner.java:234)\n\
\tat org.elasticsearch.xpack.esql.planner.LocalExecutionPlanner.planLimit(LocalExecutionPlanner.java:677)\n\
\tat org.elasticsearch.xpack.esql.planner.LocalExecutionPlanner.plan(LocalExecutionPlanner.java:212)\n\
\tat org.elasticsearch.xpack.esql.planner.LocalExecutionPlanner.planProject(LocalExecutionPlanner.java:634)\n\
\tat org.elasticsearch.xpack.esql.planner.LocalExecutionPlanner.plan(LocalExecutionPlanner.java:208)\n\
\tat org.elasticsearch.xpack.esql.planner.LocalExecutionPlanner.planOutput(LocalExecutionPlanner.java:281)\n\
\tat org.elasticsearch.xpack.esql.planner.LocalExecutionPlanner.plan(LocalExecutionPlanner.java:238)\n\
\tat org.elasticsearch.xpack.esql.planner.LocalExecutionPlanner.plan(LocalExecutionPlanner.java:179)\n\
\tat org.elasticsearch.xpack.esql.plugin.ComputeService.runCompute(ComputeService.java:464)\n\
\tat org.elasticsearch.xpack.esql.plugin.ComputeService.execute(ComputeService.java:222)\n\
...
The PushDownAndCombineLimits step fails to produce a TopN:
[2024-11-28T23:39:37,636][INFO ][o.e.x.e.o.LogicalPlanOptimizer] [test-cluster-0] Rule logical.PushDownAndCombineLimits applied
Limit[1000[INTEGER]] ! Project[[emp_no{f}#15, language_code{r}#4, language_name{f}#17]]
\_Project[[emp_no{f}#15, language_code{r}#4, language_name{f}#17]] ! \_Limit[1000[INTEGER]]
\_Join[LEFT,[language_code{r}#4],[language_code{r}#4],[language_code{f}#16]] = \_Join[LEFT,[language_code{r}#4],[language_code{r}#4],[language_code{f}#16]]
|_Eval[[TOSTRING(languages{f}#11) AS language_code]] = |_Eval[[TOSTRING(languages{f}#11) AS language_code]]
| \_OrderBy[[Order[emp_no{f}#15,ASC,LAST]]] = | \_OrderBy[[Order[emp_no{f}#15,ASC,LAST]]]
| \_EsRelation[employees][emp_no{f}#15, languages{f}#11, languages.byte{f}#12, ..] = | \_EsRelation[employees][emp_no{f}#15, languages{f}#11, languages.byte{f}#12, ..]
\_EsRelation[languages_lookup][LOOKUP][language_code{f}#16, language_name{f}#17] = \_EsRelation[languages_lookup][LOOKUP][language_code{f}#16, language_name{f}#17]
The same step does indeed succeed if there is no Eval upstream from the Join, which is why the csv test basicOnTheCoordinator succeeds.