ESQL: MV_EXPAND not pushed down past Project prevents optimizations #119074
Description
Example with LOOKUP JOIN:
FROM test
| LOOKUP JOIN lookup_index ON first_name
| MV_EXPAND foo
| KEEP foo
After surrogate plan substitution, we have the following:
[2024-12-19T08:52:47,070][INFO ][o.e.x.e.o.LogicalPlanOptimizer] [testLookupJoinFieldLoading] Rule logical.SubstituteSurrogatePlans applied
Limit[1000[INTEGER]]
\_EsqlProject[[foo{r}#141]]
\_MvExpand[foo{f}#138,foo{r}#141,null]
\_Project[[_meta_field{f}#132, emp_no{f}#126, first_name{f}#127, gender{f}#128, hire_date{f}#133, job{f}#134, job.raw{f}
#135, languages{f}#129, last_name{f}#130, long_noidx{f}#136, salary{f}#131, bar{f}#137, baz{f}#139, foo{f}#138]]
\_Join[LEFT,[first_name{f}#127],[first_name{f}#127],[first_name{f}#140]]
|_EsRelation[test][_meta_field{f}#132, emp_no{f}#126, first_name{f}#12..]
\_EsRelation[lookup_index][LOOKUP][bar{f}#137, baz{f}#139, first_name{f}#140, foo{f}#1..]
A Project was inserted downstream from the Join - but it's mostly redundant, because the EsqlProject at the top tells us that we only need the foo field.
Now, because we don't push down the MV_EXPAND, the EsqlProject and Project never meet - otherwise they would be combined via CombineProjections. As a result, we have to load all the fields from the lookup index, because they are all required in the Project downstream from it.