@Rocky When creating an async materialized view, is it possible to workaround the limitation that the partition column should reference a single column from the SELECT statement, when the column is actually computed like

COALESCE(a.date, b.date)

due to outer join?

Directly using a computed expression like

COALESCE(a.date, b.date)

in the

PARTITION BY

clause of an asynchronous materialized view (MV) is not supported. StarRocks requires the partition column to reference a single column from the

SELECT

statement that maps directly to a partition column of a base table. However, you can workaround this limitation using a

UNION ALL

strategy (available since v3.3), which allows an MV to align partitions with multiple base tables. The Workaround:

UNION ALL

Instead of using a

FULL OUTER JOIN

with

COALESCE

, you can split the logic into a

UNION ALL

where each branch provides a direct mapping to a base table's partition column. StarRocks can then track the partition relationships for all involved tables. Example: If you want to join

table_a

and

table_b

on a date and partition the MV by that date:

sql
CREATE MATERIALIZED VIEW async_mv
REFRESH ASYNC
PARTITION BY (mv_date) -- This alias maps to base table columns in each branch
AS
-- Branch 1: Rows in A (including matches in B)
SELECT 
    a.date AS mv_date,
    a.id,
    b.value
FROM table_a a
LEFT JOIN table_b b ON a.date = b.date AND a.id = b.id

UNION ALL

-- Branch 2: Rows only in B
SELECT 
    b.date AS mv_date,
    b.id,
    b.value
FROM table_b b
LEFT ANTI JOIN table_a a ON b.date = a.date AND b.id = a.id;

Why this works: * Partition Alignment (v3.3+): StarRocks supports "Align partitions with multiple base tables." In a

UNION ALL

structure, if the column used in

PARTITION BY

(

mv_date

) originates from base table partition columns in every branch, StarRocks can establish the necessary mapping to perform incremental refreshes. * Avoids Expressions: In each branch,

mv_date

is a simple reference to either

a.date

or

b.date

, satisfying the requirement that the partition column must reference a base table partition column. Key Limitations to Keep in Mind: 1. Version Requirement: This "multi-source" partition alignment requires StarRocks v3.3 or higher. 2. Mapping Constraint: The partition columns across all base tables must have the same data type (e.g., both

DATE

or both

DATETIME

). 3. Single Column Reference: The

PARTITION BY

clause itself can only contain a single column alias (or

date_trunc

on one) unless you are using the Multi-Column Partition feature introduced in v3.5, but even then, expressions like

COALESCE

remain unsupported within the partition definition. If your use case strictly requires a Join (and

UNION ALL

is too complex), you must choose one primary table's partition column to be the "reference" for the MV's partitioning. Updates to the other (non-referenced) table would then trigger a full refresh or be ignored depending on the

mv_rewrite_staleness_threshold

setting. References • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md