You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2026/08/01 10:37:51 UTC
Re: [I] Support expression based window function (value based `RANGE`) [datafusion]
alamb commented on issue #15714:
URL: https://github.com/apache/datafusion/issues/15714#issuecomment-5151056351
I updated the title of this ticket to describe the gap in feature (rather than this is a bug)
I think the core limitation today in DataFusion is that the window size must be a constant (either rows or expression). Window sizes that are expressions themselves are not supported.
As an example of what is supported today in DataFusion (54)
```sql
> SELECT
id, ts, val,
-- ROWS: fixed count of physical rows (1 preceding + current)
SUM(val) OVER (
ORDER BY ts, id
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS rows_frame,
-- RANGE: value-based offset on the ORDER BY column (1 day window)
SUM(val) OVER (
ORDER BY ts
RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
) AS range_frame,
-- GROUPS: counts distinct peer groups (tied ORDER BY values) as one unit
SUM(val) OVER (
ORDER BY ts
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS groups_frame
FROM t
ORDER BY ts, id;
+----+---------------------+-----+------------+-------------+--------------+
| id | ts | val | rows_frame | range_frame | groups_frame |
+----+---------------------+-----+------------+-------------+--------------+
| 1 | 2024-01-01T00:00:00 | 10 | 10 | 30 | 30 |
| 2 | 2024-01-01T00:00:00 | 20 | 30 | 30 | 30 |
| 3 | 2024-01-02T00:00:00 | 30 | 50 | 60 | 60 |
| 4 | 2024-01-04T00:00:00 | 40 | 70 | 40 | 70 |
| 5 | 2024-01-05T00:00:00 | 50 | 90 | 90 | 90 |
+----+---------------------+-----+------------+-------------+--------------+
5 row(s) fetched.
Elapsed 0.002 seconds.
```
Here are examples of queries with arbitrary expressions that don't work
```sql
> -- FAILS: bound is an *expression* (spec's `expr PRECEDING`), issue #15714
SELECT val, SUM(val) OVER (
ORDER BY ts
RANGE BETWEEN (INTERVAL '1' DAY + INTERVAL '1' DAY) PRECEDING AND CURRENT ROW
) FROM t;
Error during planning: Invalid window frame: frame offsets for RANGE must be either a numeric value, a string value or an interval
```
```sql
> -- FAILS: same problem for ROWS with an integer expression
SELECT val, SUM(val) OVER (
ORDER BY ts
ROWS BETWEEN (1 + 1) PRECEDING AND CURRENT ROW
) FROM t;
Error during planning: Invalid window frame: frame offsets for ROWS / GROUPS must be non negative integers
```
<details><summary>SQL Source</summary>
<p>
```sql
CREATE TABLE t (id INT, ts TIMESTAMP, val INT) AS VALUES
(1, TIMESTAMP '2024-01-01 00:00:00', 10),
(2, TIMESTAMP '2024-01-01 00:00:00', 20), -- same ts as row 1 -> tied peer group
(3, TIMESTAMP '2024-01-02 00:00:00', 30),
(4, TIMESTAMP '2024-01-04 00:00:00', 40), -- gap of 2 days from previous
(5, TIMESTAMP '2024-01-05 00:00:00', 50);
SELECT
id, ts, val,
-- ROWS: fixed count of physical rows (1 preceding + current)
SUM(val) OVER (
ORDER BY ts, id
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS rows_frame,
-- RANGE: value-based offset on the ORDER BY column (1 day window)
SUM(val) OVER (
ORDER BY ts
RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
) AS range_frame,
-- GROUPS: counts distinct peer groups (tied ORDER BY values) as one unit
SUM(val) OVER (
ORDER BY ts
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS groups_frame
FROM t
ORDER BY ts, id;
```
This is the smae as DuckDB
```sql
memory D CREATE TABLE t AS
SELECT * FROM (VALUES
(1, TIMESTAMP '2024-01-01 00:00:00', 10),
(2, TIMESTAMP '2024-01-01 00:00:00', 20), -- same ts as row 1 -> tied peer group
(3, TIMESTAMP '2024-01-02 00:00:00', 30),
(4, TIMESTAMP '2024-01-04 00:00:00', 40), -- gap of 2 days from previous
(5, TIMESTAMP '2024-01-05 00:00:00', 50)
) AS t(id, ts, val);
memory D SELECT
id, ts, val,
-- ROWS: fixed count of physical rows (1 preceding + current)
SUM(val) OVER (
ORDER BY ts, id
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS rows_frame,
-- RANGE: value-based offset on the ORDER BY column (1 day window)
SUM(val) OVER (
ORDER BY ts
RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
) AS range_frame,
-- GROUPS: counts distinct peer groups (tied ORDER BY values) as one unit
SUM(val) OVER (
ORDER BY ts
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS groups_frame
FROM t
ORDER BY ts, id;
┌───────┬─────────────────────┬───────┬────────────┬─────────────┬──────────────┐
│ id │ ts │ val │ rows_frame │ range_frame │ groups_frame │
│ int32 │ timestamp │ int32 │ int128 │ int128 │ int128 │
├───────┼─────────────────────┼───────┼────────────┼─────────────┼──────────────┤
│ 1 │ 2024-01-01 00:00:00 │ 10 │ 10 │ 30 │ 30 │
│ 2 │ 2024-01-01 00:00:00 │ 20 │ 30 │ 30 │ 30 │
│ 3 │ 2024-01-02 00:00:00 │ 30 │ 50 │ 60 │ 60 │
│ 4 │ 2024-01-04 00:00:00 │ 40 │ 70 │ 40 │ 70 │
│ 5 │ 2024-01-05 00:00:00 │ 50 │ 90 │ 90 │ 90 │
└───────┴─────────────────────┴───────┴────────────┴─────────────┴──────────────┘
```
</p>
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org