ESQL: Lookup Join meta issue

[costin]

Description

Meta ticket around the main implementation topics required for lookup join

• [x] add grammar / parser LOOKUP JOIN
• [x] USING condition
  • [ ] rewrite into JOIN equi-join
  • [x] make ON field alias to USING field (syntax already used in enrich) - alternative is to deprecate this syntax
  • [ ] change index name grammar for identifier to source (to allow date math and others)
  • [ ] enhance the field matching grammar against various declarations such as parent.subfield & co.
• [ ] CSV tests (some suggestions to start with: https://github.com/elastic/elasticsearch/pull/116515#discussion_r1846386923)
• [ ] name qualifiers (for disambiguation) (look to descope)
• [ ] field resolution
  • [ ] establish shadowing rules
  • [ ] clarify in USING/ON pattern the rules around the join key which are following downstream (left vs right)
  • [ ] make the other side fields nullable (this changes the equality for existing fields causing side-effects) create new fields or update the entire field declaration.
  • [ ] check resolution exception to differentiate between 500 and 400 (https://github.com/elastic/elasticsearch/pull/116515#discussion_r1845526933)
• [ ] clarify output order rules in case of common keys (join keys and columns with same names) for consistency
  • SQL JOIN:
    • columns are kept in place and in lacking qualifiers, lead to ambiguation errors
    • JOIN USING moves the join keys in front followed by the columns in table declaration order
  • in ESQL the columns override each other:
    • in EVAL, the latest declaration wins alongside its position - all previous declarations are hidden as if they were removed
    • existing LOOKUP and INLINESTATS return left columns, join keys, right columns using EVAL rules for non-joining keys
    • STATS BY x, returns grouping keys last (declaration order)
  • proposal:
    • a. return join keys first (like SQL first)
    • b. return join keys last (declaration order, similar to STATS BY - clashes with USING)
  • c.f. output computation in Analyzer (https://github.com/elastic/elasticsearch/pull/116515#discussion_r1845526358)
• [ ] Look whether the LookupJoin output could be computed inside the class
• [ ] Resolve lookup table mappings - https://github.com/elastic/elasticsearch/pull/117246
• [ ] Update verifier and add corresponding tests https://github.com/elastic/elasticsearch/pull/116515#discussion_r1845504613.
  • the existing CSV tests are very basic (one specifically filters out all entries) and needs a lot more beefing

Compute

• [ ] Make field extractor work with LookupOperators (keep field extraction in one place)
• [ ] Bifurcate join operator/allow other operators between join and Lookup source (eval, filter, etc..)
• [ ] Align LookupJoin and LookupJoinExec - the first allows branching, the second does not.

Maybe

• [ ] obtain IndexMode inside IndexResolver (FieldCaps) Later
• [ ] refactor JoinConfig into general Expression

[elasticsearchmachine]

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