Styjun

Having a structure where there is a base table, then a materialized view base_mv that aggregates sending the result TO an AggregatedMergeTree table base_agg_by_id. Then we have a view over this final table. base_unique. Similarly as in this blog post](https://www.altinity.com/blog/clickhouse-continues-to-crush-time-series).

However, if I delete from base, I would expect the base_mv would trigger the mutation and then act on it, and reflected on the base_agg_by_id, but it doesn't.

Is this the expected behaviour? How to DELETE in such a schema?

I've seen here that in MVs that keep data you can act on .inner tables. However in this case, since the table is from an AggregatedMergeTree and its fields are defined as functions (e.g. AggregateFunction(argMax, String, DateTime) ), I cannot apply a deletion via a value such as ALTER base_agg_by_id DELETE WHERE field = 'myval'.

Note. For the record, we have these tables in a replicated environment using Replicated* engine: base_d, base_agg_by_id_d, base_unique_d

Mutations are not propagated to materialized views.

The reason is very simple: it not possible in common case. And even in cases when it is theoretically possible it can be very expensive operation.

For example, let's say you're deleting one record from the table which references some userid. And your materialized view contains uniqState( userid ). Data structures used for calculating uniqState don't support 'remove' operation; but even if they would - the is no way to decide if that userid should be removed or not without rereading whole data for the partition again because that userid could be seen in other records too.

So in general case, you need to refill the whole partition for your AggregatedMergeTree.

I.e. something like (daily partitioning case):

 ALTER amt_table DROP PARTITION '2019-03-01';

 -- use same select as in your materialized view
 INSERT INTO amt_table SELECT ... WHERE date = '2019-03-01';