Incremental Materialized Views clickhouse with join tables

I am using ClickHouse and I have a materialized view.

When I have such a data structure, there is no problem, and when data enters my main table, my view also gets updated and always reflects the new data.

for example :

CREATE TABLE table_a (
    id UInt32,
    name String,
)
ENGINE = MergeTree()
ORDER BY id;

CREATE TABLE table_a_store (
    id UInt32,
    name String,
)
ENGINE = SummingMergeTree()
ORDER BY id;

CREATE MATERIALIZED VIEW table_a_store_mv TO table_a_store AS
SELECT id, name FROM table_a;

INSERT INTO table_a (id, name) VALUES (3, 'Alice2'), (4, 'Bob2');

SELECT * FROM table_a_store; -- have updated data in my mt view table

The problem arises when I need to join two tables whose data needs to be inserted into the view table.

for example:

CREATE TABLE table_a (
    id UInt32,
    name String,
)
ENGINE = MergeTree()
ORDER BY id;

CREATE TABLE table_b (
    id UInt32,
    a_id UInt32,
)
ENGINE = MergeTree()
ORDER BY id;

CREATE TABLE table_ab_store (
    a_id UInt32,
    b_id UInt32,
    name String,
)
ENGINE = SummingMergeTree()
ORDER BY a_id;

CREATE MATERIALIZED VIEW table_ab_store_mv TO table_ab_store AS
SELECT a.id AS a_id, b.id AS b_id, a.name AS name FROM table_a a JOIN table_b b ON a.id = b.a_id;

INSERT INTO table_a (id, name) VALUES (1, 'Alice1'), (2, 'Bob');
INSERT INTO table_b (id, a_id) VALUES (1, 1), (2, 2);

SELECT * FROM table_a; -- have DATA now
SELECT * FROM table_b; -- have DATA now

SELECT * FROM table_ab_store;  -- but empty

Now my materialized view is empty even though data is being added to tables A and B.

Can you provide me with a solution?

Solution

Such a MV design may look strange, but it works as designed.
Inserts into the RIGHT table of JOIN don't trigger inserts into MV.
An inserted into the LEFT table block of data is just joined to the full content of the RIGHT one and goes to MV.

CREATE TABLE table_a (
    id UInt32,
    name String,
)
ENGINE = MergeTree()
ORDER BY id;

CREATE TABLE table_b (
    id UInt32,
    a_id UInt32,
)
ENGINE = MergeTree()
ORDER BY id;

CREATE TABLE table_ab_store (
    a_id UInt32,
    b_id UInt32,
    name String,
)
ENGINE = SummingMergeTree()
ORDER BY a_id;

CREATE MATERIALIZED VIEW table_ab_store_mv TO table_ab_store AS
SELECT a.id AS a_id, b.id AS b_id, a.name AS name
FROM table_a a 
JOIN table_b b ON a.id = b.a_id;

INSERT INTO table_b (id, a_id) VALUES (1, 1), (2, 2);
INSERT INTO table_a (id, name) VALUES (1, 'Alice1'), (2, 'Bob');
SELECT 1 as sel_num, * FROM table_ab_store FORMAT PrettyCompactMonoBlock;  -- as expected
INSERT INTO table_b (id, a_id) VALUES (1, 1), (2, 2);
SELECT 2 as sel_num, * FROM table_ab_store FORMAT PrettyCompactMonoBlock;  -- the same content, because an insert into the right table of JOIN doesn't trigger an insert into MV

The result is:

   +-sel_num-+-a_id-+-b_id-+-name---+
1. |       1 |    1 |    1 | Alice1 |
2. |       1 |    2 |    2 | Bob    |
   +---------+------+------+--------+
   +-sel_num-+-a_id-+-b_id-+-name---+
1. |       2 |    1 |    1 | Alice1 |
2. |       2 |    2 |    2 | Bob    |
   +---------+------+------+--------+

fiddle link