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:

• Fix variable shadowing and add (csv) tests for all relevant edge cases. LOOKUP shouldn't duplicate the output if the same field was already present in the input #109392 ESQL: Fix LOOKUP attribute shadowing #109807
• Fix Join.references() by either moving the join condition and match fields out of the JoinConfig into Join, or by overriding Join.references() directly. ESQL: Fix Join references #109989
• Move mapTable to LocalRelation to the PreAnalyzer phase (https://github.com/elastic/elasticsearch/pull/107987/files#r1587059151) ESQL: Refactor Lookup to have the lookup tables modeled into a Relation in the pre-analysis stage #109541 ESQL: Resolve tables to LocalRelation centrally #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). LOOKUP ON expression #109389
• Figure out what to do about this comment. ESQL: Refactor Lookup to have the lookup tables modeled into a Relation in the pre-analysis stage #109541
• ESQL: double lookup failure #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:

• Settle on the format for the tables parameter ESQL: Improve syntax for LOOKUP tables #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