Clickhouse: Data not insert to MATERIALIZED created from another MATERIALIZED VIEW

[Kiennh]

Describe the bug or unexpected behaviour
When I create MATERIALIZED view from another MATERIALIZED view, data not auto insert from the first view to the second view.

How to reproduce
Create table and the views:

CREATE TABLE a1(label String) ENGINE=Log ;   
CREATE MATERIALIZED VIEW a2 engine=Log as select * from a1; 
CREATE MATERIALIZED VIEW a3 engine=Log as select * from a2;
CREATE MATERIALIZED VIEW a4 engine=Log as select * from a3;

Insert data into the table a1:

INSERT INTO a1 values('1');   

Test the result

SELECT *
FROM a2

┌─label─┐
│ 1     │
└───────┘

1 rows in set. Elapsed: 0.002 sec. 

SELECT *
FROM a4

Ok.

The select query to a3, a4 have 0 rows return, expect 1 rows with label '1'

• Which ClickHouse server version to use
  This error coming from some version of clickhouse such as:
  clickhouse-server: 19.13.5.44
  clickhouse-server: 19.15.2.2

[den-crane]

Workaround:

 `.inner.a2`

CREATE TABLE a1(label String) engine=Log ;   
CREATE MATERIALIZED VIEW a2 engine=Log as select * from a1; 
CREATE MATERIALIZED VIEW a3 engine=Log as select * from `.inner.a2`;
CREATE MATERIALIZED VIEW a4 engine=Log as select * from `.inner.a3`;

PS: don't use engine=Log as persistent storage. MergeTree* family should be used as a storage.
Engine=Log is for intermediate ('temporary') tables.

[Kiennh]

Thank you, it's working.

Ps: I just use Log engine for reproduce this issue.

[juansc]

I tried various docker images and I found that this bug starts closer to clickhouse-server:19.11.12.69. I'll work on creating a minimal schema and then post it here.

[listenerri]

Is there any progress ?

[listenerri]

I confirmed that the problem started with tag: 19.11.12.69. Guess was introduced by some commit to fix comp-matview. The last working tag was: 19.11.11.57.

|CH version|MV2 based on MV1|Use multiple Join in MV|
|----|----|----|
|19.11.11.57|√|×|
|19.11.12.69|×|√|

[listenerri]

I found a workaround, referring to the test sql script in this PR: #6324
The content of test sql script (Works well for recursive MV):

DROP TABLE IF EXISTS test.src;
DROP TABLE IF EXISTS test.dst1;
DROP TABLE IF EXISTS test.dst2;

USE test;

CREATE TABLE src (x UInt8) ENGINE Memory;
CREATE TABLE dst1 (x UInt8) ENGINE Memory;
CREATE MATERIALIZED VIEW src_to_dst1 TO dst1 AS SELECT x + 1 as x FROM src;
CREATE MATERIALIZED VIEW dst2 ENGINE Memory AS SELECT x + 1 as x FROM dst1;

INSERT INTO src VALUES (1), (2);
SELECT * FROM dst1 ORDER BY x;
SELECT * FROM dst2 ORDER BY x;

DROP TABLE src;
DROP TABLE src_to_dst1;
DROP TABLE dst1;
DROP TABLE dst2;

The key point is that the first MV uses the TO keyword.

If I don't use the TO keyword, the second MV will not receive data:

DROP TABLE IF EXISTS test.src;
DROP TABLE IF EXISTS test.dst1;
DROP TABLE IF EXISTS test.dst2;

USE test;

CREATE TABLE src (x UInt8) ENGINE Memory;
CREATE MATERIALIZED VIEW dst1 ENGINE Memory AS SELECT x + 1 as x FROM src;
CREATE MATERIALIZED VIEW dst2 ENGINE Memory AS SELECT x + 1 as x FROM dst1;

INSERT INTO src VALUES (1), (2);
SELECT * FROM dst1 ORDER BY x;
SELECT * FROM dst2 ORDER BY x;

DROP TABLE src;
DROP TABLE src_to_dst1;
DROP TABLE dst1;
DROP TABLE dst2;