feat: support arbitrary expression as time column of time window function

xiangjinwu commented 1 year ago

Describe the bug

In a time window function like tumble or hop:

TUMBLE(table_or_source, start_time, window_size)
HOP(table_or_source, start_time, hop_size, window_size)

The current implementation requires start_time to be a column name rather than arbitrary expression. But the error message is misleading.

To Reproduce

CREATE TABLE t (
    tag VARCHAR,
    value INT,
    ms BIGINT
);

CREATE MATERIALIZED VIEW mv1 AS
SELECT
    window_start,
    tag,
    SUM(value) as total
FROM
    TUMBLE(
        t,
        to_timestamp(ms / 1e3),
        INTERVAL '10' MINUTE
    )
GROUP BY
    window_start,
    tag;

Current error message is:

ERROR:  QueryError: Bind error: the 2st arg of window table function should be a timestamp with time zone, timestamp or date column

Expected behavior

Pick one of the following:

Enhance binder+planner so that we can support arbitrary expression as start_time, as long as it has correct return type.
Make the error message more precise and helpful, guiding user to use with-cte when it is an expression rather than a column name.

https://github.com/risingwavelabs/risingwave/blob/a061017590a291d2adf1531507fdc4d27d2ad44f/src/frontend/src/binder/relation/window_table_function.rs#L82-L92

Additional context

Note that we do have strict requirement of the 1st and 3rd argument:

table_or_source is required to be a simple name (table, source, mv, cte, view) rather than an arbitrary relation (subquery, join, table function). It is challenging to support them in the parser. #2733
window_size (and hop_size) is required to be a literal rather than arbitrary expression returning interval. It is practical and enables us to check validity in frontend. https://github.com/risingwavelabs/risingwave/issues/2469#issuecomment-1194115859

Current workaround is to use with-cte syntax:

CREATE MATERIALIZED VIEW mv1 AS
WITH t AS (SELECT
    tag, value,
    to_timestamp(ms / 1e3) AS ts
FROM t)
SELECT
    window_start,
    tag,
    SUM(value) as total
FROM
    TUMBLE(
        t,
        ts,
        INTERVAL '10' MINUTE
    )
GROUP BY
    window_start,
    tag;

hzxa21 commented 1 year ago

table_or_source is required to be a simple name (table, source, mv, cte, view) rather than an arbitrary relation (subquery, join, table function).

I encounter a similar issue recently and I just workaround it via CREATE VIEW

fuyufjh commented 1 year ago

Is it done?

xiangjinwu commented 1 year ago

Current status: The error message has been fixed. It guides the user to use CTE or view as workaround.

To improve user experience, we may support arbitrary expression at this place i.e. provide a syntax sugar rather than writing CTE. So keeping this issue open but at a later release.

risingwavelabs / risingwave