This is an automated email from the ASF dual-hosted git repository.
englefly pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 7d66bc07db7 [opt](nereids) adjust Left join cost
factor:probeShortcutFactor (#60183)
7d66bc07db7 is described below
commit 7d66bc07db7196d249e9d00d83f82ec45285f6fd
Author: minghong <[email protected]>
AuthorDate: Wed Jan 28 10:34:26 2026 +0800
[opt](nereids) adjust Left join cost factor:probeShortcutFactor (#60183)
### What problem does this PR solve?
probeShortcutFactor is not reasonable when right row count is much more
than left row count.
tpch affected query(1T)
before:22.sql,49.008,2.729,2.582,2.666,2.582
after: 22.sql,1.466,1.439,1.384,1.379,1.379
tpcds affected queries(1T)
before:
query33.sql,3.252,0.490,0.454,0.412,0.412
query35.sql,12.897,1.059,1.054,1.039,1.039
query64.sql,139.298,2.788,20.533,8.234,2.788
query69.sql,3.004,0.835,0.773,0.772,0.772
after:
query33.sql,1.114,0.512,0.540,0.468,0.468
query35.sql,1.398,1.146,1.194,1.180,1.146
query64.sql,3.562,1.939,2.054,1.985,1.939
query69.sql,0.826,0.848,0.806,0.804,0.804
---
.../org/apache/doris/nereids/cost/CostModel.java | 3 +-
.../tpcds_sf100/constraints/query23.out | 12 +-
.../shape_check/tpcds_sf100/rf_prune/query33.out | 34 +++---
.../shape_check/tpcds_sf100/rf_prune/query35.out | 58 ++++-----
.../shape_check/tpcds_sf100/rf_prune/query64.out | 129 +++++++++++----------
.../data/shape_check/tpcds_sf100/shape/query33.out | 26 ++---
.../data/shape_check/tpcds_sf100/shape/query35.out | 58 ++++-----
.../data/shape_check/tpcds_sf100/shape/query64.out | 129 +++++++++++----------
.../data/shape_check/tpcds_sf1000/hint/query69.out | 34 +++---
.../shape_check/tpcds_sf1000/shape/query33.out | 26 ++---
.../shape_check/tpcds_sf1000/shape/query35.out | 44 +++----
.../shape_check/tpcds_sf1000/shape/query64.out | 129 +++++++++++----------
.../shape_check/tpcds_sf1000/shape/query69.out | 34 +++---
.../check_point/probeShortcutFactor.out | 10 ++
.../data/shape_check/tpch_sf1000/rf_prune/q22.out | 20 ++--
.../data/shape_check/tpch_sf1000/shape/q22.out | 20 ++--
.../check_point/probeShortcutFactor.groovy | 44 +++++++
17 files changed, 434 insertions(+), 376 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModel.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModel.java
index 9f828847eaa..34bb9dacddf 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModel.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModel.java
@@ -452,7 +452,8 @@ class CostModel extends PlanVisitor<Cost, PlanContext> {
);
}
double probeShortcutFactor = 1.0;
- if (ConnectContext.get() != null &&
ConnectContext.get().getStatementContext() != null
+ if (rightRowCount < 10 * leftRowCount
+ && ConnectContext.get() != null &&
ConnectContext.get().getStatementContext() != null
&&
!ConnectContext.get().getStatementContext().isHasUnknownColStats()
&& physicalHashJoin.getJoinType().isLeftSemiOrAntiJoin()
&& physicalHashJoin.getOtherJoinConjuncts().isEmpty()
diff --git
a/regression-test/data/shape_check/tpcds_sf100/constraints/query23.out
b/regression-test/data/shape_check/tpcds_sf100/constraints/query23.out
index 9aac34d3e02..17c6c0e8b7a 100644
--- a/regression-test/data/shape_check/tpcds_sf100/constraints/query23.out
+++ b/regression-test/data/shape_check/tpcds_sf100/constraints/query23.out
@@ -53,29 +53,29 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------------hashAgg[LOCAL]
----------------PhysicalUnion
------------------PhysicalProject
---------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = frequent_ss_items.item_sk))
otherCondition=() build RFs:RF5 item_sk->[cs_item_sk]
+--------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = frequent_ss_items.item_sk))
otherCondition=() build RFs:RF5 cs_item_sk->[item_sk]
+----------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF5
----------------------PhysicalProject
------------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((catalog_sales.cs_bill_customer_sk =
best_ss_customer.c_customer_sk)) otherCondition=() build RFs:RF4
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:RF3 d_date_sk->[cs_sold_date_sk]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF3
RF4 RF5
+--------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF3
RF4
------------------------------PhysicalProject
--------------------------------filter((date_dim.d_moy = 5) and
(date_dim.d_year = 2000))
----------------------------------PhysicalOlapScan[date_dim]
--------------------------PhysicalCteConsumer ( cteId=CTEId#2 )
-----------------------PhysicalCteConsumer ( cteId=CTEId#0 )
------------------PhysicalProject
---------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = frequent_ss_items.item_sk))
otherCondition=() build RFs:RF8 item_sk->[ws_item_sk]
+--------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = frequent_ss_items.item_sk))
otherCondition=() build RFs:RF8 ws_item_sk->[item_sk]
+----------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF8
----------------------PhysicalProject
------------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((web_sales.ws_bill_customer_sk =
best_ss_customer.c_customer_sk)) otherCondition=() build RFs:RF7
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:RF6 d_date_sk->[ws_sold_date_sk]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[web_sales] apply RFs: RF6 RF7
RF8
+--------------------------------PhysicalOlapScan[web_sales] apply RFs: RF6 RF7
------------------------------PhysicalProject
--------------------------------filter((date_dim.d_moy = 5) and
(date_dim.d_year = 2000))
----------------------------------PhysicalOlapScan[date_dim]
--------------------------PhysicalCteConsumer ( cteId=CTEId#2 )
-----------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query33.out
b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query33.out
index 514fc6cf682..a09b5b4ae49 100644
--- a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query33.out
+++ b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query33.out
@@ -9,18 +9,21 @@ PhysicalResultSink
------------hashAgg[LOCAL]
--------------PhysicalUnion
----------------PhysicalProject
-------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF3 i_manufact_id->[i_manufact_id]
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF3 i_manufact_id->[i_manufact_id]
+--------------------PhysicalProject
+----------------------filter((item.i_category = 'Home'))
+------------------------PhysicalOlapScan[item] apply RFs: RF3
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF2 i_item_sk->[ss_item_sk]
+----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
------------------------------PhysicalProject
--------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_addr_sk = customer_address.ca_address_sk))
otherCondition=() build RFs:RF1 ca_address_sk->[ss_addr_sk]
----------------------------------PhysicalProject
------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
--------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[store_sales] apply
RFs: RF0 RF1 RF2
+----------------------------------------PhysicalOlapScan[store_sales] apply
RFs: RF0 RF1
--------------------------------------PhysicalProject
----------------------------------------filter((date_dim.d_moy = 1) and
(date_dim.d_year = 2002))
------------------------------------------PhysicalOlapScan[date_dim]
@@ -28,23 +31,23 @@ PhysicalResultSink
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item] apply RFs: RF3
+--------------------------------PhysicalOlapScan[item]
+----------------PhysicalProject
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF7 i_manufact_id->[i_manufact_id]
--------------------PhysicalProject
----------------------filter((item.i_category = 'Home'))
-------------------------PhysicalOlapScan[item]
-----------------PhysicalProject
-------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF7 i_manufact_id->[i_manufact_id]
+------------------------PhysicalOlapScan[item] apply RFs: RF7
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF6 i_item_sk->[cs_item_sk]
+----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = item.i_item_sk)) otherCondition=()
------------------------------PhysicalProject
--------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_bill_addr_sk =
customer_address.ca_address_sk)) otherCondition=() build RFs:RF5
ca_address_sk->[cs_bill_addr_sk]
----------------------------------PhysicalProject
------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[cs_sold_date_sk]
--------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[catalog_sales] apply
RFs: RF4 RF5 RF6
+----------------------------------------PhysicalOlapScan[catalog_sales] apply
RFs: RF4 RF5
--------------------------------------PhysicalProject
----------------------------------------filter((date_dim.d_moy = 1) and
(date_dim.d_year = 2002))
------------------------------------------PhysicalOlapScan[date_dim]
@@ -52,19 +55,19 @@ PhysicalResultSink
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item] apply RFs: RF7
+--------------------------------PhysicalOlapScan[item]
+----------------PhysicalProject
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF11 i_manufact_id->[i_manufact_id]
--------------------PhysicalProject
----------------------filter((item.i_category = 'Home'))
-------------------------PhysicalOlapScan[item]
-----------------PhysicalProject
-------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF11 i_manufact_id->[i_manufact_id]
+------------------------PhysicalOlapScan[item] apply RFs: RF11
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
--------------------------PhysicalProject
----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=() build
RFs:RF10 ws_item_sk->[i_item_sk]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item] apply RFs: RF10 RF11
+--------------------------------PhysicalOlapScan[item] apply RFs: RF10
------------------------------PhysicalProject
--------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_bill_addr_sk = customer_address.ca_address_sk))
otherCondition=() build RFs:RF9 ca_address_sk->[ws_bill_addr_sk]
----------------------------------PhysicalProject
@@ -77,7 +80,4 @@ PhysicalResultSink
----------------------------------PhysicalProject
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
---------------------PhysicalProject
-----------------------filter((item.i_category = 'Home'))
-------------------------PhysicalOlapScan[item]
diff --git a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query35.out
b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query35.out
index aa5b1beeef1..990cd91fc37 100644
--- a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query35.out
+++ b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query35.out
@@ -10,38 +10,38 @@ PhysicalResultSink
--------------hashAgg[LOCAL]
----------------PhysicalProject
------------------filter(OR[ifnull($c$1, FALSE),ifnull($c$2, FALSE)])
---------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=()
+--------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=()
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=()
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[cs_sold_date_sk]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
-------------------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF3
ss_customer_sk->[c_customer_sk,ws_bill_customer_sk]
---------------------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[customer(c)] apply RFs:
RF3
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[ws_sold_date_sk]
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[web_sales] apply RFs:
RF2 RF3
---------------------------------------PhysicalProject
-----------------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 2001))
-------------------------------------------PhysicalOlapScan[date_dim]
---------------------------------PhysicalProject
-----------------------------------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
---------------------------------------PhysicalOlapScan[store_sales] apply RFs:
RF1
-------------------------------------PhysicalProject
---------------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 2001))
-----------------------------------------PhysicalOlapScan[date_dim]
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[customer_address(ca)]
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[customer_demographics]
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF0 d_date_sk->[cs_sold_date_sk]
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF5
--------------------------PhysicalProject
----------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year =
2001))
------------------------------PhysicalOlapScan[date_dim]
+----------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
+------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk)) otherCondition=()
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF3 d_date_sk->[ss_sold_date_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[store_sales] apply RFs: RF3
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 2001))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=()
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[customer(c)]
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[customer_address(ca)]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[customer_demographics]
+------------------------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_qoy < 4) and (date_dim.d_year
= 2001))
+--------------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query64.out
b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query64.out
index 28df68528b5..92a15d7ac88 100644
--- a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query64.out
+++ b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query64.out
@@ -7,85 +7,86 @@ PhysicalCteAnchor ( cteId=CTEId#1 )
--------PhysicalDistribute[DistributionSpecHash]
----------hashAgg[LOCAL]
------------PhysicalProject
---------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk))
otherCondition=()
+--------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF19 i_item_sk->[cr_item_sk,cs_item_sk,sr_item_sk,ss_item_sk]
----------------PhysicalProject
-------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))
otherCondition=()
+------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd2.hd_income_band_sk = ib2.ib_income_band_sk))
otherCondition=()
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=(( not (cd_marital_status = cd_marital_status))) build RFs:RF17
ss_customer_sk->[c_customer_sk]
+----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd1.hd_income_band_sk = ib1.ib_income_band_sk))
otherCondition=()
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk))
otherCondition=()
+--------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk))
otherCondition=()
----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk)) otherCondition=()
+------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=()
--------------------------------PhysicalProject
----------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_hdemo_sk = hd2.hd_demo_sk)) otherCondition=()
------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[customer] apply RFs:
RF17
-------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd2.hd_income_band_sk = ib2.ib_income_band_sk))
otherCondition=()
+--------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk)) otherCondition=()
----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[household_demographics(hd2)]
+------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_promo_sk = promotion.p_promo_sk))
otherCondition=()
+--------------------------------------------PhysicalProject
+----------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk))
otherCondition=(( not (cd_marital_status = cd_marital_status)))
+------------------------------------------------PhysicalProject
+--------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk))
otherCondition=()
+----------------------------------------------------PhysicalProject
+------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk))
otherCondition=()
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))
otherCondition=()
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_customer_sk =
customer.c_customer_sk)) otherCondition=()
+----------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_store_sk = store.s_store_sk))
otherCondition=()
+--------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[ss_sold_date_sk]
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_item_sk = cs_ui.cs_item_sk))
otherCondition=() build RFs:RF4 cs_item_sk->[sr_item_sk,ss_item_sk]
+----------------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------------hashJoin[INNER_JOIN
bucketShuffle] hashCondition=((store_sales.ss_item_sk =
store_returns.sr_item_sk) and (store_sales.ss_ticket_number =
store_returns.sr_ticket_number)) otherCondition=()
+--------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------PhysicalOlapScan[store_sales]
apply RFs: RF4 RF5 RF19
+--------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------PhysicalOlapScan[store_returns]
apply RFs: RF4 RF19
+----------------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------------filter((sale
> (2 * refund)))
+--------------------------------------------------------------------------------hashAgg[GLOBAL]
+----------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------------hashAgg[LOCAL]
+--------------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------------hashJoin[INNER_JOIN
bucketShuffle] hashCondition=((catalog_sales.cs_item_sk =
catalog_returns.cr_item_sk) and (catalog_sales.cs_order_number =
catalog_returns.cr_order_number)) otherCondition=()
+------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF19
+------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------PhysicalOlapScan[catalog_returns]
apply RFs: RF19
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------filter(d_year
IN (2001, 2002))
+----------------------------------------------------------------------------PhysicalOlapScan[date_dim(d1)]
+--------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------PhysicalOlapScan[store]
+----------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------PhysicalOlapScan[customer]
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[date_dim(d2)]
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------PhysicalOlapScan[date_dim(d3)]
+----------------------------------------------------PhysicalProject
+------------------------------------------------------PhysicalOlapScan[customer_demographics(cd1)]
+------------------------------------------------PhysicalProject
+--------------------------------------------------PhysicalOlapScan[customer_demographics(cd2)]
+--------------------------------------------PhysicalProject
+----------------------------------------------PhysicalOlapScan[promotion]
----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[income_band(ib2)]
+------------------------------------------PhysicalOlapScan[household_demographics(hd1)]
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[household_demographics(hd2)]
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[customer_demographics(cd2)]
+----------------------------------PhysicalOlapScan[customer_address(ad1)]
----------------------------PhysicalProject
------------------------------PhysicalOlapScan[customer_address(ad2)]
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_item_sk = store_returns.sr_item_sk) and
(store_sales.ss_ticket_number = store_returns.sr_ticket_number))
otherCondition=() build RFs:RF11 ss_item_sk->[sr_item_sk];RF12
ss_ticket_number->[sr_ticket_number]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[store_returns] apply RFs: RF11
RF12
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=()
build RFs:RF10 ss_addr_sk->[ca_address_sk]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[customer_address(ad1)]
apply RFs: RF10
---------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk)) otherCondition=()
build RFs:RF9 ss_cdemo_sk->[cd_demo_sk]
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[customer_demographics(cd1)]
apply RFs: RF9
-------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF8 i_item_sk->[cr_item_sk,cs_item_sk,ss_item_sk]
---------------------------------------PhysicalProject
-----------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_promo_sk = promotion.p_promo_sk))
otherCondition=()
-------------------------------------------PhysicalProject
---------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=()
-----------------------------------------------PhysicalProject
-------------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd1.hd_income_band_sk = ib1.ib_income_band_sk))
otherCondition=()
---------------------------------------------------PhysicalProject
-----------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk))
otherCondition=()
-------------------------------------------------------PhysicalProject
---------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_item_sk = cs_ui.cs_item_sk))
otherCondition=() build RFs:RF3 cs_item_sk->[ss_item_sk]
-----------------------------------------------------------PhysicalProject
-------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk]
---------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------PhysicalOlapScan[store_sales]
apply RFs: RF2 RF3 RF8
---------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------filter(d_year
IN (2001, 2002))
-------------------------------------------------------------------PhysicalOlapScan[date_dim(d1)]
-----------------------------------------------------------PhysicalProject
-------------------------------------------------------------filter((sale > (2
* refund)))
---------------------------------------------------------------hashAgg[GLOBAL]
-----------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
-------------------------------------------------------------------hashAgg[LOCAL]
---------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------hashJoin[INNER_JOIN
bucketShuffle] hashCondition=((catalog_sales.cs_item_sk =
catalog_returns.cr_item_sk) and (catalog_sales.cs_order_number =
catalog_returns.cr_order_number)) otherCondition=()
-------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF8
-------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------PhysicalOlapScan[catalog_returns]
apply RFs: RF8
-------------------------------------------------------PhysicalProject
---------------------------------------------------------PhysicalOlapScan[household_demographics(hd1)]
---------------------------------------------------PhysicalProject
-----------------------------------------------------PhysicalOlapScan[income_band(ib1)]
-----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[store]
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[promotion]
---------------------------------------PhysicalProject
-----------------------------------------filter((item.i_current_price <= 33.00)
and (item.i_current_price >= 24.00) and i_color IN ('blanched', 'brown',
'burlywood', 'chocolate', 'drab', 'medium'))
-------------------------------------------PhysicalOlapScan[item]
+--------------------------PhysicalOlapScan[income_band(ib1)]
--------------------PhysicalProject
-----------------------PhysicalOlapScan[date_dim(d2)]
+----------------------PhysicalOlapScan[income_band(ib2)]
----------------PhysicalProject
-------------------PhysicalOlapScan[date_dim(d3)]
+------------------filter((item.i_current_price <= 33.00) and
(item.i_current_price >= 24.00) and i_color IN ('blanched', 'brown',
'burlywood', 'chocolate', 'drab', 'medium'))
+--------------------PhysicalOlapScan[item]
--PhysicalResultSink
----PhysicalQuickSort[MERGE_SORT]
------PhysicalDistribute[DistributionSpecGather]
diff --git a/regression-test/data/shape_check/tpcds_sf100/shape/query33.out
b/regression-test/data/shape_check/tpcds_sf100/shape/query33.out
index 514fc6cf682..f5b2b8cb633 100644
--- a/regression-test/data/shape_check/tpcds_sf100/shape/query33.out
+++ b/regression-test/data/shape_check/tpcds_sf100/shape/query33.out
@@ -9,7 +9,10 @@ PhysicalResultSink
------------hashAgg[LOCAL]
--------------PhysicalUnion
----------------PhysicalProject
-------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF3 i_manufact_id->[i_manufact_id]
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF3 i_manufact_id->[i_manufact_id]
+--------------------PhysicalProject
+----------------------filter((item.i_category = 'Home'))
+------------------------PhysicalOlapScan[item] apply RFs: RF3
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
@@ -28,12 +31,12 @@ PhysicalResultSink
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item] apply RFs: RF3
+--------------------------------PhysicalOlapScan[item]
+----------------PhysicalProject
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF7 i_manufact_id->[i_manufact_id]
--------------------PhysicalProject
----------------------filter((item.i_category = 'Home'))
-------------------------PhysicalOlapScan[item]
-----------------PhysicalProject
-------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF7 i_manufact_id->[i_manufact_id]
+------------------------PhysicalOlapScan[item] apply RFs: RF7
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
@@ -52,19 +55,19 @@ PhysicalResultSink
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item] apply RFs: RF7
+--------------------------------PhysicalOlapScan[item]
+----------------PhysicalProject
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF11 i_manufact_id->[i_manufact_id]
--------------------PhysicalProject
----------------------filter((item.i_category = 'Home'))
-------------------------PhysicalOlapScan[item]
-----------------PhysicalProject
-------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF11 i_manufact_id->[i_manufact_id]
+------------------------PhysicalOlapScan[item] apply RFs: RF11
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
--------------------------PhysicalProject
----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=() build
RFs:RF10 ws_item_sk->[i_item_sk]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item] apply RFs: RF10 RF11
+--------------------------------PhysicalOlapScan[item] apply RFs: RF10
------------------------------PhysicalProject
--------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_bill_addr_sk = customer_address.ca_address_sk))
otherCondition=() build RFs:RF9 ca_address_sk->[ws_bill_addr_sk]
----------------------------------PhysicalProject
@@ -77,7 +80,4 @@ PhysicalResultSink
----------------------------------PhysicalProject
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
---------------------PhysicalProject
-----------------------filter((item.i_category = 'Home'))
-------------------------PhysicalOlapScan[item]
diff --git a/regression-test/data/shape_check/tpcds_sf100/shape/query35.out
b/regression-test/data/shape_check/tpcds_sf100/shape/query35.out
index fcecb53329b..ad8d398c404 100644
--- a/regression-test/data/shape_check/tpcds_sf100/shape/query35.out
+++ b/regression-test/data/shape_check/tpcds_sf100/shape/query35.out
@@ -10,38 +10,38 @@ PhysicalResultSink
--------------hashAgg[LOCAL]
----------------PhysicalProject
------------------filter(OR[ifnull($c$1, FALSE),ifnull($c$2, FALSE)])
---------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=()
+--------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=()
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF5 cd_demo_sk->[c_current_cdemo_sk]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[cs_sold_date_sk]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF4 ca_address_sk->[c_current_addr_sk]
-------------------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF3
ss_customer_sk->[c_customer_sk,ws_bill_customer_sk]
---------------------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[customer(c)] apply RFs:
RF3 RF4 RF5
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[ws_sold_date_sk]
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[web_sales] apply RFs:
RF2 RF3
---------------------------------------PhysicalProject
-----------------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 2001))
-------------------------------------------PhysicalOlapScan[date_dim]
---------------------------------PhysicalProject
-----------------------------------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
---------------------------------------PhysicalOlapScan[store_sales] apply RFs:
RF1
-------------------------------------PhysicalProject
---------------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 2001))
-----------------------------------------PhysicalOlapScan[date_dim]
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[customer_address(ca)]
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[customer_demographics]
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF0 d_date_sk->[cs_sold_date_sk]
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF5
--------------------------PhysicalProject
----------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year =
2001))
------------------------------PhysicalOlapScan[date_dim]
+----------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
+------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF4 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:RF3 d_date_sk->[ss_sold_date_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[store_sales] apply RFs: RF3
RF4
+------------------------------PhysicalProject
+--------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 2001))
+----------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF2 cd_demo_sk->[c_current_cdemo_sk]
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF1 ca_address_sk->[c_current_addr_sk]
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[customer(c)] apply RFs:
RF1 RF2
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[customer_address(ca)]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[customer_demographics]
+------------------------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_qoy < 4) and (date_dim.d_year
= 2001))
+--------------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/shape_check/tpcds_sf100/shape/query64.out
b/regression-test/data/shape_check/tpcds_sf100/shape/query64.out
index 9b91b5a6891..9c9971d7429 100644
--- a/regression-test/data/shape_check/tpcds_sf100/shape/query64.out
+++ b/regression-test/data/shape_check/tpcds_sf100/shape/query64.out
@@ -7,85 +7,86 @@ PhysicalCteAnchor ( cteId=CTEId#1 )
--------PhysicalDistribute[DistributionSpecHash]
----------hashAgg[LOCAL]
------------PhysicalProject
---------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk))
otherCondition=() build RFs:RF19 d_date_sk->[c_first_shipto_date_sk]
+--------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF19 i_item_sk->[cr_item_sk,cs_item_sk,sr_item_sk,ss_item_sk]
----------------PhysicalProject
-------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))
otherCondition=() build RFs:RF18 d_date_sk->[c_first_sales_date_sk]
+------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd2.hd_income_band_sk = ib2.ib_income_band_sk))
otherCondition=() build RFs:RF18 ib_income_band_sk->[hd_income_band_sk]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=(( not (cd_marital_status = cd_marital_status))) build RFs:RF17
ss_customer_sk->[c_customer_sk]
+----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd1.hd_income_band_sk = ib1.ib_income_band_sk))
otherCondition=() build RFs:RF17 ib_income_band_sk->[hd_income_band_sk]
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk))
otherCondition=() build RFs:RF16 ca_address_sk->[c_current_addr_sk]
+--------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk))
otherCondition=() build RFs:RF16 ca_address_sk->[c_current_addr_sk]
----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk))
otherCondition=() build RFs:RF15 cd_demo_sk->[c_current_cdemo_sk]
+------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=()
build RFs:RF15 ca_address_sk->[ss_addr_sk]
--------------------------------PhysicalProject
----------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_hdemo_sk = hd2.hd_demo_sk))
otherCondition=() build RFs:RF14 hd_demo_sk->[c_current_hdemo_sk]
------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[customer] apply RFs:
RF14 RF15 RF16 RF17 RF18 RF19
-------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd2.hd_income_band_sk = ib2.ib_income_band_sk))
otherCondition=() build RFs:RF13 ib_income_band_sk->[hd_income_band_sk]
+--------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk)) otherCondition=()
build RFs:RF13 hd_demo_sk->[ss_hdemo_sk]
----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[household_demographics(hd2)]
apply RFs: RF13
+------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_promo_sk = promotion.p_promo_sk))
otherCondition=() build RFs:RF12 p_promo_sk->[ss_promo_sk]
+--------------------------------------------PhysicalProject
+----------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk))
otherCondition=(( not (cd_marital_status = cd_marital_status))) build RFs:RF11
cd_demo_sk->[c_current_cdemo_sk]
+------------------------------------------------PhysicalProject
+--------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk))
otherCondition=() build RFs:RF10 cd_demo_sk->[ss_cdemo_sk]
+----------------------------------------------------PhysicalProject
+------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk))
otherCondition=() build RFs:RF9 d_date_sk->[c_first_shipto_date_sk]
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))
otherCondition=() build RFs:RF8 d_date_sk->[c_first_sales_date_sk]
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_customer_sk =
customer.c_customer_sk)) otherCondition=() build RFs:RF7
c_customer_sk->[ss_customer_sk]
+----------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_store_sk = store.s_store_sk))
otherCondition=() build RFs:RF6 s_store_sk->[ss_store_sk]
+--------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[ss_sold_date_sk]
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_item_sk = cs_ui.cs_item_sk))
otherCondition=() build RFs:RF4 cs_item_sk->[sr_item_sk,ss_item_sk]
+----------------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------------hashJoin[INNER_JOIN
bucketShuffle] hashCondition=((store_sales.ss_item_sk =
store_returns.sr_item_sk) and (store_sales.ss_ticket_number =
store_returns.sr_ticket_number)) otherCondition=() build RFs:RF2
sr_item_sk->[ss_item_sk];RF3 sr_ticket_number->[ss_ticket_number]
+--------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------PhysicalOlapScan[store_sales]
apply RFs: RF2 RF3 RF4 RF5 RF6 RF7 RF10 RF12 RF13 RF15 RF19
+--------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------PhysicalOlapScan[store_returns]
apply RFs: RF4 RF19
+----------------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------------filter((sale
> (2 * refund)))
+--------------------------------------------------------------------------------hashAgg[GLOBAL]
+----------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------------hashAgg[LOCAL]
+--------------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------------hashJoin[INNER_JOIN
bucketShuffle] hashCondition=((catalog_sales.cs_item_sk =
catalog_returns.cr_item_sk) and (catalog_sales.cs_order_number =
catalog_returns.cr_order_number)) otherCondition=() build RFs:RF0
cr_item_sk->[cs_item_sk];RF1 cr_order_number->[cs_order_number]
+------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF0 RF1 RF19
+------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------PhysicalOlapScan[catalog_returns]
apply RFs: RF19
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------filter(d_year
IN (2001, 2002))
+----------------------------------------------------------------------------PhysicalOlapScan[date_dim(d1)]
+--------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------PhysicalOlapScan[store]
+----------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------PhysicalOlapScan[customer]
apply RFs: RF8 RF9 RF11 RF14 RF16
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[date_dim(d2)]
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------PhysicalOlapScan[date_dim(d3)]
+----------------------------------------------------PhysicalProject
+------------------------------------------------------PhysicalOlapScan[customer_demographics(cd1)]
+------------------------------------------------PhysicalProject
+--------------------------------------------------PhysicalOlapScan[customer_demographics(cd2)]
+--------------------------------------------PhysicalProject
+----------------------------------------------PhysicalOlapScan[promotion]
----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[income_band(ib2)]
+------------------------------------------PhysicalOlapScan[household_demographics(hd1)]
apply RFs: RF17
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[household_demographics(hd2)]
apply RFs: RF18
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[customer_demographics(cd2)]
+----------------------------------PhysicalOlapScan[customer_address(ad1)]
----------------------------PhysicalProject
------------------------------PhysicalOlapScan[customer_address(ad2)]
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_item_sk = store_returns.sr_item_sk) and
(store_sales.ss_ticket_number = store_returns.sr_ticket_number))
otherCondition=() build RFs:RF11 ss_item_sk->[sr_item_sk];RF12
ss_ticket_number->[sr_ticket_number]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[store_returns] apply RFs: RF11
RF12
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=()
build RFs:RF10 ss_addr_sk->[ca_address_sk]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[customer_address(ad1)]
apply RFs: RF10
---------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk)) otherCondition=()
build RFs:RF9 ss_cdemo_sk->[cd_demo_sk]
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[customer_demographics(cd1)]
apply RFs: RF9
-------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF8 i_item_sk->[cr_item_sk,cs_item_sk,ss_item_sk]
---------------------------------------PhysicalProject
-----------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_promo_sk = promotion.p_promo_sk))
otherCondition=() build RFs:RF7 p_promo_sk->[ss_promo_sk]
-------------------------------------------PhysicalProject
---------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=()
build RFs:RF6 s_store_sk->[ss_store_sk]
-----------------------------------------------PhysicalProject
-------------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd1.hd_income_band_sk = ib1.ib_income_band_sk))
otherCondition=() build RFs:RF5 ib_income_band_sk->[hd_income_band_sk]
---------------------------------------------------PhysicalProject
-----------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk))
otherCondition=() build RFs:RF4 hd_demo_sk->[ss_hdemo_sk]
-------------------------------------------------------PhysicalProject
---------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_item_sk = cs_ui.cs_item_sk))
otherCondition=() build RFs:RF3 cs_item_sk->[ss_item_sk]
-----------------------------------------------------------PhysicalProject
-------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk]
---------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------PhysicalOlapScan[store_sales]
apply RFs: RF2 RF3 RF4 RF6 RF7 RF8
---------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------filter(d_year
IN (2001, 2002))
-------------------------------------------------------------------PhysicalOlapScan[date_dim(d1)]
-----------------------------------------------------------PhysicalProject
-------------------------------------------------------------filter((sale > (2
* refund)))
---------------------------------------------------------------hashAgg[GLOBAL]
-----------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
-------------------------------------------------------------------hashAgg[LOCAL]
---------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------hashJoin[INNER_JOIN
bucketShuffle] hashCondition=((catalog_sales.cs_item_sk =
catalog_returns.cr_item_sk) and (catalog_sales.cs_order_number =
catalog_returns.cr_order_number)) otherCondition=() build RFs:RF0
cr_item_sk->[cs_item_sk];RF1 cr_order_number->[cs_order_number]
-------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF0 RF1 RF8
-------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------PhysicalOlapScan[catalog_returns]
apply RFs: RF8
-------------------------------------------------------PhysicalProject
---------------------------------------------------------PhysicalOlapScan[household_demographics(hd1)]
apply RFs: RF5
---------------------------------------------------PhysicalProject
-----------------------------------------------------PhysicalOlapScan[income_band(ib1)]
-----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[store]
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[promotion]
---------------------------------------PhysicalProject
-----------------------------------------filter((item.i_current_price <= 33.00)
and (item.i_current_price >= 24.00) and i_color IN ('blanched', 'brown',
'burlywood', 'chocolate', 'drab', 'medium'))
-------------------------------------------PhysicalOlapScan[item]
+--------------------------PhysicalOlapScan[income_band(ib1)]
--------------------PhysicalProject
-----------------------PhysicalOlapScan[date_dim(d2)]
+----------------------PhysicalOlapScan[income_band(ib2)]
----------------PhysicalProject
-------------------PhysicalOlapScan[date_dim(d3)]
+------------------filter((item.i_current_price <= 33.00) and
(item.i_current_price >= 24.00) and i_color IN ('blanched', 'brown',
'burlywood', 'chocolate', 'drab', 'medium'))
+--------------------PhysicalOlapScan[item]
--PhysicalResultSink
----PhysicalQuickSort[MERGE_SORT]
------PhysicalDistribute[DistributionSpecGather]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/hint/query69.out
b/regression-test/data/shape_check/tpcds_sf1000/hint/query69.out
index 175f24b8cbd..953d0237ca5 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/hint/query69.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/hint/query69.out
@@ -9,39 +9,39 @@ PhysicalResultSink
------------PhysicalDistribute[DistributionSpecHash]
--------------hashAgg[LOCAL]
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF6 c_customer_sk->[ss_customer_sk]
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF7 c_customer_sk->[ss_customer_sk]
--------------------PhysicalProject
-----------------------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]
+----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF6 d_date_sk->[ss_sold_date_sk]
------------------------PhysicalProject
---------------------------PhysicalOlapScan[store_sales] apply RFs: RF5 RF6
+--------------------------PhysicalOlapScan[store_sales] apply RFs: RF6 RF7
------------------------PhysicalProject
--------------------------filter((date_dim.d_moy <= 3) and (date_dim.d_moy >=
1) and (date_dim.d_year = 2002))
----------------------------PhysicalOlapScan[date_dim]
---------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=() build RFs:RF4 c_customer_sk->[cs_ship_customer_sk]
+--------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[cs_ship_customer_sk]
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF3 d_date_sk->[cs_sold_date_sk]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[cs_sold_date_sk]
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF3 RF4
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF4 RF5
--------------------------PhysicalProject
----------------------------filter((date_dim.d_moy <= 3) and (date_dim.d_moy
>= 1) and (date_dim.d_year = 2002))
------------------------------PhysicalOlapScan[date_dim]
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF2 c_current_cdemo_sk->[cd_demo_sk]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF3 c_current_cdemo_sk->[cd_demo_sk]
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[customer_demographics] apply RFs:
RF2
---------------------------hashJoin[LEFT_ANTI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
+----------------------------PhysicalOlapScan[customer_demographics] apply RFs:
RF3
+--------------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=() build RFs:RF2 c_customer_sk->[ws_bill_customer_sk]
----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF1 ca_address_sk->[c_current_addr_sk]
+------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF1 d_date_sk->[ws_sold_date_sk]
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[customer(c)] apply RFs: RF1
+----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF1
RF2
--------------------------------PhysicalProject
-----------------------------------filter(ca_state IN ('IL', 'ME', 'TX'))
-------------------------------------PhysicalOlapScan[customer_address(ca)]
+----------------------------------filter((date_dim.d_moy <= 3) and
(date_dim.d_moy >= 1) and (date_dim.d_year = 2002))
+------------------------------------PhysicalOlapScan[date_dim]
----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF0 d_date_sk->[ws_sold_date_sk]
+------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF0 ca_address_sk->[c_current_addr_sk]
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0
+----------------------------------PhysicalOlapScan[customer(c)] apply RFs: RF0
--------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_moy <= 3) and
(date_dim.d_moy >= 1) and (date_dim.d_year = 2002))
-------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------filter(ca_state IN ('IL', 'ME', 'TX'))
+------------------------------------PhysicalOlapScan[customer_address(ca)]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query33.out
b/regression-test/data/shape_check/tpcds_sf1000/shape/query33.out
index 1f4f083cdcf..f1cd1e4c777 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/shape/query33.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/shape/query33.out
@@ -9,7 +9,10 @@ PhysicalResultSink
------------hashAgg[LOCAL]
--------------PhysicalUnion
----------------PhysicalProject
-------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF3 i_manufact_id->[i_manufact_id]
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF3 i_manufact_id->[i_manufact_id]
+--------------------PhysicalProject
+----------------------filter((item.i_category = 'Books'))
+------------------------PhysicalOlapScan[item] apply RFs: RF3
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
@@ -28,12 +31,12 @@ PhysicalResultSink
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item] apply RFs: RF3
+--------------------------------PhysicalOlapScan[item]
+----------------PhysicalProject
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF7 i_manufact_id->[i_manufact_id]
--------------------PhysicalProject
----------------------filter((item.i_category = 'Books'))
-------------------------PhysicalOlapScan[item]
-----------------PhysicalProject
-------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF7 i_manufact_id->[i_manufact_id]
+------------------------PhysicalOlapScan[item] apply RFs: RF7
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
@@ -52,12 +55,12 @@ PhysicalResultSink
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item] apply RFs: RF7
+--------------------------------PhysicalOlapScan[item]
+----------------PhysicalProject
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF11 i_manufact_id->[i_manufact_id]
--------------------PhysicalProject
----------------------filter((item.i_category = 'Books'))
-------------------------PhysicalOlapScan[item]
-----------------PhysicalProject
-------------------hashJoin[LEFT_SEMI_JOIN broadcast]
hashCondition=((item.i_manufact_id = item.i_manufact_id)) otherCondition=()
build RFs:RF11 i_manufact_id->[i_manufact_id]
+------------------------PhysicalOlapScan[item] apply RFs: RF11
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
@@ -76,8 +79,5 @@ PhysicalResultSink
------------------------------------filter((customer_address.ca_gmt_offset =
-5.00))
--------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[item] apply RFs: RF11
---------------------PhysicalProject
-----------------------filter((item.i_category = 'Books'))
-------------------------PhysicalOlapScan[item]
+--------------------------------PhysicalOlapScan[item]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query35.out
b/regression-test/data/shape_check/tpcds_sf1000/shape/query35.out
index 5eb3a1ba4f9..35be2410a0e 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/shape/query35.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/shape/query35.out
@@ -10,38 +10,38 @@ PhysicalResultSink
--------------hashAgg[LOCAL]
----------------PhysicalProject
------------------filter(OR[ifnull($c$1, FALSE),ifnull($c$2, FALSE)])
---------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=()
+--------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=()
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF5 cd_demo_sk->[c_current_cdemo_sk]
---------------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
-----------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF4 c_customer_sk->[ss_customer_sk]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[cs_sold_date_sk]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF5
+--------------------------PhysicalProject
+----------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year =
1999))
+------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF4 cd_demo_sk->[c_current_cdemo_sk]
+--------------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
+----------------------------PhysicalProject
+------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF3 d_date_sk->[ws_sold_date_sk]
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF3
+--------------------------------PhysicalProject
+----------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 1999))
+------------------------------------PhysicalOlapScan[date_dim]
+----------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF2 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:RF3 d_date_sk->[ss_sold_date_sk]
+--------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF1 d_date_sk->[ss_sold_date_sk]
----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[store_sales] apply RFs:
RF3 RF4
+------------------------------------PhysicalOlapScan[store_sales] apply RFs:
RF1 RF2
----------------------------------PhysicalProject
------------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 1999))
--------------------------------------PhysicalOlapScan[date_dim]
------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF2 ca_address_sk->[c_current_addr_sk]
+--------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF0 ca_address_sk->[c_current_addr_sk]
----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[customer(c)] apply RFs:
RF2 RF5
+------------------------------------PhysicalOlapScan[customer(c)] apply RFs:
RF0 RF4
----------------------------------PhysicalProject
------------------------------------PhysicalOlapScan[customer_address(ca)]
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF1 d_date_sk->[ws_sold_date_sk]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF1
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 1999))
-------------------------------------PhysicalOlapScan[date_dim]
--------------------------PhysicalProject
----------------------------PhysicalOlapScan[customer_demographics]
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF0 d_date_sk->[cs_sold_date_sk]
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0
---------------------------PhysicalProject
-----------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year =
1999))
-------------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query64.out
b/regression-test/data/shape_check/tpcds_sf1000/shape/query64.out
index 0762e95d146..7d5490b3d05 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/shape/query64.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/shape/query64.out
@@ -7,85 +7,86 @@ PhysicalCteAnchor ( cteId=CTEId#1 )
--------PhysicalDistribute[DistributionSpecHash]
----------hashAgg[LOCAL]
------------PhysicalProject
---------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk))
otherCondition=() build RFs:RF19 d_date_sk->[c_first_shipto_date_sk]
+--------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF19 i_item_sk->[cr_item_sk,cs_item_sk,sr_item_sk,ss_item_sk]
----------------PhysicalProject
-------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk))
otherCondition=(( not (cd_marital_status = cd_marital_status))) build RFs:RF18
ss_customer_sk->[c_customer_sk]
+------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd2.hd_income_band_sk = ib2.ib_income_band_sk))
otherCondition=() build RFs:RF18 ib_income_band_sk->[hd_income_band_sk]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk))
otherCondition=() build RFs:RF17 ca_address_sk->[c_current_addr_sk]
+----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd1.hd_income_band_sk = ib1.ib_income_band_sk))
otherCondition=() build RFs:RF17 ib_income_band_sk->[hd_income_band_sk]
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk))
otherCondition=() build RFs:RF16 cd_demo_sk->[c_current_cdemo_sk]
+--------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk))
otherCondition=() build RFs:RF16 ca_address_sk->[c_current_addr_sk]
----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))
otherCondition=() build RFs:RF15 d_date_sk->[c_first_sales_date_sk]
+------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=()
build RFs:RF15 ca_address_sk->[ss_addr_sk]
--------------------------------PhysicalProject
----------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_hdemo_sk = hd2.hd_demo_sk))
otherCondition=() build RFs:RF14 hd_demo_sk->[c_current_hdemo_sk]
------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[customer] apply RFs:
RF14 RF15 RF16 RF17 RF18 RF19
-------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd2.hd_income_band_sk = ib2.ib_income_band_sk))
otherCondition=() build RFs:RF13 ib_income_band_sk->[hd_income_band_sk]
-----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[household_demographics(hd2)]
apply RFs: RF13
+--------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk)) otherCondition=()
build RFs:RF13 hd_demo_sk->[ss_hdemo_sk]
----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[income_band(ib2)]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[date_dim(d2)]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[customer_demographics(cd2)]
-------------------------PhysicalProject
---------------------------PhysicalOlapScan[customer_address(ad2)]
---------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=()
build RFs:RF12 ca_address_sk->[ss_addr_sk]
-------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN bucketShuffle]
hashCondition=((store_sales.ss_item_sk = store_returns.sr_item_sk) and
(store_sales.ss_ticket_number = store_returns.sr_ticket_number))
otherCondition=() build RFs:RF10 ss_item_sk->[sr_item_sk];RF11
ss_ticket_number->[sr_ticket_number]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[store_returns] apply RFs: RF10
RF11
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_promo_sk = promotion.p_promo_sk))
otherCondition=() build RFs:RF9 p_promo_sk->[ss_promo_sk]
---------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk)) otherCondition=()
build RFs:RF8 cd_demo_sk->[ss_cdemo_sk]
-------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF7 i_item_sk->[cr_item_sk,cs_item_sk,ss_item_sk]
---------------------------------------PhysicalProject
-----------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=()
build RFs:RF6 s_store_sk->[ss_store_sk]
-------------------------------------------PhysicalProject
---------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd1.hd_income_band_sk = ib1.ib_income_band_sk))
otherCondition=() build RFs:RF5 ib_income_band_sk->[hd_income_band_sk]
-----------------------------------------------PhysicalProject
-------------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk)) otherCondition=()
build RFs:RF4 hd_demo_sk->[ss_hdemo_sk]
---------------------------------------------------PhysicalProject
-----------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_item_sk = cs_ui.cs_item_sk))
otherCondition=() build RFs:RF3 cs_item_sk->[ss_item_sk]
-------------------------------------------------------PhysicalProject
---------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk]
-----------------------------------------------------------PhysicalProject
-------------------------------------------------------------PhysicalOlapScan[store_sales]
apply RFs: RF2 RF3 RF4 RF6 RF7 RF8 RF9 RF12
-----------------------------------------------------------PhysicalProject
-------------------------------------------------------------filter(d_year IN
(1999, 2000))
---------------------------------------------------------------PhysicalOlapScan[date_dim(d1)]
-------------------------------------------------------PhysicalProject
---------------------------------------------------------filter((sale > (2 *
refund)))
-----------------------------------------------------------hashAgg[GLOBAL]
-------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
---------------------------------------------------------------hashAgg[LOCAL]
+------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_promo_sk = promotion.p_promo_sk))
otherCondition=() build RFs:RF12 p_promo_sk->[ss_promo_sk]
+--------------------------------------------PhysicalProject
+----------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk))
otherCondition=(( not (cd_marital_status = cd_marital_status))) build RFs:RF11
cd_demo_sk->[c_current_cdemo_sk]
+------------------------------------------------PhysicalProject
+--------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk))
otherCondition=() build RFs:RF10 cd_demo_sk->[ss_cdemo_sk]
+----------------------------------------------------PhysicalProject
+------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk))
otherCondition=() build RFs:RF9 d_date_sk->[c_first_shipto_date_sk]
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))
otherCondition=() build RFs:RF8 d_date_sk->[c_first_sales_date_sk]
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_customer_sk =
customer.c_customer_sk)) otherCondition=() build RFs:RF7
c_customer_sk->[ss_customer_sk]
----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------hashJoin[INNER_JOIN
bucketShuffle] hashCondition=((catalog_sales.cs_item_sk =
catalog_returns.cr_item_sk) and (catalog_sales.cs_order_number =
catalog_returns.cr_order_number)) otherCondition=() build RFs:RF0
cr_item_sk->[cs_item_sk];RF1 cr_order_number->[cs_order_number]
+------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_store_sk = store.s_store_sk))
otherCondition=() build RFs:RF6 s_store_sk->[ss_store_sk]
--------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF0 RF1 RF7
+----------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[ss_sold_date_sk]
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_item_sk = cs_ui.cs_item_sk))
otherCondition=() build RFs:RF4 cs_item_sk->[sr_item_sk,ss_item_sk]
+----------------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------------hashJoin[INNER_JOIN
bucketShuffle] hashCondition=((store_sales.ss_item_sk =
store_returns.sr_item_sk) and (store_sales.ss_ticket_number =
store_returns.sr_ticket_number)) otherCondition=() build RFs:RF2
sr_item_sk->[ss_item_sk];RF3 sr_ticket_number->[ss_ticket_number]
+--------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------PhysicalOlapScan[store_sales]
apply RFs: RF2 RF3 RF4 RF5 RF6 RF7 RF10 RF12 RF13 RF15 RF19
+--------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------PhysicalOlapScan[store_returns]
apply RFs: RF4 RF19
+----------------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------------filter((sale
> (2 * refund)))
+--------------------------------------------------------------------------------hashAgg[GLOBAL]
+----------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------------hashAgg[LOCAL]
+--------------------------------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------------------------------hashJoin[INNER_JOIN
bucketShuffle] hashCondition=((catalog_sales.cs_item_sk =
catalog_returns.cr_item_sk) and (catalog_sales.cs_order_number =
catalog_returns.cr_order_number)) otherCondition=() build RFs:RF0
cr_item_sk->[cs_item_sk];RF1 cr_order_number->[cs_order_number]
+------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF0 RF1 RF19
+------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------PhysicalOlapScan[catalog_returns]
apply RFs: RF19
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------filter(d_year
IN (1999, 2000))
+----------------------------------------------------------------------------PhysicalOlapScan[date_dim(d1)]
--------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------PhysicalOlapScan[catalog_returns]
apply RFs: RF7
---------------------------------------------------PhysicalProject
-----------------------------------------------------PhysicalOlapScan[household_demographics(hd1)]
apply RFs: RF5
-----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[income_band(ib1)]
-------------------------------------------PhysicalProject
---------------------------------------------PhysicalOlapScan[store]
---------------------------------------PhysicalProject
-----------------------------------------filter((item.i_current_price <= 58.00)
and (item.i_current_price >= 49.00) and i_color IN ('blush', 'lace', 'lawn',
'misty', 'orange', 'pink'))
-------------------------------------------PhysicalOlapScan[item]
+----------------------------------------------------------------------PhysicalOlapScan[store]
+----------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------PhysicalOlapScan[customer]
apply RFs: RF8 RF9 RF11 RF14 RF16
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[date_dim(d2)]
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------PhysicalOlapScan[date_dim(d3)]
+----------------------------------------------------PhysicalProject
+------------------------------------------------------PhysicalOlapScan[customer_demographics(cd1)]
+------------------------------------------------PhysicalProject
+--------------------------------------------------PhysicalOlapScan[customer_demographics(cd2)]
+--------------------------------------------PhysicalProject
+----------------------------------------------PhysicalOlapScan[promotion]
+----------------------------------------PhysicalProject
+------------------------------------------PhysicalOlapScan[household_demographics(hd1)]
apply RFs: RF17
------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[customer_demographics(cd1)]
+--------------------------------------PhysicalOlapScan[household_demographics(hd2)]
apply RFs: RF18
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[promotion]
+----------------------------------PhysicalOlapScan[customer_address(ad1)]
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[customer_address(ad2)]
------------------------PhysicalProject
---------------------------PhysicalOlapScan[customer_address(ad1)]
+--------------------------PhysicalOlapScan[income_band(ib1)]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[income_band(ib2)]
----------------PhysicalProject
-------------------PhysicalOlapScan[date_dim(d3)]
+------------------filter((item.i_current_price <= 58.00) and
(item.i_current_price >= 49.00) and i_color IN ('blush', 'lace', 'lawn',
'misty', 'orange', 'pink'))
+--------------------PhysicalOlapScan[item]
--PhysicalResultSink
----PhysicalQuickSort[MERGE_SORT]
------PhysicalDistribute[DistributionSpecGather]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query69.out
b/regression-test/data/shape_check/tpcds_sf1000/shape/query69.out
index 175f24b8cbd..953d0237ca5 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/shape/query69.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/shape/query69.out
@@ -9,39 +9,39 @@ PhysicalResultSink
------------PhysicalDistribute[DistributionSpecHash]
--------------hashAgg[LOCAL]
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF6 c_customer_sk->[ss_customer_sk]
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF7 c_customer_sk->[ss_customer_sk]
--------------------PhysicalProject
-----------------------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]
+----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF6 d_date_sk->[ss_sold_date_sk]
------------------------PhysicalProject
---------------------------PhysicalOlapScan[store_sales] apply RFs: RF5 RF6
+--------------------------PhysicalOlapScan[store_sales] apply RFs: RF6 RF7
------------------------PhysicalProject
--------------------------filter((date_dim.d_moy <= 3) and (date_dim.d_moy >=
1) and (date_dim.d_year = 2002))
----------------------------PhysicalOlapScan[date_dim]
---------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=() build RFs:RF4 c_customer_sk->[cs_ship_customer_sk]
+--------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[cs_ship_customer_sk]
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF3 d_date_sk->[cs_sold_date_sk]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[cs_sold_date_sk]
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF3 RF4
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF4 RF5
--------------------------PhysicalProject
----------------------------filter((date_dim.d_moy <= 3) and (date_dim.d_moy
>= 1) and (date_dim.d_year = 2002))
------------------------------PhysicalOlapScan[date_dim]
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF2 c_current_cdemo_sk->[cd_demo_sk]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF3 c_current_cdemo_sk->[cd_demo_sk]
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[customer_demographics] apply RFs:
RF2
---------------------------hashJoin[LEFT_ANTI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
+----------------------------PhysicalOlapScan[customer_demographics] apply RFs:
RF3
+--------------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=() build RFs:RF2 c_customer_sk->[ws_bill_customer_sk]
----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF1 ca_address_sk->[c_current_addr_sk]
+------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF1 d_date_sk->[ws_sold_date_sk]
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[customer(c)] apply RFs: RF1
+----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF1
RF2
--------------------------------PhysicalProject
-----------------------------------filter(ca_state IN ('IL', 'ME', 'TX'))
-------------------------------------PhysicalOlapScan[customer_address(ca)]
+----------------------------------filter((date_dim.d_moy <= 3) and
(date_dim.d_moy >= 1) and (date_dim.d_year = 2002))
+------------------------------------PhysicalOlapScan[date_dim]
----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF0 d_date_sk->[ws_sold_date_sk]
+------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF0 ca_address_sk->[c_current_addr_sk]
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0
+----------------------------------PhysicalOlapScan[customer(c)] apply RFs: RF0
--------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_moy <= 3) and
(date_dim.d_moy >= 1) and (date_dim.d_year = 2002))
-------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------filter(ca_state IN ('IL', 'ME', 'TX'))
+------------------------------------PhysicalOlapScan[customer_address(ca)]
diff --git
a/regression-test/data/shape_check/tpch_sf1000/check_point/probeShortcutFactor.out
b/regression-test/data/shape_check/tpch_sf1000/check_point/probeShortcutFactor.out
new file mode 100644
index 00000000000..c11e94e4777
--- /dev/null
+++
b/regression-test/data/shape_check/tpch_sf1000/check_point/probeShortcutFactor.out
@@ -0,0 +1,10 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalResultSink
+--hashJoin[RIGHT_ANTI_JOIN shuffle] hashCondition=((orders.o_custkey =
customer.c_custkey)) otherCondition=() build RFs:RF0 c_custkey->[o_custkey]
+----PhysicalProject
+------PhysicalOlapScan[orders] apply RFs: RF0
+----PhysicalProject
+------filter(substring(c_phone, 1, 2) IN ('13', '17', '18', '23', '29', '30',
'31'))
+--------PhysicalOlapScan[customer]
+
diff --git a/regression-test/data/shape_check/tpch_sf1000/rf_prune/q22.out
b/regression-test/data/shape_check/tpch_sf1000/rf_prune/q22.out
index 69a80f708f9..5f75b319bf0 100644
--- a/regression-test/data/shape_check/tpch_sf1000/rf_prune/q22.out
+++ b/regression-test/data/shape_check/tpch_sf1000/rf_prune/q22.out
@@ -8,18 +8,18 @@ PhysicalResultSink
----------PhysicalDistribute[DistributionSpecHash]
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------NestedLoopJoin[INNER_JOIN](cast(c_acctbal as DECIMALV3(38, 4))
> avg(c_acctbal))
+----------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((orders.o_custkey = customer.c_custkey)) otherCondition=() build
RFs:RF0 c_custkey->[o_custkey]
------------------PhysicalProject
---------------------hashJoin[LEFT_ANTI_JOIN bucketShuffle]
hashCondition=((orders.o_custkey = customer.c_custkey)) otherCondition=()
+--------------------PhysicalOlapScan[orders] apply RFs: RF0
+------------------PhysicalProject
+--------------------NestedLoopJoin[INNER_JOIN](cast(c_acctbal as DECIMALV3(38,
4)) > avg(c_acctbal))
----------------------PhysicalProject
------------------------filter(substring(c_phone, 1, 2) IN ('13', '17', '18',
'23', '29', '30', '31'))
--------------------------PhysicalOlapScan[customer]
-----------------------PhysicalProject
-------------------------PhysicalOlapScan[orders]
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecGather]
-----------------------hashAgg[LOCAL]
-------------------------PhysicalProject
---------------------------filter((customer.c_acctbal > 0.00) and
substring(c_phone, 1, 2) IN ('13', '17', '18', '23', '29', '30', '31'))
-----------------------------PhysicalOlapScan[customer]
+----------------------hashAgg[GLOBAL]
+------------------------PhysicalDistribute[DistributionSpecGather]
+--------------------------hashAgg[LOCAL]
+----------------------------PhysicalProject
+------------------------------filter((customer.c_acctbal > 0.00) and
substring(c_phone, 1, 2) IN ('13', '17', '18', '23', '29', '30', '31'))
+--------------------------------PhysicalOlapScan[customer]
diff --git a/regression-test/data/shape_check/tpch_sf1000/shape/q22.out
b/regression-test/data/shape_check/tpch_sf1000/shape/q22.out
index 69a80f708f9..5f75b319bf0 100644
--- a/regression-test/data/shape_check/tpch_sf1000/shape/q22.out
+++ b/regression-test/data/shape_check/tpch_sf1000/shape/q22.out
@@ -8,18 +8,18 @@ PhysicalResultSink
----------PhysicalDistribute[DistributionSpecHash]
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------NestedLoopJoin[INNER_JOIN](cast(c_acctbal as DECIMALV3(38, 4))
> avg(c_acctbal))
+----------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((orders.o_custkey = customer.c_custkey)) otherCondition=() build
RFs:RF0 c_custkey->[o_custkey]
------------------PhysicalProject
---------------------hashJoin[LEFT_ANTI_JOIN bucketShuffle]
hashCondition=((orders.o_custkey = customer.c_custkey)) otherCondition=()
+--------------------PhysicalOlapScan[orders] apply RFs: RF0
+------------------PhysicalProject
+--------------------NestedLoopJoin[INNER_JOIN](cast(c_acctbal as DECIMALV3(38,
4)) > avg(c_acctbal))
----------------------PhysicalProject
------------------------filter(substring(c_phone, 1, 2) IN ('13', '17', '18',
'23', '29', '30', '31'))
--------------------------PhysicalOlapScan[customer]
-----------------------PhysicalProject
-------------------------PhysicalOlapScan[orders]
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute[DistributionSpecGather]
-----------------------hashAgg[LOCAL]
-------------------------PhysicalProject
---------------------------filter((customer.c_acctbal > 0.00) and
substring(c_phone, 1, 2) IN ('13', '17', '18', '23', '29', '30', '31'))
-----------------------------PhysicalOlapScan[customer]
+----------------------hashAgg[GLOBAL]
+------------------------PhysicalDistribute[DistributionSpecGather]
+--------------------------hashAgg[LOCAL]
+----------------------------PhysicalProject
+------------------------------filter((customer.c_acctbal > 0.00) and
substring(c_phone, 1, 2) IN ('13', '17', '18', '23', '29', '30', '31'))
+--------------------------------PhysicalOlapScan[customer]
diff --git
a/regression-test/suites/shape_check/tpch_sf1000/check_point/probeShortcutFactor.groovy
b/regression-test/suites/shape_check/tpch_sf1000/check_point/probeShortcutFactor.groovy
new file mode 100644
index 00000000000..5f140c93bb3
--- /dev/null
+++
b/regression-test/suites/shape_check/tpch_sf1000/check_point/probeShortcutFactor.groovy
@@ -0,0 +1,44 @@
+/*
+ * 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("probeShortcutFactor") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ if (isCloudMode()) {
+ return
+ }
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_nereids_distribute_planner=false'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+ sql 'set parallel_pipeline_task_num=8'
+ sql 'set be_number_for_test=3'
+ sql "set runtime_filter_type=8"
+ sql 'set enable_runtime_filter_prune=false'
+ sql "set disable_nereids_rules=PRUNE_EMPTY_PARTITION"
+
+ // should use right anti join, not left anti join
+ qt_select """
+ explain shape plan
+ select c_custkey, c_phone, c_acctbal
+ from customer left anti join orders on o_custkey=c_custkey
+ where substring(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18',
'17') ;
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
