You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2024/03/06 05:09:38 UTC
(doris) 27/47: [test](leading) add leading tpc-ds regression test cases (#31681)
This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
commit 97640ee0e89138a2950a929198d3f16993053a58
Author: LiBinfeng <46...@users.noreply.github.com>
AuthorDate: Tue Mar 5 17:04:09 2024 +0800
[test](leading) add leading tpc-ds regression test cases (#31681)
Co-authored-by: libinfeng <li...@selectdb.com>
---
.../rules/analysis/CollectJoinConstraint.java | 2 +-
.../data/nereids_hint_tpcds_p0/shape/query1.out | 47 ++
.../data/nereids_hint_tpcds_p0/shape/query24.out | 65 ++
.../data/nereids_hint_tpcds_p0/shape/query64.out | 139 ++++
.../data/nereids_hint_tpcds_p0/shape/query67.out | 40 +
.../data/nereids_hint_tpcds_p0/shape/query72.out | 73 ++
.../data/nereids_hint_tpcds_p0/shape/query78.out | 65 ++
.../suites/nereids_hint_tpcds_p0/ddl/gen_shape.py | 26 +
.../suites/nereids_hint_tpcds_p0/ddl/shape.tmpl | 39 +
.../suites/nereids_hint_tpcds_p0/load.groovy | 809 +++++++++++++++++++++
.../nereids_hint_tpcds_p0/shape/query1.groovy | 84 +++
.../nereids_hint_tpcds_p0/shape/query24.groovy | 143 ++++
.../nereids_hint_tpcds_p0/shape/query64.groovy | 279 +++++++
.../nereids_hint_tpcds_p0/shape/query67.groovy | 121 +++
.../nereids_hint_tpcds_p0/shape/query72.groovy | 91 +++
.../nereids_hint_tpcds_p0/shape/query78.groovy | 154 ++++
16 files changed, 2176 insertions(+), 1 deletion(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/CollectJoinConstraint.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/CollectJoinConstraint.java
index 6948bc61b68..ea68f260685 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/CollectJoinConstraint.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/CollectJoinConstraint.java
@@ -105,7 +105,7 @@ public class CollectJoinConstraint implements RewriteRuleFactory {
private void collectJoinConstraintList(LeadingHint leading, Long leftHand, Long rightHand, LogicalJoin join,
Long filterTableBitMap, Long nonNullableSlotBitMap) {
Long totalTables = LongBitmap.or(leftHand, rightHand);
- if (join.getJoinType().isInnerJoin()) {
+ if (join.getJoinType().isInnerOrCrossJoin()) {
leading.setInnerJoinBitmap(LongBitmap.or(leading.getInnerJoinBitmap(), totalTables));
return;
}
diff --git a/regression-test/data/nereids_hint_tpcds_p0/shape/query1.out b/regression-test/data/nereids_hint_tpcds_p0/shape/query1.out
new file mode 100644
index 00000000000..e0104b54a42
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpcds_p0/shape/query1.out
@@ -0,0 +1,47 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !ds_shape_1 --
+PhysicalCteAnchor ( cteId=CTEId#0 )
+--PhysicalCteProducer ( cteId=CTEId#0 )
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute[DistributionSpecHash]
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((store_returns.sr_returned_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[sr_returned_date_sk]
+----------------PhysicalProject
+------------------PhysicalOlapScan[store_returns] apply RFs: RF0
+----------------PhysicalDistribute[DistributionSpecReplicated]
+------------------PhysicalProject
+--------------------filter((date_dim.d_year = 2000))
+----------------------PhysicalOlapScan[date_dim]
+--PhysicalResultSink
+----PhysicalTopN[MERGE_SORT]
+------PhysicalDistribute[DistributionSpecGather]
+--------PhysicalTopN[LOCAL_SORT]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((ctr1.ctr_store_sk = ctr2.ctr_store_sk)) otherCondition=((cast(ctr_total_return as DOUBLE) > cast((avg(cast(ctr_total_return as DECIMALV3(38, 4))) * 1.2) as DOUBLE)))
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute[DistributionSpecHash]
+------------------hashAgg[LOCAL]
+--------------------PhysicalDistribute[DistributionSpecExecutionAny]
+----------------------PhysicalProject
+------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = ctr1.ctr_store_sk)) otherCondition=() build RFs:RF1 s_store_sk->[ctr_store_sk]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashJoin[INNER_JOIN] hashCondition=((ctr1.ctr_customer_sk = customer.c_customer_sk)) otherCondition=()
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[customer]
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF1
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------PhysicalProject
+----------------------filter((store.s_state = 'TN'))
+------------------------PhysicalOlapScan[store]
+
+Hint log:
+Used: leading(store_returns broadcast date_dim )
+UnUsed:
+SyntaxError:
+
diff --git a/regression-test/data/nereids_hint_tpcds_p0/shape/query24.out b/regression-test/data/nereids_hint_tpcds_p0/shape/query24.out
new file mode 100644
index 00000000000..01ffbef3010
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpcds_p0/shape/query24.out
@@ -0,0 +1,65 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !ds_shape_24 --
+PhysicalCteAnchor ( cteId=CTEId#0 )
+--PhysicalCteProducer ( cteId=CTEId#0 )
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute[DistributionSpecHash]
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] 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:RF5 sr_item_sk->[i_item_sk,ss_item_sk];RF6 sr_ticket_number->[ss_ticket_number]
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF4 i_item_sk->[ss_item_sk]
+--------------------PhysicalDistribute[DistributionSpecHash]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN] hashCondition=((store.s_zip = customer_address.ca_zip) and (store_sales.ss_customer_sk = customer.c_customer_sk)) otherCondition=() build RFs:RF2 ca_zip->[s_zip];RF3 c_customer_sk->[ss_customer_sk]
+--------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------PhysicalProject
+------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=() build RFs:RF1 s_store_sk->[ss_store_sk]
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[store_sales] apply RFs: RF1 RF3 RF4 RF5 RF6
+--------------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------------------PhysicalProject
+------------------------------------filter((store.s_market_id = 5))
+--------------------------------------PhysicalOlapScan[store] apply RFs: RF2
+--------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------PhysicalProject
+------------------------------hashJoin[INNER_JOIN] hashCondition=((customer.c_current_addr_sk = customer_address.ca_address_sk)) otherCondition=(( not (c_birth_country = upper(ca_country)))) build RFs:RF0 ca_address_sk->[c_current_addr_sk]
+--------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[customer] apply RFs: RF0
+--------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[customer_address]
+--------------------PhysicalDistribute[DistributionSpecHash]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[item] apply RFs: RF5
+----------------PhysicalDistribute[DistributionSpecHash]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[store_returns]
+--PhysicalResultSink
+----PhysicalQuickSort[MERGE_SORT]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------NestedLoopJoin[INNER_JOIN](cast(paid as DOUBLE) > cast((0.05 * avg(cast(netpaid as DECIMALV3(38, 4)))) as DOUBLE))
+------------PhysicalProject
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute[DistributionSpecGather]
+------------------hashAgg[LOCAL]
+--------------------PhysicalDistribute[DistributionSpecExecutionAny]
+----------------------PhysicalProject
+------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------PhysicalDistribute[DistributionSpecReplicated]
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute[DistributionSpecHash]
+------------------hashAgg[LOCAL]
+--------------------PhysicalDistribute[DistributionSpecExecutionAny]
+----------------------PhysicalProject
+------------------------filter((ssales.i_color = 'aquamarine'))
+--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+
+Hint log:
+Used: leading(store_sales broadcast store { customer shuffle customer_address } shuffle item shuffle store_returns )
+UnUsed: [shuffle]_3
+SyntaxError:
+
diff --git a/regression-test/data/nereids_hint_tpcds_p0/shape/query64.out b/regression-test/data/nereids_hint_tpcds_p0/shape/query64.out
new file mode 100644
index 00000000000..a4d47cb3a81
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpcds_p0/shape/query64.out
@@ -0,0 +1,139 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !ds_shape_64 --
+PhysicalCteAnchor ( cteId=CTEId#1 )
+--PhysicalCteProducer ( cteId=CTEId#1 )
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute[DistributionSpecHash]
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_item_sk = cs_ui.cs_item_sk)) otherCondition=() build RFs:RF19 cs_item_sk->[ss_item_sk,i_item_sk,sr_item_sk]
+----------------PhysicalDistribute[DistributionSpecHash]
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_promo_sk = promotion.p_promo_sk)) otherCondition=() build RFs:RF18 p_promo_sk->[ss_promo_sk]
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=() build RFs:RF17 s_store_sk->[ss_store_sk]
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk)) otherCondition=() build RFs:RF16 d_date_sk->[ss_sold_date_sk]
+----------------------------------PhysicalProject
+------------------------------------hashJoin[INNER_JOIN] hashCondition=((hd2.hd_income_band_sk = ib2.ib_income_band_sk)) otherCondition=() build RFs:RF15 ib_income_band_sk->[hd_income_band_sk]
+--------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------PhysicalProject
+------------------------------------------hashJoin[INNER_JOIN] hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk)) otherCondition=() build RFs:RF14 ca_address_sk->[c_current_addr_sk]
+--------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------PhysicalProject
+------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((customer.c_current_hdemo_sk = hd2.hd_demo_sk)) otherCondition=() build RFs:RF13 hd_demo_sk->[c_current_hdemo_sk]
+--------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------PhysicalProject
+------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=() build RFs:RF12 ca_address_sk->[ss_addr_sk]
+--------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------------PhysicalProject
+------------------------------------------------------------hashJoin[INNER_JOIN] 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 sr_item_sk->[ss_item_sk,i_item_sk];RF11 sr_ticket_number->[ss_ticket_number]
+--------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk)) otherCondition=() build RFs:RF9 hd_demo_sk->[ss_hdemo_sk]
+--------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF8 i_item_sk->[ss_item_sk]
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk)) otherCondition=() build RFs:RF7 d_date_sk->[c_first_shipto_date_sk]
+----------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk)) otherCondition=(( not (cd_marital_status = cd_marital_status))) build RFs:RF6 cd_demo_sk->[ss_cdemo_sk]
+----------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk)) otherCondition=() build RFs:RF5 c_customer_sk->[ss_customer_sk]
+----------------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF5 RF6 RF8 RF9 RF10 RF11 RF12 RF16 RF17 RF18 RF19
+----------------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk)) otherCondition=() build RFs:RF4 cd_demo_sk->[c_current_cdemo_sk]
+----------------------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk)) otherCondition=() build RFs:RF3 d_date_sk->[c_first_sales_date_sk]
+----------------------------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------------------PhysicalOlapScan[customer] apply RFs: RF3 RF4 RF7 RF13 RF14
+----------------------------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------------------PhysicalOlapScan[customer_demographics]
+----------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------------PhysicalOlapScan[customer_demographics]
+----------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------------------------------------------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] apply RFs: RF10 RF19
+--------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((hd1.hd_income_band_sk = ib1.ib_income_band_sk)) otherCondition=() build RFs:RF2 ib_income_band_sk->[hd_income_band_sk]
+--------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------------PhysicalOlapScan[household_demographics] apply RFs: RF2
+--------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------------PhysicalOlapScan[income_band]
+--------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------PhysicalOlapScan[store_returns] apply RFs: RF19
+--------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------------PhysicalProject
+------------------------------------------------------------PhysicalOlapScan[customer_address]
+--------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------------PhysicalProject
+------------------------------------------------------PhysicalOlapScan[household_demographics] apply RFs: RF15
+--------------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------------PhysicalProject
+------------------------------------------------PhysicalOlapScan[customer_address]
+--------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------PhysicalProject
+------------------------------------------PhysicalOlapScan[income_band]
+----------------------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------------------PhysicalProject
+--------------------------------------filter(d_year IN (1999, 2000))
+----------------------------------------PhysicalOlapScan[date_dim]
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[store]
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[promotion]
+----------------PhysicalProject
+------------------filter((sale > (2 * refund)))
+--------------------hashAgg[GLOBAL]
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------hashAgg[LOCAL]
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN] 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_order_number->[cs_order_number];RF1 cr_item_sk->[cs_item_sk]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0 RF1
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[catalog_returns]
+--PhysicalResultSink
+----PhysicalQuickSort[MERGE_SORT]
+------PhysicalDistribute[DistributionSpecGather]
+--------PhysicalQuickSort[LOCAL_SORT]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((cs1.item_sk = cs2.item_sk) and (cs1.store_name = cs2.store_name) and (cs1.store_zip = cs2.store_zip)) otherCondition=((cs2.cnt <= cs1.cnt))
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------PhysicalProject
+------------------filter((cs1.syear = 1999))
+--------------------PhysicalCteConsumer ( cteId=CTEId#1 )
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------PhysicalProject
+------------------filter((cs2.syear = 2000))
+--------------------PhysicalCteConsumer ( cteId=CTEId#1 )
+
+Hint log:
+Used: leading(catalog_sales shuffle catalog_returns ) leading({ store_sales { { customer d2 } cd2 } } cd1 d3 item { hd1 ib1 } store_returns ad1 hd2 ad2 ib2 d1 store promotion cs_ui ) leading(cs1 shuffle cs2 )
+UnUsed:
+SyntaxError:
+
diff --git a/regression-test/data/nereids_hint_tpcds_p0/shape/query67.out b/regression-test/data/nereids_hint_tpcds_p0/shape/query67.out
new file mode 100644
index 00000000000..68a3c5105ed
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpcds_p0/shape/query67.out
@@ -0,0 +1,40 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !ds_shape_67 --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalTopN[LOCAL_SORT]
+--------filter((rk <= 100))
+----------PhysicalWindow
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalDistribute[DistributionSpecHash]
+----------------PhysicalPartitionTopN
+------------------PhysicalProject
+--------------------hashAgg[GLOBAL]
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------hashAgg[LOCAL]
+--------------------------PhysicalRepeat
+----------------------------PhysicalProject
+------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF2 i_item_sk->[ss_item_sk]
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=() build RFs:RF1 s_store_sk->[ss_store_sk]
+------------------------------------PhysicalProject
+--------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
+----------------------------------------PhysicalProject
+------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1 RF2
+----------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------------------------PhysicalProject
+--------------------------------------------filter((date_dim.d_month_seq <= 1228) and (date_dim.d_month_seq >= 1217))
+----------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[store]
+--------------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[item]
+
+Hint log:
+Used: leading(store_sales broadcast date_dim broadcast store broadcast item )
+UnUsed:
+SyntaxError:
+
diff --git a/regression-test/data/nereids_hint_tpcds_p0/shape/query72.out b/regression-test/data/nereids_hint_tpcds_p0/shape/query72.out
new file mode 100644
index 00000000000..0b68e3e7f8d
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpcds_p0/shape/query72.out
@@ -0,0 +1,73 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !ds_shape_72 --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalTopN[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute[DistributionSpecHash]
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN] hashCondition=((warehouse.w_warehouse_sk = inventory.inv_warehouse_sk)) otherCondition=() build RFs:RF10 w_warehouse_sk->[inv_warehouse_sk]
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN] hashCondition=((catalog_sales.cs_item_sk = inventory.inv_item_sk) and (inventory.inv_date_sk = d2.d_date_sk)) otherCondition=((inventory.inv_quantity_on_hand < catalog_sales.cs_quantity)) build RFs:RF8 d_date_sk->[inv_date_sk];RF9 cs_item_sk->[inv_item_sk]
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------PhysicalOlapScan[inventory] apply RFs: RF8 RF9 RF10
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN] hashCondition=((d1.d_week_seq = d2.d_week_seq)) otherCondition=() build RFs:RF7 d_week_seq->[d_week_seq]
+----------------------------PhysicalProject
+------------------------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((catalog_returns.cr_item_sk = catalog_sales.cs_item_sk) and (catalog_returns.cr_order_number = catalog_sales.cs_order_number)) otherCondition=() build RFs:RF5 cs_order_number->[cr_order_number];RF6 cs_item_sk->[cr_item_sk]
+--------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[catalog_returns] apply RFs: RF5 RF6
+--------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------PhysicalProject
+------------------------------------hashJoin[INNER_JOIN] hashCondition=((item.i_item_sk = catalog_sales.cs_item_sk)) otherCondition=() build RFs:RF4 i_item_sk->[cs_item_sk]
+--------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------PhysicalProject
+------------------------------------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((catalog_sales.cs_promo_sk = promotion.p_promo_sk)) otherCondition=()
+--------------------------------------------PhysicalProject
+----------------------------------------------hashJoin[INNER_JOIN] hashCondition=((catalog_sales.cs_bill_cdemo_sk = customer_demographics.cd_demo_sk)) otherCondition=() build RFs:RF3 cd_demo_sk->[cs_bill_cdemo_sk]
+------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+--------------------------------------------------PhysicalProject
+----------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((catalog_sales.cs_bill_hdemo_sk = household_demographics.hd_demo_sk)) otherCondition=() build RFs:RF2 hd_demo_sk->[cs_bill_hdemo_sk]
+------------------------------------------------------PhysicalProject
+--------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((catalog_sales.cs_ship_date_sk = d3.d_date_sk) and (catalog_sales.cs_sold_date_sk = d1.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[cs_ship_date_sk];RF1 d_date_sk->[cs_sold_date_sk]
+----------------------------------------------------------PhysicalProject
+------------------------------------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0 RF1 RF2 RF3 RF4
+----------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------NestedLoopJoin[INNER_JOIN](d3.d_date > days_add(d_date, INTERVAL 5 DAY))
+----------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------filter((d1.d_year = 1998))
+----------------------------------------------------------------------PhysicalOlapScan[date_dim] apply RFs: RF7
+------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------filter((household_demographics.hd_buy_potential = '1001-5000'))
+------------------------------------------------------------PhysicalOlapScan[household_demographics]
+------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+--------------------------------------------------PhysicalProject
+----------------------------------------------------filter((customer_demographics.cd_marital_status = 'S'))
+------------------------------------------------------PhysicalOlapScan[customer_demographics]
+--------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------------------------------PhysicalProject
+------------------------------------------------PhysicalOlapScan[promotion]
+--------------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------------PhysicalProject
+------------------------------------------PhysicalOlapScan[item]
+----------------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[date_dim]
+------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[warehouse]
+
+Hint log:
+Used: leading(inventory { catalog_returns shuffle { catalog_sales shuffle { d3 broadcast d1 } broadcast household_demographics shuffle customer_demographics broadcast promotion shuffle item } broadcast d2 } broadcast warehouse )
+UnUsed: [shuffle]_2
+SyntaxError:
+
diff --git a/regression-test/data/nereids_hint_tpcds_p0/shape/query78.out b/regression-test/data/nereids_hint_tpcds_p0/shape/query78.out
new file mode 100644
index 00000000000..cea291a3d98
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpcds_p0/shape/query78.out
@@ -0,0 +1,65 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !ds_shape_78 --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------filter(((coalesce(ws_qty, 0) > 0) OR (coalesce(cs_qty, 0) > 0)))
+------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((cs.cs_customer_sk = ss.ss_customer_sk) and (cs.cs_item_sk = ss.ss_item_sk) and (cs.cs_sold_year = ss.ss_sold_year)) otherCondition=()
+--------------PhysicalProject
+----------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((ws.ws_customer_sk = ss.ss_customer_sk) and (ws.ws_item_sk = ss.ss_item_sk) and (ws.ws_sold_year = ss.ss_sold_year)) otherCondition=()
+------------------PhysicalProject
+--------------------hashAgg[GLOBAL]
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------hashAgg[LOCAL]
+--------------------------PhysicalProject
+----------------------------hashJoin[LEFT_ANTI_JOIN] hashCondition=((store_returns.sr_ticket_number = store_sales.ss_ticket_number) and (store_sales.ss_item_sk = store_returns.sr_item_sk)) otherCondition=()
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk]
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF2
+----------------------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------------------PhysicalProject
+--------------------------------------filter((date_dim.d_year = 1998))
+----------------------------------------PhysicalOlapScan[date_dim]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[store_returns]
+------------------PhysicalProject
+--------------------hashAgg[GLOBAL]
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------hashAgg[LOCAL]
+--------------------------PhysicalProject
+----------------------------hashJoin[LEFT_ANTI_JOIN] hashCondition=((web_returns.wr_order_number = web_sales.ws_order_number) and (web_sales.ws_item_sk = web_returns.wr_item_sk)) otherCondition=()
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[ws_sold_date_sk]
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[web_sales] apply RFs: RF1
+----------------------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------------------PhysicalProject
+--------------------------------------filter((date_dim.d_year = 1998))
+----------------------------------------PhysicalOlapScan[date_dim]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[web_returns]
+--------------PhysicalProject
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------hashJoin[LEFT_ANTI_JOIN] hashCondition=((catalog_returns.cr_order_number = catalog_sales.cs_order_number) and (catalog_sales.cs_item_sk = catalog_returns.cr_item_sk)) otherCondition=()
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN] 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
+------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------PhysicalProject
+----------------------------------filter((date_dim.d_year = 1998))
+------------------------------------PhysicalOlapScan[date_dim]
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[catalog_returns]
+
+Hint log:
+Used: leading(web_sales broadcast date_dim web_returns ) leading(catalog_sales broadcast date_dim catalog_returns ) leading(store_sales broadcast date_dim store_returns ) leading(ss shuffle ws shuffle cs )
+UnUsed:
+SyntaxError:
+
diff --git a/regression-test/suites/nereids_hint_tpcds_p0/ddl/gen_shape.py b/regression-test/suites/nereids_hint_tpcds_p0/ddl/gen_shape.py
new file mode 100644
index 00000000000..8317bd1859f
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpcds_p0/ddl/gen_shape.py
@@ -0,0 +1,26 @@
+# // 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.
+if __name__ == '__main__':
+ with open('shape.tmpl', 'r') as f:
+ tmpl = f.read()
+ for i in range(1,100):
+ with open('../../../../tools/tpcds-tools/queries/sf1000/query'+str(i)+'.sql', 'r') as fi:
+ casei = tmpl.replace('{--}', str(i))
+ casei = casei.replace('{query}', fi.read().split(";")[0])
+
+ with open('../shape/query'+str(i)+'.groovy', 'w') as out:
+ out.write(casei)
\ No newline at end of file
diff --git a/regression-test/suites/nereids_hint_tpcds_p0/ddl/shape.tmpl b/regression-test/suites/nereids_hint_tpcds_p0/ddl/shape.tmpl
new file mode 100644
index 00000000000..d172804f48f
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpcds_p0/ddl/shape.tmpl
@@ -0,0 +1,39 @@
+/*
+ * 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("query{--}") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql 'set exec_mem_limit=21G'
+ sql 'set be_number_for_test=3'
+ sql 'set parallel_fragment_exec_instance_num=8; '
+ sql 'set parallel_pipeline_task_num=8; '
+ sql 'set forbid_unknown_col_stats=true'
+ sql 'set enable_nereids_timeout = false'
+ sql 'set enable_runtime_filter_prune=false'
+ sql 'set runtime_filter_type=8'
+ sql 'set dump_nereids_memo=false'
+ def ds = """{query}"""
+ qt_ds_shape_{--} '''
+ explain shape plan
+ {query}
+ '''
+}
diff --git a/regression-test/suites/nereids_hint_tpcds_p0/load.groovy b/regression-test/suites/nereids_hint_tpcds_p0/load.groovy
new file mode 100644
index 00000000000..73fa7a278be
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpcds_p0/load.groovy
@@ -0,0 +1,809 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("load") {
+ String database = context.config.getDbNameByFile(context.file)
+ sql "drop database if exists ${database}"
+ sql "create database ${database}"
+ sql "use ${database}"
+
+ sql '''
+ drop table if exists customer_demographics
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS customer_demographics (
+ cd_demo_sk bigint not null,
+ cd_gender char(1),
+ cd_marital_status char(1),
+ cd_education_status char(20),
+ cd_purchase_estimate integer,
+ cd_credit_rating char(10),
+ cd_dep_count integer,
+ cd_dep_employed_count integer,
+ cd_dep_college_count integer
+ )
+ DUPLICATE KEY(cd_demo_sk)
+ DISTRIBUTED BY HASH(cd_gender) BUCKETS 12
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists reason
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS reason (
+ r_reason_sk bigint not null,
+ r_reason_id char(16) not null,
+ r_reason_desc char(100)
+ )
+ DUPLICATE KEY(r_reason_sk)
+ DISTRIBUTED BY HASH(r_reason_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists date_dim
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS date_dim (
+ d_date_sk bigint not null,
+ d_date_id char(16) not null,
+ d_date datev2,
+ d_month_seq integer,
+ d_week_seq integer,
+ d_quarter_seq integer,
+ d_year integer,
+ d_dow integer,
+ d_moy integer,
+ d_dom integer,
+ d_qoy integer,
+ d_fy_year integer,
+ d_fy_quarter_seq integer,
+ d_fy_week_seq integer,
+ d_day_name char(9),
+ d_quarter_name char(6),
+ d_holiday char(1),
+ d_weekend char(1),
+ d_following_holiday char(1),
+ d_first_dom integer,
+ d_last_dom integer,
+ d_same_day_ly integer,
+ d_same_day_lq integer,
+ d_current_day char(1),
+ d_current_week char(1),
+ d_current_month char(1),
+ d_current_quarter char(1),
+ d_current_year char(1)
+ )
+ DUPLICATE KEY(d_date_sk)
+ DISTRIBUTED BY HASH(d_date_sk) BUCKETS 12
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists warehouse
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS warehouse (
+ w_warehouse_sk bigint not null,
+ w_warehouse_id char(16) not null,
+ w_warehouse_name varchar(20),
+ w_warehouse_sq_ft integer,
+ w_street_number char(10),
+ w_street_name varchar(60),
+ w_street_type char(15),
+ w_suite_number char(10),
+ w_city varchar(60),
+ w_county varchar(30),
+ w_state char(2),
+ w_zip char(10),
+ w_country varchar(20),
+ w_gmt_offset decimalv3(5,2)
+ )
+ DUPLICATE KEY(w_warehouse_sk)
+ DISTRIBUTED BY HASH(w_warehouse_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists catalog_sales
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS catalog_sales (
+ cs_sold_date_sk bigint,
+ cs_item_sk bigint not null,
+ cs_order_number bigint not null,
+ cs_sold_time_sk bigint,
+ cs_ship_date_sk bigint,
+ cs_bill_customer_sk bigint,
+ cs_bill_cdemo_sk bigint,
+ cs_bill_hdemo_sk bigint,
+ cs_bill_addr_sk bigint,
+ cs_ship_customer_sk bigint,
+ cs_ship_cdemo_sk bigint,
+ cs_ship_hdemo_sk bigint,
+ cs_ship_addr_sk bigint,
+ cs_call_center_sk bigint,
+ cs_catalog_page_sk bigint,
+ cs_ship_mode_sk bigint,
+ cs_warehouse_sk bigint,
+ cs_promo_sk bigint,
+ cs_quantity integer,
+ cs_wholesale_cost decimalv3(7,2),
+ cs_list_price decimalv3(7,2),
+ cs_sales_price decimalv3(7,2),
+ cs_ext_discount_amt decimalv3(7,2),
+ cs_ext_sales_price decimalv3(7,2),
+ cs_ext_wholesale_cost decimalv3(7,2),
+ cs_ext_list_price decimalv3(7,2),
+ cs_ext_tax decimalv3(7,2),
+ cs_coupon_amt decimalv3(7,2),
+ cs_ext_ship_cost decimalv3(7,2),
+ cs_net_paid decimalv3(7,2),
+ cs_net_paid_inc_tax decimalv3(7,2),
+ cs_net_paid_inc_ship decimalv3(7,2),
+ cs_net_paid_inc_ship_tax decimalv3(7,2),
+ cs_net_profit decimalv3(7,2)
+ )
+ DUPLICATE KEY(cs_sold_date_sk, cs_item_sk)
+ DISTRIBUTED BY HASH(cs_item_sk, cs_order_number) BUCKETS 32
+ PROPERTIES (
+ "replication_num" = "1",
+ "colocate_with" = "catalog"
+ )
+ '''
+
+ sql '''
+ drop table if exists call_center
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS call_center (
+ cc_call_center_sk bigint not null,
+ cc_call_center_id char(16) not null,
+ cc_rec_start_date datev2,
+ cc_rec_end_date datev2,
+ cc_closed_date_sk integer,
+ cc_open_date_sk integer,
+ cc_name varchar(50),
+ cc_class varchar(50),
+ cc_employees integer,
+ cc_sq_ft integer,
+ cc_hours char(20),
+ cc_manager varchar(40),
+ cc_mkt_id integer,
+ cc_mkt_class char(50),
+ cc_mkt_desc varchar(100),
+ cc_market_manager varchar(40),
+ cc_division integer,
+ cc_division_name varchar(50),
+ cc_company integer,
+ cc_company_name char(50),
+ cc_street_number char(10),
+ cc_street_name varchar(60),
+ cc_street_type char(15),
+ cc_suite_number char(10),
+ cc_city varchar(60),
+ cc_county varchar(30),
+ cc_state char(2),
+ cc_zip char(10),
+ cc_country varchar(20),
+ cc_gmt_offset decimalv3(5,2),
+ cc_tax_percentage decimalv3(5,2)
+ )
+ DUPLICATE KEY(cc_call_center_sk)
+ DISTRIBUTED BY HASH(cc_call_center_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists inventory
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS inventory (
+ inv_date_sk bigint not null,
+ inv_item_sk bigint not null,
+ inv_warehouse_sk bigint,
+ inv_quantity_on_hand integer
+ )
+ DUPLICATE KEY(inv_date_sk, inv_item_sk, inv_warehouse_sk)
+ DISTRIBUTED BY HASH(inv_date_sk, inv_item_sk, inv_warehouse_sk) BUCKETS 32
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists catalog_returns
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS catalog_returns (
+ cr_item_sk bigint not null,
+ cr_order_number bigint not null,
+ cr_returned_date_sk bigint,
+ cr_returned_time_sk bigint,
+ cr_refunded_customer_sk bigint,
+ cr_refunded_cdemo_sk bigint,
+ cr_refunded_hdemo_sk bigint,
+ cr_refunded_addr_sk bigint,
+ cr_returning_customer_sk bigint,
+ cr_returning_cdemo_sk bigint,
+ cr_returning_hdemo_sk bigint,
+ cr_returning_addr_sk bigint,
+ cr_call_center_sk bigint,
+ cr_catalog_page_sk bigint,
+ cr_ship_mode_sk bigint,
+ cr_warehouse_sk bigint,
+ cr_reason_sk bigint,
+ cr_return_quantity integer,
+ cr_return_amount decimalv3(7,2),
+ cr_return_tax decimalv3(7,2),
+ cr_return_amt_inc_tax decimalv3(7,2),
+ cr_fee decimalv3(7,2),
+ cr_return_ship_cost decimalv3(7,2),
+ cr_refunded_cash decimalv3(7,2),
+ cr_reversed_charge decimalv3(7,2),
+ cr_store_credit decimalv3(7,2),
+ cr_net_loss decimalv3(7,2)
+ )
+ DUPLICATE KEY(cr_item_sk, cr_order_number)
+ DISTRIBUTED BY HASH(cr_item_sk, cr_order_number) BUCKETS 32
+ PROPERTIES (
+ "replication_num" = "1",
+ "colocate_with" = "catalog"
+ )
+ '''
+
+ sql '''
+ drop table if exists household_demographics
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS household_demographics (
+ hd_demo_sk bigint not null,
+ hd_income_band_sk bigint,
+ hd_buy_potential char(15),
+ hd_dep_count integer,
+ hd_vehicle_count integer
+ )
+ DUPLICATE KEY(hd_demo_sk)
+ DISTRIBUTED BY HASH(hd_demo_sk) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists customer_address
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS customer_address (
+ ca_address_sk bigint not null,
+ ca_address_id char(16) not null,
+ ca_street_number char(10),
+ ca_street_name varchar(60),
+ ca_street_type char(15),
+ ca_suite_number char(10),
+ ca_city varchar(60),
+ ca_county varchar(30),
+ ca_state char(2),
+ ca_zip char(10),
+ ca_country varchar(20),
+ ca_gmt_offset decimalv3(5,2),
+ ca_location_type char(20)
+ )
+ DUPLICATE KEY(ca_address_sk)
+ DISTRIBUTED BY HASH(ca_address_sk) BUCKETS 12
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists income_band
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS income_band (
+ ib_income_band_sk bigint not null,
+ ib_lower_bound integer,
+ ib_upper_bound integer
+ )
+ DUPLICATE KEY(ib_income_band_sk)
+ DISTRIBUTED BY HASH(ib_income_band_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists catalog_page
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS catalog_page (
+ cp_catalog_page_sk bigint not null,
+ cp_catalog_page_id char(16) not null,
+ cp_start_date_sk integer,
+ cp_end_date_sk integer,
+ cp_department varchar(50),
+ cp_catalog_number integer,
+ cp_catalog_page_number integer,
+ cp_description varchar(100),
+ cp_type varchar(100)
+ )
+ DUPLICATE KEY(cp_catalog_page_sk)
+ DISTRIBUTED BY HASH(cp_catalog_page_sk) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists item
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS item (
+ i_item_sk bigint not null,
+ i_item_id char(16) not null,
+ i_rec_start_date datev2,
+ i_rec_end_date datev2,
+ i_item_desc varchar(200),
+ i_current_price decimalv3(7,2),
+ i_wholesale_cost decimalv3(7,2),
+ i_brand_id integer,
+ i_brand char(50),
+ i_class_id integer,
+ i_class char(50),
+ i_category_id integer,
+ i_category char(50),
+ i_manufact_id integer,
+ i_manufact char(50),
+ i_size char(20),
+ i_formulation char(20),
+ i_color char(20),
+ i_units char(10),
+ i_container char(10),
+ i_manager_id integer,
+ i_product_name char(50)
+ )
+ DUPLICATE KEY(i_item_sk)
+ DISTRIBUTED BY HASH(i_item_sk) BUCKETS 12
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists web_returns
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS web_returns (
+ wr_item_sk bigint not null,
+ wr_order_number bigint not null,
+ wr_returned_date_sk bigint,
+ wr_returned_time_sk bigint,
+ wr_refunded_customer_sk bigint,
+ wr_refunded_cdemo_sk bigint,
+ wr_refunded_hdemo_sk bigint,
+ wr_refunded_addr_sk bigint,
+ wr_returning_customer_sk bigint,
+ wr_returning_cdemo_sk bigint,
+ wr_returning_hdemo_sk bigint,
+ wr_returning_addr_sk bigint,
+ wr_web_page_sk bigint,
+ wr_reason_sk bigint,
+ wr_return_quantity integer,
+ wr_return_amt decimalv3(7,2),
+ wr_return_tax decimalv3(7,2),
+ wr_return_amt_inc_tax decimalv3(7,2),
+ wr_fee decimalv3(7,2),
+ wr_return_ship_cost decimalv3(7,2),
+ wr_refunded_cash decimalv3(7,2),
+ wr_reversed_charge decimalv3(7,2),
+ wr_account_credit decimalv3(7,2),
+ wr_net_loss decimalv3(7,2)
+ )
+ DUPLICATE KEY(wr_item_sk, wr_order_number)
+ DISTRIBUTED BY HASH(wr_item_sk, wr_order_number) BUCKETS 32
+ PROPERTIES (
+ "replication_num" = "1",
+ "colocate_with" = "web"
+ )
+ '''
+
+ sql '''
+ drop table if exists web_site
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS web_site (
+ web_site_sk bigint not null,
+ web_site_id char(16) not null,
+ web_rec_start_date datev2,
+ web_rec_end_date datev2,
+ web_name varchar(50),
+ web_open_date_sk bigint,
+ web_close_date_sk bigint,
+ web_class varchar(50),
+ web_manager varchar(40),
+ web_mkt_id integer,
+ web_mkt_class varchar(50),
+ web_mkt_desc varchar(100),
+ web_market_manager varchar(40),
+ web_company_id integer,
+ web_company_name char(50),
+ web_street_number char(10),
+ web_street_name varchar(60),
+ web_street_type char(15),
+ web_suite_number char(10),
+ web_city varchar(60),
+ web_county varchar(30),
+ web_state char(2),
+ web_zip char(10),
+ web_country varchar(20),
+ web_gmt_offset decimalv3(5,2),
+ web_tax_percentage decimalv3(5,2)
+ )
+ DUPLICATE KEY(web_site_sk)
+ DISTRIBUTED BY HASH(web_site_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists promotion
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS promotion (
+ p_promo_sk bigint not null,
+ p_promo_id char(16) not null,
+ p_start_date_sk bigint,
+ p_end_date_sk bigint,
+ p_item_sk bigint,
+ p_cost decimalv3(15,2),
+ p_response_targe integer,
+ p_promo_name char(50),
+ p_channel_dmail char(1),
+ p_channel_email char(1),
+ p_channel_catalog char(1),
+ p_channel_tv char(1),
+ p_channel_radio char(1),
+ p_channel_press char(1),
+ p_channel_event char(1),
+ p_channel_demo char(1),
+ p_channel_details varchar(100),
+ p_purpose char(15),
+ p_discount_active char(1)
+ )
+ DUPLICATE KEY(p_promo_sk)
+ DISTRIBUTED BY HASH(p_promo_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists web_sales
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS web_sales (
+ ws_sold_date_sk bigint,
+ ws_item_sk bigint not null,
+ ws_order_number bigint not null,
+ ws_sold_time_sk bigint,
+ ws_ship_date_sk bigint,
+ ws_bill_customer_sk bigint,
+ ws_bill_cdemo_sk bigint,
+ ws_bill_hdemo_sk bigint,
+ ws_bill_addr_sk bigint,
+ ws_ship_customer_sk bigint,
+ ws_ship_cdemo_sk bigint,
+ ws_ship_hdemo_sk bigint,
+ ws_ship_addr_sk bigint,
+ ws_web_page_sk bigint,
+ ws_web_site_sk bigint,
+ ws_ship_mode_sk bigint,
+ ws_warehouse_sk bigint,
+ ws_promo_sk bigint,
+ ws_quantity integer,
+ ws_wholesale_cost decimalv3(7,2),
+ ws_list_price decimalv3(7,2),
+ ws_sales_price decimalv3(7,2),
+ ws_ext_discount_amt decimalv3(7,2),
+ ws_ext_sales_price decimalv3(7,2),
+ ws_ext_wholesale_cost decimalv3(7,2),
+ ws_ext_list_price decimalv3(7,2),
+ ws_ext_tax decimalv3(7,2),
+ ws_coupon_amt decimalv3(7,2),
+ ws_ext_ship_cost decimalv3(7,2),
+ ws_net_paid decimalv3(7,2),
+ ws_net_paid_inc_tax decimalv3(7,2),
+ ws_net_paid_inc_ship decimalv3(7,2),
+ ws_net_paid_inc_ship_tax decimalv3(7,2),
+ ws_net_profit decimalv3(7,2)
+ )
+ DUPLICATE KEY(ws_sold_date_sk, ws_item_sk)
+ DISTRIBUTED BY HASH(ws_item_sk, ws_order_number) BUCKETS 32
+ PROPERTIES (
+ "replication_num" = "1",
+ "colocate_with" = "web"
+ )
+ '''
+
+ sql '''
+ drop table if exists store
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS store (
+ s_store_sk bigint not null,
+ s_store_id char(16) not null,
+ s_rec_start_date datev2,
+ s_rec_end_date datev2,
+ s_closed_date_sk bigint,
+ s_store_name varchar(50),
+ s_number_employees integer,
+ s_floor_space integer,
+ s_hours char(20),
+ s_manager varchar(40),
+ s_market_id integer,
+ s_geography_class varchar(100),
+ s_market_desc varchar(100),
+ s_market_manager varchar(40),
+ s_division_id integer,
+ s_division_name varchar(50),
+ s_company_id integer,
+ s_company_name varchar(50),
+ s_street_number varchar(10),
+ s_street_name varchar(60),
+ s_street_type char(15),
+ s_suite_number char(10),
+ s_city varchar(60),
+ s_county varchar(30),
+ s_state char(2),
+ s_zip char(10),
+ s_country varchar(20),
+ s_gmt_offset decimalv3(5,2),
+ s_tax_precentage decimalv3(5,2)
+ )
+ DUPLICATE KEY(s_store_sk)
+ DISTRIBUTED BY HASH(s_store_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists time_dim
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS time_dim (
+ t_time_sk bigint not null,
+ t_time_id char(16) not null,
+ t_time integer,
+ t_hour integer,
+ t_minute integer,
+ t_second integer,
+ t_am_pm char(2),
+ t_shift char(20),
+ t_sub_shift char(20),
+ t_meal_time char(20)
+ )
+ DUPLICATE KEY(t_time_sk)
+ DISTRIBUTED BY HASH(t_time_sk) BUCKETS 12
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists web_page
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS web_page (
+ wp_web_page_sk bigint not null,
+ wp_web_page_id char(16) not null,
+ wp_rec_start_date datev2,
+ wp_rec_end_date datev2,
+ wp_creation_date_sk bigint,
+ wp_access_date_sk bigint,
+ wp_autogen_flag char(1),
+ wp_customer_sk bigint,
+ wp_url varchar(100),
+ wp_type char(50),
+ wp_char_count integer,
+ wp_link_count integer,
+ wp_image_count integer,
+ wp_max_ad_count integer
+ )
+ DUPLICATE KEY(wp_web_page_sk)
+ DISTRIBUTED BY HASH(wp_web_page_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists store_returns
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS store_returns (
+ sr_item_sk bigint not null,
+ sr_ticket_number bigint not null,
+ sr_returned_date_sk bigint,
+ sr_return_time_sk bigint,
+ sr_customer_sk bigint,
+ sr_cdemo_sk bigint,
+ sr_hdemo_sk bigint,
+ sr_addr_sk bigint,
+ sr_store_sk bigint,
+ sr_reason_sk bigint,
+ sr_return_quantity integer,
+ sr_return_amt decimalv3(7,2),
+ sr_return_tax decimalv3(7,2),
+ sr_return_amt_inc_tax decimalv3(7,2),
+ sr_fee decimalv3(7,2),
+ sr_return_ship_cost decimalv3(7,2),
+ sr_refunded_cash decimalv3(7,2),
+ sr_reversed_charge decimalv3(7,2),
+ sr_store_credit decimalv3(7,2),
+ sr_net_loss decimalv3(7,2)
+ )
+ duplicate key(sr_item_sk, sr_ticket_number)
+ distributed by hash (sr_item_sk, sr_ticket_number) buckets 32
+ properties (
+ "replication_num" = "1",
+ "colocate_with" = "store"
+ )
+ '''
+
+ sql '''
+ drop table if exists store_sales
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS store_sales (
+ ss_sold_date_sk bigint,
+ ss_item_sk bigint not null,
+ ss_ticket_number bigint not null,
+ ss_sold_time_sk bigint,
+ ss_customer_sk bigint,
+ ss_cdemo_sk bigint,
+ ss_hdemo_sk bigint,
+ ss_addr_sk bigint,
+ ss_store_sk bigint,
+ ss_promo_sk bigint,
+ ss_quantity integer,
+ ss_wholesale_cost decimalv3(7,2),
+ ss_list_price decimalv3(7,2),
+ ss_sales_price decimalv3(7,2),
+ ss_ext_discount_amt decimalv3(7,2),
+ ss_ext_sales_price decimalv3(7,2),
+ ss_ext_wholesale_cost decimalv3(7,2),
+ ss_ext_list_price decimalv3(7,2),
+ ss_ext_tax decimalv3(7,2),
+ ss_coupon_amt decimalv3(7,2),
+ ss_net_paid decimalv3(7,2),
+ ss_net_paid_inc_tax decimalv3(7,2),
+ ss_net_profit decimalv3(7,2)
+ )
+ DUPLICATE KEY(ss_sold_date_sk, ss_item_sk)
+ DISTRIBUTED BY HASH(ss_item_sk, ss_ticket_number) BUCKETS 32
+ PROPERTIES (
+ "replication_num" = "1",
+ "colocate_with" = "store"
+ )
+ '''
+
+ sql '''
+ drop table if exists ship_mode
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS ship_mode (
+ sm_ship_mode_sk bigint not null,
+ sm_ship_mode_id char(16) not null,
+ sm_type char(30),
+ sm_code char(10),
+ sm_carrier char(20),
+ sm_contract char(20)
+ )
+ DUPLICATE KEY(sm_ship_mode_sk)
+ DISTRIBUTED BY HASH(sm_ship_mode_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists customer
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS customer (
+ c_customer_sk bigint not null,
+ c_customer_id char(16) not null,
+ c_current_cdemo_sk bigint,
+ c_current_hdemo_sk bigint,
+ c_current_addr_sk bigint,
+ c_first_shipto_date_sk bigint,
+ c_first_sales_date_sk bigint,
+ c_salutation char(10),
+ c_first_name char(20),
+ c_last_name char(30),
+ c_preferred_cust_flag char(1),
+ c_birth_day integer,
+ c_birth_month integer,
+ c_birth_year integer,
+ c_birth_country varchar(20),
+ c_login char(13),
+ c_email_address char(50),
+ c_last_review_date_sk bigint
+ )
+ DUPLICATE KEY(c_customer_sk)
+ DISTRIBUTED BY HASH(c_customer_id) BUCKETS 12
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists dbgen_version
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS dbgen_version
+ (
+ dv_version varchar(16) ,
+ dv_create_date datev2 ,
+ dv_create_time datetime ,
+ dv_cmdline_args varchar(200)
+ )
+ DUPLICATE KEY(dv_version)
+ DISTRIBUTED BY HASH(dv_version) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+}
diff --git a/regression-test/suites/nereids_hint_tpcds_p0/shape/query1.groovy b/regression-test/suites/nereids_hint_tpcds_p0/shape/query1.groovy
new file mode 100644
index 00000000000..1294eb657b6
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpcds_p0/shape/query1.groovy
@@ -0,0 +1,84 @@
+/*
+ * 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("query1") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql 'set exec_mem_limit=21G'
+ sql 'set be_number_for_test=3'
+ sql 'set parallel_fragment_exec_instance_num=8; '
+ sql 'set parallel_pipeline_task_num=8; '
+ sql 'set forbid_unknown_col_stats=true'
+ sql 'set enable_nereids_timeout = false'
+ sql 'set enable_runtime_filter_prune=false'
+ sql 'set runtime_filter_type=8'
+ sql 'set dump_nereids_memo=false'
+ def ds = """with customer_total_return as
+(select sr_customer_sk as ctr_customer_sk
+,sr_store_sk as ctr_store_sk
+,sum(SR_FEE) as ctr_total_return
+from store_returns
+,date_dim
+where sr_returned_date_sk = d_date_sk
+and d_year =2000
+group by sr_customer_sk
+,sr_store_sk)
+ select c_customer_id
+from customer_total_return ctr1
+,store
+,customer
+where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
+from customer_total_return ctr2
+where ctr1.ctr_store_sk = ctr2.ctr_store_sk)
+and s_store_sk = ctr1.ctr_store_sk
+and s_state = 'TN'
+and ctr1.ctr_customer_sk = c_customer_sk
+order by c_customer_id
+limit 100"""
+ qt_ds_shape_1 '''
+ explain shape plan
+ with customer_total_return as
+(select
+/*+ leading(store_returns broadcast date_dim) */
+sr_customer_sk as ctr_customer_sk
+,sr_store_sk as ctr_store_sk
+,sum(SR_FEE) as ctr_total_return
+from store_returns
+,date_dim
+where sr_returned_date_sk = d_date_sk
+and d_year =2000
+group by sr_customer_sk
+,sr_store_sk)
+ select
+ c_customer_id
+from customer_total_return ctr1
+,store
+,customer
+where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
+from customer_total_return ctr2
+where ctr1.ctr_store_sk = ctr2.ctr_store_sk)
+and s_store_sk = ctr1.ctr_store_sk
+and s_state = 'TN'
+and ctr1.ctr_customer_sk = c_customer_sk
+order by c_customer_id
+limit 100
+ '''
+}
diff --git a/regression-test/suites/nereids_hint_tpcds_p0/shape/query24.groovy b/regression-test/suites/nereids_hint_tpcds_p0/shape/query24.groovy
new file mode 100644
index 00000000000..478ce76859c
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpcds_p0/shape/query24.groovy
@@ -0,0 +1,143 @@
+/*
+ * 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("query24") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql 'set exec_mem_limit=21G'
+ sql 'set be_number_for_test=3'
+ sql 'set parallel_fragment_exec_instance_num=8; '
+ sql 'set parallel_pipeline_task_num=8; '
+ sql 'set forbid_unknown_col_stats=true'
+ sql 'set enable_nereids_timeout = false'
+ sql 'set enable_runtime_filter_prune=false'
+ sql 'set runtime_filter_type=8'
+ sql 'set dump_nereids_memo=false'
+ def ds = """with ssales as
+(select c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size
+ ,sum(ss_net_paid) netpaid
+from store_sales
+ ,store_returns
+ ,store
+ ,item
+ ,customer
+ ,customer_address
+where ss_ticket_number = sr_ticket_number
+ and ss_item_sk = sr_item_sk
+ and ss_customer_sk = c_customer_sk
+ and ss_item_sk = i_item_sk
+ and ss_store_sk = s_store_sk
+ and c_current_addr_sk = ca_address_sk
+ and c_birth_country <> upper(ca_country)
+ and s_zip = ca_zip
+and s_market_id=5
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size)
+select c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,sum(netpaid) paid
+from ssales
+where i_color = 'aquamarine'
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+having sum(netpaid) > (select 0.05*avg(netpaid)
+ from ssales)
+order by c_last_name
+ ,c_first_name
+ ,s_store_name
+"""
+ qt_ds_shape_24 '''
+ explain shape plan
+ with ssales as
+(select
+/*+ leading(store_sales broadcast store shuffle {customer shuffle customer_address} shuffle item shuffle store_returns) */
+c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size
+ ,sum(ss_net_paid) netpaid
+from store_sales
+ ,store_returns
+ ,store
+ ,item
+ ,customer
+ ,customer_address
+where ss_ticket_number = sr_ticket_number
+ and ss_item_sk = sr_item_sk
+ and ss_customer_sk = c_customer_sk
+ and ss_item_sk = i_item_sk
+ and ss_store_sk = s_store_sk
+ and c_current_addr_sk = ca_address_sk
+ and c_birth_country <> upper(ca_country)
+ and s_zip = ca_zip
+and s_market_id=5
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,ca_state
+ ,s_state
+ ,i_color
+ ,i_current_price
+ ,i_manager_id
+ ,i_units
+ ,i_size)
+select c_last_name
+ ,c_first_name
+ ,s_store_name
+ ,sum(netpaid) paid
+from ssales
+where i_color = 'aquamarine'
+group by c_last_name
+ ,c_first_name
+ ,s_store_name
+having sum(netpaid) > (select 0.05*avg(netpaid)
+ from ssales)
+order by c_last_name
+ ,c_first_name
+ ,s_store_name
+
+ '''
+}
diff --git a/regression-test/suites/nereids_hint_tpcds_p0/shape/query64.groovy b/regression-test/suites/nereids_hint_tpcds_p0/shape/query64.groovy
new file mode 100644
index 00000000000..8ac80b20afe
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpcds_p0/shape/query64.groovy
@@ -0,0 +1,279 @@
+/*
+ * 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("query64") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql 'set exec_mem_limit=21G'
+ sql 'set be_number_for_test=3'
+ sql 'set parallel_fragment_exec_instance_num=8; '
+ sql 'set parallel_pipeline_task_num=8; '
+ sql 'set forbid_unknown_col_stats=true'
+ sql 'set enable_nereids_timeout = false'
+ sql 'set enable_runtime_filter_prune=false'
+ sql 'set runtime_filter_type=8'
+ sql 'set dump_nereids_memo=false'
+ def ds = """with cs_ui as
+ (select cs_item_sk
+ ,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
+ from catalog_sales
+ ,catalog_returns
+ where cs_item_sk = cr_item_sk
+ and cs_order_number = cr_order_number
+ group by cs_item_sk
+ having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
+cross_sales as
+ (select i_product_name product_name
+ ,i_item_sk item_sk
+ ,s_store_name store_name
+ ,s_zip store_zip
+ ,ad1.ca_street_number b_street_number
+ ,ad1.ca_street_name b_street_name
+ ,ad1.ca_city b_city
+ ,ad1.ca_zip b_zip
+ ,ad2.ca_street_number c_street_number
+ ,ad2.ca_street_name c_street_name
+ ,ad2.ca_city c_city
+ ,ad2.ca_zip c_zip
+ ,d1.d_year as syear
+ ,d2.d_year as fsyear
+ ,d3.d_year s2year
+ ,count(*) cnt
+ ,sum(ss_wholesale_cost) s1
+ ,sum(ss_list_price) s2
+ ,sum(ss_coupon_amt) s3
+ FROM store_sales
+ ,store_returns
+ ,cs_ui
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,customer
+ ,customer_demographics cd1
+ ,customer_demographics cd2
+ ,promotion
+ ,household_demographics hd1
+ ,household_demographics hd2
+ ,customer_address ad1
+ ,customer_address ad2
+ ,income_band ib1
+ ,income_band ib2
+ ,item
+ WHERE ss_store_sk = s_store_sk AND
+ ss_sold_date_sk = d1.d_date_sk AND
+ ss_customer_sk = c_customer_sk AND
+ ss_cdemo_sk= cd1.cd_demo_sk AND
+ ss_hdemo_sk = hd1.hd_demo_sk AND
+ ss_addr_sk = ad1.ca_address_sk and
+ ss_item_sk = i_item_sk and
+ ss_item_sk = sr_item_sk and
+ ss_ticket_number = sr_ticket_number and
+ ss_item_sk = cs_ui.cs_item_sk and
+ c_current_cdemo_sk = cd2.cd_demo_sk AND
+ c_current_hdemo_sk = hd2.hd_demo_sk AND
+ c_current_addr_sk = ad2.ca_address_sk and
+ c_first_sales_date_sk = d2.d_date_sk and
+ c_first_shipto_date_sk = d3.d_date_sk and
+ ss_promo_sk = p_promo_sk and
+ hd1.hd_income_band_sk = ib1.ib_income_band_sk and
+ hd2.hd_income_band_sk = ib2.ib_income_band_sk and
+ cd1.cd_marital_status <> cd2.cd_marital_status and
+ i_color in ('orange','lace','lawn','misty','blush','pink') and
+ i_current_price between 48 and 48 + 10 and
+ i_current_price between 48 + 1 and 48 + 15
+group by i_product_name
+ ,i_item_sk
+ ,s_store_name
+ ,s_zip
+ ,ad1.ca_street_number
+ ,ad1.ca_street_name
+ ,ad1.ca_city
+ ,ad1.ca_zip
+ ,ad2.ca_street_number
+ ,ad2.ca_street_name
+ ,ad2.ca_city
+ ,ad2.ca_zip
+ ,d1.d_year
+ ,d2.d_year
+ ,d3.d_year
+)
+select cs1.product_name
+ ,cs1.store_name
+ ,cs1.store_zip
+ ,cs1.b_street_number
+ ,cs1.b_street_name
+ ,cs1.b_city
+ ,cs1.b_zip
+ ,cs1.c_street_number
+ ,cs1.c_street_name
+ ,cs1.c_city
+ ,cs1.c_zip
+ ,cs1.syear
+ ,cs1.cnt
+ ,cs1.s1 as s11
+ ,cs1.s2 as s21
+ ,cs1.s3 as s31
+ ,cs2.s1 as s12
+ ,cs2.s2 as s22
+ ,cs2.s3 as s32
+ ,cs2.syear
+ ,cs2.cnt
+from cross_sales cs1,cross_sales cs2
+where cs1.item_sk=cs2.item_sk and
+ cs1.syear = 1999 and
+ cs2.syear = 1999 + 1 and
+ cs2.cnt <= cs1.cnt and
+ cs1.store_name = cs2.store_name and
+ cs1.store_zip = cs2.store_zip
+order by cs1.product_name
+ ,cs1.store_name
+ ,cs2.cnt
+ ,cs1.s1
+ ,cs2.s1"""
+ qt_ds_shape_64 '''
+ explain shape plan
+ with cs_ui as
+ (select
+ /*+ leading(catalog_sales shuffle catalog_returns) */
+ cs_item_sk
+ ,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
+ from catalog_sales
+ ,catalog_returns
+ where cs_item_sk = cr_item_sk
+ and cs_order_number = cr_order_number
+ group by cs_item_sk
+ having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
+cross_sales as
+ (select
+ /*+ leading( {store_sales {{customer d2} cd2}} cd1 d3 item {hd1 ib1} store_returns ad1 hd2 ad2 ib2 d1 store promotion cs_ui) */
+ i_product_name product_name
+ ,i_item_sk item_sk
+ ,s_store_name store_name
+ ,s_zip store_zip
+ ,ad1.ca_street_number b_street_number
+ ,ad1.ca_street_name b_street_name
+ ,ad1.ca_city b_city
+ ,ad1.ca_zip b_zip
+ ,ad2.ca_street_number c_street_number
+ ,ad2.ca_street_name c_street_name
+ ,ad2.ca_city c_city
+ ,ad2.ca_zip c_zip
+ ,d1.d_year as syear
+ ,d2.d_year as fsyear
+ ,d3.d_year s2year
+ ,count(*) cnt
+ ,sum(ss_wholesale_cost) s1
+ ,sum(ss_list_price) s2
+ ,sum(ss_coupon_amt) s3
+ FROM store_sales
+ ,store_returns
+ ,cs_ui
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,customer
+ ,customer_demographics cd1
+ ,customer_demographics cd2
+ ,promotion
+ ,household_demographics hd1
+ ,household_demographics hd2
+ ,customer_address ad1
+ ,customer_address ad2
+ ,income_band ib1
+ ,income_band ib2
+ ,item
+ WHERE ss_store_sk = s_store_sk AND
+ ss_sold_date_sk = d1.d_date_sk AND
+ ss_customer_sk = c_customer_sk AND
+ ss_cdemo_sk= cd1.cd_demo_sk AND
+ ss_hdemo_sk = hd1.hd_demo_sk AND
+ ss_addr_sk = ad1.ca_address_sk and
+ ss_item_sk = i_item_sk and
+ ss_item_sk = sr_item_sk and
+ ss_ticket_number = sr_ticket_number and
+ ss_item_sk = cs_ui.cs_item_sk and
+ c_current_cdemo_sk = cd2.cd_demo_sk AND
+ c_current_hdemo_sk = hd2.hd_demo_sk AND
+ c_current_addr_sk = ad2.ca_address_sk and
+ c_first_sales_date_sk = d2.d_date_sk and
+ c_first_shipto_date_sk = d3.d_date_sk and
+ ss_promo_sk = p_promo_sk and
+ hd1.hd_income_band_sk = ib1.ib_income_band_sk and
+ hd2.hd_income_band_sk = ib2.ib_income_band_sk and
+ cd1.cd_marital_status <> cd2.cd_marital_status and
+ i_color in ('orange','lace','lawn','misty','blush','pink') and
+ i_current_price between 48 and 48 + 10 and
+ i_current_price between 48 + 1 and 48 + 15
+group by i_product_name
+ ,i_item_sk
+ ,s_store_name
+ ,s_zip
+ ,ad1.ca_street_number
+ ,ad1.ca_street_name
+ ,ad1.ca_city
+ ,ad1.ca_zip
+ ,ad2.ca_street_number
+ ,ad2.ca_street_name
+ ,ad2.ca_city
+ ,ad2.ca_zip
+ ,d1.d_year
+ ,d2.d_year
+ ,d3.d_year
+)
+select
+/*+ leading(cs1 shuffle cs2) */
+ cs1.product_name
+ ,cs1.store_name
+ ,cs1.store_zip
+ ,cs1.b_street_number
+ ,cs1.b_street_name
+ ,cs1.b_city
+ ,cs1.b_zip
+ ,cs1.c_street_number
+ ,cs1.c_street_name
+ ,cs1.c_city
+ ,cs1.c_zip
+ ,cs1.syear
+ ,cs1.cnt
+ ,cs1.s1 as s11
+ ,cs1.s2 as s21
+ ,cs1.s3 as s31
+ ,cs2.s1 as s12
+ ,cs2.s2 as s22
+ ,cs2.s3 as s32
+ ,cs2.syear
+ ,cs2.cnt
+from cross_sales cs1,cross_sales cs2
+where cs1.item_sk=cs2.item_sk and
+ cs1.syear = 1999 and
+ cs2.syear = 1999 + 1 and
+ cs2.cnt <= cs1.cnt and
+ cs1.store_name = cs2.store_name and
+ cs1.store_zip = cs2.store_zip
+order by cs1.product_name
+ ,cs1.store_name
+ ,cs2.cnt
+ ,cs1.s1
+ ,cs2.s1
+ '''
+}
diff --git a/regression-test/suites/nereids_hint_tpcds_p0/shape/query67.groovy b/regression-test/suites/nereids_hint_tpcds_p0/shape/query67.groovy
new file mode 100644
index 00000000000..2194aee3105
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpcds_p0/shape/query67.groovy
@@ -0,0 +1,121 @@
+/*
+ * 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("query67") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql 'set exec_mem_limit=21G'
+ sql 'set be_number_for_test=3'
+ sql 'set parallel_fragment_exec_instance_num=8; '
+ sql 'set parallel_pipeline_task_num=8; '
+ sql 'set forbid_unknown_col_stats=true'
+ sql 'set enable_nereids_timeout = false'
+ sql 'set enable_runtime_filter_prune=false'
+ sql 'set runtime_filter_type=8'
+ sql 'set dump_nereids_memo=false'
+ def ds = """select *
+from (select i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sumsales
+ ,rank() over (partition by i_category order by sumsales desc) rk
+ from (select i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales
+ from store_sales
+ ,date_dim
+ ,store
+ ,item
+ where ss_sold_date_sk=d_date_sk
+ and ss_item_sk=i_item_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq between 1217 and 1217+11
+ group by rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2
+where rk <= 100
+order by i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sumsales
+ ,rk
+limit 100"""
+ qt_ds_shape_67 '''
+ explain shape plan
+ select *
+from (select i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sumsales
+ ,rank() over (partition by i_category order by sumsales desc) rk
+ from (select
+ /*+ leading(store_sales broadcast date_dim broadcast store broadcast item) */
+ i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales
+ from store_sales
+ ,date_dim
+ ,store
+ ,item
+ where ss_sold_date_sk=d_date_sk
+ and ss_item_sk=i_item_sk
+ and ss_store_sk = s_store_sk
+ and d_month_seq between 1217 and 1217+11
+ group by rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2
+where rk <= 100
+order by i_category
+ ,i_class
+ ,i_brand
+ ,i_product_name
+ ,d_year
+ ,d_qoy
+ ,d_moy
+ ,s_store_id
+ ,sumsales
+ ,rk
+limit 100
+ '''
+}
diff --git a/regression-test/suites/nereids_hint_tpcds_p0/shape/query72.groovy b/regression-test/suites/nereids_hint_tpcds_p0/shape/query72.groovy
new file mode 100644
index 00000000000..b1232affd10
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpcds_p0/shape/query72.groovy
@@ -0,0 +1,91 @@
+/*
+ * 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("query72") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql 'set exec_mem_limit=21G'
+ sql 'set be_number_for_test=3'
+ sql 'set parallel_fragment_exec_instance_num=8; '
+ sql 'set parallel_pipeline_task_num=8; '
+ sql 'set forbid_unknown_col_stats=true'
+ sql 'set enable_nereids_timeout = false'
+ sql 'set enable_runtime_filter_prune=false'
+ sql 'set runtime_filter_type=8'
+ sql 'set dump_nereids_memo=false'
+ def ds = """select i_item_desc
+ ,w_warehouse_name
+ ,d1.d_week_seq
+ ,sum(case when p_promo_sk is null then 1 else 0 end) no_promo
+ ,sum(case when p_promo_sk is not null then 1 else 0 end) promo
+ ,count(*) total_cnt
+from catalog_sales
+join inventory on (cs_item_sk = inv_item_sk)
+join warehouse on (w_warehouse_sk=inv_warehouse_sk)
+join item on (i_item_sk = cs_item_sk)
+join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk)
+join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk)
+join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk)
+join date_dim d2 on (inv_date_sk = d2.d_date_sk)
+join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk)
+left outer join promotion on (cs_promo_sk=p_promo_sk)
+left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number)
+where d1.d_week_seq = d2.d_week_seq
+ and inv_quantity_on_hand < cs_quantity
+ and (d3.d_date > (d1.d_date + INTERVAL '5' DAY))
+ and hd_buy_potential = '1001-5000'
+ and d1.d_year = 1998
+ and cd_marital_status = 'S'
+group by i_item_desc,w_warehouse_name,d1.d_week_seq
+order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq
+limit 100"""
+ qt_ds_shape_72 '''
+ explain shape plan
+ select
+ /*+ leading( inventory shuffle { catalog_returns shuffle {catalog_sales shuffle {d3 broadcast d1} broadcast household_demographics shuffle customer_demographics broadcast promotion shuffle item} broadcast d2} broadcast warehouse) */
+ i_item_desc
+ ,w_warehouse_name
+ ,d1.d_week_seq
+ ,sum(case when p_promo_sk is null then 1 else 0 end) no_promo
+ ,sum(case when p_promo_sk is not null then 1 else 0 end) promo
+ ,count(*) total_cnt
+from catalog_sales
+join inventory on (cs_item_sk = inv_item_sk)
+join warehouse on (w_warehouse_sk=inv_warehouse_sk)
+join item on (i_item_sk = cs_item_sk)
+join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk)
+join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk)
+join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk)
+join date_dim d2 on (inv_date_sk = d2.d_date_sk)
+join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk)
+left outer join promotion on (cs_promo_sk=p_promo_sk)
+left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number)
+where d1.d_week_seq = d2.d_week_seq
+ and inv_quantity_on_hand < cs_quantity
+ and (d3.d_date > (d1.d_date + INTERVAL '5' DAY))
+ and hd_buy_potential = '1001-5000'
+ and d1.d_year = 1998
+ and cd_marital_status = 'S'
+group by i_item_desc,w_warehouse_name,d1.d_week_seq
+order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq
+limit 100
+ '''
+}
diff --git a/regression-test/suites/nereids_hint_tpcds_p0/shape/query78.groovy b/regression-test/suites/nereids_hint_tpcds_p0/shape/query78.groovy
new file mode 100644
index 00000000000..16ec6a863f2
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpcds_p0/shape/query78.groovy
@@ -0,0 +1,154 @@
+/*
+ * 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("query78") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql 'set exec_mem_limit=21G'
+ sql 'set be_number_for_test=3'
+ sql 'set parallel_fragment_exec_instance_num=8; '
+ sql 'set parallel_pipeline_task_num=8; '
+ sql 'set forbid_unknown_col_stats=true'
+ sql 'set enable_nereids_timeout = false'
+ sql 'set enable_runtime_filter_prune=false'
+ sql 'set runtime_filter_type=8'
+ sql 'set dump_nereids_memo=false'
+ def ds = """with ws as
+ (select d_year AS ws_sold_year, ws_item_sk,
+ ws_bill_customer_sk ws_customer_sk,
+ sum(ws_quantity) ws_qty,
+ sum(ws_wholesale_cost) ws_wc,
+ sum(ws_sales_price) ws_sp
+ from web_sales
+ left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk
+ join date_dim on ws_sold_date_sk = d_date_sk
+ where wr_order_number is null and d_year=1998
+ group by d_year, ws_item_sk, ws_bill_customer_sk
+ ),
+cs as
+ (select d_year AS cs_sold_year, cs_item_sk,
+ cs_bill_customer_sk cs_customer_sk,
+ sum(cs_quantity) cs_qty,
+ sum(cs_wholesale_cost) cs_wc,
+ sum(cs_sales_price) cs_sp
+ from catalog_sales
+ left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk
+ join date_dim on cs_sold_date_sk = d_date_sk
+ where cr_order_number is null and d_year=1998
+ group by d_year, cs_item_sk, cs_bill_customer_sk
+ ),
+ss as
+ (select d_year AS ss_sold_year, ss_item_sk,
+ ss_customer_sk,
+ sum(ss_quantity) ss_qty,
+ sum(ss_wholesale_cost) ss_wc,
+ sum(ss_sales_price) ss_sp
+ from store_sales
+ left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk
+ join date_dim on ss_sold_date_sk = d_date_sk
+ where sr_ticket_number is null and d_year=1998
+ group by d_year, ss_item_sk, ss_customer_sk
+ )
+select
+ss_customer_sk,
+round(ss_qty/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2) ratio,
+ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
+coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
+coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
+coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
+from ss
+left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)
+left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=ss_item_sk and cs_customer_sk=ss_customer_sk)
+where (coalesce(ws_qty,0)>0 or coalesce(cs_qty, 0)>0) and ss_sold_year=1998
+order by
+ ss_customer_sk,
+ ss_qty desc, ss_wc desc, ss_sp desc,
+ other_chan_qty,
+ other_chan_wholesale_cost,
+ other_chan_sales_price,
+ ratio
+limit 100"""
+ qt_ds_shape_78 '''
+ explain shape plan
+ with ws as
+ (select
+ /*+ leading(web_sales broadcast date_dim web_returns) */
+ d_year AS ws_sold_year, ws_item_sk,
+ ws_bill_customer_sk ws_customer_sk,
+ sum(ws_quantity) ws_qty,
+ sum(ws_wholesale_cost) ws_wc,
+ sum(ws_sales_price) ws_sp
+ from web_sales
+ left join web_returns on wr_order_number=ws_order_number and ws_item_sk=wr_item_sk
+ join date_dim on ws_sold_date_sk = d_date_sk
+ where wr_order_number is null and d_year=1998
+ group by d_year, ws_item_sk, ws_bill_customer_sk
+ ),
+cs as
+ (select
+ /*+ leading(catalog_sales broadcast date_dim catalog_returns) */
+ d_year AS cs_sold_year, cs_item_sk,
+ cs_bill_customer_sk cs_customer_sk,
+ sum(cs_quantity) cs_qty,
+ sum(cs_wholesale_cost) cs_wc,
+ sum(cs_sales_price) cs_sp
+ from catalog_sales
+ left join catalog_returns on cr_order_number=cs_order_number and cs_item_sk=cr_item_sk
+ join date_dim on cs_sold_date_sk = d_date_sk
+ where cr_order_number is null and d_year=1998
+ group by d_year, cs_item_sk, cs_bill_customer_sk
+ ),
+ss as
+ (select
+ /*+ leading(store_sales broadcast date_dim store_returns) */
+ d_year AS ss_sold_year, ss_item_sk,
+ ss_customer_sk,
+ sum(ss_quantity) ss_qty,
+ sum(ss_wholesale_cost) ss_wc,
+ sum(ss_sales_price) ss_sp
+ from store_sales
+ left join store_returns on sr_ticket_number=ss_ticket_number and ss_item_sk=sr_item_sk
+ join date_dim on ss_sold_date_sk = d_date_sk
+ where sr_ticket_number is null and d_year=1998
+ group by d_year, ss_item_sk, ss_customer_sk
+ )
+select
+/*+ leading(ss shuffle ws shuffle cs) */
+ss_customer_sk,
+round(ss_qty/(coalesce(ws_qty,0)+coalesce(cs_qty,0)),2) ratio,
+ss_qty store_qty, ss_wc store_wholesale_cost, ss_sp store_sales_price,
+coalesce(ws_qty,0)+coalesce(cs_qty,0) other_chan_qty,
+coalesce(ws_wc,0)+coalesce(cs_wc,0) other_chan_wholesale_cost,
+coalesce(ws_sp,0)+coalesce(cs_sp,0) other_chan_sales_price
+from ss
+left join ws on (ws_sold_year=ss_sold_year and ws_item_sk=ss_item_sk and ws_customer_sk=ss_customer_sk)
+left join cs on (cs_sold_year=ss_sold_year and cs_item_sk=ss_item_sk and cs_customer_sk=ss_customer_sk)
+where (coalesce(ws_qty,0)>0 or coalesce(cs_qty, 0)>0) and ss_sold_year=1998
+order by
+ ss_customer_sk,
+ ss_qty desc, ss_wc desc, ss_sp desc,
+ other_chan_qty,
+ other_chan_wholesale_cost,
+ other_chan_sales_price,
+ ratio
+limit 100
+ '''
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org