ESQL: No rows with JOIN/ENRICH/SAMPLE + EVAL + KEEP

[ivancea]

Weird interaction resulting in no rows:

Correct:

FROM languages
| DROP language_code
| EVAL language_code = 3
| LOOKUP JOIN languages_lookup ON language_code

# Results:
 language_code | language_name 
---------------+---------------
3              |Spanish
3              |Spanish
3              |Spanish
3              |Spanish

Wrong results:

FROM languages
| DROP language_code
| EVAL language_code = 3
| LOOKUP JOIN languages_lookup ON language_code
| KEEP language_code

# Results:
 language_code 
---------------

You can remove the first DROP if the original index doesn't have the JOIN key column

[elasticsearchmachine]

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

[ivancea]

After some investigation, it's happening because of fieldNames() returning fields from the lookup to be searched through field-caps, which return no concrete indices for them.

The same is also happening with ENRICH, also in 8.17.

Also, chaining any of those commands may render a similar result: They will add "their own fields" to the field_caps

[astefan]

The actual problem seems to be, as @ivancea pointed out offline, the premature elimination of an EsRelation from the logical nodes tree which is removed with PruneEmptyPlans thus making the query a LocalRelation one - in other words, not reaching ES at all.

There is a set of three major use cases here, that are covered by the same set of code:

• from employees | keep height | drop height | eval x = 1 | stats c=count(x), mi=min(x), s=sum(x) this eliminates all the columns from employees thus indicating there is no need for employees to be queried, but the follow up eval creates a new column and runs some stats on it. This correctly returns 0 and null.
• from employees | drop languages | eval languages = 123 | keep languages this tells the field_caps call we make to only look at the field languages, gets the information for it, creates an EsRelation[languages] then drops the languages from the logical plan, creates another column with the same name and keeps only that. This use case is not covered now in our code, like we cover the stats use case above. This logical plan ends up incorrectly as a LocalRelation.
• the same as the previous use case: from employees | drop height | eval x = 1 | stats c=count(*), count(x). This one shouldn't return 0, but it does. It should return as the number of rows in employees.

The last two use cases have something in common: they just need a count of documents from ES, they don't need the actual rows.
This has been identified as such some time ago in this issue. The first use case is in the same ballpark: it doesn't need the rows of employees, and it's setting the number of rows to 0 because all the columns (and implicitly all the rows) have been eliminated.