http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I have logical views with some logi...
# questions-and-troubleshooting
a
@Rocky I have logical views with some logic on top of mat views and I want to have benefits from mv query rewrite by creating smaller versions of existed mvs. What are limitations for query rewrite in this scenario?
r
In StarRocks, creating "smaller" (more aggregated or filtered) materialized views (MVs) to optimize queries that currently hit larger MVs via logical views is a supported strategy, but it involves several layers of the rewrite engine. Based on the documentation for Nested Materialized Views and View-based Rewrite, here are the primary limitations and considerations for this scenario: 1. Rewrite Direction and Expansion * Expansion requirement: By default, StarRocks expands logical views into their underlying SQL before attempting a rewrite. If your logical view is built on 
MV1
, the query is effectively treated as a query on 
MV1
. * MV as a "Base Table": If you create a "smaller" 
MV2
based on the same base tables as 
MV1
, the rewrite will only succeed if the optimizer expands 
MV1
(the large one) back to the base tables during the query planning phase. Generally, if a query explicitly references a materialized view, the optimizer may not try to rewrite it using another MV unless it's a Nested MV relationship. * Recommendation: To ensure the best rewrite performance, define your "smaller" 
MV2
directly on top of 
MV1
 (Nested MV). StarRocks explicitly supports rewriting queries on base tables or intermediate MVs to use nested, more aggregated MVs. 2. View-Based Rewrite Limitations (v3.3+) If your logical views contain complex logic on top of the MVs, StarRocks uses the 
LogicalViewScanOperator
(introduced in v3.3) to match view structures. Limitations include: * Column Naming: The logical view must not have columns with the same name; you must use unique aliases. * Data Types: The view must contain at least one column of type 
INT
, 
DATE
, or 
STRING
. * No Random Functions: If the logical view uses non-deterministic functions (e.g., 
rand()
, 
uuid()
), rewrite is disabled. * No Partition Union Rewrite: Currently, StarRocks does not support rewriting through a 
UNION
of partitions in the view logic. 3. General Materialized View Rewrite Limitations Even if the view-specific rules are met, the standard MV rewrite restrictions apply to your "smaller" MVs: * Unsupported SQL Features: The definition of your smaller MV cannot include 
LIMIT
, 
ORDER BY
, 
UNION
, 
EXCEPT
, 
INTERSECT
, 
WINDOW FUNCTIONS
, or 
GROUPING SETS
. * Consistency: The smaller MV must be active and refreshed. If it is stale (out of sync with 
MV1
or base tables), it won't be used unless your session property 
query_rewrite_consistency
is set to 
checked
or 
loose
. * Catalog Restrictions: Rewrite is not supported if the base tables are in a JDBC catalog. 4. Nested Depth and Complexity * While StarRocks supports nested MVs, very deep nesting or extremely complex logic within the logical view can sometimes cause the optimizer to exceed its cost/time budget, leading it to skip the rewrite. * SPJG Pattern: Both the query (after expanding the logical view) and the smaller MV must strictly follow the Select-Project-Join-Group-by pattern. Summary Checklist To make this work: 1. Prefer Nesting: Define the smaller MV using the large MV as its source table. 2. Verify Version: Ensure you are on v3.3.0 or later for the best support of complex logical view rewrites. 3. Check Explain Plan: Use 
EXPLAIN <query>
to see if 
MaterializedView: true
appears. If it doesn't, use 
EXPLAIN VERBOSE
to check why the rewrite failed (the 
Materialized View Rewrite Fail
section). References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
a
@Rocky for example
Copy code
CREATE OR REPLACE VIEW `supply_chain`.`shipment_log_lv` (
    `report_week`,
    `signal_type`,
    `warehouse_zone`,
    `routing_flag`,
    `carrier_label`,
    `carrier_code`,
    `sku_tag`,
    `depot_iso`,
    `lag_days`,
    `roll_metric`,
    `scan_mode`,
    `client_uuid`,
    `firmware`,
    `dock_type`,
    `procedure_code`,
    `batch_name`,
    `lane`,
    `component`,
    `supplier_label`,
    `third_party_ref`,
    `batch_id`,
    `confirmed`,
    `relay_node`,
    `sku_id`,
    `routing_active`,
    `broker_label`,
    `broker_id`,
    `template_id`,
    `template_label`,
    `product_sku`,
    `manifest`,
    `feed_origin`,
    `approved_flag`,
    `scan_hits`,
    `distinct_units`,
    `gross_units`,
    `invoice_usd`,
    `outbound_taps`,
    `outbound_views`,
    `spend_usd`
)
SECURITY NONE
AS
SELECT
    `combined`.`report_week`,
    `combined`.`signal_type`,
    `combined`.`warehouse_zone`,
    `combined`.`routing_flag`,
    `combined`.`carrier_label`,
    `combined`.`carrier_code`,
    `combined`.`sku_tag`,
    `combined`.`depot_iso`,
    `combined`.`lag_days`,
    `combined`.`roll_metric`,
    `combined`.`scan_mode`,
    `combined`.`client_uuid`,
    `combined`.`firmware`,
    `combined`.`dock_type`,
    `combined`.`procedure_code`,
    `combined`.`batch_name`,
    `combined`.`lane`,
    `combined`.`component`,
    `combined`.`supplier_label`,
    `combined`.`third_party_ref`,
    `combined`.`batch_id`,
    `combined`.`confirmed`,
    `combined`.`relay_node`,
    `combined`.`sku_id`,
    `combined`.`routing_active`,
    `combined`.`broker_label`,
    `combined`.`broker_id`,
    `combined`.`template_id`,
    `combined`.`template_label`,
    `combined`.`product_sku`,
    `combined`.`manifest`,
    `combined`.`feed_origin`,
    `combined`.`approved_flag`,
    `combined`.`scan_hits`,
    CASE WHEN (`supply_chain`.`rsm`.`client_uuid` IS NULL) THEN `combined`.`distinct_units` END AS `distinct_units`,
    CASE WHEN (`supply_chain`.`rsm`.`client_uuid` IS NULL) THEN `combined`.`gross_units` END AS `gross_units`,
    CASE WHEN (`supply_chain`.`rsm`.`client_uuid` IS NULL) THEN `combined`.`invoice_usd` END AS `invoice_usd`,
    CASE WHEN (`supply_chain`.`rsm`.`client_uuid` IS NULL) THEN `combined`.`outbound_taps` END AS `outbound_taps`,
    CASE WHEN (`supply_chain`.`rsm`.`client_uuid` IS NULL) THEN `combined`.`outbound_views` END AS `outbound_views`,
    CASE WHEN (`supply_chain`.`rsm`.`client_uuid` IS NULL) THEN `combined`.`spend_usd` END AS `spend_usd`
FROM
    (
        SELECT
            `inf`.`report_week`, `inf`.`signal_type` AS `signal_type`, `inf`.`warehouse_zone`, `inf`.`routing_flag`, `inf`.`carrier_label`, `inf`.`carrier_code`, `inf`.`sku_tag`,
            `inf`.`depot_iso`, `inf`.`lag_days`,
            `inf`.`roll_metric`, `inf`.`scan_mode`, `inf`.`client_uuid`, `inf`.`firmware`, `inf`.`dock_type`,
            `inf`.`procedure_code`, `inf`.`batch_name`, `inf`.`lane`, `inf`.`component`, `inf`.`supplier_label`,
            `inf`.`third_party_ref`, `inf`.`batch_id`, `inf`.`confirmed`, `inf`.`relay_node`,
            `inf`.`sku_id`, `inf`.`routing_active`, `inf`.`broker_label`, `inf`.`broker_id`, `inf`.`template_id`,
            `inf`.`template_label`, `inf`.`product_sku`, `inf`.`manifest` AS `manifest`,
            NULL AS `feed_origin`,
            NULL AS `approved_flag`,
            NULL AS `scan_hits`,
            `inf`.`distinct_units`,
            `inf`.`gross_units`,
            `inf`.`invoice_usd`,
            NULL AS `outbound_taps`,
            NULL AS `outbound_views`,
            NULL AS `spend_usd`
        FROM
            `supply_chain`.`inbound_feed_mv` AS `inf`
        WHERE (
            (`inf`.`signal_type` = 'pkg_scan' AND IFNULL(`inf`.`dock_type`, '') NOT IN ('BLOCKED', 'UNKNOWN'))
            OR (`inf`.`signal_type` IN ('pkg_transfer', 'pkg_intake', 'pkg_dispatch', 'pkg_return') AND `inf`.`dock_type` IN ('RAIL_NORTH', 'RAIL_SOUTH', 'ROAD_EAST', 'ROAD_WEST'))
            OR (`inf`.`signal_type` IN ('pkg_pickup', 'pkg_load', 'pkg_reload') AND `inf`.`dock_type` IN ('AIR_CARGO', 'SEA_CARGO', 'AIR_EXPRESS', 'SEA_EXPRESS', 'GROUND_BULK', 'GROUND_EXPRESS'))
            OR (`inf`.`signal_type` IN ('pkg_invoice', 'pkg_manifest', 'pkg_status', 'pkg_exception') AND IFNULL(`inf`.`dock_type`, '') NOT IN ('BLOCKED', ''))
            OR (`inf`.`signal_type` = 'pkg_label' AND IFNULL(`inf`.`dock_type`, '') != 'BLOCKED')
        )

        UNION ALL

        SELECT
            `fcm`.`report_week`, `fcm`.`signal_type` AS `signal_type`, 'DIRECT' AS `warehouse_zone`, 'PURCHASED' AS `routing_flag`, `fcm`.`carrier_label`, `fcm`.`carrier_code`, `fcm`.`sku_tag`,
            `fcm`.`depot_iso`,
            NULL AS `lag_days`,
            NULL AS `roll_metric`,
            NULL AS `scan_mode`,
            `fcm`.`client_uuid`, `fcm`.`firmware`, `fcm`.`dock_type`, `fcm`.`procedure_code`, `fcm`.`batch_name`, `fcm`.`lane`,
            `fcm`.`component`, `fcm`.`supplier_label`, `fcm`.`third_party_ref`, `fcm`.`batch_id`, `fcm`.`confirmed`,
            `fcm`.`relay_node`, `fcm`.`sku_id`,
            NULL AS `routing_active`,
            `fcm`.`broker_label`, `fcm`.`broker_id`, `fcm`.`template_id`, `fcm`.`template_label`, `fcm`.`product_sku`,
            `fcm`.`manifest` AS `manifest`, `fcm`.`feed_origin`, `fcm`.`approved_flag`, `fcm`.`scan_hits`,
            NULL AS `distinct_units`,
            NULL AS `gross_units`,
            NULL AS `invoice_usd`,
            `fcm`.`outbound_taps`,
            `fcm`.`outbound_views`,
            `fcm`.`spend_usd`
        FROM
            `supply_chain`.`freight_cost_mv` AS `fcm`
    ) AS `combined`
LEFT OUTER JOIN  [BROADCAST]
    `supply_chain`.`blocked_clients_mv` AS `bcm`
    ON `combined`.`client_uuid` = `bcm`.`client_uuid`
LEFT OUTER JOIN [BROADCAST]
    `supply_chain`.`restricted_suppliers_mv` AS `rsm`
    ON  (`combined`.`client_uuid` = `rsm`.`client_uuid`)
    AND (`combined`.`third_party_ref` = `rsm`.`third_party_ref`)
    AND (`combined`.`batch_id` = `rsm`.`batch_id`)
WHERE `bcm`.`client_uuid` IS NULL
this is my LV and I have smaller version of inbound_feed_mv. my query
Copy code
select warehouse_zone, sum(gross_units)
from shipment_log_lv
where "report_week" >= '2026-06-08'
group by 1
will StarRocks use smaller version of inbound_feed_mv assuming it has selected columns? if no, why?
r
In your specific scenario, StarRocks will likely not use a smaller, pre-aggregated version of 
inbound_feed_mv
to satisfy that query. Here is why, along with the specific limitations that prevent the rewrite: 1. The "Masking Logic" Dependency (
CASE WHEN
) In your Logical View (LV), the 
gross_units
column is not a simple pass-through. It is defined as: 
CASE WHEN (rsm.client_uuid IS NULL) THEN combined.gross_units END
When you run 
SELECT sum(gross_units) ...
, the optimizer expands the LV. It sees that to calculate the sum, it must first perform a 
LEFT JOIN
with 
restricted_suppliers_mv
(rsm) to evaluate that 
CASE
expression for every row. * The Limitation: If your "smaller version" of 
inbound_feed_mv
is pre-aggregated (e.g., grouped by 
warehouse_zone
), it has already lost the row-level detail (
client_uuid
, 
third_party_ref
, 
batch_id
) required to perform the join with 
rsm
. * Result: The optimizer cannot "push" the sum down into a smaller MV because the sum depends on a join condition that hasn't happened yet. 2. View Expansion and SPJG Matching StarRocks attempts to rewrite queries by matching the SPJG (Select-Project-Join-GroupBy) pattern. * After the LV is expanded, your query becomes a complex plan involving 
UNION ALL
, multiple 
LEFT JOINs
, and conditional logic. * To use a smaller MV, that MV's definition must "cover" a subtree of the expanded query plan. _ If you only create a smaller version of 
inbound_feed_mv
, it only covers the bottom leaf of the plan. Because the 
sum()
and 
group by
in your query are at the very _top* (above the Joins and Unions), the optimizer cannot replace the bottom scan with a pre-aggregated result without breaking the join logic. 3. UNION ALL Complexity Your LV uses 
UNION ALL
. While StarRocks (starting from v2.5+) supports rewriting queries that contain 
UNION ALL
, it is much more restrictive. For the rewrite to succeed, the optimizer usually needs to match the structure of the entire Union. If you only provide a smaller MV for one side of the Union (
inbound_feed_mv
), the engine still has to process the other side (
freight_cost_mv
) and the joins, often deciding it is safer/easier to just hit the original MVs. How to make it work If you want to benefit from query rewrite in this scenario, you have two main options: 1. Create an MV on the Logical View itself: Instead of creating a smaller version of the underlying components, create a new Materialized View that uses the Logical View as its base:
Copy code
sql
    CREATE MATERIALIZED VIEW shipment_log_summary_mv
    AS
    SELECT warehouse_zone, report_week, sum(gross_units)
    FROM shipment_log_lv
    GROUP BY warehouse_zone, report_week;
StarRocks (v3.3+) is excellent at matching queries against MVs built on top of complex LVs. 2. Ensure the "Smaller MV" is not aggregated: If the "smaller version" is simply a filtered or "slimmer" version (contains all join columns like 
client_uuid
, 
batch_id
, etc., but fewer rows/columns), the optimizer can use it. It will replace the scan of 
inbound_feed_mv
with the smaller one and then proceed with the joins as usual. Summary of Limitations for your LV: * Non-deterministic logic: If the 
CASE
logic or Joins make the result non-transparent, rewrite fails. * Join Columns: Any MV used for rewrite must contain all columns used in the LV's joins and 
WHERE
clauses if the aggregation is performed above them. * View-based Rewrite (v3.3+): Ensure you are on v3.3 or later; earlier versions struggle significantly with 
UNION
+ 
JOIN
patterns inside LVs. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
a
@Rocky I've created mv on top of above lv and refresh fails with
Copy code
Refresh mv failed after 1 times, try lock failed: 0, error-msg : java.lang.IllegalArgumentException: Cannot compare PListCell with other type: PRangeCell
	at com.google.common.base.Preconditions.checkArgument(Preconditions.java:218)
	at com.starrocks.sql.common.PListCell.compareTo(PListCell.java:145)
	at com.starrocks.sql.common.PCellWithName.compareTo(PCellWithName.java:65)
	at com.starrocks.sql.common.PCellWithName.compareTo(PCellWithName.java:34)
	at java.base/java.util.TreeMap.put(TreeMap.java:814)
	at java.base/java.util.TreeMap.put(TreeMap.java:534)
	at java.base/java.util.TreeSet.add(TreeSet.java:255)
	at java.base/java.util.AbstractCollection.addAll(AbstractCollection.java:336)
	at java.base/java.util.TreeSet.addAll(TreeSet.java:309)
	at java.base/java.util.TreeSet.<init>(TreeSet.java:160)
	at com.starrocks.sql.common.PCellSortedSet.of(PCellSortedSet.java:83)
	at com.starrocks.sql.common.ListPartitionDiffer.collectBasePartitionCells(ListPartitionDiffer.java:358)
	at com.starrocks.sql.common.ListPartitionDiffer.computePartitionDiff(ListPartitionDiffer.java:381)
	at com.starrocks.sql.common.ListPartitionDiffer.computePartitionDiff(ListPartitionDiffer.java:370)
	at com.starrocks.scheduler.mv.pct.MVPCTRefreshListPartitioner.syncAddOrDropPartitions(MVPCTRefreshListPartitioner.java:133)
	at com.starrocks.mv.refresh.pct.MVPCTRefreshSynchronizer.syncPartitions(MVPCTRefreshSynchronizer.java:163)
	at com.starrocks.mv.refresh.pct.MVPCTRefreshSynchronizer.syncAndCheckPCTPartitions(MVPCTRefreshSynchronizer.java:199)
	at com.starrocks.mv.refresh.pct.MVPCTRefreshSynchronizer.syncAndCheckPCTPartitions(MVPCTRefreshSynchronizer.java:83)
	at com.starrocks.scheduler.mv.pct.MVPCTRefreshProcessor.getProcessExecPlan(MVPCTRefreshProcessor.java:138)
	at com.starrocks.scheduler.MVTaskRunProcessor.doProcessTaskRun(MVTaskRunProcessor.java:337)
	at com.starrocks.scheduler.MVTaskRunProcessor.retryProcessTaskRun(MVTaskRunProcessor.java:303)
	at com.starrocks.scheduler.MVTaskRunProcessor.processTaskRun(MVTaskRunProcessor.java:204)
	at com.starrocks.scheduler.TaskRun.doExecuteTaskRun(TaskRun.java:472)
	at com.starrocks.scheduler.TaskRun.executeTaskRun(TaskRun.java:432)
	at com.starrocks.scheduler.TaskRunExecutor.lambda$executeTaskRun$1(TaskRunExecutor.java:66)
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:840)
r
The error 
java.lang.IllegalArgumentException: Cannot compare PListCell with other type: PRangeCell
indicates a conflict between List Partitioning and Range Partitioning in your Materialized View (MV) hierarchy. In StarRocks, when you create a partitioned MV on top of other MVs (Nested MV), the system uses Partition Change Tracking (PCT) to determine which specific partitions need refreshing. This error occurs because the refresh engine is trying to compare or synchronize partitions from your base MVs, but they are using incompatible partitioning strategies. Why this is happening Based on your Logical View (LV), you are performing a 
UNION ALL
between 
inbound_feed_mv
and 
freight_cost_mv
. Most likely: 1. 
inbound_feed_mv
 is using Range Partitioning (e.g., 
PARTITION BY report_week
defined as ranges). 2. 
freight_cost_mv
 is using List Partitioning (e.g., 
PARTITION BY LIST (report_week)
or the newer expression partitioning that inferred a list). 3. The new MV on top of the LV is likely partitioned. When it tries to "sync" its partitions with the base MVs, it encounters a 
PRangeCell
(Range) from one side of the union and a 
PListCell
(List) from the other. The code currently cannot compare these two types to calculate the "delta" for refresh. How to Fix It 1. Align the Partitioning of Base MVs The most robust fix is to ensure that both 
inbound_feed_mv
and 
freight_cost_mv
use the exact same partitioning type. * If both are partitioned by 
report_week
, ensure they both use Range or both use List/Expression partitioning. * In newer StarRocks versions, it is highly recommended to use Expression Partitioning (e.g., 
PARTITION BY (report_week)
) for both, as this is the unified path. 2. Create the Top-Level MV without Partitioning If you don't strictly need the top-level MV to refresh incrementally partition-by-partition, you can create it without a 
PARTITION BY
clause:
Copy code
sql
CREATE MATERIALIZED VIEW shipment_log_summary_mv
REFRESH ASYNC
AS 
SELECT ... FROM shipment_log_lv ...
This will perform a Full Refresh. While slower, it bypasses the partition synchronization logic (PCT) and avoids the comparison error. 3. Use 
FORCE
Refresh or Disable Partition Tracking You can try to hint the MV to ignore the partition dependency of one of the bases, though this is less common for nested MVs. However, usually, the failure happens during the initial refresh/sync phase. Summary of the Conflict The StarRocks optimizer and refresh scheduler (as of v3.x) require partition consistency when propagating partition information through a 
UNION
. You cannot "merge" a Range-partitioned table and a List-partitioned table into a single partitioned MV if you want incremental refreshes to work. Check your DDLs: Run 
SHOW CREATE TABLE inbound_feed_mv
and 
SHOW CREATE TABLE freight_cost_mv
. If one has 
PARTITION BY RANGE
and the other has 
PARTITION BY LIST
(or no range definitions), that is the source of the exception. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/materialized_view_task_run_details.md • knowledge/trouble_shooting/fe_deadlock_problems.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
3 Views
Open in Slack
PreviousNext
Powered by