[CI] EsqlClientYamlIT test {p0=esql/40_unsupported_types/nested declared inline} failing

[elasticsearchmachine]

Build Scans:

• elasticsearch-intake #26606 / 9.0.5_bwc-snapshots
• elasticsearch-pull-request #84609 / 8.19.1_part_3_bwc-snapshots

Reproduction Line:

./gradlew ":x-pack:plugin:esql:qa:server:mixed-cluster:v9.0.5#yamlRestTest" -Dtests.class="org.elasticsearch.xpack.esql.qa.mixed.EsqlClientYamlIT" -Dtests.method="test {p0=esql/40_unsupported_types/nested declared inline}" -Dtests.seed=4E60A4E4A57049F6 -Dtests.bwc=true -Dtests.locale=km -Dtests.timezone=Africa/Banjul -Druntime.java=24

Applicable branches:
main

Reproduces locally?:
N/A

Failure History:
See dashboard

Failure Message:

java.lang.AssertionError: Failure at [esql/40_unsupported_types:435]: expected [2xx] status code but api [esql.query] returned [400 Bad Request] [{"error":{"root_cause":[{"type":"verification_exception","reason":"Found 1 problem\nline 1:19: Unknown column [find_me]","stack_trace":"org.elasticsearch.xpack.esql.VerificationException: Found 1 problem\nline 1:19: Unknown column [find_me]\n\tat org.elasticsearch.xpack.esql.analysis.Analyzer.verify(Analyzer.java:194)\n\tat org.elasticsearch.xpack.esql.analysis.Analyzer.analyze(Analyzer.java:188)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.lambda$analyzedPlan$7(EsqlSession.java:317)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.analyzeAndMaybeRetry(EsqlSession.java:524)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.lambda$analyzedPlan$16(EsqlSession.java:362)\n\tat org.elasticsearch.server@9.0.5-SNAPSHOT/org.elasticsearch.action.ActionListenerImplementations$ResponseWrappingActionListener.onResponse(ActionListener
[truncated]

Issue Reasons:

• [main] 2 failures in test test {p0=esql/40_unsupported_types/nested declared inline} (0.7% fail rate in 292 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)

[fang-xing-esql]

This happens intermittently, I couldn't reproduce it on 8.18 with the command in the description.

[alex-spies]

Still failing intermittently, I see 6 failures over the last 28 days.

[alex-spies]

Test was added in #111495 when we removed nested fields.

[elasticsearchmachine]

This has been muted on branch main

Mute Reasons:

• [main] 2 failures in test test {p0=esql/40_unsupported_types/nested declared inline} (0.7% fail rate in 292 executions)

Build Scans:

• elasticsearch-intake #26606 / 9.0.5_bwc-snapshots
• elasticsearch-pull-request #84609 / 8.19.1_part_3_bwc-snapshots

[astefan]

@tlrx reaching out to you regarding this seemingly unrelated CI failure because:

• it happened in one of your PRs - Acquire the engine reset write lock during post_recovery refresh #132531 - I am assuming when CI was running a BWC test
• that PR, to my untrained eye, seems to have something to do with the failure itself.

[2025-08-08T00:33:25,494][INFO ][o.e.c.m.MetadataCreateIndexService] [test-cluster-1] creating index [test] in project [default], cause [api], templates [], shards [5]/[1]
[2025-08-08T00:33:25,665][INFO ][o.e.c.m.MetadataMappingService] [test-cluster-1] [test/nIer1hywSfiaA0yBpwFl2A] update_mapping [_doc]
[2025-08-08T00:33:25,725][WARN ][o.e.c.RestClient         ][test] request [POST http://[::1]:37835/_query?error_trace=true] returned 1 warnings: [299 Elasticsearch-9.2.0-ddc40b5f29e0161bdb3b78be8dd7918b06def9d3 "No limit defined, adding default limit of [1000]"]
[2025-08-08T00:33:25,726][INFO ][o.e.x.e.q.m.EsqlClientYamlIT][test] Dump test yaml ............
[2025-08-08T00:33:25,731][INFO ][o.e.x.e.q.m.EsqlClientYamlIT][test] Stash dump on test failure [{
  "stash" : {
    "body" : {
      "error" : {
        "root_cause" : [
          {
            "type" : "verification_exception",
            "reason" : "Found 1 problem
line 1:19: Unknown column [find_me]",..................
[2025-08-08T00:33:25,733][INFO ][o.e.c.r.a.AllocationService] [test-cluster-1] current.health="GREEN" message="Cluster health status changed from [YELLOW] to [GREEN] (reason: [shards started [[test][4]]])." previous.health="YELLOW" reason="shards started [[test][4]]"
[2025-08-08T00:33:25,752][INFO ][o.e.x.e.q.m.EsqlClientYamlIT][test] Waiting for all cluster updates up to this moment to be processed
.....

It seems that the test index was not fully ready when the query was executed. The query, among others, is running _field_caps to learn about the find_me field that does exist in the mapping of the index. And the failure to find that field in the _field_caps response would result in that VerificationException above.

Could the work in your PR be fixing something that would eventually allow the test index to be ready when the query is executed?