This is an automated email from the ASF dual-hosted git repository.
dataroaring pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/doris.git
commit 18a69dfa04cef4a86e96094804643d0f1382b016
Author: xzj7019 <131111794+xzj7...@users.noreply.github.com>
AuthorDate: Fri Jul 19 15:39:54 2024 +0800
[opt](nereids) refine left semi/anti cost under short-cut opt (#37951)
Refine left semi/anti cost computing under short-cut opt, for the case
whose semi/anti join has the small left side and big right side, which
original solution can't support. This pr reduce the left style cost by
reduce the right side cost and improve the possibility of choosing left
style joins.
Pass the performance test on tpch/tpcds/usercase.
previous work: #37060
---
.../org/apache/doris/nereids/cost/CostModelV1.java | 9 ++--
.../java/org/apache/doris/qe/SessionVariable.java | 2 +-
.../shape/query23.out | 12 +++---
.../shape/query33.out | 26 +++++------
.../shape/query35.out | 44 +++++++++----------
.../shape/query58.out | 16 +++----
.../shape/query69.out | 42 +++++++++---------
.../shape/query83.out | 16 +++----
.../noStatsRfPrune/query14.out | 8 ++--
.../noStatsRfPrune/query58.out | 18 ++++----
.../noStatsRfPrune/query95.out | 28 ++++++------
.../no_stats_shape/query14.out | 10 ++---
.../no_stats_shape/query58.out | 24 +++++------
.../no_stats_shape/query95.out | 28 ++++++------
.../rf_prune/query14.out | 46 ++++++++++----------
.../rf_prune/query16.out | 4 +-
.../rf_prune/query23.out | 12 +++---
.../rf_prune/query33.out | 34 +++++++--------
.../rf_prune/query35.out | 50 +++++++++++-----------
.../rf_prune/query69.out | 42 +++++++++---------
.../rf_prune/query94.out | 4 +-
.../nereids_tpcds_shape_sf100_p0/shape/query14.out | 46 ++++++++++----------
.../nereids_tpcds_shape_sf100_p0/shape/query16.out | 4 +-
.../nereids_tpcds_shape_sf100_p0/shape/query23.out | 12 +++---
.../nereids_tpcds_shape_sf100_p0/shape/query33.out | 26 +++++------
.../nereids_tpcds_shape_sf100_p0/shape/query35.out | 50 +++++++++++-----------
.../nereids_tpcds_shape_sf100_p0/shape/query69.out | 42 +++++++++---------
.../nereids_tpcds_shape_sf100_p0/shape/query94.out | 4 +-
.../nereids_tpch_shape_sf1000_p0/rf_prune/q16.out | 2 +-
.../nereids_tpch_shape_sf1000_p0/shape/q16.out | 2 +-
30 files changed, 330 insertions(+), 333 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
index 68ddbac13aa..4ef38138755 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
@@ -387,7 +387,9 @@ class CostModelV1 extends PlanVisitor<Cost, PlanContext> {
);
}
double probeShortcutFactor = 1.0;
- if (physicalHashJoin.getJoinType().isLeftSemiOrAntiJoin()
+ if (ConnectContext.get() != null &&
ConnectContext.get().getStatementContext() != null
+ &&
!ConnectContext.get().getStatementContext().isHasUnknownColStats()
+ && physicalHashJoin.getJoinType().isLeftSemiOrAntiJoin()
&& physicalHashJoin.getOtherJoinConjuncts().isEmpty()
&& physicalHashJoin.getMarkJoinConjuncts().isEmpty()) {
// left semi/anti has short-cut opt, add probe side factor for
distinguishing from the right ones
@@ -414,15 +416,14 @@ class CostModelV1 extends PlanVisitor<Cost, PlanContext> {
}
}
return CostV1.of(context.getSessionVariable(),
- leftRowCount * probeShortcutFactor
- + rightRowCount * buildSideFactor
+ leftRowCount * probeShortcutFactor + rightRowCount *
probeShortcutFactor * buildSideFactor
+ outputRowCount * probeSideFactor,
rightRowCount,
0
);
}
return CostV1.of(context.getSessionVariable(),
- leftRowCount * probeShortcutFactor + rightRowCount +
outputRowCount,
+ leftRowCount * probeShortcutFactor + rightRowCount *
probeShortcutFactor + outputRowCount,
rightRowCount, 0
);
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
index 4daa717f8bc..082e5605764 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
@@ -1297,7 +1297,7 @@ public class SessionVariable implements Serializable,
Writable {
private double broadcastRightTableScaleFactor = 0.0;
@VariableMgr.VarAttr(name = LEFT_SEMI_OR_ANTI_PROBE_FACTOR)
- private double leftSemiOrAntiProbeFactor = 0.1;
+ private double leftSemiOrAntiProbeFactor = 0.05;
@VariableMgr.VarAttr(name = BROADCAST_ROW_COUNT_LIMIT, needForward = true)
private double broadcastRowCountLimit = 30000000;
diff --git
a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query23.out
b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query23.out
index fa66824d3b8..9e2140c8327 100644
--- a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query23.out
+++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query23.out
@@ -52,8 +52,7 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------hashAgg[LOCAL]
--------------PhysicalUnion
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = frequent_ss_items.item_sk))
otherCondition=() build RFs:RF4 cs_item_sk->[item_sk]
---------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF4
+------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = frequent_ss_items.item_sk))
otherCondition=()
--------------------PhysicalProject
----------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk =
best_ss_customer.c_customer_sk)) otherCondition=()
------------------------PhysicalProject
@@ -64,17 +63,18 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------------------filter((date_dim.d_moy = 7) and (date_dim.d_year
= 2000))
--------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalCteConsumer ( cteId=CTEId#2 )
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = frequent_ss_items.item_sk))
otherCondition=() build RFs:RF6 ws_item_sk->[item_sk]
---------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF6
+------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = frequent_ss_items.item_sk))
otherCondition=()
--------------------PhysicalProject
----------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_bill_customer_sk =
best_ss_customer.c_customer_sk)) otherCondition=()
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[ws_sold_date_sk]
+--------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[ws_sold_date_sk]
----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[web_sales] apply RFs: RF5
+------------------------------PhysicalOlapScan[web_sales] apply RFs: RF4
----------------------------PhysicalProject
------------------------------filter((date_dim.d_moy = 7) and (date_dim.d_year
= 2000))
--------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalCteConsumer ( cteId=CTEId#2 )
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query33.out
b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query33.out
index 652253ae101..2f1f06384d0 100644
--- a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query33.out
+++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query33.out
@@ -9,10 +9,7 @@ PhysicalResultSink
------------hashAgg[LOCAL]
--------------PhysicalUnion
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF3 i_manufact_id->[i_manufact_id]
---------------------PhysicalProject
-----------------------filter((item.i_category = 'Books'))
-------------------------PhysicalOlapScan[item] apply RFs: RF3
+------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF3 i_manufact_id->[i_manufact_id]
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
@@ -31,12 +28,12 @@ PhysicalResultSink
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item]
-----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF7 i_manufact_id->[i_manufact_id]
+--------------------------------PhysicalOlapScan[item] apply RFs: RF3
--------------------PhysicalProject
----------------------filter((item.i_category = 'Books'))
-------------------------PhysicalOlapScan[item] apply RFs: RF7
+------------------------PhysicalOlapScan[item]
+----------------PhysicalProject
+------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF7 i_manufact_id->[i_manufact_id]
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
@@ -55,12 +52,12 @@ PhysicalResultSink
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item]
-----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF11 i_manufact_id->[i_manufact_id]
+--------------------------------PhysicalOlapScan[item] apply RFs: RF7
--------------------PhysicalProject
----------------------filter((item.i_category = 'Books'))
-------------------------PhysicalOlapScan[item] apply RFs: RF11
+------------------------PhysicalOlapScan[item]
+----------------PhysicalProject
+------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF11 i_manufact_id->[i_manufact_id]
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
@@ -79,5 +76,8 @@ PhysicalResultSink
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item]
+--------------------------------PhysicalOlapScan[item] apply RFs: RF11
+--------------------PhysicalProject
+----------------------filter((item.i_category = 'Books'))
+------------------------PhysicalOlapScan[item]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query35.out
b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query35.out
index ce3860e81ff..d0b387b176e 100644
--- a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query35.out
+++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query35.out
@@ -10,38 +10,38 @@ PhysicalResultSink
--------------hashAgg[LOCAL]
----------------PhysicalProject
------------------filter((ifnull($c$1, FALSE) OR ifnull($c$2, FALSE)))
---------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=()
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[cs_sold_date_sk]
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF5
---------------------------PhysicalProject
-----------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year =
1999))
-------------------------------PhysicalOlapScan[date_dim]
-----------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
-------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[ws_sold_date_sk]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[web_sales] apply RFs: RF4
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year
= 1999))
---------------------------------PhysicalOlapScan[date_dim]
-------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[ss_customer_sk]
+--------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=()
+----------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
+------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[ss_customer_sk]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk]
+----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[ss_sold_date_sk]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[store_sales] apply RFs: RF2
RF3
+--------------------------------PhysicalOlapScan[store_sales] apply RFs: RF4
RF5
------------------------------PhysicalProject
--------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 1999))
----------------------------------PhysicalOlapScan[date_dim]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF1 cd_demo_sk->[c_current_cdemo_sk]
+----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF3 cd_demo_sk->[c_current_cdemo_sk]
------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF0 ca_address_sk->[c_current_addr_sk]
+--------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF2 ca_address_sk->[c_current_addr_sk]
----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[customer] apply RFs: RF0
RF1
+------------------------------------PhysicalOlapScan[customer] apply RFs: RF2
RF3
----------------------------------PhysicalProject
------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
--------------------------------PhysicalOlapScan[customer_demographics]
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN broadcast]
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
+----------------------------PhysicalProject
+------------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year
= 1999))
+--------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF0 d_date_sk->[cs_sold_date_sk]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year =
1999))
+------------------------------PhysicalOlapScan[date_dim]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query58.out
b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query58.out
index 3b7ac825253..3988afcb1c5 100644
--- a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query58.out
+++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query58.out
@@ -5,17 +5,17 @@ PhysicalResultSink
----PhysicalDistribute[DistributionSpecGather]
------PhysicalTopN[LOCAL_SORT]
--------PhysicalProject
-----------hashJoin[INNER_JOIN colocated] hashCondition=((ss_items.item_id =
ws_items.item_id)) otherCondition=((cast(cs_item_rev as DOUBLE) <= cast((1.1 *
ws_item_rev) as DOUBLE)) and (cast(cs_item_rev as DOUBLE) >= cast((0.9 *
ws_item_rev) as DOUBLE)) and (cast(ss_item_rev as DOUBLE) <= cast((1.1 *
ws_item_rev) as DOUBLE)) and (cast(ss_item_rev as DOUBLE) >= cast((0.9 *
ws_item_rev) as DOUBLE)) and (cast(ws_item_rev as DOUBLE) <= cast((1.1 *
cs_item_rev) as DOUBLE)) and (cast(ws_item_re [...]
+----------hashJoin[INNER_JOIN colocated] hashCondition=((ss_items.item_id =
cs_items.item_id)) otherCondition=((cast(cs_item_rev as DOUBLE) <= cast((1.1 *
ss_item_rev) as DOUBLE)) and (cast(cs_item_rev as DOUBLE) <= cast((1.1 *
ws_item_rev) as DOUBLE)) and (cast(cs_item_rev as DOUBLE) >= cast((0.9 *
ss_item_rev) as DOUBLE)) and (cast(cs_item_rev as DOUBLE) >= cast((0.9 *
ws_item_rev) as DOUBLE)) and (cast(ss_item_rev as DOUBLE) <= cast((1.1 *
cs_item_rev) as DOUBLE)) and (cast(ss_item_re [...]
------------PhysicalProject
--------------hashAgg[GLOBAL]
----------------PhysicalDistribute[DistributionSpecHash]
------------------hashAgg[LOCAL]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=() build
RFs:RF12 i_item_sk->[ws_item_sk]
+----------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF12 i_item_sk->[cs_item_sk]
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF11 d_date_sk->[ws_sold_date_sk]
+--------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF11 d_date_sk->[cs_sold_date_sk]
----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[web_sales] apply RFs: RF11 RF12
+------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF11
RF12
----------------------------PhysicalProject
------------------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=() build
RFs:RF10 d_date->[d_date]
--------------------------------PhysicalProject
@@ -32,7 +32,7 @@ PhysicalResultSink
------------------------PhysicalProject
--------------------------PhysicalOlapScan[item] apply RFs: RF13
------------PhysicalProject
---------------hashJoin[INNER_JOIN colocated] hashCondition=((ss_items.item_id
= cs_items.item_id)) otherCondition=((cast(cs_item_rev as DOUBLE) <= cast((1.1
* ss_item_rev) as DOUBLE)) and (cast(cs_item_rev as DOUBLE) >= cast((0.9 *
ss_item_rev) as DOUBLE)) and (cast(ss_item_rev as DOUBLE) <= cast((1.1 *
cs_item_rev) as DOUBLE)) and (cast(ss_item_rev as DOUBLE) >= cast((0.9 *
cs_item_rev) as DOUBLE))) build RFs:RF8 item_id->[i_item_id]
+--------------hashJoin[INNER_JOIN colocated] hashCondition=((ss_items.item_id
= ws_items.item_id)) otherCondition=((cast(ss_item_rev as DOUBLE) <= cast((1.1
* ws_item_rev) as DOUBLE)) and (cast(ss_item_rev as DOUBLE) >= cast((0.9 *
ws_item_rev) as DOUBLE)) and (cast(ws_item_rev as DOUBLE) <= cast((1.1 *
ss_item_rev) as DOUBLE)) and (cast(ws_item_rev as DOUBLE) >= cast((0.9 *
ss_item_rev) as DOUBLE))) build RFs:RF8 item_id->[i_item_id]
----------------PhysicalProject
------------------hashAgg[GLOBAL]
--------------------PhysicalDistribute[DistributionSpecHash]
@@ -63,11 +63,11 @@ PhysicalResultSink
--------------------PhysicalDistribute[DistributionSpecHash]
----------------------hashAgg[LOCAL]
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF3 i_item_sk->[cs_item_sk]
+--------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=() build
RFs:RF3 i_item_sk->[ws_item_sk]
----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[cs_sold_date_sk]
+------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[ws_sold_date_sk]
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[catalog_sales] apply RFs:
RF2 RF3
+----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF2
RF3
--------------------------------PhysicalProject
----------------------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=() build
RFs:RF1 d_date->[d_date]
------------------------------------PhysicalProject
diff --git
a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query69.out
b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query69.out
index c72d47fb024..422f77b5841 100644
--- a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query69.out
+++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query69.out
@@ -9,39 +9,39 @@ PhysicalResultSink
------------PhysicalDistribute[DistributionSpecHash]
--------------hashAgg[LOCAL]
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF7 c_customer_sk->[ss_customer_sk]
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF6 c_customer_sk->[ss_customer_sk]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF6 d_date_sk->[ss_sold_date_sk]
+----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[ss_sold_date_sk]
------------------------PhysicalProject
---------------------------PhysicalOlapScan[store_sales] apply RFs: RF6 RF7
+--------------------------PhysicalOlapScan[store_sales] apply RFs: RF5 RF6
------------------------PhysicalProject
--------------------------filter((date_dim.d_moy <= 3) and (date_dim.d_moy >=
1) and (date_dim.d_year = 2002))
----------------------------PhysicalOlapScan[date_dim]
---------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[cs_ship_customer_sk]
+--------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=() build RFs:RF4 c_customer_sk->[cs_ship_customer_sk]
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[cs_sold_date_sk]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF3 d_date_sk->[cs_sold_date_sk]
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF4 RF5
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF3 RF4
--------------------------PhysicalProject
----------------------------filter((date_dim.d_moy <= 3) and (date_dim.d_moy
>= 1) and (date_dim.d_year = 2002))
------------------------------PhysicalOlapScan[date_dim]
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF3 c_current_cdemo_sk->[cd_demo_sk]
+------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF2 c_current_cdemo_sk->[cd_demo_sk]
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[customer_demographics] apply RFs:
RF3
---------------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=() build RFs:RF2 c_customer_sk->[ws_bill_customer_sk]
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
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
RF2
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_moy <= 3) and
(date_dim.d_moy >= 1) and (date_dim.d_year = 2002))
-------------------------------------PhysicalOlapScan[date_dim]
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF0 ca_address_sk->[c_current_addr_sk]
+----------------------------PhysicalOlapScan[customer_demographics] apply RFs:
RF2
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF1 ca_address_sk->[c_current_addr_sk]
+------------------------------hashJoin[LEFT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[customer] apply RFs: RF0
+----------------------------------PhysicalOlapScan[customer] apply RFs: RF1
--------------------------------PhysicalProject
-----------------------------------filter(ca_state IN ('IL', 'ME', 'TX'))
-------------------------------------PhysicalOlapScan[customer_address]
+----------------------------------hashJoin[INNER_JOIN broadcast]
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
+------------------------------------PhysicalProject
+--------------------------------------filter((date_dim.d_moy <= 3) and
(date_dim.d_moy >= 1) and (date_dim.d_year = 2002))
+----------------------------------------PhysicalOlapScan[date_dim]
+------------------------------PhysicalProject
+--------------------------------filter(ca_state IN ('IL', 'ME', 'TX'))
+----------------------------------PhysicalOlapScan[customer_address]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query83.out
b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query83.out
index c282ed46945..3e3381fe800 100644
--- a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query83.out
+++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query83.out
@@ -5,9 +5,9 @@ PhysicalResultSink
----PhysicalDistribute[DistributionSpecGather]
------PhysicalTopN[LOCAL_SORT]
--------PhysicalProject
-----------hashJoin[INNER_JOIN colocated] hashCondition=((sr_items.item_id =
wr_items.item_id)) otherCondition=() build RFs:RF13
item_id->[i_item_id,i_item_id]
+----------hashJoin[INNER_JOIN colocated] hashCondition=((sr_items.item_id =
cr_items.item_id)) otherCondition=() build RFs:RF13
item_id->[i_item_id,i_item_id]
------------PhysicalProject
---------------hashJoin[INNER_JOIN colocated] hashCondition=((sr_items.item_id
= cr_items.item_id)) otherCondition=() build RFs:RF12 item_id->[i_item_id]
+--------------hashJoin[INNER_JOIN colocated] hashCondition=((sr_items.item_id
= wr_items.item_id)) otherCondition=() build RFs:RF12 item_id->[i_item_id]
----------------PhysicalProject
------------------hashAgg[GLOBAL]
--------------------PhysicalDistribute[DistributionSpecHash]
@@ -36,11 +36,11 @@ PhysicalResultSink
--------------------PhysicalDistribute[DistributionSpecHash]
----------------------hashAgg[LOCAL]
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((catalog_returns.cr_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF7 i_item_sk->[cr_item_sk]
+--------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_returns.wr_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF7 i_item_sk->[wr_item_sk]
----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_returns.cr_returned_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF6 d_date_sk->[cr_returned_date_sk]
+------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_returns.wr_returned_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF6 d_date_sk->[wr_returned_date_sk]
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[catalog_returns] apply RFs:
RF6 RF7
+----------------------------------PhysicalOlapScan[web_returns] apply RFs: RF6
RF7
--------------------------------PhysicalProject
----------------------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=() build
RFs:RF5 d_date->[d_date]
------------------------------------PhysicalProject
@@ -59,11 +59,11 @@ PhysicalResultSink
----------------PhysicalDistribute[DistributionSpecHash]
------------------hashAgg[LOCAL]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_returns.wr_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF3 i_item_sk->[wr_item_sk]
+----------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((catalog_returns.cr_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF3 i_item_sk->[cr_item_sk]
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_returns.wr_returned_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[wr_returned_date_sk]
+--------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_returns.cr_returned_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[cr_returned_date_sk]
----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[web_returns] apply RFs: RF2 RF3
+------------------------------PhysicalOlapScan[catalog_returns] apply RFs: RF2
RF3
----------------------------PhysicalProject
------------------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=() build
RFs:RF1 d_date->[d_date]
--------------------------------PhysicalProject
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query14.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query14.out
index 52fd962d6ab..16f3bec6f15 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query14.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query14.out
@@ -136,13 +136,13 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------------hashAgg[LOCAL]
------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF17 d_date_sk->[ws_sold_date_sk]
+--------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF18 d_date_sk->[ws_sold_date_sk]
----------------------------------------PhysicalProject
------------------------------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=()
---------------------------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = cross_items.ss_item_sk))
otherCondition=()
-----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[web_sales]
apply RFs: RF17
+--------------------------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = cross_items.ss_item_sk))
otherCondition=()
----------------------------------------------PhysicalCteConsumer (
cteId=CTEId#0 )
+----------------------------------------------PhysicalProject
+------------------------------------------------PhysicalOlapScan[web_sales]
apply RFs: RF18
--------------------------------------------PhysicalProject
----------------------------------------------PhysicalOlapScan[item]
----------------------------------------PhysicalProject
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query58.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query58.out
index 29925634b2a..a154c3b953b 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query58.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query58.out
@@ -21,9 +21,7 @@ PhysicalResultSink
--------------------------------PhysicalProject
----------------------------------PhysicalOlapScan[item] apply RFs: RF12 RF13
----------------------------PhysicalProject
-------------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=() build
RFs:RF9 d_date->[d_date]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[date_dim] apply RFs: RF9
+------------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=()
--------------------------------PhysicalProject
----------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((date_dim.d_week_seq = date_dim.d_week_seq)) otherCondition=()
build RFs:RF8 d_week_seq->[d_week_seq]
------------------------------------PhysicalProject
@@ -33,6 +31,8 @@ PhysicalResultSink
----------------------------------------PhysicalProject
------------------------------------------filter((date_dim.d_date =
'2001-03-24'))
--------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[date_dim]
----------------PhysicalProject
------------------hashAgg[GLOBAL]
--------------------PhysicalDistribute[DistributionSpecHash]
@@ -46,9 +46,7 @@ PhysicalResultSink
--------------------------------PhysicalProject
----------------------------------PhysicalOlapScan[item] apply RFs: RF13
----------------------------PhysicalProject
-------------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=() build
RFs:RF5 d_date->[d_date]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[date_dim] apply RFs: RF5
+------------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=()
--------------------------------PhysicalProject
----------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((date_dim.d_week_seq = date_dim.d_week_seq)) otherCondition=()
build RFs:RF4 d_week_seq->[d_week_seq]
------------------------------------PhysicalProject
@@ -58,6 +56,8 @@ PhysicalResultSink
----------------------------------------PhysicalProject
------------------------------------------filter((date_dim.d_date =
'2001-03-24'))
--------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[date_dim]
------------PhysicalProject
--------------hashAgg[GLOBAL]
----------------PhysicalDistribute[DistributionSpecHash]
@@ -71,9 +71,7 @@ PhysicalResultSink
----------------------------PhysicalProject
------------------------------PhysicalOlapScan[item]
------------------------PhysicalProject
---------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=() build
RFs:RF1 d_date->[d_date]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[date_dim] apply RFs: RF1
+--------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=()
----------------------------PhysicalProject
------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((date_dim.d_week_seq = date_dim.d_week_seq)) otherCondition=()
build RFs:RF0 d_week_seq->[d_week_seq]
--------------------------------PhysicalProject
@@ -83,4 +81,6 @@ PhysicalResultSink
------------------------------------PhysicalProject
--------------------------------------filter((date_dim.d_date = '2001-03-24'))
----------------------------------------PhysicalOlapScan[date_dim]
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[date_dim]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query95.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query95.out
index 313d625fa34..fce09b1b604 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query95.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/noStatsRfPrune/query95.out
@@ -5,9 +5,9 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----PhysicalProject
------hashJoin[INNER_JOIN shuffle] hashCondition=((ws1.ws_order_number =
ws2.ws_order_number)) otherCondition=(( not (ws_warehouse_sk =
ws_warehouse_sk))) build RFs:RF0 ws_order_number->[ws_order_number]
--------PhysicalProject
-----------PhysicalOlapScan[web_sales] apply RFs: RF0 RF6
+----------PhysicalOlapScan[web_sales] apply RFs: RF0 RF7
--------PhysicalProject
-----------PhysicalOlapScan[web_sales] apply RFs: RF6
+----------PhysicalOlapScan[web_sales] apply RFs: RF7
--PhysicalResultSink
----PhysicalTopN[GATHER_SORT]
------hashAgg[DISTINCT_GLOBAL]
@@ -16,21 +16,21 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------hashAgg[GLOBAL]
--------------hashAgg[LOCAL]
----------------PhysicalProject
-------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_web_site_sk = web_site.web_site_sk)) otherCondition=()
build RFs:RF5 web_site_sk->[ws_web_site_sk]
+------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_web_site_sk = web_site.web_site_sk)) otherCondition=()
build RFs:RF6 web_site_sk->[ws_web_site_sk]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_ship_addr_sk = customer_address.ca_address_sk))
otherCondition=() build RFs:RF4 ca_address_sk->[ws_ship_addr_sk]
+----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_ship_addr_sk = customer_address.ca_address_sk))
otherCondition=() build RFs:RF5 ca_address_sk->[ws_ship_addr_sk]
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_ship_date_sk = date_dim.d_date_sk)) otherCondition=()
build RFs:RF3 d_date_sk->[ws_ship_date_sk]
-----------------------------hashJoin[LEFT_SEMI_JOIN colocated]
hashCondition=((ws1.ws_order_number = web_returns.wr_order_number))
otherCondition=()
-------------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((ws1.ws_order_number = ws_wh.ws_order_number)) otherCondition=()
+--------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_ship_date_sk = date_dim.d_date_sk)) otherCondition=()
build RFs:RF4 d_date_sk->[ws_ship_date_sk]
+----------------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((ws1.ws_order_number = ws_wh.ws_order_number)) otherCondition=()
build RFs:RF3 ws_order_number->[ws_order_number]
+------------------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs:
RF3
+------------------------------hashJoin[RIGHT_SEMI_JOIN bucketShuffle]
hashCondition=((ws1.ws_order_number = web_returns.wr_order_number))
otherCondition=() build RFs:RF2 ws_order_number->[wr_order_number];RF7
ws_order_number->[ws_order_number,ws_order_number]
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF3
RF4 RF5
---------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
-------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_returns.wr_order_number = ws_wh.ws_order_number))
otherCondition=() build RFs:RF6 wr_order_number->[ws_order_number]
-----------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[web_returns]
+----------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_returns.wr_order_number = ws_wh.ws_order_number))
otherCondition=()
+------------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[web_returns] apply RFs:
RF2
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF4
RF5 RF6
----------------------------PhysicalProject
------------------------------filter((date_dim.d_date <= '1999-04-02') and
(date_dim.d_date >= '1999-02-01'))
--------------------------------PhysicalOlapScan[date_dim]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query14.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query14.out
index 1e37ac8f702..a7e5eb01cf5 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query14.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query14.out
@@ -136,13 +136,13 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------------hashAgg[LOCAL]
------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF17 d_date_sk->[ws_sold_date_sk]
+--------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF18 d_date_sk->[ws_sold_date_sk]
----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=() build
RFs:RF16 i_item_sk->[ss_item_sk,ws_item_sk]
---------------------------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = cross_items.ss_item_sk))
otherCondition=()
+------------------------------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=() build
RFs:RF17 i_item_sk->[ss_item_sk,ws_item_sk]
+--------------------------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = cross_items.ss_item_sk))
otherCondition=() build RFs:RF16 ws_item_sk->[ss_item_sk]
+----------------------------------------------PhysicalCteConsumer (
cteId=CTEId#0 ) apply RFs: RF16 RF17
----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[web_sales]
apply RFs: RF16 RF17
-----------------------------------------------PhysicalCteConsumer (
cteId=CTEId#0 ) apply RFs: RF16
+------------------------------------------------PhysicalOlapScan[web_sales]
apply RFs: RF17 RF18
--------------------------------------------PhysicalProject
----------------------------------------------PhysicalOlapScan[item]
----------------------------------------PhysicalProject
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query58.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query58.out
index fcb11cadc05..cd737bad3f6 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query58.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query58.out
@@ -21,18 +21,18 @@ PhysicalResultSink
--------------------------------PhysicalProject
----------------------------------PhysicalOlapScan[item] apply RFs: RF12 RF13
----------------------------PhysicalProject
-------------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=() build
RFs:RF9 d_date->[d_date]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[date_dim] apply RFs: RF9
+------------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=() build
RFs:RF9 d_date->[d_date]
--------------------------------PhysicalProject
----------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((date_dim.d_week_seq = date_dim.d_week_seq)) otherCondition=()
build RFs:RF8 d_week_seq->[d_week_seq]
------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[date_dim] apply RFs: RF8
+--------------------------------------PhysicalOlapScan[date_dim] apply RFs:
RF8 RF9
------------------------------------PhysicalAssertNumRows
--------------------------------------PhysicalDistribute[DistributionSpecGather]
----------------------------------------PhysicalProject
------------------------------------------filter((date_dim.d_date =
'2001-03-24'))
--------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[date_dim]
----------------PhysicalProject
------------------hashAgg[GLOBAL]
--------------------PhysicalDistribute[DistributionSpecHash]
@@ -46,18 +46,18 @@ PhysicalResultSink
--------------------------------PhysicalProject
----------------------------------PhysicalOlapScan[item] apply RFs: RF13
----------------------------PhysicalProject
-------------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=() build
RFs:RF5 d_date->[d_date]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[date_dim] apply RFs: RF5
+------------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=() build
RFs:RF5 d_date->[d_date]
--------------------------------PhysicalProject
----------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((date_dim.d_week_seq = date_dim.d_week_seq)) otherCondition=()
build RFs:RF4 d_week_seq->[d_week_seq]
------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[date_dim] apply RFs: RF4
+--------------------------------------PhysicalOlapScan[date_dim] apply RFs:
RF4 RF5
------------------------------------PhysicalAssertNumRows
--------------------------------------PhysicalDistribute[DistributionSpecGather]
----------------------------------------PhysicalProject
------------------------------------------filter((date_dim.d_date =
'2001-03-24'))
--------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[date_dim]
------------PhysicalProject
--------------hashAgg[GLOBAL]
----------------PhysicalDistribute[DistributionSpecHash]
@@ -71,16 +71,16 @@ PhysicalResultSink
----------------------------PhysicalProject
------------------------------PhysicalOlapScan[item]
------------------------PhysicalProject
---------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=() build
RFs:RF1 d_date->[d_date]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[date_dim] apply RFs: RF1
+--------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((date_dim.d_date = date_dim.d_date)) otherCondition=() build
RFs:RF1 d_date->[d_date]
----------------------------PhysicalProject
------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((date_dim.d_week_seq = date_dim.d_week_seq)) otherCondition=()
build RFs:RF0 d_week_seq->[d_week_seq]
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[date_dim] apply RFs: RF0
+----------------------------------PhysicalOlapScan[date_dim] apply RFs: RF0 RF1
--------------------------------PhysicalAssertNumRows
----------------------------------PhysicalDistribute[DistributionSpecGather]
------------------------------------PhysicalProject
--------------------------------------filter((date_dim.d_date = '2001-03-24'))
----------------------------------------PhysicalOlapScan[date_dim]
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[date_dim]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query95.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query95.out
index 8486403dba9..aaaba243279 100644
---
a/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query95.out
+++
b/regression-test/data/nereids_tpcds_shape_sf100_p0/no_stats_shape/query95.out
@@ -5,9 +5,9 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----PhysicalProject
------hashJoin[INNER_JOIN shuffle] hashCondition=((ws1.ws_order_number =
ws2.ws_order_number)) otherCondition=(( not (ws_warehouse_sk =
ws_warehouse_sk))) build RFs:RF0 ws_order_number->[ws_order_number];RF1
ws_order_number->[ws_order_number]
--------PhysicalProject
-----------PhysicalOlapScan[web_sales] apply RFs: RF0 RF1 RF12 RF13
+----------PhysicalOlapScan[web_sales] apply RFs: RF0 RF1 RF14 RF15
--------PhysicalProject
-----------PhysicalOlapScan[web_sales] apply RFs: RF12 RF13
+----------PhysicalOlapScan[web_sales] apply RFs: RF14 RF15
--PhysicalResultSink
----PhysicalTopN[GATHER_SORT]
------hashAgg[DISTINCT_GLOBAL]
@@ -16,21 +16,21 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------hashAgg[GLOBAL]
--------------hashAgg[LOCAL]
----------------PhysicalProject
-------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_web_site_sk = web_site.web_site_sk)) otherCondition=()
build RFs:RF10 web_site_sk->[ws_web_site_sk];RF11 web_site_sk->[ws_web_site_sk]
+------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_web_site_sk = web_site.web_site_sk)) otherCondition=()
build RFs:RF12 web_site_sk->[ws_web_site_sk];RF13 web_site_sk->[ws_web_site_sk]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_ship_addr_sk = customer_address.ca_address_sk))
otherCondition=() build RFs:RF8 ca_address_sk->[ws_ship_addr_sk];RF9
ca_address_sk->[ws_ship_addr_sk]
+----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_ship_addr_sk = customer_address.ca_address_sk))
otherCondition=() build RFs:RF10 ca_address_sk->[ws_ship_addr_sk];RF11
ca_address_sk->[ws_ship_addr_sk]
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_ship_date_sk = date_dim.d_date_sk)) otherCondition=()
build RFs:RF6 d_date_sk->[ws_ship_date_sk];RF7 d_date_sk->[ws_ship_date_sk]
-----------------------------hashJoin[LEFT_SEMI_JOIN colocated]
hashCondition=((ws1.ws_order_number = web_returns.wr_order_number))
otherCondition=() build RFs:RF4
wr_order_number->[ws_order_number,ws_order_number];RF5
wr_order_number->[ws_order_number,ws_order_number]
-------------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((ws1.ws_order_number = ws_wh.ws_order_number)) otherCondition=()
+--------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_ship_date_sk = date_dim.d_date_sk)) otherCondition=()
build RFs:RF8 d_date_sk->[ws_ship_date_sk];RF9 d_date_sk->[ws_ship_date_sk]
+----------------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((ws1.ws_order_number = ws_wh.ws_order_number)) otherCondition=()
build RFs:RF6 ws_order_number->[ws_order_number];RF7
ws_order_number->[ws_order_number]
+------------------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs:
RF6 RF7
+------------------------------hashJoin[RIGHT_SEMI_JOIN bucketShuffle]
hashCondition=((ws1.ws_order_number = web_returns.wr_order_number))
otherCondition=() build RFs:RF4 ws_order_number->[wr_order_number];RF5
ws_order_number->[wr_order_number];RF14
ws_order_number->[ws_order_number,ws_order_number];RF15
ws_order_number->[ws_order_number,ws_order_number]
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF4
RF5 RF6 RF7 RF8 RF9 RF10 RF11
---------------------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply
RFs: RF4 RF5
-------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_returns.wr_order_number = ws_wh.ws_order_number))
otherCondition=() build RFs:RF12
wr_order_number->[ws_order_number,ws_order_number];RF13
wr_order_number->[ws_order_number,ws_order_number]
-----------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[web_returns]
+----------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_returns.wr_order_number = ws_wh.ws_order_number))
otherCondition=() build RFs:RF2 wr_order_number->[ws_order_number];RF3
wr_order_number->[ws_order_number]
+------------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
apply RFs: RF2 RF3
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[web_returns] apply RFs:
RF4 RF5
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF8
RF9 RF10 RF11 RF12 RF13
----------------------------PhysicalProject
------------------------------filter((date_dim.d_date <= '1999-04-02') and
(date_dim.d_date >= '1999-02-01'))
--------------------------------PhysicalOlapScan[date_dim]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query14.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query14.out
index be165273766..d3827831c39 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query14.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query14.out
@@ -119,19 +119,18 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------------hashAgg[LOCAL]
------------------------------------PhysicalProject
---------------------------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((catalog_sales.cs_item_sk = cross_items.ss_item_sk))
otherCondition=() build RFs:RF16 cs_item_sk->[ss_item_sk]
-----------------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
apply RFs: RF16
+--------------------------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((catalog_sales.cs_item_sk = item.i_item_sk)) otherCondition=()
+----------------------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = cross_items.ss_item_sk))
otherCondition=()
+------------------------------------------PhysicalProject
+--------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF14 d_date_sk->[cs_sold_date_sk]
+----------------------------------------------PhysicalProject
+------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF14
+----------------------------------------------PhysicalProject
+------------------------------------------------filter((date_dim.d_moy = 11)
and (date_dim.d_year = 2002))
+--------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = item.i_item_sk)) otherCondition=()
---------------------------------------------PhysicalProject
-----------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF14 d_date_sk->[cs_sold_date_sk]
-------------------------------------------------PhysicalProject
---------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF14
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((date_dim.d_moy = 11)
and (date_dim.d_year = 2002))
-----------------------------------------------------PhysicalOlapScan[date_dim]
---------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[item]
+------------------------------------------PhysicalOlapScan[item]
----------------------------PhysicalProject
------------------------------PhysicalAssertNumRows
--------------------------------PhysicalDistribute[DistributionSpecGather]
@@ -143,19 +142,18 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------------hashAgg[LOCAL]
------------------------------------PhysicalProject
---------------------------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((web_sales.ws_item_sk = cross_items.ss_item_sk))
otherCondition=() build RFs:RF19 ws_item_sk->[ss_item_sk]
-----------------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
apply RFs: RF19
+--------------------------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=()
+----------------------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = cross_items.ss_item_sk))
otherCondition=()
+------------------------------------------PhysicalProject
+--------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF16 d_date_sk->[ws_sold_date_sk]
+----------------------------------------------PhysicalProject
+------------------------------------------------PhysicalOlapScan[web_sales]
apply RFs: RF16
+----------------------------------------------PhysicalProject
+------------------------------------------------filter((date_dim.d_moy = 11)
and (date_dim.d_year = 2002))
+--------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=()
---------------------------------------------PhysicalProject
-----------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF17 d_date_sk->[ws_sold_date_sk]
-------------------------------------------------PhysicalProject
---------------------------------------------------PhysicalOlapScan[web_sales]
apply RFs: RF17
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((date_dim.d_moy = 11)
and (date_dim.d_year = 2002))
-----------------------------------------------------PhysicalOlapScan[date_dim]
---------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[item]
+------------------------------------------PhysicalOlapScan[item]
----------------------------PhysicalProject
------------------------------PhysicalAssertNumRows
--------------------------------PhysicalDistribute[DistributionSpecGather]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query16.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query16.out
index ba90af331bb..1733e793f1f 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query16.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query16.out
@@ -8,7 +8,7 @@ PhysicalResultSink
----------hashAgg[GLOBAL]
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((cs1.cs_order_number = cs2.cs_order_number)) otherCondition=((
not (cs_warehouse_sk = cs_warehouse_sk))) build RFs:RF3
cs_order_number->[cs_order_number]
+----------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((cs1.cs_order_number = cs2.cs_order_number)) otherCondition=((
not (cs_warehouse_sk = cs_warehouse_sk))) build RFs:RF3
cs_order_number->[cs_order_number]
------------------PhysicalProject
--------------------PhysicalOlapScan[catalog_sales] apply RFs: RF3
------------------PhysicalProject
@@ -17,7 +17,7 @@ PhysicalResultSink
------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((cs1.cs_ship_date_sk = date_dim.d_date_sk)) otherCondition=()
build RFs:RF1 d_date_sk->[cs_ship_date_sk]
--------------------------PhysicalProject
----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((cs1.cs_ship_addr_sk = customer_address.ca_address_sk))
otherCondition=() build RFs:RF0 ca_address_sk->[cs_ship_addr_sk]
-------------------------------hashJoin[LEFT_ANTI_JOIN shuffle]
hashCondition=((cs1.cs_order_number = cr1.cr_order_number)) otherCondition=()
+------------------------------hashJoin[LEFT_ANTI_JOIN broadcast]
hashCondition=((cs1.cs_order_number = cr1.cr_order_number)) otherCondition=()
--------------------------------PhysicalProject
----------------------------------PhysicalOlapScan[catalog_sales] apply RFs:
RF0 RF1 RF2
--------------------------------PhysicalProject
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query23.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query23.out
index 1bf8480c752..9894c67e590 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query23.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query23.out
@@ -52,8 +52,7 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------hashAgg[LOCAL]
--------------PhysicalUnion
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = frequent_ss_items.item_sk))
otherCondition=() build RFs:RF4 cs_item_sk->[item_sk]
---------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF4
+------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = frequent_ss_items.item_sk))
otherCondition=()
--------------------PhysicalProject
----------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk =
best_ss_customer.c_customer_sk)) otherCondition=()
------------------------PhysicalProject
@@ -64,17 +63,18 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------------------filter((date_dim.d_moy = 5) and (date_dim.d_year
= 2000))
--------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalCteConsumer ( cteId=CTEId#2 )
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = frequent_ss_items.item_sk))
otherCondition=() build RFs:RF6 ws_item_sk->[item_sk]
---------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF6
+------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = frequent_ss_items.item_sk))
otherCondition=()
--------------------PhysicalProject
----------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_bill_customer_sk =
best_ss_customer.c_customer_sk)) otherCondition=()
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[ws_sold_date_sk]
+--------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[ws_sold_date_sk]
----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[web_sales] apply RFs: RF5
+------------------------------PhysicalOlapScan[web_sales] apply RFs: RF4
----------------------------PhysicalProject
------------------------------filter((date_dim.d_moy = 5) and (date_dim.d_year
= 2000))
--------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalCteConsumer ( cteId=CTEId#2 )
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query33.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query33.out
index 73df959527c..0687ef8857a 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query33.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query33.out
@@ -9,21 +9,18 @@ PhysicalResultSink
------------hashAgg[LOCAL]
--------------PhysicalUnion
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF3 i_manufact_id->[i_manufact_id]
---------------------PhysicalProject
-----------------------filter((item.i_category = 'Home'))
-------------------------PhysicalOlapScan[item] apply RFs: RF3
+------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF3 i_manufact_id->[i_manufact_id]
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
+----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF2 i_item_sk->[ss_item_sk]
------------------------------PhysicalProject
--------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((store_sales.ss_addr_sk = customer_address.ca_address_sk))
otherCondition=() build RFs:RF1 ca_address_sk->[ss_addr_sk]
----------------------------------PhysicalProject
------------------------------------hashJoin[INNER_JOIN broadcast]
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 RF1
+----------------------------------------PhysicalOlapScan[store_sales] apply
RFs: RF0 RF1 RF2
--------------------------------------PhysicalProject
----------------------------------------filter((date_dim.d_moy = 1) and
(date_dim.d_year = 2002))
------------------------------------------PhysicalOlapScan[date_dim]
@@ -31,23 +28,23 @@ PhysicalResultSink
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item]
-----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF7 i_manufact_id->[i_manufact_id]
+--------------------------------PhysicalOlapScan[item] apply RFs: RF3
--------------------PhysicalProject
----------------------filter((item.i_category = 'Home'))
-------------------------PhysicalOlapScan[item] apply RFs: RF7
+------------------------PhysicalOlapScan[item]
+----------------PhysicalProject
+------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF7 i_manufact_id->[i_manufact_id]
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = item.i_item_sk)) otherCondition=()
+----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF6 i_item_sk->[cs_item_sk]
------------------------------PhysicalProject
--------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((catalog_sales.cs_bill_addr_sk =
customer_address.ca_address_sk)) otherCondition=() build RFs:RF5
ca_address_sk->[cs_bill_addr_sk]
----------------------------------PhysicalProject
------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[cs_sold_date_sk]
--------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[catalog_sales] apply
RFs: RF4 RF5
+----------------------------------------PhysicalOlapScan[catalog_sales] apply
RFs: RF4 RF5 RF6
--------------------------------------PhysicalProject
----------------------------------------filter((date_dim.d_moy = 1) and
(date_dim.d_year = 2002))
------------------------------------------PhysicalOlapScan[date_dim]
@@ -55,19 +52,19 @@ PhysicalResultSink
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item]
-----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF11 i_manufact_id->[i_manufact_id]
+--------------------------------PhysicalOlapScan[item] apply RFs: RF7
--------------------PhysicalProject
----------------------filter((item.i_category = 'Home'))
-------------------------PhysicalOlapScan[item] apply RFs: RF11
+------------------------PhysicalOlapScan[item]
+----------------PhysicalProject
+------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF11 i_manufact_id->[i_manufact_id]
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
--------------------------PhysicalProject
----------------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=() build
RFs:RF10 ws_item_sk->[i_item_sk]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item] apply RFs: RF10
+--------------------------------PhysicalOlapScan[item] apply RFs: RF10 RF11
------------------------------PhysicalProject
--------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_sales.ws_bill_addr_sk = customer_address.ca_address_sk))
otherCondition=() build RFs:RF9 ca_address_sk->[ws_bill_addr_sk]
----------------------------------PhysicalProject
@@ -80,4 +77,7 @@ PhysicalResultSink
----------------------------------PhysicalProject
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
+--------------------PhysicalProject
+----------------------filter((item.i_category = 'Home'))
+------------------------PhysicalOlapScan[item]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query35.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query35.out
index df1167d7bf3..49d318314de 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query35.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query35.out
@@ -10,38 +10,38 @@ PhysicalResultSink
--------------hashAgg[LOCAL]
----------------PhysicalProject
------------------filter((ifnull($c$1, FALSE) OR ifnull($c$2, FALSE)))
---------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=()
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[cs_sold_date_sk]
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF5
---------------------------PhysicalProject
-----------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year =
2001))
-------------------------------PhysicalOlapScan[date_dim]
-----------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
-------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[ws_sold_date_sk]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[web_sales] apply RFs: RF4
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year
= 2001))
---------------------------------PhysicalOlapScan[date_dim]
-------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk)) otherCondition=()
---------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk]
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[store_sales] apply RFs: RF2
-------------------------------PhysicalProject
---------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 2001))
-----------------------------------PhysicalOlapScan[date_dim]
+--------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=()
+----------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
+------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF5 ss_customer_sk->[c_customer_sk]
--------------------------PhysicalProject
----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=()
------------------------------PhysicalProject
--------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[customer]
+------------------------------------PhysicalOlapScan[customer] apply RFs: RF5
----------------------------------PhysicalProject
------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
--------------------------------PhysicalOlapScan[customer_demographics]
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[store_sales] apply RFs: RF2
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 2001))
+----------------------------------PhysicalOlapScan[date_dim]
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN broadcast]
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
+----------------------------PhysicalProject
+------------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year
= 2001))
+--------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF0 d_date_sk->[cs_sold_date_sk]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year =
2001))
+------------------------------PhysicalOlapScan[date_dim]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query69.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query69.out
index 090d1940739..7293ef67e29 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query69.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query69.out
@@ -9,39 +9,39 @@ PhysicalResultSink
------------PhysicalDistribute[DistributionSpecHash]
--------------hashAgg[LOCAL]
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF7 c_customer_sk->[ss_customer_sk]
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF6 c_customer_sk->[ss_customer_sk]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF6 d_date_sk->[ss_sold_date_sk]
+----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[ss_sold_date_sk]
------------------------PhysicalProject
---------------------------PhysicalOlapScan[store_sales] apply RFs: RF6 RF7
+--------------------------PhysicalOlapScan[store_sales] apply RFs: RF5 RF6
------------------------PhysicalProject
--------------------------filter((date_dim.d_moy <= 3) and (date_dim.d_moy >=
1) and (date_dim.d_year = 2000))
----------------------------PhysicalOlapScan[date_dim]
---------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[cs_ship_customer_sk]
+--------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=() build RFs:RF4 c_customer_sk->[cs_ship_customer_sk]
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[cs_sold_date_sk]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF3 d_date_sk->[cs_sold_date_sk]
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF4 RF5
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF3 RF4
--------------------------PhysicalProject
----------------------------filter((date_dim.d_moy <= 3) and (date_dim.d_moy
>= 1) and (date_dim.d_year = 2000))
------------------------------PhysicalOlapScan[date_dim]
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF3 c_current_cdemo_sk->[cd_demo_sk]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF2 c_current_cdemo_sk->[cd_demo_sk]
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[customer_demographics] apply RFs:
RF3
---------------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=() build RFs:RF2 c_customer_sk->[ws_bill_customer_sk]
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
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
RF2
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_moy <= 3) and
(date_dim.d_moy >= 1) and (date_dim.d_year = 2000))
-------------------------------------PhysicalOlapScan[date_dim]
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF0 ca_address_sk->[c_current_addr_sk]
+----------------------------PhysicalOlapScan[customer_demographics] apply RFs:
RF2
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF1 ca_address_sk->[c_current_addr_sk]
+------------------------------hashJoin[LEFT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[customer] apply RFs: RF0
+----------------------------------PhysicalOlapScan[customer] apply RFs: RF1
--------------------------------PhysicalProject
-----------------------------------filter(ca_state IN ('MI', 'TX', 'VA'))
-------------------------------------PhysicalOlapScan[customer_address]
+----------------------------------hashJoin[INNER_JOIN broadcast]
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
+------------------------------------PhysicalProject
+--------------------------------------filter((date_dim.d_moy <= 3) and
(date_dim.d_moy >= 1) and (date_dim.d_year = 2000))
+----------------------------------------PhysicalOlapScan[date_dim]
+------------------------------PhysicalProject
+--------------------------------filter(ca_state IN ('MI', 'TX', 'VA'))
+----------------------------------PhysicalOlapScan[customer_address]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query94.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query94.out
index e13bdc06291..6a25137bf51 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query94.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/rf_prune/query94.out
@@ -8,7 +8,7 @@ PhysicalResultSink
----------hashAgg[GLOBAL]
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((ws1.ws_order_number = ws2.ws_order_number)) otherCondition=((
not (ws_warehouse_sk = ws_warehouse_sk))) build RFs:RF3
ws_order_number->[ws_order_number]
+----------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((ws1.ws_order_number = ws2.ws_order_number)) otherCondition=((
not (ws_warehouse_sk = ws_warehouse_sk))) build RFs:RF3
ws_order_number->[ws_order_number]
------------------PhysicalProject
--------------------PhysicalOlapScan[web_sales] apply RFs: RF3
------------------PhysicalProject
@@ -17,7 +17,7 @@ PhysicalResultSink
------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_ship_date_sk = date_dim.d_date_sk)) otherCondition=()
build RFs:RF1 d_date_sk->[ws_ship_date_sk]
--------------------------PhysicalProject
----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_ship_addr_sk = customer_address.ca_address_sk))
otherCondition=() build RFs:RF0 ca_address_sk->[ws_ship_addr_sk]
-------------------------------hashJoin[LEFT_ANTI_JOIN shuffle]
hashCondition=((ws1.ws_order_number = wr1.wr_order_number)) otherCondition=()
+------------------------------hashJoin[LEFT_ANTI_JOIN broadcast]
hashCondition=((ws1.ws_order_number = wr1.wr_order_number)) otherCondition=()
--------------------------------PhysicalProject
----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0
RF1 RF2
--------------------------------PhysicalProject
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query14.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query14.out
index 4ad3fc850f6..0c5d7c928bd 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query14.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query14.out
@@ -119,19 +119,18 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------------hashAgg[LOCAL]
------------------------------------PhysicalProject
---------------------------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((catalog_sales.cs_item_sk = cross_items.ss_item_sk))
otherCondition=() build RFs:RF16 cs_item_sk->[ss_item_sk]
-----------------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
apply RFs: RF16
+--------------------------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((catalog_sales.cs_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF15 i_item_sk->[cs_item_sk,ss_item_sk]
+----------------------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = cross_items.ss_item_sk))
otherCondition=()
+------------------------------------------PhysicalProject
+--------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF14 d_date_sk->[cs_sold_date_sk]
+----------------------------------------------PhysicalProject
+------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF14 RF15
+----------------------------------------------PhysicalProject
+------------------------------------------------filter((date_dim.d_moy = 11)
and (date_dim.d_year = 2002))
+--------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------PhysicalCteConsumer ( cteId=CTEId#0
) apply RFs: RF15
----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF15 i_item_sk->[cs_item_sk]
---------------------------------------------PhysicalProject
-----------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF14 d_date_sk->[cs_sold_date_sk]
-------------------------------------------------PhysicalProject
---------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF14 RF15
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((date_dim.d_moy = 11)
and (date_dim.d_year = 2002))
-----------------------------------------------------PhysicalOlapScan[date_dim]
---------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[item]
+------------------------------------------PhysicalOlapScan[item]
----------------------------PhysicalProject
------------------------------PhysicalAssertNumRows
--------------------------------PhysicalDistribute[DistributionSpecGather]
@@ -143,19 +142,18 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------------hashAgg[LOCAL]
------------------------------------PhysicalProject
---------------------------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((web_sales.ws_item_sk = cross_items.ss_item_sk))
otherCondition=() build RFs:RF19 ws_item_sk->[ss_item_sk]
-----------------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
apply RFs: RF19
+--------------------------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=() build
RFs:RF17 i_item_sk->[ss_item_sk,ws_item_sk]
+----------------------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = cross_items.ss_item_sk))
otherCondition=()
+------------------------------------------PhysicalProject
+--------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF16 d_date_sk->[ws_sold_date_sk]
+----------------------------------------------PhysicalProject
+------------------------------------------------PhysicalOlapScan[web_sales]
apply RFs: RF16 RF17
+----------------------------------------------PhysicalProject
+------------------------------------------------filter((date_dim.d_moy = 11)
and (date_dim.d_year = 2002))
+--------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------PhysicalCteConsumer ( cteId=CTEId#0
) apply RFs: RF17
----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=() build
RFs:RF18 i_item_sk->[ws_item_sk]
---------------------------------------------PhysicalProject
-----------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF17 d_date_sk->[ws_sold_date_sk]
-------------------------------------------------PhysicalProject
---------------------------------------------------PhysicalOlapScan[web_sales]
apply RFs: RF17 RF18
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((date_dim.d_moy = 11)
and (date_dim.d_year = 2002))
-----------------------------------------------------PhysicalOlapScan[date_dim]
---------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[item]
+------------------------------------------PhysicalOlapScan[item]
----------------------------PhysicalProject
------------------------------PhysicalAssertNumRows
--------------------------------PhysicalDistribute[DistributionSpecGather]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query16.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query16.out
index ba90af331bb..1733e793f1f 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query16.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query16.out
@@ -8,7 +8,7 @@ PhysicalResultSink
----------hashAgg[GLOBAL]
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((cs1.cs_order_number = cs2.cs_order_number)) otherCondition=((
not (cs_warehouse_sk = cs_warehouse_sk))) build RFs:RF3
cs_order_number->[cs_order_number]
+----------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((cs1.cs_order_number = cs2.cs_order_number)) otherCondition=((
not (cs_warehouse_sk = cs_warehouse_sk))) build RFs:RF3
cs_order_number->[cs_order_number]
------------------PhysicalProject
--------------------PhysicalOlapScan[catalog_sales] apply RFs: RF3
------------------PhysicalProject
@@ -17,7 +17,7 @@ PhysicalResultSink
------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((cs1.cs_ship_date_sk = date_dim.d_date_sk)) otherCondition=()
build RFs:RF1 d_date_sk->[cs_ship_date_sk]
--------------------------PhysicalProject
----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((cs1.cs_ship_addr_sk = customer_address.ca_address_sk))
otherCondition=() build RFs:RF0 ca_address_sk->[cs_ship_addr_sk]
-------------------------------hashJoin[LEFT_ANTI_JOIN shuffle]
hashCondition=((cs1.cs_order_number = cr1.cr_order_number)) otherCondition=()
+------------------------------hashJoin[LEFT_ANTI_JOIN broadcast]
hashCondition=((cs1.cs_order_number = cr1.cr_order_number)) otherCondition=()
--------------------------------PhysicalProject
----------------------------------PhysicalOlapScan[catalog_sales] apply RFs:
RF0 RF1 RF2
--------------------------------PhysicalProject
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query23.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query23.out
index 28e49b0a70c..893f7e0e2cc 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query23.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query23.out
@@ -52,8 +52,7 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------hashAgg[LOCAL]
--------------PhysicalUnion
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = frequent_ss_items.item_sk))
otherCondition=() build RFs:RF4 cs_item_sk->[item_sk]
---------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF4
+------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = frequent_ss_items.item_sk))
otherCondition=()
--------------------PhysicalProject
----------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk =
best_ss_customer.c_customer_sk)) otherCondition=()
------------------------PhysicalProject
@@ -64,17 +63,18 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------------------filter((date_dim.d_moy = 5) and (date_dim.d_year
= 2000))
--------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalCteConsumer ( cteId=CTEId#2 )
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = frequent_ss_items.item_sk))
otherCondition=() build RFs:RF6 ws_item_sk->[item_sk]
---------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF6
+------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = frequent_ss_items.item_sk))
otherCondition=()
--------------------PhysicalProject
----------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_bill_customer_sk =
best_ss_customer.c_customer_sk)) otherCondition=()
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[ws_sold_date_sk]
+--------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[ws_sold_date_sk]
----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[web_sales] apply RFs: RF5
+------------------------------PhysicalOlapScan[web_sales] apply RFs: RF4
----------------------------PhysicalProject
------------------------------filter((date_dim.d_moy = 5) and (date_dim.d_year
= 2000))
--------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalCteConsumer ( cteId=CTEId#2 )
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query33.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query33.out
index 655e0c12832..0687ef8857a 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query33.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query33.out
@@ -9,10 +9,7 @@ PhysicalResultSink
------------hashAgg[LOCAL]
--------------PhysicalUnion
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF3 i_manufact_id->[i_manufact_id]
---------------------PhysicalProject
-----------------------filter((item.i_category = 'Home'))
-------------------------PhysicalOlapScan[item] apply RFs: RF3
+------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF3 i_manufact_id->[i_manufact_id]
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
@@ -31,12 +28,12 @@ PhysicalResultSink
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item]
-----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF7 i_manufact_id->[i_manufact_id]
+--------------------------------PhysicalOlapScan[item] apply RFs: RF3
--------------------PhysicalProject
----------------------filter((item.i_category = 'Home'))
-------------------------PhysicalOlapScan[item] apply RFs: RF7
+------------------------PhysicalOlapScan[item]
+----------------PhysicalProject
+------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF7 i_manufact_id->[i_manufact_id]
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
@@ -55,19 +52,19 @@ PhysicalResultSink
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item]
-----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF11 i_manufact_id->[i_manufact_id]
+--------------------------------PhysicalOlapScan[item] apply RFs: RF7
--------------------PhysicalProject
----------------------filter((item.i_category = 'Home'))
-------------------------PhysicalOlapScan[item] apply RFs: RF11
+------------------------PhysicalOlapScan[item]
+----------------PhysicalProject
+------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF11 i_manufact_id->[i_manufact_id]
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
--------------------------PhysicalProject
----------------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=() build
RFs:RF10 ws_item_sk->[i_item_sk]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item] apply RFs: RF10
+--------------------------------PhysicalOlapScan[item] apply RFs: RF10 RF11
------------------------------PhysicalProject
--------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_sales.ws_bill_addr_sk = customer_address.ca_address_sk))
otherCondition=() build RFs:RF9 ca_address_sk->[ws_bill_addr_sk]
----------------------------------PhysicalProject
@@ -80,4 +77,7 @@ PhysicalResultSink
----------------------------------PhysicalProject
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
+--------------------PhysicalProject
+----------------------filter((item.i_category = 'Home'))
+------------------------PhysicalOlapScan[item]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query35.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query35.out
index 12a43b15ccb..dcc86d24029 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query35.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query35.out
@@ -10,38 +10,38 @@ PhysicalResultSink
--------------hashAgg[LOCAL]
----------------PhysicalProject
------------------filter((ifnull($c$1, FALSE) OR ifnull($c$2, FALSE)))
---------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=()
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[cs_sold_date_sk]
+--------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=()
+----------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
+------------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF5 ss_customer_sk->[c_customer_sk]
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF5
+----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF4 cd_demo_sk->[c_current_cdemo_sk]
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF3 ca_address_sk->[c_current_addr_sk]
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[customer] apply RFs: RF3
RF4 RF5
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[customer_address]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[customer_demographics]
--------------------------PhysicalProject
-----------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year =
2001))
-------------------------------PhysicalOlapScan[date_dim]
-----------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
+----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[store_sales] apply RFs: RF2
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 2001))
+----------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[ws_sold_date_sk]
+--------------------------hashJoin[INNER_JOIN broadcast]
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: RF4
+------------------------------PhysicalOlapScan[web_sales] apply RFs: RF1
----------------------------PhysicalProject
------------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year
= 2001))
--------------------------------PhysicalOlapScan[date_dim]
-------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[ss_customer_sk]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF0 d_date_sk->[cs_sold_date_sk]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk]
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[store_sales] apply RFs: RF2
RF3
-------------------------------PhysicalProject
---------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 2001))
-----------------------------------PhysicalOlapScan[date_dim]
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF1 cd_demo_sk->[c_current_cdemo_sk]
-------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF0 ca_address_sk->[c_current_addr_sk]
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[customer] apply RFs: RF0
RF1
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[customer_address]
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[customer_demographics]
+----------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year =
2001))
+------------------------------PhysicalOlapScan[date_dim]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query69.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query69.out
index 090d1940739..7293ef67e29 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query69.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query69.out
@@ -9,39 +9,39 @@ PhysicalResultSink
------------PhysicalDistribute[DistributionSpecHash]
--------------hashAgg[LOCAL]
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF7 c_customer_sk->[ss_customer_sk]
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF6 c_customer_sk->[ss_customer_sk]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF6 d_date_sk->[ss_sold_date_sk]
+----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[ss_sold_date_sk]
------------------------PhysicalProject
---------------------------PhysicalOlapScan[store_sales] apply RFs: RF6 RF7
+--------------------------PhysicalOlapScan[store_sales] apply RFs: RF5 RF6
------------------------PhysicalProject
--------------------------filter((date_dim.d_moy <= 3) and (date_dim.d_moy >=
1) and (date_dim.d_year = 2000))
----------------------------PhysicalOlapScan[date_dim]
---------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[cs_ship_customer_sk]
+--------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=() build RFs:RF4 c_customer_sk->[cs_ship_customer_sk]
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[cs_sold_date_sk]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF3 d_date_sk->[cs_sold_date_sk]
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF4 RF5
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF3 RF4
--------------------------PhysicalProject
----------------------------filter((date_dim.d_moy <= 3) and (date_dim.d_moy
>= 1) and (date_dim.d_year = 2000))
------------------------------PhysicalOlapScan[date_dim]
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF3 c_current_cdemo_sk->[cd_demo_sk]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF2 c_current_cdemo_sk->[cd_demo_sk]
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[customer_demographics] apply RFs:
RF3
---------------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=() build RFs:RF2 c_customer_sk->[ws_bill_customer_sk]
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
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
RF2
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_moy <= 3) and
(date_dim.d_moy >= 1) and (date_dim.d_year = 2000))
-------------------------------------PhysicalOlapScan[date_dim]
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF0 ca_address_sk->[c_current_addr_sk]
+----------------------------PhysicalOlapScan[customer_demographics] apply RFs:
RF2
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF1 ca_address_sk->[c_current_addr_sk]
+------------------------------hashJoin[LEFT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[customer] apply RFs: RF0
+----------------------------------PhysicalOlapScan[customer] apply RFs: RF1
--------------------------------PhysicalProject
-----------------------------------filter(ca_state IN ('MI', 'TX', 'VA'))
-------------------------------------PhysicalOlapScan[customer_address]
+----------------------------------hashJoin[INNER_JOIN broadcast]
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
+------------------------------------PhysicalProject
+--------------------------------------filter((date_dim.d_moy <= 3) and
(date_dim.d_moy >= 1) and (date_dim.d_year = 2000))
+----------------------------------------PhysicalOlapScan[date_dim]
+------------------------------PhysicalProject
+--------------------------------filter(ca_state IN ('MI', 'TX', 'VA'))
+----------------------------------PhysicalOlapScan[customer_address]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query94.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query94.out
index e13bdc06291..6a25137bf51 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query94.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query94.out
@@ -8,7 +8,7 @@ PhysicalResultSink
----------hashAgg[GLOBAL]
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((ws1.ws_order_number = ws2.ws_order_number)) otherCondition=((
not (ws_warehouse_sk = ws_warehouse_sk))) build RFs:RF3
ws_order_number->[ws_order_number]
+----------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((ws1.ws_order_number = ws2.ws_order_number)) otherCondition=((
not (ws_warehouse_sk = ws_warehouse_sk))) build RFs:RF3
ws_order_number->[ws_order_number]
------------------PhysicalProject
--------------------PhysicalOlapScan[web_sales] apply RFs: RF3
------------------PhysicalProject
@@ -17,7 +17,7 @@ PhysicalResultSink
------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_ship_date_sk = date_dim.d_date_sk)) otherCondition=()
build RFs:RF1 d_date_sk->[ws_ship_date_sk]
--------------------------PhysicalProject
----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((ws1.ws_ship_addr_sk = customer_address.ca_address_sk))
otherCondition=() build RFs:RF0 ca_address_sk->[ws_ship_addr_sk]
-------------------------------hashJoin[LEFT_ANTI_JOIN shuffle]
hashCondition=((ws1.ws_order_number = wr1.wr_order_number)) otherCondition=()
+------------------------------hashJoin[LEFT_ANTI_JOIN broadcast]
hashCondition=((ws1.ws_order_number = wr1.wr_order_number)) otherCondition=()
--------------------------------PhysicalProject
----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0
RF1 RF2
--------------------------------PhysicalProject
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/rf_prune/q16.out
b/regression-test/data/nereids_tpch_shape_sf1000_p0/rf_prune/q16.out
index bbf5ed160d7..f04b0bc7663 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/rf_prune/q16.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/rf_prune/q16.out
@@ -7,7 +7,7 @@ PhysicalResultSink
--------hashAgg[GLOBAL]
----------PhysicalDistribute[DistributionSpecHash]
------------hashAgg[LOCAL]
---------------hashJoin[LEFT_ANTI_JOIN shuffle]
hashCondition=((partsupp.ps_suppkey = supplier.s_suppkey)) otherCondition=()
+--------------hashJoin[LEFT_ANTI_JOIN broadcast]
hashCondition=((partsupp.ps_suppkey = supplier.s_suppkey)) otherCondition=()
----------------PhysicalProject
------------------hashJoin[INNER_JOIN colocated]
hashCondition=((part.p_partkey = partsupp.ps_partkey)) otherCondition=() build
RFs:RF0 p_partkey->[ps_partkey]
--------------------PhysicalProject
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q16.out
b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q16.out
index bbf5ed160d7..f04b0bc7663 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q16.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q16.out
@@ -7,7 +7,7 @@ PhysicalResultSink
--------hashAgg[GLOBAL]
----------PhysicalDistribute[DistributionSpecHash]
------------hashAgg[LOCAL]
---------------hashJoin[LEFT_ANTI_JOIN shuffle]
hashCondition=((partsupp.ps_suppkey = supplier.s_suppkey)) otherCondition=()
+--------------hashJoin[LEFT_ANTI_JOIN broadcast]
hashCondition=((partsupp.ps_suppkey = supplier.s_suppkey)) otherCondition=()
----------------PhysicalProject
------------------hashJoin[INNER_JOIN colocated]
hashCondition=((part.p_partkey = partsupp.ps_partkey)) otherCondition=() build
RFs:RF0 p_partkey->[ps_partkey]
--------------------PhysicalProject
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
