http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I am having an issue where MV rewri...
# questions-and-troubleshooting
c
@Rocky I am having an issue where MV rewrite fails on a bounded time predicate, but succeeds on an open-ended one, even though both are aligned with the MV please see attached logs
session.out
r
This is a common observation in StarRocks when working with Materialized View (MV) rewrites and time-based predicates. Even if your predicates appear "aligned" with the MV, the optimizer treats bounded ranges (e.g., 
dt BETWEEN A AND B
) and open-ended ranges (e.g., 
dt >= A
) differently during the rewrite phase. Here are the most likely reasons why your bounded time predicate is failing rewrite while the open-ended one succeeds: 1. Data Type Mismatch (The most common cause) StarRocks is strict about type alignment for range matching. * The Issue: If your base table/MV column is of type 
DATE
, but your bounded query uses 
DATETIME
strings (e.g., 
'2023-01-01 00:00:00'
) or vice versa, the optimizer may fail to mathematically prove that the bounded range is a strict subset of the MV's data. * Why open-ended works: Open-ended predicates often bypass some of the stricter range-intersection logic that bounded predicates trigger. * Solution: Ensure the constants in your query exactly match the column type in the MV (e.g., use 
DATE '2023-01-01'
if the column is a 
DATE
). 2. Missing Column in the MV 
SELECT
List For a range predicate to be rewritable, the column used in the 
WHERE
clause must be present in the 
SELECT
list of the Materialized View definition. * The Check: Verify that your MV definition includes the time column (e.g., 
dt
) in its 
SELECT
statement. * Example:
Copy code
sql
    -- If this fails:
    SELECT sum(val) FROM base WHERE dt >= '2023-01-01' AND dt <= '2023-01-10';
    -- Ensure your MV is NOT just:
    CREATE MATERIALIZED VIEW mv1 AS SELECT sum(val) FROM base GROUP BY ...;
    -- It MUST be:
    CREATE MATERIALIZED VIEW mv1 AS SELECT dt, sum(val) FROM base GROUP BY dt, ...;
3. Partition Staleness and "Union Rewrite" If your MV is partitioned and some partitions are stale or have not been refreshed yet: * Open-ended Success: StarRocks has a feature called Partition Union Rewrite. If you query 
dt >= '2023-01-01'
, StarRocks can rewrite the query to take what it can from the MV and "union" the remaining/stale data from the base table. * Bounded Failure: Sometimes, the logic for "unioning" a bounded range is more complex for the optimizer, especially if the boundaries don't align perfectly with the partition edges or if 
query_rewrite_consistency
is set to 
ENFORCED
. * Solution: Check the refresh status of your partitions using 
SHOW MATERIALIZED VIEWS
and consider setting 
PROPERTIES("query_rewrite_consistency" = "LOOSE")
temporarily to see if it resolves the issue. 4. Predicate Normalization (BETWEEN vs >= AND <=) The optimizer sometimes handles 
BETWEEN
differently than explicit 
>=
and 
<=
operators. * Test: If you are using 
BETWEEN
, try rewriting the query to use 
dt >= 'start' AND dt <= 'end'
to see if the rewrite triggers. How to Diagnose To find the exact reason for the failure, run the following command in your SQL client:
Copy code
sql
TRACE REASON MV <your_query_here>;
* What to look for: Look for messages like 
cannot rewrite range predicate
or 
columns in query not found in MV
. * If you are on version 3.2+, you can also use 
TRACE LOGS MV <query>;
for a more detailed step-by-step breakdown of why each candidate MV was rejected. Recommendation: Check if your query time constants include a time component (HHmmss) while your MV column is just a DATE. This is the #1 cause of "aligned" but failing bounded predicates failing. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • table_design/data_distribution/dynamic_partitioning.md • faq/Sql_faq.md • release_notes/release-3.1.md
The issue you are describing—where an open-ended predicate (e.g., 
>= '2026-05-07'
) triggers a rewrite but a bounded one (e.g., 
BETWEEN
or 
>= AND <=
) fails—is usually caused by partition boundary misalignment or data consistency checks when the query boundary overlaps into a stale or non-existent partition. Based on your query, here are the most likely causes and how to resolve them: 1. Partition Boundary "Leaking" (Inclusive vs. Exclusive) In StarRocks, partitions are typically left-closed and right-open 
[start, end)
. * The Issue: Your query uses 
event_timestamp <= cast('2026-05-08 00:00:00' as datetime)
. * The timestamp 
'2026-05-08 00:00:00'
is technically the start of the next partition (May 8th). * If your Materialized View (MV) has only refreshed data up to the May 7th partition, it contains data for 
[2026-05-07, 2026-05-08)
. * Because your query is inclusive (
<=
), it requires the single point at exactly midnight on the 8th. If that May 8th partition is stale or hasn't been created in the MV yet, the rewrite for the bounded range fails because the optimizer cannot satisfy the entire range from the "active" MV partitions. * Why open-ended works: When you use 
event_timestamp >= '2026-05-07'
, StarRocks can use Partition Union Rewrite. It takes what it can from the MV (the May 7th partition) and unions it with the base table for everything from May 8th onwards. Bounded ranges are often more strictly matched. * Solution: Try changing the upper bound to be exclusive:
Copy code
sql
    WHERE event_timestamp >= '2026-05-07 00:00:00'
      AND event_timestamp <  '2026-05-08 00:00:00' -- Changed <= to <
2. Missing Column in MV 
SELECT
List For a query with a 
WHERE
filter to be rewritten, the filtering column must be present in the 
SELECT
list of the Materialized View definition. * The Check: Ensure your MV definition includes 
event_timestamp
. If it is an aggregate MV, 
event_timestamp
must be in the 
GROUP BY
clause. * Example:
Copy code
sql
    -- This MV can rewrite your query:
    CREATE MATERIALIZED VIEW events_mv AS
    SELECT event_timestamp, COUNT(1)
    FROM events.trader_delivery_raw
    GROUP BY event_timestamp;
3. Consistency Properties If your MV is slightly behind the base table, the optimizer might reject a bounded rewrite to ensure data integrity. * Solution: Check if setting the following session variables allows the rewrite to succeed:
Copy code
sql
    SET query_rewrite_consistency = 'loose';
    -- Or for the specific MV:
    ALTER MATERIALIZED VIEW your_mv_name SET ("query_rewrite_consistency" = "loose");
4. How to Interpret your 
TRACE REASON
 Since you have already run the 
TRACE REASON
command, look for these specific keywords in the output: * "The partitions of MV are out of date": Confirms that the May 8th boundary is hitting a stale partition. * "The query contains columns that are not in the MV": Confirms 
event_timestamp
is missing from the MV's 
SELECT
list. * "Predicate cannot be compensated": Means the optimizer couldn't find a way to "patch" the missing May 8th midnight data from the base table. Recommended Next Steps: 1. Check MV Status: Run 
SHOW ALTER MATERIALIZED VIEW FROM events;
and ensure the 
LastRefreshStatus
is 
SUCCESS
. 2. Verify Alignment: Change the 
<=
to 
<
in your query. 3. Check Schema: Ensure the base table column 
event_timestamp
and the MV column have the exact same type (e.g., both 
DATETIME
). If one is 
DATETIME
and the other is 
DATE
, the bounded rewrite will often fail. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
c
@Rocky Here is my base table
Copy code
CREATE TABLE `trader_delivery_raw` (
  `event_timestamp` datetime NOT NULL ...
Here is my MV
Copy code
SELECT
    -- Time dimensions
    date_trunc('minute', event_timestamp) AS event_minute,
    date_trunc('hour', event_timestamp) AS event_hour, ...
FROM
    trader_delivery_raw
Here is my query
Copy code
trace reason SELECT COUNT(1)  FROM events.trader_delivery_raw WHERE event_timestamp >= cast('2026-05-07 00:00:00' as datetime) and event_timestamp < cast('2026-05-08 00:00:00' as datetime);
Here is my output
Copy code
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String                                                                                                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|     [RBO] [PHASE1] MV rewrite fail for mv_trader_delivery_by_minute: Rewrite scalar operator failed: 1: event_timestamp <= 2026-05-08 00:00:00 cannot be rewritten                                      |
|     [RBO] [PHASE1] MV rewrite fail for mv_trader_delivery_by_minute: Rewrite scalar operator failed: date_trunc(cast(hour as datetime), 1: event_timestamp) >= 2026-05-07 00:00:00 cannot be rewritten  |
|     [RBO] [PHASE2] MV rewrite fail for mv_trader_delivery_by_minute: Rewrite scalar operator failed: 1: event_timestamp <= 2026-05-08 00:00:00 cannot be rewritten                                      |
|     [RBO] [PHASE2] MV rewrite fail for mv_trader_delivery_by_minute: Rewrite scalar operator failed: date_trunc(cast(hour as datetime), 1: event_timestamp) >= 2026-05-07 00:00:00 cannot be rewritten  |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Nothing is stale, MV is current.
r
The issue you are seeing is a classic case of mathematical safety in query rewrites involving truncated time columns. In your Materialized View (MV), you have 
date_trunc('hour', event_timestamp)
but you do not have the raw 
event_timestamp
column. When you query the base table using the raw column, the optimizer tries to map your filter to the truncated column in the MV. Why the Bounded Predicate (
<=
) Fails The optimizer rejects the rewrite for 
<=
because it would lead to incorrect results. Here is the logic: 1. The Query: 
WHERE event_timestamp <= '2026-05-08 00:00:00'
2. The MV Column: 
event_hour
(which is 
date_trunc('hour', event_timestamp)
) 3. The Conflict: If the optimizer tried to use the MV, it would have to filter by 
event_hour <= '2026-05-08 00:00:00'
. 4. The Data Leak: Imagine a record with a raw timestamp of 
2026-05-08 00:30:00
. * This record should be excluded by your query (since 
00:30
> 
00:00
). * However, its 
event_hour
is 
2026-05-08 00:00:00
. * If the MV were used, this record would be incorrectly included because its truncated value satisfies the filter. 5. The Conclusion: Since the raw 
event_timestamp
is not in the MV to perform a "cleanup" filter (predicate compensation), the optimizer must fail the rewrite to ensure data accuracy. Why the Open-Ended Predicate (
>=
) Succeeds Open-ended lower bounds are mathematically "safe" when aligned with the truncation: * If 
event_timestamp >= '2026-05-07 00:00:00'
, then any record's 
event_hour
will also be 
>= '2026-05-07 00:00:00'
. * Crucially, there are no records where the truncated value is 
>=
the start time but the raw value is 
<
the start time (assuming alignment at the hour mark). Therefore, no "extra" data is pulled in, and the rewrite is safe. How to Fix It You have three options to resolve this: 1. Add the raw column to the MV (Recommended) Add 
event_timestamp
to the 
SELECT
list of your MV. This allows the optimizer to use the MV and then apply a "residual filter" on the raw column to trim off the extra seconds/minutes.
Copy code
sql
CREATE MATERIALIZED VIEW mv_trader_delivery_by_minute AS
SELECT 
    event_timestamp, -- Add this
    date_trunc('minute', event_timestamp) AS event_minute,
    ...
2. Use an exclusive upper bound If you change your query to use 
<
(exclusive) and the value is aligned with your truncation granularity, the rewrite will often succeed because the mapping becomes safe.
Copy code
sql
WHERE event_timestamp >= '2026-05-07 00:00:00' 
  AND event_timestamp <  '2026-05-08 00:00:00' -- Changed <= to <
3. Query the truncated column directly If your query doesn't need second-level precision, query the column that exists in the MV:
Copy code
sql
SELECT COUNT(1) 
FROM events.trader_delivery_raw 
WHERE event_hour >= '2026-05-07 00:00:00' 
  AND event_hour <  '2026-05-08 00:00:00';
Summary of your Trace: The line 
1: event_timestamp <= 2026-05-08 00:00:00 cannot be rewritten
is the optimizer's way of saying "I can't guarantee that 
event_hour
covers exactly this range without grabbing extra data from the next hour."
The discrepancy you're seeing in the 
trace reason
output is the key to solving this. Even though you've updated your query to use 
<
(exclusive), the trace output you provided still shows the optimizer evaluating 
event_timestamp <= 2026-05-08 00:00:00
. This is the reason for the failure. Why the Bounded Predicate Fails In StarRocks, when you query a raw column (
event_timestamp
) but the Materialized View (MV) only contains truncated columns (
event_minute
, 
event_hour
), the optimizer must perform a "mathematical safety" check. 1. The "Data Leak" Problem with 
<=
: * Your query asks for data up to and including exactly 
2026-05-08 00:00:00
. * If the optimizer tried to use the MV's 
event_hour
column, it would look for 
event_hour <= 2026-05-08 00:00:00
. * However, a record from the base table with a timestamp of 
2026-05-08 00:30:00
would have an 
event_hour
of 
2026-05-08 00:00:00
. * If the rewrite happened, this record (the 00:30 one) would be incorrectly included in your result because its truncated value satisfies the filter, even though its raw value does not. * Because the raw 
event_timestamp
is not in the MV, the optimizer has no way to apply a "residual filter" to remove those extra 30 minutes. Therefore, it rejects the rewrite to ensure data accuracy. 2. Why Open-Ended (
>=
) Works: * 
event_timestamp >= '00:00:00'
is mathematically equivalent to 
event_hour >= '00:00:00'
. There is no risk of "leaking" data from before the start time because truncation always moves time backwards. Why the 
<
(Exclusive) might still be showing as 
<=
 If you are sure you ran the query with 
event_timestamp < '2026-05-08 00:00:00'
but the trace still shows 
<=
, check for these three things: * Predicate Normalization: If you are using 
BETWEEN
, StarRocks always expands it to 
>=
and 
<=
. * Stale Trace: Ensure you are looking at the fresh output of the 
trace reason
command for the specific query with the 
<
operator. * Type Alignment: If the 
event_timestamp
is a 
DATETIME
and you are comparing it against a constant, StarRocks sometimes normalizes ranges. However, 
<
should typically stay 
<
unless the constant is modified. How to Fix It To allow StarRocks to rewrite this query (and any range query) reliably, you should use one of these two approaches: Option 1: Add the Raw Column to the MV (Recommended) Add the raw 
event_timestamp
to the 
SELECT
list of your MV. For an aggregate MV, this means adding it to the 
GROUP BY
. Note: If this makes the MV too large (too many rows), use Option 2. By having the raw column available, the optimizer can use the MV to find the general range and then apply the exact filter 
WHERE event_timestamp < ...
on the results. Option 2: Query the Truncated Column Directly If your application can be adjusted, query the column that exists in the MV. This removes all ambiguity for the optimizer:
Copy code
sql
SELECT COUNT(1) 
FROM events.trader_delivery_raw 
WHERE event_minute >= '2026-05-07 00:00:00' 
  AND event_minute <  '2026-05-08 00:00:00';
Option 3: Verify Alignment If you must query the base table, ensure your upper bound is strictly exclusive (
<
) and perfectly aligned with the granularity in the MV (e.g., exactly on the minute or hour mark). If you use 
2026-05-08 00:00:00
and the MV is truncated by 
minute
, it should theoretically work if the optimizer recognizes the alignment. Summary Recommendation: Double-check your query for any 
BETWEEN
usage and try setting 
SET query_rewrite_consistency = 'loose';
in your session to see if the optimizer becomes less conservative with the alignment check. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md
c
🤦
Open in Slack
PreviousNext
Powered by