@Rocky - We have shared-data mode cluster(Version: 4.0.12) where we are testing starrocks Materialized View(MV) rewrite feature by creating few MV and running few set of queries. We found that if MV is created with two tables joined on left join with few filters. If query is fired using the same tables,same join and within the same range as data filters, The rewrite is not happening. Are there any known limitations currently on this?

Hello! How can I help you with StarRocks today? ^^?Rocks today?

@Rocky - We have shared-data mode cluster(Version: 4.0.12) where we are testing starrocks Materialized View(MV) rewrite feature by creating few MV and running few set of queries. We found that if MV is created with two tables joined on left join with few filters. If query is fired using the same tables,same join and within the same range as data filters, The rewrite is not happening. Are there any known limitations currently on this?

Regarding your issue with Materialized View (MV) rewrite in StarRocks (Version 4.0.12) using a LEFT JOIN with filters, here are the most likely causes and current limitations: 1. Complex Predicates in

ON

Clauses (Outer Joins) A known limitation in StarRocks is that the optimizer cannot easily rewrite queries involving complex join predicates within the

ON

clause of an Outer Join (Left/Right/Full). * Limitation: Predicates in an

ON

clause of a

LEFT JOIN

have different semantic meanings than those in a

WHERE

clause. * Recommendation: If your filters are inside the

ON

clause (e.g.,

... LEFT JOIN tableB ON A.id = B.id AND B.status = 'active'

), try moving the non-join filters to a

WHERE

clause if possible, or ensure the query's structure exactly matches the MV's structure. 2. Column Projection and Predicates For a rewrite to occur, the MV must "cover" the query. This means: * Columns: All columns used in the query's

SELECT

,

JOIN

conditions, and

WHERE

clauses must be present in the MV's

SELECT

list. * Filter Range: While you mentioned the query is within the "same range," ensure that the MV's filter is not more restrictive than the query's filter. If the MV has

WHERE date > '2023-01-01'

and the query has

WHERE date > '2022-01-01'

, the rewrite will fail because the MV doesn't contain all the required data. 3. MV Freshness and Consistency In StarRocks, query rewrite is sensitive to data consistency: * Status: Ensure the MV is

ACTIVE

and has been successfully refreshed. You can check this via

SHOW MATERIALIZED VIEWS

. * Consistency Level: By default,

query_rewrite_consistency

is set to

checked

, meaning the MV will only be used if it is up-to-date with the base tables. If the base tables have changed and the MV hasn't refreshed, rewrite will not occur unless you set

mv_rewrite_staleness_second

to a higher value or use

SET query_rewrite_consistency = 'loose'

. 4. Shared-data Mode Specifics In StarRocks 4.0+, synchronous MVs are supported in shared-data clusters, but asynchronous MVs (which handle complex rewrites) rely on the FE's refresh task scheduler. Ensure that the warehouse/resource group assigned to the MV refresh is functioning correctly in your shared-data environment. 5. How to Debug (The "Trace" Tool) To find the exact reason for the rewrite failure, use the TRACE command. This is the most effective way to identify which rule failed:

sql
-- For a concise reason
TRACE REASON MV <your_query>;

-- For a detailed execution log of the rewrite process
TRACE LOGS MV <your_query>;

Look for terms like

discarded

or

rejected

in the output. Common rejection reasons include

compensate_predicate_failed

or

column_not_found

. Note on Version: StarRocks 4.0.12 is a very recent release. If the

TRACE

output suggests a logical match but it still fails, it may be worth checking for any newly introduced regressions in the 4.x branch via the StarRocks GitHub issues or community Slack. References • release_notes/release-4.0.md • release_notes/release-3.3.md • release_notes/release-2.5.md