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`)
- 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
Assessment
This issue has not been assessed yet.