This is an automated email from the ASF dual-hosted git repository.
jakevin pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new f97c40d0e12 [fix](Nereids) fix plan shape of query64 unstable (#26012)
f97c40d0e12 is described below
commit f97c40d0e12bdf83e0bd110ad5245a915852da37
Author: 谢健 <[email protected]>
AuthorDate: Mon Oct 30 19:49:28 2023 +0800
[fix](Nereids) fix plan shape of query64 unstable (#26012)
don't remove the physical plan after optimizing the plan in dphyper.
---
.../org/apache/doris/nereids/cost/CostModelV1.java | 2 +-
.../doris/nereids/jobs/executor/Optimizer.java | 2 -
.../java/org/apache/doris/nereids/memo/Memo.java | 26 ---
.../shape/query64.out | 154 ++++++-------
.../shape/query64.groovy | 245 ++++++++++-----------
5 files changed, 201 insertions(+), 228 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
index 8c2b467920e..c6bb6e6fdff 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
@@ -67,7 +67,7 @@ class CostModelV1 extends PlanVisitor<Cost, PlanContext> {
// the penalty factor is no more than BROADCAST_JOIN_SKEW_PENALTY_LIMIT
static final double BROADCAST_JOIN_SKEW_RATIO = 30.0;
static final double BROADCAST_JOIN_SKEW_PENALTY_LIMIT = 2.0;
- private int beNumber = 1;
+ private final int beNumber;
public CostModelV1() {
if (ConnectContext.get().getSessionVariable().isPlayNereidsDump()) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Optimizer.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Optimizer.java
index 432d2dcd224..19dd3b00bba 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Optimizer.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Optimizer.java
@@ -73,8 +73,6 @@ public class Optimizer {
// Due to EnsureProjectOnTopJoin, root group can't be Join Group, so
DPHyp doesn't change the root group
cascadesContext.pushJob(new JoinOrderJob(root,
cascadesContext.getCurrentJobContext()));
cascadesContext.getJobScheduler().executeJobPool(cascadesContext);
- // after DPHyp just keep logical expression
- cascadesContext.getMemo().removePhysicalExpression();
cascadesContext.getStatementContext().setOtherJoinReorder(true);
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/Memo.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/Memo.java
index 41f4ae7ceae..ea35558c42e 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/Memo.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/Memo.java
@@ -50,7 +50,6 @@ import org.apache.logging.log4j.Logger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
-import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Optional;
@@ -116,31 +115,6 @@ public class Memo {
return groupExpressions.size();
}
- /** just keep LogicalExpression in Memo. */
- public void removePhysicalExpression() {
- groupExpressions.entrySet().removeIf(entry ->
entry.getValue().getPlan() instanceof PhysicalPlan);
-
- Iterator<Map.Entry<GroupId, Group>> iterator =
groups.entrySet().iterator();
- while (iterator.hasNext()) {
- Map.Entry<GroupId, Group> entry = iterator.next();
- Group group = entry.getValue();
-
- group.clearPhysicalExpressions();
- group.clearLowestCostPlans();
- group.removeParentPhysicalExpressions();
- group.setExplored(false);
-
- if (group.getLogicalExpressions().isEmpty() &&
group.getPhysicalExpressions().isEmpty()) {
- iterator.remove();
- }
- }
-
- // logical groupExpression reset ruleMask
- groupExpressions.values().stream()
- .filter(groupExpression -> groupExpression.getPlan()
instanceof LogicalPlan)
- .forEach(GroupExpression::clearApplied);
- }
-
private Plan skipProject(Plan plan, Group targetGroup) {
// Some top project can't be eliminated
if (plan instanceof LogicalProject) {
diff --git
a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query64.out
b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query64.out
index 599cc0b52f2..fc18aba3fda 100644
--- a/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query64.out
+++ b/regression-test/data/nereids_tpcds_shape_sf1000_p0/shape/query64.out
@@ -9,103 +9,105 @@ PhysicalCteAnchor ( cteId=CTEId#1 )
------------PhysicalProject
--------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_store_sk =
store.s_store_sk))otherCondition=()
----------------PhysicalProject
-------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_item_sk
= cs_ui.cs_item_sk))otherCondition=()
---------------------PhysicalDistribute
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_shipto_date_sk =
d3.d_date_sk))otherCondition=()
+------------------hashJoin[INNER_JOIN] hashCondition=((store_sales.ss_promo_sk
= promotion.p_promo_sk))otherCondition=()
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk))otherCondition=((
not (cd_marital_status = cd_marital_status)))
+------------------------PhysicalDistribute
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))otherCondition=()
+----------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_shipto_date_sk =
d3.d_date_sk))otherCondition=()
------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN]
hashCondition=((hd2.hd_income_band_sk = ib2.ib_income_band_sk))otherCondition=()
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk))otherCondition=()
---------------------------------------PhysicalDistribute
+--------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_customer_sk =
customer.c_customer_sk))otherCondition=()
+----------------------------------PhysicalDistribute
+------------------------------------PhysicalProject
+--------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_addr_sk =
ad2.ca_address_sk))otherCondition=()
+----------------------------------------PhysicalDistribute
+------------------------------------------PhysicalProject
+--------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))otherCondition=()
+----------------------------------------------PhysicalDistribute
+------------------------------------------------PhysicalProject
+--------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_hdemo_sk = hd2.hd_demo_sk))otherCondition=()
+----------------------------------------------------PhysicalProject
+------------------------------------------------------PhysicalOlapScan[customer]
+----------------------------------------------------PhysicalDistribute
+------------------------------------------------------PhysicalProject
+--------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((hd2.hd_income_band_sk = ib2.ib_income_band_sk))otherCondition=()
+----------------------------------------------------------PhysicalDistribute
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[household_demographics]
+----------------------------------------------------------PhysicalDistribute
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[income_band]
+----------------------------------------------PhysicalDistribute
+------------------------------------------------PhysicalProject
+--------------------------------------------------PhysicalOlapScan[date_dim]
+----------------------------------------PhysicalDistribute
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[customer_address]
+----------------------------------PhysicalDistribute
+------------------------------------PhysicalProject
+--------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = store_returns.sr_item_sk) and
(store_sales.ss_ticket_number =
store_returns.sr_ticket_number))otherCondition=()
----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk))otherCondition=()
---------------------------------------------PhysicalProject
-----------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_promo_sk =
promotion.p_promo_sk))otherCondition=()
+------------------------------------------PhysicalOlapScan[store_returns]
+----------------------------------------PhysicalDistribute
+------------------------------------------PhysicalProject
+--------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = cs_ui.cs_item_sk))otherCondition=()
+----------------------------------------------PhysicalDistribute
------------------------------------------------PhysicalProject
---------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk))otherCondition=((
not (cd_marital_status = cd_marital_status)))
+--------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk))otherCondition=()
----------------------------------------------------PhysicalDistribute
------------------------------------------------------PhysicalProject
---------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_addr_sk =
ad2.ca_address_sk))otherCondition=()
+--------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk))otherCondition=()
----------------------------------------------------------PhysicalDistribute
------------------------------------------------------------PhysicalProject
---------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_current_hdemo_sk = hd2.hd_demo_sk))otherCondition=()
+--------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk))otherCondition=()
----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = store_returns.sr_item_sk) and
(store_sales.ss_ticket_number =
store_returns.sr_ticket_number))otherCondition=()
---------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------PhysicalOlapScan[store_returns]
+------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk))otherCondition=()
+--------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk))otherCondition=()
+----------------------------------------------------------------------PhysicalProject
+------------------------------------------------------------------------PhysicalOlapScan[store_sales]
+----------------------------------------------------------------------PhysicalDistribute
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------filter((item.i_current_price
<= 58.00) and (item.i_current_price >= 49.00) and i_color IN ('blush', 'lace',
'lawn', 'misty', 'orange', 'pink'))
+----------------------------------------------------------------------------PhysicalOlapScan[item]
--------------------------------------------------------------------PhysicalDistribute
----------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_customer_sk =
customer.c_customer_sk))otherCondition=()
---------------------------------------------------------------------------PhysicalDistribute
-----------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------PhysicalOlapScan[customer]
---------------------------------------------------------------------------PhysicalDistribute
-----------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk))otherCondition=()
---------------------------------------------------------------------------------PhysicalDistribute
-----------------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------------PhysicalOlapScan[customer_demographics]
---------------------------------------------------------------------------------PhysicalDistribute
-----------------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk))otherCondition=()
---------------------------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk))otherCondition=()
-----------------------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------------------PhysicalOlapScan[store_sales]
-----------------------------------------------------------------------------------------PhysicalDistribute
-------------------------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------------------------filter((item.i_current_price
<= 58.00) and (item.i_current_price >= 49.00) and i_color IN ('blush', 'lace',
'lawn', 'misty', 'orange', 'pink'))
-----------------------------------------------------------------------------------------------PhysicalOlapScan[item]
---------------------------------------------------------------------------------------PhysicalDistribute
-----------------------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------------------filter(d_year
IN (1999, 2000))
---------------------------------------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------------------------------filter(d_year
IN (1999, 2000))
+--------------------------------------------------------------------------PhysicalOlapScan[date_dim]
----------------------------------------------------------------PhysicalDistribute
------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------PhysicalOlapScan[household_demographics]
+--------------------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((hd1.hd_income_band_sk =
ib1.ib_income_band_sk))otherCondition=()
+----------------------------------------------------------------------PhysicalDistribute
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------PhysicalOlapScan[household_demographics]
+----------------------------------------------------------------------PhysicalDistribute
+------------------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------------------PhysicalOlapScan[income_band]
----------------------------------------------------------PhysicalDistribute
------------------------------------------------------------PhysicalProject
---------------------------------------------------------------PhysicalOlapScan[customer_address]
+--------------------------------------------------------------PhysicalOlapScan[customer_demographics]
----------------------------------------------------PhysicalDistribute
------------------------------------------------------PhysicalProject
---------------------------------------------------------PhysicalOlapScan[customer_demographics]
-------------------------------------------------PhysicalDistribute
---------------------------------------------------PhysicalProject
-----------------------------------------------------PhysicalOlapScan[promotion]
---------------------------------------------PhysicalDistribute
+--------------------------------------------------------PhysicalOlapScan[customer_address]
----------------------------------------------PhysicalProject
-------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((hd1.hd_income_band_sk = ib1.ib_income_band_sk))otherCondition=()
---------------------------------------------------PhysicalDistribute
-----------------------------------------------------PhysicalProject
-------------------------------------------------------PhysicalOlapScan[household_demographics]
---------------------------------------------------PhysicalDistribute
-----------------------------------------------------PhysicalProject
-------------------------------------------------------PhysicalOlapScan[income_band]
---------------------------------------PhysicalDistribute
-----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[customer_address]
-----------------------------------PhysicalDistribute
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[income_band]
+------------------------------------------------filter((sale > (2 * refund)))
+--------------------------------------------------hashAgg[GLOBAL]
+----------------------------------------------------PhysicalDistribute
+------------------------------------------------------hashAgg[LOCAL]
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((catalog_sales.cs_item_sk = catalog_returns.cr_item_sk) and
(catalog_sales.cs_order_number =
catalog_returns.cr_order_number))otherCondition=()
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[catalog_sales]
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[catalog_returns]
------------------------------PhysicalDistribute
--------------------------------PhysicalProject
----------------------------------PhysicalOlapScan[date_dim]
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[date_dim]
---------------------PhysicalProject
-----------------------filter((sale > (2 * refund)))
-------------------------hashAgg[GLOBAL]
---------------------------PhysicalDistribute
-----------------------------hashAgg[LOCAL]
-------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN]
hashCondition=((catalog_sales.cs_item_sk = catalog_returns.cr_item_sk) and
(catalog_sales.cs_order_number =
catalog_returns.cr_order_number))otherCondition=()
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[catalog_sales]
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[catalog_returns]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[customer_demographics]
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[promotion]
----------------PhysicalDistribute
------------------PhysicalProject
--------------------PhysicalOlapScan[store]
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf1000_p0/shape/query64.groovy
b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/shape/query64.groovy
index 3f84a7ad885..140ad0c63fe 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf1000_p0/shape/query64.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf1000_p0/shape/query64.groovy
@@ -24,134 +24,133 @@ suite("query64") {
sql 'set enable_fallback_to_original_planner=false'
sql 'set exec_mem_limit=21G'
sql 'set be_number_for_test=3'
+ sql 'set enable_pipeline_engine = true'
sql 'set parallel_fragment_exec_instance_num=8; '
sql 'set parallel_pipeline_task_num=8; '
sql 'set forbid_unknown_col_stats=true'
sql 'set broadcast_row_count_limit = 30000000'
sql 'set enable_nereids_timeout = false'
- sql 'set dump_nereids_memo=true'
-
-// qt_ds_shape_64 '''
-// explain shape plan
-// with cs_ui as
-// (select cs_item_sk
-// ,sum(cs_ext_list_price) as
sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
-// from catalog_sales
-// ,catalog_returns
-// where cs_item_sk = cr_item_sk
-// and cs_order_number = cr_order_number
-// group by cs_item_sk
-// having
sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
-// cross_sales as
-// (select i_product_name product_name
-// ,i_item_sk item_sk
-// ,s_store_name store_name
-// ,s_zip store_zip
-// ,ad1.ca_street_number b_street_number
-// ,ad1.ca_street_name b_street_name
-// ,ad1.ca_city b_city
-// ,ad1.ca_zip b_zip
-// ,ad2.ca_street_number c_street_number
-// ,ad2.ca_street_name c_street_name
-// ,ad2.ca_city c_city
-// ,ad2.ca_zip c_zip
-// ,d1.d_year as syear
-// ,d2.d_year as fsyear
-// ,d3.d_year s2year
-// ,count(*) cnt
-// ,sum(ss_wholesale_cost) s1
-// ,sum(ss_list_price) s2
-// ,sum(ss_coupon_amt) s3
-// FROM store_sales
-// ,store_returns
-// ,cs_ui
-// ,date_dim d1
-// ,date_dim d2
-// ,date_dim d3
-// ,store
-// ,customer
-// ,customer_demographics cd1
-// ,customer_demographics cd2
-// ,promotion
-// ,household_demographics hd1
-// ,household_demographics hd2
-// ,customer_address ad1
-// ,customer_address ad2
-// ,income_band ib1
-// ,income_band ib2
-// ,item
-// WHERE ss_store_sk = s_store_sk AND
-// ss_sold_date_sk = d1.d_date_sk AND
-// ss_customer_sk = c_customer_sk AND
-// ss_cdemo_sk= cd1.cd_demo_sk AND
-// ss_hdemo_sk = hd1.hd_demo_sk AND
-// ss_addr_sk = ad1.ca_address_sk and
-// ss_item_sk = i_item_sk and
-// ss_item_sk = sr_item_sk and
-// ss_ticket_number = sr_ticket_number and
-// ss_item_sk = cs_ui.cs_item_sk and
-// c_current_cdemo_sk = cd2.cd_demo_sk AND
-// c_current_hdemo_sk = hd2.hd_demo_sk AND
-// c_current_addr_sk = ad2.ca_address_sk and
-// c_first_sales_date_sk = d2.d_date_sk and
-// c_first_shipto_date_sk = d3.d_date_sk and
-// ss_promo_sk = p_promo_sk and
-// hd1.hd_income_band_sk = ib1.ib_income_band_sk and
-// hd2.hd_income_band_sk = ib2.ib_income_band_sk and
-// cd1.cd_marital_status <> cd2.cd_marital_status and
-// i_color in ('orange','lace','lawn','misty','blush','pink') and
-// i_current_price between 48 and 48 + 10 and
-// i_current_price between 48 + 1 and 48 + 15
-// group by i_product_name
-// ,i_item_sk
-// ,s_store_name
-// ,s_zip
-// ,ad1.ca_street_number
-// ,ad1.ca_street_name
-// ,ad1.ca_city
-// ,ad1.ca_zip
-// ,ad2.ca_street_number
-// ,ad2.ca_street_name
-// ,ad2.ca_city
-// ,ad2.ca_zip
-// ,d1.d_year
-// ,d2.d_year
-// ,d3.d_year
-// )
-// select cs1.product_name
-// ,cs1.store_name
-// ,cs1.store_zip
-// ,cs1.b_street_number
-// ,cs1.b_street_name
-// ,cs1.b_city
-// ,cs1.b_zip
-// ,cs1.c_street_number
-// ,cs1.c_street_name
-// ,cs1.c_city
-// ,cs1.c_zip
-// ,cs1.syear
-// ,cs1.cnt
-// ,cs1.s1 as s11
-// ,cs1.s2 as s21
-// ,cs1.s3 as s31
-// ,cs2.s1 as s12
-// ,cs2.s2 as s22
-// ,cs2.s3 as s32
-// ,cs2.syear
-// ,cs2.cnt
-// from cross_sales cs1,cross_sales cs2
-// where cs1.item_sk=cs2.item_sk and
-// cs1.syear = 1999 and
-// cs2.syear = 1999 + 1 and
-// cs2.cnt <= cs1.cnt and
-// cs1.store_name = cs2.store_name and
-// cs1.store_zip = cs2.store_zip
-// order by cs1.product_name
-// ,cs1.store_name
-// ,cs2.cnt
-// ,cs1.s1
-// ,cs2.s1;
+ qt_ds_shape_64 '''
+ explain shape plan
+ with cs_ui as
+ (select cs_item_sk
+ ,sum(cs_ext_list_price) as
sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
+ from catalog_sales
+ ,catalog_returns
+ where cs_item_sk = cr_item_sk
+ and cs_order_number = cr_order_number
+ group by cs_item_sk
+ having
sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
+cross_sales as
+ (select i_product_name product_name
+ ,i_item_sk item_sk
+ ,s_store_name store_name
+ ,s_zip store_zip
+ ,ad1.ca_street_number b_street_number
+ ,ad1.ca_street_name b_street_name
+ ,ad1.ca_city b_city
+ ,ad1.ca_zip b_zip
+ ,ad2.ca_street_number c_street_number
+ ,ad2.ca_street_name c_street_name
+ ,ad2.ca_city c_city
+ ,ad2.ca_zip c_zip
+ ,d1.d_year as syear
+ ,d2.d_year as fsyear
+ ,d3.d_year s2year
+ ,count(*) cnt
+ ,sum(ss_wholesale_cost) s1
+ ,sum(ss_list_price) s2
+ ,sum(ss_coupon_amt) s3
+ FROM store_sales
+ ,store_returns
+ ,cs_ui
+ ,date_dim d1
+ ,date_dim d2
+ ,date_dim d3
+ ,store
+ ,customer
+ ,customer_demographics cd1
+ ,customer_demographics cd2
+ ,promotion
+ ,household_demographics hd1
+ ,household_demographics hd2
+ ,customer_address ad1
+ ,customer_address ad2
+ ,income_band ib1
+ ,income_band ib2
+ ,item
+ WHERE ss_store_sk = s_store_sk AND
+ ss_sold_date_sk = d1.d_date_sk AND
+ ss_customer_sk = c_customer_sk AND
+ ss_cdemo_sk= cd1.cd_demo_sk AND
+ ss_hdemo_sk = hd1.hd_demo_sk AND
+ ss_addr_sk = ad1.ca_address_sk and
+ ss_item_sk = i_item_sk and
+ ss_item_sk = sr_item_sk and
+ ss_ticket_number = sr_ticket_number and
+ ss_item_sk = cs_ui.cs_item_sk and
+ c_current_cdemo_sk = cd2.cd_demo_sk AND
+ c_current_hdemo_sk = hd2.hd_demo_sk AND
+ c_current_addr_sk = ad2.ca_address_sk and
+ c_first_sales_date_sk = d2.d_date_sk and
+ c_first_shipto_date_sk = d3.d_date_sk and
+ ss_promo_sk = p_promo_sk and
+ hd1.hd_income_band_sk = ib1.ib_income_band_sk and
+ hd2.hd_income_band_sk = ib2.ib_income_band_sk and
+ cd1.cd_marital_status <> cd2.cd_marital_status and
+ i_color in ('orange','lace','lawn','misty','blush','pink') and
+ i_current_price between 48 and 48 + 10 and
+ i_current_price between 48 + 1 and 48 + 15
+group by i_product_name
+ ,i_item_sk
+ ,s_store_name
+ ,s_zip
+ ,ad1.ca_street_number
+ ,ad1.ca_street_name
+ ,ad1.ca_city
+ ,ad1.ca_zip
+ ,ad2.ca_street_number
+ ,ad2.ca_street_name
+ ,ad2.ca_city
+ ,ad2.ca_zip
+ ,d1.d_year
+ ,d2.d_year
+ ,d3.d_year
+)
+select cs1.product_name
+ ,cs1.store_name
+ ,cs1.store_zip
+ ,cs1.b_street_number
+ ,cs1.b_street_name
+ ,cs1.b_city
+ ,cs1.b_zip
+ ,cs1.c_street_number
+ ,cs1.c_street_name
+ ,cs1.c_city
+ ,cs1.c_zip
+ ,cs1.syear
+ ,cs1.cnt
+ ,cs1.s1 as s11
+ ,cs1.s2 as s21
+ ,cs1.s3 as s31
+ ,cs2.s1 as s12
+ ,cs2.s2 as s22
+ ,cs2.s3 as s32
+ ,cs2.syear
+ ,cs2.cnt
+from cross_sales cs1,cross_sales cs2
+where cs1.item_sk=cs2.item_sk and
+ cs1.syear = 1999 and
+ cs2.syear = 1999 + 1 and
+ cs2.cnt <= cs1.cnt and
+ cs1.store_name = cs2.store_name and
+ cs1.store_zip = cs2.store_zip
+order by cs1.product_name
+ ,cs1.store_name
+ ,cs2.cnt
+ ,cs1.s1
+ ,cs2.s1;
-// '''
+ '''
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
