Lookup index validity error message for ES|QL could be improved

[astefan]

Description

Given the following lookup index

    "settings": {
        "index": {
            "mode": "lookup"
        }
    },
    "mappings": {
        "properties": {
            "id1": {
                "type": "integer"
            }
    }

and a query FROM employees | LOOKUP JOIN test1 ON languages | KEEP emp_no, languages the error message presented invalid [test1] resolution in lookup mode to [0] indices:

• is slightly misleading: the index test1 DOES exist and the confusion comes from [0] indices message which imo it suggests that there are no test1 indices. If I use lookup join whatever and whatever index doesn't exist the error message is Unknown index [whatever]
• doesn't say what is the actual problem: languages field doesn't exist in index test1.
• if test1 has index mode set as logsdb or no specific index mode, the same error message is presented, which doesn't say much about the real problem.

Actually, we have some sort of validation that refers to missing fields.
For query FROM employees | LOOKUP JOIN test1 on id1 we get Unknown column [id1] in left side of join.
For query FROM employees | LOOKUP JOIN test1 ON id1*`` we get 1:39: Unknown column [id1*] in left side of join\nline 1:39: Unknown column [id1*] in right side of join

[elasticsearchmachine]

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

[hye-on]

Hi @astefan Can I work on this task ?:)

[latiffurkandemir]

hi @astefan can I work on the task?

[alsaeedd]

Hey @astefan! I opened a PR that addresses this issue. Please check it out over here.🙏

[JihwanByun]

Hi @astefan I have submitted a PR to address this issue: here.
It would be great if you could take a look. Thanks!

[alex-spies]

Heya, I removed the good first issue label - I think I misjudged this. A correct solution may have to consider how field caps calls are made for lookup joins to determine what lookup indices and fields are available, which is subtle and also can't be tested in isolation, without an integration test.