This is an automated email from the ASF dual-hosted git repository.
jakevin pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new e19af1b2edb [regression](Nereids) add rule test for push down limit +
sort test (#26642)
e19af1b2edb is described below
commit e19af1b2edb748995a39ea8d22871b8c1f11181a
Author: 谢健 <jianx...@gmail.com>
AuthorDate: Mon Dec 4 14:18:55 2023 +0800
[regression](Nereids) add rule test for push down limit + sort test (#26642)
---
.../limit_push_down/limit_push_down.out | 1115 ++++++++++++++++++++
.../limit_push_down/order_push_down.out | 993 +++++++++++++++++
.../limit_push_down/limit_push_down.groovy | 205 ++++
.../limit_push_down/order_push_down.groovy | 210 ++++
.../suites/nereids_rules_p0/load.groovy | 71 ++
5 files changed, 2594 insertions(+)
diff --git
a/regression-test/data/nereids_rules_p0/limit_push_down/limit_push_down.out
b/regression-test/data/nereids_rules_p0/limit_push_down/limit_push_down.out
new file mode 100644
index 00000000000..0a6d01ac107
--- /dev/null
+++ b/regression-test/data/nereids_rules_p0/limit_push_down/limit_push_down.out
@@ -0,0 +1,1115 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !limit_project --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !limit_offset_project --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !limit_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !limit_semi_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[LEFT_SEMI_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !right_semi_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[RIGHT_SEMI_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !left_anti_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[LEFT_ANTI_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !right_anti_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[RIGHT_ANTI_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !full_outer_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !left_outer_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalLimit[LOCAL]
+--------------PhysicalProject
+----------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !right_outer_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=() build RFs:RF0 id->[id]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t1] apply RFs: RF0
+------------PhysicalDistribute
+--------------PhysicalLimit[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t2]
+
+-- !cross_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------NestedLoopJoin[CROSS_JOIN]
+------------PhysicalLimit[LOCAL]
+--------------PhysicalProject
+----------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalLimit[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t2]
+
+-- !limit_offset_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !limit_distinct --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------PhysicalOlapScan[t1]
+
+-- !limit_offset_agg --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------PhysicalOlapScan[t1]
+
+-- !limit_distinct --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------NestedLoopJoin[CROSS_JOIN]
+--------------PhysicalLimit[LOCAL]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+--------------PhysicalDistribute
+----------------PhysicalProject
+------------------PhysicalOlapScan[t2]
+
+-- !limit_distinct --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------NestedLoopJoin[CROSS_JOIN]
+--------------PhysicalLimit[LOCAL]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+--------------PhysicalDistribute
+----------------PhysicalProject
+------------------PhysicalOlapScan[t2]
+
+-- !limit_distinct --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+--------------PhysicalLimit[LOCAL]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+--------------PhysicalDistribute
+----------------PhysicalProject
+------------------PhysicalOlapScan[t2]
+
+-- !limit_distinct --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+--------------PhysicalLimit[LOCAL]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+--------------PhysicalDistribute
+----------------PhysicalProject
+------------------PhysicalOlapScan[t2]
+
+-- !limit_offset_agg --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------PhysicalOlapScan[t1]
+
+-- !limit_set_operation --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalUnion
+----------------PhysicalDistribute
+------------------PhysicalLimit[LOCAL]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[t1]
+----------------PhysicalDistribute
+------------------PhysicalLimit[LOCAL]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[t2]
+
+-- !limit_offset_set_operation --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalIntersect
+----------PhysicalDistribute
+------------PhysicalProject
+--------------PhysicalOlapScan[t1]
+----------PhysicalDistribute
+------------PhysicalProject
+--------------PhysicalOlapScan[t2]
+
+-- !limit_window --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalLimit[LOCAL]
+------PhysicalWindow
+--------PhysicalQuickSort[MERGE_SORT]
+----------PhysicalDistribute
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalPartitionTopN
+----------------PhysicalOlapScan[t1]
+
+-- !limit_offset_window --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalLimit[LOCAL]
+------PhysicalWindow
+--------PhysicalQuickSort[MERGE_SORT]
+----------PhysicalDistribute
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalPartitionTopN
+----------------PhysicalOlapScan[t1]
+
+-- !limit_filter --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------filter((t1.id = 1))
+------------PhysicalOlapScan[t1]
+
+-- !limit_offset_filter --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------filter((t1.id = 1))
+------------PhysicalOlapScan[t1]
+
+-- !limit_project_filter --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------filter((t1.id > 100))
+------------PhysicalOlapScan[t1]
+
+-- !limit_join_filter --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=() build RFs:RF0 id->[id]
+------------PhysicalProject
+--------------filter((t1.id > 100))
+----------------PhysicalOlapScan[t1] apply RFs: RF0
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------filter((t2.id > 100))
+------------------PhysicalOlapScan[t2]
+
+-- !limit_subquery --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !limit_subquery_filter --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------filter((t1.id > 100))
+------------PhysicalOlapScan[t1]
+
+-- !limit_subquery_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !limit_subquery_window --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalLimit[LOCAL]
+------PhysicalWindow
+--------PhysicalQuickSort[MERGE_SORT]
+----------PhysicalDistribute
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalPartitionTopN
+----------------PhysicalOlapScan[t1]
+
+-- !limit_nested_subquery --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !limit_union_filter --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalUnion
+----------------PhysicalDistribute
+------------------PhysicalLimit[LOCAL]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------filter((t1.id > 100))
+--------------------------PhysicalOlapScan[t1]
+----------------PhysicalDistribute
+------------------PhysicalLimit[LOCAL]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------filter((t2.id > 100))
+--------------------------PhysicalOlapScan[t2]
+
+-- !limit_union_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalUnion
+----------------PhysicalLimit[LOCAL]
+------------------hashAgg[GLOBAL]
+--------------------PhysicalDistribute
+----------------------hashAgg[LOCAL]
+------------------------PhysicalProject
+--------------------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id =
t2.id)) otherCondition=()
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[t1]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[t2]
+----------------PhysicalDistribute
+------------------PhysicalLimit[LOCAL]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t3.id =
t4.id)) otherCondition=()
+--------------------------PhysicalLimit[LOCAL]
+----------------------------hashAgg[LOCAL]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[t3]
+--------------------------PhysicalDistribute
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[t4]
+
+-- !limit_union_window --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------hashAgg[GLOBAL]
+----------hashAgg[LOCAL]
+------------PhysicalUnion
+--------------PhysicalLimit[LOCAL]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute
+--------------------hashAgg[LOCAL]
+----------------------PhysicalWindow
+------------------------PhysicalQuickSort[MERGE_SORT]
+--------------------------PhysicalDistribute
+----------------------------PhysicalQuickSort[LOCAL_SORT]
+------------------------------PhysicalOlapScan[t1]
+--------------PhysicalLimit[LOCAL]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute
+--------------------hashAgg[LOCAL]
+----------------------PhysicalWindow
+------------------------PhysicalQuickSort[MERGE_SORT]
+--------------------------PhysicalDistribute
+----------------------------PhysicalQuickSort[LOCAL_SORT]
+------------------------------PhysicalOlapScan[t2]
+
+-- !limit_subquery_join_filter --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=() build RFs:RF0 id->[id]
+------------PhysicalProject
+--------------filter((t1.id > 100))
+----------------PhysicalOlapScan[t1] apply RFs: RF0
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------filter((t2.id > 100))
+------------------PhysicalOlapScan[t2]
+
+-- !limit_subquery_join_window --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalWindow
+----------PhysicalQuickSort[LOCAL_SORT]
+------------PhysicalPartitionTopN
+--------------PhysicalProject
+----------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------------PhysicalOlapScan[t1]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+
+-- !limit_subquery_union_filter --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalUnion
+----------------PhysicalDistribute
+------------------PhysicalLimit[LOCAL]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------filter((t1.id > 100))
+--------------------------PhysicalOlapScan[t1]
+----------------PhysicalDistribute
+------------------PhysicalLimit[LOCAL]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------filter((t2.id > 100))
+--------------------------PhysicalOlapScan[t2]
+
+-- !limit_subquery_union_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalUnion
+----------------PhysicalDistribute
+------------------PhysicalLimit[LOCAL]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[t1]
+--------------------------PhysicalDistribute
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[t2]
+----------------PhysicalDistribute
+------------------PhysicalLimit[LOCAL]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN] hashCondition=((t3.id = t4.id))
otherCondition=() build RFs:RF1 id->[id]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[t3] apply RFs: RF1
+--------------------------PhysicalDistribute
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[t4]
+
+-- !limit_subquery_union_window --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalWindow
+----------PhysicalPartitionTopN
+------------PhysicalDistribute
+--------------PhysicalPartitionTopN
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute
+--------------------hashAgg[LOCAL]
+----------------------PhysicalUnion
+------------------------PhysicalDistribute
+--------------------------PhysicalOlapScan[t1]
+------------------------PhysicalDistribute
+--------------------------PhysicalOlapScan[t2]
+
+-- !limit_correlated_subquery --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashJoin[LEFT_SEMI_JOIN] hashCondition=((t2.id = t1.id))
otherCondition=()
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+--------PhysicalDistribute
+----------PhysicalProject
+------------PhysicalOlapScan[t2]
+
+-- !limit_correlated_subquery_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashJoin[LEFT_SEMI_JOIN] hashCondition=((t3.id = t1.id))
otherCondition=()
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+--------PhysicalDistribute
+----------PhysicalProject
+------------PhysicalOlapScan[t3]
+
+-- !limit_correlated_subquery_window --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalWindow
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[LEFT_SEMI_JOIN] hashCondition=((t2.id = t1.id))
otherCondition=()
+------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !limit_cte_query --
+PhysicalCteAnchor ( cteId=CTEId#0 )
+--PhysicalCteProducer ( cteId=CTEId#0 )
+----PhysicalProject
+------filter((t1.id < 10))
+--------PhysicalOlapScan[t1]
+--PhysicalResultSink
+----PhysicalLimit[GLOBAL]
+------PhysicalDistribute
+--------PhysicalLimit[LOCAL]
+----------PhysicalDistribute
+------------PhysicalCteConsumer ( cteId=CTEId#0 )
+
+-- !limit_cte_query_join --
+PhysicalCteAnchor ( cteId=CTEId#0 )
+--PhysicalCteProducer ( cteId=CTEId#0 )
+----PhysicalProject
+------filter((t1.id < 10))
+--------PhysicalOlapScan[t1]
+--PhysicalCteAnchor ( cteId=CTEId#1 )
+----PhysicalCteProducer ( cteId=CTEId#1 )
+------PhysicalProject
+--------filter((t2.id < 10))
+----------PhysicalOlapScan[t2]
+----PhysicalResultSink
+------PhysicalLimit[GLOBAL]
+--------PhysicalDistribute
+----------PhysicalLimit[LOCAL]
+------------PhysicalProject
+--------------hashJoin[FULL_OUTER_JOIN] hashCondition=((cte1.id = cte2.id))
otherCondition=()
+----------------PhysicalDistribute
+------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+----------------PhysicalDistribute
+------------------PhysicalCteConsumer ( cteId=CTEId#1 )
+
+-- !limit_cte_query_window --
+PhysicalCteAnchor ( cteId=CTEId#0 )
+--PhysicalCteProducer ( cteId=CTEId#0 )
+----PhysicalProject
+------filter((t1.id < 10))
+--------PhysicalOlapScan[t1]
+--PhysicalResultSink
+----PhysicalLimit[GLOBAL]
+------PhysicalLimit[LOCAL]
+--------PhysicalWindow
+----------PhysicalQuickSort[MERGE_SORT]
+------------PhysicalDistribute
+--------------PhysicalQuickSort[LOCAL_SORT]
+----------------PhysicalPartitionTopN
+------------------PhysicalDistribute
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+
+-- !limit_project_filter --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------filter((t1.id > 100))
+------------PhysicalOlapScan[t1]
+
+-- !limit_join_filter --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=() build RFs:RF0 id->[id]
+------------PhysicalProject
+--------------filter((t1.id > 100))
+----------------PhysicalOlapScan[t1] apply RFs: RF0
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------filter((t2.id > 100))
+------------------PhysicalOlapScan[t2]
+
+-- !limit_subquery --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !limit_subquery_filter --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------filter((t1.id > 100))
+------------PhysicalOlapScan[t1]
+
+-- !limit_subquery_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !limit_subquery_window --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalLimit[LOCAL]
+------PhysicalProject
+--------PhysicalWindow
+----------PhysicalQuickSort[MERGE_SORT]
+------------PhysicalDistribute
+--------------PhysicalQuickSort[LOCAL_SORT]
+----------------PhysicalPartitionTopN
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+
+-- !limit_nested_subquery --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !limit_subquery_order_by --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !limit_subquery_order_by_offset --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !limit_subquery_distinct --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------PhysicalOlapScan[t1]
+
+-- !limit_cross_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------NestedLoopJoin[CROSS_JOIN]
+------------PhysicalLimit[LOCAL]
+--------------PhysicalProject
+----------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalLimit[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t2]
+
+-- !limit_multiple_left_outer_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t3.id))
otherCondition=()
+------------PhysicalLimit[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------------PhysicalLimit[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t3]
+
+-- !limit_multiple_right_outer_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t3.id))
otherCondition=() build RFs:RF1 id->[id]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1] apply RFs: RF1
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalLimit[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t3]
+
+-- !limit_multiple_full_outer_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t3.id))
otherCondition=()
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t3]
+
+-- !limit_multiple_cross_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------NestedLoopJoin[CROSS_JOIN]
+------------PhysicalLimit[LOCAL]
+--------------PhysicalProject
+----------------NestedLoopJoin[CROSS_JOIN]
+------------------PhysicalLimit[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+------------------PhysicalDistribute
+--------------------PhysicalLimit[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalLimit[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t3]
+
+-- !limit_left_outer_join_right_outer_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t3.id))
otherCondition=() build RFs:RF0 id->[id]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1] apply RFs: RF0
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalLimit[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t3]
+
+-- !limit_left_outer_join_full_outer_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t3.id))
otherCondition=()
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t3]
+
+-- !limit_left_outer_join_cross_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------NestedLoopJoin[CROSS_JOIN]
+------------PhysicalLimit[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------------PhysicalLimit[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalLimit[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t3]
+
+-- !limit_right_outer_join_full_outer_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t3.id))
otherCondition=()
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t3]
+
+-- !limit_right_outer_join_cross_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------NestedLoopJoin[CROSS_JOIN]
+------------PhysicalLimit[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=() build RFs:RF0 id->[id]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1] apply RFs: RF0
+------------------PhysicalDistribute
+--------------------PhysicalLimit[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalLimit[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t3]
+
+-- !limit_full_outer_join_cross_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------NestedLoopJoin[CROSS_JOIN]
+------------PhysicalLimit[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalLimit[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t3]
+
+-- !limit_left_outer_join_right_outer_join_full_outer_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t4.id))
otherCondition=()
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t3.id))
otherCondition=()
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id =
t2.id)) otherCondition=()
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[t1]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[t2]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t3]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t4]
+
+-- !limit_left_outer_join_right_outer_join_cross_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------NestedLoopJoin[CROSS_JOIN]
+------------PhysicalLimit[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t3.id))
otherCondition=() build RFs:RF0 id->[id]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id =
t2.id)) otherCondition=()
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[t1] apply RFs: RF0
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[t2]
+------------------PhysicalDistribute
+--------------------PhysicalLimit[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[t3]
+------------PhysicalDistribute
+--------------PhysicalLimit[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t4]
+
+-- !limit_left_outer_join_full_outer_join_cross_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------NestedLoopJoin[CROSS_JOIN]
+------------PhysicalLimit[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t3.id))
otherCondition=()
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id =
t2.id)) otherCondition=()
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[t1]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[t2]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t3]
+------------PhysicalDistribute
+--------------PhysicalLimit[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t4]
+
+-- !limit_right_outer_join_full_outer_join_cross_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------NestedLoopJoin[CROSS_JOIN]
+------------PhysicalLimit[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t3.id))
otherCondition=()
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id =
t2.id)) otherCondition=()
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[t1]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[t2]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t3]
+------------PhysicalDistribute
+--------------PhysicalLimit[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t4]
+
+-- !limit_left_outer_join_right_outer_join_full_outer_join_cross_join --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------NestedLoopJoin[CROSS_JOIN]
+------------PhysicalLimit[LOCAL]
+--------------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t4.id))
otherCondition=()
+----------------PhysicalDistribute
+------------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t3.id))
otherCondition=()
+--------------------PhysicalDistribute
+----------------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id =
t2.id)) otherCondition=()
+------------------------PhysicalOlapScan[t1]
+------------------------PhysicalDistribute
+--------------------------PhysicalOlapScan[t2]
+--------------------PhysicalDistribute
+----------------------PhysicalOlapScan[t3]
+----------------PhysicalDistribute
+------------------PhysicalOlapScan[t4]
+------------PhysicalDistribute
+--------------PhysicalLimit[LOCAL]
+----------------PhysicalOlapScan[t4]
+
diff --git
a/regression-test/data/nereids_rules_p0/limit_push_down/order_push_down.out
b/regression-test/data/nereids_rules_p0/limit_push_down/order_push_down.out
new file mode 100644
index 00000000000..d8820f7568c
--- /dev/null
+++ b/regression-test/data/nereids_rules_p0/limit_push_down/order_push_down.out
@@ -0,0 +1,993 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !limit_offset_sort_project --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !limit_sort_join --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1] apply RFs: RF0
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !limit_sort_semi_join --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[LEFT_SEMI_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1] apply RFs: RF0
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !right_semi_join_order --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[RIGHT_SEMI_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !left_anti_join_order --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[LEFT_ANTI_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !right_anti_join_order --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[RIGHT_ANTI_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !full_outer_join_order --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !left_outer_join_order --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalTopN[LOCAL_SORT]
+------PhysicalProject
+--------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+----------PhysicalTopN[MERGE_SORT]
+------------PhysicalDistribute
+--------------PhysicalTopN[LOCAL_SORT]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t1]
+----------PhysicalDistribute
+------------PhysicalProject
+--------------PhysicalOlapScan[t2]
+
+-- !right_outer_join_order --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=() build RFs:RF0 id->[id]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t1] apply RFs: RF0
+------------PhysicalDistribute
+--------------PhysicalTopN[MERGE_SORT]
+----------------PhysicalDistribute
+------------------PhysicalTopN[LOCAL_SORT]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+
+-- !cross_join_order --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalTopN[LOCAL_SORT]
+------PhysicalProject
+--------NestedLoopJoin[CROSS_JOIN]
+----------PhysicalTopN[MERGE_SORT]
+------------PhysicalDistribute
+--------------PhysicalTopN[LOCAL_SORT]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t1]
+----------PhysicalDistribute
+------------PhysicalProject
+--------------PhysicalOlapScan[t2]
+
+-- !limit_offset_sort_join --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1] apply RFs: RF0
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !limit_sort_agg_having --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------PhysicalOlapScan[t1]
+
+-- !limit_offset_agg_having --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------PhysicalOlapScan[t1]
+
+-- !limit_offset_sort_agg_having --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------PhysicalOlapScan[t1]
+
+-- !limit_distinct --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------NestedLoopJoin[CROSS_JOIN]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+
+-- !limit_distinct --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------NestedLoopJoin[CROSS_JOIN]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+
+-- !limit_distinct --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+
+-- !limit_distinct --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+
+-- !limit_window --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalLimit[LOCAL]
+------PhysicalWindow
+--------PhysicalQuickSort[MERGE_SORT]
+----------PhysicalDistribute
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalPartitionTopN
+----------------PhysicalOlapScan[t1]
+
+-- !limit_sort_window --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalTopN[LOCAL_SORT]
+------PhysicalWindow
+--------PhysicalQuickSort[MERGE_SORT]
+----------PhysicalDistribute
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalOlapScan[t1]
+
+-- !limit_offset_window --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalLimit[LOCAL]
+------PhysicalWindow
+--------PhysicalQuickSort[MERGE_SORT]
+----------PhysicalDistribute
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalPartitionTopN
+----------------PhysicalOlapScan[t1]
+
+-- !limit_offset_sort_window --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalTopN[LOCAL_SORT]
+------PhysicalWindow
+--------PhysicalQuickSort[MERGE_SORT]
+----------PhysicalDistribute
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalOlapScan[t1]
+
+-- !limit_sort_filter --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------filter((t1.id = 1))
+------------PhysicalOlapScan[t1]
+
+-- !limit_offset_sort_filter --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------filter((t1.id = 1))
+------------PhysicalOlapScan[t1]
+
+-- !limit_subquery_order_by_inside_limit_outside --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !limit_subquery_all_inside --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !limit_set_operation --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalUnion
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t2]
+
+-- !limit_outside_order_inside_set_operation --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalUnion
+----------------PhysicalDistribute
+------------------PhysicalLimit[LOCAL]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[t1]
+----------------PhysicalDistribute
+------------------PhysicalLimit[LOCAL]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[t2]
+
+-- !limit_inside_set_operation --
+PhysicalResultSink
+--PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalUnion
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalTopN[MERGE_SORT]
+----------------PhysicalDistribute
+------------------PhysicalTopN[LOCAL_SORT]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+
+-- !limit_offset_set_operation --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalIntersect
+----------PhysicalDistribute
+------------PhysicalProject
+--------------PhysicalOlapScan[t1]
+----------PhysicalDistribute
+------------PhysicalProject
+--------------PhysicalOlapScan[t2]
+
+-- !limit_window --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalTopN[LOCAL_SORT]
+------PhysicalWindow
+--------PhysicalQuickSort[MERGE_SORT]
+----------PhysicalDistribute
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalOlapScan[t1]
+
+-- !limit_offset_window --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalTopN[LOCAL_SORT]
+------PhysicalWindow
+--------PhysicalQuickSort[MERGE_SORT]
+----------PhysicalDistribute
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalOlapScan[t1]
+
+-- !limit_filter --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------filter((t1.id = 1))
+------------PhysicalOlapScan[t1]
+
+-- !limit_offset_filter --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------filter((t1.id = 1))
+------------PhysicalOlapScan[t1]
+
+-- !limit_project_filter --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------filter((t1.id > 100))
+------------PhysicalOlapScan[t1]
+
+-- !limit_join_filter --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=() build RFs:RF0 id->[id]
+------------PhysicalProject
+--------------filter((t1.id > 100))
+----------------PhysicalOlapScan[t1] apply RFs: RF0
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------filter((t2.id > 100))
+------------------PhysicalOlapScan[t2]
+
+-- !limit_subquery --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !limit_subquery_filter --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------filter((t1.id > 100))
+------------PhysicalOlapScan[t1]
+
+-- !limit_subquery_join --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1] apply RFs: RF0
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !limit_subquery_window --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalTopN[LOCAL_SORT]
+------PhysicalWindow
+--------PhysicalQuickSort[MERGE_SORT]
+----------PhysicalDistribute
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalOlapScan[t1]
+
+-- !limit_nested_subquery --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !limit_union_filter --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalUnion
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------filter((t1.id > 100))
+----------------------PhysicalOlapScan[t1]
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------filter((t2.id > 100))
+----------------------PhysicalOlapScan[t2]
+
+-- !limit_union_join --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalUnion
+----------------PhysicalProject
+------------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[t1]
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[t2]
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t3.id = t4.id))
otherCondition=()
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[t3]
+----------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[t4]
+
+-- !limit_union_window --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalUnion
+----------------PhysicalDistribute
+------------------PhysicalWindow
+--------------------PhysicalQuickSort[MERGE_SORT]
+----------------------PhysicalDistribute
+------------------------PhysicalQuickSort[LOCAL_SORT]
+--------------------------PhysicalOlapScan[t1]
+----------------PhysicalDistribute
+------------------PhysicalWindow
+--------------------PhysicalQuickSort[MERGE_SORT]
+----------------------PhysicalDistribute
+------------------------PhysicalQuickSort[LOCAL_SORT]
+--------------------------PhysicalOlapScan[t2]
+
+-- !limit_subquery_join_filter --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=() build RFs:RF0 id->[id]
+------------PhysicalProject
+--------------filter((t1.id > 100))
+----------------PhysicalOlapScan[t1] apply RFs: RF0
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------filter((t2.id > 100))
+------------------PhysicalOlapScan[t2]
+
+-- !limit_subqueryjoin_window --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalWindow
+----------PhysicalQuickSort[LOCAL_SORT]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+----------------PhysicalOlapScan[t1]
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t2]
+
+-- !limit_subquery_union_filter --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalUnion
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------filter((t1.id > 100))
+----------------------PhysicalOlapScan[t1]
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------filter((t2.id > 100))
+----------------------PhysicalOlapScan[t2]
+
+-- !limit_subquery_union_join --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalUnion
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[t1]
+----------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[t2]
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN] hashCondition=((t3.id = t4.id))
otherCondition=() build RFs:RF1 id->[id]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[t3] apply RFs: RF1
+----------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[t4]
+
+-- !limit_subquery_union_window --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalWindow
+----------PhysicalQuickSort[LOCAL_SORT]
+------------PhysicalDistribute
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute
+------------------hashAgg[LOCAL]
+--------------------PhysicalUnion
+----------------------PhysicalDistribute
+------------------------PhysicalOlapScan[t1]
+----------------------PhysicalDistribute
+------------------------PhysicalOlapScan[t2]
+
+-- !limit_correlated_subquery --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashJoin[LEFT_SEMI_JOIN] hashCondition=((t2.id = t1.id))
otherCondition=()
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+--------PhysicalDistribute
+----------PhysicalProject
+------------PhysicalOlapScan[t2]
+
+-- !limit_correlated_subquery_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashJoin[LEFT_SEMI_JOIN] hashCondition=((t3.id = t1.id))
otherCondition=()
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+--------PhysicalDistribute
+----------PhysicalProject
+------------PhysicalOlapScan[t3]
+
+-- !limit_correlated_subquery_window --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalWindow
+----------PhysicalQuickSort[LOCAL_SORT]
+------------PhysicalProject
+--------------hashJoin[LEFT_SEMI_JOIN] hashCondition=((t2.id = t1.id))
otherCondition=()
+----------------PhysicalOlapScan[t1]
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t2]
+
+-- !limit_cte_query --
+PhysicalCteAnchor ( cteId=CTEId#0 )
+--PhysicalCteProducer ( cteId=CTEId#0 )
+----PhysicalProject
+------filter((t1.id < 10))
+--------PhysicalOlapScan[t1]
+--PhysicalResultSink
+----PhysicalTopN[MERGE_SORT]
+------PhysicalDistribute
+--------PhysicalTopN[LOCAL_SORT]
+----------PhysicalDistribute
+------------PhysicalCteConsumer ( cteId=CTEId#0 )
+
+-- !limit_cte_outside_query --
+PhysicalCteAnchor ( cteId=CTEId#0 )
+--PhysicalCteProducer ( cteId=CTEId#0 )
+----PhysicalProject
+------filter((t1.id < 10))
+--------PhysicalOlapScan[t1]
+--PhysicalResultSink
+----PhysicalLimit[GLOBAL]
+------PhysicalDistribute
+--------PhysicalLimit[LOCAL]
+----------PhysicalDistribute
+------------PhysicalCteConsumer ( cteId=CTEId#0 )
+
+-- !limit_cte_outside_query --
+PhysicalCteAnchor ( cteId=CTEId#0 )
+--PhysicalCteProducer ( cteId=CTEId#0 )
+----PhysicalTopN[MERGE_SORT]
+------PhysicalDistribute
+--------PhysicalTopN[LOCAL_SORT]
+----------PhysicalProject
+------------filter((t1.id < 10))
+--------------PhysicalOlapScan[t1]
+--PhysicalResultSink
+----PhysicalDistribute
+------PhysicalCteConsumer ( cteId=CTEId#0 )
+
+-- !limit_cte_query_join --
+PhysicalCteAnchor ( cteId=CTEId#0 )
+--PhysicalCteProducer ( cteId=CTEId#0 )
+----PhysicalProject
+------filter((t1.id < 10))
+--------PhysicalOlapScan[t1]
+--PhysicalCteAnchor ( cteId=CTEId#1 )
+----PhysicalCteProducer ( cteId=CTEId#1 )
+------PhysicalProject
+--------filter((t2.id < 10))
+----------PhysicalOlapScan[t2]
+----PhysicalResultSink
+------PhysicalTopN[MERGE_SORT]
+--------PhysicalDistribute
+----------PhysicalTopN[LOCAL_SORT]
+------------PhysicalProject
+--------------hashJoin[FULL_OUTER_JOIN] hashCondition=((cte1.id = cte2.id))
otherCondition=()
+----------------PhysicalDistribute
+------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+----------------PhysicalDistribute
+------------------PhysicalCteConsumer ( cteId=CTEId#1 )
+
+-- !limit_cte_query_window --
+PhysicalCteAnchor ( cteId=CTEId#0 )
+--PhysicalCteProducer ( cteId=CTEId#0 )
+----PhysicalProject
+------filter((t1.id < 10))
+--------PhysicalOlapScan[t1]
+--PhysicalResultSink
+----PhysicalTopN[MERGE_SORT]
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalWindow
+----------PhysicalQuickSort[MERGE_SORT]
+------------PhysicalDistribute
+--------------PhysicalQuickSort[LOCAL_SORT]
+----------------PhysicalCteConsumer ( cteId=CTEId#0 )
+
+-- !limit_project_filter --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------filter((t1.id > 100))
+------------PhysicalOlapScan[t1]
+
+-- !limit_join_filter --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=() build RFs:RF0 id->[id]
+------------PhysicalProject
+--------------filter((t1.id > 100))
+----------------PhysicalOlapScan[t1] apply RFs: RF0
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------filter((t2.id > 100))
+------------------PhysicalOlapScan[t2]
+
+-- !limit_subquery_join --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1] apply RFs: RF0
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !limit_subquery_window --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalTopN[LOCAL_SORT]
+------PhysicalProject
+--------PhysicalWindow
+----------PhysicalQuickSort[MERGE_SORT]
+------------PhysicalDistribute
+--------------PhysicalQuickSort[LOCAL_SORT]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t1]
+
+-- !limit_nested_subquery --
+PhysicalResultSink
+--PhysicalLimit[GLOBAL]
+----PhysicalDistribute
+------PhysicalLimit[LOCAL]
+--------PhysicalProject
+----------PhysicalOlapScan[t1]
+
+-- !limit_subquery_distinct --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------PhysicalOlapScan[t1]
+
+-- !limit_cross_join --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalTopN[LOCAL_SORT]
+------PhysicalProject
+--------NestedLoopJoin[CROSS_JOIN]
+----------PhysicalTopN[MERGE_SORT]
+------------PhysicalDistribute
+--------------PhysicalTopN[LOCAL_SORT]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t1]
+----------PhysicalDistribute
+------------PhysicalProject
+--------------PhysicalOlapScan[t2]
+
+-- !limit_multiple_left_outer_join --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalTopN[LOCAL_SORT]
+------PhysicalProject
+--------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t3.id))
otherCondition=()
+----------PhysicalTopN[MERGE_SORT]
+------------PhysicalTopN[LOCAL_SORT]
+--------------PhysicalProject
+----------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------------PhysicalTopN[MERGE_SORT]
+--------------------PhysicalDistribute
+----------------------PhysicalTopN[LOCAL_SORT]
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[t1]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+----------PhysicalDistribute
+------------PhysicalProject
+--------------PhysicalOlapScan[t3]
+
+-- !limit_multiple_right_outer_join --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t3.id))
otherCondition=()
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1] apply RFs: RF0 RF1
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t3]
+
+-- !limit_multiple_full_outerjoin --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t3.id))
otherCondition=()
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t2.id))
otherCondition=()
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[t3]
+
+-- !limit_subquery_cross_join --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalTopN[LOCAL_SORT]
+------PhysicalProject
+--------NestedLoopJoin[CROSS_JOIN]
+----------PhysicalTopN[MERGE_SORT]
+------------PhysicalDistribute
+--------------PhysicalTopN[LOCAL_SORT]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t1]
+----------PhysicalDistribute
+------------PhysicalOlapScan[t2]
+
+-- !limit_subquery_multiple_join --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((subq.id = t3.id))
otherCondition=()
+------------hashJoin[INNER_JOIN] hashCondition=((subq.id = t2.id))
otherCondition=()
+--------------PhysicalProject
+----------------PhysicalOlapScan[t1] apply RFs: RF0 RF1
+--------------PhysicalDistribute
+----------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalOlapScan[t3]
+
+-- !limit_subquery_multiple_join_nested_subquery --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((subq2.id = t3.id))
otherCondition=()
+------------hashJoin[INNER_JOIN] hashCondition=((subq2.id = t2.id))
otherCondition=()
+--------------PhysicalProject
+----------------PhysicalOlapScan[t1] apply RFs: RF0 RF1
+--------------PhysicalDistribute
+----------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalOlapScan[t3]
+
+-- !limit_subquery_multiple_join_nested_subquery_distinct --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((subq2.id = t3.id))
otherCondition=()
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN] hashCondition=((subq2.id = t2.id))
otherCondition=()
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1] apply RFs: RF0 RF1
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t2]
+--------------PhysicalDistribute
+----------------PhysicalProject
+------------------PhysicalOlapScan[t3]
+
+-- !limit_subquery_multiple_join_nested_subquery_distinct_filter --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((subq2.id = t3.id))
otherCondition=() build RFs:RF1 id->[id]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN] hashCondition=((subq2.id = t2.id))
otherCondition=() build RFs:RF0 id->[id]
+------------------PhysicalProject
+--------------------filter((t1.id > 100))
+----------------------PhysicalOlapScan[t1] apply RFs: RF0 RF1
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------filter((t2.id > 100))
+------------------------PhysicalOlapScan[t2]
+--------------PhysicalDistribute
+----------------PhysicalProject
+------------------filter((t3.id > 100))
+--------------------PhysicalOlapScan[t3]
+
diff --git
a/regression-test/suites/nereids_rules_p0/limit_push_down/limit_push_down.groovy
b/regression-test/suites/nereids_rules_p0/limit_push_down/limit_push_down.groovy
new file mode 100644
index 00000000000..9f17ac849b4
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/limit_push_down/limit_push_down.groovy
@@ -0,0 +1,205 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("limit_push_down") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+ sql "use regression_test_nereids_rules_p0"
+ sql """ SET inline_cte_referenced_threshold=0 """
+ sql "set disable_join_reorder=true"
+ //`limit 1, project`:
+ qt_limit_project """ explain shape plan SELECT t1.id as c FROM t1 LIMIT 1;
"""
+ //`limit 1 offset 1, project`:
+ qt_limit_offset_project """ explain shape plan SELECT t1.id as c FROM t1
LIMIT 1 OFFSET 1; """
+ //`limit 1, join`:
+ qt_limit_join """ explain shape plan SELECT t1.id FROM t1 JOIN t2 ON t1.id
= t2.id LIMIT 1; """
+ //`limit 1, semi join`:
+ qt_limit_semi_join """ explain shape plan SELECT t1.id FROM t1 LEFT SEMI
JOIN t2 ON t1.id = t2.id LIMIT 1; """
+ // Right Semi Join
+ qt_right_semi_join """ explain shape plan SELECT t2.id FROM t1 RIGHT SEMI
JOIN t2 ON t1.id = t2.id LIMIT 1; """
+ // Left Anti Join
+ qt_left_anti_join """ explain shape plan SELECT t1.id FROM t1 LEFT ANTI
JOIN t2 ON t1.id = t2.id LIMIT 1; """
+ // Right Anti Join
+ qt_right_anti_join """ explain shape plan SELECT t2.id FROM t1 RIGHT ANTI
JOIN t2 ON t1.id = t2.id LIMIT 1; """
+ // Full Outer Join
+ qt_full_outer_join """ explain shape plan SELECT t1.id FROM t1 FULL OUTER
JOIN t2 ON t1.id = t2.id LIMIT 1; """
+ // Left Outer Join
+ qt_left_outer_join """ explain shape plan SELECT t1.id FROM t1 LEFT OUTER
JOIN t2 ON t1.id = t2.id LIMIT 1; """
+ // Right Outer Join
+ qt_right_outer_join """ explain shape plan SELECT t1.id FROM t1 RIGHT
OUTER JOIN t2 ON t1.id = t2.id LIMIT 1; """
+ // Cross Join
+ qt_cross_join """ explain shape plan SELECT t1.id FROM t1 CROSS JOIN t2
LIMIT 1; """
+ //`limit 1 offset 1, join`:
+ qt_limit_offset_join """ explain shape plan SELECT t1.id FROM t1 JOIN t2
ON t1.id = t2.id LIMIT 1 OFFSET 1; """
+ //`limit 1, agg & scalar agg:
+ qt_limit_distinct """ explain shape plan SELECT distinct t1.id FROM t1
LIMIT 1; """
+ //`limit 1 offset 1, agg & scalar agg`:
+ qt_limit_offset_agg """ explain shape plan SELECT distinct t1.id c FROM t1
ORDER BY c LIMIT 1 OFFSET 1; """
+ //`limit 1, agg & scalar agg join:
+ qt_limit_distinct """ explain shape plan SELECT distinct t1.id FROM t1
inner join t2 on true LIMIT 1; """
+ //`limit 1, agg & scalar agg join:
+ qt_limit_distinct """ explain shape plan SELECT distinct t1.id FROM t1
cross join t2 LIMIT 1; """
+ //`limit 1, agg & scalar agg left outer join:
+ qt_limit_distinct """ explain shape plan SELECT distinct t1.id FROM t1
left outer join t2 on t1.id = t2.id LIMIT 1; """
+ //`limit 1, agg & scalar agg right outer join:
+ qt_limit_distinct """ explain shape plan SELECT distinct t1.id FROM t1
left outer join t2 on t1.id = t2.id LIMIT 1; """
+
+ //`limit 1 offset 1, agg & scalar agg`:
+ qt_limit_offset_agg """ explain shape plan SELECT distinct t1.id c FROM t1
ORDER BY c LIMIT 1 OFFSET 1; """
+ //`limit 1, Set Operation`:
+ qt_limit_set_operation """ explain shape plan SELECT * FROM (SELECT t1.id
FROM t1 UNION SELECT t2.id FROM t2) u LIMIT 1; """
+ //`limit 1 offset 1, Set Operation`:
+ qt_limit_offset_set_operation """ explain shape plan SELECT * FROM (SELECT
t1.id FROM t1 INTERSECT SELECT t2.id FROM t2) u LIMIT 1 OFFSET 1; """
+ //`limit 1, window`:
+ qt_limit_window """ explain shape plan SELECT id, msg, ROW_NUMBER() OVER
(ORDER BY id) AS row_num FROM t1 LIMIT 1; """
+ //`limit 1 offset 1, window`:
+ qt_limit_offset_window """ explain shape plan SELECT id, msg, ROW_NUMBER()
OVER (ORDER BY id) AS row_num FROM t1 LIMIT 1 OFFSET 1; """
+ //`limit 1, filter`:
+ qt_limit_filter """ explain shape plan SELECT t1.id FROM t1 WHERE id = 1
LIMIT 1; """
+ //`limit 1 offset 1, filter`:
+ qt_limit_offset_filter """ explain shape plan SELECT t1.id FROM t1 WHERE
id = 1 LIMIT 1 OFFSET 1; """
+ // `limit 1, project, filter`:
+ qt_limit_project_filter """explain shape plan SELECT t1.id AS c FROM t1
WHERE t1.id > 100 LIMIT 1;"""
+ // `limit 1, join, filter`:
+ qt_limit_join_filter """explain shape plan SELECT t1.id FROM t1 JOIN t2 ON
t1.id = t2.id WHERE t1.id > 100 LIMIT 1;"""
+ // `limit 1, subquery`:
+ qt_limit_subquery """explain shape plan SELECT * FROM (SELECT t1.id FROM
t1) AS subq LIMIT 1;"""
+ // `limit 1, subquery, filter`:
+ qt_limit_subquery_filter """explain shape plan SELECT * FROM (SELECT t1.id
FROM t1 WHERE t1.id > 100) AS subq LIMIT 1;"""
+ // `limit 1, subquery, join`:
+ qt_limit_subquery_join """explain shape plan SELECT * FROM (SELECT t1.id
FROM t1 JOIN t2 ON t1.id = t2.id) AS subq LIMIT 1;"""
+ // `limit 1, subquery, window`:
+ qt_limit_subquery_window """explain shape plan SELECT id, msg,
ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM (SELECT t1.id, t1.msg FROM t1)
AS subq LIMIT 1;"""
+ // `limit 1, nested subquery`:
+ qt_limit_nested_subquery """explain shape plan SELECT * FROM (SELECT *
FROM (SELECT t1.id FROM t1) AS subq1) AS subq2 LIMIT 1;"""
+ // `limit 1, union, filter`:
+ qt_limit_union_filter """explain shape plan SELECT * FROM (SELECT t1.id
FROM t1 WHERE t1.id > 100 UNION SELECT t2.id FROM t2 WHERE t2.id > 100) u LIMIT
1;"""
+ // `limit 1, union, join`:
+ qt_limit_union_join """explain shape plan SELECT * FROM (SELECT t1.id FROM
t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id UNION SELECT t3.id FROM t3 LEFT OUTER
JOIN t4 ON t3.id = t4.id) u LIMIT 1;"""
+ // `limit 1, union, window`:
+ qt_limit_union_window """explain shape plan SELECT * FROM (SELECT id, msg,
ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM t1 UNION SELECT id, msg,
ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM t2) u LIMIT 1;"""
+ // `limit 1, subquery, join, filter`:
+ qt_limit_subquery_join_filter """explain shape plan SELECT * FROM (SELECT
t1.id FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.id > 100) AS subq LIMIT 1;"""
+
+ // `limit 1, subquery, join, window`:
+ qt_limit_subquery_join_window """explain shape plan SELECT id, msg,
ROW_NUMBER() OVER (PARTITION BY subq.id ORDER BY subq.id) AS row_num FROM
(SELECT t1.id, t1.msg FROM t1 left outer JOIN t2 ON t1.id = t2.id) AS subq
LIMIT 1;"""
+
+ // `limit 1, subquery, union, filter`:
+ qt_limit_subquery_union_filter """explain shape plan SELECT * FROM (SELECT
t1.id FROM t1 WHERE t1.id > 100 UNION SELECT t2.id FROM t2 WHERE t2.id > 100)
AS subq LIMIT 1;"""
+
+ // `limit 1, subquery, union, join`:
+ qt_limit_subquery_union_join """explain shape plan SELECT * FROM (SELECT
t1.id FROM t1 JOIN t2 ON t1.id = t2.id UNION SELECT t3.id FROM t3 JOIN t4 ON
t3.id = t4.id) AS subq LIMIT 1;"""
+
+ // `limit 1, subquery, union, window`:
+ qt_limit_subquery_union_window """explain shape plan SELECT id, msg,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS row_num FROM (SELECT id, msg
FROM t1 UNION SELECT id, msg FROM t2) AS subq LIMIT 1;"""
+
+ // `limit 1, correlated subquery`:
+ qt_limit_correlated_subquery """explain shape plan SELECT t1.id FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id LIMIT 1);"""
+
+ // `limit 1, correlated subquery, join`:
+ qt_limit_correlated_subquery_join """explain shape plan SELECT t1.id FROM
t1 JOIN t2 ON t1.id = t2.id WHERE EXISTS (SELECT 1 FROM t3 WHERE t3.id = t1.id
LIMIT 1);"""
+
+ // `limit 1, correlated subquery, window`:
+ qt_limit_correlated_subquery_window """explain shape plan SELECT id, msg,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS row_num FROM t1 WHERE EXISTS
(SELECT 1 FROM t2 WHERE t2.id = t1.id LIMIT 1);"""
+
+ // `limit 1, cte query`:
+ qt_limit_cte_query """explain shape plan WITH cte AS (SELECT id FROM t1
WHERE id < 10) SELECT id FROM cte LIMIT 1;"""
+
+ // `limit 1, cte query, join`:
+ qt_limit_cte_query_join """explain shape plan WITH cte1 AS (SELECT id FROM
t1 WHERE id < 10), cte2 AS (SELECT id FROM t2 WHERE id < 10) SELECT cte1.id
FROM cte1 full outer JOIN cte2 ON cte1.id = cte2.id LIMIT 1;"""
+
+ // `limit 1, cte query, window`:
+ qt_limit_cte_query_window """explain shape plan WITH cte AS (SELECT id
FROM t1 WHERE id < 10) SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS row_num
FROM cte LIMIT 1;"""
+
+ // `limit 1, project, filter`:
+ qt_limit_project_filter """explain shape plan SELECT t1.id AS c FROM t1
WHERE t1.id > 100 LIMIT 1;"""
+
+ // `limit 1, join, filter`:
+ qt_limit_join_filter """explain shape plan SELECT t1.id FROM t1 JOIN t2 ON
t1.id = t2.id WHERE t1.id > 100 LIMIT 1;"""
+
+ // `limit 1, subquery`:
+ qt_limit_subquery """explain shape plan SELECT * FROM (SELECT t1.id FROM
t1) AS subq LIMIT 1;"""
+
+ // `limit 1, subquery, filter`:
+ qt_limit_subquery_filter """explain shape plan SELECT * FROM (SELECT t1.id
FROM t1 WHERE t1.id > 100) AS subq LIMIT 1;"""
+
+ // `limit 1, subquery, join`:
+ qt_limit_subquery_join """explain shape plan SELECT * FROM (SELECT t1.id
FROM t1 JOIN t2 ON t1.id = t2.id) AS subq LIMIT 1;"""
+
+ // `limit 1, subquery, window`:
+ qt_limit_subquery_window """explain shape plan SELECT id, msg,
ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM (SELECT t1.id as id, t1.msg
FROM t1) AS subq LIMIT 1;"""
+
+ // `limit 1, nested subquery`:
+ qt_limit_nested_subquery """explain shape plan SELECT * FROM (SELECT *
FROM (SELECT t1.id FROM t1) AS subq1) AS subq2 LIMIT 1;"""
+
+ // `limit 1, subquery, order by`:
+ qt_limit_subquery_order_by """explain shape plan SELECT * FROM (SELECT
t1.id FROM t1 ORDER BY t1.id DESC) AS subq LIMIT 1;"""
+
+ // `limit 1, subquery, order by, offset`:
+ qt_limit_subquery_order_by_offset """explain shape plan SELECT * FROM
(SELECT t1.id FROM t1 ORDER BY t1.id DESC) AS subq LIMIT 1 OFFSET 1;"""
+
+ // `limit 1, subquery, distinct`:
+ qt_limit_subquery_distinct """explain shape plan SELECT DISTINCT subq.id
FROM (SELECT id FROM t1) AS subq LIMIT 1;"""
+
+ // `limit 1, cross join`:
+ qt_limit_cross_join """explain shape plan SELECT t1.id FROM t1 INNER JOIN
t2 on true LIMIT 1;"""
+
+ // `limit 1, multiple left outer join`:
+ qt_limit_multiple_left_outer_join """explain shape plan SELECT t1.id FROM
t1 LEFT OUTER JOIN t2 ON t1.id = t2.id LEFT OUTER JOIN t3 ON t1.id = t3.id
LIMIT 1;"""
+
+ // `limit 1, multiple right outer join`:
+ qt_limit_multiple_right_outer_join """explain shape plan SELECT t1.id FROM
t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id RIGHT OUTER JOIN t3 ON t1.id = t3.id
LIMIT 1;"""
+
+ // `limit 1, multiple full outer join`:
+ qt_limit_multiple_full_outer_join """explain shape plan SELECT t1.id FROM
t1 FULL OUTER JOIN t2 ON t1.id = t2.id FULL OUTER JOIN t3 ON t1.id = t3.id
LIMIT 1;"""
+
+ // `limit 1, multiple cross join`:
+ qt_limit_multiple_cross_join """explain shape plan SELECT t1.id FROM t1
CROSS JOIN t2 CROSS JOIN t3 LIMIT 1;"""
+
+ // `limit 1, left outer join, right outer join`:
+ qt_limit_left_outer_join_right_outer_join """explain shape plan SELECT
t1.id FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id RIGHT OUTER JOIN t3 ON t1.id
= t3.id LIMIT 1;"""
+
+ // `limit 1, left outer join, full outer join`:
+ qt_limit_left_outer_join_full_outer_join """explain shape plan SELECT
t1.id FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id FULL OUTER JOIN t3 ON t1.id =
t3.id LIMIT 1;"""
+
+ // `limit 1, left outer join, cross join`:
+ qt_limit_left_outer_join_cross_join """explain shape plan SELECT t1.id
FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id CROSS JOIN t3 LIMIT 1;"""
+
+ // `limit 1, right outer join, full outer join`:
+ qt_limit_right_outer_join_full_outer_join """explain shape plan SELECT
t1.id FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id FULL OUTER JOIN t3 ON t1.id
= t3.id LIMIT 1;"""
+
+ // `limit 1, right outer join, cross join`:
+ qt_limit_right_outer_join_cross_join """explain shape plan SELECT t1.id
FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id CROSS JOIN t3 LIMIT 1;"""
+
+ // `limit 1, full outer join, cross join`:
+ qt_limit_full_outer_join_cross_join """explain shape plan SELECT t1.id
FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id CROSS JOIN t3 LIMIT 1;"""
+
+ // `limit 1, left outer join, right outer join, full outer join`:
+ qt_limit_left_outer_join_right_outer_join_full_outer_join """explain shape
plan SELECT t1.id FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id RIGHT OUTER JOIN
t3 ON t1.id = t3.id FULL OUTER JOIN t4 ON t1.id = t4.id LIMIT 1;"""
+
+ // `limit 1, left outer join, right outer join, cross join`:
+ qt_limit_left_outer_join_right_outer_join_cross_join """explain shape plan
SELECT t1.id FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id RIGHT OUTER JOIN t3 ON
t1.id = t3.id CROSS JOIN t4 LIMIT 1;"""
+
+ // `limit 1, left outer join, full outer join, cross join`:
+ qt_limit_left_outer_join_full_outer_join_cross_join """explain shape plan
SELECT t1.id FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id FULL OUTER JOIN t3 ON
t1.id = t3.id CROSS JOIN t4 LIMIT 1;"""
+
+ // `limit 1, right outer join, full outer join, cross join`:
+ qt_limit_right_outer_join_full_outer_join_cross_join """explain shape plan
SELECT t1.id FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id FULL OUTER JOIN t3 ON
t1.id = t3.id CROSS JOIN t4 LIMIT 1;"""
+
+ // `limit 1, left outer join, right outer join, full outer join, cross
join`:
+ qt_limit_left_outer_join_right_outer_join_full_outer_join_cross_join
"""explain shape plan SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id
RIGHT OUTER JOIN t3 ON t1.id = t3.id FULL OUTER JOIN t4 ON t1.id = t4.id inner
JOIN t4 on TRUE LIMIT 1;"""
+}
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_rules_p0/limit_push_down/order_push_down.groovy
b/regression-test/suites/nereids_rules_p0/limit_push_down/order_push_down.groovy
new file mode 100644
index 00000000000..b52a382e167
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/limit_push_down/order_push_down.groovy
@@ -0,0 +1,210 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("order_push_down") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+ sql "use regression_test_nereids_rules_p0"
+ sql """ SET inline_cte_referenced_threshold=0 """
+ sql "set disable_join_reorder=true"
+ //`limit 1 offset 1 + sort, project`:
+ qt_limit_offset_sort_project """ explain shape plan SELECT t1.id FROM t1
ORDER BY id LIMIT 1 OFFSET 1; """
+ //`limit 1 + sort, join`:
+ qt_limit_sort_join """ explain shape plan SELECT t1.id FROM t1 JOIN t2 ON
t1.id = t2.id ORDER BY t1.id LIMIT 1; """
+ //`limit 1 + sort, semi join`:
+ qt_limit_sort_semi_join """ explain shape plan SELECT t1.id FROM t1 LEFT
SEMI JOIN t2 ON t1.id = t2.id ORDER BY t1.id LIMIT 1; """
+ // Right Semi Join with Order By
+ qt_right_semi_join_order """ explain shape plan SELECT t2.id FROM t1 RIGHT
SEMI JOIN t2 ON t1.id = t2.id ORDER BY t2.id LIMIT 1; """
+ // Left Anti Join with Order By
+ qt_left_anti_join_order """ explain shape plan SELECT t1.id FROM t1 LEFT
ANTI JOIN t2 ON t1.id = t2.id ORDER BY t1.id LIMIT 1; """
+ // Right Anti Join with Order By
+ qt_right_anti_join_order """ explain shape plan SELECT t2.id FROM t1 RIGHT
ANTI JOIN t2 ON t1.id = t2.id ORDER BY t2.id LIMIT 1; """
+ // Full Outer Join with Order By
+ qt_full_outer_join_order """ explain shape plan SELECT t1.id FROM t1 FULL
OUTER JOIN t2 ON t1.id = t2.id ORDER BY t1.id LIMIT 1; """
+ // Left Outer Join with Order By
+ qt_left_outer_join_order """ explain shape plan SELECT t1.id FROM t1 LEFT
OUTER JOIN t2 ON t1.id = t2.id ORDER BY t1.id LIMIT 1; """
+ // Right Outer Join with Order By
+ qt_right_outer_join_order """ explain shape plan SELECT t2.id FROM t1
RIGHT OUTER JOIN t2 ON t1.id = t2.id ORDER BY t2.id LIMIT 1; """
+ // Cross Join with Order By
+ qt_cross_join_order """ explain shape plan SELECT t1.id FROM t1 CROSS JOIN
t2 ORDER BY t1.id LIMIT 1; """
+ qt_limit_offset_sort_join """ explain shape plan SELECT t1.id FROM t1 JOIN
t2 ON t1.id = t2.id ORDER BY t1.id LIMIT 1 OFFSET 1; """
+ // `limit 1 + sort, agg & scalar agg and having`:
+ qt_limit_sort_agg_having """ explain shape plan SELECT distinct id c FROM
t1 ORDER BY c LIMIT 1; """
+ //`limit 1 offset 1, agg & scalar agg and having`:
+ qt_limit_offset_agg_having """ explain shape plan SELECT distinct id c
FROM t1 ORDER BY c LIMIT 1 OFFSET 1; """
+ //`limit 1 offset 1 + sort, agg & scalar agg and having`:
+ qt_limit_offset_sort_agg_having """ explain shape plan SELECT distinct id
c FROM t1 ORDER BY c LIMIT 1 OFFSET 1; """
+ //`limit 1, agg & scalar agg join:
+ qt_limit_distinct """ explain shape plan SELECT distinct t1.msg c FROM t1
inner join t2 on true ORDER BY c LIMIT 1; """
+ //`limit 1, agg & scalar agg join:
+ qt_limit_distinct """ explain shape plan SELECT distinct t1.msg c FROM t1
cross join t2 ORDER BY c LIMIT 1; """
+ //`limit 1, agg & scalar agg left outer join:
+ qt_limit_distinct """ explain shape plan SELECT distinct t1.msg c FROM t1
left outer join t2 on t1.id = t2.id ORDER BY c LIMIT 1; """
+ //`limit 1, agg & scalar agg right outer join:
+ qt_limit_distinct """ explain shape plan SELECT distinct t1.msg c FROM t1
left outer join t2 on t1.id = t2.id ORDER BY c LIMIT 1; """
+ //`limit 1, window`:
+ qt_limit_window """ explain shape plan SELECT id, msg, ROW_NUMBER() OVER
(ORDER BY id) AS row_num FROM t1 LIMIT 1; """
+ //`limit 1 + sort, window`:
+ qt_limit_sort_window """ explain shape plan SELECT id, msg, ROW_NUMBER()
OVER (ORDER BY id) AS row_num FROM t1 ORDER BY id LIMIT 1; """
+ //`limit 1 offset 1, window`:
+ qt_limit_offset_window """ explain shape plan SELECT id, msg, ROW_NUMBER()
OVER (ORDER BY id) AS row_num FROM t1 LIMIT 1 OFFSET 1; """
+ // `limit 1 offset 1 + sort, window`:
+ qt_limit_offset_sort_window """ explain shape plan SELECT id, msg,
ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM t1 ORDER BY id LIMIT 1 OFFSET
1; """
+ //`limit 1 + sort, filter`:
+ qt_limit_sort_filter """ explain shape plan SELECT t1.id FROM t1 WHERE id
= 1 ORDER BY id LIMIT 1; """
+ //`limit 1 offset 1 + sort, filter`:
+ qt_limit_offset_sort_filter """ explain shape plan SELECT t1.id FROM t1
WHERE id = 1 ORDER BY id LIMIT 1; """
+ // `limit 1, subquery with order by`:
+ qt_limit_subquery_order_by_inside_limit_outside """explain shape plan
SELECT * FROM (SELECT t1.id FROM t1 order by id) AS subq LIMIT 1;"""
+ // `limit 1, subquery with order by`:
+ qt_limit_subquery_all_inside """explain shape plan SELECT * FROM (SELECT
t1.id FROM t1 order by id LIMIT 1) AS subq ;"""
+ // `LIMIT` with Set Operation and `ORDER BY`:
+ qt_limit_set_operation """explain shape plan SELECT * FROM (SELECT t1.id
FROM t1 UNION SELECT t2.id FROM t2) u ORDER BY id LIMIT 1;"""
+ // `LIMIT` with Set Operation and `ORDER BY`:
+ qt_limit_outside_order_inside_set_operation """explain shape plan SELECT *
FROM (SELECT t1.id FROM t1 UNION SELECT t2.id FROM t2 ORDER BY id) u LIMIT
1;"""
+ // `LIMIT` with Set Operation and `ORDER BY`:
+ qt_limit_inside_set_operation """explain shape plan SELECT * FROM (SELECT
t1.id FROM t1 UNION SELECT t2.id FROM t2 ORDER BY id LIMIT 1) u;"""
+
+ // `LIMIT` with Set Operation and `OFFSET` with `ORDER BY`:
+ qt_limit_offset_set_operation """explain shape plan SELECT * FROM (SELECT
t1.id FROM t1 INTERSECT SELECT t2.id FROM t2) u ORDER BY id LIMIT 1 OFFSET 1;"""
+
+ // `LIMIT` with Window function and `ORDER BY`:
+ qt_limit_window """explain shape plan SELECT id, msg, ROW_NUMBER() OVER
(ORDER BY id) AS row_num FROM t1 ORDER BY id LIMIT 1;"""
+
+ // `LIMIT` with Window function and `OFFSET` with `ORDER BY`:
+ qt_limit_offset_window """explain shape plan SELECT id, msg, ROW_NUMBER()
OVER (ORDER BY id) AS row_num FROM t1 ORDER BY id LIMIT 1 OFFSET 1;"""
+
+ // `LIMIT` with Filter and `ORDER BY`:
+ qt_limit_filter """explain shape plan SELECT t1.id FROM t1 WHERE id = 1
ORDER BY id LIMIT 1;"""
+
+ // `LIMIT` with Filter and `OFFSET` with `ORDER BY`:
+ qt_limit_offset_filter """explain shape plan SELECT t1.id FROM t1 WHERE id
= 1 ORDER BY id LIMIT 1 OFFSET 1;"""
+
+ // `LIMIT` with Projection, Filter, and `ORDER BY`:
+ qt_limit_project_filter """explain shape plan SELECT t1.id AS c FROM t1
WHERE t1.id > 100 ORDER BY t1.id LIMIT 1;"""
+
+ // `LIMIT` with Join, Filter, and `ORDER BY`:
+ qt_limit_join_filter """explain shape plan SELECT t1.id FROM t1 JOIN t2 ON
t1.id = t2.id WHERE t1.id > 100 ORDER BY t1.id LIMIT 1;"""
+
+ // `LIMIT` with Subquery and `ORDER BY`:
+ qt_limit_subquery """explain shape plan SELECT * FROM (SELECT t1.id FROM
t1) AS subq ORDER BY id LIMIT 1;"""
+
+ // `LIMIT` with Subquery, Filter, and `ORDER BY`:
+ qt_limit_subquery_filter """explain shape plan SELECT * FROM (SELECT t1.id
FROM t1 WHERE t1.id > 100) AS subq ORDER BY id LIMIT 1;"""
+
+ // `LIMIT` with Subquery, Join, and `ORDER BY`:
+ qt_limit_subquery_join """explain shape plan SELECT * FROM (SELECT t1.id
FROM t1 JOIN t2 ON t1.id = t2.id) AS subq ORDER BY id LIMIT 1;"""
+
+ // `LIMIT` with Subquery, Window function, and `ORDER BY`:
+ qt_limit_subquery_window """explain shape plan SELECT id, msg,
ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM (SELECT t1.id, t1.msg FROM t1)
AS subq ORDER BY id LIMIT 1;"""
+
+ // `LIMIT` with Nested Subquery and `ORDER BY`:
+ qt_limit_nested_subquery """explain shape plan SELECT * FROM (SELECT *
FROM (SELECT t1.id FROM t1) AS subq1) AS subq2 ORDER BY id LIMIT 1;"""
+
+ // `LIMIT` with Union, Filter, and `ORDER BY`:
+ qt_limit_union_filter """explain shape plan SELECT * FROM (SELECT t1.id
FROM t1 WHERE t1.id > 100 UNION SELECT t2.id FROM t2 WHERE t2.id > 100) u ORDER
BY id LIMIT 1;"""
+
+ // `LIMIT` with Union, Join, and `ORDER BY`:
+ qt_limit_union_join """explain shape plan SELECT * FROM (SELECT t1.id FROM
t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id UNION SELECT t3.id FROM t3 LEFT OUTER
JOIN t4 ON t3.id = t4.id) u ORDER BY id LIMIT 1;"""
+
+ // `LIMIT` with Union, Window function, and `ORDER BY`:
+ qt_limit_union_window """explain shape plan SELECT * FROM (SELECT id, msg,
ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM t1 UNION SELECT id, msg,
ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM t2) u ORDER BY id LIMIT 1;"""
+
+ // `LIMIT` with Subquery, Join, Filter, and `ORDER BY`:
+ qt_limit_subquery_join_filter """explain shape plan SELECT * FROM (SELECT
t1.id FROM t1 JOIN t2 ON t1.id = t2.id WHERE t1.id > 100) AS subq ORDER BY id
LIMIT 1;"""
+
+ // `LIMIT` with Subquery, Join, Window function, and `ORDER BY`:
+ qt_limit_subqueryjoin_window """explain shape plan SELECT id, msg,
ROW_NUMBER() OVER (PARTITION BY subq.id ORDER BY subq.id) AS row_num FROM
(SELECT t1.id, t1.msg FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id) AS subq
ORDER BY id LIMIT 1;"""
+
+ // `LIMIT` with Subquery, Union, Filter, and `ORDER BY`:
+ qt_limit_subquery_union_filter """explain shape plan SELECT * FROM (SELECT
t1.id FROM t1 WHERE t1.id > 100 UNION SELECT t2.id FROM t2 WHERE t2.id > 100)
AS subq ORDER BY id LIMIT 1;"""
+
+ // `LIMIT` with Subquery, Union, Join, and `ORDER BY`:
+ qt_limit_subquery_union_join """explain shape plan SELECT * FROM (SELECT
t1.id FROM t1 JOIN t2 ON t1.id = t2.id UNION SELECT t3.id FROM t3 JOIN t4 ON
t3.id = t4.id) AS subq ORDER BY id LIMIT 1;"""
+
+ // `LIMIT` with Subquery, Union, Window function, and `ORDER BY`:
+ qt_limit_subquery_union_window """explain shape plan SELECT id, msg,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS row_num FROM (SELECT id, msg
FROM t1 UNION SELECT id, msg FROM t2) AS subq ORDER BY id LIMIT 1;"""
+
+ // `LIMIT` with Correlated Subquery and `ORDER BY`:
+ qt_limit_correlated_subquery """explain shape plan SELECT t1.id FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id ORDER BY id LIMIT 1);"""
+
+ // `LIMIT` with Correlated Subquery, Join, and `ORDER BY`:
+ qt_limit_correlated_subquery_join """explain shape plan SELECT t1.id FROM
t1 JOIN t2 ON t1.id = t2.id WHERE EXISTS (SELECT 1 FROM t3 WHERE t3.id = t1.id
ORDER BY id LIMIT 1);"""
+
+ // `limit 1, correlated subquery, window`:
+ qt_limit_correlated_subquery_window """explain shape plan SELECT id, msg,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS row_num FROM t1 WHERE EXISTS
(SELECT 1 FROM t2 WHERE t2.id = t1.id LIMIT 1) ORDER BY id;"""
+
+ // `limit 1, cte query`:
+ qt_limit_cte_query """explain shape plan WITH cte AS (SELECT id FROM t1
WHERE id < 10) SELECT id FROM cte ORDER BY id LIMIT 1;"""
+
+ // `limit 1, cte query`:
+ qt_limit_cte_outside_query """explain shape plan WITH cte AS (SELECT id
FROM t1 WHERE id < 10 ORDER BY id) SELECT id FROM cte LIMIT 1;"""
+
+ // `limit 1, cte query`:
+ qt_limit_cte_outside_query """explain shape plan WITH cte AS (SELECT id
FROM t1 WHERE id < 10 ORDER BY id LIMIT 1) SELECT id FROM cte;"""
+
+ // `limit 1, cte query, join`:
+ qt_limit_cte_query_join """explain shape plan WITH cte1 AS (SELECT id FROM
t1 WHERE id < 10), cte2 AS (SELECT id FROM t2 WHERE id < 10) SELECT cte1.id
FROM cte1 full outer JOIN cte2 ON cte1.id = cte2.id ORDER BY cte1.id LIMIT 1;"""
+
+ // `limit 1, cte query, window`:
+ qt_limit_cte_query_window """explain shape plan WITH cte AS (SELECT id
FROM t1 WHERE id < 10) SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS row_num
FROM cte ORDER BY id LIMIT 1;"""
+
+ // `limit 1, project, filter`:
+ qt_limit_project_filter """explain shape plan SELECT t1.id AS c FROM t1
WHERE t1.id > 100 ORDER BY t1.id LIMIT 1;"""
+
+ // `limit 1, join, filter`:
+ qt_limit_join_filter """explain shape plan SELECT t1.id FROM t1 JOIN t2 ON
t1.id = t2.id WHERE t1.id > 100 ORDER BY t1.id LIMIT 1;"""
+
+ // `limit 1, subquery, join`:
+ qt_limit_subquery_join """explain shape plan SELECT * FROM (SELECT t1.id
FROM t1 JOIN t2 ON t1.id = t2.id) AS subq ORDER BY subq.id LIMIT 1;"""
+
+ // `limit 1, subquery, window`:
+ qt_limit_subquery_window """explain shape plan SELECT id, msg,
ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM (SELECT t1.id as id, t1.msg
FROM t1) AS subq ORDER BY id LIMIT 1;"""
+
+ // `limit 1, nested subquery`:
+ qt_limit_nested_subquery """explain shape plan SELECT * FROM (SELECT *
FROM (SELECT t1.id FROM t1) AS subq1) AS subq2 LIMIT 1;"""
+
+ // `limit 1, subquery, distinct`:
+ qt_limit_subquery_distinct """explain shape plan SELECT DISTINCT subq.id
FROM (SELECT id FROM t1) AS subq ORDER BY subq.id LIMIT 1;"""
+
+ // `limit 1, cross join`:
+ qt_limit_cross_join """explain shape plan SELECT t1.id FROM t1 INNER JOIN
t2 on true ORDER BY t1.id LIMIT 1;"""
+
+ // `limit 1, multiple left outer join`:
+ qt_limit_multiple_left_outer_join """explain shape plan SELECT t1.id FROM
t1 LEFT OUTER JOIN t2 ON t1.id = t2.id LEFT OUTER JOIN t3 ON t1.id = t3.id
ORDER BY t1.id LIMIT 1;"""
+
+ // `limit 1, multiple right outer join`:
+ qt_limit_multiple_right_outer_join """explain shape plan SELECT t1.id FROM
t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id RIGHT OUTER JOIN t3 ON t1.id = t3.id
ORDER BY t1.id LIMIT 1;"""
+
+ // `limit 1, multiple full outer join`:
+ qt_limit_multiple_full_outerjoin """explain shape plan SELECT t1.id FROM
t1 FULL OUTER JOIN t2 ON t1.id = t2.id FULL OUTER JOIN t3 ON t1.id = t3.id
ORDER BY t1.id LIMIT 1;"""
+
+ // `limit 1, subquery, cross join`:
+ qt_limit_subquery_cross_join """explain shape plan SELECT * FROM (SELECT
t1.id FROM t1) AS subq CROSS JOIN t2 ORDER BY subq.id LIMIT 1;"""
+
+ // `limit 1, subquery, multiple join`:
+ qt_limit_subquery_multiple_join """explain shape plan SELECT * FROM
(SELECT t1.id FROM t1) AS subq JOIN t2 ON subq.id = t2.id JOIN t3 ON subq.id =
t3.id ORDER BY subq.id LIMIT 1;"""
+
+ // `limit 1, subquery, multiple join, nested subquery`:
+ qt_limit_subquery_multiple_join_nested_subquery """explain shape plan
SELECT * FROM (SELECT * FROM (SELECT t1.id FROM t1) AS subq1) AS subq2 JOIN t2
ON subq2.id = t2.id JOIN t3 ON subq2.id = t3.id ORDER BY subq2.id LIMIT 1;"""
+
+ // `limit 1, subquery, multiple join, nested subquery, distinct`:
+ qt_limit_subquery_multiple_join_nested_subquery_distinct """explain shape
plan SELECT DISTINCT subq2.id FROM (SELECT * FROM (SELECT t1.id FROM t1) AS
subq1) AS subq2 JOIN t2 ON subq2.id = t2.id JOIN t3 ON subq2.id = t3.id ORDER
BY subq2.id LIMIT 1;"""
+
+ // `limit 1, subquery, multiple join, nested subquery, distinct, filter`:
+ qt_limit_subquery_multiple_join_nested_subquery_distinct_filter """explain
shape plan SELECT DISTINCT subq2.id FROM (SELECT * FROM (SELECT t1.id FROM t1
WHERE t1.id > 100) AS subq1) AS subq2 JOIN t2 ON subq2.id = t2.id JOIN t3 ON
subq2.id = t3.id ORDER BY subq2.id LIMIT 1;"""
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_rules_p0/load.groovy
b/regression-test/suites/nereids_rules_p0/load.groovy
new file mode 100644
index 00000000000..0468e3e1e44
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/load.groovy
@@ -0,0 +1,71 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("load") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+ sql """
+ DROP TABLE IF EXISTS t1
+ """
+ sql """
+ CREATE TABLE IF NOT EXISTS t1(
+ `id` int(11) NULL,
+ `msg` text NULL
+ ) ENGINE = OLAP
+ DISTRIBUTED BY HASH(id) BUCKETS 4
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """
+ DROP TABLE IF EXISTS t2
+ """
+ sql """
+ CREATE TABLE IF NOT EXISTS t2(
+ `id` int(11) NULL,
+ `msg` text NULL
+ ) ENGINE = OLAP
+ DISTRIBUTED BY HASH(id) BUCKETS 4
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS t3(
+ `id` int(11) NULL,
+ `msg` text NULL
+ ) ENGINE = OLAP
+ DISTRIBUTED BY HASH(id) BUCKETS 4
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS t4(
+ `id` int(11) NULL,
+ `msg` text NULL
+ ) ENGINE = OLAP
+ DISTRIBUTED BY HASH(id) BUCKETS 4
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+}
+
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
