ClickHouse DB::Exception: Division by zero (Code 153)

Code: 153. DB::Exception: Division by zero (ILLEGAL_DIVISION). This ClickHouse error is raised when an integer division (intDiv, %, modulo, intDivOrZero on a min-int corner) sees a zero divisor and there is no safe-variant function chosen by the query. Note that floating-point divide does not raise this error: it returns inf, -inf, or nan per IEEE 754. The error is specific to integer paths and to a few corner cases like dividing the minimum negative integer by -1.

What This Error Means

ClickHouse distinguishes integer division from floating-point division. intDiv(a, b) and the SQL % operator throw ILLEGAL_DIVISION when b = 0 or when the quotient would overflow (e.g., INT_MIN / -1). The plain divide(a, b) operator (the SQL /) coerces both arguments to Float64 and returns +inf, -inf, or nan for zero divisors - it does not throw. Decimal types behave like the integer path: they throw on zero. So the error always points at one of intDiv, %, modulo, intDivOrZero overflow, or a Decimal-typed division.

The fix is to choose a function that handles zero explicitly: intDivOrZero(a, b) returns 0, intDivOrNull(a, b) and divideOrNull(a, b) return NULL, or wrap the divisor with NULLIF(b, 0) to convert zero to NULL and propagate NULL downstream.

Common Causes

intDiv(x, y) or x / y where y is an aggregated column that produced zero. Common in conversion-rate calculations: intDiv(conversions, visits) when visits is zero in some row.
SQL modulo x % 0 or modulo(x, 0) after a filter that did not actually exclude zeros. Confirm with SELECT count() FROM t WHERE divisor_column = 0.
Decimal division: toDecimal64(x, 4) / toDecimal64(y, 4) with y = 0. Decimal divisions also throw, unlike Float64 divisions.
Window-function quotients (a / windowed_b) where the window frame is empty or yields a zero sum.
intDivOrZero with the corner case intDivOrZero(INT_MIN, -1) - the result does not fit in the dividend's signed type and still throws.

How to Fix Division by Zero

Choose the right safe-variant function based on the type you want returned:

-- Integer division returning 0 for zero divisors:
SELECT intDivOrZero(conversions, visits) FROM funnel;
-- Integer division returning NULL for zero divisors:
SELECT intDivOrNull(conversions, visits) FROM funnel;
-- Floating-point division returning NULL for zero divisors:
SELECT divideOrNull(revenue, sessions) FROM funnel;

Or use NULLIF to convert zero to NULL, which propagates through any downstream arithmetic:
```
SELECT revenue / NULLIF(sessions, 0) AS revenue_per_session FROM funnel;
```
The output column becomes nullable. Combine with coalesce(... , 0) if you want a numeric default.

Use IF/CASE for full control:

SELECT if(sessions > 0, revenue / sessions, 0) AS rps FROM funnel;
SELECT CASE WHEN sessions > 0 THEN revenue / sessions ELSE 0 END FROM funnel;

Filter zeros out at the source when zero divisors are not meaningful business data:
```
SELECT revenue / sessions FROM funnel WHERE sessions > 0;
```
Switch from intDiv to divide if floating-point semantics are acceptable. The Float64 path returns inf/nan, which downstream isFinite() and isNaN() can filter cleanly without raising.
For Decimal arithmetic, multiply and divide manually with an explicit zero check, or convert to Float64 for ratios where the IEEE result is acceptable.

Root-Cause Analysis

Find the offending query through system.query_log and then identify which expression caused the throw:

-- Recent code-153 failures
SELECT event_time, user, query_id, query, exception
FROM system.query_log
WHERE event_date >= today() - 1
  AND exception_code = 153
ORDER BY event_time DESC LIMIT 20;

-- Find columns with zero divisors that have been used in recent queries
SELECT count() AS rows_with_zero_divisor
FROM your_table
WHERE divisor_column = 0;

Preventive Measures

Make safe-variant functions the default in shared SQL libraries and dashboard tooling. divideOrNull and NULLIF cost almost nothing and prevent surprise 153 errors in production.
Add constraints in the upstream pipeline so divisor columns cannot be zero where the business model says they should not be (e.g., a session must have at least one impression). Validate during ETL into MergeTree.
Build a CI check that scans saved queries for raw intDiv(, %, and / against columns known to be sparse, and flags them in code review.
Use materialized columns or computed columns at write time for common ratios, with the zero-divisor case handled once at write rather than at every query.

Resolve `ILLEGAL_DIVISION` (Code 153) Automatically with Pulse

Pulse is an AI DBA for ClickHouse (and Kafka and Elasticsearch). When Code: 153. DB::Exception: Division by zero fires from a dashboard, ETL, or scheduled report in your environment, Pulse:

Continuously tracks exception_code = 153 events in system.query_log, the offending query_id, and the column referenced by intDiv, %, modulo, or Decimal /
Correlates the throw with row-level data drift - a new zero appearing in a divisor column, a window frame producing an empty sum, or an aggregated denominator collapsing - and ties it to the materialized view or upstream pipeline that introduced the zero
Identifies which of the five causes above applies - integer-path intDiv, raw modulo, Decimal division (which throws unlike Float64), windowed quotient over an empty frame, or the intDivOrZero(INT_MIN, -1) overflow corner
Recommends the precise rewrite - swap intDiv for intDivOrZero or intDivOrNull, wrap with NULLIF(divisor, 0), use if(divisor > 0, a/divisor, 0), or move ratios to a materialized column on MergeTree
Applies low-risk fixes automatically with your approval (proposing a PR against the saved-query repository) or flags affected dashboards before users see "report failed"

Pulse turns the manual system.query_log triage above into an agentic SRE workflow. Start a free trial.

Frequently Asked Questions

Q: What is the fastest way to diagnose Code 153 division-by-zero errors when they appear on a production dashboard?
A: Query system.query_log for exception_code = 153 to find the offending query, then run SELECT count() FROM your_table WHERE divisor_column = 0 to confirm the bad row pattern. For continuous coverage, Pulse is an AI DBA for ClickHouse that detects Code 153 in real time, points at the specific column producing zero divisors, and recommends the right safe-variant rewrite (intDivOrZero, divideOrNull, or NULLIF).

Q: What does ClickHouse error code 153 mean?
A: Code 153 is ILLEGAL_DIVISION. It is raised when an integer or Decimal division (intDiv, %, modulo, or Decimal /) sees a zero divisor, or when an integer division would overflow (e.g., INT_MIN / -1). Floating-point divide does not raise this error - it returns IEEE 754 special values.

Q: How do I avoid division by zero in ClickHouse?
A: Use a safe-variant function: intDivOrZero (returns 0), intDivOrNull or divideOrNull (returns NULL), or wrap the divisor with NULLIF(b, 0). For explicit control, use if(b != 0, a/b, default_value) or CASE WHEN b != 0 THEN a/b ELSE default END.

Q: Is there a tryDivide or tryDiv function in ClickHouse?
A: No - those names do not exist. The correct safe-variant function names are divideOrNull, intDivOrNull, and intDivOrZero. Some older blog posts mention tryDiv, but it is not in the ClickHouse arithmetic functions reference.

Q: How does ClickHouse handle floating-point division by zero?
A: divide(x, 0.0) or x / 0.0 returns +Infinity for positive x, -Infinity for negative x, and NaN for 0/0. This follows IEEE 754. If you want NULL instead of these special values, use divideOrNull or wrap with nullIf(isFinite(...)).

Q: Why does my query throw division by zero only sometimes?
A: The exception is raised per-row at runtime, not at planning time. If only a subset of rows has a zero divisor (a sparse column, or a new data anomaly), the query succeeds on most data and fails the moment the executor encounters the bad row. Use a safe-variant function or filter zeros out.

Q: Can ClickHouse be configured to return NULL or 0 globally for division by zero?
A: No - there is no global setting that changes division semantics. The safe behavior must be expressed per-function (intDivOrZero, divideOrNull) or per-expression (NULLIF, IF). This is intentional: silent zero substitution can mask real data bugs.

ClickHouse Coalesce Function - combine with NULLIF to provide defaults
ClickHouse CASE Expressions - explicit zero handling
ClickHouse MergeTree Engine - where divisor columns typically live
AggregatingMergeTree - rollups that may produce zero denominators
Memory Limit Exceeded - common co-occurring error in analytic queries
Read Timeout Error - distinct failure mode worth ruling out
ClickHouse Documentation Hub - index of all ClickHouse KB pages