Simple Aggregation functions like Max and Sum requires AggregatingMergeTree instead of MergeTree

[raghukn]

Please make sure that the version you're using is still supported (you can find the list here).

You have to provide the following information whenever possible.

Company or project name
Proect ReBase

Describe what's wrong

Simple Aggregation functions like Max and Sum requires AggregatingMergeTree instead of MergeTree

Does it reproduce on the most recent release?

24.4.1.2088

How to reproduce

• Which ClickHouse server version to use : 24.4.1.2088

Using MergeTree - (Does not work)

CREATE TABLE knr_example_table(id UInt32, value UInt32) ENGINE = MergeTree() ORDER BY id;

CREATE TABLE knr_example_sum_tbl (id UInt32, maxval UInt32) ENGINE = MergeTree() ORDER BY id;

create materialized view knr_example_sum_mv to knr_example_sum_tbl as Select id, max(value) as maxval  from knr_example_table  group by id;

insert into knr_example_table values (4,50); insert into knr_example_table values (4,100);

select id, maxval from knr_example_sum_mv;

===============

Using AggregatingMergeTree - (Works correctly)

create table knr_example_sum_tbl (id UInt32, maxval AggregateFunction(max,UInt32)) ENGINE = AggregatingMergeTree() ORDER BY id;

create materialized view knr_example_sum_mv to knr_example_sum_tbl as Select id, maxState(value) as maxval  from knr_example_table  group by id;

insert into knr_example_table values (4,50); insert into knr_example_table values (4,100);

select id, maxMerge(maxval) from knr_example_sum_mv group by id;

Expected behavior

Simple Aggregation functions like Max and Sum doesnt work correctly when Enginen is MergeTree

Error message and/or stacktrace

Incorrect or unmerged rows in MV

Additional context

Add any other context about the problem here.

[alexey-milovidov]

Ordinary MergeTree does not aggregate data in the background, that's the purpose of AggregatingMergeTree.

[raghukn]

Ok, I was of the opinion that for simple Opeartions like SUM or MAX (Which do not need additional state information to merge, simple mergetree would suffice). Infact I find that without using state, even AggregatingMergeTree donot work.

Aggregating MergeTree without sumState / maxState

create table knr_example_sum_tbl (id UInt32, sumval UInt32) ENGINE = AggregatingMergeTree() ORDER BY id;

create materialized view knr_example_sum_mv to knr_example_sum_tbl as select id, sum(value) as sumval  from knr_example_table  group by id;

insert into knr_example_table values (4,50); insert into knr_example_table values (4,100);

Result after final merge

SELECT *
FROM knr_example_sum_mv

Query id: e69ceacb-4461-49c2-8ea0-9cea0ef386a0

   ┌─id─┬─sumval─┐
1. │  4 │     50 │
   └────┴────────┘

1 row in set. Elapsed: 0.003 sec. 

I would have expected 150 as sumval in the result.