feat: OverWindow in the batch mode

[neverchanje]

Describe the bug

On Postgres:

postgres=# create table t (groupid int, username varchar);
CREATE TABLE
postgres=# SELECT row_number() OVER ( PARTITION BY t.groupid ORDER BY t.username) AS usergroupid                                                                                                       FROM                                                                                                                           t;
 usergroupid
-------------
(0 rows)

But it fails on RisingWave:

ERROR:  QueryError: internal error: OverAgg can not be transformed. Plan:
LogicalProject { exprs: [ROW_NUMBER] }
└─LogicalOverAgg { window_function: ROW_NUMBER() OVER(PARTITION BY t.groupid ORDER BY t.username ASC NULLS LAST) }
  └─LogicalScan { table: t, columns: [groupid, username, _row_id] }

Is it a bug or a known issue?

To Reproduce

No response

Expected behavior

No response

Additional context

No response

[xxchan]

It's a known limitation. Currently we only support TopN pattern. General OverWindow (risingwavelabs/rfcs#8) is not implemented yet. cc @TennyZhuang is this feature on our recent schedule? 🤪

[neverchanje]

We can consider adding the support for batch mode first. It won't require much effort.

[fuyufjh]

@neverchanje Please reconfirm the req, thanks!

[neverchanje]

@fuyufjh Need some time for investigation. Will decide the deadline before Mar 27. 🙏

[fuyufjh]

I think this will be done in next quarter's OKR. I'll close this first and @stdrc must have more detailed plans for it.

[stdrc]

Yep we now have too many issues tracking over window🤣. Now I'm tracking batch over window feature in #9931 and overall task list in #9124.

BTW, FYI, batch over window will ready very soon, maybe a few days.