ESQL: LOOKUP followups

[nik9000]

Description

Because LOOKUP is super experimental we're going to merge without doing a few things. These are all things we're ok doing after merging, but want to do before too long

TODO next:

• [x] Fix variable shadowing and add (csv) tests for all relevant edge cases. https://github.com/elastic/elasticsearch/issues/109392 #109807
• [x] Fix Join.references() by either moving the join condition and match fields out of the JoinConfig into Join, or by overriding Join.references() directly. https://github.com/elastic/elasticsearch/pull/109989
• [x] Move mapTable to LocalRelation to the PreAnalyzer phase (https://github.com/elastic/elasticsearch/pull/107987/files#r1587059151) https://github.com/elastic/elasticsearch/issues/109541 https://github.com/elastic/elasticsearch/pull/110097 Turns out it's not worth it at the moment. That change would have made more trouble than improvements.
• [ ] Allow expressions over ON fields (https://github.com/elastic/elasticsearch/pull/107987/files#r1626767875). https://github.com/elastic/elasticsearch/issues/109389
• [ ] Figure out what to do about this comment. https://github.com/elastic/elasticsearch/issues/109541
• [ ] https://github.com/elastic/elasticsearch/issues/111398

TODO soon:

• [ ] Fix push down across LOOKUP so we don't end up preventing TopNs forming from Limits + Sorts; c.f. Optimizer rules for Grok/Dissect/Eval (and to some extent Enrich)

TODO before we drop experimental:

• [x] Settle on the format for the tables parameter https://github.com/elastic/elasticsearch/pull/109489

TODO eventually:

• [ ] LogicalPlanOptimizer.PruneColumns and other optimizer rules that are applied to Eval/Grok/Dissect/Enrich but not yet Lookup
• [ ] Optimize away LOOKUP when we prune all of the columns added by it

[elasticsearchmachine]

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

[alex-spies]

There's a bug with optimization rules looping. Good find @dgieselaar !

POST _query
{
  "query": """
    ROW id = [ "a", "b" ] 
    | LOOKUP joins ON id
    | MV_EXPAND id
  """,
  "tables": {
    "joins": {
      "id": {
        "keyword": [ "a", "b"]
      },
      "join": {
        "keyword": [ "c", "d" ]
      }
    }
  }
}

This results in

Rule execution limit [100] reached