ClickHouse / ClickHouse/ClickHouse
case insensitive search is not working with tokenbf_v1 on plain string column
- 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
Assessment
This issue has not been assessed yet.