[CI] EsqlSpecIT test {enrich.EnrichAgesStatsYear SYNC} failing

[elasticsearchmachine]

Build Scans:

• elasticsearch-periodic-platform-support #7725 / rocky-9_platform-support-unix
• elasticsearch-periodic-platform-support #7719 / ubuntu-2004_platform-support-unix
• elasticsearch-periodic-platform-support #7707 / windows-2019_checkpart3_platform-support-windows
• elasticsearch-periodic-platform-support #7707 / ubuntu-2404-aarch64_checkpart3_platform-support-arm
• elasticsearch-periodic-platform-support #7701 / windows-2022_checkpart3_platform-support-windows

Reproduction Line:

./gradlew ":x-pack:plugin:esql:qa:server:multi-node:javaRestTest" --tests "org.elasticsearch.xpack.esql.qa.multi_node.EsqlSpecIT" -Dtests.method="test {enrich.EnrichAgesStatsYear ASYNC}" -Dtests.seed=B12161BADEB59AB8 -Dtests.locale=sd -Dtests.timezone=Europe/Prague -Druntime.java=24

Applicable branches:
8.x

Reproduces locally?:
N/A

Failure History:
See dashboard

Failure Message:

java.lang.AssertionError: 
Data mismatch:
row 0 column 2:a list containing
row 0 column 2:0: expected "1" but was "2"
row 1 column 2:a list containing
row 1 column 2:0: expected "4" but was "8"
row 2 column 2:a list containing
row 2 column 2:0: expected "7" but was "14"
row 3 column 2:a list containing
row 3 column 2:0: expected "6" but was "12"
row 4 column 2:a list containing
row 4 column 2:0: expected "8" but was "16"
row 5 column 2:a list containing
row 5 column 2:0: expected "8" but was "16"
Actual:
birth_year:long | age_group:keyword | count:long
1965            | Middle-aged       | 2         
1964            | Middle-aged       | 8         
1963            | Middle-aged       | 14        
1962            | Senior            | 12        
1961            | Senior            | 16        
1960            | Senior            | 16        

Expected:
birth_year:long | age_group:keyword | count:long
1965            | Middle-aged       | 1         
1964            | Middle-aged       | 
[truncated]

Issue Reasons:

• [8.x] 5 failures in test test {enrich.EnrichAgesStatsYear SYNC} (2.7% fail rate in 188 executions)
• [8.x] 4 failures in pipeline elasticsearch-periodic-platform-support (80.0% fail rate in 5 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]

#127041, #127042, #127043 are duplicates of this, and they look like wrong results, all the counts are doubled, I couldn't reproduce this locally, setting risk to medium for now.

[alex-spies]

Couldn't reproduce, either. Also, only the first build scan shows this test failure, the other ones failed due to timeouts during test setup.

The fact that we count every row twice is suspicious. Maybe it's some kind of (very rare) timing issue where, possibly, a shard was being relocated from one node to the other and therefore FROM employees | ... read the employees index twice?