apache / apache/datafusion

Support expression based window function (value based `RANGE`)

Open
#15,714 2 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
Rust
Stars
9.3k
Forks
2.4k
Avg merge
3d 7h
Merged PRs (30d)
344

Description

### Describe the bug

It looks the current range frame defines as

Image

but the range should defines as: from [MySQL spec](https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html)

```
frame_extent:
{frame_start | frame_between}

frame_between:
BETWEEN frame_start AND frame_end

frame_start, frame_end: {
CURRENT ROW
| UNBOUNDED PRECEDING
| UNBOUNDED FOLLOWING
| expr PRECEDING
| expr FOLLOWING
}
```

for example, if we aggregate on a date window( the last month's measure), we can sql like

```sql
select so.shop_id, date_trunc('month', so.order_date) as month, count(distinct si.sale_order_id) as cnt_orders,
sum(quantity) as quantities, sum(amount) as amounts,
first_value( count(distinct si.sale_order_id) ) over w as l1
from sale_items si
left join sale_orders so on so.sale_order_id = si.sale_order_id
group by so.shop_id, date_trunc('month', so.order_date)
window w as ( partition by so.shop_id order by date_trunc('month', so.order_date)
range between interval 31 days PRECEDING and interval 1 days preceding )
order by so.shop_id, date_trunc('month', so.order_date);
```

### To Reproduce

_No response_

### Expected behavior

_No response_

### Additional context

_No response_

Contributor guide

Open the contributing guide

Research direction

Start by comparing DataFusion's current RANGE window-frame behavior with the linked MySQL frame specification and the example query using interval PRECEDING bounds. Trace the window-frame parsing and validation entry points, then verify that expression-based RANGE bounds such as interval 31 days PRECEDING are accepted and produce the expected results.

Written by the indexing model from the issue text.

Assessment

Tech stack
rust, sql
Domain
databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.