ClickHouse / ClickHouse/ClickHouse

Materialized view refresh after depends on - does not work

Open
#91,353 0 comments 0 reactions 0 assignees View on GitHub
comp-refreshable-materialized-view external potential bug
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

### Company or project name

I found an example `CREATE MATERIALIZED VIEW destination REFRESH AFTER 1 HOUR DEPENDS ON source AS SELECT ... FROM source` in the manual [https://clickhouse.com/docs/sql-reference/statements/create/view#materialized-view](https://clickhouse.com/docs/sql-reference/statements/create/view#materialized-view)
However, when I wrote SQL query `create materialized view processing.dashboard_main_mv
refresh after 10 minute depends on processing.dashboard_main_zsl_vm empty
append to processing.dashboard_main empty as
select ...` I got the next error `SQL Error [22000]: Code: 36. DB::Exception: DEPENDS ON is allowed only for REFRESH EVERY, not REFRESH AFTER. (BAD_ARGUMENTS) (version 25.8.2.29 (official build)) `

I need refresh depended view with some delay after master view

### Describe what's wrong

According the manual error never occur

### Does it reproduce on the most recent release?

Yes

### How to reproduce

Please run the script

```
create table raw_table(
id Int32,
name String
)
engine = Memory;

create table processing_table(
id Int32,
name String
)
engine = Memory;

create materialized view processing_table_mv refresh every 10 minute append to processing_table as
select id, name from raw_table;

create table report_table(
id Int32,
name String
)
engine = Memory;

create materialized view report_table_mv refresh after 5 minute depends on processing_table_mv append to report_table as
select id, name from processing_table;
```

### Expected behavior

No any error

### Error message and/or stacktrace

SQL Error [22000]: Code: 36. DB::Exception: DEPENDS ON is allowed only for REFRESH EVERY, not REFRESH AFTER. (BAD_ARGUMENTS) (version 25.8.2.29 (official build))

### Additional context

_No response_

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.