LOOKUP JOIN fails with cryptic error when we DROP the fields looked up

[craigtaverner]

A query that uses KEEP (or DROP) to drop the fields that are looked up will fail with a runtime error during LOOKUP.

ROW left = "left", client_ip = "172.21.0.5", right = "right"
| LOOKUP JOIN clientips_lookup ON client_ip
| KEEP left, client_ip, right

Failed with:

path: /_query, params: {format=txt, error_trace=true}, status: 500 java.lang.ArrayIndexOutOfBoundsException: Index -1 out of bounds for length 0
        at org.elasticsearch.compute.operator.lookup.MergePositionsOperator.<init>(MergePositionsOperator.java:76)
        at org.elasticsearch.xpack.esql.enrich.AbstractLookupService.doLookup(AbstractLookupService.java:329)
...

The problem is that the LOOKUP JOIN should return the env field, and dropping it causes the error, since all fields left after the KEEP actually come from the left index.

The current behaviour differs between main and the latest code in #118429.

• main uses fieldcaps * for lookup indices, and so will resolve to lookup env even if it is later dropped by the KEEP. This is wrong, since we do a lot of work which we then drop
• https://github.com/elastic/elasticsearch/pull/118429 instead does fieldcaps on what it sees the query as needing, which does not include any fields on the right. But instead of generating a validation error or erasing the LOOKUP JOIN entirely, it passes the lookup no fields down to the runtime, and that chokes with the cryptic error.

Both are incorrect.

Choices of behaviour:

• Throw a validation error for LOOKUP JOIN that has output only from the left index.
• Rewrite away LOOKUP JOIN if the JOIN does not actually achieve anything.
• Allow LOOKUP JOIN on no output fields, but return as many rows as match the join key, but return no extra columns

This last option appears to be favoured as we move in the direction of returning increased cardinality instead of multivalue results. See #118781

[elasticsearchmachine]

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

[alex-spies]

In case we change the semantics of LOOKUP JOIN to create multiple rows in case of multiple matching join keys, pruning the LOOKUP JOIN altogether is actually wrong. (Super edge case, but still.)

Then we need to enable LOOKUP JOIN for the edge case of 0 looked up fields.

[alex-spies]

I believe that #119475 fixes the underlying problem by allowing lookups with 0 looked up fields.

If that's correct, then the remaining work here is:

• Check and re-enabled any csv tests disabled due to this bug.
• Remove this hack from PruneColumns.
• Add a couple other csv tests, esp. one that would run into the code path of the hack mentioned above.

[alex-spies]

I just ran an experiment on Nik's PR #119475 and it looks like it's indeed fixed there:

curl -u elastic-admin:elastic-password -H "Content-Type: application/json" "127.0.0.1:9200/languages_lookup" -XPUT -d '{
  "mappings": {
        "properties": {"language_code": {"type": "keyword"}, "language_name":{"type": "keyword"}}}, "settings": {"index": {"mode": "lookup", "number_of_shards": 1}}}'

curl -u elastic:password -HContent-Type:application/json 'localhost:9200/languages_lookup/_doc?refresh' -d'{"language_code": "1", "language_name": "English"}'

curl -u elastic:password -HContent-Type:application/json 'localhost:9200/languages_lookup/_doc?refresh' -d'{"language_code": "1", "language_name": "English2"}'

curl -u elastic:password -H "Content-Type: application/json" "127.0.0.1:9200/_query?format=txt" -d ' 
{
  "query": "row language_code = \"1\" | lookup join languages_lookup on language_code | keep language_code"
}'

 language_code 
---------------
1              
1  

From the trace logs, I can see that the LookupOperator indeed doesn't load any fields:

Local execution plan:
DriverFactory(instances = 1, type = SINGLETON)
\_LocalSourceOperator[org.elasticsearch.xpack.esql.planner.LocalExecutionPlanner$$Lambda/0x00007e99dc6fb950@5b214b9d]
\_LimitOperator[limit = 1000]
\_LookupOperator[index=languages_lookup match_field=language_code load_fields=[] inputChannel=0]
\_ProjectOperator[projection = [0]]
\_OutputOperator[columns = [language_code]]