ClickHouse DB::Exception: Cannot add different aggregate states

The "DB::Exception: Cannot add different aggregate states" error in ClickHouse occurs when you attempt to merge or combine aggregate function states that are incompatible. Aggregate states in ClickHouse are binary representations of partial aggregation results, and they can only be merged if they were produced by the same aggregate function with the same parameters and input types. The error code is CANNOT_ADD_DIFFERENT_AGGREGATE_STATES.

Impact

The query or merge operation fails. This commonly affects queries reading from AggregatingMergeTree tables, materialized views with aggregate state columns, or manual use of -State and -Merge combinators. If the incompatible states are stored in a table, the issue may persist until the data is corrected.

Common Causes

1. Schema change in AggregatingMergeTree table -- if the aggregate function type of a column was changed (e.g., from sumState(UInt32) to sumState(UInt64)), existing parts contain states that are incompatible with the new definition.
2. Materialized view definition mismatch -- the materialized view produces aggregate states with a different function or type than what the target table expects.
3. Merging states from different functions -- attempting to merge a sumState with an avgState, for example.
4. Type mismatch in the aggregated column -- sumState(UInt32) and sumState(Float64) produce different state formats even though they use the same function.
5. ClickHouse version difference -- in rare cases, aggregate state binary formats can change between major versions, making old states incompatible with new code.

Troubleshooting and Resolution Steps

1. Check the column type definition on the target table:

   SELECT name, type FROM system.columns
   WHERE database = 'default' AND table = 'your_agg_table'
   AND type LIKE 'AggregateFunction%';
   

2. Verify the materialized view produces compatible states:

   SHOW CREATE TABLE your_materialized_view;
   

   Compare the aggregate function signatures in the MV's SELECT with the target table's column types.

3. Ensure function and type consistency. The function name, parameters, and input types must all match:

   -- Target table column type
   -- AggregateFunction(sum, UInt64)
   
   -- MV must produce exactly: sumState(column_of_type_UInt64)
   -- Not: sumState(column_of_type_UInt32)  -- different input type
   -- Not: avgState(column_of_type_UInt64)  -- different function
   

4. If a schema change caused the issue, migrate the data:

   -- Create new table with correct types
   CREATE TABLE new_agg_table (
       key String,
       total AggregateFunction(sum, UInt64)
   ) ENGINE = AggregatingMergeTree() ORDER BY key;
   
   -- Re-aggregate from source data
   INSERT INTO new_agg_table
   SELECT key, sumState(toUInt64(value)) FROM source_table GROUP BY key;
   
   -- Swap tables
   RENAME TABLE your_agg_table TO old_agg_table, new_agg_table TO your_agg_table;
   

5. For version upgrade issues, rebuild aggregate tables from source data:

   TRUNCATE TABLE your_agg_table;
   INSERT INTO your_agg_table
   SELECT ... sumState(...), avgState(...) FROM source_table GROUP BY ...;
   

6. Check for parts with old schema data:

   SELECT name, modification_time, rows
   FROM system.parts
   WHERE database = 'default' AND table = 'your_agg_table' AND active
   ORDER BY modification_time;
   

   Parts created before a schema change may contain incompatible states.

Best Practices

• Define AggregateFunction column types explicitly and precisely, including the input types: AggregateFunction(sum, UInt64) rather than relying on type inference.
• When changing aggregate function definitions, always migrate data rather than altering column types in place.
• Keep materialized view definitions in sync with their target tables. If you change one, update the other.
• Test aggregate state compatibility in a staging environment before applying schema changes to production.
• Document the exact aggregate function signatures used in each AggregatingMergeTree table for future reference.
• When upgrading ClickHouse across major versions, test that existing aggregate states can be read correctly before migrating production.

Frequently Asked Questions

Q: Can I cast one aggregate state type to another?
A: No. Aggregate states have opaque binary formats that differ between functions and input types. There is no cast or conversion operation between different state types. You must re-aggregate from the source data.

Q: Why does sumState(UInt32) differ from sumState(UInt64)?
A: The internal representation of the aggregate state includes the data type of the accumulated value. sumState(UInt32) stores a UInt32 accumulator, while sumState(UInt64) stores a UInt64 accumulator. These binary representations are not interchangeable.

Q: Can this error occur during background merges?
A: Yes. If parts contain incompatible aggregate states (e.g., after a schema change), background merges will fail. This can cause the number of parts to grow. Fix the issue by migrating data to a correctly defined table.

Q: How do I prevent this when using materialized views?
A: Ensure the SELECT clause in your materialized view exactly matches the column types of the target table. Use explicit type casts in the MV definition if needed: sumState(toUInt64(amount)) to match an AggregateFunction(sum, UInt64) column.