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 c125148deb76ae7072d3381f3f1bf3e9510c0385 Author: xzj7019 <[email protected]> AuthorDate: Fri Apr 26 18:27:51 2024 +0800 [opt](Nereids) bucket shuffle downgrade expansion (#34088) Expand bucket shuffle downgrade condition, which originally requiring a single partition after pruning, basic table and bucket number < para number. Currently, we expect this option can be used for disabling bucket shuffle more efficiently, without above restrictions. Co-authored-by: zhongjian.xzj <[email protected]> --- .../properties/ChildrenPropertiesRegulator.java | 39 +--- .../bs_downgrade_shape/query13.out | 40 ++++ .../bs_downgrade_shape/query19.out | 41 ++++ .../bs_downgrade_shape/query44.out | 75 +++++++ .../bs_downgrade_shape/query45.out | 38 ++++ .../bs_downgrade_shape/query54.out | 84 +++++++ .../bs_downgrade_shape/query56.out | 95 ++++++++ .../bs_downgrade_shape/query6.out | 54 +++++ .../bs_downgrade_shape/query61.out | 83 +++++++ .../bs_downgrade_shape/query68.out | 47 ++++ .../bs_downgrade_shape/query8.out | 47 ++++ .../bs_downgrade_shape/query91.out | 46 ++++ .../bs_downgrade_shape/query95.out | 55 +++++ .../bs_downgrade_shape/query13.groovy | 136 +++++++++++ .../bs_downgrade_shape/query19.groovy | 82 +++++++ .../bs_downgrade_shape/query44.groovy | 102 +++++++++ .../bs_downgrade_shape/query45.groovy | 72 ++++++ .../bs_downgrade_shape/query54.groovy | 144 ++++++++++++ .../bs_downgrade_shape/query56.groovy | 170 ++++++++++++++ .../bs_downgrade_shape/query6.groovy | 84 +++++++ .../bs_downgrade_shape/query61.groovy | 120 ++++++++++ .../bs_downgrade_shape/query68.groovy | 116 ++++++++++ .../bs_downgrade_shape/query8.groovy | 248 +++++++++++++++++++++ .../bs_downgrade_shape/query91.groovy | 94 ++++++++ .../bs_downgrade_shape/query95.groovy | 96 ++++++++ 25 files changed, 2173 insertions(+), 35 deletions(-) diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ChildrenPropertiesRegulator.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ChildrenPropertiesRegulator.java index 366730f7dc5..8cae2c8775c 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ChildrenPropertiesRegulator.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ChildrenPropertiesRegulator.java @@ -30,7 +30,6 @@ import org.apache.doris.nereids.trees.expressions.Expression; import org.apache.doris.nereids.trees.expressions.SlotReference; import org.apache.doris.nereids.trees.expressions.functions.agg.MultiDistinction; import org.apache.doris.nereids.trees.plans.AggMode; -import org.apache.doris.nereids.trees.plans.GroupPlan; import org.apache.doris.nereids.trees.plans.JoinType; import org.apache.doris.nereids.trees.plans.Plan; import org.apache.doris.nereids.trees.plans.SortPhase; @@ -40,7 +39,6 @@ import org.apache.doris.nereids.trees.plans.physical.PhysicalFilter; import org.apache.doris.nereids.trees.plans.physical.PhysicalHashAggregate; import org.apache.doris.nereids.trees.plans.physical.PhysicalHashJoin; import org.apache.doris.nereids.trees.plans.physical.PhysicalNestedLoopJoin; -import org.apache.doris.nereids.trees.plans.physical.PhysicalOlapScan; import org.apache.doris.nereids.trees.plans.physical.PhysicalPartitionTopN; import org.apache.doris.nereids.trees.plans.physical.PhysicalProject; import org.apache.doris.nereids.trees.plans.physical.PhysicalSetOperation; @@ -209,38 +207,12 @@ public class ChildrenPropertiesRegulator extends PlanVisitor<Boolean, Void> { return true; } - private boolean isBucketShuffleDownGrade(Plan oneSidePlan, DistributionSpecHash otherSideSpec) { - // improper to do bucket shuffle join: - // oneSide: - // 1. base table - // 2. single partition after pruning - // 3. tablets' number is small enough (< paraInstanceNum) - // otherSide: ShuffleType.EXECUTION_BUCKETED + private boolean isBucketShuffleDownGrade(DistributionSpecHash srcSideSpec) { boolean isBucketShuffleDownGrade = ConnectContext.get().getSessionVariable().isEnableBucketShuffleDownGrade(); if (!isBucketShuffleDownGrade) { return false; - } else if (otherSideSpec.getShuffleType() != ShuffleType.EXECUTION_BUCKETED) { - return false; } else { - int paraNum = Math.max(1, ConnectContext.get().getSessionVariable().getParallelExecInstanceNum()); - if (((GroupPlan) oneSidePlan).getGroup().getPhysicalExpressions().isEmpty()) { - return false; - } else { - Plan plan = ((GroupPlan) oneSidePlan).getGroup().getPhysicalExpressions().get(0).getPlan(); - while ((plan instanceof PhysicalProject || plan instanceof PhysicalFilter) - && !((GroupPlan) plan.child(0)).getGroup().getPhysicalExpressions().isEmpty()) { - plan = ((GroupPlan) plan.child(0)).getGroup().getPhysicalExpressions().get(0).getPlan(); - } - if (plan != null && plan instanceof PhysicalOlapScan - && ((PhysicalOlapScan) plan).getSelectedPartitionIds().size() <= 1 - && ((PhysicalOlapScan) plan).getTable() != null - && ((PhysicalOlapScan) plan).getTable().getDefaultDistributionInfo() != null - && ((PhysicalOlapScan) plan).getTable().getDefaultDistributionInfo().getBucketNum() < paraNum) { - return true; - } else { - return false; - } - } + return srcSideSpec.getShuffleType() == ShuffleType.EXECUTION_BUCKETED; } } @@ -262,9 +234,6 @@ public class ChildrenPropertiesRegulator extends PlanVisitor<Boolean, Void> { DistributionSpec leftDistributionSpec = childrenProperties.get(0).getDistributionSpec(); DistributionSpec rightDistributionSpec = childrenProperties.get(1).getDistributionSpec(); - Plan leftChild = hashJoin.child(0); - Plan rightChild = hashJoin.child(1); - // broadcast do not need regular if (rightDistributionSpec instanceof DistributionSpecReplicated) { return true; @@ -296,7 +265,7 @@ public class ChildrenPropertiesRegulator extends PlanVisitor<Boolean, Void> { ShuffleType.EXECUTION_BUCKETED, leftHashSpec, rightHashSpec, (DistributionSpecHash) requiredProperties.get(0).getDistributionSpec(), (DistributionSpecHash) requiredProperties.get(1).getDistributionSpec())); - } else if (isBucketShuffleDownGrade(leftChild, rightHashSpec)) { + } else if (isBucketShuffleDownGrade(rightHashSpec)) { updatedForLeft = Optional.of(calAnotherSideRequired( ShuffleType.EXECUTION_BUCKETED, leftHashSpec, leftHashSpec, (DistributionSpecHash) requiredProperties.get(0).getDistributionSpec(), @@ -305,7 +274,7 @@ public class ChildrenPropertiesRegulator extends PlanVisitor<Boolean, Void> { ShuffleType.EXECUTION_BUCKETED, leftHashSpec, rightHashSpec, (DistributionSpecHash) requiredProperties.get(0).getDistributionSpec(), (DistributionSpecHash) requiredProperties.get(1).getDistributionSpec())); - } else if (isBucketShuffleDownGrade(rightChild, leftHashSpec)) { + } else if (isBucketShuffleDownGrade(leftHashSpec)) { updatedForLeft = Optional.of(calAnotherSideRequired( ShuffleType.EXECUTION_BUCKETED, rightHashSpec, leftHashSpec, (DistributionSpecHash) requiredProperties.get(1).getDistributionSpec(), diff --git a/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query13.out b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query13.out new file mode 100644 index 00000000000..ff71ac9d027 --- /dev/null +++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query13.out @@ -0,0 +1,40 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !ds_shape_13 -- +PhysicalResultSink +--hashAgg[GLOBAL] +----PhysicalDistribute[DistributionSpecGather] +------hashAgg[LOCAL] +--------PhysicalProject +----------hashJoin[INNER_JOIN] hashCondition=((store.s_store_sk = store_sales.ss_store_sk)) otherCondition=() build RFs:RF4 ss_store_sk->[s_store_sk] +------------PhysicalDistribute[DistributionSpecHash] +--------------PhysicalProject +----------------PhysicalOlapScan[store] apply RFs: RF4 +------------PhysicalDistribute[DistributionSpecHash] +--------------PhysicalProject +----------------hashJoin[INNER_JOIN] hashCondition=((customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk)) otherCondition=((((household_demographics.hd_dep_count = 1) AND ((((customer_demographics.cd_marital_status = 'D') AND (customer_demographics.cd_education_status = 'Primary')) AND ((store_sales.ss_sales_price >= 50.00) AND (store_sales.ss_sales_price <= 100.00))) OR (((customer_demographics.cd_marital_status = 'W') AND (customer_demographics.cd_education_status = '2 yr Degree [...] +------------------PhysicalDistribute[DistributionSpecHash] +--------------------PhysicalProject +----------------------filter(((((customer_demographics.cd_marital_status = 'M') AND (customer_demographics.cd_education_status = 'College')) OR ((customer_demographics.cd_marital_status = 'D') AND (customer_demographics.cd_education_status = 'Primary'))) OR ((customer_demographics.cd_marital_status = 'W') AND (customer_demographics.cd_education_status = '2 yr Degree')))) +------------------------PhysicalOlapScan[customer_demographics] apply RFs: RF3 +------------------PhysicalDistribute[DistributionSpecHash] +--------------------PhysicalProject +----------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk)) otherCondition=() build RFs:RF2 hd_demo_sk->[ss_hdemo_sk] +------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[ss_sold_date_sk] +--------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_addr_sk = customer_address.ca_address_sk)) otherCondition=((((ca_state IN ('IL', 'TN', 'TX') AND ((store_sales.ss_net_profit >= 100.00) AND (store_sales.ss_net_profit <= 200.00))) OR (ca_state IN ('ID', 'OH', 'WY') AND ((store_sales.ss_net_profit >= 150.00) AND (store_sales.ss_net_profit <= 300.00)))) OR (ca_state IN ('IA', 'MS', 'SC') AND ((store_sales.ss_net_profit >= 50.00) AND (store_sales.ss_net_profit <= [...] +----------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------PhysicalProject +--------------------------------filter((store_sales.ss_net_profit <= 300.00) and (store_sales.ss_net_profit >= 50.00) and (store_sales.ss_sales_price <= 200.00) and (store_sales.ss_sales_price >= 50.00)) +----------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1 RF2 +----------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------PhysicalProject +--------------------------------filter((customer_address.ca_country = 'United States') and ca_state IN ('IA', 'ID', 'IL', 'MS', 'OH', 'SC', 'TN', 'TX', 'WY')) +----------------------------------PhysicalOlapScan[customer_address] +--------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------PhysicalProject +------------------------------filter((date_dim.d_year = 2001)) +--------------------------------PhysicalOlapScan[date_dim] +------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------PhysicalProject +----------------------------filter(hd_dep_count IN (1, 3)) +------------------------------PhysicalOlapScan[household_demographics] + diff --git a/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query19.out b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query19.out new file mode 100644 index 00000000000..6b7d023e3be --- /dev/null +++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query19.out @@ -0,0 +1,41 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !ds_shape_19 -- +PhysicalResultSink +--PhysicalTopN[MERGE_SORT] +----PhysicalDistribute[DistributionSpecGather] +------PhysicalTopN[LOCAL_SORT] +--------PhysicalProject +----------hashAgg[GLOBAL] +------------PhysicalDistribute[DistributionSpecHash] +--------------hashAgg[LOCAL] +----------------PhysicalProject +------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=(( not (substring(ca_zip, 1, 5) = substring(s_zip, 1, 5)))) build RFs:RF4 s_store_sk->[ss_store_sk] +--------------------PhysicalProject +----------------------hashJoin[INNER_JOIN] hashCondition=((customer.c_current_addr_sk = customer_address.ca_address_sk)) otherCondition=() build RFs:RF3 c_current_addr_sk->[ca_address_sk] +------------------------PhysicalDistribute[DistributionSpecHash] +--------------------------PhysicalProject +----------------------------PhysicalOlapScan[customer_address] apply RFs: RF3 +------------------------PhysicalDistribute[DistributionSpecHash] +--------------------------PhysicalProject +----------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk)) otherCondition=() build RFs:RF2 ss_customer_sk->[c_customer_sk] +------------------------------PhysicalDistribute[DistributionSpecHash] +--------------------------------PhysicalProject +----------------------------------PhysicalOlapScan[customer] apply RFs: RF2 +------------------------------PhysicalDistribute[DistributionSpecHash] +--------------------------------PhysicalProject +----------------------------------hashJoin[INNER_JOIN] hashCondition=((date_dim.d_date_sk = store_sales.ss_sold_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[ss_sold_date_sk] +------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF0 i_item_sk->[ss_item_sk] +--------------------------------------PhysicalProject +----------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1 RF4 +--------------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------------PhysicalProject +------------------------------------------filter((item.i_manager_id = 14)) +--------------------------------------------PhysicalOlapScan[item] +------------------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------------------PhysicalProject +----------------------------------------filter((date_dim.d_moy = 11) and (date_dim.d_year = 2002)) +------------------------------------------PhysicalOlapScan[date_dim] +--------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------PhysicalProject +------------------------PhysicalOlapScan[store] + diff --git a/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query44.out b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query44.out new file mode 100644 index 00000000000..ea4ea67293e --- /dev/null +++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query44.out @@ -0,0 +1,75 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !ds_shape_44 -- +PhysicalResultSink +--PhysicalTopN[MERGE_SORT] +----PhysicalDistribute[DistributionSpecGather] +------PhysicalTopN[LOCAL_SORT] +--------PhysicalProject +----------hashJoin[INNER_JOIN] hashCondition=((asceding.rnk = descending.rnk)) otherCondition=() +------------PhysicalDistribute[DistributionSpecHash] +--------------PhysicalProject +----------------hashJoin[INNER_JOIN] hashCondition=((i1.i_item_sk = asceding.item_sk)) otherCondition=() build RFs:RF1 item_sk->[i_item_sk] +------------------PhysicalProject +--------------------PhysicalOlapScan[item] apply RFs: RF1 +------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------PhysicalProject +----------------------filter((rnk < 11)) +------------------------PhysicalWindow +--------------------------PhysicalQuickSort[MERGE_SORT] +----------------------------PhysicalDistribute[DistributionSpecGather] +------------------------------PhysicalQuickSort[LOCAL_SORT] +--------------------------------PhysicalPartitionTopN +----------------------------------PhysicalProject +------------------------------------NestedLoopJoin[INNER_JOIN](cast(rank_col as DOUBLE) > cast((0.9 * rank_col) as DOUBLE)) +--------------------------------------PhysicalProject +----------------------------------------hashAgg[GLOBAL] +------------------------------------------PhysicalDistribute[DistributionSpecHash] +--------------------------------------------hashAgg[LOCAL] +----------------------------------------------PhysicalProject +------------------------------------------------filter((ss1.ss_store_sk = 4)) +--------------------------------------------------PhysicalOlapScan[store_sales] +--------------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------------PhysicalProject +------------------------------------------PhysicalAssertNumRows +--------------------------------------------PhysicalDistribute[DistributionSpecGather] +----------------------------------------------PhysicalProject +------------------------------------------------hashAgg[GLOBAL] +--------------------------------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------------------------------hashAgg[LOCAL] +------------------------------------------------------PhysicalProject +--------------------------------------------------------filter((store_sales.ss_store_sk = 4) and ss_hdemo_sk IS NULL) +----------------------------------------------------------PhysicalOlapScan[store_sales] +------------PhysicalDistribute[DistributionSpecHash] +--------------PhysicalProject +----------------hashJoin[INNER_JOIN] hashCondition=((i2.i_item_sk = descending.item_sk)) otherCondition=() build RFs:RF0 item_sk->[i_item_sk] +------------------PhysicalProject +--------------------PhysicalOlapScan[item] apply RFs: RF0 +------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------PhysicalProject +----------------------filter((rnk < 11)) +------------------------PhysicalWindow +--------------------------PhysicalQuickSort[MERGE_SORT] +----------------------------PhysicalDistribute[DistributionSpecGather] +------------------------------PhysicalQuickSort[LOCAL_SORT] +--------------------------------PhysicalPartitionTopN +----------------------------------PhysicalProject +------------------------------------NestedLoopJoin[INNER_JOIN](cast(rank_col as DOUBLE) > cast((0.9 * rank_col) as DOUBLE)) +--------------------------------------PhysicalProject +----------------------------------------hashAgg[GLOBAL] +------------------------------------------PhysicalDistribute[DistributionSpecHash] +--------------------------------------------hashAgg[LOCAL] +----------------------------------------------PhysicalProject +------------------------------------------------filter((ss1.ss_store_sk = 4)) +--------------------------------------------------PhysicalOlapScan[store_sales] +--------------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------------PhysicalProject +------------------------------------------PhysicalAssertNumRows +--------------------------------------------PhysicalDistribute[DistributionSpecGather] +----------------------------------------------PhysicalProject +------------------------------------------------hashAgg[GLOBAL] +--------------------------------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------------------------------hashAgg[LOCAL] +------------------------------------------------------PhysicalProject +--------------------------------------------------------filter((store_sales.ss_store_sk = 4) and ss_hdemo_sk IS NULL) +----------------------------------------------------------PhysicalOlapScan[store_sales] + diff --git a/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query45.out b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query45.out new file mode 100644 index 00000000000..091bda5f39c --- /dev/null +++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query45.out @@ -0,0 +1,38 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !ds_shape_45 -- +PhysicalResultSink +--PhysicalTopN[MERGE_SORT] +----PhysicalDistribute[DistributionSpecGather] +------PhysicalTopN[LOCAL_SORT] +--------hashAgg[GLOBAL] +----------PhysicalDistribute[DistributionSpecHash] +------------hashAgg[LOCAL] +--------------PhysicalProject +----------------filter((substring(ca_zip, 1, 5) IN ('80348', '81792', '83405', '85392', '85460', '85669', '86197', '86475', '88274') OR $c$1)) +------------------hashJoin[INNER_JOIN] hashCondition=((customer.c_current_addr_sk = customer_address.ca_address_sk)) otherCondition=() build RFs:RF3 c_current_addr_sk->[ca_address_sk] +--------------------PhysicalProject +----------------------PhysicalOlapScan[customer_address] apply RFs: RF3 +--------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------PhysicalProject +------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_bill_customer_sk = customer.c_customer_sk)) otherCondition=() build RFs:RF2 ws_bill_customer_sk->[c_customer_sk] +--------------------------PhysicalProject +----------------------------PhysicalOlapScan[customer] apply RFs: RF2 +--------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------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] +------------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF0 i_item_sk->[ws_item_sk] +--------------------------------PhysicalProject +----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0 RF1 +--------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------PhysicalProject +------------------------------------hashJoin[LEFT_SEMI_JOIN] hashCondition=((item.i_item_id = item.i_item_id)) otherCondition=() +--------------------------------------PhysicalProject +----------------------------------------PhysicalOlapScan[item] +--------------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------------PhysicalProject +------------------------------------------filter(i_item_sk IN (11, 13, 17, 19, 2, 23, 29, 3, 5, 7)) +--------------------------------------------PhysicalOlapScan[item] +------------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------------PhysicalProject +----------------------------------filter((date_dim.d_qoy = 1) and (date_dim.d_year = 2000)) +------------------------------------PhysicalOlapScan[date_dim] + diff --git a/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query54.out b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query54.out new file mode 100644 index 00000000000..be67d64e1d3 --- /dev/null +++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query54.out @@ -0,0 +1,84 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !ds_shape_54 -- +PhysicalResultSink +--PhysicalTopN[MERGE_SORT] +----PhysicalDistribute[DistributionSpecGather] +------PhysicalTopN[LOCAL_SORT] +--------PhysicalProject +----------hashAgg[GLOBAL] +------------PhysicalDistribute[DistributionSpecHash] +--------------hashAgg[LOCAL] +----------------PhysicalProject +------------------hashAgg[GLOBAL] +--------------------PhysicalDistribute[DistributionSpecHash] +----------------------hashAgg[LOCAL] +------------------------PhysicalProject +--------------------------NestedLoopJoin[INNER_JOIN](cast(d_month_seq as BIGINT) <= (d_month_seq + 3)) +----------------------------PhysicalProject +------------------------------NestedLoopJoin[INNER_JOIN](cast(d_month_seq as BIGINT) >= (d_month_seq + 1)) +--------------------------------PhysicalProject +----------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF7 d_date_sk->[ss_sold_date_sk] +------------------------------------PhysicalProject +--------------------------------------hashJoin[INNER_JOIN] hashCondition=((my_customers.c_customer_sk = store_sales.ss_customer_sk)) otherCondition=() build RFs:RF6 c_customer_sk->[ss_customer_sk] +----------------------------------------PhysicalProject +------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF6 RF7 +----------------------------------------PhysicalDistribute[DistributionSpecReplicated] +------------------------------------------PhysicalProject +--------------------------------------------hashJoin[INNER_JOIN] hashCondition=((customer_address.ca_county = store.s_county) and (customer_address.ca_state = store.s_state)) otherCondition=() build RFs:RF4 s_county->[ca_county];RF5 s_state->[ca_state] +----------------------------------------------hashJoin[INNER_JOIN] hashCondition=((my_customers.c_current_addr_sk = customer_address.ca_address_sk)) otherCondition=() build RFs:RF3 c_current_addr_sk->[ca_address_sk] +------------------------------------------------PhysicalProject +--------------------------------------------------PhysicalOlapScan[customer_address] apply RFs: RF3 RF4 RF5 +------------------------------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------------------------------PhysicalProject +----------------------------------------------------hashAgg[GLOBAL] +------------------------------------------------------PhysicalDistribute[DistributionSpecHash] +--------------------------------------------------------hashAgg[LOCAL] +----------------------------------------------------------PhysicalProject +------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((customer.c_customer_sk = cs_or_ws_sales.customer_sk)) otherCondition=() build RFs:RF2 customer_sk->[c_customer_sk] +--------------------------------------------------------------PhysicalProject +----------------------------------------------------------------PhysicalOlapScan[customer] apply RFs: RF2 +--------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------------------------------------PhysicalProject +------------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((cs_or_ws_sales.sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[cs_sold_date_sk,ws_sold_date_sk] +--------------------------------------------------------------------PhysicalProject +----------------------------------------------------------------------hashJoin[INNER_JOIN] hashCondition=((cs_or_ws_sales.item_sk = item.i_item_sk)) otherCondition=() build RFs:RF0 i_item_sk->[cs_item_sk,ws_item_sk] +------------------------------------------------------------------------PhysicalUnion +--------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------------------------------------------------------PhysicalProject +------------------------------------------------------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0 RF1 +--------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------------------------------------------------------PhysicalProject +------------------------------------------------------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0 RF1 +------------------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------------------------------------------------------PhysicalProject +----------------------------------------------------------------------------filter((item.i_category = 'Music') and (item.i_class = 'country')) +------------------------------------------------------------------------------PhysicalOlapScan[item] +--------------------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------------------------------------------PhysicalProject +------------------------------------------------------------------------filter((date_dim.d_moy = 1) and (date_dim.d_year = 1999)) +--------------------------------------------------------------------------PhysicalOlapScan[date_dim] +----------------------------------------------PhysicalDistribute[DistributionSpecReplicated] +------------------------------------------------PhysicalProject +--------------------------------------------------PhysicalOlapScan[store] +------------------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------------------PhysicalProject +----------------------------------------PhysicalOlapScan[date_dim] +--------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------PhysicalAssertNumRows +------------------------------------PhysicalDistribute[DistributionSpecGather] +--------------------------------------hashAgg[GLOBAL] +----------------------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------------------hashAgg[LOCAL] +--------------------------------------------PhysicalProject +----------------------------------------------filter((date_dim.d_moy = 1) and (date_dim.d_year = 1999)) +------------------------------------------------PhysicalOlapScan[date_dim] +----------------------------PhysicalDistribute[DistributionSpecReplicated] +------------------------------PhysicalAssertNumRows +--------------------------------PhysicalDistribute[DistributionSpecGather] +----------------------------------hashAgg[GLOBAL] +------------------------------------PhysicalDistribute[DistributionSpecHash] +--------------------------------------hashAgg[LOCAL] +----------------------------------------PhysicalProject +------------------------------------------filter((date_dim.d_moy = 1) and (date_dim.d_year = 1999)) +--------------------------------------------PhysicalOlapScan[date_dim] + diff --git a/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query56.out b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query56.out new file mode 100644 index 00000000000..b74545fe806 --- /dev/null +++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query56.out @@ -0,0 +1,95 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !ds_shape_56 -- +PhysicalResultSink +--PhysicalTopN[MERGE_SORT] +----PhysicalDistribute[DistributionSpecGather] +------PhysicalTopN[LOCAL_SORT] +--------hashAgg[GLOBAL] +----------PhysicalDistribute[DistributionSpecHash] +------------hashAgg[LOCAL] +--------------PhysicalUnion +----------------PhysicalProject +------------------hashAgg[GLOBAL] +--------------------PhysicalDistribute[DistributionSpecHash] +----------------------hashAgg[LOCAL] +------------------------PhysicalProject +--------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_addr_sk = customer_address.ca_address_sk)) otherCondition=() build RFs:RF3 ca_address_sk->[ss_addr_sk] +----------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------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_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 RF2 RF3 +------------------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------------------PhysicalProject +----------------------------------------filter((date_dim.d_moy = 3) and (date_dim.d_year = 2000)) +------------------------------------------PhysicalOlapScan[date_dim] +--------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------hashJoin[LEFT_SEMI_JOIN] hashCondition=((item.i_item_id = item.i_item_id)) otherCondition=() build RFs:RF0 i_item_id->[i_item_id] +------------------------------------PhysicalProject +--------------------------------------PhysicalOlapScan[item] apply RFs: RF0 +------------------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------------------PhysicalProject +----------------------------------------filter(i_color IN ('orchid', 'pink', 'powder')) +------------------------------------------PhysicalOlapScan[item] +----------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------PhysicalProject +--------------------------------filter((customer_address.ca_gmt_offset = -6.00)) +----------------------------------PhysicalOlapScan[customer_address] +----------------PhysicalProject +------------------hashAgg[GLOBAL] +--------------------PhysicalDistribute[DistributionSpecHash] +----------------------hashAgg[LOCAL] +------------------------PhysicalProject +--------------------------hashJoin[INNER_JOIN] hashCondition=((catalog_sales.cs_bill_addr_sk = customer_address.ca_address_sk)) otherCondition=() build RFs:RF7 ca_address_sk->[cs_bill_addr_sk] +----------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------hashJoin[INNER_JOIN] hashCondition=((catalog_sales.cs_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF6 i_item_sk->[cs_item_sk] +--------------------------------PhysicalProject +----------------------------------hashJoin[INNER_JOIN] 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 RF6 RF7 +------------------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------------------PhysicalProject +----------------------------------------filter((date_dim.d_moy = 3) and (date_dim.d_year = 2000)) +------------------------------------------PhysicalOlapScan[date_dim] +--------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------hashJoin[LEFT_SEMI_JOIN] hashCondition=((item.i_item_id = item.i_item_id)) otherCondition=() build RFs:RF4 i_item_id->[i_item_id] +------------------------------------PhysicalProject +--------------------------------------PhysicalOlapScan[item] apply RFs: RF4 +------------------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------------------PhysicalProject +----------------------------------------filter(i_color IN ('orchid', 'pink', 'powder')) +------------------------------------------PhysicalOlapScan[item] +----------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------PhysicalProject +--------------------------------filter((customer_address.ca_gmt_offset = -6.00)) +----------------------------------PhysicalOlapScan[customer_address] +----------------PhysicalProject +------------------hashAgg[GLOBAL] +--------------------PhysicalDistribute[DistributionSpecHash] +----------------------hashAgg[LOCAL] +------------------------PhysicalProject +--------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_bill_addr_sk = customer_address.ca_address_sk)) otherCondition=() build RFs:RF11 ws_bill_addr_sk->[ca_address_sk] +----------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------PhysicalProject +--------------------------------filter((customer_address.ca_gmt_offset = -6.00)) +----------------------------------PhysicalOlapScan[customer_address] apply RFs: RF11 +----------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF10 i_item_sk->[ws_item_sk] +--------------------------------PhysicalProject +----------------------------------hashJoin[INNER_JOIN] hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF9 d_date_sk->[ws_sold_date_sk] +------------------------------------PhysicalProject +--------------------------------------PhysicalOlapScan[web_sales] apply RFs: RF9 RF10 +------------------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------------------PhysicalProject +----------------------------------------filter((date_dim.d_moy = 3) and (date_dim.d_year = 2000)) +------------------------------------------PhysicalOlapScan[date_dim] +--------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------hashJoin[LEFT_SEMI_JOIN] hashCondition=((item.i_item_id = item.i_item_id)) otherCondition=() build RFs:RF8 i_item_id->[i_item_id] +------------------------------------PhysicalProject +--------------------------------------PhysicalOlapScan[item] apply RFs: RF8 +------------------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------------------PhysicalProject +----------------------------------------filter(i_color IN ('orchid', 'pink', 'powder')) +------------------------------------------PhysicalOlapScan[item] + diff --git a/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query6.out b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query6.out new file mode 100644 index 00000000000..b2169d25149 --- /dev/null +++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query6.out @@ -0,0 +1,54 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !ds_shape_6 -- +PhysicalResultSink +--PhysicalTopN[MERGE_SORT] +----PhysicalDistribute[DistributionSpecGather] +------PhysicalTopN[LOCAL_SORT] +--------PhysicalProject +----------filter((cnt >= 10)) +------------hashAgg[GLOBAL] +--------------PhysicalDistribute[DistributionSpecHash] +----------------hashAgg[LOCAL] +------------------PhysicalProject +--------------------hashJoin[INNER_JOIN] hashCondition=((a.ca_address_sk = c.c_current_addr_sk)) otherCondition=() build RFs:RF5 c_current_addr_sk->[ca_address_sk] +----------------------PhysicalDistribute[DistributionSpecHash] +------------------------PhysicalProject +--------------------------PhysicalOlapScan[customer_address] apply RFs: RF5 +----------------------PhysicalDistribute[DistributionSpecHash] +------------------------PhysicalProject +--------------------------hashJoin[INNER_JOIN] hashCondition=((c.c_customer_sk = s.ss_customer_sk)) otherCondition=() build RFs:RF4 ss_customer_sk->[c_customer_sk] +----------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------PhysicalProject +--------------------------------PhysicalOlapScan[customer] apply RFs: RF4 +----------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------PhysicalProject +--------------------------------hashJoin[INNER_JOIN] hashCondition=((s.ss_item_sk = i.i_item_sk)) otherCondition=() build RFs:RF3 i_item_sk->[ss_item_sk] +----------------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------------PhysicalProject +--------------------------------------hashJoin[INNER_JOIN] hashCondition=((s.ss_sold_date_sk = d.d_date_sk)) otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk] +----------------------------------------PhysicalProject +------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF2 RF3 +----------------------------------------PhysicalDistribute[DistributionSpecReplicated] +------------------------------------------hashJoin[INNER_JOIN] hashCondition=((d.d_month_seq = date_dim.d_month_seq)) otherCondition=() build RFs:RF1 d_month_seq->[d_month_seq] +--------------------------------------------PhysicalProject +----------------------------------------------PhysicalOlapScan[date_dim] apply RFs: RF1 +--------------------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------------------PhysicalAssertNumRows +------------------------------------------------PhysicalDistribute[DistributionSpecGather] +--------------------------------------------------hashAgg[GLOBAL] +----------------------------------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------------------------------hashAgg[LOCAL] +--------------------------------------------------------PhysicalProject +----------------------------------------------------------filter((date_dim.d_moy = 3) and (date_dim.d_year = 2002)) +------------------------------------------------------------PhysicalOlapScan[date_dim] +----------------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------------hashJoin[INNER_JOIN] hashCondition=((j.i_category = i.i_category)) otherCondition=((cast(i_current_price as DECIMALV3(38, 5)) > (1.2 * avg(cast(i_current_price as DECIMALV3(9, 4)))))) build RFs:RF0 i_category->[i_category] +--------------------------------------PhysicalProject +----------------------------------------PhysicalOlapScan[item] apply RFs: RF0 +--------------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------------hashAgg[GLOBAL] +------------------------------------------PhysicalDistribute[DistributionSpecHash] +--------------------------------------------hashAgg[LOCAL] +----------------------------------------------PhysicalProject +------------------------------------------------PhysicalOlapScan[item] + diff --git a/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query61.out b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query61.out new file mode 100644 index 00000000000..982462b51cf --- /dev/null +++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query61.out @@ -0,0 +1,83 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !ds_shape_61 -- +PhysicalResultSink +--PhysicalTopN[MERGE_SORT] +----PhysicalTopN[LOCAL_SORT] +------PhysicalProject +--------NestedLoopJoin[CROSS_JOIN] +----------hashAgg[GLOBAL] +------------PhysicalDistribute[DistributionSpecGather] +--------------hashAgg[LOCAL] +----------------PhysicalProject +------------------hashJoin[INNER_JOIN] hashCondition=((customer_address.ca_address_sk = customer.c_current_addr_sk)) otherCondition=() build RFs:RF10 c_current_addr_sk->[ca_address_sk] +--------------------PhysicalDistribute[DistributionSpecHash] +----------------------PhysicalProject +------------------------filter((customer_address.ca_gmt_offset = -7.00)) +--------------------------PhysicalOlapScan[customer_address] apply RFs: RF10 +--------------------PhysicalDistribute[DistributionSpecHash] +----------------------PhysicalProject +------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk)) otherCondition=() build RFs:RF9 ss_customer_sk->[c_customer_sk] +--------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------PhysicalProject +------------------------------PhysicalOlapScan[customer] apply RFs: RF9 +--------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------PhysicalProject +------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=() build RFs:RF8 s_store_sk->[ss_store_sk] +--------------------------------PhysicalProject +----------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_promo_sk = promotion.p_promo_sk)) otherCondition=() build RFs:RF7 p_promo_sk->[ss_promo_sk] +------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF6 i_item_sk->[ss_item_sk] +--------------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF5 d_date_sk->[ss_sold_date_sk] +----------------------------------------PhysicalProject +------------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF5 RF6 RF7 RF8 +----------------------------------------PhysicalDistribute[DistributionSpecReplicated] +------------------------------------------PhysicalProject +--------------------------------------------filter((date_dim.d_moy = 12) and (date_dim.d_year = 2000)) +----------------------------------------------PhysicalOlapScan[date_dim] +--------------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------------PhysicalProject +------------------------------------------filter((item.i_category = 'Home')) +--------------------------------------------PhysicalOlapScan[item] +------------------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------------------PhysicalProject +----------------------------------------filter((((promotion.p_channel_dmail = 'Y') OR (promotion.p_channel_email = 'Y')) OR (promotion.p_channel_tv = 'Y'))) +------------------------------------------PhysicalOlapScan[promotion] +--------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------PhysicalProject +------------------------------------filter((store.s_gmt_offset = -7.00)) +--------------------------------------PhysicalOlapScan[store] +----------PhysicalDistribute[DistributionSpecReplicated] +------------hashAgg[GLOBAL] +--------------PhysicalDistribute[DistributionSpecGather] +----------------hashAgg[LOCAL] +------------------PhysicalProject +--------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=() build RFs:RF4 s_store_sk->[ss_store_sk] +----------------------PhysicalProject +------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk)) otherCondition=() build RFs:RF3 c_customer_sk->[ss_customer_sk] +--------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------PhysicalProject +------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF2 i_item_sk->[ss_item_sk] +--------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF1 d_date_sk->[ss_sold_date_sk] +----------------------------------PhysicalProject +------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF1 RF2 RF3 RF4 +----------------------------------PhysicalDistribute[DistributionSpecReplicated] +------------------------------------PhysicalProject +--------------------------------------filter((date_dim.d_moy = 12) and (date_dim.d_year = 2000)) +----------------------------------------PhysicalOlapScan[date_dim] +--------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------PhysicalProject +------------------------------------filter((item.i_category = 'Home')) +--------------------------------------PhysicalOlapScan[item] +--------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------hashJoin[INNER_JOIN] hashCondition=((customer_address.ca_address_sk = customer.c_current_addr_sk)) otherCondition=() build RFs:RF0 ca_address_sk->[c_current_addr_sk] +------------------------------PhysicalDistribute[DistributionSpecHash] +--------------------------------PhysicalProject +----------------------------------PhysicalOlapScan[customer] apply RFs: RF0 +------------------------------PhysicalDistribute[DistributionSpecHash] +--------------------------------PhysicalProject +----------------------------------filter((customer_address.ca_gmt_offset = -7.00)) +------------------------------------PhysicalOlapScan[customer_address] +----------------------PhysicalDistribute[DistributionSpecReplicated] +------------------------PhysicalProject +--------------------------filter((store.s_gmt_offset = -7.00)) +----------------------------PhysicalOlapScan[store] + diff --git a/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query68.out b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query68.out new file mode 100644 index 00000000000..dc8b5303dfd --- /dev/null +++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query68.out @@ -0,0 +1,47 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !ds_shape_68 -- +PhysicalResultSink +--PhysicalTopN[MERGE_SORT] +----PhysicalDistribute[DistributionSpecGather] +------PhysicalTopN[LOCAL_SORT] +--------PhysicalProject +----------hashJoin[INNER_JOIN] hashCondition=((customer.c_current_addr_sk = current_addr.ca_address_sk)) otherCondition=(( not (ca_city = bought_city))) build RFs:RF5 c_current_addr_sk->[ca_address_sk] +------------PhysicalDistribute[DistributionSpecHash] +--------------PhysicalProject +----------------PhysicalOlapScan[customer_address] apply RFs: RF5 +------------PhysicalDistribute[DistributionSpecHash] +--------------PhysicalProject +----------------hashJoin[INNER_JOIN] hashCondition=((dn.ss_customer_sk = customer.c_customer_sk)) otherCondition=() build RFs:RF4 ss_customer_sk->[c_customer_sk] +------------------PhysicalDistribute[DistributionSpecHash] +--------------------PhysicalProject +----------------------PhysicalOlapScan[customer] apply RFs: RF4 +------------------PhysicalDistribute[DistributionSpecHash] +--------------------PhysicalProject +----------------------hashAgg[LOCAL] +------------------------PhysicalProject +--------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_addr_sk = customer_address.ca_address_sk)) otherCondition=() build RFs:RF3 ss_addr_sk->[ca_address_sk] +----------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------PhysicalProject +--------------------------------PhysicalOlapScan[customer_address] apply RFs: RF3 +----------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------PhysicalProject +--------------------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk)) otherCondition=() build RFs:RF2 hd_demo_sk->[ss_hdemo_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_dom <= 2) and (date_dim.d_dom >= 1) and d_year IN (1998, 1999, 2000)) +------------------------------------------------PhysicalOlapScan[date_dim] +--------------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------------PhysicalProject +------------------------------------------filter(s_city IN ('Fairview', 'Midway')) +--------------------------------------------PhysicalOlapScan[store] +----------------------------------PhysicalDistribute[DistributionSpecReplicated] +------------------------------------PhysicalProject +--------------------------------------filter(((household_demographics.hd_dep_count = 3) OR (household_demographics.hd_vehicle_count = 4))) +----------------------------------------PhysicalOlapScan[household_demographics] + diff --git a/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query8.out b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query8.out new file mode 100644 index 00000000000..aa750d86818 --- /dev/null +++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query8.out @@ -0,0 +1,47 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !ds_shape_8 -- +PhysicalResultSink +--PhysicalTopN[MERGE_SORT] +----PhysicalDistribute[DistributionSpecGather] +------PhysicalTopN[LOCAL_SORT] +--------hashAgg[GLOBAL] +----------PhysicalDistribute[DistributionSpecHash] +------------hashAgg[LOCAL] +--------------PhysicalProject +----------------hashJoin[INNER_JOIN] hashCondition=((expr_substring(s_zip, 1, 2) = expr_substring(ca_zip, 1, 2))) otherCondition=() +------------------PhysicalProject +--------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_store_sk = store.s_store_sk)) otherCondition=() build RFs:RF2 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:RF1 d_date_sk->[ss_sold_date_sk] +--------------------------PhysicalProject +----------------------------PhysicalOlapScan[store_sales] apply RFs: RF1 RF2 +--------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------PhysicalProject +------------------------------filter((date_dim.d_qoy = 2) and (date_dim.d_year = 1998)) +--------------------------------PhysicalOlapScan[date_dim] +----------------------PhysicalDistribute[DistributionSpecReplicated] +------------------------PhysicalProject +--------------------------PhysicalOlapScan[store] +------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------PhysicalProject +----------------------PhysicalIntersect +------------------------PhysicalDistribute[DistributionSpecHash] +--------------------------PhysicalProject +----------------------------filter(substring(ca_zip, 1, 5) IN ('10298', '10374', '10425', '11340', '11489', '11618', '11652', '11686', '11855', '11912', '12197', '12318', '12320', '12350', '13086', '13123', '13261', '13338', '13376', '13378', '13443', '13844', '13869', '13918', '14073', '14155', '14196', '14242', '14312', '14440', '14530', '14851', '15371', '15475', '15543', '15734', '15751', '15782', '15794', '16005', '16226', '16364', '16515', '16704', '16791', '16891', '17167', '17193 [...] +------------------------------PhysicalOlapScan[customer_address] +------------------------PhysicalDistribute[DistributionSpecHash] +--------------------------PhysicalProject +----------------------------filter((cnt > 10)) +------------------------------hashAgg[GLOBAL] +--------------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------------hashAgg[LOCAL] +------------------------------------PhysicalProject +--------------------------------------hashJoin[INNER_JOIN] hashCondition=((customer_address.ca_address_sk = customer.c_current_addr_sk)) otherCondition=() build RFs:RF0 c_current_addr_sk->[ca_address_sk] +----------------------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------------------PhysicalProject +--------------------------------------------PhysicalOlapScan[customer_address] apply RFs: RF0 +----------------------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------------------PhysicalProject +--------------------------------------------filter((customer.c_preferred_cust_flag = 'Y')) +----------------------------------------------PhysicalOlapScan[customer] + diff --git a/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query91.out b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query91.out new file mode 100644 index 00000000000..42598f25208 --- /dev/null +++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query91.out @@ -0,0 +1,46 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !ds_shape_91 -- +PhysicalResultSink +--PhysicalQuickSort[MERGE_SORT] +----PhysicalDistribute[DistributionSpecGather] +------PhysicalQuickSort[LOCAL_SORT] +--------PhysicalProject +----------hashAgg[GLOBAL] +------------PhysicalDistribute[DistributionSpecHash] +--------------hashAgg[LOCAL] +----------------PhysicalProject +------------------hashJoin[INNER_JOIN] hashCondition=((catalog_returns.cr_call_center_sk = call_center.cc_call_center_sk)) otherCondition=() build RFs:RF5 cc_call_center_sk->[cr_call_center_sk] +--------------------PhysicalProject +----------------------hashJoin[INNER_JOIN] hashCondition=((catalog_returns.cr_returned_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF4 d_date_sk->[cr_returned_date_sk] +------------------------PhysicalProject +--------------------------hashJoin[INNER_JOIN] hashCondition=((catalog_returns.cr_returning_customer_sk = customer.c_customer_sk)) otherCondition=() build RFs:RF3 c_customer_sk->[cr_returning_customer_sk] +----------------------------PhysicalProject +------------------------------PhysicalOlapScan[catalog_returns] apply RFs: RF3 RF4 RF5 +----------------------------PhysicalDistribute[DistributionSpecReplicated] +------------------------------hashJoin[INNER_JOIN] hashCondition=((customer_address.ca_address_sk = customer.c_current_addr_sk)) otherCondition=() build RFs:RF2 c_current_addr_sk->[ca_address_sk] +--------------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------------PhysicalProject +------------------------------------filter((customer_address.ca_gmt_offset = -7.00)) +--------------------------------------PhysicalOlapScan[customer_address] apply RFs: RF2 +--------------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------------hashJoin[INNER_JOIN] hashCondition=((household_demographics.hd_demo_sk = customer.c_current_hdemo_sk)) otherCondition=() build RFs:RF1 hd_demo_sk->[c_current_hdemo_sk] +------------------------------------hashJoin[INNER_JOIN] hashCondition=((customer_demographics.cd_demo_sk = customer.c_current_cdemo_sk)) otherCondition=() build RFs:RF0 cd_demo_sk->[c_current_cdemo_sk] +--------------------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------------------PhysicalProject +------------------------------------------PhysicalOlapScan[customer] apply RFs: RF0 RF1 +--------------------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------------------PhysicalProject +------------------------------------------filter((((customer_demographics.cd_marital_status = 'M') AND (customer_demographics.cd_education_status = 'Unknown')) OR ((customer_demographics.cd_marital_status = 'W') AND (customer_demographics.cd_education_status = 'Advanced Degree')))) +--------------------------------------------PhysicalOlapScan[customer_demographics] +------------------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------------------PhysicalProject +----------------------------------------filter((hd_buy_potential like 'Unknown%')) +------------------------------------------PhysicalOlapScan[household_demographics] +------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------PhysicalProject +----------------------------filter((date_dim.d_moy = 12) and (date_dim.d_year = 2000)) +------------------------------PhysicalOlapScan[date_dim] +--------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------PhysicalProject +------------------------PhysicalOlapScan[call_center] + diff --git a/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query95.out b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query95.out new file mode 100644 index 00000000000..476a65baed6 --- /dev/null +++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query95.out @@ -0,0 +1,55 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !ds_shape_95 -- +PhysicalCteAnchor ( cteId=CTEId#0 ) +--PhysicalCteProducer ( cteId=CTEId#0 ) +----PhysicalProject +------hashJoin[INNER_JOIN] hashCondition=((ws1.ws_order_number = ws2.ws_order_number)) otherCondition=(( not (ws_warehouse_sk = ws_warehouse_sk))) build RFs:RF0 ws_order_number->[ws_order_number] +--------PhysicalDistribute[DistributionSpecHash] +----------PhysicalProject +------------PhysicalOlapScan[web_sales] apply RFs: RF0 RF7 +--------PhysicalDistribute[DistributionSpecHash] +----------PhysicalProject +------------PhysicalOlapScan[web_sales] apply RFs: RF7 +--PhysicalResultSink +----PhysicalTopN[MERGE_SORT] +------PhysicalTopN[LOCAL_SORT] +--------hashAgg[DISTINCT_GLOBAL] +----------PhysicalDistribute[DistributionSpecGather] +------------hashAgg[DISTINCT_LOCAL] +--------------hashAgg[GLOBAL] +----------------hashAgg[LOCAL] +------------------PhysicalProject +--------------------hashJoin[RIGHT_SEMI_JOIN] hashCondition=((ws1.ws_order_number = web_returns.wr_order_number)) otherCondition=() build RFs:RF6 ws_order_number->[wr_order_number,ws_order_number] +----------------------PhysicalDistribute[DistributionSpecHash] +------------------------PhysicalProject +--------------------------hashJoin[INNER_JOIN] hashCondition=((web_returns.wr_order_number = ws_wh.ws_order_number)) otherCondition=() build RFs:RF5 wr_order_number->[ws_order_number] +----------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------PhysicalProject +--------------------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF5 RF6 +----------------------------PhysicalDistribute[DistributionSpecHash] +------------------------------PhysicalProject +--------------------------------PhysicalOlapScan[web_returns] apply RFs: RF6 +----------------------PhysicalProject +------------------------hashJoin[RIGHT_SEMI_JOIN] hashCondition=((ws1.ws_order_number = ws_wh.ws_order_number)) otherCondition=() build RFs:RF7 ws_order_number->[ws_order_number,ws_order_number] +--------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------PhysicalProject +------------------------------PhysicalCteConsumer ( cteId=CTEId#0 ) +--------------------------PhysicalDistribute[DistributionSpecHash] +----------------------------hashJoin[INNER_JOIN] hashCondition=((ws1.ws_web_site_sk = web_site.web_site_sk)) otherCondition=() build RFs:RF3 web_site_sk->[ws_web_site_sk] +------------------------------hashJoin[INNER_JOIN] hashCondition=((ws1.ws_ship_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF2 d_date_sk->[ws_ship_date_sk] +--------------------------------hashJoin[INNER_JOIN] hashCondition=((ws1.ws_ship_addr_sk = customer_address.ca_address_sk)) otherCondition=() build RFs:RF1 ca_address_sk->[ws_ship_addr_sk] +----------------------------------PhysicalProject +------------------------------------PhysicalOlapScan[web_sales] apply RFs: RF1 RF2 RF3 +----------------------------------PhysicalDistribute[DistributionSpecReplicated] +------------------------------------PhysicalProject +--------------------------------------filter((customer_address.ca_state = 'VA')) +----------------------------------------PhysicalOlapScan[customer_address] +--------------------------------PhysicalDistribute[DistributionSpecReplicated] +----------------------------------PhysicalProject +------------------------------------filter((date_dim.d_date <= '2001-05-31') and (date_dim.d_date >= '2001-04-01')) +--------------------------------------PhysicalOlapScan[date_dim] +------------------------------PhysicalDistribute[DistributionSpecReplicated] +--------------------------------PhysicalProject +----------------------------------filter((web_site.web_company_name = 'pri')) +------------------------------------PhysicalOlapScan[web_site] + diff --git a/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query13.groovy b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query13.groovy new file mode 100644 index 00000000000..5718fe49c27 --- /dev/null +++ b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query13.groovy @@ -0,0 +1,136 @@ +/* + * 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("query13") { + 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' + sql 'set enable_bucket_shuffle_downgrade=true' + def ds = """select avg(ss_quantity) + ,avg(ss_ext_sales_price) + ,avg(ss_ext_wholesale_cost) + ,sum(ss_ext_wholesale_cost) + from store_sales + ,store + ,customer_demographics + ,household_demographics + ,customer_address + ,date_dim + where s_store_sk = ss_store_sk + and ss_sold_date_sk = d_date_sk and d_year = 2001 + and((ss_hdemo_sk=hd_demo_sk + and cd_demo_sk = ss_cdemo_sk + and cd_marital_status = 'M' + and cd_education_status = 'College' + and ss_sales_price between 100.00 and 150.00 + and hd_dep_count = 3 + )or + (ss_hdemo_sk=hd_demo_sk + and cd_demo_sk = ss_cdemo_sk + and cd_marital_status = 'D' + and cd_education_status = 'Primary' + and ss_sales_price between 50.00 and 100.00 + and hd_dep_count = 1 + ) or + (ss_hdemo_sk=hd_demo_sk + and cd_demo_sk = ss_cdemo_sk + and cd_marital_status = 'W' + and cd_education_status = '2 yr Degree' + and ss_sales_price between 150.00 and 200.00 + and hd_dep_count = 1 + )) + and((ss_addr_sk = ca_address_sk + and ca_country = 'United States' + and ca_state in ('IL', 'TN', 'TX') + and ss_net_profit between 100 and 200 + ) or + (ss_addr_sk = ca_address_sk + and ca_country = 'United States' + and ca_state in ('WY', 'OH', 'ID') + and ss_net_profit between 150 and 300 + ) or + (ss_addr_sk = ca_address_sk + and ca_country = 'United States' + and ca_state in ('MS', 'SC', 'IA') + and ss_net_profit between 50 and 250 + )) +""" + qt_ds_shape_13 ''' + explain shape plan + select avg(ss_quantity) + ,avg(ss_ext_sales_price) + ,avg(ss_ext_wholesale_cost) + ,sum(ss_ext_wholesale_cost) + from store_sales + ,store + ,customer_demographics + ,household_demographics + ,customer_address + ,date_dim + where s_store_sk = ss_store_sk + and ss_sold_date_sk = d_date_sk and d_year = 2001 + and((ss_hdemo_sk=hd_demo_sk + and cd_demo_sk = ss_cdemo_sk + and cd_marital_status = 'M' + and cd_education_status = 'College' + and ss_sales_price between 100.00 and 150.00 + and hd_dep_count = 3 + )or + (ss_hdemo_sk=hd_demo_sk + and cd_demo_sk = ss_cdemo_sk + and cd_marital_status = 'D' + and cd_education_status = 'Primary' + and ss_sales_price between 50.00 and 100.00 + and hd_dep_count = 1 + ) or + (ss_hdemo_sk=hd_demo_sk + and cd_demo_sk = ss_cdemo_sk + and cd_marital_status = 'W' + and cd_education_status = '2 yr Degree' + and ss_sales_price between 150.00 and 200.00 + and hd_dep_count = 1 + )) + and((ss_addr_sk = ca_address_sk + and ca_country = 'United States' + and ca_state in ('IL', 'TN', 'TX') + and ss_net_profit between 100 and 200 + ) or + (ss_addr_sk = ca_address_sk + and ca_country = 'United States' + and ca_state in ('WY', 'OH', 'ID') + and ss_net_profit between 150 and 300 + ) or + (ss_addr_sk = ca_address_sk + and ca_country = 'United States' + and ca_state in ('MS', 'SC', 'IA') + and ss_net_profit between 50 and 250 + )) + + ''' +} diff --git a/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query19.groovy b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query19.groovy new file mode 100644 index 00000000000..322c84b782b --- /dev/null +++ b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query19.groovy @@ -0,0 +1,82 @@ +/* + * 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("query19") { + 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' + sql 'set enable_bucket_shuffle_downgrade=true' + def ds = """select i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact, + sum(ss_ext_sales_price) ext_price + from date_dim, store_sales, item,customer,customer_address,store + where d_date_sk = ss_sold_date_sk + and ss_item_sk = i_item_sk + and i_manager_id=14 + and d_moy=11 + and d_year=2002 + and ss_customer_sk = c_customer_sk + and c_current_addr_sk = ca_address_sk + and substr(ca_zip,1,5) <> substr(s_zip,1,5) + and ss_store_sk = s_store_sk + group by i_brand + ,i_brand_id + ,i_manufact_id + ,i_manufact + order by ext_price desc + ,i_brand + ,i_brand_id + ,i_manufact_id + ,i_manufact +limit 100 """ + qt_ds_shape_19 ''' + explain shape plan + select i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact, + sum(ss_ext_sales_price) ext_price + from date_dim, store_sales, item,customer,customer_address,store + where d_date_sk = ss_sold_date_sk + and ss_item_sk = i_item_sk + and i_manager_id=14 + and d_moy=11 + and d_year=2002 + and ss_customer_sk = c_customer_sk + and c_current_addr_sk = ca_address_sk + and substr(ca_zip,1,5) <> substr(s_zip,1,5) + and ss_store_sk = s_store_sk + group by i_brand + ,i_brand_id + ,i_manufact_id + ,i_manufact + order by ext_price desc + ,i_brand + ,i_brand_id + ,i_manufact_id + ,i_manufact +limit 100 + ''' +} diff --git a/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query44.groovy b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query44.groovy new file mode 100644 index 00000000000..309c650df16 --- /dev/null +++ b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query44.groovy @@ -0,0 +1,102 @@ +/* + * 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("query44") { + 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' + sql 'set enable_bucket_shuffle_downgrade=true' + def ds = """select asceding.rnk, i1.i_product_name best_performing, i2.i_product_name worst_performing +from(select * + from (select item_sk,rank() over (order by rank_col asc) rnk + from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col + from store_sales ss1 + where ss_store_sk = 4 + group by ss_item_sk + having avg(ss_net_profit) > 0.9*(select avg(ss_net_profit) rank_col + from store_sales + where ss_store_sk = 4 + and ss_hdemo_sk is null + group by ss_store_sk))V1)V11 + where rnk < 11) asceding, + (select * + from (select item_sk,rank() over (order by rank_col desc) rnk + from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col + from store_sales ss1 + where ss_store_sk = 4 + group by ss_item_sk + having avg(ss_net_profit) > 0.9*(select avg(ss_net_profit) rank_col + from store_sales + where ss_store_sk = 4 + and ss_hdemo_sk is null + group by ss_store_sk))V2)V21 + where rnk < 11) descending, +item i1, +item i2 +where asceding.rnk = descending.rnk + and i1.i_item_sk=asceding.item_sk + and i2.i_item_sk=descending.item_sk +order by asceding.rnk +limit 100""" + qt_ds_shape_44 ''' + explain shape plan + select asceding.rnk, i1.i_product_name best_performing, i2.i_product_name worst_performing +from(select * + from (select item_sk,rank() over (order by rank_col asc) rnk + from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col + from store_sales ss1 + where ss_store_sk = 4 + group by ss_item_sk + having avg(ss_net_profit) > 0.9*(select avg(ss_net_profit) rank_col + from store_sales + where ss_store_sk = 4 + and ss_hdemo_sk is null + group by ss_store_sk))V1)V11 + where rnk < 11) asceding, + (select * + from (select item_sk,rank() over (order by rank_col desc) rnk + from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col + from store_sales ss1 + where ss_store_sk = 4 + group by ss_item_sk + having avg(ss_net_profit) > 0.9*(select avg(ss_net_profit) rank_col + from store_sales + where ss_store_sk = 4 + and ss_hdemo_sk is null + group by ss_store_sk))V2)V21 + where rnk < 11) descending, +item i1, +item i2 +where asceding.rnk = descending.rnk + and i1.i_item_sk=asceding.item_sk + and i2.i_item_sk=descending.item_sk +order by asceding.rnk +limit 100 + ''' +} diff --git a/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query45.groovy b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query45.groovy new file mode 100644 index 00000000000..90b08d167e9 --- /dev/null +++ b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query45.groovy @@ -0,0 +1,72 @@ +/* + * 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("query45") { + 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' + sql 'set enable_bucket_shuffle_downgrade=true' + def ds = """select ca_zip, ca_city, sum(ws_sales_price) + from web_sales, customer, customer_address, date_dim, item + where ws_bill_customer_sk = c_customer_sk + and c_current_addr_sk = ca_address_sk + and ws_item_sk = i_item_sk + and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792') + or + i_item_id in (select i_item_id + from item + where i_item_sk in (2, 3, 5, 7, 11, 13, 17, 19, 23, 29) + ) + ) + and ws_sold_date_sk = d_date_sk + and d_qoy = 1 and d_year = 2000 + group by ca_zip, ca_city + order by ca_zip, ca_city + limit 100""" + qt_ds_shape_45 ''' + explain shape plan + select ca_zip, ca_city, sum(ws_sales_price) + from web_sales, customer, customer_address, date_dim, item + where ws_bill_customer_sk = c_customer_sk + and c_current_addr_sk = ca_address_sk + and ws_item_sk = i_item_sk + and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792') + or + i_item_id in (select i_item_id + from item + where i_item_sk in (2, 3, 5, 7, 11, 13, 17, 19, 23, 29) + ) + ) + and ws_sold_date_sk = d_date_sk + and d_qoy = 1 and d_year = 2000 + group by ca_zip, ca_city + order by ca_zip, ca_city + limit 100 + ''' +} diff --git a/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query54.groovy b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query54.groovy new file mode 100644 index 00000000000..67cc77943d6 --- /dev/null +++ b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query54.groovy @@ -0,0 +1,144 @@ +/* + * 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("query54") { + 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' + sql 'set enable_bucket_shuffle_downgrade=true' + def ds = """with my_customers as ( + select distinct c_customer_sk + , c_current_addr_sk + from + ( select cs_sold_date_sk sold_date_sk, + cs_bill_customer_sk customer_sk, + cs_item_sk item_sk + from catalog_sales + union all + select ws_sold_date_sk sold_date_sk, + ws_bill_customer_sk customer_sk, + ws_item_sk item_sk + from web_sales + ) cs_or_ws_sales, + item, + date_dim, + customer + where sold_date_sk = d_date_sk + and item_sk = i_item_sk + and i_category = 'Music' + and i_class = 'country' + and c_customer_sk = cs_or_ws_sales.customer_sk + and d_moy = 1 + and d_year = 1999 + ) + , my_revenue as ( + select c_customer_sk, + sum(ss_ext_sales_price) as revenue + from my_customers, + store_sales, + customer_address, + store, + date_dim + where c_current_addr_sk = ca_address_sk + and ca_county = s_county + and ca_state = s_state + and ss_sold_date_sk = d_date_sk + and c_customer_sk = ss_customer_sk + and d_month_seq between (select distinct d_month_seq+1 + from date_dim where d_year = 1999 and d_moy = 1) + and (select distinct d_month_seq+3 + from date_dim where d_year = 1999 and d_moy = 1) + group by c_customer_sk + ) + , segments as + (select cast((revenue/50) as int) as segment + from my_revenue + ) + select segment, count(*) as num_customers, segment*50 as segment_base + from segments + group by segment + order by segment, num_customers + limit 100""" + qt_ds_shape_54 ''' + explain shape plan + with my_customers as ( + select distinct c_customer_sk + , c_current_addr_sk + from + ( select cs_sold_date_sk sold_date_sk, + cs_bill_customer_sk customer_sk, + cs_item_sk item_sk + from catalog_sales + union all + select ws_sold_date_sk sold_date_sk, + ws_bill_customer_sk customer_sk, + ws_item_sk item_sk + from web_sales + ) cs_or_ws_sales, + item, + date_dim, + customer + where sold_date_sk = d_date_sk + and item_sk = i_item_sk + and i_category = 'Music' + and i_class = 'country' + and c_customer_sk = cs_or_ws_sales.customer_sk + and d_moy = 1 + and d_year = 1999 + ) + , my_revenue as ( + select c_customer_sk, + sum(ss_ext_sales_price) as revenue + from my_customers, + store_sales, + customer_address, + store, + date_dim + where c_current_addr_sk = ca_address_sk + and ca_county = s_county + and ca_state = s_state + and ss_sold_date_sk = d_date_sk + and c_customer_sk = ss_customer_sk + and d_month_seq between (select distinct d_month_seq+1 + from date_dim where d_year = 1999 and d_moy = 1) + and (select distinct d_month_seq+3 + from date_dim where d_year = 1999 and d_moy = 1) + group by c_customer_sk + ) + , segments as + (select cast((revenue/50) as int) as segment + from my_revenue + ) + select segment, count(*) as num_customers, segment*50 as segment_base + from segments + group by segment + order by segment, num_customers + limit 100 + ''' +} diff --git a/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query56.groovy b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query56.groovy new file mode 100644 index 00000000000..8eb5ff81c8b --- /dev/null +++ b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query56.groovy @@ -0,0 +1,170 @@ +/* + * 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("query56") { + 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' + sql 'set enable_bucket_shuffle_downgrade=true' + def ds = """with ss as ( + select i_item_id,sum(ss_ext_sales_price) total_sales + from + store_sales, + date_dim, + customer_address, + item + where i_item_id in (select + i_item_id +from item +where i_color in ('powder','orchid','pink')) + and ss_item_sk = i_item_sk + and ss_sold_date_sk = d_date_sk + and d_year = 2000 + and d_moy = 3 + and ss_addr_sk = ca_address_sk + and ca_gmt_offset = -6 + group by i_item_id), + cs as ( + select i_item_id,sum(cs_ext_sales_price) total_sales + from + catalog_sales, + date_dim, + customer_address, + item + where + i_item_id in (select + i_item_id +from item +where i_color in ('powder','orchid','pink')) + and cs_item_sk = i_item_sk + and cs_sold_date_sk = d_date_sk + and d_year = 2000 + and d_moy = 3 + and cs_bill_addr_sk = ca_address_sk + and ca_gmt_offset = -6 + group by i_item_id), + ws as ( + select i_item_id,sum(ws_ext_sales_price) total_sales + from + web_sales, + date_dim, + customer_address, + item + where + i_item_id in (select + i_item_id +from item +where i_color in ('powder','orchid','pink')) + and ws_item_sk = i_item_sk + and ws_sold_date_sk = d_date_sk + and d_year = 2000 + and d_moy = 3 + and ws_bill_addr_sk = ca_address_sk + and ca_gmt_offset = -6 + group by i_item_id) + select i_item_id ,sum(total_sales) total_sales + from (select * from ss + union all + select * from cs + union all + select * from ws) tmp1 + group by i_item_id + order by total_sales, + i_item_id + limit 100""" + qt_ds_shape_56 ''' + explain shape plan + with ss as ( + select i_item_id,sum(ss_ext_sales_price) total_sales + from + store_sales, + date_dim, + customer_address, + item + where i_item_id in (select + i_item_id +from item +where i_color in ('powder','orchid','pink')) + and ss_item_sk = i_item_sk + and ss_sold_date_sk = d_date_sk + and d_year = 2000 + and d_moy = 3 + and ss_addr_sk = ca_address_sk + and ca_gmt_offset = -6 + group by i_item_id), + cs as ( + select i_item_id,sum(cs_ext_sales_price) total_sales + from + catalog_sales, + date_dim, + customer_address, + item + where + i_item_id in (select + i_item_id +from item +where i_color in ('powder','orchid','pink')) + and cs_item_sk = i_item_sk + and cs_sold_date_sk = d_date_sk + and d_year = 2000 + and d_moy = 3 + and cs_bill_addr_sk = ca_address_sk + and ca_gmt_offset = -6 + group by i_item_id), + ws as ( + select i_item_id,sum(ws_ext_sales_price) total_sales + from + web_sales, + date_dim, + customer_address, + item + where + i_item_id in (select + i_item_id +from item +where i_color in ('powder','orchid','pink')) + and ws_item_sk = i_item_sk + and ws_sold_date_sk = d_date_sk + and d_year = 2000 + and d_moy = 3 + and ws_bill_addr_sk = ca_address_sk + and ca_gmt_offset = -6 + group by i_item_id) + select i_item_id ,sum(total_sales) total_sales + from (select * from ss + union all + select * from cs + union all + select * from ws) tmp1 + group by i_item_id + order by total_sales, + i_item_id + limit 100 + ''' +} diff --git a/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query6.groovy b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query6.groovy new file mode 100644 index 00000000000..8a4e9fc33be --- /dev/null +++ b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query6.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("query6") { + 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' + sql 'set enable_bucket_shuffle_downgrade=true' + def ds = """select a.ca_state state, count(*) cnt + from customer_address a + ,customer c + ,store_sales s + ,date_dim d + ,item i + where a.ca_address_sk = c.c_current_addr_sk + and c.c_customer_sk = s.ss_customer_sk + and s.ss_sold_date_sk = d.d_date_sk + and s.ss_item_sk = i.i_item_sk + and d.d_month_seq = + (select distinct (d_month_seq) + from date_dim + where d_year = 2002 + and d_moy = 3 ) + and i.i_current_price > 1.2 * + (select avg(j.i_current_price) + from item j + where j.i_category = i.i_category) + group by a.ca_state + having count(*) >= 10 + order by cnt, a.ca_state + limit 100""" + qt_ds_shape_6 ''' + explain shape plan + select a.ca_state state, count(*) cnt + from customer_address a + ,customer c + ,store_sales s + ,date_dim d + ,item i + where a.ca_address_sk = c.c_current_addr_sk + and c.c_customer_sk = s.ss_customer_sk + and s.ss_sold_date_sk = d.d_date_sk + and s.ss_item_sk = i.i_item_sk + and d.d_month_seq = + (select distinct (d_month_seq) + from date_dim + where d_year = 2002 + and d_moy = 3 ) + and i.i_current_price > 1.2 * + (select avg(j.i_current_price) + from item j + where j.i_category = i.i_category) + group by a.ca_state + having count(*) >= 10 + order by cnt, a.ca_state + limit 100 + ''' +} diff --git a/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query61.groovy b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query61.groovy new file mode 100644 index 00000000000..afc92b3a6d6 --- /dev/null +++ b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query61.groovy @@ -0,0 +1,120 @@ +/* + * 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("query61") { + 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' + sql 'set enable_bucket_shuffle_downgrade=true' + def ds = """select promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100 +from + (select sum(ss_ext_sales_price) promotions + from store_sales + ,store + ,promotion + ,date_dim + ,customer + ,customer_address + ,item + where ss_sold_date_sk = d_date_sk + and ss_store_sk = s_store_sk + and ss_promo_sk = p_promo_sk + and ss_customer_sk= c_customer_sk + and ca_address_sk = c_current_addr_sk + and ss_item_sk = i_item_sk + and ca_gmt_offset = -7 + and i_category = 'Home' + and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y') + and s_gmt_offset = -7 + and d_year = 2000 + and d_moy = 12) promotional_sales, + (select sum(ss_ext_sales_price) total + from store_sales + ,store + ,date_dim + ,customer + ,customer_address + ,item + where ss_sold_date_sk = d_date_sk + and ss_store_sk = s_store_sk + and ss_customer_sk= c_customer_sk + and ca_address_sk = c_current_addr_sk + and ss_item_sk = i_item_sk + and ca_gmt_offset = -7 + and i_category = 'Home' + and s_gmt_offset = -7 + and d_year = 2000 + and d_moy = 12) all_sales +order by promotions, total +limit 100""" + qt_ds_shape_61 ''' + explain shape plan + select promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100 +from + (select sum(ss_ext_sales_price) promotions + from store_sales + ,store + ,promotion + ,date_dim + ,customer + ,customer_address + ,item + where ss_sold_date_sk = d_date_sk + and ss_store_sk = s_store_sk + and ss_promo_sk = p_promo_sk + and ss_customer_sk= c_customer_sk + and ca_address_sk = c_current_addr_sk + and ss_item_sk = i_item_sk + and ca_gmt_offset = -7 + and i_category = 'Home' + and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y') + and s_gmt_offset = -7 + and d_year = 2000 + and d_moy = 12) promotional_sales, + (select sum(ss_ext_sales_price) total + from store_sales + ,store + ,date_dim + ,customer + ,customer_address + ,item + where ss_sold_date_sk = d_date_sk + and ss_store_sk = s_store_sk + and ss_customer_sk= c_customer_sk + and ca_address_sk = c_current_addr_sk + and ss_item_sk = i_item_sk + and ca_gmt_offset = -7 + and i_category = 'Home' + and s_gmt_offset = -7 + and d_year = 2000 + and d_moy = 12) all_sales +order by promotions, total +limit 100 + ''' +} diff --git a/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query68.groovy b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query68.groovy new file mode 100644 index 00000000000..a9d1967d203 --- /dev/null +++ b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query68.groovy @@ -0,0 +1,116 @@ +/* + * 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("query68") { + 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' + sql 'set enable_bucket_shuffle_downgrade=true' + def ds = """select c_last_name + ,c_first_name + ,ca_city + ,bought_city + ,ss_ticket_number + ,extended_price + ,extended_tax + ,list_price + from (select ss_ticket_number + ,ss_customer_sk + ,ca_city bought_city + ,sum(ss_ext_sales_price) extended_price + ,sum(ss_ext_list_price) list_price + ,sum(ss_ext_tax) extended_tax + from store_sales + ,date_dim + ,store + ,household_demographics + ,customer_address + where store_sales.ss_sold_date_sk = date_dim.d_date_sk + and store_sales.ss_store_sk = store.s_store_sk + and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk + and store_sales.ss_addr_sk = customer_address.ca_address_sk + and date_dim.d_dom between 1 and 2 + and (household_demographics.hd_dep_count = 3 or + household_demographics.hd_vehicle_count= 4) + and date_dim.d_year in (1998,1998+1,1998+2) + and store.s_city in ('Fairview','Midway') + group by ss_ticket_number + ,ss_customer_sk + ,ss_addr_sk,ca_city) dn + ,customer + ,customer_address current_addr + where ss_customer_sk = c_customer_sk + and customer.c_current_addr_sk = current_addr.ca_address_sk + and current_addr.ca_city <> bought_city + order by c_last_name + ,ss_ticket_number + limit 100""" + qt_ds_shape_68 ''' + explain shape plan + select c_last_name + ,c_first_name + ,ca_city + ,bought_city + ,ss_ticket_number + ,extended_price + ,extended_tax + ,list_price + from (select ss_ticket_number + ,ss_customer_sk + ,ca_city bought_city + ,sum(ss_ext_sales_price) extended_price + ,sum(ss_ext_list_price) list_price + ,sum(ss_ext_tax) extended_tax + from store_sales + ,date_dim + ,store + ,household_demographics + ,customer_address + where store_sales.ss_sold_date_sk = date_dim.d_date_sk + and store_sales.ss_store_sk = store.s_store_sk + and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk + and store_sales.ss_addr_sk = customer_address.ca_address_sk + and date_dim.d_dom between 1 and 2 + and (household_demographics.hd_dep_count = 3 or + household_demographics.hd_vehicle_count= 4) + and date_dim.d_year in (1998,1998+1,1998+2) + and store.s_city in ('Fairview','Midway') + group by ss_ticket_number + ,ss_customer_sk + ,ss_addr_sk,ca_city) dn + ,customer + ,customer_address current_addr + where ss_customer_sk = c_customer_sk + and customer.c_current_addr_sk = current_addr.ca_address_sk + and current_addr.ca_city <> bought_city + order by c_last_name + ,ss_ticket_number + limit 100 + ''' +} diff --git a/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query8.groovy b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query8.groovy new file mode 100644 index 00000000000..98a6e694c19 --- /dev/null +++ b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query8.groovy @@ -0,0 +1,248 @@ +/* + * 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("query8") { + 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' + sql 'set enable_bucket_shuffle_downgrade=true' + def ds = """select s_store_name + ,sum(ss_net_profit) + from store_sales + ,date_dim + ,store, + (select ca_zip + from ( + SELECT substr(ca_zip,1,5) ca_zip + FROM customer_address + WHERE substr(ca_zip,1,5) IN ( + '47602','16704','35863','28577','83910','36201', + '58412','48162','28055','41419','80332', + '38607','77817','24891','16226','18410', + '21231','59345','13918','51089','20317', + '17167','54585','67881','78366','47770', + '18360','51717','73108','14440','21800', + '89338','45859','65501','34948','25973', + '73219','25333','17291','10374','18829', + '60736','82620','41351','52094','19326', + '25214','54207','40936','21814','79077', + '25178','75742','77454','30621','89193', + '27369','41232','48567','83041','71948', + '37119','68341','14073','16891','62878', + '49130','19833','24286','27700','40979', + '50412','81504','94835','84844','71954', + '39503','57649','18434','24987','12350', + '86379','27413','44529','98569','16515', + '27287','24255','21094','16005','56436', + '91110','68293','56455','54558','10298', + '83647','32754','27052','51766','19444', + '13869','45645','94791','57631','20712', + '37788','41807','46507','21727','71836', + '81070','50632','88086','63991','20244', + '31655','51782','29818','63792','68605', + '94898','36430','57025','20601','82080', + '33869','22728','35834','29086','92645', + '98584','98072','11652','78093','57553', + '43830','71144','53565','18700','90209', + '71256','38353','54364','28571','96560', + '57839','56355','50679','45266','84680', + '34306','34972','48530','30106','15371', + '92380','84247','92292','68852','13338', + '34594','82602','70073','98069','85066', + '47289','11686','98862','26217','47529', + '63294','51793','35926','24227','14196', + '24594','32489','99060','49472','43432', + '49211','14312','88137','47369','56877', + '20534','81755','15794','12318','21060', + '73134','41255','63073','81003','73873', + '66057','51184','51195','45676','92696', + '70450','90669','98338','25264','38919', + '59226','58581','60298','17895','19489', + '52301','80846','95464','68770','51634', + '19988','18367','18421','11618','67975', + '25494','41352','95430','15734','62585', + '97173','33773','10425','75675','53535', + '17879','41967','12197','67998','79658', + '59130','72592','14851','43933','68101', + '50636','25717','71286','24660','58058', + '72991','95042','15543','33122','69280', + '11912','59386','27642','65177','17672', + '33467','64592','36335','54010','18767', + '63193','42361','49254','33113','33159', + '36479','59080','11855','81963','31016', + '49140','29392','41836','32958','53163', + '13844','73146','23952','65148','93498', + '14530','46131','58454','13376','13378', + '83986','12320','17193','59852','46081', + '98533','52389','13086','68843','31013', + '13261','60560','13443','45533','83583', + '11489','58218','19753','22911','25115', + '86709','27156','32669','13123','51933', + '39214','41331','66943','14155','69998', + '49101','70070','35076','14242','73021', + '59494','15782','29752','37914','74686', + '83086','34473','15751','81084','49230', + '91894','60624','17819','28810','63180', + '56224','39459','55233','75752','43639', + '55349','86057','62361','50788','31830', + '58062','18218','85761','60083','45484', + '21204','90229','70041','41162','35390', + '16364','39500','68908','26689','52868', + '81335','40146','11340','61527','61794', + '71997','30415','59004','29450','58117', + '69952','33562','83833','27385','61860', + '96435','48333','23065','32961','84919', + '61997','99132','22815','56600','68730', + '48017','95694','32919','88217','27116', + '28239','58032','18884','16791','21343', + '97462','18569','75660','15475') + intersect + select ca_zip + from (SELECT substr(ca_zip,1,5) ca_zip,count(*) cnt + FROM customer_address, customer + WHERE ca_address_sk = c_current_addr_sk and + c_preferred_cust_flag='Y' + group by ca_zip + having count(*) > 10)A1)A2) V1 + where ss_store_sk = s_store_sk + and ss_sold_date_sk = d_date_sk + and d_qoy = 2 and d_year = 1998 + and (substr(s_zip,1,2) = substr(V1.ca_zip,1,2)) + group by s_store_name + order by s_store_name + limit 100""" + qt_ds_shape_8 ''' + explain shape plan + select s_store_name + ,sum(ss_net_profit) + from store_sales + ,date_dim + ,store, + (select ca_zip + from ( + SELECT substr(ca_zip,1,5) ca_zip + FROM customer_address + WHERE substr(ca_zip,1,5) IN ( + '47602','16704','35863','28577','83910','36201', + '58412','48162','28055','41419','80332', + '38607','77817','24891','16226','18410', + '21231','59345','13918','51089','20317', + '17167','54585','67881','78366','47770', + '18360','51717','73108','14440','21800', + '89338','45859','65501','34948','25973', + '73219','25333','17291','10374','18829', + '60736','82620','41351','52094','19326', + '25214','54207','40936','21814','79077', + '25178','75742','77454','30621','89193', + '27369','41232','48567','83041','71948', + '37119','68341','14073','16891','62878', + '49130','19833','24286','27700','40979', + '50412','81504','94835','84844','71954', + '39503','57649','18434','24987','12350', + '86379','27413','44529','98569','16515', + '27287','24255','21094','16005','56436', + '91110','68293','56455','54558','10298', + '83647','32754','27052','51766','19444', + '13869','45645','94791','57631','20712', + '37788','41807','46507','21727','71836', + '81070','50632','88086','63991','20244', + '31655','51782','29818','63792','68605', + '94898','36430','57025','20601','82080', + '33869','22728','35834','29086','92645', + '98584','98072','11652','78093','57553', + '43830','71144','53565','18700','90209', + '71256','38353','54364','28571','96560', + '57839','56355','50679','45266','84680', + '34306','34972','48530','30106','15371', + '92380','84247','92292','68852','13338', + '34594','82602','70073','98069','85066', + '47289','11686','98862','26217','47529', + '63294','51793','35926','24227','14196', + '24594','32489','99060','49472','43432', + '49211','14312','88137','47369','56877', + '20534','81755','15794','12318','21060', + '73134','41255','63073','81003','73873', + '66057','51184','51195','45676','92696', + '70450','90669','98338','25264','38919', + '59226','58581','60298','17895','19489', + '52301','80846','95464','68770','51634', + '19988','18367','18421','11618','67975', + '25494','41352','95430','15734','62585', + '97173','33773','10425','75675','53535', + '17879','41967','12197','67998','79658', + '59130','72592','14851','43933','68101', + '50636','25717','71286','24660','58058', + '72991','95042','15543','33122','69280', + '11912','59386','27642','65177','17672', + '33467','64592','36335','54010','18767', + '63193','42361','49254','33113','33159', + '36479','59080','11855','81963','31016', + '49140','29392','41836','32958','53163', + '13844','73146','23952','65148','93498', + '14530','46131','58454','13376','13378', + '83986','12320','17193','59852','46081', + '98533','52389','13086','68843','31013', + '13261','60560','13443','45533','83583', + '11489','58218','19753','22911','25115', + '86709','27156','32669','13123','51933', + '39214','41331','66943','14155','69998', + '49101','70070','35076','14242','73021', + '59494','15782','29752','37914','74686', + '83086','34473','15751','81084','49230', + '91894','60624','17819','28810','63180', + '56224','39459','55233','75752','43639', + '55349','86057','62361','50788','31830', + '58062','18218','85761','60083','45484', + '21204','90229','70041','41162','35390', + '16364','39500','68908','26689','52868', + '81335','40146','11340','61527','61794', + '71997','30415','59004','29450','58117', + '69952','33562','83833','27385','61860', + '96435','48333','23065','32961','84919', + '61997','99132','22815','56600','68730', + '48017','95694','32919','88217','27116', + '28239','58032','18884','16791','21343', + '97462','18569','75660','15475') + intersect + select ca_zip + from (SELECT substr(ca_zip,1,5) ca_zip,count(*) cnt + FROM customer_address, customer + WHERE ca_address_sk = c_current_addr_sk and + c_preferred_cust_flag='Y' + group by ca_zip + having count(*) > 10)A1)A2) V1 + where ss_store_sk = s_store_sk + and ss_sold_date_sk = d_date_sk + and d_qoy = 2 and d_year = 1998 + and (substr(s_zip,1,2) = substr(V1.ca_zip,1,2)) + group by s_store_name + order by s_store_name + limit 100 + ''' +} diff --git a/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query91.groovy b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query91.groovy new file mode 100644 index 00000000000..9fd084f69be --- /dev/null +++ b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query91.groovy @@ -0,0 +1,94 @@ +/* + * 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("query91") { + 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' + sql 'set enable_bucket_shuffle_downgrade=true' + def ds = """select + cc_call_center_id Call_Center, + cc_name Call_Center_Name, + cc_manager Manager, + sum(cr_net_loss) Returns_Loss +from + call_center, + catalog_returns, + date_dim, + customer, + customer_address, + customer_demographics, + household_demographics +where + cr_call_center_sk = cc_call_center_sk +and cr_returned_date_sk = d_date_sk +and cr_returning_customer_sk= c_customer_sk +and cd_demo_sk = c_current_cdemo_sk +and hd_demo_sk = c_current_hdemo_sk +and ca_address_sk = c_current_addr_sk +and d_year = 2000 +and d_moy = 12 +and ( (cd_marital_status = 'M' and cd_education_status = 'Unknown') + or(cd_marital_status = 'W' and cd_education_status = 'Advanced Degree')) +and hd_buy_potential like 'Unknown%' +and ca_gmt_offset = -7 +group by cc_call_center_id,cc_name,cc_manager,cd_marital_status,cd_education_status +order by sum(cr_net_loss) desc""" + qt_ds_shape_91 ''' + explain shape plan + select + cc_call_center_id Call_Center, + cc_name Call_Center_Name, + cc_manager Manager, + sum(cr_net_loss) Returns_Loss +from + call_center, + catalog_returns, + date_dim, + customer, + customer_address, + customer_demographics, + household_demographics +where + cr_call_center_sk = cc_call_center_sk +and cr_returned_date_sk = d_date_sk +and cr_returning_customer_sk= c_customer_sk +and cd_demo_sk = c_current_cdemo_sk +and hd_demo_sk = c_current_hdemo_sk +and ca_address_sk = c_current_addr_sk +and d_year = 2000 +and d_moy = 12 +and ( (cd_marital_status = 'M' and cd_education_status = 'Unknown') + or(cd_marital_status = 'W' and cd_education_status = 'Advanced Degree')) +and hd_buy_potential like 'Unknown%' +and ca_gmt_offset = -7 +group by cc_call_center_id,cc_name,cc_manager,cd_marital_status,cd_education_status +order by sum(cr_net_loss) desc + ''' +} diff --git a/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query95.groovy b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query95.groovy new file mode 100644 index 00000000000..2837fea540f --- /dev/null +++ b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/bs_downgrade_shape/query95.groovy @@ -0,0 +1,96 @@ +/* + * 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("query95") { + 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' + sql 'set enable_bucket_shuffle_downgrade=true' + def ds = """with ws_wh as +(select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 + from web_sales ws1,web_sales ws2 + where ws1.ws_order_number = ws2.ws_order_number + and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) + select + count(distinct ws_order_number) as "order count" + ,sum(ws_ext_ship_cost) as "total shipping cost" + ,sum(ws_net_profit) as "total net profit" +from + web_sales ws1 + ,date_dim + ,customer_address + ,web_site +where + d_date between '2001-4-01' and + (cast('2001-4-01' as date) + interval 60 day) +and ws1.ws_ship_date_sk = d_date_sk +and ws1.ws_ship_addr_sk = ca_address_sk +and ca_state = 'VA' +and ws1.ws_web_site_sk = web_site_sk +and web_company_name = 'pri' +and ws1.ws_order_number in (select ws_order_number + from ws_wh) +and ws1.ws_order_number in (select wr_order_number + from web_returns,ws_wh + where wr_order_number = ws_wh.ws_order_number) +order by count(distinct ws_order_number) +limit 100""" + qt_ds_shape_95 ''' + explain shape plan + with ws_wh as +(select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 + from web_sales ws1,web_sales ws2 + where ws1.ws_order_number = ws2.ws_order_number + and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) + select + count(distinct ws_order_number) as "order count" + ,sum(ws_ext_ship_cost) as "total shipping cost" + ,sum(ws_net_profit) as "total net profit" +from + web_sales ws1 + ,date_dim + ,customer_address + ,web_site +where + d_date between '2001-4-01' and + (cast('2001-4-01' as date) + interval 60 day) +and ws1.ws_ship_date_sk = d_date_sk +and ws1.ws_ship_addr_sk = ca_address_sk +and ca_state = 'VA' +and ws1.ws_web_site_sk = web_site_sk +and web_company_name = 'pri' +and ws1.ws_order_number in (select ws_order_number + from ws_wh) +and ws1.ws_order_number in (select wr_order_number + from web_returns,ws_wh + where wr_order_number = ws_wh.ws_order_number) +order by count(distinct ws_order_number) +limit 100 + ''' +} --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
