Versions issues

All popular subdatabases are being evaluated, so you may encounter different behaviors depending on the version. This page focuses on those issues.

Clickhouse window with group by

ClickHouse 23 behaves incorrectly when you try to apply a window function after aggregation—the aggregation simply doesn’t apply. This issue is fixed in ClickHouse 24.

---

Consider the following example: the next cell creates two Docker containers, one with ClickHouse 23 and another with ClickHouse 24.

docker run -it --rm --name click_23 -d clickhouse/clickhouse-server:23.9.1
docker run -it --rm --name click_24 -d clickhouse/clickhouse-server:24.9.1.3278

319336787ada78390da5c93b2a177f7d7a902e0b6abc32ad5a4a2536a810d48c
bdec89e1bf8e4cf97e9b5897a3218981ff47f1f4c3d287e3191e4b80ba1ead3d

In the following example, we’re attempting to aggregate a value and then shift the result of the aggregation by one.

docker exec -i click_23 clickhouse-client --multiquery << EOF
CREATE TEMPORARY TABLE temp (some_value Int32) 
ENGINE = Memory;

INSERT INTO temp VALUES
    (3), (3), (1), (4), (3);

SELECT 
    some_value AS aggregation,
    any(some_value) OVER (ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS shift
FROM temp 
GROUP BY some_value
FORMAT Pretty;
EOF

┏━━━━━━━━━━━━━┳━━━━━━━┓
┃ aggregation ┃ shift ┃
┡━━━━━━━━━━━━━╇━━━━━━━┩
│           4 │     4 │
├─────────────┼───────┤
│           3 │     3 │
├─────────────┼───────┤
│           1 │     1 │
└─────────────┴───────┘

As a result, the shift column, which should be shifted relative to the aggregation, ends up having the same value.

The following cell shows the same query in ClickHouse 24.

docker exec -i click_24 clickhouse-client --multiquery << EOF
CREATE TEMPORARY TABLE temp (some_value Int32)
ENGINE = Memory;

INSERT INTO temp VALUES
    (3), (3), (1), (4), (3);

SELECT 
    some_value AS aggregation,
    any(some_value) OVER (ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS shift
FROM temp
GROUP BY some_value
FORMAT Pretty;
EOF

   ┏━━━━━━━━━━━━━┳━━━━━━━┓
   ┃ aggregation ┃ shift ┃
   ┡━━━━━━━━━━━━━╇━━━━━━━┩
1. │           4 │     3 │
   ├─────────────┼───────┤
2. │           3 │     1 │
   ├─────────────┼───────┤
3. │           1 │     0 │
   └─────────────┴───────┘

The result of the query is just as expected.

Possible solution

You can fix the ClickHouse 23 query by using the leadInFrame function. The following cell demonstrates how to use the leadInFrame function.

docker exec -i click_23 clickhouse-client --multiquery << EOF
CREATE TEMPORARY TABLE temp (some_value Int32) 
ENGINE = Memory;

INSERT INTO temp VALUES
    (3), (3), (1), (4), (3);

SELECT 
    some_value,
    any(some_value) OVER (ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS shift1,
    leadInFrame(some_value) OVER () AS shift2
FROM temp 
GROUP BY some_value
FORMAT Pretty;
EOF

┏━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━┓
┃ some_value ┃ shift1 ┃ shift2 ┃
┡━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━┩
│          4 │      4 │      3 │
├────────────┼────────┼────────┤
│          3 │      3 │      1 │
├────────────┼────────┼────────┤
│          1 │      1 │      0 │
└────────────┴────────┴────────┘

The following cell stops the containers used in these examples.

docker stop click_23 click_24

click_23
click_24