ClickHouse / ClickHouse/ClickHouse

How to use lowercase index on map values.

Open
#69,773 4 comments 0 reactions 0 assignees View on GitHub
comp-skip-index external question
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

### Company or project name

_No response_

### Question

I am trying to create a lowercase index on map values, so that I case use LIKE operator for case insensitive search as ILIKE doesn't use index.

* Table creation
```
CREATE TABLE test_table
(
id UInt64,
value Map(String, String),
index value_key_index mapKeys(value) TYPE tokenbf_v1(1024, 4, 0) GRANULARITY 1,
index value_value_index arrayMap(x -> lower(x), mapValues(value)) TYPE tokenbf_v1(1024, 4, 0) GRANULARITY 1
) ENGINE=MergeTree ORDER BY id;
```
* Sample data
```
INSERT INTO test_table
SELECT number as id, map(toString(number), toString(number)) as value
FROM numbers(1000000);
```

* Sample queries
```
EXPLAIN indexes = 1 select * from test_table where value['10000'] = lower('10000')
┌─explain─────────────────────────────────────────┐
1. │ Expression ((Project names + Projection)) │
2. │ Expression │
3. │ ReadFromMergeTree (default.test_table) │
4. │ Indexes: │
5. │ PrimaryKey │
6. │ Condition: true │
7. │ Parts: 1/1 │
8. │ Granules: 123/123 │
9. │ Skip │
10. │ Name: value_key_index │
11. │ Description: bloom_filter GRANULARITY 4 │
12. │ Parts: 1/1 │
13. │ Granules: 4/123 │
└─────────────────────────────────────────────────┘

EXPLAIN indexes = 1 select * from test_table where lower(value['10000']) = lower('10000')
┌─explain────────────────────────────────────┐
1. │ Expression ((Project names + Projection)) │
2. │ Expression │
3. │ ReadFromMergeTree (default.test_table) │
4. │ Indexes: │
5. │ PrimaryKey │
6. │ Condition: true │
7. │ Parts: 1/1 │
8. │ Granules: 123/123 │
└────────────────────────────────────────────┘
```

So my question is how can I use the `value_value_index` here or is there other way of defining the lowercase index for maps ?

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.