alter_MV_with_SummingMergeTree

alter_MV_with_SummingMergeTree.txt

create table source (A Int64, amount Float64) Engine=MergeTree order by A;

create materialized view target Engine=SummingMergeTree order by A
as select A, sum(amount) s from source group by A

insert into source values(1,1);

alter table source add column B Int64 after A;

detach table target;

alter table `.inner.target` add column B Int64 after A, modify order by (A,B);

SHOW CREATE TABLE `.inner.target`;

┌─statement─────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE dw.`.inner.target` (`A` Int64, `B` Int64, `s` Float64)                       │
│ ENGINE = SummingMergeTree PRIMARY KEY A ORDER BY (A, B) SETTINGS index_granularity = 8192 │
└───────────────────────────────────────────────────────────────────────────────────────────┘
!!! the index has not changed! PRIMARY KEY A
!!! ORDER BY (A, B) changed - fields by which SummingMergeTree collapses records.
!!! and this is good, long indexes of 20 fields are not needed, reduce performance and consume memory



attach materialized view target  Engine=SummingMergeTree 
as select A, B, sum(amount) s
from source group by A,B;

insert into source values(1,2,1);

optimize table target final;

SELECT *
FROM target

┌─A─┬─B─┬─s─┐
│ 1 │ 0 │ 1 │
│ 1 │ 2 │ 1 │
└───┴───┴───┘

In version 21.11.1 when I am trying to run:

attach materialized view target  Engine=SummingMergeTree 
as select A, B, sum(amount) s
from source group by A,B;

I am receive:

Received exception from server (version 21.11.1):
Code: 80. DB::Exception: Received from localhost:9000. DB::Exception: Incorrect ATTACH TABLE query for Atomic database engine. Use one of the following queries instead:
1. ATTACH TABLE target;
2. CREATE TABLE target <table definition>;
3. ATTACH TABLE target FROM '/path/to/data/' <table definition>;
4. ATTACH TABLE target UUID '<uuid>' <table definition>;. (INCORRECT_QUERY)

@timson I would say it's a new bug.
I suggest to switch to TO syntax https://den-crane.github.io/Everything_you_should_know_about_materialized_views_commented.pdf

CREATE DATABASE ordinary_db ENGINE = Ordinary;
use ordinary_db ;

then do the test @timson