This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new 09ed1fe893a branch-4.0: [Fix](rules) fix result wrong of
PushDownAggThroughJoinOnPkFk #59498 (#59703)
09ed1fe893a is described below
commit 09ed1fe893a27ed56a7ed6465b0cbdda42f194a4
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Mon Jan 12 10:20:47 2026 +0800
branch-4.0: [Fix](rules) fix result wrong of PushDownAggThroughJoinOnPkFk
#59498 (#59703)
Cherry-picked from #59498
Co-authored-by: feiniaofeiafei <[email protected]>
---
.../apache/doris/nereids/properties/FuncDeps.java | 51 +++++++++-----
.../nereids/rules/rewrite/EliminateGroupByKey.java | 43 +++++++-----
.../rewrite/PushDownAggThroughJoinOnPkFk.java | 44 ++++++++----
.../agg_join_pkfk/agg_join_pkfk.out | 36 ++++++++++
.../tpcds_sf100/noStatsRfPrune/query38.out | 81 +++++++++++-----------
.../tpcds_sf100/noStatsRfPrune/query87.out | 81 +++++++++++-----------
.../tpcds_sf100/no_stats_shape/query38.out | 81 +++++++++++-----------
.../tpcds_sf100/no_stats_shape/query87.out | 81 +++++++++++-----------
.../shape_check/tpcds_sf100/rf_prune/query38.out | 81 +++++++++++-----------
.../shape_check/tpcds_sf100/rf_prune/query87.out | 81 +++++++++++-----------
.../data/shape_check/tpcds_sf100/shape/query38.out | 81 +++++++++++-----------
.../data/shape_check/tpcds_sf100/shape/query87.out | 81 +++++++++++-----------
.../data/shape_check/tpcds_sf1000/hint/query38.out | 81 +++++++++++-----------
.../data/shape_check/tpcds_sf1000/hint/query87.out | 81 +++++++++++-----------
.../shape_check/tpcds_sf1000/shape/query38.out | 81 +++++++++++-----------
.../shape_check/tpcds_sf1000/shape/query87.out | 81 +++++++++++-----------
.../agg_join_pkfk/agg_join_pkfk.groovy | 63 +++++++++++++++++
17 files changed, 658 insertions(+), 551 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/FuncDeps.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/FuncDeps.java
index 849736ef51a..879b2de9fe6 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/FuncDeps.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/FuncDeps.java
@@ -144,27 +144,28 @@ public class FuncDeps {
* <p>
* Example:
* Given:
- * - Initial slots: {{A, B, C}, {D, E}, {F, G}}
- * - Required outputs: {A, D, F}
- * - Valid functional dependencies: {A} -> {B}, {D, E} -> {G}, {F} -> {G}
+ * - Initial slots: {{A}, {B}, {C}, {D}, {E}}
+ * - Required outputs: {}
+ * - validItems: {A} -> {B}, {B} -> {C}, {C} -> {D}, {D} -> {A}, {A} -> {E}
*
* Process:
- * 1. Start with minSlotSet = {{A, B, C}, {D, E}, {F, G}}
+ * 1. Start with minSlotSet = {{A}, {B}, {C}, {D}, {E}}
* 2. For {A} -> {B}:
* - Both {A} and {B} are in minSlotSet, so mark {B} for elimination
- * 3. For {D, E} -> {G}:
- * - Both {D, E} and {G} are in minSlotSet, so mark {G} for elimination
- * 4. For {F} -> {G}:
- * - Both {F} and {G} are in minSlotSet, but {G} is already marked for
elimination
- * 5. Remove eliminated slots: {B} and {G}
+ * 3. For {B} -> {C}:
+ * - Both {B} and {C} are in minSlotSet, so mark {C} for elimination
+ * 4. For {C} -> {D}:
+ * - Both {C} and {D} are in minSlotSet, so mark {D} for elimination
+ * 4. For {D} -> {E}:
+ * - Both {D} and {E} are in minSlotSet, so mark {E} for elimination
*
- * Result: {{A, C}, {D, E}, {F}}
+ * Result: {{A}}
* </p>
*
* @param slots the initial set of slot sets to be reduced
* @param requireOutputs the set of slots that must be preserved in the
output
* @return the minimal set of slot sets after applying all possible
reductions
- */
+ */
public Set<Set<Slot>> eliminateDeps(Set<Set<Slot>> slots, Set<Slot>
requireOutputs) {
Set<Set<Slot>> minSlotSet = Sets.newHashSet(slots);
Set<Set<Slot>> eliminatedSlots = new HashSet<>();
@@ -201,16 +202,30 @@ public class FuncDeps {
}
/**
- * find the determinants of dependencies
+ * Finds all slot sets that have a bijective relationship with the given
slot set.
+ * Given edges containing:
+ * {A} -> {{B}, {C}}
+ * {B} -> {{A}, {D}}
+ * {C} -> {{A}}
+ * When slot = {A}, returns {{B}} because {A} and {B} mutually determine
each other.
+ * {C} is not returned because {C} does not determine {A} (one-way
dependency only).
*/
- public Set<Set<Slot>> findDeterminats(Set<Slot> dependency) {
- Set<Set<Slot>> determinants = new HashSet<>();
- for (FuncDepsItem item : items) {
- if (item.dependencies.equals(dependency)) {
- determinants.add(item.determinants);
+ public Set<Set<Slot>> findBijectionSlots(Set<Slot> slot) {
+ Set<Set<Slot>> bijectionSlots = new HashSet<>();
+ if (!edges.containsKey(slot)) {
+ return bijectionSlots;
+ }
+ for (Set<Slot> dep : edges.get(slot)) {
+ if (!edges.containsKey(dep)) {
+ continue;
+ }
+ for (Set<Slot> det : edges.get(dep)) {
+ if (det.equals(slot)) {
+ bijectionSlots.add(dep);
+ }
}
}
- return determinants;
+ return bijectionSlots;
}
@Override
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateGroupByKey.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateGroupByKey.java
index fbe0988daff..4e1b3117ab5 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateGroupByKey.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateGroupByKey.java
@@ -18,6 +18,7 @@
package org.apache.doris.nereids.rules.rewrite;
import org.apache.doris.nereids.annotation.DependsRules;
+import org.apache.doris.nereids.properties.DataTrait;
import org.apache.doris.nereids.properties.FuncDeps;
import org.apache.doris.nereids.rules.Rule;
import org.apache.doris.nereids.rules.RuleType;
@@ -78,6 +79,28 @@ public class EliminateGroupByKey implements
RewriteRuleFactory {
}
LogicalAggregate<Plan> eliminateGroupByKey(LogicalAggregate<? extends
Plan> agg, Set<Slot> requireOutput) {
+ Set<Expression> removeExpression = findCanBeRemovedExpressions(agg,
requireOutput,
+ agg.child().getLogicalProperties().getTrait());
+ List<Expression> newGroupExpression = new ArrayList<>();
+ for (Expression expression : agg.getGroupByExpressions()) {
+ if (!removeExpression.contains(expression)) {
+ newGroupExpression.add(expression);
+ }
+ }
+ List<NamedExpression> newOutput = new ArrayList<>();
+ for (NamedExpression expression : agg.getOutputExpressions()) {
+ if (!removeExpression.contains(expression)) {
+ newOutput.add(expression);
+ }
+ }
+ return agg.withGroupByAndOutput(newGroupExpression, newOutput);
+ }
+
+ /**
+ * return removeExpression
+ */
+ public static Set<Expression>
findCanBeRemovedExpressions(LogicalAggregate<? extends Plan> agg,
+ Set<Slot> requireOutput, DataTrait dataTrait) {
Map<Expression, Set<Slot>> groupBySlots = new HashMap<>();
Set<Slot> validSlots = new HashSet<>();
for (Expression expression : agg.getGroupByExpressions()) {
@@ -85,10 +108,9 @@ public class EliminateGroupByKey implements
RewriteRuleFactory {
validSlots.addAll(expression.getInputSlots());
}
- FuncDeps funcDeps = agg.child().getLogicalProperties()
- .getTrait().getAllValidFuncDeps(validSlots);
+ FuncDeps funcDeps = dataTrait.getAllValidFuncDeps(validSlots);
if (funcDeps.isEmpty()) {
- return null;
+ return new HashSet<>();
}
Set<Set<Slot>> minGroupBySlots = funcDeps.eliminateDeps(new
HashSet<>(groupBySlots.values()), requireOutput);
@@ -99,19 +121,6 @@ public class EliminateGroupByKey implements
RewriteRuleFactory {
removeExpression.add(entry.getKey());
}
}
-
- List<Expression> newGroupExpression = new ArrayList<>();
- for (Expression expression : agg.getGroupByExpressions()) {
- if (!removeExpression.contains(expression)) {
- newGroupExpression.add(expression);
- }
- }
- List<NamedExpression> newOutput = new ArrayList<>();
- for (NamedExpression expression : agg.getOutputExpressions()) {
- if (!removeExpression.contains(expression)) {
- newOutput.add(expression);
- }
- }
- return agg.withGroupByAndOutput(newGroupExpression, newOutput);
+ return removeExpression;
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownAggThroughJoinOnPkFk.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownAggThroughJoinOnPkFk.java
index 2aeb59ae9c7..28dcc005ce4 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownAggThroughJoinOnPkFk.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownAggThroughJoinOnPkFk.java
@@ -18,7 +18,6 @@
package org.apache.doris.nereids.rules.rewrite;
import org.apache.doris.common.Pair;
-import org.apache.doris.nereids.properties.DataTrait;
import org.apache.doris.nereids.properties.FuncDeps;
import org.apache.doris.nereids.rules.Rule;
import org.apache.doris.nereids.rules.RuleType;
@@ -125,19 +124,38 @@ public class PushDownAggThroughJoinOnPkFk implements
RewriteRuleFactory {
}
// eliminate the slot of primary plan in agg
+ // e.g.
+ // select primary_table_pk, primary_table_other from primary_table join
foreign_table on pk = fk
+ // group by pk, primary_table_other_cols;
private LogicalAggregate<?> eliminatePrimaryOutput(LogicalAggregate<?>
agg, Plan child,
Plan primary, Plan foreign) {
Set<Slot> aggInputs = agg.getInputSlots();
if (primary.getOutputSet().stream().noneMatch(aggInputs::contains)) {
return agg;
}
+ // Firstly, using fd to eliminate group by key.
+ // group by pk, primary_table_other_cols;
+ // -> group by pk;
+ Set<Expression> removeExpression =
EliminateGroupByKey.findCanBeRemovedExpressions(agg,
+ Sets.intersection(agg.getOutputSet(), foreign.getOutputSet()),
+ child.getLogicalProperties().getTrait());
+ List<Expression> minGroupBySlotList = new ArrayList<>();
+ for (Expression expression : agg.getGroupByExpressions()) {
+ if (!removeExpression.contains(expression)) {
+ minGroupBySlotList.add(expression);
+ }
+ }
+
+ // Secondly, put bijective slot into map: {pk : fk}
+ // Bijective slots are mutually interchangeable within GROUP BY keys.
+ // group by pk -> group by fk
Set<Slot> primaryOutputSet = primary.getOutputSet();
Set<Slot> primarySlots = Sets.intersection(aggInputs,
primaryOutputSet);
- DataTrait dataTrait = child.getLogicalProperties().getTrait();
- FuncDeps funcDeps =
dataTrait.getAllValidFuncDeps(Sets.union(foreign.getOutputSet(),
primary.getOutputSet()));
HashMap<Slot, Slot> primaryToForeignDeps = new HashMap<>();
+ FuncDeps funcDepsForJoin = child.getLogicalProperties().getTrait()
+ .getAllValidFuncDeps(Sets.union(primaryOutputSet,
foreign.getOutputSet()));
for (Slot slot : primarySlots) {
- Set<Set<Slot>> replacedSlotSets =
funcDeps.findDeterminats(ImmutableSet.of(slot));
+ Set<Set<Slot>> replacedSlotSets =
funcDepsForJoin.findBijectionSlots(ImmutableSet.of(slot));
for (Set<Slot> replacedSlots : replacedSlotSets) {
if
(primaryOutputSet.stream().noneMatch(replacedSlots::contains)
&& replacedSlots.size() == 1) {
@@ -147,19 +165,23 @@ public class PushDownAggThroughJoinOnPkFk implements
RewriteRuleFactory {
}
}
- Set<Expression> newGroupBySlots = constructNewGroupBy(agg,
primaryOutputSet, primaryToForeignDeps);
+ // Thirdly, construct new Agg below join.
+ // For the pk-fk join, the foreign table side will not expand rows.
+ // As a result, executing agg(group by fk) before join is same with
executing agg(group by fk) after join.
+ Set<Expression> newGroupBySlots =
constructNewGroupBy(minGroupBySlotList, primaryOutputSet,
+ primaryToForeignDeps);
List<NamedExpression> newOutput = constructNewOutput(
- agg, primaryOutputSet, primaryToForeignDeps, funcDeps,
primary);
+ agg, primaryOutputSet, primaryToForeignDeps, funcDepsForJoin,
primary);
if (newGroupBySlots == null || newOutput == null) {
return null;
}
return agg.withGroupByAndOutput(ImmutableList.copyOf(newGroupBySlots),
ImmutableList.copyOf(newOutput));
}
- private @Nullable Set<Expression> constructNewGroupBy(LogicalAggregate<?>
agg, Set<Slot> primaryOutputs,
- Map<Slot, Slot> primaryToForeignBiDeps) {
+ private @Nullable Set<Expression> constructNewGroupBy(List<? extends
Expression> gbyExpression,
+ Set<Slot> primaryOutputs, Map<Slot, Slot> primaryToForeignBiDeps) {
Set<Expression> newGroupBySlots = new HashSet<>();
- for (Expression expression : agg.getGroupByExpressions()) {
+ for (Expression expression : gbyExpression) {
if (!(expression instanceof Slot)) {
return null;
}
@@ -196,9 +218,7 @@ public class PushDownAggThroughJoinOnPkFk implements
RewriteRuleFactory {
&& expression.child(0).child(0) instanceof Slot) {
// count(slot) can be rewritten by circle deps
Slot slot = (Slot) expression.child(0).child(0);
- if (primaryToForeignDeps.containsKey(slot)
- && funcDeps.isCircleDeps(
- ImmutableSet.of(slot),
ImmutableSet.of(primaryToForeignDeps.get(slot)))) {
+ if (primaryToForeignDeps.containsKey(slot)) {
expression = (NamedExpression) expression.rewriteUp(e ->
e instanceof Slot
? primaryToForeignDeps.getOrDefault((Slot)
e, (Slot) e)
diff --git
a/regression-test/data/nereids_rules_p0/agg_join_pkfk/agg_join_pkfk.out
b/regression-test/data/nereids_rules_p0/agg_join_pkfk/agg_join_pkfk.out
new file mode 100644
index 00000000000..4a514373e80
--- /dev/null
+++ b/regression-test/data/nereids_rules_p0/agg_join_pkfk/agg_join_pkfk.out
@@ -0,0 +1,36 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !not_push_down_shape --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----hashAgg[LOCAL]
+------hashJoin[INNER_JOIN] hashCondition=((store_sales_test.ss_customer_sk =
customer_test.c_customer_sk)) otherCondition=()
+--------PhysicalOlapScan[store_sales_test]
+--------PhysicalOlapScan[customer_test]
+
+-- !not_push_down_result --
+Smith John 2024-01-01
+
+-- !push_down_shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((store_sales_test.ss_customer_sk =
customer_test.c_customer_sk)) otherCondition=()
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalOlapScan[store_sales_test]
+----PhysicalOlapScan[customer_test]
+
+-- !push_down_result --
+John 1 2024-01-01
+John 2 2024-01-01
+
+-- !push_down_with_count_shape --
+PhysicalResultSink
+--hashJoin[INNER_JOIN] hashCondition=((store_sales_test.ss_customer_sk =
customer_test.c_customer_sk)) otherCondition=()
+----hashAgg[GLOBAL]
+------hashAgg[LOCAL]
+--------PhysicalOlapScan[store_sales_test]
+----PhysicalOlapScan[customer_test]
+
+-- !push_down_with_count_result --
+John 1 2024-01-01 1
+John 2 2024-01-01 1
+
diff --git
a/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query38.out
b/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query38.out
index d4857e3c0cd..1008fac09b2 100644
--- a/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query38.out
+++ b/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query38.out
@@ -8,49 +8,46 @@ PhysicalResultSink
----------hashAgg[LOCAL]
------------PhysicalProject
--------------PhysicalIntersect RFV2: RF6[c_last_name->c_last_name]
RF7[c_last_name->c_last_name]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=()
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------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_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
-------------------------------------PhysicalOlapScan[date_dim]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=()
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------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]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[catalog_sales] apply RFs:
RF2
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
-------------------------------------PhysicalOlapScan[date_dim]
+------------------------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
+----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=()
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[web_sales] apply RFs: RF1
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[customer]
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
+------------------------------PhysicalOlapScan[date_dim]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer] RFV2: RF6
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=()
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------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: RF4
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
-------------------------------------PhysicalOlapScan[date_dim]
+------------------------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
+----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=()
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF3
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[customer] RFV2: RF6
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
+------------------------------PhysicalOlapScan[date_dim]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer] RFV2: RF7
+------------------------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
+----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=()
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[store_sales] apply RFs: RF5
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[customer] RFV2: RF7
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
+------------------------------PhysicalOlapScan[date_dim]
diff --git
a/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query87.out
b/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query87.out
index 0af75be03b5..f7c070bd942 100644
--- a/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query87.out
+++ b/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query87.out
@@ -6,49 +6,46 @@ PhysicalResultSink
------hashAgg[LOCAL]
--------PhysicalProject
----------PhysicalExcept
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=()
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
---------------------------------PhysicalOlapScan[date_dim]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=()
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
---------------------------------PhysicalOlapScan[date_dim]
+--------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF1 d_date_sk->[ss_sold_date_sk]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=()
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[store_sales] apply RFs: RF1
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer]
+----------------------PhysicalProject
+------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
+--------------------------PhysicalOlapScan[date_dim]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=()
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
---------------------------------PhysicalOlapScan[date_dim]
+--------------------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
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=()
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF3
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer]
+----------------------PhysicalProject
+------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
+--------------------------PhysicalOlapScan[date_dim]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
+--------------------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]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=()
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[web_sales] apply RFs: RF5
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer]
+----------------------PhysicalProject
+------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
+--------------------------PhysicalOlapScan[date_dim]
diff --git
a/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query38.out
b/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query38.out
index fa43188e97d..a56a536123e 100644
--- a/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query38.out
+++ b/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query38.out
@@ -8,49 +8,46 @@ PhysicalResultSink
----------hashAgg[LOCAL]
------------PhysicalProject
--------------PhysicalIntersect RFV2: RF6[c_last_name->c_last_name]
RF7[c_last_name->c_last_name]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF1 c_customer_sk->[ws_bill_customer_sk]
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------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
RF1
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
-------------------------------------PhysicalOlapScan[date_dim]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[cs_bill_customer_sk]
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------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]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[catalog_sales] apply RFs:
RF2 RF3
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
-------------------------------------PhysicalOlapScan[date_dim]
+------------------------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
+----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF0 c_customer_sk->[ws_bill_customer_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0 RF1
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[customer]
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
+------------------------------PhysicalOlapScan[date_dim]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer] RFV2: RF6
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[ss_customer_sk]
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------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: RF4
RF5
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
-------------------------------------PhysicalOlapScan[date_dim]
+------------------------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
+----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF2 c_customer_sk->[cs_bill_customer_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2
RF3
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[customer] RFV2: RF6
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
+------------------------------PhysicalOlapScan[date_dim]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer] RFV2: RF7
+------------------------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
+----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF4 c_customer_sk->[ss_customer_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[store_sales] apply RFs: RF4
RF5
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[customer] RFV2: RF7
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
+------------------------------PhysicalOlapScan[date_dim]
diff --git
a/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query87.out
b/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query87.out
index b67550c0227..a31c5dc9e11 100644
--- a/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query87.out
+++ b/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query87.out
@@ -6,49 +6,46 @@ PhysicalResultSink
------hashAgg[LOCAL]
--------PhysicalProject
----------PhysicalExcept
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF1 c_customer_sk->[ss_customer_sk]
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
---------------------------------PhysicalOlapScan[date_dim]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[cs_bill_customer_sk]
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2
RF3
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
---------------------------------PhysicalOlapScan[date_dim]
+--------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF1 d_date_sk->[ss_sold_date_sk]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF0 c_customer_sk->[ss_customer_sk]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer]
+----------------------PhysicalProject
+------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
+--------------------------PhysicalOlapScan[date_dim]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[ws_bill_customer_sk]
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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 RF5
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
---------------------------------PhysicalOlapScan[date_dim]
+--------------------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
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF2 c_customer_sk->[cs_bill_customer_sk]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2 RF3
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer]
+----------------------PhysicalProject
+------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
+--------------------------PhysicalOlapScan[date_dim]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
+--------------------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]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF4 c_customer_sk->[ws_bill_customer_sk]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[web_sales] apply RFs: RF4 RF5
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer]
+----------------------PhysicalProject
+------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
+--------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query38.out
b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query38.out
index d4857e3c0cd..7534ddcc8c2 100644
--- a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query38.out
+++ b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query38.out
@@ -8,49 +8,46 @@ PhysicalResultSink
----------hashAgg[LOCAL]
------------PhysicalProject
--------------PhysicalIntersect RFV2: RF6[c_last_name->c_last_name]
RF7[c_last_name->c_last_name]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=()
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------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_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
-------------------------------------PhysicalOlapScan[date_dim]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=()
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------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]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[catalog_sales] apply RFs:
RF2
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
-------------------------------------PhysicalOlapScan[date_dim]
+------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_sales.ws_bill_customer_sk = 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:RF0 d_date_sk->[ws_sold_date_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer] RFV2: RF6
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=()
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------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: RF4
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
-------------------------------------PhysicalOlapScan[date_dim]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=()
+--------------------------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]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer] RFV2: RF6
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer] RFV2: RF7
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=()
+--------------------------PhysicalProject
+----------------------------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: RF4
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer] RFV2: RF7
diff --git a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query87.out
b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query87.out
index 00d730cfd4c..32c3855f30b 100644
--- a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query87.out
+++ b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query87.out
@@ -6,49 +6,46 @@ PhysicalResultSink
------hashAgg[LOCAL]
--------PhysicalProject
----------PhysicalExcept RFV2: RF6[c_last_name->c_last_name]
RF7[c_last_name->c_last_name]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=()
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
---------------------------------PhysicalOlapScan[date_dim]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=()
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
---------------------------------PhysicalOlapScan[date_dim]
+--------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=()
+----------------------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
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
+------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[customer]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer] RFV2: RF6
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=()
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
---------------------------------PhysicalOlapScan[date_dim]
+--------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=()
+----------------------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]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
+------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[customer] RFV2: RF6
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer] RFV2: RF7
+--------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_sales.ws_bill_customer_sk = 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:RF4 d_date_sk->[ws_sold_date_sk]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[web_sales] apply RFs: RF4
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
+------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[customer] RFV2: RF7
diff --git a/regression-test/data/shape_check/tpcds_sf100/shape/query38.out
b/regression-test/data/shape_check/tpcds_sf100/shape/query38.out
index fa43188e97d..e55825e7e76 100644
--- a/regression-test/data/shape_check/tpcds_sf100/shape/query38.out
+++ b/regression-test/data/shape_check/tpcds_sf100/shape/query38.out
@@ -8,49 +8,46 @@ PhysicalResultSink
----------hashAgg[LOCAL]
------------PhysicalProject
--------------PhysicalIntersect RFV2: RF6[c_last_name->c_last_name]
RF7[c_last_name->c_last_name]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF1 c_customer_sk->[ws_bill_customer_sk]
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------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
RF1
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
-------------------------------------PhysicalOlapScan[date_dim]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[cs_bill_customer_sk]
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------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]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[catalog_sales] apply RFs:
RF2 RF3
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
-------------------------------------PhysicalOlapScan[date_dim]
+------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF1 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:RF0 d_date_sk->[ws_sold_date_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0 RF1
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer] RFV2: RF6
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[ss_customer_sk]
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------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: RF4
RF5
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
-------------------------------------PhysicalOlapScan[date_dim]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[cs_bill_customer_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]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2
RF3
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer] RFV2: RF6
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer] RFV2: RF7
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_customer_sk = customer.c_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:RF4 d_date_sk->[ss_sold_date_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[store_sales] apply RFs: RF4
RF5
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_month_seq <= 1194) and
(date_dim.d_month_seq >= 1183))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer] RFV2: RF7
diff --git a/regression-test/data/shape_check/tpcds_sf100/shape/query87.out
b/regression-test/data/shape_check/tpcds_sf100/shape/query87.out
index cfd1a87f7da..247dfbbbc89 100644
--- a/regression-test/data/shape_check/tpcds_sf100/shape/query87.out
+++ b/regression-test/data/shape_check/tpcds_sf100/shape/query87.out
@@ -6,49 +6,46 @@ PhysicalResultSink
------hashAgg[LOCAL]
--------PhysicalProject
----------PhysicalExcept RFV2: RF6[c_last_name->c_last_name]
RF7[c_last_name->c_last_name]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF1 c_customer_sk->[ss_customer_sk]
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
---------------------------------PhysicalOlapScan[date_dim]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[cs_bill_customer_sk]
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2
RF3
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
---------------------------------PhysicalOlapScan[date_dim]
+--------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF1 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:RF0 d_date_sk->[ss_sold_date_sk]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
+------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[customer]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer] RFV2: RF6
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[ws_bill_customer_sk]
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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 RF5
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
---------------------------------PhysicalOlapScan[date_dim]
+--------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[cs_bill_customer_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]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2 RF3
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
+------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[customer] RFV2: RF6
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer] RFV2: RF7
+--------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF5 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:RF4 d_date_sk->[ws_sold_date_sk]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[web_sales] apply RFs: RF4 RF5
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1195) and
(date_dim.d_month_seq >= 1184))
+------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[customer] RFV2: RF7
diff --git a/regression-test/data/shape_check/tpcds_sf1000/hint/query38.out
b/regression-test/data/shape_check/tpcds_sf1000/hint/query38.out
index 1a49401e891..fc988652648 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/hint/query38.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/hint/query38.out
@@ -8,49 +8,46 @@ PhysicalResultSink
----------hashAgg[LOCAL]
------------PhysicalProject
--------------PhysicalIntersect
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF1 c_customer_sk->[ws_bill_customer_sk]
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------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
RF1
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1200) and
(date_dim.d_month_seq >= 1189))
-------------------------------------PhysicalOlapScan[date_dim]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[cs_bill_customer_sk]
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------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]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[catalog_sales] apply RFs:
RF2 RF3
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1200) and
(date_dim.d_month_seq >= 1189))
-------------------------------------PhysicalOlapScan[date_dim]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF1 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:RF0 d_date_sk->[ws_sold_date_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0 RF1
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_month_seq <= 1200) and
(date_dim.d_month_seq >= 1189))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[ss_customer_sk]
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------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: RF4
RF5
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1200) and
(date_dim.d_month_seq >= 1189))
-------------------------------------PhysicalOlapScan[date_dim]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[cs_bill_customer_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]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2
RF3
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_month_seq <= 1200) and
(date_dim.d_month_seq >= 1189))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_customer_sk = customer.c_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:RF4 d_date_sk->[ss_sold_date_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[store_sales] apply RFs: RF4
RF5
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_month_seq <= 1200) and
(date_dim.d_month_seq >= 1189))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/hint/query87.out
b/regression-test/data/shape_check/tpcds_sf1000/hint/query87.out
index 4f102613865..9f5547c4459 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/hint/query87.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/hint/query87.out
@@ -6,49 +6,46 @@ PhysicalResultSink
------hashAgg[LOCAL]
--------PhysicalProject
----------PhysicalExcept
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF1 c_customer_sk->[ss_customer_sk]
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1213) and
(date_dim.d_month_seq >= 1202))
---------------------------------PhysicalOlapScan[date_dim]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[cs_bill_customer_sk]
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2
RF3
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1213) and
(date_dim.d_month_seq >= 1202))
---------------------------------PhysicalOlapScan[date_dim]
+--------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF1 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:RF0 d_date_sk->[ss_sold_date_sk]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1213) and
(date_dim.d_month_seq >= 1202))
+------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[customer]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[ws_bill_customer_sk]
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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 RF5
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1213) and
(date_dim.d_month_seq >= 1202))
---------------------------------PhysicalOlapScan[date_dim]
+--------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[cs_bill_customer_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]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2 RF3
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1213) and
(date_dim.d_month_seq >= 1202))
+------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[customer]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
+--------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF5 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:RF4 d_date_sk->[ws_sold_date_sk]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[web_sales] apply RFs: RF4 RF5
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1213) and
(date_dim.d_month_seq >= 1202))
+------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[customer]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query38.out
b/regression-test/data/shape_check/tpcds_sf1000/shape/query38.out
index 1a49401e891..fc988652648 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/shape/query38.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/shape/query38.out
@@ -8,49 +8,46 @@ PhysicalResultSink
----------hashAgg[LOCAL]
------------PhysicalProject
--------------PhysicalIntersect
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF1 c_customer_sk->[ws_bill_customer_sk]
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------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
RF1
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1200) and
(date_dim.d_month_seq >= 1189))
-------------------------------------PhysicalOlapScan[date_dim]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[cs_bill_customer_sk]
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------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]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[catalog_sales] apply RFs:
RF2 RF3
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1200) and
(date_dim.d_month_seq >= 1189))
-------------------------------------PhysicalOlapScan[date_dim]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF1 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:RF0 d_date_sk->[ws_sold_date_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0 RF1
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_month_seq <= 1200) and
(date_dim.d_month_seq >= 1189))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[ss_customer_sk]
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------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: RF4
RF5
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_month_seq <= 1200) and
(date_dim.d_month_seq >= 1189))
-------------------------------------PhysicalOlapScan[date_dim]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[cs_bill_customer_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]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2
RF3
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_month_seq <= 1200) and
(date_dim.d_month_seq >= 1189))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer]
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------PhysicalOlapScan[customer]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_customer_sk = customer.c_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:RF4 d_date_sk->[ss_sold_date_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[store_sales] apply RFs: RF4
RF5
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_month_seq <= 1200) and
(date_dim.d_month_seq >= 1189))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query87.out
b/regression-test/data/shape_check/tpcds_sf1000/shape/query87.out
index 4f102613865..9f5547c4459 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/shape/query87.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/shape/query87.out
@@ -6,49 +6,46 @@ PhysicalResultSink
------hashAgg[LOCAL]
--------PhysicalProject
----------PhysicalExcept
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF1 c_customer_sk->[ss_customer_sk]
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1213) and
(date_dim.d_month_seq >= 1202))
---------------------------------PhysicalOlapScan[date_dim]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[cs_bill_customer_sk]
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2
RF3
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1213) and
(date_dim.d_month_seq >= 1202))
---------------------------------PhysicalOlapScan[date_dim]
+--------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF1 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:RF0 d_date_sk->[ss_sold_date_sk]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1213) and
(date_dim.d_month_seq >= 1202))
+------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[customer]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[ws_bill_customer_sk]
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecHash]
-----------------------hashAgg[LOCAL]
-------------------------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 RF5
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_month_seq <= 1213) and
(date_dim.d_month_seq >= 1202))
---------------------------------PhysicalOlapScan[date_dim]
+--------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[cs_bill_customer_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]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF2 RF3
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1213) and
(date_dim.d_month_seq >= 1202))
+------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[customer]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
+--------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk))
otherCondition=() build RFs:RF5 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:RF4 d_date_sk->[ws_sold_date_sk]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[web_sales] apply RFs: RF4 RF5
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_month_seq <= 1213) and
(date_dim.d_month_seq >= 1202))
+------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[customer]
diff --git
a/regression-test/suites/nereids_rules_p0/agg_join_pkfk/agg_join_pkfk.groovy
b/regression-test/suites/nereids_rules_p0/agg_join_pkfk/agg_join_pkfk.groovy
new file mode 100644
index 00000000000..778547830c1
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/agg_join_pkfk/agg_join_pkfk.groovy
@@ -0,0 +1,63 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+suite("agg_join_pkfk") {
+ multi_sql """
+ SET ignore_shape_nodes='PhysicalDistribute,PhysicalProject';
+ set runtime_filter_mode=OFF;
+ set disable_join_reorder=true;
+ drop table if exists customer_test;
+ CREATE TABLE customer_test (
+ c_customer_sk INT not null ,
+ c_first_name VARCHAR(50),
+ c_last_name VARCHAR(50)
+ );
+ drop table if exists store_sales_test;
+ CREATE TABLE store_sales_test (
+ ss_customer_sk INT,
+ d_date DATE
+ );
+
+ INSERT INTO customer_test VALUES (1, 'John', 'Smith');
+ INSERT INTO customer_test VALUES (2, 'John', 'Smith');
+
+ INSERT INTO store_sales_test VALUES (1, '2024-01-01');
+ INSERT INTO store_sales_test VALUES (2, '2024-01-01');
+
+ alter table customer_test add constraint c_pk primary key
(c_customer_sk);
+ alter table store_sales_test add constraint ss_c_fk foreign key
(ss_customer_sk) references customer_test(c_customer_sk);
+ """
+ explainAndOrderResult 'not_push_down', """
+ SELECT DISTINCT c_last_name, c_first_name, d_date
+ FROM store_sales_test inner join customer_test
+ on store_sales_test.ss_customer_sk = customer_test.c_customer_sk;
+ """
+
+ explainAndOrderResult 'push_down', """
+ SELECT DISTINCT c_first_name,c_customer_sk, d_date
+ FROM store_sales_test inner join customer_test
+ on store_sales_test.ss_customer_sk = customer_test.c_customer_sk;
+ """
+
+ explainAndOrderResult 'push_down_with_count', """
+ SELECT c_first_name,c_customer_sk, d_date,count(c_customer_sk) from (
+ select *
+ FROM store_sales_test inner join customer_test
+ on store_sales_test.ss_customer_sk = customer_test.c_customer_sk
+ ) t
+ group by c_first_name,c_customer_sk, d_date;
+ """
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
