This is an automated email from the ASF dual-hosted git repository.
englefly pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new efdfdcc840b [fix](cases) fix regression test (#38197)
efdfdcc840b is described below
commit efdfdcc840befac4f625b4297cc0f4b808a6246b
Author: LiBinfeng <[email protected]>
AuthorDate: Tue Jul 23 10:30:27 2024 +0800
[fix](cases) fix regression test (#38197)
regression test broken because of pr influence each other:
#37951 and #38081
---
.../data/nereids_hint_tpcds_p0/shape/query23.out | 12 +-
.../data/nereids_hint_tpcds_p0/shape/query35.out | 28 ++--
.../data/nereids_hint_tpcds_p0/shape/query69.out | 42 ++---
.../nereids_hint_tpcds_p0/shape/query33.groovy | 186 ---------------------
.../nereids_hint_tpcds_p0/shape/query35.groovy | 149 -----------------
.../nereids_hint_tpcds_p0/shape/query83.groovy | 169 -------------------
6 files changed, 41 insertions(+), 545 deletions(-)
diff --git a/regression-test/data/nereids_hint_tpcds_p0/shape/query23.out
b/regression-test/data/nereids_hint_tpcds_p0/shape/query23.out
index fa66824d3b8..9e2140c8327 100644
--- a/regression-test/data/nereids_hint_tpcds_p0/shape/query23.out
+++ b/regression-test/data/nereids_hint_tpcds_p0/shape/query23.out
@@ -52,8 +52,7 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------hashAgg[LOCAL]
--------------PhysicalUnion
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = frequent_ss_items.item_sk))
otherCondition=() build RFs:RF4 cs_item_sk->[item_sk]
---------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF4
+------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_item_sk = frequent_ss_items.item_sk))
otherCondition=()
--------------------PhysicalProject
----------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((catalog_sales.cs_bill_customer_sk =
best_ss_customer.c_customer_sk)) otherCondition=()
------------------------PhysicalProject
@@ -64,17 +63,18 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
------------------------------filter((date_dim.d_moy = 7) and (date_dim.d_year
= 2000))
--------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalCteConsumer ( cteId=CTEId#2 )
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = frequent_ss_items.item_sk))
otherCondition=() build RFs:RF6 ws_item_sk->[item_sk]
---------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF6
+------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_item_sk = frequent_ss_items.item_sk))
otherCondition=()
--------------------PhysicalProject
----------------------hashJoin[LEFT_SEMI_JOIN shuffle]
hashCondition=((web_sales.ws_bill_customer_sk =
best_ss_customer.c_customer_sk)) otherCondition=()
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[ws_sold_date_sk]
+--------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[ws_sold_date_sk]
----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[web_sales] apply RFs: RF5
+------------------------------PhysicalOlapScan[web_sales] apply RFs: RF4
----------------------------PhysicalProject
------------------------------filter((date_dim.d_moy = 7) and (date_dim.d_year
= 2000))
--------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalCteConsumer ( cteId=CTEId#2 )
+--------------------PhysicalCteConsumer ( cteId=CTEId#0 )
diff --git a/regression-test/data/nereids_hint_tpcds_p0/shape/query35.out
b/regression-test/data/nereids_hint_tpcds_p0/shape/query35.out
index ce3860e81ff..9601991cdc5 100644
--- a/regression-test/data/nereids_hint_tpcds_p0/shape/query35.out
+++ b/regression-test/data/nereids_hint_tpcds_p0/shape/query35.out
@@ -18,30 +18,30 @@ PhysicalResultSink
--------------------------PhysicalProject
----------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year =
1999))
------------------------------PhysicalOlapScan[date_dim]
-----------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
-------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[ws_sold_date_sk]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[web_sales] apply RFs: RF4
-----------------------------PhysicalProject
-------------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year
= 1999))
---------------------------------PhysicalOlapScan[date_dim]
-------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF3 c_customer_sk->[ss_customer_sk]
+----------------------hashJoin[LEFT_SEMI_JOIN bucketShuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
+------------------------hashJoin[RIGHT_SEMI_JOIN shuffle]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF4 c_customer_sk->[ss_customer_sk]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk]
+----------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF3 d_date_sk->[ss_sold_date_sk]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[store_sales] apply RFs: RF2
RF3
+--------------------------------PhysicalOlapScan[store_sales] apply RFs: RF3
RF4
------------------------------PhysicalProject
--------------------------------filter((date_dim.d_qoy < 4) and
(date_dim.d_year = 1999))
----------------------------------PhysicalOlapScan[date_dim]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF1 cd_demo_sk->[c_current_cdemo_sk]
+----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF2 cd_demo_sk->[c_current_cdemo_sk]
------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF0 ca_address_sk->[c_current_addr_sk]
+--------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF1 ca_address_sk->[c_current_addr_sk]
----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[customer] apply RFs: RF0
RF1
+------------------------------------PhysicalOlapScan[customer] apply RFs: RF1
RF2
----------------------------------PhysicalProject
------------------------------------PhysicalOlapScan[customer_address]
------------------------------PhysicalProject
--------------------------------PhysicalOlapScan[customer_demographics]
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF0 d_date_sk->[ws_sold_date_sk]
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[web_sales] apply RFs: RF0
+----------------------------PhysicalProject
+------------------------------filter((date_dim.d_qoy < 4) and (date_dim.d_year
= 1999))
+--------------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/nereids_hint_tpcds_p0/shape/query69.out
b/regression-test/data/nereids_hint_tpcds_p0/shape/query69.out
index c72d47fb024..422f77b5841 100644
--- a/regression-test/data/nereids_hint_tpcds_p0/shape/query69.out
+++ b/regression-test/data/nereids_hint_tpcds_p0/shape/query69.out
@@ -9,39 +9,39 @@ PhysicalResultSink
------------PhysicalDistribute[DistributionSpecHash]
--------------hashAgg[LOCAL]
----------------PhysicalProject
-------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF7 c_customer_sk->[ss_customer_sk]
+------------------hashJoin[RIGHT_SEMI_JOIN shuffleBucket]
hashCondition=((c.c_customer_sk = store_sales.ss_customer_sk))
otherCondition=() build RFs:RF6 c_customer_sk->[ss_customer_sk]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF6 d_date_sk->[ss_sold_date_sk]
+----------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF5 d_date_sk->[ss_sold_date_sk]
------------------------PhysicalProject
---------------------------PhysicalOlapScan[store_sales] apply RFs: RF6 RF7
+--------------------------PhysicalOlapScan[store_sales] apply RFs: RF5 RF6
------------------------PhysicalProject
--------------------------filter((date_dim.d_moy <= 3) and (date_dim.d_moy >=
1) and (date_dim.d_year = 2002))
----------------------------PhysicalOlapScan[date_dim]
---------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=() build RFs:RF5 c_customer_sk->[cs_ship_customer_sk]
+--------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = catalog_sales.cs_ship_customer_sk))
otherCondition=() build RFs:RF4 c_customer_sk->[cs_ship_customer_sk]
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF4 d_date_sk->[cs_sold_date_sk]
+------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF3 d_date_sk->[cs_sold_date_sk]
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF4 RF5
+----------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF3 RF4
--------------------------PhysicalProject
----------------------------filter((date_dim.d_moy <= 3) and (date_dim.d_moy
>= 1) and (date_dim.d_year = 2002))
------------------------------PhysicalOlapScan[date_dim]
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF3 c_current_cdemo_sk->[cd_demo_sk]
+------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((customer_demographics.cd_demo_sk = c.c_current_cdemo_sk))
otherCondition=() build RFs:RF2 c_current_cdemo_sk->[cd_demo_sk]
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[customer_demographics] apply RFs:
RF3
---------------------------hashJoin[RIGHT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=() build RFs:RF2 c_customer_sk->[ws_bill_customer_sk]
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF1 d_date_sk->[ws_sold_date_sk]
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[web_sales] apply RFs: RF1
RF2
---------------------------------PhysicalProject
-----------------------------------filter((date_dim.d_moy <= 3) and
(date_dim.d_moy >= 1) and (date_dim.d_year = 2002))
-------------------------------------PhysicalOlapScan[date_dim]
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF0 ca_address_sk->[c_current_addr_sk]
+----------------------------PhysicalOlapScan[customer_demographics] apply RFs:
RF2
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN shuffle]
hashCondition=((c.c_current_addr_sk = ca.ca_address_sk)) otherCondition=()
build RFs:RF1 ca_address_sk->[c_current_addr_sk]
+------------------------------hashJoin[LEFT_ANTI_JOIN shuffle]
hashCondition=((c.c_customer_sk = web_sales.ws_bill_customer_sk))
otherCondition=()
--------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[customer] apply RFs: RF0
+----------------------------------PhysicalOlapScan[customer] apply RFs: RF1
--------------------------------PhysicalProject
-----------------------------------filter(ca_state IN ('IL', 'ME', 'TX'))
-------------------------------------PhysicalOlapScan[customer_address]
+----------------------------------hashJoin[INNER_JOIN broadcast]
hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk))
otherCondition=() build RFs:RF0 d_date_sk->[ws_sold_date_sk]
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[web_sales] apply RFs:
RF0
+------------------------------------PhysicalProject
+--------------------------------------filter((date_dim.d_moy <= 3) and
(date_dim.d_moy >= 1) and (date_dim.d_year = 2002))
+----------------------------------------PhysicalOlapScan[date_dim]
+------------------------------PhysicalProject
+--------------------------------filter(ca_state IN ('IL', 'ME', 'TX'))
+----------------------------------PhysicalOlapScan[customer_address]
diff --git a/regression-test/suites/nereids_hint_tpcds_p0/shape/query33.groovy
b/regression-test/suites/nereids_hint_tpcds_p0/shape/query33.groovy
deleted file mode 100644
index 5ab1da27a19..00000000000
--- a/regression-test/suites/nereids_hint_tpcds_p0/shape/query33.groovy
+++ /dev/null
@@ -1,186 +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("query33") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=21G'
- sql 'set be_number_for_test=3'
- sql 'set parallel_fragment_exec_instance_num=8; '
- sql 'set parallel_pipeline_task_num=8; '
- sql 'set forbid_unknown_col_stats=true'
- sql 'set enable_nereids_timeout = false'
- sql 'set enable_runtime_filter_prune=false'
- sql 'set runtime_filter_type=8'
- sql 'set dump_nereids_memo=false'
- sql "set disable_nereids_rules=PRUNE_EMPTY_PARTITION"
-
- def ds = """with ss as (
- select
- i_manufact_id,sum(ss_ext_sales_price) total_sales
- from
- store_sales,
- date_dim,
- customer_address,
- item
- where
- i_manufact_id in (select
- i_manufact_id
-from
- item
-where i_category in ('Books'))
- and ss_item_sk = i_item_sk
- and ss_sold_date_sk = d_date_sk
- and d_year = 2001
- and d_moy = 3
- and ss_addr_sk = ca_address_sk
- and ca_gmt_offset = -5
- group by i_manufact_id),
- cs as (
- select
- i_manufact_id,sum(cs_ext_sales_price) total_sales
- from
- catalog_sales,
- date_dim,
- customer_address,
- item
- where
- i_manufact_id in (select
- i_manufact_id
-from
- item
-where i_category in ('Books'))
- and cs_item_sk = i_item_sk
- and cs_sold_date_sk = d_date_sk
- and d_year = 2001
- and d_moy = 3
- and cs_bill_addr_sk = ca_address_sk
- and ca_gmt_offset = -5
- group by i_manufact_id),
- ws as (
- select
- i_manufact_id,sum(ws_ext_sales_price) total_sales
- from
- web_sales,
- date_dim,
- customer_address,
- item
- where
- i_manufact_id in (select
- i_manufact_id
-from
- item
-where i_category in ('Books'))
- and ws_item_sk = i_item_sk
- and ws_sold_date_sk = d_date_sk
- and d_year = 2001
- and d_moy = 3
- and ws_bill_addr_sk = ca_address_sk
- and ca_gmt_offset = -5
- group by i_manufact_id)
- select i_manufact_id ,sum(total_sales) total_sales
- from (select * from ss
- union all
- select * from cs
- union all
- select * from ws) tmp1
- group by i_manufact_id
- order by total_sales
-limit 100"""
- qt_ds_shape_33 '''
- explain shape plan
- with ss as (
- select
- /*+ leading(store_sales date_dim customer_address item) */
- i_manufact_id,sum(ss_ext_sales_price) total_sales
- from
- store_sales,
- date_dim,
- customer_address,
- item
- where
- i_manufact_id in (select
- i_manufact_id
-from
- item
-where i_category in ('Books'))
- and ss_item_sk = i_item_sk
- and ss_sold_date_sk = d_date_sk
- and d_year = 2001
- and d_moy = 3
- and ss_addr_sk = ca_address_sk
- and ca_gmt_offset = -5
- group by i_manufact_id),
- cs as (
- select
- /*+ leading(catalog_sales date_dim customer_address item) */
- i_manufact_id,sum(cs_ext_sales_price) total_sales
- from
- catalog_sales,
- date_dim,
- customer_address,
- item
- where
- i_manufact_id in (select
- i_manufact_id
-from
- item
-where i_category in ('Books'))
- and cs_item_sk = i_item_sk
- and cs_sold_date_sk = d_date_sk
- and d_year = 2001
- and d_moy = 3
- and cs_bill_addr_sk = ca_address_sk
- and ca_gmt_offset = -5
- group by i_manufact_id),
- ws as (
- select
- /*+ leading(web_sales date_dim customer_address item) */
- i_manufact_id,sum(ws_ext_sales_price) total_sales
- from
- web_sales,
- date_dim,
- customer_address,
- item
- where
- i_manufact_id in (select
- i_manufact_id
-from
- item
-where i_category in ('Books'))
- and ws_item_sk = i_item_sk
- and ws_sold_date_sk = d_date_sk
- and d_year = 2001
- and d_moy = 3
- and ws_bill_addr_sk = ca_address_sk
- and ca_gmt_offset = -5
- group by i_manufact_id)
- select i_manufact_id ,sum(total_sales) total_sales
- from (select * from ss
- union all
- select * from cs
- union all
- select * from ws) tmp1
- group by i_manufact_id
- order by total_sales
-limit 100
- '''
-}
diff --git a/regression-test/suites/nereids_hint_tpcds_p0/shape/query35.groovy
b/regression-test/suites/nereids_hint_tpcds_p0/shape/query35.groovy
deleted file mode 100644
index a5187db652b..00000000000
--- a/regression-test/suites/nereids_hint_tpcds_p0/shape/query35.groovy
+++ /dev/null
@@ -1,149 +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("query35") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=21G'
- sql 'set be_number_for_test=3'
- sql 'set parallel_fragment_exec_instance_num=8; '
- sql 'set parallel_pipeline_task_num=8; '
- sql 'set forbid_unknown_col_stats=true'
- sql 'set enable_nereids_timeout = false'
- sql 'set enable_runtime_filter_prune=false'
- sql 'set runtime_filter_type=8'
- sql 'set dump_nereids_memo=false'
- sql "set disable_nereids_rules=PRUNE_EMPTY_PARTITION"
-
- def ds = """select
- ca_state,
- cd_gender,
- cd_marital_status,
- cd_dep_count,
- count(*) cnt1,
- avg(cd_dep_count),
- stddev_samp(cd_dep_count),
- sum(cd_dep_count),
- cd_dep_employed_count,
- count(*) cnt2,
- avg(cd_dep_employed_count),
- stddev_samp(cd_dep_employed_count),
- sum(cd_dep_employed_count),
- cd_dep_college_count,
- count(*) cnt3,
- avg(cd_dep_college_count),
- stddev_samp(cd_dep_college_count),
- sum(cd_dep_college_count)
- from
- customer c,customer_address ca,customer_demographics
- where
- c.c_current_addr_sk = ca.ca_address_sk and
- cd_demo_sk = c.c_current_cdemo_sk and
- exists (select *
- from store_sales,date_dim
- where c.c_customer_sk = ss_customer_sk and
- ss_sold_date_sk = d_date_sk and
- d_year = 1999 and
- d_qoy < 4) and
- (exists (select *
- from web_sales,date_dim
- where c.c_customer_sk = ws_bill_customer_sk and
- ws_sold_date_sk = d_date_sk and
- d_year = 1999 and
- d_qoy < 4) or
- exists (select *
- from catalog_sales,date_dim
- where c.c_customer_sk = cs_ship_customer_sk and
- cs_sold_date_sk = d_date_sk and
- d_year = 1999 and
- d_qoy < 4))
- group by ca_state,
- cd_gender,
- cd_marital_status,
- cd_dep_count,
- cd_dep_employed_count,
- cd_dep_college_count
- order by ca_state,
- cd_gender,
- cd_marital_status,
- cd_dep_count,
- cd_dep_employed_count,
- cd_dep_college_count
- limit 100"""
- qt_ds_shape_35 '''
- explain shape plan
- select
- ca_state,
- cd_gender,
- cd_marital_status,
- cd_dep_count,
- count(*) cnt1,
- avg(cd_dep_count),
- stddev_samp(cd_dep_count),
- sum(cd_dep_count),
- cd_dep_employed_count,
- count(*) cnt2,
- avg(cd_dep_employed_count),
- stddev_samp(cd_dep_employed_count),
- sum(cd_dep_employed_count),
- cd_dep_college_count,
- count(*) cnt3,
- avg(cd_dep_college_count),
- stddev_samp(cd_dep_college_count),
- sum(cd_dep_college_count)
- from
- customer c,customer_address ca,customer_demographics
- where
- c.c_current_addr_sk = ca.ca_address_sk and
- cd_demo_sk = c.c_current_cdemo_sk and
- exists (select *
- from store_sales,date_dim
- where c.c_customer_sk = ss_customer_sk and
- ss_sold_date_sk = d_date_sk and
- d_year = 1999 and
- d_qoy < 4) and
- (exists (select *
- from web_sales,date_dim
- where c.c_customer_sk = ws_bill_customer_sk and
- ws_sold_date_sk = d_date_sk and
- d_year = 1999 and
- d_qoy < 4) or
- exists (select *
- from catalog_sales,date_dim
- where c.c_customer_sk = cs_ship_customer_sk and
- cs_sold_date_sk = d_date_sk and
- d_year = 1999 and
- d_qoy < 4))
- group by ca_state,
- cd_gender,
- cd_marital_status,
- cd_dep_count,
- cd_dep_employed_count,
- cd_dep_college_count
- order by ca_state,
- cd_gender,
- cd_marital_status,
- cd_dep_count,
- cd_dep_employed_count,
- cd_dep_college_count
- limit 100
- '''
-}
diff --git a/regression-test/suites/nereids_hint_tpcds_p0/shape/query83.groovy
b/regression-test/suites/nereids_hint_tpcds_p0/shape/query83.groovy
deleted file mode 100644
index ff60c602ac6..00000000000
--- a/regression-test/suites/nereids_hint_tpcds_p0/shape/query83.groovy
+++ /dev/null
@@ -1,169 +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("query83") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=21G'
- sql 'set be_number_for_test=3'
- sql 'set parallel_fragment_exec_instance_num=8; '
- sql 'set parallel_pipeline_task_num=8; '
- sql 'set forbid_unknown_col_stats=true'
- sql 'set enable_nereids_timeout = false'
- sql 'set enable_runtime_filter_prune=false'
- sql 'set runtime_filter_type=8'
- sql 'set dump_nereids_memo=false'
- sql "set disable_nereids_rules=PRUNE_EMPTY_PARTITION"
-
- def ds = """with sr_items as
- (select i_item_id item_id,
- sum(sr_return_quantity) sr_item_qty
- from store_returns,
- item,
- date_dim
- where sr_item_sk = i_item_sk
- and d_date in
- (select d_date
- from date_dim
- where d_week_seq in
- (select d_week_seq
- from date_dim
- where d_date in ('2001-07-13','2001-09-10','2001-11-16')))
- and sr_returned_date_sk = d_date_sk
- group by i_item_id),
- cr_items as
- (select i_item_id item_id,
- sum(cr_return_quantity) cr_item_qty
- from catalog_returns,
- item,
- date_dim
- where cr_item_sk = i_item_sk
- and d_date in
- (select d_date
- from date_dim
- where d_week_seq in
- (select d_week_seq
- from date_dim
- where d_date in ('2001-07-13','2001-09-10','2001-11-16')))
- and cr_returned_date_sk = d_date_sk
- group by i_item_id),
- wr_items as
- (select i_item_id item_id,
- sum(wr_return_quantity) wr_item_qty
- from web_returns,
- item,
- date_dim
- where wr_item_sk = i_item_sk
- and d_date in
- (select d_date
- from date_dim
- where d_week_seq in
- (select d_week_seq
- from date_dim
- where d_date in ('2001-07-13','2001-09-10','2001-11-16')))
- and wr_returned_date_sk = d_date_sk
- group by i_item_id)
- select sr_items.item_id
- ,sr_item_qty
- ,sr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 sr_dev
- ,cr_item_qty
- ,cr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 cr_dev
- ,wr_item_qty
- ,wr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 wr_dev
- ,(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 average
- from sr_items
- ,cr_items
- ,wr_items
- where sr_items.item_id=cr_items.item_id
- and sr_items.item_id=wr_items.item_id
- order by sr_items.item_id
- ,sr_item_qty
- limit 100"""
- qt_ds_shape_83 '''
- explain shape plan
- with sr_items as
- (select i_item_id item_id,
- sum(sr_return_quantity) sr_item_qty
- from store_returns,
- item,
- date_dim
- where sr_item_sk = i_item_sk
- and d_date in
- (select d_date
- from date_dim
- where d_week_seq in
- (select d_week_seq
- from date_dim
- where d_date in ('2001-07-13','2001-09-10','2001-11-16')))
- and sr_returned_date_sk = d_date_sk
- group by i_item_id),
- cr_items as
- (select i_item_id item_id,
- sum(cr_return_quantity) cr_item_qty
- from catalog_returns,
- item,
- date_dim
- where cr_item_sk = i_item_sk
- and d_date in
- (select d_date
- from date_dim
- where d_week_seq in
- (select d_week_seq
- from date_dim
- where d_date in ('2001-07-13','2001-09-10','2001-11-16')))
- and cr_returned_date_sk = d_date_sk
- group by i_item_id),
- wr_items as
- (select i_item_id item_id,
- sum(wr_return_quantity) wr_item_qty
- from web_returns,
- item,
- date_dim
- where wr_item_sk = i_item_sk
- and d_date in
- (select d_date
- from date_dim
- where d_week_seq in
- (select d_week_seq
- from date_dim
- where d_date in ('2001-07-13','2001-09-10','2001-11-16')))
- and wr_returned_date_sk = d_date_sk
- group by i_item_id)
- select
- /*+ leading(sr_items cr_items wr_items) */
- sr_items.item_id
- ,sr_item_qty
- ,sr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 sr_dev
- ,cr_item_qty
- ,cr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 cr_dev
- ,wr_item_qty
- ,wr_item_qty/(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 * 100 wr_dev
- ,(sr_item_qty+cr_item_qty+wr_item_qty)/3.0 average
- from sr_items
- ,cr_items
- ,wr_items
- where sr_items.item_id=cr_items.item_id
- and sr_items.item_id=wr_items.item_id
- order by sr_items.item_id
- ,sr_item_qty
- limit 100
- '''
-}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
