ClickHouse / ClickHouse/ClickHouse

Recreating a materialized view's source table with an incompatible schema silently breaks the MV until insert time

Open
#112,260 0 comments 0 reactions 0 assignees View on GitHub
comp-materialized-view
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

**Describe the unexpected behaviour**

ClickHouse enforces that you cannot remove a column that a materialized view reads from its source table — but only on the `ALTER TABLE ... DROP COLUMN` path. The equivalent state can be reached unchecked via `DROP TABLE` + `CREATE TABLE` (or `RENAME TABLE`), which silently leaves the materialized view referencing a column that no longer exists. The breakage is not reported at `CREATE` time; instead it surfaces much later as a hard `INSERT` failure that blocks **all** writes to the recreated source table.

**How to reproduce**

ClickHouse version: `26.7.1.1` (also reproduces with `clickhouse local`).

```sql
CREATE TABLE t (i Int32, j Int32) ENGINE = MergeTree ORDER BY i;
CREATE TABLE tx (i Int32, avgJ Float64) ENGINE = AggregatingMergeTree ORDER BY i;
CREATE MATERIALIZED VIEW mv TO tx AS SELECT i, avg(j) AS avgJ FROM t GROUP BY i;

INSERT INTO t VALUES (1, 10), (1, 20), (2, 30); -- OK

DROP TABLE t; -- allowed, even though mv reads FROM t
CREATE TABLE t (i Int32) ENGINE = MergeTree ORDER BY i; -- recreated WITHOUT column j, also allowed

INSERT INTO t VALUES (5); -- FAILS
```

The final insert fails with:

```
Code: 47. DB::Exception: Unknown expression or function identifier `j` in scope
SELECT i, avg(j) AS avgJ FROM default.t GROUP BY i. Maybe you meant: ['i']:
while pushing to view default.mv. (UNKNOWN_IDENTIFIER)
```

From this point on, every `INSERT INTO t` fails until the user drops/fixes `mv` or restores column `j`.

**Expected behavior**

The same invariant is already enforced for the direct path — dropping a column that a materialized view depends on is rejected up front:

```sql
ALTER TABLE t DROP COLUMN j;
-- Code: 524. DB::Exception: Trying to ALTER DROP column j which is referenced
-- by materialized view ['mv']. (ALTER_OF_COLUMN_IS_FORBIDDEN)
```

Since `DROP TABLE t` + `CREATE TABLE t (i)` reaches the same end state (a source table missing a column an MV reads), the user should be told about the broken dependency at the point of the breaking change — either as an error mirroring `ALTER DROP COLUMN`, or at minimum as a warning — rather than only discovering it later via a write-blocking `INSERT` failure.

**Additional context**

- The dependency is tracked (`system.tables.dependencies_table` for `t` shows `['mv']`), but this "view push" dependency is not consulted when recreating/renaming the source table.
- The existing `ALTER DROP COLUMN` guard lives in `MergeTreeData.cpp` and uses `getDependentViewsByColumn`, which computes exactly the set of source columns each dependent MV requires — the same helper could be reused to validate the source schema at `CREATE TABLE`/`RENAME TABLE` time.
- `check_table_dependencies` / `check_referential_table_dependencies` do not protect the source table here (source-of-MV is intentionally not a referential dependency, so that dropping/recreating a source remains possible).
- `RENAME TABLE t TO ...` out from under the MV is likewise unguarded.

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.