Failure: clickhouse-head-x86-with-analyzer-keeper / window_functions / window_functions - AssertionError: snapshot mismatch in aggregate_funcs over rows frame (topK / topKWeighted)

[CarlosFelipeOR]

Version: 25.12.1.229 (Head)

---

Description

Window Functions tests involving topK() and topKWeighted() began failing on ClickHouse >= 25.12.
The failure was caused by a change in the internal ordering of values returned by these aggregate functions when used inside window frames.

The set of returned values and their frequency ordering remain correct, but the ordering of values with equal frequency is no longer stable, unlike previous ClickHouse versions.
This breaks snapshot comparisons and also affects ORDER BY func semantics, since arrays are compared lexicographically.

According to the ClickHouse documentation, this new behavior is valid:

“The resulting array is sorted in descending order of approximate frequency of values (not by the values themselves).”

Previous versions happened to produce a deterministic value order, but this was not guaranteed.

Resolution

• Added version-specific snapshots for topK() and topKWeighted() when running on ClickHouse >= 25.12.
• Updated the test logic to automatically select the correct snapshot based on the ClickHouse version.
• No upstream issue is required since the new behavior complies with the documented semantics of topK().
• The regression suite now supports both older and newer ClickHouse versions without false failures.
• Commits:
  • x86: 06f54e9
  • aarch64: cd64cba

---

Failure Description:

1) Non-distributed – topK(salary)

Test path

/window functions/tests/non distributed/aggregate funcs/aggregate funcs over rows frame/func='topK(salary)'

Query

SELECT topK(salary) OVER (
    ORDER BY salary, empno
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS func
FROM empsalary
ORDER BY func DESC

Error

AssertionError: Oops! Assertion failed

SnapshotError:
  filename: window_functions/tests/snapshots/common.py.tests.x86_64.snapshot
  name: _window_functions_tests_aggregate_funcs_aggregate_funcs_over_rows_frame_func__topK_salary__

snapshot_value:
    [5200,4800,5000,6000]
    [4800,5200,5000,6000]
    [4800,5200,4500,5000,6000]
    [4800,5200,4200,4500,5000,6000]
    [4800,5200,3900,4200,4500,5000,6000]
    [4800,5200,3500,3900,4200,4500,5000,6000]

actual_value:
    [5200,4800,6000,5000]
    [5200,4800,4200,5000,6000,4500]
    [5200,4800,3900,5000,6000,4200,4500]
    [5200,4800,3500,5000,6000,4200,3900,4500]
    [4800,5200,6000,5000]
    [4800,5200,4500,6000,5000]

---

2) Non-distributed – topKWeighted(salary, 1)

Test path

/window functions/tests/non distributed/aggregate funcs/aggregate funcs over rows frame/func='topKWeighted(salary, 1)'

Query

SELECT topKWeighted(salary, 1) OVER (
    ORDER BY salary, empno
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS func
FROM empsalary
ORDER BY func DESC

Error

AssertionError: Oops! Assertion failed

SnapshotError:
  filename: window_functions/tests/snapshots/common.py.tests.x86_64.snapshot
  name: _window_functions_tests_aggregate_funcs_aggregate_funcs_over_rows_frame_func__topKWeighted_salary__1__

snapshot_value:
    [5200,4800,5000,6000]
    [4800,5200,5000,6000]
    [4800,5200,4500,5000,6000]
    [4800,5200,4200,4500,5000,6000]
    [4800,5200,3900,4200,4500,5000,6000]
    [4800,5200,3500,3900,4200,4500,5000,6000]

actual_value:
    [5200,4800,6000,5000]
    [5200,4800,4200,5000,6000,4500]
    [5200,4800,3900,5000,6000,4200,4500]
    [5200,4800,3500,5000,6000,4200,3900,4500]
    [4800,5200,6000,5000]
    [4800,5200,4500,6000,5000]

---

3) Distributed – topK(salary)

Test path

/window functions/tests/distributed/aggregate funcs/aggregate funcs over rows frame/func='topK(salary)'

Query

SELECT topK(salary) OVER (
    ORDER BY salary, empno
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS func
FROM empsalary
ORDER BY func DESC

Error

AssertionError: Oops! Assertion failed (SnapshotError)

snapshot_value:
    [5200,4800,5000,6000]
    [4800,5200,5000,6000]
    [4800,5200,4500,5000,6000]
    [4800,5200,4200,4500,5000,6000]
    [4800,5200,3900,4200,4500,5000,6000]
    [4800,5200,3500,3900,4200,4500,5000,6000]

actual_value:
    [5200,4800,6000,5000]
    [5200,4800,4200,5000,6000,4500]
    [5200,4800,3900,5000,6000,4200,4500]
    [5200,4800,3500,5000,6000,4200,3900,4500]
    [4800,5200,6000,5000]
    [4800,5200,4500,6000,5000]

---

4) Distributed – topKWeighted(salary, 1)

Test path

/window functions/tests/distributed/aggregate funcs/aggregate funcs over rows frame/func='topKWeighted(salary, 1)'

Query

SELECT topKWeighted(salary, 1) OVER (
    ORDER BY salary, empno
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS func
FROM empsalary
ORDER BY func DESC

Error

AssertionError: Oops! Assertion failed (SnapshotError)

snapshot_value:
    [5200,4800,5000,6000]
    [4800,5200,5000,6000]
    [4800,5200,4500,5000,6000]
    [4800,5200,4200,4500,5000,6000]
    [4800,5200,3900,4200,4500,5000,6000]
    [4800,5200,3500,3900,4200,4500,5000,6000]

actual_value:
    [5200,4800,6000,5000]
    [5200,4800,4200,5000,6000,4500]
    [5200,4800,3900,5000,6000,4200,4500]
    [5200,4800,3500,5000,6000,4200,3900,4500]
    [4800,5200,6000,5000]
    [4800,5200,4500,6000,5000]

---

CI Run

https://github.com/Altinity/clickhouse-regression/actions/runs/19776301887/job/56669336548

Artifacts and Reports

Artifacts will be uploaded to https://altinity-internal-test-reports.s3.amazonaws.com/index.html#clickhouse/25.12.1.229/19776301887/testflows/x86_64/with_analyzer/zookeeper/without_thread_fuzzer/window_functions/
Review report at https://altinity-internal-test-reports.s3.amazonaws.com/clickhouse/25.12.1.229/19776301887/testflows/x86_64/with_analyzer/zookeeper/without_thread_fuzzer/window_functions/report.html
Review new fails (nice) at https://altinity-internal-test-reports.s3.amazonaws.com/clickhouse/25.12.1.229/19776301887/testflows/x86_64/with_analyzer/zookeeper/without_thread_fuzzer/window_functions/nice-new-fails.log.txt

---

[CarlosFelipeOR]

Local Retest

Running only the first failing test

Command:

python3 -u window_functions/regression.py \
  --local \
  --clickhouse docker://clickhouse/clickhouse-server:head-alpine \
  --log windows.log \
  --with-analyzer \
  --only '/window functions/tests/non distributed/aggregate funcs/aggregate funcs over rows frame/func=＇topK（salary）＇/*'

Result on 25.12.1.229 (head-alpine): ❌ Failed again with the same failures
Result on 25.11.2.24 (latest-alpine): ✅ Passed

---

Running the entire suite

Command:

python3 -u window_functions/regression.py \
  --local \
  --clickhouse docker://clickhouse/clickhouse-server:head-alpine \
  --log windows.log \
  --with-analyzer \
  --test-to-end

Result on 25.12.1.229 (head-alpine): ❌ Failed again with the same failures
Result on 25.11.2.24 (latest-alpine): ✅ Passed

[CarlosFelipeOR]

Steps to Reproduce

The issue can be reproduced using the following SQL queries.

---

1. Create the table

CREATE TABLE empsalary
(
    depname String,
    empno UInt32,
    salary UInt32,
    enroll_date Date
)
ENGINE = Memory;

2. Insert the dataset used by the Window Functions test suite

INSERT INTO empsalary VALUES
('personnel', 5, 3500, '2007-12-10'),
('personnel', 2, 3900, '2006-12-23'),
('develop',   7, 4200, '2008-01-01'),
('develop',   9, 4500, '2008-01-01'),
('sales',     3, 4800, '2007-08-01'),
('sales',     4, 4800, '2007-08-08'),
('sales',     1, 5000, '2006-10-01'),
('develop',  10, 5200, '2007-08-01'),
('develop',  11, 5200, '2007-08-15'),
('develop',   8, 6000, '2006-10-01');

3. Run the TOPK window function query (same used by the test suite)

SELECT topK(salary) OVER (
    ORDER BY salary ASC, empno ASC
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS func
FROM empsalary
ORDER BY func DESC;

---

Expected Snapshot Output (from regression suite)

[6000]
[5200,6000]
[5200,6000]
[5200,5000,6000]
[5200,4800,5000,6000]
[4800,5200,5000,6000]
[4800,5200,4500,5000,6000]
[4800,5200,4200,4500,5000,6000]
[4800,5200,3900,4200,4500,5000,6000]
[4800,5200,3500,3900,4200,4500,5000,6000]

Actual Output (ClickHouse current behavior)

[6000]
[5200,6000]
[5200,6000]
[5200,5000,6000]
[5200,4800,6000,5000]
[5200,4800,4200,5000,6000,4500]
[5200,4800,3900,5000,6000,4200,4500]
[5200,4800,3500,5000,6000,4200,3900,4500]
[4800,5200,6000,5000]
[4800,5200,4500,6000,5000]

[CarlosFelipeOR]

Additional findings

Removing the final ORDER BY func DESC makes the difference easier to see.

Query:

SELECT topK(salary) OVER (
    ORDER BY salary ASC, empno ASC
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS func
FROM empsalary

Latest (expected snapshot from previous versions)

[4800,5200,3500,3900,4200,4500,5000,6000]
[4800,5200,3900,4200,4500,5000,6000]
[4800,5200,4200,4500,5000,6000]
[4800,5200,4500,5000,6000]
[4800,5200,5000,6000]
[5200,4800,5000,6000]
[5200,5000,6000]
[5200,6000]
[5200,6000]
[6000]

HEAD (current behavior)

[5200,4800,3500,5000,6000,4200,3900,4500]
[5200,4800,3900,5000,6000,4200,4500]
[5200,4800,4200,5000,6000,4500]
[4800,5200,4500,6000,5000]
[4800,5200,6000,5000]
[5200,4800,6000,5000]
[5200,5000,6000]
[5200,6000]
[5200,6000]
[6000]

Observation

The set of values returned by topK() is the same, and the frequency ordering is still correct
The documentation for topK() states: “The resulting array is sorted in descending order of approximate frequency of values (not by the values themselves).”.

What changed in HEAD is that the order of elements with the same frequency is no longer stable.
Previous versions consistently ordered ties by value (which was never guaranteed by the documentation), while the current version returns them in an undefined order.

Because the internal array ordering changed, any final query using ORDER BY func DESC now produces different row ordering as well, since arrays are compared lexicographically.

In short:

• the window contents are the same,
• the frequency ordering is preserved,
• but internal array ordering changed, which also impacts the final result ordering.

[vzakaznikov]

Yes, file an issue. Sent from [Proton Mail](https://proton.me/mail/home) for Android.

…

-------- Original Message --------

On Friday, 12/05/25 at 15:05 Carlos Felipe de Oliveira Raymundo ***@***.***> wrote: CarlosFelipeOR left a comment [(Altinity/clickhouse-regression#74)](#74 (comment)) Additional findings The [documentation](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/topk) for topK() states: > “topK(N) — Returns an array of the approximately most frequent values in the specified column. The resulting array is sorted in descending order of approximate frequency of values (not by the values themselves).” > > This function does not provide a guaranteed result. In certain situations, errors might occur and it might return frequent values that aren't the most frequent values. Despite this, in previous ClickHouse releases the function produced stable and deterministic results for the dataset in question, always matching the snapshots in the Window Functions suite. In the current version, it instead returns arrays with fewer elements and a different ordering, even when the window contains all data and N = 10 would allow for full output. Because the behavior with this dataset used to be consistent, the current divergence appears to be a regression rather than acceptable approximation noise. ***@***.***(https://github.com/vzakaznikov) , should we open an issue upstream to report this behavior, or should we adapt our test suite to account for the fact that topK() does not guarantee deterministic output? — Reply to this email directly, [view it on GitHub](#74 (comment)), or [unsubscribe](https://github.com/notifications/unsubscribe-auth/AJ6ABQDLOL4C3LV4OQDHVF34AHQQHAVCNFSM6AAAAACODRAS6GVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTMMJYGQYTENJYGU). You are receiving this because you were mentioned.Message ID: ***@***.***>

[CarlosFelipeOR]

@vzakaznikov I updated the previous comment considering the new findings.

This is not an issue - it is following the documentation by sorting the array in descending order of frequency.
In previous versions, the array happened to be ordered by value as well, but this behavior was never guaranteed by the documentation, only the descending order by frequency was.

I have updated our tests accordingly by adjusting the snapshots for versions >= 25.12 on both x86 and aarch64.

Commits:

• x86: 06f54e9
• aarch64: cd64cba

Let me know if you have any concerns.