ClickHouse / ClickHouse/ClickHouse

Partition pruning throws Division by zero under use_skip_indexes = 0 on a sub-expression that only fails for a pruned-away partition

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

Description

With `use_skip_indexes = 0`, partition pruning evaluates a predicate sub-expression that fails for a pruned-away partition and throws — the query dies with `Code: 153. DB::Exception: Division by zero. (ILLEGAL_DIVISION)` even though the predicate `p != 1` excludes exactly the partition (`p = 1`) whose substituted constant makes `intDiv(1, p - 1)` unevaluable. With skip indexes enabled (the default) the same query correctly returns `0`.

This violates the contract stated by the test that guards this area (`tests/queries/0_stateless/04312_partition_constants_folding_eval_error.sql`, added in #104582): "Constant folding in index analysis must never throw when a substituted partition constant makes a sub-expression fail to evaluate ... and analysis must not throw even when all index analysis is disabled." The test covers `use_skip_indexes = 0` only in combination with `use_partition_pruning = 0` — and disabling partition pruning is precisely what suppresses the failure, so the throwing combination is untested.

**How to reproduce** (the test's own table, single stock server):

```sql
CREATE TABLE t_folding_eval_error (p Int64, b UInt64)
ENGINE = MergeTree ORDER BY b PARTITION BY p
SETTINGS index_granularity = 1;

INSERT INTO t_folding_eval_error VALUES (1, 10);

SELECT count() FROM t_folding_eval_error WHERE p != 1 AND intDiv(1, p - 1) > 0;
-- 0, correct (use_skip_indexes = 1 default)

SELECT count() FROM t_folding_eval_error WHERE p != 1 AND intDiv(1, p - 1) > 0
SETTINGS use_skip_indexes = 0;
-- Code: 153. DB::Exception: Division by zero. (ILLEGAL_DIVISION)
```

Deterministic, 20/20 runs. The suppression matrix isolates the mechanism:

| settings on top of defaults | result |
|---|---|
| (none) | `0` |
| `use_skip_indexes = 0` | throws 153 |
| `use_skip_indexes = 0, use_constant_folding_in_index_analysis = 1` | throws 153 |
| `use_skip_indexes = 0, use_primary_key = 0` | throws 153 |
| `use_skip_indexes = 0, use_partition_pruning = 0` | `0` |

So the throw comes from the partition-pruning evaluation, `use_constant_folding_in_index_analysis` does not participate (its default is `0`), and the skip-index analysis path is what shields partition pruning when it is on. Both analyzer settings behave identically (`enable_analyzer = 0` also throws).

**Expected behavior**

`0`, per the guard test's own comment: the result must match the unfolded path and analysis must never throw on a sub-expression whose failing evaluation belongs to a partition the predicate excludes.

**Versions**: master `26.8.1.1589`.

Related: https://github.com/ClickHouse/ClickHouse/pull/104582

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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.