ClickHouse / ClickHouse/ClickHouse
Skip index over a Map column `j.m` wrongly prunes granules when the predicate reads a JSON dynamic path `j.m.key_<k>` of a sibling JSON column `j`
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
**Describe what's wrong**
TL;DR: when a table has a `JSON` column `j` and a Map column named `` `j.m` `` carrying a skip index over `mapKeys` or `mapValues`, a predicate on `j.m.key_` — which the analyzer resolves as the dynamic path `m.key_` under `j` — is misread by skip-index analysis as the map's key subcolumn, and every granule whose map lacks the key `` is pruned, so matching rows are silently dropped.
1. Skip-index analysis resolves a name shaped `.key_` to "the map's element at key ``" with a textual split (`tryParseMapSubcolumnName`). #119457 refuses the split when the name is claimed by an enumerable column or subcolumn (a physical column, a `Tuple` element, a typed JSON path), via `IMergeTreeIndex::getColumnsShadowingMapSubcolumns` (`src/Storages/MergeTree/MergeTreeIndices.cpp:92`), which enumerates the metadata's columns with `withSubcolumns()`.
2. A dynamic JSON path is resolved per query from the data and never appears in that enumeration, so this face persists after #119457; its description discloses it as a pre-existing residual. The predicate reads the value stored under `j`'s dynamic path (measured: `SELECT j.m.key_nokey` returns the JSON value, not the map element), while the index probes `mapKeys(j.m)` for `nokey` and prunes the granule.
3. All map-aware index conditions share the split, so `ngrambf_v1` (measured), `tokenbf_v1`, `bloom_filter`, `sparse_grams` and `text` indexes over `mapKeys`/`mapValues` are affected.
**Does it reproduce on the most recent release?**
Reproduced on a 26.9.1.1 master build; the code merged in #119457 cannot cover this face because the shadowing set is enumerated from the table metadata and dynamic paths are data-dependent.
**How to reproduce**
```sql
SET use_skip_indexes = 1;
CREATE TABLE t_json (j JSON, `j.m` Map(String, String),
INDEX idx mapKeys(`j.m`) TYPE ngrambf_v1(3, 512, 3, 0) GRANULARITY 1)
ENGINE = MergeTree ORDER BY tuple();
INSERT INTO t_json VALUES ('{"m":{"key_nokey":"hello"}}', {'abc' : 'x'});
SELECT j.m.key_nokey FROM t_json; -- 'hello': the name resolves to the dynamic path under `j`
SELECT count() FROM t_json WHERE j.m.key_nokey = 'hello'; -- 0, wrong
SELECT count() FROM t_json WHERE j.m.key_nokey = 'hello' SETTINGS use_skip_indexes = 0; -- 1
```
**Expected behavior**
The query returns the matching row (`count()` = 1) whether or not the skip index is applied.
**Additional context**
The safe direction mirrors #119457: when the name can resolve to anything other than a genuine Map key subcolumn, the index must not apply. Since no enumerated set can cover dynamic paths, the split likely has to be refused whenever a prefix of the name is a `JSON` column, or the resolution has to consult the storage snapshot the way `FunctionToSubcolumnsPass` does.
Contributor guide
Assessment
This issue has not been assessed yet.