ClickHouse DB::Exception: Decimal overflow

The "DB::Exception: Decimal overflow" error in ClickHouse occurs when the result of a decimal arithmetic operation exceeds the maximum precision that the target Decimal type can represent. The DECIMAL_OVERFLOW error code is raised when a multiplication, addition, subtraction, or other arithmetic operation on Decimal columns produces a value too large for the declared precision and scale.

Impact

The query that triggers the overflow will fail and return no results. This can affect financial calculations, aggregation pipelines, and any workload that relies on exact decimal arithmetic. If the overflow happens in an INSERT with a computed expression, the entire batch will be rejected.

Common Causes

1. Multiplying Decimal32/Decimal64 values where the result exceeds the type's range (overflow is checked for these types; for Decimal128/Decimal256 arithmetic, ClickHouse returns an incorrect result rather than raising this error)
2. Aggregating large numbers of Decimal values with SUM, leading to a result that exceeds the precision
3. Casting or converting a value to a Decimal type with insufficient precision (this path does raise the error for Decimal128/Decimal256 as well)
4. Intermediate calculations in expressions that temporarily exceed the decimal precision even if the final result would fit
5. Using Decimal32 or Decimal64 for values that require higher precision
6. Division operations where the result requires more decimal places than the target scale allows

Troubleshooting and Resolution Steps

1. Identify the query and expression causing the overflow:

   SELECT query_id, query, exception, event_time
   FROM system.query_log
   WHERE exception LIKE '%DECIMAL_OVERFLOW%'
   ORDER BY event_time DESC
   LIMIT 10;
   

2. Check the Decimal types of the columns involved:

   SELECT name, type
   FROM system.columns
   WHERE database = 'my_db' AND table = 'my_table'
     AND type LIKE '%Decimal%';
   

3. Use a wider Decimal type to accommodate larger values:

   -- Switch from Decimal64 to Decimal128
   ALTER TABLE my_table MODIFY COLUMN amount Decimal128(4);
   

4. Set the decimal_check_overflow setting to 0 if you want to skip overflow checks for Decimal32/Decimal64 operations (use with caution; this disables the check rather than truncating, so an out-of-range value yields an incorrect result):

   SET decimal_check_overflow = 0;
   SELECT col1 * col2 FROM my_table;
   

   Note that overflow checking is only implemented for Decimal32 and Decimal64. For Decimal128 and Decimal256, ClickHouse does not check arithmetic overflow at all and returns an incorrect result instead of raising this error; the DECIMAL_OVERFLOW error for those wider types comes from casts/conversions (for example toDecimal128/CAST), not from arithmetic.

5. Cast intermediate results to a wider type within your query:

   SELECT toDecimal128(col1, 4) * toDecimal128(col2, 4) AS result
   FROM my_table;
   

6. For aggregations, cast before aggregating to ensure the accumulator has enough precision:

   SELECT sum(toDecimal128(amount, 2)) AS total
   FROM my_table;
   

7. Check the actual range of values in the column to determine the appropriate precision:

   SELECT min(amount), max(amount), max(abs(amount))
   FROM my_table;
   

Best Practices

• Choose Decimal types with enough headroom for your expected data range. If values can reach millions and you need 4 decimal places, Decimal64(4) (max ~9.2 * 10^14) may not be enough; consider Decimal128(4).
• Be mindful of precision expansion in multiplication: multiplying Decimal(P1, S1) by Decimal(P2, S2) produces a result with precision P1+P2 and scale S1+S2.
• Use explicit casts in complex arithmetic expressions to control intermediate precision.
• Avoid decimal_check_overflow = 0 in production unless you fully understand the implications of silent truncation on your data accuracy.
• Regularly review the value ranges in Decimal columns to catch growing data before it hits overflow limits.
• For purely aggregation-based workloads, consider using Float64 if exact decimal precision is not strictly required.

Frequently Asked Questions

Q: What is the maximum value a Decimal type can hold in ClickHouse?
A: Decimal32 supports up to 9 significant digits, Decimal64 up to 18 digits, Decimal128 up to 38 digits, and Decimal256 up to 76 digits. The actual maximum value depends on the scale (number of decimal places) you configure.

Q: Does setting decimal_check_overflow = 0 risk data corruption?
A: It does not corrupt stored data, but it disables the overflow check for Decimal32/Decimal64 operations, so an out-of-range result is returned silently instead of raising an error, producing mathematically incorrect values. This is particularly dangerous for financial data where precision matters. (Decimal128/Decimal256 arithmetic is never overflow-checked regardless of this setting.)

Q: Can I mix Decimal types in arithmetic expressions?
A: Yes, ClickHouse will automatically promote to the wider type. However, the resulting precision may still exceed the maximum if both operands are already wide Decimal types. Explicit casting to a Decimal256 may be necessary in extreme cases.