ClickHouse / ClickHouse/ClickHouse

indexHint over a Merge table's _table virtual column fails with NOT_FOUND_COLUMN_IN_BLOCK instead of narrowing the read

Open
#114,013 0 comments 0 reactions 1 assignee Claimed by @azat View on GitHub
comp-query-optimizer comp-skip-index comp-storage-merge minor potential bug
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

### Describe what's wrong

Wrapping a predicate that references the `_table` virtual column of a `Merge` table in `indexHint(...)` makes the query fail with `NOT_FOUND_COLUMN_IN_BLOCK`, even though the same predicate runs fine bare. `indexHint` is documented as a read-narrowing no-op for row-level semantics, so `indexHint(p) AND p` must return exactly the rows of `WHERE p` — instead it throws an exception.

### How to reproduce

Master 26.8.1.1008 (commit `438589fa59984635eed24aa9f450d55a4763bb66`), deterministic 20/20, `enable_analyzer = 1` (default):

```sql
CREATE TABLE m_v1 (key UInt32, value UInt32) ENGINE = MergeTree ORDER BY key;
CREATE TABLE m_v2 (key UInt32, value UInt32) ENGINE = MergeTree ORDER BY key;
INSERT INTO m_v1 VALUES (1, 10);
INSERT INTO m_v2 VALUES (2, 20);
CREATE TABLE m_all (key UInt32, value UInt32) ENGINE = Merge(currentDatabase(), '^m_v[0-9]$');

SELECT _table, key FROM m_all WHERE _table = 'm_v1' ORDER BY key;
-- m_v1 1 (correct)

SELECT _table, key FROM m_all WHERE indexHint(_table = 'm_v1') AND (_table = 'm_v1') ORDER BY key;
-- Code: 10. DB::Exception: Not found column _table in block: while executing 'INPUT : 1 -> _table LowCardinality(String) : 3'. (NOT_FOUND_COLUMN_IN_BLOCK)
```

### Expected behavior

`indexHint(p) AND p` returns the rows of `WHERE p` — the hint may only narrow which granules are read, never fail the query. Expected output: `m_v1 1`.

### Additional context

The failure needs the virtual column inside the `indexHint` argument; `indexHint(key = 1) AND (_table = 'm_v1')` (virtual outside the hint) works. So the hint's index-analysis expression evidently demands `_table` as an input of the read step where the `Merge` child read does not provide it.

Found by an automatic optimizer-testing framework (differential testing of optimizer settings, query plans, and equivalent rewrites).

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.