BatchSort gets dropped from an inner batch query

[kwannoel]

Describe the bug

dev=> explain select * from rw_catalog.rw_event_logs_unordered order by timestamp desc
dev-> ;
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 BatchSort { order: [rw_event_logs_unordered.timestamp DESC] }
 └─BatchScan { table: rw_event_logs_unordered, columns: [unique_id, timestamp, event_type, info] }
(2 rows)


dev=> explain select * from ( select * from rw_catalog.rw_event_logs_unordered order by timestamp desc);
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 BatchScan { table: rw_event_logs_unordered, columns: [unique_id, timestamp, event_type, info] }
(1 row)

Error message/log

No response

To Reproduce

No response

Expected behavior

No response

How did you deploy RisingWave?

No response

The version of RisingWave

No response

Additional context

No response

[xiangjinwu]

It is by design that order by is only effective at top level or part of agg/window function. Do you expect Project/Filter to be order-preserving while Agg/Join are allowed to output in any order? What about other relational operators?