ClickHouse / ClickHouse/ClickHouse

case insensitive search is not working with tokenbf_v1 on plain string column

Open
#79,592 2 comments 0 reactions 0 assignees View on GitHub
comp-skip-index external potential bug
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
20h 33m
Merged PRs (30d)
501

Description

### Company or project name

IBM

### Describe what's wrong

Fiddle: https://fiddle.clickhouse.com/2c9cb680-9d39-4916-8f88-31ff76e668b0

I'm working with version `24.8.12.28` but it also reproduces with the latest version and it seems the index is not applied for case insensitive search, even if it is specified with `lowerUTF8(v)` as suggested in the [function support documentation](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree#functions-support). I tried both, `ILIKE` and `hasTokenCaseInsensitive` but both approaches do not trigger the lower case index. Actually, only the index with the unmodified value is working as expected.

I've opened https://github.com/ClickHouse/ClickHouse/issues/79586 as well, suggesting that there is a defect on the `bloom_filter` as soon as `arrayMap` is used.
The commonality between both issues is that the index fails when functions are used in the index specification. Indices on plain values however work as expected.

Also, this issue might be related to https://github.com/ClickHouse/ClickHouse/issues/69773, while the mentioned issue is concerned with Arrays which are not officially supported by `tokenbf_v1` as per my understanding.

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

Yes

### How to reproduce

See https://fiddle.clickhouse.com/2c9cb680-9d39-4916-8f88-31ff76e668b0

### Expected behavior

According to the [function support documentation](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree#functions-support), `tokenbf_v1` should allow case-insensitive search, if the index is created on lowercase values. Therefore, my expectation would be that queries with `ILIKE` or `hasTokenCaseInsensitive` trigger the index use if it was specified with `lowerUTF8(v)`.

### Error message and/or stacktrace

_No response_

### Additional context

_No response_

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.