ClickHouse / ClickHouse/ClickHouse

Primary-key analysis evaluates `intDiv(10, x)` at a key-range endpoint `x = 0` and throws `Division by zero` although `x != 0 AND ...` short-circuits at row level

Open
#119,597 3 comments 0 reactions 0 assignees View on GitHub
comp-mergetree minor potential bug
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

### Describe what's wrong

Primary-key index analysis applies the monotonic-function chain of a key condition to the key-range endpoints, and for `intDiv(, x)` (or `divide(, x)` over a `Decimal` key) it evaluates the function at an endpoint `x = 0` whenever the primary key holds the value `0` at a granule boundary. `intDiv(10, 0)` throws, so a query whose row-level predicate is perfectly safe (`x != 0 AND intDiv(10, x) > 4`, `x > 0 AND intDiv(10, x) > 4`, even `x > 5000 AND intDiv(10, x) > 4` — all short-circuited by `AND` at row level) fails with `Code: 153. DB::Exception: Division by zero. (ILLEGAL_DIVISION)`. The same query with `use_primary_key = 0` returns the right answer, and the row-level evaluation never divides by zero. Only the analysis path throws; the partition-pruning face of the same problem is tracked in #101481 (no companion filter, `single_point`) and #115271 (`use_skip_indexes = 0`), while this face needs nothing but a table ordered by the column and default settings.

### How to reproduce

```sql
CREATE TABLE t (x Int32) ENGINE = MergeTree ORDER BY x;
INSERT INTO t SELECT number FROM numbers(100000);

SELECT count() FROM t WHERE x != 0 AND intDiv(10, x) > 4;
-- Code: 153. DB::Exception: Division by zero. (ILLEGAL_DIVISION)
SELECT count() FROM t WHERE x > 0 AND intDiv(10, x) > 4;
-- Code: 153
SELECT count() FROM t WHERE x > 5000 AND intDiv(10, x) > 4;
-- Code: 153 (the query cannot match a single row)

SELECT count() FROM t WHERE x != 0 AND intDiv(10, x) > 4 SETTINGS use_primary_key = 0;
-- 2
SELECT count() FROM t WHERE x != 0 AND 10 / x > 4;
-- 2 (Float division has no failure point, so the same analysis is harmless)
```

The trigger is the value `0` sitting at a key-range endpoint: with rows `1..100000` (no zero) the query returns `2`, with rows `-50000..49999` (zero inside a granule, not on its boundary) it returns `2`, with rows `0..99999` it throws; with `index_granularity = 1` and rows `(0, 1, 2)` or `(-2, -1, 0, 1, 2)` it throws while `(-2, -1, 1, 2)` returns `2`. A `Decimal(9, 2)` key with `divide(10, x) > 4` throws the same way (`index_granularity = 1`, rows `0, 1, 2`). `enable_analyzer = 0`, `query_plan_enable_optimizations = 0` and `use_lightweight_primary_key_index_analysis = 0` do not help.

Server-side stack trace of the exception:

```
DB::throwIfDivisionLeadsToFPE
DB::impl_::BinaryOperation<..., DB::DivideIntegralImpl<...>>::process
DB::FunctionBinaryArithmetic::executeImpl2
DB::FunctionWithOptionalConstArg::execute src/Storages/MergeTree/KeyCondition.cpp
DB::applyFunction src/Storages/MergeTree/KeyCondition.cpp
DB::KeyCondition::applyMonotonicFunctionsChainToRange src/Storages/MergeTree/KeyCondition.cpp
DB::KeyCondition::checkInHyperrectangle
DB::KeyCondition::checkInRange
DB::MergeTreeDataSelectExecutor::markRangesFromPKRange
DB::MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes
DB::ReadFromMergeTree::selectRangesToRead
```

### Does it reproduce on the most recent release?

Yes: master 26.9.1.1224 and the releases 25.3.14, 25.8.33, 26.3.33, 26.6.4, 26.7.6 (checked on fiddle.clickhouse.com) — long-standing, not a regression of the recent monotonicity fixes.

### Expected behavior

`2` (the rows `x = 1` and `x = 2`). Index analysis must not throw for a sub-expression that the row-level predicate never evaluates on the failing value; when the function is undefined at an endpoint of the range the chain should be treated as non-monotonic (the range kept), exactly as the existing guard test `04312_partition_constants_folding_eval_error` demands for the partition-key face ("analysis must never throw"). `tests/queries/0_stateless/04073_divide_intdivide_monotonicity_key_condition.sql` covers only `PARTITION BY x` tables, where the partition minmax index removes the `x = 0` partition before the primary-key ranges are looked at.

Related: https://github.com/ClickHouse/ClickHouse/issues/101481
Related: https://github.com/ClickHouse/ClickHouse/issues/115271
Related: https://github.com/ClickHouse/ClickHouse/pull/112156

Found by an automatic optimizer-testing framework (differential testing of optimizer settings, query plans, and equivalent rewrites).

Contributor guide

Open the contributing guide

Research direction

Start in src/Storages/MergeTree/KeyCondition.cpp, especially applyMonotonicFunctionsChainToRange and the surrounding range checks, then run the SQL reproduction from the issue. Compare the behavior with tests/queries/0_stateless/04073_divide_intdivide_monotonicity_key_condition.sql and add coverage for an ORDER BY key whose endpoint is zero. Done means the query returns 2 without a division-by-zero exception during primary-key analysis.

Written by the indexing model from the issue text.

Assessment

Tech stack
cpp, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
62/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.