ESQL: Make query planning/optimization aware of the version of nodes/clusters

[alex-spies]

Currently, ES|QL's query planner/optimizer can plan a query in exactly 1 way, and the resulting query plan has to work when sent to all data nodes involved in the query, that is, all other nodes in the cluster and all nodes from remote clusters (in case of CCS).

This is limiting when we'd like to plan or optimize a query plan in a specific way that will be incompatible with older nodes.

We've run into this problem a bunch of times already:

• We cannot fix the overflow of SUM aggregations because that requires changing the plan and would be breaking for old nodes: ESQL: Prevent overflow on SUM using multiple aggregators #116170 (comment)
• Optimizing for STATS |SORT | LIMIT could be solved by fusing them into a dedicated TopNAgg node, but old nodes wouldn't know what to do with this.
• Pushing down LOOKUP JOIN past Project had to settle for a sub-optimal approach because 8.18 nodes couldn't internally rename the fields added by LOOKUP JOIN.
• Not being able to gracefully turn on data types that used to be unsupported in the past due to bwc issues (ESQL: data types transitioning from unsupported to supported fail queries #135193)

Going forward, I expect the number of problems to increase, esp. when we introduce qualifiers - optimizations that do anything with qualifiers will just straight out not work at all with 8.19 nodes.

Let's solve this by making the planner/optimizer aware of the minimum version of the nodes involved in the query - both local and remote, to cover rolling upgrade and CCS scenarios.

[elasticsearchmachine]

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

[alex-spies]

Another thing we can't do right now that this change would enable: we cannot change the layout of operators (which columns they produce and what data they populate them with)! If we want to, say, change the intermediate state of an aggregation, we're out of luck.

[alex-spies]

As pointed out by @julian-elastic , we need to also take into account the versions of lookup nodes that we send requests to in case of LOOKUP JOIN.

[alex-spies]

When a query cannot run, or cannot use some fields due to being unsupported only on some remote nodes, we probably want to warn the user that some old nodes are holding them back and we cannot run the query as if only new nodes were involved.

[alex-spies]

We also probably need to think of a way to determine that a query requires a minimum version to run, at all, and rather than defaulting to an older query plan version - and failing the whole query - we rather want to exclude remote nodes that are just too old altogether, and treat them as partial failures.

Edit: @nik9000 correctly pointed out that manually excluding remotes that are on older versions is likely sufficient for now. Users can just send a query that doesn't require old remote clusters to run. I agree.