http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> We're on 3.5.2, building rolling-wi...
# questions-and-troubleshooting
a
@Rocky We're on 3.5.2, building rolling-window analytics (e.g. trailing 7-hour avg). Today we emit row-based frames: SUM(rev) OVER (ORDER BY ts ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) This breaks on sparse data — gaps in the time series silently shift the window into the wrong calendar range. The clean fix is value-based RANGE + INTERVAL bounds: SUM(rev) OVER (ORDER BY ts RANGE BETWEEN INTERVAL '7' HOUR PRECEDING AND INTERVAL '1' HOUR PRECEDING) On 3.5.2 the planner rejects this for analytic windows. 1. Supported in any current release we missed? 2. On the roadmap? Target version? 3. Recommended workaround besides upstream densification? Docs we checked: https://docs.starrocks.io/docs/sql-reference/sql-functions/Window_function/
r
The behavior you're seeing is expected on v3.5.2. While StarRocks supports the 
RANGE
frame for 
UNBOUNDED
bounds, the support for value-based offsets with 
INTERVAL
 (e.g., 
RANGE BETWEEN INTERVAL '7' HOUR PRECEDING
) is a more recent addition or improvement in the planner. 1. Supported Version? As of v3.5.x, StarRocks generally expects a numeric offset 
m
in 
RANGE BETWEEN m PRECEDING
when the 
ORDER BY
column is numeric. The specific syntax using 
INTERVAL
with 
DATETIME
columns is part of the v4.0 roadmap and has been targeted for compatibility with engines like Trino. Since you are currently in May 2026 (per the context), StarRocks v4.0 (released in April 2026) is the version where major enhancements to window frame specifications (including better alignment with ANSI SQL for 
RANGE
frames) were finalized. 2. Recommended Workaround: Unix Timestamp Conversion You can achieve the "clean fix" on your current version (3.5.2) by converting your 
ts
column to a numeric unix timestamp. StarRocks' 
RANGE
frame works well with numeric types and constant offsets. Instead of:
Copy code
sql
-- This fails in 3.5.2 planner
SUM(rev) OVER (
    ORDER BY ts 
    RANGE BETWEEN INTERVAL '7' HOUR PRECEDING AND INTERVAL '1' HOUR PRECEDING
)
Use this workaround:
Copy code
sql
-- Convert to seconds (BIGINT) and use numeric offsets
SUM(rev) OVER (
    ORDER BY UNIX_TIMESTAMP(ts) 
    RANGE BETWEEN 25200 PRECEDING AND 3600 PRECEDING
)
Note: $7 \text{ hours} = 25,200 \text{ seconds}$; $1 \text{ hour} = 3,600 \text{ seconds}$. 3. Other Workarounds (Non-Densified) If you are dealing with massive scale where the window function performance is a bottleneck, consider: * ASOF JOIN: Introduced in StarRocks 3.2, 
ASOF JOIN
is highly optimized for finding the "latest value before X" in sparse time series. While it doesn't directly sum a range, it is often more efficient for "point-in-time" lookups. * Self-Join with Range: For a rolling sum, you can join the table to itself on a range condition. This handles the "sparsity" problem correctly without densification:
Copy code
sql
    SELECT
        a.ts,
        SUM(b.rev) as trailing_avg
    FROM telemetry a
    JOIN telemetry b
      ON  b.ts >= a.ts - INTERVAL 7 HOUR
      AND b.ts <  a.ts - INTERVAL 1 HOUR
    GROUP BY a.ts, ...
Performance Note: This can be slower than window functions but is perfectly accurate for sparse data. * Time Slicing: If your "trailing 7-hour average" can be aligned to fixed 1-hour slots, use 
time_slice()
or 
date_trunc()
with a standard 
GROUP BY
to reduce data volume before applying window functions. Summary * Current (3.5.2): Rejects 
INTERVAL
in 
RANGE
frames. * Target: Upgrade to v4.0 for full ANSI-compliant 
RANGE BETWEEN INTERVAL
support. * Immediate Fix: Use 
UNIX_TIMESTAMP(ts)
and numeric offsets in the 
OVER
clause. References • sql-reference/sql-functions/Window_function.md
a
@Rocky, tried this on StarRocks version 4.0.9 RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING Error: Detail message: The value expression of a PRECEDING/FOLLOWING clause of a RANGE window must be implicitly convertable to the ORDER BY expression's type: interval '7' DAY cannot be implicitly converted to 
base_agg
.
Day
.
r
While StarRocks has been moving toward ANSI/Trino compatibility (including improvements in the v4.x branch), the current planner implementation for 
RANGE
window frames still expects the offset value to be implicitly convertible to the data type of the 
ORDER BY
column. The error you're seeing—`interval '7' DAY cannot be implicitly converted to base_agg.Day`—occurs because the engine is attempting to apply the offset using simple addition/subtraction logic (
Day - offset
), and an 
INTERVAL
type is not directly castable to a 
DATE
or 
DATETIME
type in this specific context. The Solution: Use Numeric Offsets In StarRocks, when using 
RANGE
on time-based columns, the offset should be a numeric literal (integer/decimal). The unit of the numeric value depends on the data type of your 
ORDER BY
column: 1. If the column is `DATE`: The numeric value represents Days. 2. If the column is `DATETIME`: The numeric value represents Seconds. Case A: If 
Day
is a 
DATE
type Change your query to use raw integers (1 unit = 1 day):
Copy code
sql
SELECT 
    Day, 
    SUM(rev) OVER (
        ORDER BY Day 
        RANGE BETWEEN 7 PRECEDING AND 1 FOLLOWING
    ) as rolling_rev
FROM base_agg;
Case B: If 
Day
is a 
DATETIME
type The numeric value is treated as seconds. To offset by days, you must multiply by 86,400:
Copy code
sql
SELECT 
    Day, 
    SUM(rev) OVER (
        ORDER BY Day 
        RANGE BETWEEN 604800 PRECEDING AND 86400 FOLLOWING
    ) as rolling_rev
FROM base_agg;
(7 days = 604,800 seconds; 1 day = 86,400 seconds) Why did the 
INTERVAL
syntax fail in 4.0.9? Although version 4.0 expanded support for many window features (like 
DISTINCT
inside window aggregates), the 
INTERVAL
literal inside 
RANGE
frames is currently only supported when using specific SQL dialects or is still undergoing refinement in the primary planner. Recommended Workaround (The "Safe" Way): If you want to avoid guessing units (seconds vs. days), the most robust method across all StarRocks versions is to force the 
ORDER BY
into a unix timestamp:
Copy code
sql
SUM(rev) OVER (
    ORDER BY UNIX_TIMESTAMP(Day) 
    RANGE BETWEEN 604800 PRECEDING AND 86400 FOLLOWING
)
Summary for your version (4.0.9): * Interval syntax: Not currently supported as a direct literal in 
RANGE
(hence the conversion error). * Numeric syntax: Supported. Use Integers (Days for 
DATE
, Seconds for 
DATETIME
). * Roadmap: Full ANSI 
RANGE BETWEEN INTERVAL
support is targeted for the v4.1+ release cycle to align with standard Trino/Presto behaviors. References • sql-reference/sql-functions/Window_function.md
a
@Rocky, https://github.com/StarRocks/starrocks/pull/73030 in this PR it seems like RANGE with true time semantics is supported. in which version it will be supported?
r
Based on the pull request you identified (#73030), StarRocks is officially adding support for value-based offsets in 
RANGE
frames (including 
INTERVAL
semantics). 1. Which version will support this? This feature is targeted for StarRocks 4.1. As of early June 2026, PR #73030 has been merged into the 
main
development branch. Since your current version is 4.0.9, you are just one minor release cycle behind this functionality. Version 4.1 is the milestone that unifies the window frame syntax with ANSI SQL and Trino-style 
INTERVAL
offsets. 2. Why does it fail in 4.0.9? In the v4.0.x series, the window function planner still relies on a more restrictive type-checking logic for the 
RANGE
offset. It expects the offset value to be a numeric type that matches the unit of the 
ORDER BY
expression (e.g., raw integers for Days if the column is a 
DATE
). It does not yet recognize the 
INTERVAL '7' DAY
object as a valid offset for date-arithmetic within the window frame operator. 3. Recommendation for now (v4.0.9) Until you upgrade to v4.1, you must continue using the numeric workaround mentioned previously. The engine will perform the calculation correctly, but you have to specify the offset in the "base unit" of the column: * For 
DATE
columns (Offset in Days):
Copy code
sql
    RANGE BETWEEN 7 PRECEDING AND 1 FOLLOWING
* For 
DATETIME
columns (Offset in Seconds):
Copy code
sql
    -- 7 Days = 604800 seconds, 1 Day = 86400 seconds
    RANGE BETWEEN 604800 PRECEDING AND 86400 FOLLOWING
Once you move to v4.1, the exact syntax you tried (
RANGE BETWEEN INTERVAL '7' DAY PRECEDING...
) will be supported natively without manual unit conversion.
a
@Rocky does 4.0.9 support PRECEDING AND FOLLOWING?
r
Yes, StarRocks 4.0.9 supports the 
PRECEDING AND FOLLOWING
syntax in window frames. The error you received was not caused by using both bounds (Preceding and Following) simultaneously, but rather by the data type of the offset (
INTERVAL
). Why your previous query failed In version 4.0.9, the planner recognizes the 
RANGE BETWEEN ... PRECEDING AND ... FOLLOWING
structure, but it requires the offset values to be numeric literals (integers/decimals) that are implicitly convertible to the type of your 
ORDER BY
column. The engine currently tries to perform math like 
Day - offset
. Since StarRocks cannot implicitly cast a 
DATE
to an 
INTERVAL
(or vice versa) in this specific windowing logic, it throws the "cannot be implicitly converted" error. How to use it in 4.0.9 To use both 
PRECEDING
and 
FOLLOWING
in a single 
RANGE
frame on your current version, use raw numbers. The unit of the number depends on the column type: 1. If 
Day
is a 
DATE
type (unit = Days):
Copy code
sql
    -- 7 days before to 1 day after
    SUM(rev) OVER (
        ORDER BY Day
        RANGE BETWEEN 7 PRECEDING AND 1 FOLLOWING
    )
2. If 
Day
is a 
DATETIME
type (unit = Seconds):
Copy code
sql
    -- 7 days (604,800s) before to 1 day (86,400s) after
    SUM(rev) OVER (
        ORDER BY Day
        RANGE BETWEEN 604800 PRECEDING AND 86400 FOLLOWING
    )
Support Summary Feature | Supported in 4.0.9? | Supported in 4.1.x? 
ROWS BETWEEN ... PRECEDING AND ... FOLLOWING
| Yes (Numeric offsets) | Yes 
RANGE BETWEEN ... PRECEDING AND ... FOLLOWING
| Yes (Numeric offsets) | Yes 
RANGE BETWEEN INTERVAL ...
| No (Cast error) | Yes (via PR #73030) Recommendation: Stick to the numeric offsets for now. If your column is a 
DATETIME
and the math gets messy, you can use 
ORDER BY UNIX_TIMESTAMP(Day)
to ensure the 
RANGE
units are always in seconds. References • sql-reference/sql-functions/Window_function.md
2 Views
Open in Slack
PreviousNext
Powered by