ESQL: Improve Enrich handling in mapper/remote enrich planning

[costin]

Description

Follow- up to #114665 - right now the mapper has two different sections one after the other handling Enrich mode with coordinator setting, with two different styles: the first works on physical plan, the other on logical plan - both need to be consolidated and reworked
See #114665 (review)

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[alex-spies]

We needed to disable the physical consistency check in case of remote enrich, see #118531.

[alex-spies]

The current remote enrich planning seems to work around the fact that once we have a limit/topn upstream of the enrich, it will stay there and the enrich will never be planned on the data node because limit/topn is a pipeline breaker. Then we do some heavy handed plan manipulation to make a copy of the limit/topn downstream of the remote enrich, and we force the fragment to include the remote enrich.

@smalyshev , before you start going after this, let's get in touch. We have correct ways of pushing things down the plan which we should try and use to make the remote enrich hack go away. I think the crux may be that we try and push down limits past enriches, but for remote enriches we actually want to reverse this and try to push down the enrich as far as possible.

The remote enrich planning seems to implicitly push down the enrich past pipeline breakers, sidestepping the normal planning process of pipeline breakers in the process. While implicitly pushing down, it also runs into the same problems with shadowing that our optimizer rules already deal with correctly, and creates inconsistent plans, like here.

[alex-spies]

We've run into the buggy remote enrich planning one more time in #129745 (comment). The places where we need to work around the remote enrich planning are piling up, so there's definitive value in fixing this.