ClickHouse DB::Exception: Not found column in block

The "DB::Exception: Not found column in block" error fires when ClickHouse expects a specific column inside an internal data block but cannot find it. This is different from the more common "Unknown column" syntax error — here the query parsed successfully, but at execution time the data block handed between pipeline stages is missing a column that the next stage needs.

Impact

Queries that hit this error fail outright, which can break dashboards, ETL pipelines, and materialized view refresh chains. Because the problem often hides behind projections or MV definitions, it may appear suddenly after a schema change even though the SELECT statement itself looks correct.

Common Causes

1. Materialized view schema drift — the source table was altered (column added, removed, or renamed) but the MV's internal target table still expects the old schema.
2. Projection mismatch — a projection references a column that no longer exists in the table, or was defined with an expression that ClickHouse can no longer resolve against the current block.
3. Subquery or CTE not exposing needed columns — a column referenced in an outer query is not produced by the inner subquery or CTE.
4. UNION ALL branch inconsistencies — one branch produces a column that another branch omits, and a downstream operation references the missing one.
5. Engine-level bug during merge or mutation — rare, but certain versions have bugs where intermediate blocks lose columns after a mutation or lightweight delete.

Troubleshooting and Resolution Steps

1. Read the full error message carefully. It will tell you which column is missing and often which part of the pipeline expected it. Note the column name exactly, including case.

2. Check materialized view definitions. If the error occurs during an INSERT (which triggers MV population), inspect the MV's SELECT:

   SHOW CREATE TABLE your_mv;
   

   Verify every column in the MV's SELECT still exists in the source table.

3. Compare source and target schemas. For MVs, make sure the target table's columns match what the MV SELECT produces:

   DESCRIBE TABLE your_mv_target;
   

4. Inspect projections. List projections for the table and check their definitions:

   SELECT name, query FROM system.projections
   WHERE database = 'your_db' AND table = 'your_table';
   

   If a projection references a dropped column, remove and recreate it.

5. Rebuild the problematic projection. Drop the stale projection and re-add it with the corrected definition:

   ALTER TABLE your_table DROP PROJECTION proj_name;
   ALTER TABLE your_table ADD PROJECTION proj_name (SELECT ...);
   ALTER TABLE your_table MATERIALIZE PROJECTION proj_name;
   

6. Check subqueries and CTEs. Ensure every column referenced in the outer query is explicitly listed in the inner SELECT. ClickHouse does not implicitly pass through columns that are not selected.

7. Review recent ALTER TABLE operations. A renamed or dropped column may have invalidated dependent objects. Query system.query_log for recent DDL:

   SELECT query FROM system.query_log
   WHERE type = 'QueryFinish' AND query ILIKE '%ALTER TABLE your_table%'
   ORDER BY event_time DESC LIMIT 10;
   

Best Practices

• After any ALTER TABLE that changes columns, review all materialized views and projections that depend on the table.
• Use explicit column lists in MV SELECT statements rather than SELECT *, so changes in the source table surface as clear errors instead of silent schema drift.
• Test schema migrations in a staging environment before applying them to production.
• Monitor system.query_log for NOT_FOUND_COLUMN_IN_BLOCK errors so you catch them before users do.

Frequently Asked Questions

Q: How is NOT_FOUND_COLUMN_IN_BLOCK different from "Unknown column"?
A: "Unknown column" is a parser-time error — the column name is not recognized at all. NOT_FOUND_COLUMN_IN_BLOCK happens at execution time when one pipeline stage produces a block that is missing a column the next stage expects. It usually involves internal objects like projections or materialized views.

Q: Can this error appear during INSERT operations?
A: Yes. If the table has materialized views whose SELECT references a column that is no longer part of the inserted block, the error fires during the INSERT that populates the MV.

Q: Will detaching and re-attaching a projection fix this?
A: Detaching alone won't help if the projection's definition references a missing column. You need to drop the projection and recreate it with a valid column set.

Q: I see this error only on one replica. Why?
A: Replicas may be at different schema versions if an ALTER was applied but hasn't fully propagated. Check system.replicas for schema mismatches and wait for or manually trigger synchronization.