Window bound offset field is too restrictive for RANGE mode

[vbarua]

The current definitions of Preceding and Following Window Bounds both declare the offset as

int64 offset = 1;

with a note

A strictly positive integer specifying the number of records that the window extends back from the current record. Required.

This is true for ROWS and GROUPS mode (the latter of which is not yet supported in Substrait). However RANGE mode allows more than just integer offsets. As described in the Postgres docs:

In RANGE mode, these options require that the ORDER BY clause specify exactly one column. The offset specifies the maximum difference between the value of that column in the current row and its value in preceding or following rows of the frame. The data type of the offset expression varies depending on the data type of the ordering column. For numeric ordering columns it is typically of the same type as the ordering column, but for datetime ordering columns it is an interval.

The offset field should be reworked to allow fully describing bounds using RANGE mode, which is effectively all numeric types + intervals

Sources:

• https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
• https://dev.mysql.com/doc/refman/8.3/en/window-functions-frames.html
• https://trino.io/blog/2021/03/10/introducing-new-window-features.html#-full-support-for-frame-type-range