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`

Open
#119,617 0 comments 0 reactions 0 assignees View on GitHub
comp-json-datatype comp-skip-index minor potential bug
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

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.