[CI] MixedClusterEsqlSpecIT test {enrich.DoubleRemoteEnrich ASYNC} failing

[elasticsearchmachine]

Build Scans:

• elasticsearch-periodic #9618 / 8.14.3_bwc
• elasticsearch-periodic #9590 / 8.14.3_bwc
• elasticsearch-periodic #9583 / 8.14.3_bwc
• elasticsearch-periodic #9569 / 8.14.3_bwc
• elasticsearch-periodic #9555 / 8.14.3_bwc
• elasticsearch-periodic #9548 / 8.14.3_bwc
• elasticsearch-periodic #9541 / 8.14.3_bwc
• elasticsearch-periodic #9527 / 8.14.3_bwc
• elasticsearch-periodic #9520 / 8.14.3_bwc
• elasticsearch-periodic #9499 / 8.14.3_bwc

Reproduction Line:

./gradlew ":x-pack:plugin:esql:qa:server:mixed-cluster:v8.14.3#javaRestTest" -Dtests.class="org.elasticsearch.xpack.esql.qa.mixed.MixedClusterEsqlSpecIT" -Dtests.method="test {enrich.CoordinatorEnrichAfterRemoteEnrich SYNC}" -Dtests.seed=8F9DE5212EB17470 -Dtests.bwc=true -Dtests.locale=kde-Latn-TZ -Dtests.timezone=Asia/Hong_Kong -Druntime.java=24

Applicable branches:
8.18

Reproduces locally?:
N/A

Failure History:
See dashboard

Failure Message:

org.elasticsearch.client.ResponseException: method [POST], host [http://[::1]:35333], URI [/_query?format=yaml&pretty=true&error_trace=true], status line [HTTP/1.1 500 Internal Server Error]
Warnings: [No limit defined, adding default limit of [1000]]
---
error:
  root_cause:
  - type: "illegal_state_exception"
    reason: "Found 1 problem\nline 2:3: Plan [EsqlProject[[message{f}#788, language_code{r}#782,\
      \ language_name{r}#791 AS first_language_name, language_name{r}#794]]] optimized\
      \ incorrectly due to missing references [language_name{r}#791]"
    stack_trace: "org.elasticsearch.ElasticsearchException$1: Found 1 problem\nline\
      \ 2:3: Plan [EsqlProject[[message{f}#788, language_code{r}#782, language_name{r}#791\
      \ AS first_language_name, language_name{r}#794]]] optimized incorrectly due\
      \ to missing references [language_name{r}#791]\n\tat org.elasticsearch.server@8.14.3/org.elasticsearch.ElasticsearchException.guessRootCauses(ElasticsearchException.
[truncated]

Issue Reasons:

• [8.18] 12 failures in test test {enrich.DoubleRemoteEnrich ASYNC} (4.9% fail rate in 244 executions)
• [8.18] 12 failures in step 8.14.3_bwc (57.1% fail rate in 21 executions)
• [8.18] 12 failures in pipeline elasticsearch-periodic (57.1% fail rate in 21 executions)

Note:
This issue was created using new test triage automation. Please report issues or feedback to es-delivery.

[elasticsearchmachine]

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

[alex-spies]

Closed by #132016