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
- 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
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