ClickHouse / ClickHouse/ClickHouse

Text index preprocessor is dropped wherever index analysis does not run (SELECT list, use_skip_indexes = 0) while the tokenizer rewrite still fires — same expression returns different results by position, and disabling skip indexes silently loses rows

Open
#117,077 1 comment 0 reactions 1 assignee Claimed by @ahmadov View on GitHub
bug-unreleased comp-query-optimizer comp-text-index
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

### Describe what's wrong

**With a `text` index that declares a `preprocessor`, the rewrite that makes text-search functions follow the index definition applies the index tokenizer (and postprocessor) to every occurrence of the function, but applies the preprocessor only to an index-analyzed predicate inside a filter DAG. Every occurrence where the rewrite fires without index analysis — the SELECT list, or a WHERE clause under `use_skip_indexes = 0` — is evaluated with a third semantics that matches neither the plain function nor the index definition: the haystack is split with the index tokenizer, but neither the haystack nor the needle goes through the preprocessor.**

Two user-visible consequences:

1. With the common configuration `tokenizer = splitByNonAlpha, preprocessor = lower(s)`, `WHERE hasToken(s, 'foo')` is case-insensitive while `SELECT hasToken(s, 'foo')` from the same table is case-sensitive. The same expression selects a row in WHERE and simultaneously evaluates to 0 for that row in the SELECT list. `hasAnyTokens`, `hasAllTokens` and `hasPhrase` behave the same way.

2. With a non-default tokenizer, `use_skip_indexes = 0` changes the result of a WHERE clause: rows are silently lost relative to the default, and the reduced result matches neither the index semantics nor the plain function semantics, so turning the optimization off is not a fallback to either well-defined behavior.

- **Root cause:** in `optimizeDirectReadFromTextIndex.cpp` (`processTextIndexFunction`), `apply_preprocessor` requires `is_filter_dag && condition.is_index_analyzed`, while `apply_tokenizer` and `apply_postprocessor` are unconditional. The rewrite itself runs on non-filter DAGs too (it is needed there to inject the index tokenizer into the 2-argument forms), so those occurrences get tokenizer-without-preprocessor semantics. When `apply_preprocessor` is true the needle constant is also preprocessed (`preprocessor->processConstant`); when it is false the needle stays raw as well.

### Which ClickHouse versions are affected?

Reproduced on a master build (26.9 pre-release). The gating is unchanged on current master (`src/Processors/QueryPlan/Optimizations/optimizeDirectReadFromTextIndex.cpp`, the `apply_preprocessor` initialization).

### How to reproduce

Plain `clickhouse local`, all settings at defaults except the experimental gate.

Case A — the common configuration, WHERE vs SELECT list:

```sql
SET allow_experimental_full_text_index = 1;

CREATE TABLE t_common (id UInt64, s String,
INDEX idx(s) TYPE text(tokenizer = splitByNonAlpha, preprocessor = lower(s))
) ENGINE = MergeTree ORDER BY id;
INSERT INTO t_common VALUES (1, 'FOO bar'), (2, 'foo bar');

SELECT groupArray(id) FROM t_common WHERE hasToken(s, 'foo'); -- [1,2] (case-insensitive: preprocessor applied)
SELECT id, hasToken(s, 'foo') FROM t_common ORDER BY id; -- 1 -> 0, 2 -> 1 (case-sensitive: preprocessor dropped)
```

The WHERE clause matches both rows; the identical expression in the SELECT list returns 0 for row 1.

Case B — non-default tokenizer, three different answers for one expression, and row loss under `use_skip_indexes = 0`:

```sql
SET allow_experimental_full_text_index = 1;

CREATE TABLE t (id UInt64, s String,
INDEX idx(s) TYPE text(tokenizer = splitByString(['|']), preprocessor = lower(s))
) ENGINE = MergeTree ORDER BY id;
INSERT INTO t VALUES (1, 'A b|c'), (2, 'a b|c'), (3, 'zzz');

SELECT groupArray(id) FROM t WHERE hasAnyTokens(s, ['a b']); -- [1,2] (index semantics)
SELECT id, hasAnyTokens(s, ['a b']) FROM t ORDER BY id; -- (0,1,0) (tokenizer applied, preprocessor dropped)

SET use_skip_indexes = 0;
SELECT groupArray(id) FROM t WHERE hasAnyTokens(s, ['a b']); -- [2] (row 1 silently lost)
```

Reference vectors for the SELECT-list column over rows (1,2,3): the index definition (`lower`, then split by `|`) gives (1,1,0); the plain function without any index gives (0,0,0) — verified on an identical table with no index, where the WHERE clause returns no rows and the SELECT list is all zeros; the actual output (0,1,0) matches neither.

`EXPLAIN actions = 1` shows the mechanism. The SELECT-list occurrence is rewritten to the 3-argument form with the index tokenizer injected and no `lower` anywhere:

```
Output: id, hasAnyTokens(s, ['a b'], 'splitByString([\'|\'])')
```

while the default WHERE path reads the index virtual column (`__text_index_idx_hasAnyTokens_...`), and the `use_skip_indexes = 0` WHERE path gets the same preprocessor-less 3-argument form as its `Prewhere filter column`, which is why it loses row 1.

### Expected behavior

An expression over an indexed column evaluates to the same value in every position of the same query, and `use_skip_indexes` does not change query results. Wherever the rewrite adopts the index semantics for these functions, it applies the complete index definition — preprocessor, tokenizer and postprocessor — to the occurrence.

### Error message and/or stacktrace

No error. Wrong results are returned silently.

### Additional context

This is distinct from the accepted index-vs-scan divergence of #105231 (closed as by-design) and #113855 (closed as not-planned). Those describe a predicate acquiring the index semantics instead of the plain-function semantics, and the closure of #113855 relies on the statement that the same expression evaluated outside an index-eligible position gives the plain-function answer. That baseline is what breaks here: the non-filter occurrence does not fall back to the plain function — the tokenizer rewrite still fires and only the preprocessor is dropped, producing a result that is wrong under both declared semantics, as the (0,1,0) vector above shows. No setting recovers the affected occurrences: the rewrite runs regardless of `query_plan_direct_read_from_text_index` and `use_skip_indexes`, and disabling the index analysis is exactly what triggers the wrong form. #116476 and #117075 concern a different mechanism in the same optimization (the postprocessed-haystack rejoin for phrase matching) and do not involve the preprocessor gating.

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.