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