You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2024/02/21 11:19:03 UTC
(doris) 03/08: [fix](nereids)should normalize window expression by bottom project's output (#31115)
This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
commit eb5e6a829aea47e53071e4f7bed84b8799a157b2
Author: starocean999 <40...@users.noreply.github.com>
AuthorDate: Wed Feb 21 17:44:05 2024 +0800
[fix](nereids)should normalize window expression by bottom project's output (#31115)
---
.../ExtractAndNormalizeWindowExpression.java | 19 ++++--
.../nereids_p0/cte/test_cte_filter_pushdown.out | 18 +++++
.../shape/query51.out | 77 +++++++++++-----------
.../shape/query70.out | 33 +++++-----
.../noStatsRfPrune/query51.out | 77 +++++++++++-----------
.../noStatsRfPrune/query70.out | 48 +++++++-------
.../no_stats_shape/query51.out | 77 +++++++++++-----------
.../no_stats_shape/query70.out | 50 +++++++-------
.../rf_prune/query51.out | 77 +++++++++++-----------
.../rf_prune/query70.out | 33 +++++-----
.../nereids_tpcds_shape_sf100_p0/shape/query51.out | 77 +++++++++++-----------
.../nereids_tpcds_shape_sf100_p0/shape/query70.out | 33 +++++-----
.../nereids_p0/cte/test_cte_filter_pushdown.groovy | 30 ++++++++-
13 files changed, 347 insertions(+), 302 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ExtractAndNormalizeWindowExpression.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ExtractAndNormalizeWindowExpression.java
index 3f13f8bd3f3..697eb8fa5a3 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ExtractAndNormalizeWindowExpression.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ExtractAndNormalizeWindowExpression.java
@@ -38,6 +38,7 @@ import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Sets;
import java.util.List;
+import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;
import java.util.stream.Stream;
@@ -91,11 +92,21 @@ public class ExtractAndNormalizeWindowExpression extends OneRewriteRuleFactory i
// 2. handle window's outputs and windowExprs
// need to replace exprs with SlotReference in WindowSpec, due to LogicalWindow.getExpressions()
- List<NamedExpression> normalizedOutputs1 = context.normalizeToUseSlotRef(outputs);
+
+ // because alias is pushed down to bottom project
+ // we need replace alias's child expr with corresponding alias's slot in output
+ // so create a customNormalizeMap alias's child -> alias.toSlot to do it
+ Map<Expression, Slot> customNormalizeMap = toBePushedDown.stream()
+ .filter(expr -> expr instanceof Alias)
+ .collect(Collectors.toMap(expr -> ((Alias) expr).child(), expr -> ((Alias) expr).toSlot(),
+ (oldExpr, newExpr) -> oldExpr));
+
+ List<NamedExpression> normalizedOutputs = context.normalizeToUseSlotRef(outputs,
+ (ctx, expr) -> customNormalizeMap.getOrDefault(expr, null));
Set<WindowExpression> normalizedWindows =
- ExpressionUtils.collect(normalizedOutputs1, WindowExpression.class::isInstance);
+ ExpressionUtils.collect(normalizedOutputs, WindowExpression.class::isInstance);
- existedAlias = ExpressionUtils.collect(normalizedOutputs1, Alias.class::isInstance);
+ existedAlias = ExpressionUtils.collect(normalizedOutputs, Alias.class::isInstance);
NormalizeToSlotContext ctxForWindows = NormalizeToSlotContext.buildContext(
existedAlias, Sets.newHashSet(normalizedWindows));
@@ -105,7 +116,7 @@ public class ExtractAndNormalizeWindowExpression extends OneRewriteRuleFactory i
new LogicalWindow<>(ImmutableList.copyOf(normalizedWindowWithAlias), normalizedChild);
// 3. handle top projects
- List<NamedExpression> topProjects = ctxForWindows.normalizeToUseSlotRef(normalizedOutputs1);
+ List<NamedExpression> topProjects = ctxForWindows.normalizeToUseSlotRef(normalizedOutputs);
return project.withProjectsAndChild(topProjects, normalizedLogicalWindow);
}).toRule(RuleType.EXTRACT_AND_NORMALIZE_WINDOW_EXPRESSIONS);
}
diff --git a/regression-test/data/nereids_p0/cte/test_cte_filter_pushdown.out b/regression-test/data/nereids_p0/cte/test_cte_filter_pushdown.out
index 6b25adffa3e..7ced0007c18 100644
--- a/regression-test/data/nereids_p0/cte/test_cte_filter_pushdown.out
+++ b/regression-test/data/nereids_p0/cte/test_cte_filter_pushdown.out
@@ -41,3 +41,21 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------------filter((m2.k1 = 1))
----------------PhysicalCteConsumer ( cteId=CTEId#0 )
+-- !cte_filter_pushdown_3 --
+PhysicalResultSink
+--PhysicalDistribute[DistributionSpecGather]
+----PhysicalProject
+------hashJoin[INNER_JOIN] hashCondition=((k3 = dd.k3)) otherCondition=()
+--------PhysicalDistribute[DistributionSpecHash]
+----------PhysicalProject
+------------filter((tmp2.k3 = 0))
+--------------PhysicalWindow
+----------------PhysicalQuickSort[LOCAL_SORT]
+------------------PhysicalProject
+--------------------filter((tmp.k1 = 1))
+----------------------PhysicalOlapScan[test]
+--------PhysicalDistribute[DistributionSpecHash]
+----------PhysicalProject
+------------filter((dd.k3 = 0))
+--------------PhysicalOlapScan[baseall]
+
diff --git a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query51.out b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query51.out
index a2b1574bf0e..0483d72c793 100644
--- a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query51.out
+++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query51.out
@@ -4,45 +4,44 @@ PhysicalResultSink
--PhysicalTopN[MERGE_SORT]
----PhysicalDistribute[DistributionSpecGather]
------PhysicalTopN[LOCAL_SORT]
---------PhysicalProject
-----------filter((web_cumulative > store_cumulative))
-------------PhysicalWindow
---------------PhysicalQuickSort[LOCAL_SORT]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[FULL_OUTER_JOIN] hashCondition=((web.d_date = store.d_date) and (web.item_sk = store.item_sk)) otherCondition=()
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------PhysicalProject
---------------------------PhysicalWindow
-----------------------------PhysicalQuickSort[LOCAL_SORT]
-------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------PhysicalProject
-----------------------------------hashAgg[GLOBAL]
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------hashAgg[LOCAL]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[ws_sold_date_sk]
+--------filter((web_cumulative > store_cumulative))
+----------PhysicalWindow
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------PhysicalProject
+------------------hashJoin[FULL_OUTER_JOIN] hashCondition=((web.d_date = store.d_date) and (web.item_sk = store.item_sk)) otherCondition=()
+--------------------PhysicalDistribute[DistributionSpecHash]
+----------------------PhysicalProject
+------------------------PhysicalWindow
+--------------------------PhysicalQuickSort[LOCAL_SORT]
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------hashAgg[GLOBAL]
+----------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------hashAgg[LOCAL]
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[ws_sold_date_sk]
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[web_sales] apply RFs: RF1
+------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[web_sales] apply RFs: RF1
---------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------PhysicalProject
-------------------------------------------------filter((date_dim.d_month_seq <= 1223) and (date_dim.d_month_seq >= 1212))
---------------------------------------------------PhysicalOlapScan[date_dim]
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------PhysicalProject
---------------------------PhysicalWindow
-----------------------------PhysicalQuickSort[LOCAL_SORT]
-------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------PhysicalProject
-----------------------------------hashAgg[GLOBAL]
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------hashAgg[LOCAL]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
+----------------------------------------------filter((date_dim.d_month_seq <= 1223) and (date_dim.d_month_seq >= 1212))
+------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------PhysicalDistribute[DistributionSpecHash]
+----------------------PhysicalProject
+------------------------PhysicalWindow
+--------------------------PhysicalQuickSort[LOCAL_SORT]
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------hashAgg[GLOBAL]
+----------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------hashAgg[LOCAL]
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0
+------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0
---------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------PhysicalProject
-------------------------------------------------filter((date_dim.d_month_seq <= 1223) and (date_dim.d_month_seq >= 1212))
---------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------------filter((date_dim.d_month_seq <= 1223) and (date_dim.d_month_seq >= 1212))
+------------------------------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query70.out b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query70.out
index 1123317f157..35ca9710459 100644
--- a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query70.out
+++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query70.out
@@ -32,23 +32,22 @@ PhysicalResultSink
--------------------------------------PhysicalProject
----------------------------------------filter((ranking <= 5))
------------------------------------------PhysicalWindow
---------------------------------------------PhysicalPartitionTopN
-----------------------------------------------PhysicalDistribute[DistributionSpecHash]
-------------------------------------------------PhysicalPartitionTopN
---------------------------------------------------PhysicalProject
-----------------------------------------------------hashAgg[GLOBAL]
-------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------------------------hashAgg[LOCAL]
-----------------------------------------------------------PhysicalProject
-------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=() build RFs:RF1 s_store_sk->[ss_store_sk]
---------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((date_dim.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
-----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
-----------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------filter((date_dim.d_month_seq <= 1231) and (date_dim.d_month_seq >= 1220))
-----------------------------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------------PhysicalQuickSort[LOCAL_SORT]
+----------------------------------------------PhysicalPartitionTopN
+------------------------------------------------PhysicalProject
+--------------------------------------------------hashAgg[GLOBAL]
+----------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------hashAgg[LOCAL]
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=() build RFs:RF1 s_store_sk->[ss_store_sk]
+------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((date_dim.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
+--------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
--------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[store]
+------------------------------------------------------------------filter((date_dim.d_month_seq <= 1231) and (date_dim.d_month_seq >= 1220))
+--------------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------PhysicalOlapScan[store]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query51.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query51.out
index 648db486099..0c8124f96ed 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query51.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query51.out
@@ -4,45 +4,44 @@ PhysicalResultSink
--PhysicalTopN[MERGE_SORT]
----PhysicalDistribute[DistributionSpecGather]
------PhysicalTopN[LOCAL_SORT]
---------PhysicalProject
-----------filter((web_cumulative > store_cumulative))
-------------PhysicalWindow
---------------PhysicalQuickSort[LOCAL_SORT]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[FULL_OUTER_JOIN] hashCondition=((web.d_date = store.d_date) and (web.item_sk = store.item_sk)) otherCondition=()
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------PhysicalProject
---------------------------PhysicalWindow
-----------------------------PhysicalQuickSort[LOCAL_SORT]
-------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------PhysicalProject
-----------------------------------hashAgg[GLOBAL]
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------hashAgg[LOCAL]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[ss_sold_date_sk]
+--------filter((web_cumulative > store_cumulative))
+----------PhysicalWindow
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------PhysicalProject
+------------------hashJoin[FULL_OUTER_JOIN] hashCondition=((web.d_date = store.d_date) and (web.item_sk = store.item_sk)) otherCondition=()
+--------------------PhysicalDistribute[DistributionSpecHash]
+----------------------PhysicalProject
+------------------------PhysicalWindow
+--------------------------PhysicalQuickSort[LOCAL_SORT]
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------hashAgg[GLOBAL]
+----------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------hashAgg[LOCAL]
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[ss_sold_date_sk]
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF1
+------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF1
---------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------PhysicalProject
-------------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
---------------------------------------------------PhysicalOlapScan[date_dim]
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------PhysicalProject
---------------------------PhysicalWindow
-----------------------------PhysicalQuickSort[LOCAL_SORT]
-------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------PhysicalProject
-----------------------------------hashAgg[GLOBAL]
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------hashAgg[LOCAL]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ws_sold_date_sk]
+----------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
+------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------PhysicalDistribute[DistributionSpecHash]
+----------------------PhysicalProject
+------------------------PhysicalWindow
+--------------------------PhysicalQuickSort[LOCAL_SORT]
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------hashAgg[GLOBAL]
+----------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------hashAgg[LOCAL]
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ws_sold_date_sk]
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0
+------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0
---------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------PhysicalProject
-------------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
---------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
+------------------------------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query70.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query70.out
index 01583d0ca4f..d919942139d 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query70.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query70.out
@@ -15,40 +15,38 @@ PhysicalResultSink
------------------------hashAgg[LOCAL]
--------------------------PhysicalRepeat
----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=() build RFs:RF3 s_store_sk->[ss_store_sk]
+------------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=() build RFs:RF4 s_store_sk->[ss_store_sk]
--------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN] hashCondition=((d1.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk]
+----------------------------------hashJoin[INNER_JOIN] hashCondition=((d1.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF3 d_date_sk->[ss_sold_date_sk]
------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF2 RF3
+--------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF3 RF4
------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------PhysicalProject
----------------------------------------filter((d1.d_month_seq <= 1224) and (d1.d_month_seq >= 1213))
------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------PhysicalDistribute[DistributionSpecReplicated]
----------------------------------hashJoin[RIGHT_SEMI_JOIN] hashCondition=((store.s_state = tmp1.s_state)) otherCondition=()
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------PhysicalProject
-----------------------------------------filter((ranking <= 5))
-------------------------------------------PhysicalWindow
+------------------------------------PhysicalProject
+--------------------------------------filter((ranking <= 5))
+----------------------------------------PhysicalWindow
+------------------------------------------PhysicalQuickSort[LOCAL_SORT]
--------------------------------------------PhysicalPartitionTopN
-----------------------------------------------PhysicalDistribute[DistributionSpecHash]
-------------------------------------------------PhysicalPartitionTopN
---------------------------------------------------PhysicalProject
-----------------------------------------------------hashAgg[GLOBAL]
-------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------------------------hashAgg[LOCAL]
-----------------------------------------------------------PhysicalProject
-------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=()
---------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((date_dim.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
-----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0
-----------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------filter((date_dim.d_month_seq <= 1224) and (date_dim.d_month_seq >= 1213))
-----------------------------------------------------------------------PhysicalOlapScan[date_dim]
---------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[store]
+----------------------------------------------PhysicalProject
+------------------------------------------------hashAgg[GLOBAL]
+--------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------hashAgg[LOCAL]
+------------------------------------------------------PhysicalProject
+--------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=()
+----------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((date_dim.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0
+------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------filter((date_dim.d_month_seq <= 1224) and (date_dim.d_month_seq >= 1213))
+------------------------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[store]
------------------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------------------PhysicalProject
----------------------------------------PhysicalOlapScan[store]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query51.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query51.out
index 648db486099..0c8124f96ed 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query51.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query51.out
@@ -4,45 +4,44 @@ PhysicalResultSink
--PhysicalTopN[MERGE_SORT]
----PhysicalDistribute[DistributionSpecGather]
------PhysicalTopN[LOCAL_SORT]
---------PhysicalProject
-----------filter((web_cumulative > store_cumulative))
-------------PhysicalWindow
---------------PhysicalQuickSort[LOCAL_SORT]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[FULL_OUTER_JOIN] hashCondition=((web.d_date = store.d_date) and (web.item_sk = store.item_sk)) otherCondition=()
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------PhysicalProject
---------------------------PhysicalWindow
-----------------------------PhysicalQuickSort[LOCAL_SORT]
-------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------PhysicalProject
-----------------------------------hashAgg[GLOBAL]
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------hashAgg[LOCAL]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[ss_sold_date_sk]
+--------filter((web_cumulative > store_cumulative))
+----------PhysicalWindow
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------PhysicalProject
+------------------hashJoin[FULL_OUTER_JOIN] hashCondition=((web.d_date = store.d_date) and (web.item_sk = store.item_sk)) otherCondition=()
+--------------------PhysicalDistribute[DistributionSpecHash]
+----------------------PhysicalProject
+------------------------PhysicalWindow
+--------------------------PhysicalQuickSort[LOCAL_SORT]
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------hashAgg[GLOBAL]
+----------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------hashAgg[LOCAL]
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[ss_sold_date_sk]
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF1
+------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF1
---------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------PhysicalProject
-------------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
---------------------------------------------------PhysicalOlapScan[date_dim]
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------PhysicalProject
---------------------------PhysicalWindow
-----------------------------PhysicalQuickSort[LOCAL_SORT]
-------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------PhysicalProject
-----------------------------------hashAgg[GLOBAL]
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------hashAgg[LOCAL]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ws_sold_date_sk]
+----------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
+------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------PhysicalDistribute[DistributionSpecHash]
+----------------------PhysicalProject
+------------------------PhysicalWindow
+--------------------------PhysicalQuickSort[LOCAL_SORT]
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------hashAgg[GLOBAL]
+----------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------hashAgg[LOCAL]
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ws_sold_date_sk]
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0
+------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0
---------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------PhysicalProject
-------------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
---------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
+------------------------------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query70.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query70.out
index 63b6d3b5b42..c0383fa20db 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query70.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query70.out
@@ -15,40 +15,38 @@ PhysicalResultSink
------------------------hashAgg[LOCAL]
--------------------------PhysicalRepeat
----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=() build RFs:RF3 s_store_sk->[ss_store_sk]
+------------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=() build RFs:RF4 s_store_sk->[ss_store_sk]
--------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN] hashCondition=((d1.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk]
+----------------------------------hashJoin[INNER_JOIN] hashCondition=((d1.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF3 d_date_sk->[ss_sold_date_sk]
------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF2 RF3
+--------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF3 RF4
------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------PhysicalProject
----------------------------------------filter((d1.d_month_seq <= 1224) and (d1.d_month_seq >= 1213))
------------------------------------------PhysicalOlapScan[date_dim]
--------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------hashJoin[RIGHT_SEMI_JOIN] hashCondition=((store.s_state = tmp1.s_state)) otherCondition=()
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------PhysicalProject
-----------------------------------------filter((ranking <= 5))
-------------------------------------------PhysicalWindow
+----------------------------------hashJoin[RIGHT_SEMI_JOIN] hashCondition=((store.s_state = tmp1.s_state)) otherCondition=() build RFs:RF2 s_state->[s_state]
+------------------------------------PhysicalProject
+--------------------------------------filter((ranking <= 5))
+----------------------------------------PhysicalWindow
+------------------------------------------PhysicalQuickSort[LOCAL_SORT]
--------------------------------------------PhysicalPartitionTopN
-----------------------------------------------PhysicalDistribute[DistributionSpecHash]
-------------------------------------------------PhysicalPartitionTopN
---------------------------------------------------PhysicalProject
-----------------------------------------------------hashAgg[GLOBAL]
-------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------------------------hashAgg[LOCAL]
-----------------------------------------------------------PhysicalProject
-------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=() build RFs:RF1 s_store_sk->[ss_store_sk]
---------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((date_dim.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
-----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
-----------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------filter((date_dim.d_month_seq <= 1224) and (date_dim.d_month_seq >= 1213))
-----------------------------------------------------------------------PhysicalOlapScan[date_dim]
---------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[store]
+----------------------------------------------PhysicalProject
+------------------------------------------------hashAgg[GLOBAL]
+--------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------hashAgg[LOCAL]
+------------------------------------------------------PhysicalProject
+--------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=() build RFs:RF1 s_store_sk->[ss_store_sk]
+----------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((date_dim.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
+------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------filter((date_dim.d_month_seq <= 1224) and (date_dim.d_month_seq >= 1213))
+------------------------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[store] apply RFs: RF2
------------------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------------------PhysicalProject
----------------------------------------PhysicalOlapScan[store]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query51.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query51.out
index 648db486099..0c8124f96ed 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query51.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query51.out
@@ -4,45 +4,44 @@ PhysicalResultSink
--PhysicalTopN[MERGE_SORT]
----PhysicalDistribute[DistributionSpecGather]
------PhysicalTopN[LOCAL_SORT]
---------PhysicalProject
-----------filter((web_cumulative > store_cumulative))
-------------PhysicalWindow
---------------PhysicalQuickSort[LOCAL_SORT]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[FULL_OUTER_JOIN] hashCondition=((web.d_date = store.d_date) and (web.item_sk = store.item_sk)) otherCondition=()
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------PhysicalProject
---------------------------PhysicalWindow
-----------------------------PhysicalQuickSort[LOCAL_SORT]
-------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------PhysicalProject
-----------------------------------hashAgg[GLOBAL]
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------hashAgg[LOCAL]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[ss_sold_date_sk]
+--------filter((web_cumulative > store_cumulative))
+----------PhysicalWindow
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------PhysicalProject
+------------------hashJoin[FULL_OUTER_JOIN] hashCondition=((web.d_date = store.d_date) and (web.item_sk = store.item_sk)) otherCondition=()
+--------------------PhysicalDistribute[DistributionSpecHash]
+----------------------PhysicalProject
+------------------------PhysicalWindow
+--------------------------PhysicalQuickSort[LOCAL_SORT]
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------hashAgg[GLOBAL]
+----------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------hashAgg[LOCAL]
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[ss_sold_date_sk]
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF1
+------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF1
---------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------PhysicalProject
-------------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
---------------------------------------------------PhysicalOlapScan[date_dim]
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------PhysicalProject
---------------------------PhysicalWindow
-----------------------------PhysicalQuickSort[LOCAL_SORT]
-------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------PhysicalProject
-----------------------------------hashAgg[GLOBAL]
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------hashAgg[LOCAL]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ws_sold_date_sk]
+----------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
+------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------PhysicalDistribute[DistributionSpecHash]
+----------------------PhysicalProject
+------------------------PhysicalWindow
+--------------------------PhysicalQuickSort[LOCAL_SORT]
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------hashAgg[GLOBAL]
+----------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------hashAgg[LOCAL]
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ws_sold_date_sk]
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0
+------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0
---------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------PhysicalProject
-------------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
---------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
+------------------------------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query70.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query70.out
index d9995e7762f..93610210333 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query70.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query70.out
@@ -32,23 +32,22 @@ PhysicalResultSink
--------------------------------------PhysicalProject
----------------------------------------filter((ranking <= 5))
------------------------------------------PhysicalWindow
---------------------------------------------PhysicalPartitionTopN
-----------------------------------------------PhysicalDistribute[DistributionSpecHash]
-------------------------------------------------PhysicalPartitionTopN
---------------------------------------------------PhysicalProject
-----------------------------------------------------hashAgg[GLOBAL]
-------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------------------------hashAgg[LOCAL]
-----------------------------------------------------------PhysicalProject
-------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=()
---------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((date_dim.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
-----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0
-----------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------filter((date_dim.d_month_seq <= 1224) and (date_dim.d_month_seq >= 1213))
-----------------------------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------------PhysicalQuickSort[LOCAL_SORT]
+----------------------------------------------PhysicalPartitionTopN
+------------------------------------------------PhysicalProject
+--------------------------------------------------hashAgg[GLOBAL]
+----------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------hashAgg[LOCAL]
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=()
+------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((date_dim.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
+--------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0
--------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[store]
+------------------------------------------------------------------filter((date_dim.d_month_seq <= 1224) and (date_dim.d_month_seq >= 1213))
+--------------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------PhysicalOlapScan[store]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query51.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query51.out
index 648db486099..0c8124f96ed 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query51.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query51.out
@@ -4,45 +4,44 @@ PhysicalResultSink
--PhysicalTopN[MERGE_SORT]
----PhysicalDistribute[DistributionSpecGather]
------PhysicalTopN[LOCAL_SORT]
---------PhysicalProject
-----------filter((web_cumulative > store_cumulative))
-------------PhysicalWindow
---------------PhysicalQuickSort[LOCAL_SORT]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[FULL_OUTER_JOIN] hashCondition=((web.d_date = store.d_date) and (web.item_sk = store.item_sk)) otherCondition=()
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------PhysicalProject
---------------------------PhysicalWindow
-----------------------------PhysicalQuickSort[LOCAL_SORT]
-------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------PhysicalProject
-----------------------------------hashAgg[GLOBAL]
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------hashAgg[LOCAL]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[ss_sold_date_sk]
+--------filter((web_cumulative > store_cumulative))
+----------PhysicalWindow
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------PhysicalProject
+------------------hashJoin[FULL_OUTER_JOIN] hashCondition=((web.d_date = store.d_date) and (web.item_sk = store.item_sk)) otherCondition=()
+--------------------PhysicalDistribute[DistributionSpecHash]
+----------------------PhysicalProject
+------------------------PhysicalWindow
+--------------------------PhysicalQuickSort[LOCAL_SORT]
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------hashAgg[GLOBAL]
+----------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------hashAgg[LOCAL]
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[ss_sold_date_sk]
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF1
+------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF1
---------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------PhysicalProject
-------------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
---------------------------------------------------PhysicalOlapScan[date_dim]
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------PhysicalProject
---------------------------PhysicalWindow
-----------------------------PhysicalQuickSort[LOCAL_SORT]
-------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------PhysicalProject
-----------------------------------hashAgg[GLOBAL]
-------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------hashAgg[LOCAL]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ws_sold_date_sk]
+----------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
+------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------PhysicalDistribute[DistributionSpecHash]
+----------------------PhysicalProject
+------------------------PhysicalWindow
+--------------------------PhysicalQuickSort[LOCAL_SORT]
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------hashAgg[GLOBAL]
+----------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------hashAgg[LOCAL]
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ws_sold_date_sk]
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0
+------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0
---------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-----------------------------------------------PhysicalProject
-------------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
---------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------------filter((date_dim.d_month_seq <= 1227) and (date_dim.d_month_seq >= 1216))
+------------------------------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query70.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query70.out
index c571cc81f8b..f3e8b1c3a72 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query70.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query70.out
@@ -32,23 +32,22 @@ PhysicalResultSink
--------------------------------------PhysicalProject
----------------------------------------filter((ranking <= 5))
------------------------------------------PhysicalWindow
---------------------------------------------PhysicalPartitionTopN
-----------------------------------------------PhysicalDistribute[DistributionSpecHash]
-------------------------------------------------PhysicalPartitionTopN
---------------------------------------------------PhysicalProject
-----------------------------------------------------hashAgg[GLOBAL]
-------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------------------------hashAgg[LOCAL]
-----------------------------------------------------------PhysicalProject
-------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=() build RFs:RF1 s_store_sk->[ss_store_sk]
---------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((date_dim.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
-----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
-----------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
-------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------filter((date_dim.d_month_seq <= 1224) and (date_dim.d_month_seq >= 1213))
-----------------------------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------------PhysicalQuickSort[LOCAL_SORT]
+----------------------------------------------PhysicalPartitionTopN
+------------------------------------------------PhysicalProject
+--------------------------------------------------hashAgg[GLOBAL]
+----------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------hashAgg[LOCAL]
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=() build RFs:RF1 s_store_sk->[ss_store_sk]
+------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((date_dim.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
+--------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
--------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[store]
+------------------------------------------------------------------filter((date_dim.d_month_seq <= 1224) and (date_dim.d_month_seq >= 1213))
+--------------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------PhysicalOlapScan[store]
diff --git a/regression-test/suites/nereids_p0/cte/test_cte_filter_pushdown.groovy b/regression-test/suites/nereids_p0/cte/test_cte_filter_pushdown.groovy
index 53ae45f7c0f..981181e446d 100644
--- a/regression-test/suites/nereids_p0/cte/test_cte_filter_pushdown.groovy
+++ b/regression-test/suites/nereids_p0/cte/test_cte_filter_pushdown.groovy
@@ -18,7 +18,6 @@ suite("test_cte_filter_pushdown") {
sql "SET enable_nereids_planner=true"
sql "SET enable_pipeline_engine=true"
sql "SET enable_fallback_to_original_planner=false"
- sql "set enable_runtime_filter_prune=false"
// CTE filter pushing down with the same filter
qt_cte_filter_pushdown_1 """
explain shape plan
@@ -44,4 +43,33 @@ suite("test_cte_filter_pushdown") {
) temp
where k1 = 1;
"""
+ sql 'set exec_mem_limit=21G'
+ sql 'set be_number_for_test=3'
+ sql 'set parallel_fragment_exec_instance_num=8; '
+ sql 'set parallel_pipeline_task_num=8; '
+ sql 'set forbid_unknown_col_stats=true'
+ sql 'set enable_nereids_timeout = false'
+ sql 'set enable_runtime_filter_prune=false'
+ sql 'set runtime_filter_mode=off'
+ sql 'set dump_nereids_memo=false'
+ sql "set disable_join_reorder=true"
+ qt_cte_filter_pushdown_3 """
+ explain shape plan
+ with tmp as (
+ select
+ k1,
+ k3,
+ sum(k2) over (partition by l.k1 order by l.k3 ) pay_num
+ from ( select * from nereids_test_query_db.test)l
+ ),
+ tmp2 as (
+ select
+ tt.*
+ from
+ tmp tt join (select k3 from nereids_test_query_db.baseall ) dd
+ on tt.k3=dd.k3
+ )
+ SELECT * from tmp2
+ where k3=0 and k1=1;
+ """
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org