clickhouse alter MATERIALIZED VIEW add column

Question

env: Clikchouse version:22.3.3.44; Database engine: atomic

I have a raw table and mv, schema like this:

CREATE TABLE IF NOT EXISTS test.Income_Raw on cluster '{cluster}' (
  Id Int64,
  DateNum Date,
  Cnt Int64,
  LoadTime DateTime
) ENGINE==MergeTree
PARTITION BY toYYYYMMDD(LoadTime)
ORDER BY (Id, DateNum);

CREATE MATERIALIZED VIEW test.Income_MV on cluster '{cluster}'
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/{shard}/{database}/{table}', '{replica}')
PARTITION BY toYYYYMM(DateNum)
ORDER BY (Id, DateNum)
TTL DateNum+ INTERVAL 100 DAY
AS SELECT
    DateNum,
    Id,
    argMaxState(Cnt,  LoadTime) as Cnt ,
    maxState( LoadTime)  as latest_loadtime
FROM test.Income_Raw
GROUP BY Id, DataNum;

now I want to add a column named 'price' to raw table and mv, so I run sql step by step like below:

// first I alter raw table
1. alter table test.Income_Raw on cluster '{cluster}' add column Price Int32

// below sqls, I run to alter MV
2. detach test.Income_MV on cluster '{cluster}'

3. alter test.`.inner_id.{uuid}` on cluster '{cluster}' add column Price Int32

// step 4, basically I just use 'attach' replace 'create' and add 'Price' to select query
4. attach MATERIALIZED VIEW test.Income_MV on cluster '{cluster}'
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/{shard}/{database}/{table}', '{replica}')
PARTITION BY toYYYYMM(DateNum)
ORDER BY (Id, DateNum)
TTL DateNum+ INTERVAL 100 DAY
AS SELECT
    DateNum,
    Id,
    Price,
    argMaxState(Cnt,  LoadTime) as Cnt ,
    maxState( LoadTime)  as latest_loadtime
FROM test.Income_Raw
GROUP BY Id, DataNum, Price;

but at step 4, I met error like this

Code: 80. DB::Exception: Incorrect ATTACH TABLE query for Atomic database engine. Use one of the following queries instead:
1. ATTACH TABLE Income_MV;
2. CREATE TABLE Income_MV <table definition>;
3. ATTACH TABLE Income_MV FROM '/path/to/data/' <table definition>;
4. ATTACH TABLE Income_MVUUID '<uuid>' <table definition>;. (INCORRECT_QUERY) (version 22.3.3.44 (official build))

these sqls I runned is I followed from below references.

https://kb.altinity.com/altinity-kb-schema-design/materialized-views/

Clickhouse altering materialized view's select

so my question is, how to modify mv select query, which step I was wrong?

Answer 1

I figure out that just need:

prepare: use explicit target table instead inner table for MV

1 alter MV target table

2 drop MV

3 re-create MV with new query