ESQL: LOOKUP JOIN should have SQL LEFT JOIN semantics

[alex-spies]

LOOKUP JOIN should handle multiple matching keys like SQL: create one output row for each matching document from the lookup index (instead of stuffing all matches into a single multi-value, preserving the rows from the left hand side).

This also requires a small tweak to PushDownAndCombineLimits.

[elasticsearchmachine]

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

[craigtaverner]

This affects the possible solutions we should consider for #118778

[nik9000]

• ESQL: Compute infrastruture for LEFT JOIN #118889
• ESQL: Make LOOKUP more left-joiny #119475
• Multivalued input fields ESQL: Ignore multivalued key columns in lookup index on JOIN #120726
• Restore ordinals optimization ESQL: LOOKUP JOIN should optimize queries against ordinals blocks #121811
• Fix LOOKUP JOIN fails with cryptic error when we DROP the fields looked up #118778

[alex-spies]

I think there's one more work item:

• Make PushDownAndCombineLimits correct for left-join semantics. ESQL: Fix LOOKUP JOIN with limit #120411

The solution is likely to still push down limits past left joins. (Otherwise, queries with SORTs will just fail because we cannot actually execute OrderExec - a limit has to be pushed down to be combined with an OrderExec into a TopNExec.) But to be correct, we need to keep a copy of the limit after the left join. I.e. the optimization rule needs to become

LOOKUP JOIN lu_idx ON lu_field | LIMIT 100
->
LIMIT 100 | LOOKUP JOIN lu_idx ON lu_field | LIMIT 100

but without ending up in an infinite loop when the optimization rule is run again later.

[alex-spies]

Got a reproducer for the broken limit pushdown on Nik's PR #119475:

nonUniqueRightKeyOnTheCoordinatorLateLimit
required_capability: join_lookup_v11

FROM employees
| SORT emp_no
| EVAL language_code = emp_no % 10
| LOOKUP JOIN languages_lookup_non_unique_key ON language_code
| KEEP emp_no, language_code, language_name, country
| LIMIT 4
| SORT country
;

emp_no:integer | language_code:integer | language_name:keyword | country:text
10001          | 1                     | English               | Canada
10001          | 1                     | null                  | United Kingdom
10001          | 1                     | English               | United States of America
10001          | 1                     | English               | null
;

On the PR, it incorrectly pushes down the LIMIT 4 (but keeps another LIMIT 4 at the top of the plan, which comes from the LIMIT 1000 that we insert automatically) and returns a result with 4 rows, some of which have language code 2.