Close Flaky Issues

Close Flaky Test Issues Skill

Arguments

• $0 (optional): number of days the test must be failure-free on master before it is eligible for closing. Default: 14.
• $1 (optional): pass dry-run to print the table of close candidates and stop without calling gh issue close. Any other value (or none) runs the full sweep.

Goal

Sweep open flaky test issues and close ones where:

1. The test has 0 failures on master within the threshold window, AND
2. (Ideally) a fix commit can be linked to the closure.

These issues are auto-generated by the CI bot — closing one is safe because the bot reopens automatically if the test fails again on master.

Steps

1. List open issues

mkdir -p tmp/flaky
gh issue list --repo ClickHouse/ClickHouse --label "flaky test" --state open \
  --limit 200 --json number,title,body > tmp/flaky/issues.json

Each body has the format:

Test name: <test_name>
Failure reason: <reason>
CI report: <url>
Failing test history: <play.clickhouse.com link with base64 SQL>

Extract (number, test_name) pairs:

jq -r '.[] | [.number, ((.body | capture("Test name: (?<n>.+)")? | .n) // "")] | @tsv' tmp/flaky/issues.json

The trailing ? after capture is required: without it, an issue with a null body or one that doesn't contain Test name: raises an error that aborts the whole pipeline and silently drops every subsequent issue. With ?, those rows produce an empty string instead, and you can fall back to the title or to the base64 SQL after # in the Failing test history URL — some old issues use the title rather than Test name: in the body.

2. Query CI failure history

Run one batch query against play.clickhouse.com for all extracted test names. The checks table is publicly readable via the play user.

Before running the query, guard against an empty list. If jq extracted no non-empty test names (zero open flaky test issues, or all bodies missing Test name:), test_name IN () is invalid SQL and aborts the sweep. Skip the query in that case and report "nothing to close".

Escape single quotes in test names before interpolation. Per ClickHouse SQL rules, a literal ' inside a string literal is written as ''. Names that contain a quote (e.g. parametrized integration tests like test_foo[a'b]) will otherwise produce invalid SQL or, worse, parse as a different list. Apply s/'/''/g to each extracted name before joining them with , .

curl -sS 'https://play.clickhouse.com/?user=play' --data-binary "
SELECT test_name,
       countIf(test_status IN ('FAIL','ERROR')) AS failures_90d,
       countIf(test_status IN ('FAIL','ERROR') AND check_start_time >= now() - INTERVAL 30 DAY) AS failures_30d,
       countIf(test_status IN ('FAIL','ERROR') AND check_start_time >= now() - INTERVAL 14 DAY) AS failures_14d,
       countIf(test_status IN ('FAIL','ERROR') AND check_start_time >= now() - INTERVAL 7 DAY)  AS failures_7d,
       maxIf(check_start_time, test_status IN ('FAIL','ERROR'))                                  AS last_fail
FROM checks
WHERE check_start_time >= now() - INTERVAL 90 DAY
  AND test_name IN ( '<test1>', '<test2>', ... )
  AND (pull_request_number = 0 OR base_ref IN ('master',''))
GROUP BY test_name
ORDER BY failures_7d DESC, failures_14d DESC
FORMAT TabSeparatedWithNames
"

Important:

• pull_request_number = 0 OR base_ref IN ('master','') filters to master commits and PRs targeting master — fork-PR noise is dropped. The empty-string case covers older rows where base_ref was not populated for direct-master runs.
• Tests with 0 rows returned have never failed on master in the lookback window — those are also closeable.
• last_fail defaults to 1970-01-01 if the test never failed.

3. Categorize

For each issue, decide based on failures_<threshold-days>d:

• 0 failures in the window → close candidate
• >= 1 failure in the window → keep open

Default threshold is 14 days. Use 7 days for an aggressive sweep, 30 days for a conservative one.

4. Find the fix commit (best-effort)

For each close candidate, search git log:

# Look for "fix flaky" commits naming the test
git log origin/master --since="<date 90 days ago>" --format="%H %s" \
  --grep="<test_name_fragment>" -i

# Look at recent direct modifications to the test file
git log origin/master --since="<date 90 days ago>" --format="%H %s" -- "<test_path>"

Not every close candidate has an explicit fix commit — some stabilize via surrounding infra changes. That's fine; close those too, just without a commit reference.

For integration tests (test_X/test.py::...), search the directory: tests/integration/test_X/. For stateless tests (NNNNN_name), search: tests/queries/0_stateless/NNNNN_name.*.