
Filing this issue to track a question/comment that was raised by @ywangd  here https://github.com/elastic/elasticsearch/pull/98309#discussion_r1294115440

One question I do have is why we chose to [execute the LookupRequest with the enrich origin](https://github.com/elastic/elasticsearch/blob/2e4440ff1c0decf79f8a40688566411f7d518aa7/x-pack/plugin/esql/src/main/java/org/elasticsearch/xpack/esql/enrich/EnrichLookupService.java#L141C2-L141C2). It means the following query works when the user has access to only the playlist index without access to either the songs enrich policy or its associated index.

```
FROM playlist | ENRICH songs ON song_id
```

It feels a bit weird to me that we have no control on who can use what enrich process. That said, we don't have a good story around enrich and ingest pipeline either, i.e. we have no control over who can use what pipeline where a pipeline can contain an enrich processor. There have been quite a few complaints from users and we have an https://github.com/elastic/elasticsearch/issues/57586 for it. I wonder whether we should not repeat the problem here with ES|QL. On the surface, it might be easier to tackle compared to ingest pipelines since ES|QL is read-only and has no "bulk" operations. What do you think?
x-pack/plugin/esql/src/main/java/org/elasticsearch/xpack/esql/optimizer/LogicalPlanOptimizer.java
if (pushable.size() > 0) {
                if (nonPushable.size() > 0) {
                    Filter pushed = new Filter(filter.source(), unary.child(), Predicates.combineAnd(pushable));
                    plan = filter.with(unary.replaceChild(pushed), Predicates.combineAnd(nonPushable));
                } else {
                    plan = unary.replaceChild(filter.with(unary.child(), filter.condition()));
                }
            } else {
                plan = filter;
            }
            return plan;
        }
    }
    /**
     * Pushes Evals past OrderBys. Although it seems arbitrary whether the OrderBy or the Eval is executed first,
     * this transformation ensures that OrderBys only separated by an eval can be combined by PushDownAndCombineOrderBy.
     *
     * E.g.:
     *
     * ... | sort a | eval x = b + 1 | sort x
     *
     * becomes
     *
     * ... | eval x = b + 1 | sort a | sort x
     *
     * Ordering the evals before the orderBys has the advantage that it's always possible to order the plans like this.
     * E.g., in the example above it would not be possible to put the eval after the two orderBys.
     */
    protected static class PushDownEval extends OptimizerRules.OptimizerRule<Eval> {