where clause is not supported in add materialized view clause

[JohnHuang93]

Feature request

Problem: Currently the where clause is not supported in add materialized view clause and a query with a where clause cannot hit a materialized view.

Example: 1.table Name:TABLE1 2.data model:Duplicate Key Model, DUPLICATE KEY(TDATE,COLUMN1,COLUMN2) 3.materialized views:

CREATE MATERIALIZED VIEW test1_mv as 
SELECT TDATE,SUM(MONEY) MONEY,SUM(SUM(MONEY)) OVER(PARTITION BY TDATE) SUM_MONEY 
FROM TABLE1 M GROUP BY TDATE ORDER BY TDATE WHERE TDATE=date'2022-01-01';

4.problems:

--problem1:
ERROR 1064 (HY000): The where clause is not supported in add materialized view clause,*********
--problem2:
ERROR 1064 (HY000): The materialized view only support the single column or function expr. Error column: sum(sum(MONEY)) OVER (PARTITION BY TDATE)

Alternatives my considered I hope to achieve the following:

1. Materialized views can support where clauses
2. The materialized view can use the window function
3. Add the where clause to the query statement to match the materialized view

Additional context StarRocks version：2.2.2 community edition

[imay]

this feature will be supported in version 2.4

[JohnHuang93]

Thanks,Please ask when version 2.4 will be released? @imay

[jaogoy]

@JohnHuang93 You can try the async Material View feature in V2.4. But for sync MV, StarRocks doesn't support where clause and window functions.

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 6 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to StarRocks!