ClickHouse / ClickHouse/ClickHouse

Two skip indexes + an OR with a constant disjunct + a function-wrapped comparison (`toString(e) < 'c' AND (0 OR id != 5)`) return no rows: the combined skip-index AND/OR processing prunes every granule (`use_skip_indexes_for_disjunctions`)

Open
#119,528 1 comment 0 reactions 1 assignee Claimed by @shankar-iyer View on GitHub
comp-skip-index minor potential bug
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

### Company or project name

_No response_

### Describe what's wrong

With two skip indexes on a table, a `WHERE` of the form `(col1) < c AND (0 OR col2 != x)` returns **no rows**, although every row satisfies it. Each index on its own keeps the granule (`EXPLAIN indexes = 1` shows `Granules: 1/1` for both), but the `` step ("Final set of granules after AND/OR processing") drops every granule (`Granules: 0/1`). `use_skip_indexes_for_disjunctions = 0` (and `use_skip_indexes = 0`) return the correct rows, so the defect is in the per-granule AND/OR combination of the individual indexes' results when one disjunct is a constant (`0`, `1 = 2`, an inverted `BETWEEN` folded to `0`, ...) and another atom on the *other* indexed column cannot be evaluated by that index (a non-monotonic function wrapper such as `toString`).

The same shape without the function wrapper (`e < 'c' AND (0 OR id != 5)`) is correct, and so is the shape with a single skip index, so the wrong result needs: two skip indexes, an OR containing a constant disjunct, and a function-wrapped comparison on one of the indexed columns. Row loss is silent.

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

Yes, on master `26.9.1.1184` and on every cached master build back to `26.9.1.1012`. Present at `use_skip_indexes_for_disjunctions = 1` (the default).

### How to reproduce

```sql
DROP TABLE IF EXISTS t_skip_or0;
CREATE TABLE t_skip_or0
(
id UInt32,
e Enum8('a' = 1, 'b' = 2, 'c' = 3),
INDEX ie e TYPE minmax GRANULARITY 1,
INDEX ii id TYPE minmax GRANULARITY 1
)
ENGINE = MergeTree ORDER BY tuple();
INSERT INTO t_skip_or0 SELECT number, ['a', 'b', 'c'][number % 3 + 1] FROM numbers(1000);

SELECT count() FROM t_skip_or0 WHERE toString(e) < 'c' AND (0 OR id != 5);
-- 0 <- wrong
SELECT count() FROM t_skip_or0 WHERE toString(e) < 'c' AND (0 OR id != 5) SETTINGS use_skip_indexes_for_disjunctions = 0;
-- 667 <- correct
SELECT count() FROM t_skip_or0 WHERE toString(e) < 'c' AND (0 OR id != 5) SETTINGS use_skip_indexes = 0;
-- 667

-- the function wrapper may sit on the other side as well:
SELECT count() FROM t_skip_or0 WHERE e < 'c' AND (toString(id) != '5' OR 0);
-- 0 <- wrong (667 with use_skip_indexes_for_disjunctions = 0)

-- correct shapes on the same table, for contrast:
SELECT count() FROM t_skip_or0 WHERE e < 'c' AND (0 OR id != 5); -- 667
SELECT count() FROM t_skip_or0 WHERE toString(e) < 'c' AND (0 OR id != 5) SETTINGS ignore_data_skipping_indices = 'ii'; -- 667 (one index only)
SELECT count() FROM t_skip_or0 WHERE toString(e) < 'c' AND (0 OR id != 5) SETTINGS ignore_data_skipping_indices = 'ie'; -- 667
```

`EXPLAIN indexes = 1` for the failing query:

```
Prewhere filter column: (0 OR id != 5) AND toString(e) < 'c'
Indexes:
Skip
Name: ie
Description: minmax GRANULARITY 1
Condition: (toString(e) in (-Inf, 'c'))
Parts: 1/1
Granules: 1/1
Skip
Name: ii
Description: minmax GRANULARITY 1
Condition: (id not in [5, 5])
Parts: 1/1
Granules: 1/1
Skip
Name:
Description: Final set of granules after AND/OR processing
Parts: 1/1
Granules: 0/1
```

The original finding had the constant disjunct written as an inverted `BETWEEN` (`dt BETWEEN toDateTime('2024-01-13 12:00:00') AND toDateTime('2024-01-03 20:00:00')`, folded to `0`) and `set(64)` indexes in addition to the `minmax` ones; the shape above is the minimum that still fails. The same result comes from `secondary_indices_enable_bulk_filtering = 0`, `use_skip_indexes_on_data_read = 0`, `optimize_move_to_prewhere = 0` and `enable_analyzer = 0`, so it is not specific to those paths.

### Expected behavior

`667` rows for both failing queries. A constant-false disjunct must not turn the OR into "no granule can match" for granules where the other disjunct cannot be excluded.

### Additional context

Related: https://github.com/ClickHouse/ClickHouse/issues/106362 (closed; the nested-OR over-pruning in the same disjunction-tracking path, fixed by https://github.com/ClickHouse/ClickHouse/pull/103929 — this shape still fails after it)

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.