[CI] MixedClusterEsqlSpecIT test {stats.Count_or_null SYNC #2} failing

[elasticsearchmachine]

Build Scans:

• elasticsearch-periodic #3442 / 8.11.4_bwc
• elasticsearch-pull-request #25954 / 8.11.4_bwc

Reproduction Line:

./gradlew ":x-pack:plugin:esql:qa:server:mixed-cluster:v8.11.4#javaRestTest" -Dtests.class="org.elasticsearch.xpack.esql.qa.mixed.MixedClusterEsqlSpecIT" -Dtests.method="test {stats.Count_or_null SYNC #2}" -Dtests.seed=CEA1B2D9884DE759 -Dtests.bwc=true -Dtests.locale=ar-YE -Dtests.timezone=Africa/Banjul -Druntime.java=22

Applicable branches:
main

Reproduces locally?:
N/A

Failure History:
See dashboard

Failure Message:

org.elasticsearch.client.ResponseException: method [POST], host [http://[::1]:35265], URI [/_query?pretty=true&error_trace=true], status line [HTTP/1.1 400 Bad Request]
Warnings: [No limit defined, adding default limit of [500]]
{
  "error" : {
    "root_cause" : [
      {
        "type" : "verification_exception",
        "reason" : "Found 2 problems\nline 2:9: aggregate function's field must be an attribute or literal; found [n > 0 OR NULL] of type [Or]\nline 2:31: aggregate function's field must be an attribute or literal; found [n < 0 OR NULL] of type [Or]",
        "stack_trace" : "org.elasticsearch.xpack.esql.analysis.VerificationException: Found 2 problems\nline 2:9: aggregate function's field must be an attribute or literal; found [n > 0 OR NULL] of type [Or]\nline 2:31: aggregate function's field must be an attribute or literal; found [n < 0 OR NULL] of type [Or]\n\tat org.elasticsearch.xpack.esql.analysis.Analyzer.verify(Analyzer.java:113)\n\tat org.elasticsearch.xpack.esql.analysis.Analyzer.analyze(Analyzer.java:107)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.lambda$analyzedPlan$2(EsqlSession.java:143)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.lambda$preAnalyze$5(EsqlSession.java:168)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.action.ActionListener$2.onResponse(ActionListener.java:177)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.preAnalyzeIndices(EsqlSession.java:206)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.lambda$preAnalyze$6(EsqlSession.java:166)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.action.ActionListenerImplementations$ResponseWrappingActionListener.onResponse(ActionListenerImplementations.java:236)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.action.ActionListener$4.onResponse(ActionListener.java:330)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.action.support.RefCountingListener.finish(RefCountingListener.java:121)\n\tat org.elasticsearch.base@8.11.4/org.elasticsearch.core.AbstractRefCounted$1.closeInternal(AbstractRefCounted.java:117)\n\tat org.elasticsearch.base@8.11.4/org.elasticsearch.core.AbstractRefCounted.decRef(AbstractRefCounted.java:69)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.action.support.RefCountingRunnable.close(RefCountingRunnable.java:116)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.action.support.RefCountingListener.close(RefCountingListener.java:114)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.preAnalyze(EsqlSession.java:176)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.analyzedPlan(EsqlSession.java:141)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.optimizedPlan(EsqlSession.java:290)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.physicalPlan(EsqlSession.java:298)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.optimizedPhysicalPlan(EsqlSession.java:306)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.execute(EsqlSession.java:111)\n\tat org.elasticsearch.xpack.esql.execution.PlanExecutor.esql(PlanExecutor.java:67)\n\tat org.elasticsearch.xpack.esql.plugin.TransportEsqlQueryAction.doExecuteForked(TransportEsqlQueryAction.java:102)\n\tat org.elasticsearch.xpack.esql.plugin.TransportEsqlQueryAction.lambda$doExecute$0(TransportEsqlQueryAction.java:86)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.action.ActionRunnable$3.doRun(ActionRunnable.java:73)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:26)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:33)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:983)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:26)\n\tat java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)\n\tat java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)\n\tat java.base/java.lang.Thread.run(Thread.java:1583)\n"
      }
    ],
    "type" : "verification_exception",
    "reason" : "Found 2 problems\nline 2:9: aggregate function's field must be an attribute or literal; found [n > 0 OR NULL] of type [Or]\nline 2:31: aggregate function's field must be an attribute or literal; found [n < 0 OR NULL] of type [Or]",
    "stack_trace" : "org.elasticsearch.xpack.esql.analysis.VerificationException: Found 2 problems\nline 2:9: aggregate function's field must be an attribute or literal; found [n > 0 OR NULL] of type [Or]\nline 2:31: aggregate function's field must be an attribute or literal; found [n < 0 OR NULL] of type [Or]\n\tat org.elasticsearch.xpack.esql.analysis.Analyzer.verify(Analyzer.java:113)\n\tat org.elasticsearch.xpack.esql.analysis.Analyzer.analyze(Analyzer.java:107)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.lambda$analyzedPlan$2(EsqlSession.java:143)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.lambda$preAnalyze$5(EsqlSession.java:168)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.action.ActionListener$2.onResponse(ActionListener.java:177)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.preAnalyzeIndices(EsqlSession.java:206)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.lambda$preAnalyze$6(EsqlSession.java:166)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.action.ActionListenerImplementations$ResponseWrappingActionListener.onResponse(ActionListenerImplementations.java:236)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.action.ActionListener$4.onResponse(ActionListener.java:330)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.action.support.RefCountingListener.finish(RefCountingListener.java:121)\n\tat org.elasticsearch.base@8.11.4/org.elasticsearch.core.AbstractRefCounted$1.closeInternal(AbstractRefCounted.java:117)\n\tat org.elasticsearch.base@8.11.4/org.elasticsearch.core.AbstractRefCounted.decRef(AbstractRefCounted.java:69)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.action.support.RefCountingRunnable.close(RefCountingRunnable.java:116)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.action.support.RefCountingListener.close(RefCountingListener.java:114)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.preAnalyze(EsqlSession.java:176)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.analyzedPlan(EsqlSession.java:141)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.optimizedPlan(EsqlSession.java:290)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.physicalPlan(EsqlSession.java:298)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.optimizedPhysicalPlan(EsqlSession.java:306)\n\tat org.elasticsearch.xpack.esql.session.EsqlSession.execute(EsqlSession.java:111)\n\tat org.elasticsearch.xpack.esql.execution.PlanExecutor.esql(PlanExecutor.java:67)\n\tat org.elasticsearch.xpack.esql.plugin.TransportEsqlQueryAction.doExecuteForked(TransportEsqlQueryAction.java:102)\n\tat org.elasticsearch.xpack.esql.plugin.TransportEsqlQueryAction.lambda$doExecute$0(TransportEsqlQueryAction.java:86)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.action.ActionRunnable$3.doRun(ActionRunnable.java:73)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:26)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.common.util.concurrent.TimedRunnable.doRun(TimedRunnable.java:33)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:983)\n\tat org.elasticsearch.server@8.11.4/org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:26)\n\tat java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)\n\tat java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)\n\tat java.base/java.lang.Thread.run(Thread.java:1583)\n"
  },
  "status" : 400
}

Issue Reasons:

• [main] 2 consecutive failures in test test {stats.Count_or_null SYNC Discovery: Support local (JVM level) discovery #2}
• [main] 2 consecutive failures in step 8.11.4_bwc
• [main] 2 failures in test test {stats.Count_or_null SYNC Discovery: Support local (JVM level) discovery #2} (50.0% fail rate in 4 executions)
• [main] 2 failures in step 8.11.4_bwc (50.0% fail rate in 4 executions)

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

[elasticsearchmachine]

This has been muted on branch main

Mute Reasons:

• [main] 2 consecutive failures in test test {stats.Count_or_null SYNC Discovery: Support local (JVM level) discovery #2}
• [main] 2 consecutive failures in step 8.11.4_bwc
• [main] 2 failures in test test {stats.Count_or_null SYNC Discovery: Support local (JVM level) discovery #2} (50.0% fail rate in 4 executions)
• [main] 2 failures in step 8.11.4_bwc (50.0% fail rate in 4 executions)

Build Scans:

• elasticsearch-periodic #3442 / 8.11.4_bwc
• elasticsearch-pull-request #25954 / 8.11.4_bwc

[elasticsearchmachine]

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

[ivancea]

Similar issue: #110895