ClickHouse报错Code:47：缺失purchase_number字段问题求助

问题重现 #

日志中持续出现以下错误：

return (<Error> virtual bool DB::MutatePlainMergeTreeTask::executeStep(): Code: 47. DB::Exception: Missing columns: 'purchase_number' while processing query: 'SELECT purchase_number FROM rad_reports_api_release_2.purchase_notices_v1 WHERE region = 'Test'', required columns: 'purchase_number' 'region', maybe you meant: 'region': While processing (purchase_number IN ((SELECT purchase_number FROM rad_reports_api_release_2.purchase_notices_v1 WHERE region = 'Test' ) AS _subquery8297539)) OR (purchase_number IN ((SELECT purchase_number FROM rad_reports_api_release_2.purchase_notices_v1 WHERE region = 'Test') AS _subquery8297540)) OR (purchase_number IN ((SELECT registration_number FROM rad_reports_api_release_2.purchase_notices_v1 WHERE region = 'Test') AS _subquery8297541) ).(UNKNOWN_IDENTIFIER)
)

且purchase_notices_v1表实际不存在purchase_number字段，升级ClickHouse至23.11.1.2711版本无效。

问题原因 #

• 物化视图引用错误：关联purchase_notices_v1的物化视图定义中存在对purchase_number的引用，后台合并任务触发视图刷新时，就会抛出字段不存在的错误。
• 残留Mutate任务：之前针对该表的ALTER操作可能残留了未完成的mutation任务，任务逻辑包含错误字段引用，后台执行时持续报错。
• 隐藏关联对象引用：普通视图、字典或自定义函数中存在对该字段的引用，后台任务执行时触发错误查询逻辑。

修复方法 #

• 排查并清理错误物化视图
  执行SQL查询数据库内物化视图定义：

  SELECT name, create_table_query 
  FROM system.tables 
  WHERE database = 'rad_reports_api_release_2' 
    AND engine = 'MaterializedView';
  

  找到包含purchase_number引用的物化视图，删除该视图：

  DROP MATERIALIZED VIEW rad_reports_api_release_2.xxx_view_name;
  

  若需保留视图功能，重新创建不含错误字段引用的视图。

• 清理残留Mutate任务
  查询该表的mutation任务：

  SELECT * 
  FROM system.mutations 
  WHERE database = 'rad_reports_api_release_2' 
    AND table = 'purchase_notices_v1';
  

  找到状态异常或含错误字段的任务，执行删除：

  ALTER TABLE rad_reports_api_release_2.purchase_notices_v1 
  DROP MUTATION 'mutation_id';
  

  替换mutation_id为实际任务ID。

• 检查其他关联对象
  查询普通视图定义：

  SELECT name, view_definition 
  FROM system.views 
  WHERE database = 'rad_reports_api_release_2';
  

  查询字典定义：

  SELECT name, definition 
  FROM system.dictionaries 
  WHERE database = 'rad_reports_api_release_2';
  

  找到引用purchase_number的对象并修正或删除。

• 验证修复效果
  完成操作后重启ClickHouse服务，观察clickhouse-server.err.log是否仍有该错误输出。

内容的提问来源于stack exchange，提问作者Александр Орлов