This is an automated email from the ASF dual-hosted git repository.
hello-stephen 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 c70cf12a841 [opt](regression) remove duplicated tpcds 64 plan shape
check (#65652)
c70cf12a841 is described below
commit c70cf12a8417b1eff336171faa4f6294dad51670
Author: minghong <[email protected]>
AuthorDate: Thu Jul 16 14:11:20 2026 +0800
[opt](regression) remove duplicated tpcds 64 plan shape check (#65652)
### What problem does this PR solve?
tpcds 64 plan is always created by dphyper.
so keep ds64 in dephyper dir, and remove it from other dirs.
---
.../shape_check/tpcds_sf1000/shape/query64.out | 102 --------
.../tpcds_sf1000_nopkfk/shape/query64.out | 102 --------
.../tpcds_sf100/noStatsRfPrune/query64.groovy | 166 ------------
.../tpcds_sf100/no_stats_shape/query64.groovy | 166 ------------
.../tpcds_sf100/rf_prune/query64.groovy | 161 ------------
.../shape_check/tpcds_sf100/shape/query64.groovy | 161 ------------
.../shape_check/tpcds_sf1000/hint/query64.groovy | 286 ---------------------
.../shape_check/tpcds_sf1000/shape/query64.groovy | 279 --------------------
.../tpcds_sf1000_nopkfk/shape/query64.groovy | 279 --------------------
.../tpcds_sf10t_orc/shape/query64.groovy | 166 ------------
10 files changed, 1868 deletions(-)
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query64.out
b/regression-test/data/shape_check/tpcds_sf1000/shape/query64.out
deleted file mode 100644
index 7d5490b3d05..00000000000
--- a/regression-test/data/shape_check/tpcds_sf1000/shape/query64.out
+++ /dev/null
@@ -1,102 +0,0 @@
--- This file is automatically generated. You should know what you did if you
want to edit this
--- !ds_shape_64 --
-PhysicalCteAnchor ( cteId=CTEId#1 )
---PhysicalCteProducer ( cteId=CTEId#1 )
-----PhysicalProject
-------hashAgg[GLOBAL]
---------PhysicalDistribute[DistributionSpecHash]
-----------hashAgg[LOCAL]
-------------PhysicalProject
---------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF19 i_item_sk->[cr_item_sk,cs_item_sk,sr_item_sk,ss_item_sk]
-----------------PhysicalProject
-------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd2.hd_income_band_sk = ib2.ib_income_band_sk))
otherCondition=() build RFs:RF18 ib_income_band_sk->[hd_income_band_sk]
---------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd1.hd_income_band_sk = ib1.ib_income_band_sk))
otherCondition=() build RFs:RF17 ib_income_band_sk->[hd_income_band_sk]
-------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk))
otherCondition=() build RFs:RF16 ca_address_sk->[c_current_addr_sk]
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=()
build RFs:RF15 ca_address_sk->[ss_addr_sk]
---------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_hdemo_sk = hd2.hd_demo_sk))
otherCondition=() build RFs:RF14 hd_demo_sk->[c_current_hdemo_sk]
-------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk)) otherCondition=()
build RFs:RF13 hd_demo_sk->[ss_hdemo_sk]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_promo_sk = promotion.p_promo_sk))
otherCondition=() build RFs:RF12 p_promo_sk->[ss_promo_sk]
---------------------------------------------PhysicalProject
-----------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk))
otherCondition=(( not (cd_marital_status = cd_marital_status))) build RFs:RF11
cd_demo_sk->[c_current_cdemo_sk]
-------------------------------------------------PhysicalProject
---------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk))
otherCondition=() build RFs:RF10 cd_demo_sk->[ss_cdemo_sk]
-----------------------------------------------------PhysicalProject
-------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk))
otherCondition=() build RFs:RF9 d_date_sk->[c_first_shipto_date_sk]
---------------------------------------------------------PhysicalProject
-----------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))
otherCondition=() build RFs:RF8 d_date_sk->[c_first_sales_date_sk]
-------------------------------------------------------------PhysicalProject
---------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_customer_sk =
customer.c_customer_sk)) otherCondition=() build RFs:RF7
c_customer_sk->[ss_customer_sk]
-----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_store_sk = store.s_store_sk))
otherCondition=() build RFs:RF6 s_store_sk->[ss_store_sk]
---------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[ss_sold_date_sk]
-------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_item_sk = cs_ui.cs_item_sk))
otherCondition=() build RFs:RF4 cs_item_sk->[sr_item_sk,ss_item_sk]
-----------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------hashJoin[INNER_JOIN
bucketShuffle] hashCondition=((store_sales.ss_item_sk =
store_returns.sr_item_sk) and (store_sales.ss_ticket_number =
store_returns.sr_ticket_number)) otherCondition=() build RFs:RF2
sr_item_sk->[ss_item_sk];RF3 sr_ticket_number->[ss_ticket_number]
---------------------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------------------PhysicalOlapScan[store_sales]
apply RFs: RF2 RF3 RF4 RF5 RF6 RF7 RF10 RF12 RF13 RF15 RF19
---------------------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------------------PhysicalOlapScan[store_returns]
apply RFs: RF4 RF19
-----------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------filter((sale
> (2 * refund)))
---------------------------------------------------------------------------------hashAgg[GLOBAL]
-----------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
-------------------------------------------------------------------------------------hashAgg[LOCAL]
---------------------------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------------------------hashJoin[INNER_JOIN
bucketShuffle] hashCondition=((catalog_sales.cs_item_sk =
catalog_returns.cr_item_sk) and (catalog_sales.cs_order_number =
catalog_returns.cr_order_number)) otherCondition=() build RFs:RF0
cr_item_sk->[cs_item_sk];RF1 cr_order_number->[cs_order_number]
-------------------------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF0 RF1 RF19
-------------------------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------------------------PhysicalOlapScan[catalog_returns]
apply RFs: RF19
-------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------filter(d_year
IN (1999, 2000))
-----------------------------------------------------------------------------PhysicalOlapScan[date_dim(d1)]
---------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------PhysicalOlapScan[store]
-----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[customer]
apply RFs: RF8 RF9 RF11 RF14 RF16
-------------------------------------------------------------PhysicalProject
---------------------------------------------------------------PhysicalOlapScan[date_dim(d2)]
---------------------------------------------------------PhysicalProject
-----------------------------------------------------------PhysicalOlapScan[date_dim(d3)]
-----------------------------------------------------PhysicalProject
-------------------------------------------------------PhysicalOlapScan[customer_demographics(cd1)]
-------------------------------------------------PhysicalProject
---------------------------------------------------PhysicalOlapScan[customer_demographics(cd2)]
---------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[promotion]
-----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[household_demographics(hd1)]
apply RFs: RF17
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[household_demographics(hd2)]
apply RFs: RF18
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[customer_address(ad1)]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[customer_address(ad2)]
-------------------------PhysicalProject
---------------------------PhysicalOlapScan[income_band(ib1)]
---------------------PhysicalProject
-----------------------PhysicalOlapScan[income_band(ib2)]
-----------------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]
---PhysicalResultSink
-----PhysicalQuickSort[MERGE_SORT]
-------PhysicalDistribute[DistributionSpecGather]
---------PhysicalQuickSort[LOCAL_SORT]
-----------PhysicalProject
-------------hashJoin[INNER_JOIN shuffle] hashCondition=((cs1.item_sk =
cs2.item_sk) and (cs1.store_name = cs2.store_name) and (cs1.store_zip =
cs2.store_zip)) otherCondition=((cs2.cnt <= cs1.cnt)) build RFs:RF20
item_sk->[item_sk];RF21 store_name->[store_name];RF22 store_zip->[store_zip]
---------------PhysicalProject
-----------------filter((cs1.syear = 1999))
-------------------PhysicalCteConsumer ( cteId=CTEId#1 ) apply RFs: RF20 RF21
RF22
---------------PhysicalProject
-----------------filter((cs2.syear = 2000))
-------------------PhysicalCteConsumer ( cteId=CTEId#1 )
-
diff --git
a/regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query64.out
b/regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query64.out
deleted file mode 100644
index 13e1bc08953..00000000000
--- a/regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query64.out
+++ /dev/null
@@ -1,102 +0,0 @@
--- This file is automatically generated. You should know what you did if you
want to edit this
--- !ds_shape_64 --
-PhysicalCteAnchor ( cteId=CTEId#1 )
---PhysicalCteProducer ( cteId=CTEId#1 )
-----PhysicalProject
-------hashAgg[GLOBAL]
---------PhysicalDistribute[DistributionSpecHash]
-----------hashAgg[LOCAL]
-------------PhysicalProject
---------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
build RFs:RF19 i_item_sk->[cr_item_sk,cs_item_sk,sr_item_sk,ss_item_sk]
-----------------PhysicalProject
-------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd2.hd_income_band_sk = ib2.ib_income_band_sk))
otherCondition=() build RFs:RF18 ib_income_band_sk->[hd_income_band_sk]
---------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((hd1.hd_income_band_sk = ib1.ib_income_band_sk))
otherCondition=() build RFs:RF17 ib_income_band_sk->[hd_income_band_sk]
-------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_addr_sk = ad2.ca_address_sk))
otherCondition=() build RFs:RF16 ca_address_sk->[c_current_addr_sk]
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_addr_sk = ad1.ca_address_sk)) otherCondition=()
build RFs:RF15 ca_address_sk->[ss_addr_sk]
---------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_hdemo_sk = hd2.hd_demo_sk))
otherCondition=() build RFs:RF14 hd_demo_sk->[c_current_hdemo_sk]
-------------------------------------PhysicalProject
---------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_hdemo_sk = hd1.hd_demo_sk)) otherCondition=()
build RFs:RF13 hd_demo_sk->[ss_hdemo_sk]
-----------------------------------------PhysicalProject
-------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_promo_sk = promotion.p_promo_sk))
otherCondition=() build RFs:RF12 p_promo_sk->[ss_promo_sk]
---------------------------------------------PhysicalProject
-----------------------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((customer.c_current_cdemo_sk = cd2.cd_demo_sk))
otherCondition=(( not (cd_marital_status = cd_marital_status))) build RFs:RF11
cd_demo_sk->[c_current_cdemo_sk]
-------------------------------------------------PhysicalProject
---------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_cdemo_sk = cd1.cd_demo_sk))
otherCondition=() build RFs:RF10 cd_demo_sk->[ss_cdemo_sk]
-----------------------------------------------------PhysicalProject
-------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((customer.c_first_shipto_date_sk = d3.d_date_sk))
otherCondition=() build RFs:RF9 d_date_sk->[c_first_shipto_date_sk]
---------------------------------------------------------PhysicalProject
-----------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((customer.c_first_sales_date_sk = d2.d_date_sk))
otherCondition=() build RFs:RF8 d_date_sk->[c_first_sales_date_sk]
-------------------------------------------------------------PhysicalProject
---------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_customer_sk =
customer.c_customer_sk)) otherCondition=() build RFs:RF7
c_customer_sk->[ss_customer_sk]
-----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_store_sk = store.s_store_sk))
otherCondition=() build RFs:RF6 s_store_sk->[ss_store_sk]
---------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------hashJoin[INNER_JOIN
bucketShuffle] hashCondition=((store_sales.ss_item_sk =
store_returns.sr_item_sk) and (store_sales.ss_ticket_number =
store_returns.sr_ticket_number)) otherCondition=() build RFs:RF4
sr_item_sk->[cr_item_sk,cs_item_sk,ss_item_sk];RF5
sr_ticket_number->[ss_ticket_number]
-------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_item_sk = cs_ui.cs_item_sk))
otherCondition=() build RFs:RF3 cs_item_sk->[ss_item_sk]
-----------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------hashJoin[INNER_JOIN
broadcast] hashCondition=((store_sales.ss_sold_date_sk = d1.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk]
---------------------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------------------PhysicalOlapScan[store_sales]
apply RFs: RF2 RF3 RF4 RF5 RF6 RF7 RF10 RF12 RF13 RF15 RF19
---------------------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------------------filter(d_year
IN (1999, 2000))
-------------------------------------------------------------------------------------PhysicalOlapScan[date_dim(d1)]
-----------------------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------------------filter((sale
> (2 * refund)))
---------------------------------------------------------------------------------hashAgg[GLOBAL]
-----------------------------------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
-------------------------------------------------------------------------------------hashAgg[LOCAL]
---------------------------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------------------------hashJoin[INNER_JOIN
bucketShuffle] hashCondition=((catalog_sales.cs_item_sk =
catalog_returns.cr_item_sk) and (catalog_sales.cs_order_number =
catalog_returns.cr_order_number)) otherCondition=() build RFs:RF0
cr_item_sk->[cs_item_sk];RF1 cr_order_number->[cs_order_number]
-------------------------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF0 RF1 RF4 RF19
-------------------------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------------------------PhysicalOlapScan[catalog_returns]
apply RFs: RF4 RF19
-------------------------------------------------------------------------PhysicalProject
---------------------------------------------------------------------------PhysicalOlapScan[store_returns]
apply RFs: RF19
---------------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------------PhysicalOlapScan[store]
-----------------------------------------------------------------PhysicalProject
-------------------------------------------------------------------PhysicalOlapScan[customer]
apply RFs: RF8 RF9 RF11 RF14 RF16
-------------------------------------------------------------PhysicalProject
---------------------------------------------------------------PhysicalOlapScan[date_dim(d2)]
---------------------------------------------------------PhysicalProject
-----------------------------------------------------------PhysicalOlapScan[date_dim(d3)]
-----------------------------------------------------PhysicalProject
-------------------------------------------------------PhysicalOlapScan[customer_demographics(cd1)]
-------------------------------------------------PhysicalProject
---------------------------------------------------PhysicalOlapScan[customer_demographics(cd2)]
---------------------------------------------PhysicalProject
-----------------------------------------------PhysicalOlapScan[promotion]
-----------------------------------------PhysicalProject
-------------------------------------------PhysicalOlapScan[household_demographics(hd1)]
apply RFs: RF17
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[household_demographics(hd2)]
apply RFs: RF18
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[customer_address(ad1)]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[customer_address(ad2)]
-------------------------PhysicalProject
---------------------------PhysicalOlapScan[income_band(ib1)]
---------------------PhysicalProject
-----------------------PhysicalOlapScan[income_band(ib2)]
-----------------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]
---PhysicalResultSink
-----PhysicalQuickSort[MERGE_SORT]
-------PhysicalDistribute[DistributionSpecGather]
---------PhysicalQuickSort[LOCAL_SORT]
-----------PhysicalProject
-------------hashJoin[INNER_JOIN shuffle] hashCondition=((cs1.item_sk =
cs2.item_sk) and (cs1.store_name = cs2.store_name) and (cs1.store_zip =
cs2.store_zip)) otherCondition=((cs2.cnt <= cs1.cnt)) build RFs:RF20
item_sk->[item_sk];RF21 store_name->[store_name];RF22 store_zip->[store_zip]
---------------PhysicalProject
-----------------filter((cs1.syear = 1999))
-------------------PhysicalCteConsumer ( cteId=CTEId#1 ) apply RFs: RF20 RF21
RF22
---------------PhysicalProject
-----------------filter((cs2.syear = 2000))
-------------------PhysicalCteConsumer ( cteId=CTEId#1 )
-
diff --git
a/regression-test/suites/shape_check/tpcds_sf100/noStatsRfPrune/query64.groovy
b/regression-test/suites/shape_check/tpcds_sf100/noStatsRfPrune/query64.groovy
deleted file mode 100644
index a96231ec2fe..00000000000
---
a/regression-test/suites/shape_check/tpcds_sf100/noStatsRfPrune/query64.groovy
+++ /dev/null
@@ -1,166 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("query64") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- if (isCloudMode()) {
- return
- }
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_nereids_distribute_planner=false'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=21G'
- sql 'set be_number_for_test=3'
- sql 'set enable_runtime_filter_prune=true'
- sql 'set parallel_pipeline_task_num=8'
- sql 'set forbid_unknown_col_stats=false'
- sql 'set enable_stats=false'
- sql "set runtime_filter_type=8"
- sql 'set broadcast_row_count_limit = 30000000'
- sql 'set enable_nereids_timeout = false'
- sql 'SET enable_pipeline_engine = true'
- sql "set disable_nereids_rules=PRUNE_EMPTY_PARTITION"
- sql "set memo_max_group_expression_size = 1000000"
-
- def ds64 = '''
- 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
('blanched','medium','brown','chocolate','burlywood','drab') and
- i_current_price between 23 and 23 + 10 and
- i_current_price between 23 + 1 and 23 + 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 = 2001 and
- cs2.syear = 2001 + 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 ' + ds64
-
-}
diff --git
a/regression-test/suites/shape_check/tpcds_sf100/no_stats_shape/query64.groovy
b/regression-test/suites/shape_check/tpcds_sf100/no_stats_shape/query64.groovy
deleted file mode 100644
index 28896677cf2..00000000000
---
a/regression-test/suites/shape_check/tpcds_sf100/no_stats_shape/query64.groovy
+++ /dev/null
@@ -1,166 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("query64") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- if (isCloudMode()) {
- return
- }
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_nereids_distribute_planner=false'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=21G'
- sql 'set be_number_for_test=3'
- sql 'set enable_runtime_filter_prune=false'
- sql 'set parallel_pipeline_task_num=8'
- sql 'set forbid_unknown_col_stats=false'
- sql 'set enable_stats=false'
- sql "set runtime_filter_type=8"
- sql 'set broadcast_row_count_limit = 30000000'
- sql 'set enable_nereids_timeout = false'
- sql 'SET enable_pipeline_engine = true'
- sql "set disable_nereids_rules=PRUNE_EMPTY_PARTITION"
- sql "set memo_max_group_expression_size = 1000000"
-
- def ds64 = '''
- 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
('blanched','medium','brown','chocolate','burlywood','drab') and
- i_current_price between 23 and 23 + 10 and
- i_current_price between 23 + 1 and 23 + 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 = 2001 and
- cs2.syear = 2001 + 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 ' + ds64
-
-}
diff --git
a/regression-test/suites/shape_check/tpcds_sf100/rf_prune/query64.groovy
b/regression-test/suites/shape_check/tpcds_sf100/rf_prune/query64.groovy
deleted file mode 100644
index ea7d7844ea9..00000000000
--- a/regression-test/suites/shape_check/tpcds_sf100/rf_prune/query64.groovy
+++ /dev/null
@@ -1,161 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("query64") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- if (isCloudMode()) {
- return
- }
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_nereids_distribute_planner=false'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=21G'
- sql 'set be_number_for_test=3'
- sql 'set parallel_pipeline_task_num=8; '
- sql 'set forbid_unknown_col_stats=true'
- sql 'set enable_nereids_timeout = false'
- sql 'set runtime_filter_type=8'
- sql 'set enable_runtime_filter_prune=true'
- sql "set disable_nereids_rules=PRUNE_EMPTY_PARTITION"
- sql "set memo_max_group_expression_size = 1000000"
-
- def ds = """with cs_ui as
- (select cs_item_sk
- ,sum(cs_ext_list_price) as
sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
- from catalog_sales
- ,catalog_returns
- where cs_item_sk = cr_item_sk
- and cs_order_number = cr_order_number
- group by cs_item_sk
- having
sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
-cross_sales as
- (select i_product_name product_name
- ,i_item_sk item_sk
- ,s_store_name store_name
- ,s_zip store_zip
- ,ad1.ca_street_number b_street_number
- ,ad1.ca_street_name b_street_name
- ,ad1.ca_city b_city
- ,ad1.ca_zip b_zip
- ,ad2.ca_street_number c_street_number
- ,ad2.ca_street_name c_street_name
- ,ad2.ca_city c_city
- ,ad2.ca_zip c_zip
- ,d1.d_year as syear
- ,d2.d_year as fsyear
- ,d3.d_year s2year
- ,count(*) cnt
- ,sum(ss_wholesale_cost) s1
- ,sum(ss_list_price) s2
- ,sum(ss_coupon_amt) s3
- FROM store_sales
- ,store_returns
- ,cs_ui
- ,date_dim d1
- ,date_dim d2
- ,date_dim d3
- ,store
- ,customer
- ,customer_demographics cd1
- ,customer_demographics cd2
- ,promotion
- ,household_demographics hd1
- ,household_demographics hd2
- ,customer_address ad1
- ,customer_address ad2
- ,income_band ib1
- ,income_band ib2
- ,item
- WHERE ss_store_sk = s_store_sk AND
- ss_sold_date_sk = d1.d_date_sk AND
- ss_customer_sk = c_customer_sk AND
- ss_cdemo_sk= cd1.cd_demo_sk AND
- ss_hdemo_sk = hd1.hd_demo_sk AND
- ss_addr_sk = ad1.ca_address_sk and
- ss_item_sk = i_item_sk and
- ss_item_sk = sr_item_sk and
- ss_ticket_number = sr_ticket_number and
- ss_item_sk = cs_ui.cs_item_sk and
- c_current_cdemo_sk = cd2.cd_demo_sk AND
- c_current_hdemo_sk = hd2.hd_demo_sk AND
- c_current_addr_sk = ad2.ca_address_sk and
- c_first_sales_date_sk = d2.d_date_sk and
- c_first_shipto_date_sk = d3.d_date_sk and
- ss_promo_sk = p_promo_sk and
- hd1.hd_income_band_sk = ib1.ib_income_band_sk and
- hd2.hd_income_band_sk = ib2.ib_income_band_sk and
- cd1.cd_marital_status <> cd2.cd_marital_status and
- i_color in
('blanched','medium','brown','chocolate','burlywood','drab') and
- i_current_price between 23 and 23 + 10 and
- i_current_price between 23 + 1 and 23 + 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 = 2001 and
- cs2.syear = 2001 + 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
- ${ds}
- """
-}
diff --git
a/regression-test/suites/shape_check/tpcds_sf100/shape/query64.groovy
b/regression-test/suites/shape_check/tpcds_sf100/shape/query64.groovy
deleted file mode 100644
index 557b8be4ba9..00000000000
--- a/regression-test/suites/shape_check/tpcds_sf100/shape/query64.groovy
+++ /dev/null
@@ -1,161 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("query64") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- if (isCloudMode()) {
- return
- }
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_nereids_distribute_planner=false'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=21G'
- sql 'set be_number_for_test=3'
- 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 disable_nereids_rules=PRUNE_EMPTY_PARTITION"
- sql "set memo_max_group_expression_size = 1000000"
-
- def ds = """with cs_ui as
- (select cs_item_sk
- ,sum(cs_ext_list_price) as
sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
- from catalog_sales
- ,catalog_returns
- where cs_item_sk = cr_item_sk
- and cs_order_number = cr_order_number
- group by cs_item_sk
- having
sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
-cross_sales as
- (select i_product_name product_name
- ,i_item_sk item_sk
- ,s_store_name store_name
- ,s_zip store_zip
- ,ad1.ca_street_number b_street_number
- ,ad1.ca_street_name b_street_name
- ,ad1.ca_city b_city
- ,ad1.ca_zip b_zip
- ,ad2.ca_street_number c_street_number
- ,ad2.ca_street_name c_street_name
- ,ad2.ca_city c_city
- ,ad2.ca_zip c_zip
- ,d1.d_year as syear
- ,d2.d_year as fsyear
- ,d3.d_year s2year
- ,count(*) cnt
- ,sum(ss_wholesale_cost) s1
- ,sum(ss_list_price) s2
- ,sum(ss_coupon_amt) s3
- FROM store_sales
- ,store_returns
- ,cs_ui
- ,date_dim d1
- ,date_dim d2
- ,date_dim d3
- ,store
- ,customer
- ,customer_demographics cd1
- ,customer_demographics cd2
- ,promotion
- ,household_demographics hd1
- ,household_demographics hd2
- ,customer_address ad1
- ,customer_address ad2
- ,income_band ib1
- ,income_band ib2
- ,item
- WHERE ss_store_sk = s_store_sk AND
- ss_sold_date_sk = d1.d_date_sk AND
- ss_customer_sk = c_customer_sk AND
- ss_cdemo_sk= cd1.cd_demo_sk AND
- ss_hdemo_sk = hd1.hd_demo_sk AND
- ss_addr_sk = ad1.ca_address_sk and
- ss_item_sk = i_item_sk and
- ss_item_sk = sr_item_sk and
- ss_ticket_number = sr_ticket_number and
- ss_item_sk = cs_ui.cs_item_sk and
- c_current_cdemo_sk = cd2.cd_demo_sk AND
- c_current_hdemo_sk = hd2.hd_demo_sk AND
- c_current_addr_sk = ad2.ca_address_sk and
- c_first_sales_date_sk = d2.d_date_sk and
- c_first_shipto_date_sk = d3.d_date_sk and
- ss_promo_sk = p_promo_sk and
- hd1.hd_income_band_sk = ib1.ib_income_band_sk and
- hd2.hd_income_band_sk = ib2.ib_income_band_sk and
- cd1.cd_marital_status <> cd2.cd_marital_status and
- i_color in
('blanched','medium','brown','chocolate','burlywood','drab') and
- i_current_price between 23 and 23 + 10 and
- i_current_price between 23 + 1 and 23 + 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 = 2001 and
- cs2.syear = 2001 + 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
- ${ds}
- """
-}
diff --git
a/regression-test/suites/shape_check/tpcds_sf1000/hint/query64.groovy
b/regression-test/suites/shape_check/tpcds_sf1000/hint/query64.groovy
deleted file mode 100644
index c548c8f6ce8..00000000000
--- a/regression-test/suites/shape_check/tpcds_sf1000/hint/query64.groovy
+++ /dev/null
@@ -1,286 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("query64") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- if (isCloudMode()) {
- return
- }
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_nereids_distribute_planner=false'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=21G'
- sql 'set be_number_for_test=3'
- 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 disable_nereids_rules=PRUNE_EMPTY_PARTITION"
-
- sql "set memo_max_group_expression_size = 1000000"
-
- def ds = """with cs_ui as
- (select cs_item_sk
- ,sum(cs_ext_list_price) as
sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
- from catalog_sales
- ,catalog_returns
- where cs_item_sk = cr_item_sk
- and cs_order_number = cr_order_number
- group by cs_item_sk
- having
sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
-cross_sales as
- (select i_product_name product_name
- ,i_item_sk item_sk
- ,s_store_name store_name
- ,s_zip store_zip
- ,ad1.ca_street_number b_street_number
- ,ad1.ca_street_name b_street_name
- ,ad1.ca_city b_city
- ,ad1.ca_zip b_zip
- ,ad2.ca_street_number c_street_number
- ,ad2.ca_street_name c_street_name
- ,ad2.ca_city c_city
- ,ad2.ca_zip c_zip
- ,d1.d_year as syear
- ,d2.d_year as fsyear
- ,d3.d_year s2year
- ,count(*) cnt
- ,sum(ss_wholesale_cost) s1
- ,sum(ss_list_price) s2
- ,sum(ss_coupon_amt) s3
- FROM store_sales
- ,store_returns
- ,cs_ui
- ,date_dim d1
- ,date_dim d2
- ,date_dim d3
- ,store
- ,customer
- ,customer_demographics cd1
- ,customer_demographics cd2
- ,promotion
- ,household_demographics hd1
- ,household_demographics hd2
- ,customer_address ad1
- ,customer_address ad2
- ,income_band ib1
- ,income_band ib2
- ,item
- WHERE ss_store_sk = s_store_sk AND
- ss_sold_date_sk = d1.d_date_sk AND
- ss_customer_sk = c_customer_sk AND
- ss_cdemo_sk= cd1.cd_demo_sk AND
- ss_hdemo_sk = hd1.hd_demo_sk AND
- ss_addr_sk = ad1.ca_address_sk and
- ss_item_sk = i_item_sk and
- ss_item_sk = sr_item_sk and
- ss_ticket_number = sr_ticket_number and
- ss_item_sk = cs_ui.cs_item_sk and
- c_current_cdemo_sk = cd2.cd_demo_sk AND
- c_current_hdemo_sk = hd2.hd_demo_sk AND
- c_current_addr_sk = ad2.ca_address_sk and
- c_first_sales_date_sk = d2.d_date_sk and
- c_first_shipto_date_sk = d3.d_date_sk and
- ss_promo_sk = p_promo_sk and
- hd1.hd_income_band_sk = ib1.ib_income_band_sk and
- hd2.hd_income_band_sk = ib2.ib_income_band_sk and
- cd1.cd_marital_status <> cd2.cd_marital_status and
- i_color in ('orange','lace','lawn','misty','blush','pink') and
- i_current_price between 48 and 48 + 10 and
- i_current_price between 48 + 1 and 48 + 15
-group by i_product_name
- ,i_item_sk
- ,s_store_name
- ,s_zip
- ,ad1.ca_street_number
- ,ad1.ca_street_name
- ,ad1.ca_city
- ,ad1.ca_zip
- ,ad2.ca_street_number
- ,ad2.ca_street_name
- ,ad2.ca_city
- ,ad2.ca_zip
- ,d1.d_year
- ,d2.d_year
- ,d3.d_year
-)
-select cs1.product_name
- ,cs1.store_name
- ,cs1.store_zip
- ,cs1.b_street_number
- ,cs1.b_street_name
- ,cs1.b_city
- ,cs1.b_zip
- ,cs1.c_street_number
- ,cs1.c_street_name
- ,cs1.c_city
- ,cs1.c_zip
- ,cs1.syear
- ,cs1.cnt
- ,cs1.s1 as s11
- ,cs1.s2 as s21
- ,cs1.s3 as s31
- ,cs2.s1 as s12
- ,cs2.s2 as s22
- ,cs2.s3 as s32
- ,cs2.syear
- ,cs2.cnt
-from cross_sales cs1,cross_sales cs2
-where cs1.item_sk=cs2.item_sk and
- cs1.syear = 1999 and
- cs2.syear = 1999 + 1 and
- cs2.cnt <= cs1.cnt and
- cs1.store_name = cs2.store_name and
- cs1.store_zip = cs2.store_zip
-order by cs1.product_name
- ,cs1.store_name
- ,cs2.cnt
- ,cs1.s1
- ,cs2.s1"""
- qt_ds_shape_64 '''
- explain shape plan
- with cs_ui as
- (select
- /*+ leading(catalog_sales shuffle catalog_returns) */
- cs_item_sk
- ,sum(cs_ext_list_price) as
sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
- from catalog_sales
- ,catalog_returns
- where cs_item_sk = cr_item_sk
- and cs_order_number = cr_order_number
- group by cs_item_sk
- having
sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
-cross_sales as
- (select
- /*+ leading( {store_sales {{customer d2} cd2}} cd1 d3 item {hd1 ib1}
store_returns ad1 hd2 ad2 ib2 d1 store promotion cs_ui) */
- i_product_name product_name
- ,i_item_sk item_sk
- ,s_store_name store_name
- ,s_zip store_zip
- ,ad1.ca_street_number b_street_number
- ,ad1.ca_street_name b_street_name
- ,ad1.ca_city b_city
- ,ad1.ca_zip b_zip
- ,ad2.ca_street_number c_street_number
- ,ad2.ca_street_name c_street_name
- ,ad2.ca_city c_city
- ,ad2.ca_zip c_zip
- ,d1.d_year as syear
- ,d2.d_year as fsyear
- ,d3.d_year s2year
- ,count(*) cnt
- ,sum(ss_wholesale_cost) s1
- ,sum(ss_list_price) s2
- ,sum(ss_coupon_amt) s3
- FROM store_sales
- ,store_returns
- ,cs_ui
- ,date_dim d1
- ,date_dim d2
- ,date_dim d3
- ,store
- ,customer
- ,customer_demographics cd1
- ,customer_demographics cd2
- ,promotion
- ,household_demographics hd1
- ,household_demographics hd2
- ,customer_address ad1
- ,customer_address ad2
- ,income_band ib1
- ,income_band ib2
- ,item
- WHERE ss_store_sk = s_store_sk AND
- ss_sold_date_sk = d1.d_date_sk AND
- ss_customer_sk = c_customer_sk AND
- ss_cdemo_sk= cd1.cd_demo_sk AND
- ss_hdemo_sk = hd1.hd_demo_sk AND
- ss_addr_sk = ad1.ca_address_sk and
- ss_item_sk = i_item_sk and
- ss_item_sk = sr_item_sk and
- ss_ticket_number = sr_ticket_number and
- ss_item_sk = cs_ui.cs_item_sk and
- c_current_cdemo_sk = cd2.cd_demo_sk AND
- c_current_hdemo_sk = hd2.hd_demo_sk AND
- c_current_addr_sk = ad2.ca_address_sk and
- c_first_sales_date_sk = d2.d_date_sk and
- c_first_shipto_date_sk = d3.d_date_sk and
- ss_promo_sk = p_promo_sk and
- hd1.hd_income_band_sk = ib1.ib_income_band_sk and
- hd2.hd_income_band_sk = ib2.ib_income_band_sk and
- cd1.cd_marital_status <> cd2.cd_marital_status and
- i_color in ('orange','lace','lawn','misty','blush','pink') and
- i_current_price between 48 and 48 + 10 and
- i_current_price between 48 + 1 and 48 + 15
-group by i_product_name
- ,i_item_sk
- ,s_store_name
- ,s_zip
- ,ad1.ca_street_number
- ,ad1.ca_street_name
- ,ad1.ca_city
- ,ad1.ca_zip
- ,ad2.ca_street_number
- ,ad2.ca_street_name
- ,ad2.ca_city
- ,ad2.ca_zip
- ,d1.d_year
- ,d2.d_year
- ,d3.d_year
-)
-select
-/*+ leading(cs1 shuffle cs2) */
- cs1.product_name
- ,cs1.store_name
- ,cs1.store_zip
- ,cs1.b_street_number
- ,cs1.b_street_name
- ,cs1.b_city
- ,cs1.b_zip
- ,cs1.c_street_number
- ,cs1.c_street_name
- ,cs1.c_city
- ,cs1.c_zip
- ,cs1.syear
- ,cs1.cnt
- ,cs1.s1 as s11
- ,cs1.s2 as s21
- ,cs1.s3 as s31
- ,cs2.s1 as s12
- ,cs2.s2 as s22
- ,cs2.s3 as s32
- ,cs2.syear
- ,cs2.cnt
-from cross_sales cs1,cross_sales cs2
-where cs1.item_sk=cs2.item_sk and
- cs1.syear = 1999 and
- cs2.syear = 1999 + 1 and
- cs2.cnt <= cs1.cnt and
- cs1.store_name = cs2.store_name and
- cs1.store_zip = cs2.store_zip
-order by cs1.product_name
- ,cs1.store_name
- ,cs2.cnt
- ,cs1.s1
- ,cs2.s1
- '''
-}
diff --git
a/regression-test/suites/shape_check/tpcds_sf1000/shape/query64.groovy
b/regression-test/suites/shape_check/tpcds_sf1000/shape/query64.groovy
deleted file mode 100644
index ef89e0bd0a7..00000000000
--- a/regression-test/suites/shape_check/tpcds_sf1000/shape/query64.groovy
+++ /dev/null
@@ -1,279 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("query64") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- if (isCloudMode()) {
- return
- }
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_nereids_distribute_planner=false'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=21G'
- sql 'set be_number_for_test=3'
- 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 disable_nereids_rules=PRUNE_EMPTY_PARTITION"
- sql "set memo_max_group_expression_size = 1000000"
-
- def ds = """with cs_ui as
- (select cs_item_sk
- ,sum(cs_ext_list_price) as
sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
- from catalog_sales
- ,catalog_returns
- where cs_item_sk = cr_item_sk
- and cs_order_number = cr_order_number
- group by cs_item_sk
- having
sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
-cross_sales as
- (select i_product_name product_name
- ,i_item_sk item_sk
- ,s_store_name store_name
- ,s_zip store_zip
- ,ad1.ca_street_number b_street_number
- ,ad1.ca_street_name b_street_name
- ,ad1.ca_city b_city
- ,ad1.ca_zip b_zip
- ,ad2.ca_street_number c_street_number
- ,ad2.ca_street_name c_street_name
- ,ad2.ca_city c_city
- ,ad2.ca_zip c_zip
- ,d1.d_year as syear
- ,d2.d_year as fsyear
- ,d3.d_year s2year
- ,count(*) cnt
- ,sum(ss_wholesale_cost) s1
- ,sum(ss_list_price) s2
- ,sum(ss_coupon_amt) s3
- FROM store_sales
- ,store_returns
- ,cs_ui
- ,date_dim d1
- ,date_dim d2
- ,date_dim d3
- ,store
- ,customer
- ,customer_demographics cd1
- ,customer_demographics cd2
- ,promotion
- ,household_demographics hd1
- ,household_demographics hd2
- ,customer_address ad1
- ,customer_address ad2
- ,income_band ib1
- ,income_band ib2
- ,item
- WHERE ss_store_sk = s_store_sk AND
- ss_sold_date_sk = d1.d_date_sk AND
- ss_customer_sk = c_customer_sk AND
- ss_cdemo_sk= cd1.cd_demo_sk AND
- ss_hdemo_sk = hd1.hd_demo_sk AND
- ss_addr_sk = ad1.ca_address_sk and
- ss_item_sk = i_item_sk and
- ss_item_sk = sr_item_sk and
- ss_ticket_number = sr_ticket_number and
- ss_item_sk = cs_ui.cs_item_sk and
- c_current_cdemo_sk = cd2.cd_demo_sk AND
- c_current_hdemo_sk = hd2.hd_demo_sk AND
- c_current_addr_sk = ad2.ca_address_sk and
- c_first_sales_date_sk = d2.d_date_sk and
- c_first_shipto_date_sk = d3.d_date_sk and
- ss_promo_sk = p_promo_sk and
- hd1.hd_income_band_sk = ib1.ib_income_band_sk and
- hd2.hd_income_band_sk = ib2.ib_income_band_sk and
- cd1.cd_marital_status <> cd2.cd_marital_status and
- i_color in ('orange','lace','lawn','misty','blush','pink') and
- i_current_price between 48 and 48 + 10 and
- i_current_price between 48 + 1 and 48 + 15
-group by i_product_name
- ,i_item_sk
- ,s_store_name
- ,s_zip
- ,ad1.ca_street_number
- ,ad1.ca_street_name
- ,ad1.ca_city
- ,ad1.ca_zip
- ,ad2.ca_street_number
- ,ad2.ca_street_name
- ,ad2.ca_city
- ,ad2.ca_zip
- ,d1.d_year
- ,d2.d_year
- ,d3.d_year
-)
-select cs1.product_name
- ,cs1.store_name
- ,cs1.store_zip
- ,cs1.b_street_number
- ,cs1.b_street_name
- ,cs1.b_city
- ,cs1.b_zip
- ,cs1.c_street_number
- ,cs1.c_street_name
- ,cs1.c_city
- ,cs1.c_zip
- ,cs1.syear
- ,cs1.cnt
- ,cs1.s1 as s11
- ,cs1.s2 as s21
- ,cs1.s3 as s31
- ,cs2.s1 as s12
- ,cs2.s2 as s22
- ,cs2.s3 as s32
- ,cs2.syear
- ,cs2.cnt
-from cross_sales cs1,cross_sales cs2
-where cs1.item_sk=cs2.item_sk and
- cs1.syear = 1999 and
- cs2.syear = 1999 + 1 and
- cs2.cnt <= cs1.cnt and
- cs1.store_name = cs2.store_name and
- cs1.store_zip = cs2.store_zip
-order by cs1.product_name
- ,cs1.store_name
- ,cs2.cnt
- ,cs1.s1
- ,cs2.s1"""
-// qt_ds_shape_64 '''
-// explain shape plan
-// with cs_ui as
-// (select 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
-// '''
-}
diff --git
a/regression-test/suites/shape_check/tpcds_sf1000_nopkfk/shape/query64.groovy
b/regression-test/suites/shape_check/tpcds_sf1000_nopkfk/shape/query64.groovy
deleted file mode 100644
index ef89e0bd0a7..00000000000
---
a/regression-test/suites/shape_check/tpcds_sf1000_nopkfk/shape/query64.groovy
+++ /dev/null
@@ -1,279 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("query64") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- if (isCloudMode()) {
- return
- }
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_nereids_distribute_planner=false'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=21G'
- sql 'set be_number_for_test=3'
- 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 disable_nereids_rules=PRUNE_EMPTY_PARTITION"
- sql "set memo_max_group_expression_size = 1000000"
-
- def ds = """with cs_ui as
- (select cs_item_sk
- ,sum(cs_ext_list_price) as
sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
- from catalog_sales
- ,catalog_returns
- where cs_item_sk = cr_item_sk
- and cs_order_number = cr_order_number
- group by cs_item_sk
- having
sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
-cross_sales as
- (select i_product_name product_name
- ,i_item_sk item_sk
- ,s_store_name store_name
- ,s_zip store_zip
- ,ad1.ca_street_number b_street_number
- ,ad1.ca_street_name b_street_name
- ,ad1.ca_city b_city
- ,ad1.ca_zip b_zip
- ,ad2.ca_street_number c_street_number
- ,ad2.ca_street_name c_street_name
- ,ad2.ca_city c_city
- ,ad2.ca_zip c_zip
- ,d1.d_year as syear
- ,d2.d_year as fsyear
- ,d3.d_year s2year
- ,count(*) cnt
- ,sum(ss_wholesale_cost) s1
- ,sum(ss_list_price) s2
- ,sum(ss_coupon_amt) s3
- FROM store_sales
- ,store_returns
- ,cs_ui
- ,date_dim d1
- ,date_dim d2
- ,date_dim d3
- ,store
- ,customer
- ,customer_demographics cd1
- ,customer_demographics cd2
- ,promotion
- ,household_demographics hd1
- ,household_demographics hd2
- ,customer_address ad1
- ,customer_address ad2
- ,income_band ib1
- ,income_band ib2
- ,item
- WHERE ss_store_sk = s_store_sk AND
- ss_sold_date_sk = d1.d_date_sk AND
- ss_customer_sk = c_customer_sk AND
- ss_cdemo_sk= cd1.cd_demo_sk AND
- ss_hdemo_sk = hd1.hd_demo_sk AND
- ss_addr_sk = ad1.ca_address_sk and
- ss_item_sk = i_item_sk and
- ss_item_sk = sr_item_sk and
- ss_ticket_number = sr_ticket_number and
- ss_item_sk = cs_ui.cs_item_sk and
- c_current_cdemo_sk = cd2.cd_demo_sk AND
- c_current_hdemo_sk = hd2.hd_demo_sk AND
- c_current_addr_sk = ad2.ca_address_sk and
- c_first_sales_date_sk = d2.d_date_sk and
- c_first_shipto_date_sk = d3.d_date_sk and
- ss_promo_sk = p_promo_sk and
- hd1.hd_income_band_sk = ib1.ib_income_band_sk and
- hd2.hd_income_band_sk = ib2.ib_income_band_sk and
- cd1.cd_marital_status <> cd2.cd_marital_status and
- i_color in ('orange','lace','lawn','misty','blush','pink') and
- i_current_price between 48 and 48 + 10 and
- i_current_price between 48 + 1 and 48 + 15
-group by i_product_name
- ,i_item_sk
- ,s_store_name
- ,s_zip
- ,ad1.ca_street_number
- ,ad1.ca_street_name
- ,ad1.ca_city
- ,ad1.ca_zip
- ,ad2.ca_street_number
- ,ad2.ca_street_name
- ,ad2.ca_city
- ,ad2.ca_zip
- ,d1.d_year
- ,d2.d_year
- ,d3.d_year
-)
-select cs1.product_name
- ,cs1.store_name
- ,cs1.store_zip
- ,cs1.b_street_number
- ,cs1.b_street_name
- ,cs1.b_city
- ,cs1.b_zip
- ,cs1.c_street_number
- ,cs1.c_street_name
- ,cs1.c_city
- ,cs1.c_zip
- ,cs1.syear
- ,cs1.cnt
- ,cs1.s1 as s11
- ,cs1.s2 as s21
- ,cs1.s3 as s31
- ,cs2.s1 as s12
- ,cs2.s2 as s22
- ,cs2.s3 as s32
- ,cs2.syear
- ,cs2.cnt
-from cross_sales cs1,cross_sales cs2
-where cs1.item_sk=cs2.item_sk and
- cs1.syear = 1999 and
- cs2.syear = 1999 + 1 and
- cs2.cnt <= cs1.cnt and
- cs1.store_name = cs2.store_name and
- cs1.store_zip = cs2.store_zip
-order by cs1.product_name
- ,cs1.store_name
- ,cs2.cnt
- ,cs1.s1
- ,cs2.s1"""
-// qt_ds_shape_64 '''
-// explain shape plan
-// with cs_ui as
-// (select 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
-// '''
-}
diff --git
a/regression-test/suites/shape_check/tpcds_sf10t_orc/shape/query64.groovy
b/regression-test/suites/shape_check/tpcds_sf10t_orc/shape/query64.groovy
deleted file mode 100644
index 93f665fb540..00000000000
--- a/regression-test/suites/shape_check/tpcds_sf10t_orc/shape/query64.groovy
+++ /dev/null
@@ -1,166 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("query64") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- if (isCloudMode()) {
- return
- }
- sql """
- use ${db};
- set enable_nereids_planner=true;
- set enable_nereids_distribute_planner=false;
- set enable_fallback_to_original_planner=false;
- set exec_mem_limit=21G;
- set be_number_for_test=3;
- set parallel_pipeline_task_num=8;
- set forbid_unknown_col_stats=true;
- set enable_nereids_timeout = false;
- set enable_runtime_filter_prune=false;
- set runtime_filter_type=8;
- set dump_nereids_memo=false;
- set disable_nereids_rules='PRUNE_EMPTY_PARTITION';
- set enable_fold_constant_by_be = false;
- set push_topn_to_agg = true;
- set TOPN_OPT_LIMIT_THRESHOLD = 1024;
- set enable_parallel_result_sink=true;
- set memo_max_group_expression_size = 1000000;
- """
- 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 ('azure','gainsboro','misty','blush','hot','lemon') and
- i_current_price between 80 and 80 + 10 and
- i_current_price between 80 + 1 and 80 + 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]
