ProjectSet has wrong sort order caused by projected_row_id

[xxchan]

          ![image](https://github.com/risingwavelabs/risingwave/assets/14160025/277f09d6-078e-4791-906b-d337fcba3e9f)

Still flaky? 👀

https://buildkite.com/risingwavelabs/pull-request/builds/52699#01904eb3-fde8-4565-8072-b1452cac7162

Originally posted by @yezizp2012 in #17441 (comment)

[yuhao-su]

Yes https://buildkite.com/risingwavelabs/pull-request/builds/52740#0190521c-7045-40ed-9e9f-71d894f63a00

[xxchan]

dev=> explain WITH arr AS (SELECT header_col_combined FROM additional_columns limit 1)
SELECT unnest(header_col_combined) FROM arr ORDER BY 1;
┌───────────────────────────────────────────────────────────────────────────────────────────────┐
│                                          QUERY PLAN                                           │
├───────────────────────────────────────────────────────────────────────────────────────────────┤
│ BatchProject { exprs: [Unnest($0)] }                                                          │
│ └─BatchSort { order: [projected_row_id ASC] }      <--------                                            │
│   └─BatchProjectSet { select_list: [Unnest($0)] }                                             │
│     └─BatchLimit { limit: 1, offset: 0 }                                                      │
│       └─BatchExchange { order: [], dist: Single, sequential: true }                           │
│         └─BatchLimit { limit: 1, offset: 0 }                                                  │
│           └─BatchScan { table: additional_columns, columns: [header_col_combined], limit: 1 } │
└───────────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)

[xxchan]

dev=> select unnest(array[1,2,1]) order by 1;
┌────────┐
│ unnest │
├────────┤
│      1 │
│      2 │
│      1 │
└────────┘
(3 rows)

dev=> explain select unnest(array[1,2,1]) order by 1;
┌───────────────────────────────────────────────────────────────────────────┐
│                                QUERY PLAN                                 │
├───────────────────────────────────────────────────────────────────────────┤
│ BatchProject { exprs: [Unnest(ARRAY[1, 2, 1]:List(Int32))] }              │
│ └─BatchSort { order: [projected_row_id ASC] }                             │
│   └─BatchProjectSet { select_list: [Unnest(ARRAY[1, 2, 1]:List(Int32))] } │
│     └─BatchValues { rows: [[]] }                                          │
└───────────────────────────────────────────────────────────────────────────┘

dev=> explain select 6 as x, unnest(array[1,2,1]) order by x;
┌────────────────────────────────────────────────────────────────────────────────────┐
│                                     QUERY PLAN                                     │
├────────────────────────────────────────────────────────────────────────────────────┤
│ BatchProject { exprs: [6:Int32, Unnest(ARRAY[1, 2, 1]:List(Int32))] }              │
│ └─BatchSort { order: [projected_row_id ASC] }                                      │
│   └─BatchProjectSet { select_list: [6:Int32, Unnest(ARRAY[1, 2, 1]:List(Int32))] } │
│     └─BatchValues { rows: [[]] }                                                   │
└────────────────────────────────────────────────────────────────────────────────────┘
(4 rows)

dev=> explain select 6 as x, unnest(array[1,2,1]) order by unnest;
┌────────────────────────────────────────────────────────────────────────────────────┐
│                                     QUERY PLAN                                     │
├────────────────────────────────────────────────────────────────────────────────────┤
│ BatchProject { exprs: [6:Int32, Unnest(ARRAY[1, 2, 1]:List(Int32))] }              │
│ └─BatchSort { order: [6:Int32 ASC] }                                               │
│   └─BatchProjectSet { select_list: [6:Int32, Unnest(ARRAY[1, 2, 1]:List(Int32))] } │
│     └─BatchValues { rows: [[]] }                                                   │
└────────────────────────────────────────────────────────────────────────────────────┘

[xxchan]

Also for streaming

explain create materialized view mv as select unnest(array[1,2,1]) order by 1 limit 1;

┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                        QUERY PLAN                                                                        │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ StreamMaterialize { columns: [projected_row_id(hidden), unnest, _row_id(hidden)], stream_key: [], pk_columns: [projected_row_id], pk_conflict: NoCheck } │
│ └─StreamTopN [append_only] { order: [projected_row_id ASC], limit: 1, offset: 0 }                                                                        │
│   └─StreamProjectSet { select_list: [Unnest(ARRAY[1, 2, 1]:List(Int32)), $0] }                                                                           │
│     └─StreamValues { rows: [[0:Int64]] }                                                                                                                 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘