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.