wrong query result of order by ... limit ... offset if subquery contains limit

[silverbullet233]

Steps to reproduce the behavior (Required)

1. CREATE TABLE '...'

CREATE TABLE `t0` (
  `v1` int(11) NULL COMMENT "",
  `v2` int(11) NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`v1`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`v1`) BUCKETS 10
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"storage_format" = "DEFAULT",
"enable_persistent_index" = "false"
);
insert into t0 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);

2. Query

select * from (select * from t0 limit 10) t order by v2 limit 3 offset 2;

Expected behavior (Required)

+------+------+
| v1   | v2   |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
+------+------+
3 rows in set (0.02 sec)

Real behavior (Required)

+------+------+
| v1   | v2   |
+------+------+
|    1 |    1 |
|    3 |    3 |
|    5 |    5 |
+------+------+
3 rows in set (0.02 sec)

StarRocks version (Required)

• main 5fd0757

[silverbullet233]

The following query has the similar issue, it can't get a stable and correct result

select * from (select * from t0 limit 10) t order by v2 limit 3000 offset 2;

• expected behavior

mysql>  select * from (select * from t0 limit 10) t order by v2 limit 3000 offset 2;
+------+------+
| v1   | v2   |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
|    6 |    6 |
|    7 |    7 |
|    8 |    8 |
|    9 |    9 |
|   10 |   10 |
+------+------+
8 rows in set (0.00 sec)

• real behavior

mysql>  select * from (select * from t0 limit 10) t order by v2 limit 3000 offset 2;
+------+------+
| v1   | v2   |
+------+------+
|    7 |    7 |
+------+------+
1 row in set (0.01 sec)

mysql>  select * from (select * from t0 limit 10) t order by v2 limit 3000 offset 2;
Empty set (0.01 sec)

mysql>  select * from (select * from t0 limit 10) t order by v2 limit 3000 offset 2;
+------+------+
| v1   | v2   |
+------+------+
|    9 |    9 |
|   10 |   10 |
+------+------+
2 rows in set (0.02 sec)

mysql>  select * from (select * from t0 limit 10) t order by v2 limit 3000 offset 2;
Empty set (0.02 sec)