ALTER MV MODIFY QUERY changes MV query but not columns list.

[den-crane]

CREATE TABLE src(v UInt64) ENGINE = Null;
CREATE TABLE dest(v UInt64) Engine = MergeTree() ORDER BY v;
CREATE MATERIALIZED VIEW pipe TO dest AS SELECT v FROM src;
ALTER TABLE dest ADD COLUMN v2 UInt64;

SET allow_experimental_alter_materialized_view_structure = 1;

ALTER TABLE pipe add column v2 Int64, MODIFY QUERY SELECT v * 2 as v, 1 as v2 FROM src;

DESCRIBE TABLE pipe

┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ v    │ UInt64 │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

expected 2 columns `v`, `v2`

SHOW CREATE TABLE pipe

┌─statement─────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE MATERIALIZED VIEW dw.pipe TO dw.dest
(
    `v` UInt64
) AS
SELECT
    v * 2 AS v,
    1 AS v2
FROM dw.src │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘

[alexey-milovidov]

CC @nvartolomei

[nvartolomei]

The actual result is:

Code: 48. DB::Exception: Received from localhost:9000. DB::Exception: Alter of type 'ADD COLUMN' is not supported by storage MaterializedView.

This is intended as the environment I did this for had some odd table structures that did not match what you could usually infer from the select statement and modifying table structure could end up with surprises. Instead we settled on using MV just as "pipes" connecting null tables to destination tables, and for reads read destination tables directly or create normal views (never seen them actually).

From usability perspective all "improvements" i could think of had drawbacks. If I had to do it properly again I would probably introduce a new concept just called "pipe" from getting data from one table to another and forget about materialized views.

Later add: Even if we allow explicit chaining of ADD/DROP/MODIFY COLUMN it is still not very clear if this would affect the MV or MV and underlying the table as some alters on MV are currently propagated to inner table.