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

[elasticsearchmachine]

Build Scans:

• elasticsearch-periodic #9499 / 8.14.3_bwc
• elasticsearch-periodic #9492 / 8.14.3_bwc
• elasticsearch-periodic #9478 / 8.14.3_bwc
• elasticsearch-periodic #9457 / 8.14.3_bwc
• elasticsearch-periodic #9450 / 8.14.3_bwc
• elasticsearch-periodic #9443 / 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.DoubleRemoteEnrich ASYNC}" -Dtests.seed=8689DED79E6A7E88 -Dtests.bwc=true -Dtests.locale=raj-Deva-IN -Dtests.timezone=Europe/Saratov -Druntime.java=24

Applicable branches:
8.18

Reproduces locally?:
N/A

Failure History:
See dashboard

Failure Message:

org.elasticsearch.client.ResponseException: method [GET], host [http://[::1]:45153], URI [/_query/async/FnZPT0dnNlQyUUxXOXRjVHF3QllDVHcceVN5Tk11UklRc2VoenJxaE9QNjAydzozOTY1MA==?wait_for_completion_timeout=60s&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øú�typeVillegal_state_exception�reasonàFound 1 problem
line 2:3: Plan [EsqlProject[[message{f}#10213, language_code{r}#10207, language_name{r}#10216 AS first_language_name, language_name{r}#102
19]]] optimized incorrectly due to missing references [language_name{r}#10216]ü�stack_traceäorg.elasticsearch.ElasticsearchException$1: Found 1 problem
line 2:3: Plan [EsqlProject[[message{f}#10213, language_code{r}#10207, language_name{r}#10216 AS first_language_name, language_name{r}#102
19]]] optimized incorrectly due to missing references [language_name{r}#10216]
	at org.elasticsearch.server@8.14.3/org.elasticsearch.Elasticse
[truncated]

Issue Reasons:

• [8.18] 2 consecutive failures in step 8.14.3_bwc
• [8.18] 6 failures in test test {enrich.DoubleRemoteEnrich ASYNC} (6.4% fail rate in 94 executions)
• [8.18] 6 failures in step 8.14.3_bwc (66.7% fail rate in 9 executions)
• [8.18] 6 failures in pipeline elasticsearch-periodic (66.7% fail rate in 9 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)