ClickHouse AggregatingMergeTree State/Merge Pattern

Problem

When querying tables or materialized views that use AggregatingMergeTree with state functions (sumState, uniqState, countState), queries fail or return wrong results because the columns contain aggregate state objects, not regular numeric values.

Context / Trigger Conditions

• Query fails with type errors when using sum() on AggregateFunction columns
• Creating views that query AggregatingMergeTree materialized views
• Column types show as AggregateFunction(sum, UInt64) instead of UInt64
• Migration creates a view joining with an existing aggregating materialized view
• Getting NULL or unexpected values when aggregating pre-aggregated columns

Root Cause

AggregatingMergeTree stores intermediate aggregate states, not final values. When you define:

CREATE MATERIALIZED VIEW stats
ENGINE = AggregatingMergeTree()
ORDER BY (user_id)
AS SELECT
    user_id,
    sumState(amount) as total_amount,      -- AggregateFunction(sum, UInt64)
    uniqState(session_id) as unique_sessions, -- AggregateFunction(uniq, String)
    countState() as event_count           -- AggregateFunction(count)
FROM events
GROUP BY user_id;

The columns total_amount, unique_sessions, and event_count are NOT regular numbers. They're binary blobs representing the intermediate state of the aggregation.

Wrong:

SELECT user_id, sum(total_amount) FROM stats GROUP BY user_id;
-- Error: cannot use sum() on AggregateFunction type

Correct:

SELECT user_id, sumMerge(total_amount) FROM stats GROUP BY user_id;
-- Returns the finalized numeric value

Solution

Mapping State Functions to Merge Functions

State Function	Merge Function	Purpose
sumState(x)	sumMerge(x)	Sum aggregation
countState()	countMerge(x)	Count aggregation
uniqState(x)	uniqMerge(x)	Unique count (HyperLogLog)
avgState(x)	avgMerge(x)	Average
minState(x)	minMerge(x)	Minimum
maxState(x)	maxMerge(x)	Maximum
anyState(x)	anyMerge(x)	Any value
groupArrayState(x)	groupArrayMerge(x)	Array aggregation

Example Fix

Before (broken):

CREATE VIEW leaderboard AS
SELECT
    stats.user_id,
    sum(stats.daily_views) AS views,        -- WRONG
    sum(stats.daily_unique) AS uniques,     -- WRONG
    sum(stats.videos_watched) AS videos     -- WRONG
FROM daily_stats stats
GROUP BY stats.user_id;

After (fixed):

CREATE VIEW leaderboard AS
SELECT
    stats.user_id,
    sumMerge(stats.daily_views) AS views,        -- Correct
    uniqMerge(stats.daily_unique) AS uniques,    -- Correct
    countMerge(stats.videos_watched) AS videos   -- Correct
FROM daily_stats stats
GROUP BY stats.user_id;

Identifying Affected Columns

Check the table schema to see which columns are aggregate states:

DESCRIBE TABLE your_table;

Output shows column types like:

daily_views       AggregateFunction(sum, UInt64)
daily_unique      AggregateFunction(uniq, String)
videos_watched    AggregateFunction(count)

Any column with AggregateFunction(...) type requires the corresponding *Merge() function.

Verification

1. Check your materialized view definition for *State() functions
2. Ensure all queries use matching *Merge() functions
3. Test the query returns expected numeric values, not NULL or binary blobs

-- Should return actual numbers
SELECT sumMerge(total_views), uniqMerge(unique_visitors)
FROM aggregated_stats
WHERE stat_date >= today() - 7;

Example

Migration 020 (creates the aggregating table):

CREATE MATERIALIZED VIEW creator_daily_stats
ENGINE = AggregatingMergeTree()
ORDER BY (video_author_pubkey, stat_date)
AS SELECT
    video_author_pubkey,
    toDate(created_at) as stat_date,
    sumState(view_count) as daily_views,           -- State function
    uniqState(viewer_hash) as daily_unique_viewers, -- State function
    sumState(toFloat64(total_loops)) as daily_loops,-- State function
    countState() as videos_watched                  -- State function
FROM view_counts
GROUP BY video_author_pubkey, toDate(created_at);

Migration 033 (queries the aggregating table - FIXED):

CREATE VIEW leaderboard_creators_day AS
SELECT
    cds.video_author_pubkey AS pubkey,
    p.name,
    sumMerge(cds.daily_views) AS views,            -- Merge function
    uniqMerge(cds.daily_unique_viewers) AS unique_viewers, -- Merge function
    sumMerge(cds.daily_loops) AS loops,            -- Merge function
    countMerge(cds.videos_watched) AS videos_with_views -- Merge function
FROM creator_daily_stats cds
LEFT JOIN user_profiles p ON cds.video_author_pubkey = p.pubkey
WHERE cds.stat_date >= today() - 1
GROUP BY cds.video_author_pubkey, p.name
ORDER BY views DESC;

Notes

• SummingMergeTree is different: It stores regular values and sums them during merges. With SummingMergeTree, you use regular sum() in queries. Only AggregatingMergeTree uses the State/Merge pattern.

• Why use AggregatingMergeTree?: For unique counts (uniq), you can't simply sum the counts from different parts—that would overcount. AggregatingMergeTree preserves the HyperLogLog state so merging gives correct unique counts across partitions.

• Performance: The *Merge() functions are efficient—they're designed to combine pre-computed aggregate states, not reprocess raw data.

• Migration ordering matters: If migration A creates an AggregatingMergeTree view, and migration B creates a view that queries it, migration B must use *Merge() functions.

References

• ClickHouse AggregatingMergeTree
• ClickHouse Aggregate Function Combinators
• ClickHouse -State and -Merge combinators