The default framing option for over window with order by is different from PG

[chenzl25]

Describe the bug

The default framing option is RANGE UNBOUNDED PRECEDING in PG, but in RisingWave we use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. There are some differences between them.

Error message/log

No response

To Reproduce

CREATE TABLE integers(i INTEGER);
INSERT INTO integers VALUES (1), (2), (3), (NULL);
select SUM(s1.i) OVER (partition by 'const_number' ORDER BY s1.i ) FROM integers s1, integers s2;

RisingWave result:

 sum
-----
   1
   2
   3
   4
   6
   8
  10
  12
  15
  18
  21
  24
  24
  24
  24
  24
(16 rows)

Expected behavior

 sum
-----
   4
   4
   4
   4
  12
  12
  12
  12
  24
  24
  24
  24
  24
  24
  24
  24
(16 rows)

How did you deploy RisingWave?

No response

The version of RisingWave

No response

Additional context

No response

[fuyufjh]

I am not sure whether we should change the default behavior to be aligned with PG. What's the default behaivor of Flink?

[stdrc]

This is a known limitation, and we emphasized this in our doc https://docs.risingwave.com/docs/current/window-functions/#syntax. Will align with PG after implementing https://github.com/risingwavelabs/risingwave/issues/11109

[github-actions[bot]]

This issue has been open for 60 days with no activity.

If you think it is still relevant today, and needs to be done in the near future, you can comment to update the status, or just manually remove the no-issue-activity label.

You can also confidently close this issue as not planned to keep our backlog clean. Don't worry if you think the issue is still valuable to continue in the future. It's searchable and can be reopened when it's time. 😄