This is an automated email from the ASF dual-hosted git repository.
kxiao pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.0 by this push:
new f2f8a6afcae [fix](Nereids) fix row count unconsistent when join
ordering (#24589) (#25666)
f2f8a6afcae is described below
commit f2f8a6afcae582fc0931efe9e5f61f271043b896
Author: 谢健 <[email protected]>
AuthorDate: Sun Oct 22 00:45:52 2023 +0800
[fix](Nereids) fix row count unconsistent when join ordering (#24589)
(#25666)
In the context of reorder join, when a new plan is generated, it may
include a project operation. In this case, the newly generated join root and
the original join root will no longer be in the same group. To avoid
inconsistencies in the statistics between these two groups, we keep the child
group's row count unchanged when the parent group expression is a project
operation.
---
.../nereids/jobs/cascades/DeriveStatsJob.java | 12 +++
.../nereids_tpcds_shape_sf100_p0/shape/query18.out | 14 ++--
.../nereids_tpcds_shape_sf100_p0/shape/query59.out | 35 +++++----
.../nereids_tpcds_shape_sf100_p0/shape/query61.out | 36 +++++----
.../nereids_tpcds_shape_sf100_p0/shape/query71.out | 73 +++++++++---------
.../nereids_tpcds_shape_sf100_p0/shape/query72.out | 48 +++++++++++-
.../nereids_tpcds_shape_sf100_p0/shape/query99.out | 8 +-
.../data/nereids_tpch_shape_sf1000_p0/shape/q8.out | 49 ++++++------
.../nereids_tpcds_shape_sf100_p0/rf/ds_rf18.groovy | 2 +-
.../nereids_tpcds_shape_sf100_p0/rf/ds_rf59.groovy | 83 +++++++++++----------
.../nereids_tpcds_shape_sf100_p0/rf/ds_rf61.groovy | 86 +++++++++++-----------
.../nereids_tpcds_shape_sf100_p0/rf/ds_rf72.groovy | 2 +-
.../nereids_tpcds_shape_sf100_p0/rf/ds_rf99.groovy | 2 +-
.../nereids_tpch_shape_sf1000_p0/rf/h_rf8.groovy | 2 +-
14 files changed, 261 insertions(+), 191 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/cascades/DeriveStatsJob.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/cascades/DeriveStatsJob.java
index cfe952c0f26..ec65b9af14d 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/cascades/DeriveStatsJob.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/cascades/DeriveStatsJob.java
@@ -28,6 +28,7 @@ import org.apache.doris.nereids.metrics.consumer.LogConsumer;
import org.apache.doris.nereids.metrics.event.StatsStateEvent;
import org.apache.doris.nereids.stats.StatsCalculator;
import org.apache.doris.nereids.trees.expressions.CTEId;
+import org.apache.doris.nereids.trees.plans.algebra.Project;
import org.apache.doris.qe.ConnectContext;
import org.apache.doris.statistics.Statistics;
@@ -116,6 +117,17 @@ public class DeriveStatsJob extends Job {
context.getCascadesContext().getConnectContext().getTotalHistogramMap()
.putAll(statsCalculator.getTotalHistogramMap());
}
+
+ if (groupExpression.getPlan() instanceof Project) {
+ // In the context of reorder join, when a new plan is
generated, it may include a project operation.
+ // In this case, the newly generated join root and the
original join root will no longer be in the
+ // same group. To avoid inconsistencies in the statistics
between these two groups, we keep the
+ // child group's row count unchanged when the parent group
expression is a project operation.
+ double parentRowCount =
groupExpression.getOwnerGroup().getStatistics().getRowCount();
+ groupExpression.children().forEach(g -> g.setStatistics(
+
g.getStatistics().withRowCountAndEnforceValid(parentRowCount))
+ );
+ }
}
}
}
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query18.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query18.out
index f7e24486fd6..2ce64b0b73c 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query18.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query18.out
@@ -10,15 +10,15 @@ PhysicalResultSink
--------------hashAgg[LOCAL]
----------------PhysicalRepeat
------------------PhysicalProject
---------------------hashJoin[INNER_JOIN](customer.c_current_cdemo_sk =
cd2.cd_demo_sk)
+--------------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk =
item.i_item_sk)
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[item]
----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------PhysicalOlapScan[customer_demographics]
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk =
item.i_item_sk)
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[item]
+--------------------------hashJoin[INNER_JOIN](customer.c_current_cdemo_sk =
cd2.cd_demo_sk)
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[customer_demographics]
----------------------------PhysicalDistribute
------------------------------PhysicalProject
--------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk
= date_dim.d_date_sk)
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query59.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query59.out
index 5b0559251ee..35bb32094a4 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query59.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query59.out
@@ -22,28 +22,31 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------------PhysicalDistribute
----------------PhysicalProject
------------------hashJoin[INNER_JOIN](wss.ss_store_sk = store.s_store_sk)
---------------------hashJoin[INNER_JOIN](d.d_week_seq = d_week_seq2)
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------filter((d.d_month_seq <= 1219)(d.d_month_seq >=
1208))
-----------------------------PhysicalOlapScan[date_dim]
+--------------------PhysicalDistribute
+----------------------hashJoin[INNER_JOIN](d.d_week_seq = d_week_seq1)
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter((d.d_month_seq <= 1207)(d.d_month_seq >=
1196))
+------------------------------PhysicalOlapScan[date_dim]
--------------------PhysicalDistribute
----------------------PhysicalProject
------------------------PhysicalOlapScan[store]
--------------PhysicalDistribute
----------------PhysicalProject
------------------hashJoin[INNER_JOIN](wss.ss_store_sk = store.s_store_sk)
---------------------hashJoin[INNER_JOIN](d.d_week_seq = d_week_seq1)
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------filter((d.d_month_seq <= 1207)(d.d_month_seq >=
1196))
-----------------------------PhysicalOlapScan[date_dim]
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN](d.d_week_seq = d_week_seq2)
+--------------------------PhysicalDistribute
+----------------------------PhysicalProject
+------------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
+--------------------------PhysicalDistribute
+----------------------------PhysicalProject
+------------------------------filter((d.d_month_seq <= 1219)(d.d_month_seq >=
1208))
+--------------------------------PhysicalOlapScan[date_dim]
--------------------PhysicalDistribute
----------------------PhysicalProject
------------------------PhysicalOlapScan[store]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query61.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query61.out
index bc1155555ed..878e6612558 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query61.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query61.out
@@ -20,21 +20,19 @@ PhysicalResultSink
----------------------------PhysicalOlapScan[item]
------------------------PhysicalDistribute
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN](store_sales.ss_customer_sk =
customer.c_customer_sk)
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[customer]
-------------------------------PhysicalDistribute
---------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN](store_sales.ss_promo_sk
= promotion.p_promo_sk)
-------------------------------------PhysicalProject
---------------------------------------filter((((cast(p_channel_dmail as
VARCHAR(*)) = 'Y') OR (cast(p_channel_email as VARCHAR(*)) = 'Y')) OR
(cast(p_channel_tv as VARCHAR(*)) = 'Y')))
-----------------------------------------PhysicalOlapScan[promotion]
-------------------------------------PhysicalDistribute
---------------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk
= date_dim.d_date_sk)
-----------------------------------------PhysicalProject
-------------------------------------------filter((date_dim.d_moy =
11)(date_dim.d_year = 1999))
---------------------------------------------PhysicalOlapScan[date_dim]
-----------------------------------------PhysicalDistribute
+----------------------------filter((customer_address.ca_gmt_offset = -7.00))
+------------------------------PhysicalOlapScan[customer_address]
+--------------------------PhysicalDistribute
+----------------------------PhysicalProject
+------------------------------hashJoin[INNER_JOIN](store_sales.ss_customer_sk
= customer.c_customer_sk)
+--------------------------------PhysicalDistribute
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[customer]
+--------------------------------PhysicalDistribute
+----------------------------------PhysicalProject
+------------------------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk
= date_dim.d_date_sk)
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN](store_sales.ss_promo_sk
= promotion.p_promo_sk)
------------------------------------------PhysicalProject
--------------------------------------------hashJoin[INNER_JOIN](store_sales.ss_store_sk
= store.s_store_sk)
----------------------------------------------PhysicalProject
@@ -43,6 +41,14 @@ PhysicalResultSink
------------------------------------------------PhysicalProject
--------------------------------------------------filter((store.s_gmt_offset =
-7.00))
----------------------------------------------------PhysicalOlapScan[store]
+------------------------------------------PhysicalDistribute
+--------------------------------------------PhysicalProject
+----------------------------------------------filter((((cast(p_channel_dmail
as VARCHAR(*)) = 'Y') OR (cast(p_channel_email as VARCHAR(*)) = 'Y')) OR
(cast(p_channel_tv as VARCHAR(*)) = 'Y')))
+------------------------------------------------PhysicalOlapScan[promotion]
+--------------------------------------PhysicalDistribute
+----------------------------------------PhysicalProject
+------------------------------------------filter((date_dim.d_moy =
11)(date_dim.d_year = 1999))
+--------------------------------------------PhysicalOlapScan[date_dim]
----------PhysicalDistribute
------------hashAgg[GLOBAL]
--------------PhysicalDistribute
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query71.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query71.out
index 94a0b5ca2ea..512604bb57e 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query71.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query71.out
@@ -4,48 +4,49 @@ PhysicalResultSink
--PhysicalQuickSort
----PhysicalDistribute
------PhysicalQuickSort
---------PhysicalProject
-----------hashAgg[GLOBAL]
-------------PhysicalDistribute
---------------hashAgg[LOCAL]
-----------------PhysicalProject
-------------------hashJoin[INNER_JOIN](tmp.time_sk = time_dim.t_time_sk)
---------------------PhysicalDistribute
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN](tmp.time_sk = time_dim.t_time_sk)
+------------------PhysicalDistribute
+--------------------PhysicalProject
----------------------hashJoin[INNER_JOIN](tmp.sold_item_sk = item.i_item_sk)
-------------------------PhysicalUnion
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN](date_dim.d_date_sk =
web_sales.ws_sold_date_sk)
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[web_sales]
---------------------------------PhysicalDistribute
+------------------------PhysicalDistribute
+--------------------------PhysicalUnion
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN](date_dim.d_date_sk =
web_sales.ws_sold_date_sk)
----------------------------------PhysicalProject
-------------------------------------filter((date_dim.d_moy =
12)(date_dim.d_year = 1998))
---------------------------------------PhysicalOlapScan[date_dim]
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN](date_dim.d_date_sk =
catalog_sales.cs_sold_date_sk)
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[catalog_sales]
---------------------------------PhysicalDistribute
+------------------------------------PhysicalOlapScan[web_sales]
+----------------------------------PhysicalDistribute
+------------------------------------PhysicalProject
+--------------------------------------filter((date_dim.d_moy =
12)(date_dim.d_year = 1998))
+----------------------------------------PhysicalOlapScan[date_dim]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN](date_dim.d_date_sk =
catalog_sales.cs_sold_date_sk)
----------------------------------PhysicalProject
-------------------------------------filter((date_dim.d_moy =
12)(date_dim.d_year = 1998))
---------------------------------------PhysicalOlapScan[date_dim]
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------hashJoin[INNER_JOIN](date_dim.d_date_sk =
store_sales.ss_sold_date_sk)
---------------------------------PhysicalProject
-----------------------------------PhysicalOlapScan[store_sales]
---------------------------------PhysicalDistribute
+------------------------------------PhysicalOlapScan[catalog_sales]
+----------------------------------PhysicalDistribute
+------------------------------------PhysicalProject
+--------------------------------------filter((date_dim.d_moy =
12)(date_dim.d_year = 1998))
+----------------------------------------PhysicalOlapScan[date_dim]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN](date_dim.d_date_sk =
store_sales.ss_sold_date_sk)
----------------------------------PhysicalProject
-------------------------------------filter((date_dim.d_moy =
12)(date_dim.d_year = 1998))
---------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------PhysicalOlapScan[store_sales]
+----------------------------------PhysicalDistribute
+------------------------------------PhysicalProject
+--------------------------------------filter((date_dim.d_moy =
12)(date_dim.d_year = 1998))
+----------------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalDistribute
--------------------------PhysicalProject
----------------------------filter((item.i_manager_id = 1))
------------------------------PhysicalOlapScan[item]
---------------------PhysicalDistribute
-----------------------PhysicalProject
-------------------------filter(t_meal_time IN ('breakfast', 'dinner'))
---------------------------PhysicalOlapScan[time_dim]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------filter(((cast(t_meal_time as VARCHAR(*)) = 'breakfast')
OR (cast(t_meal_time as VARCHAR(*)) = 'dinner')))
+------------------------PhysicalOlapScan[time_dim]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query72.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query72.out
index b9d611db2d8..08abc4069e3 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query72.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query72.out
@@ -13,7 +13,53 @@ PhysicalResultSink
--------------------PhysicalOlapScan[catalog_returns]
------------------PhysicalDistribute
--------------------PhysicalProject
-----------------------hashJoin[LEFT_OUTER_JOIN](catalog_sales.cs_promo_sk =
promotion.p_promo_sk)
+----------------------hashJoin[INNER_JOIN](warehouse.w_warehouse_sk =
inventory.inv_warehouse_sk)
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](catalog_sales.cs_item_sk =
inventory.inv_item_sk)(inventory.inv_date_sk =
d2.d_date_sk)(inventory.inv_quantity_on_hand < catalog_sales.cs_quantity)
+----------------------------PhysicalDistribute
+------------------------------PhysicalOlapScan[inventory]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN](d1.d_week_seq =
d2.d_week_seq)
+----------------------------------PhysicalDistribute
+------------------------------------PhysicalProject
+--------------------------------------hashJoin[LEFT_OUTER_JOIN](catalog_sales.cs_promo_sk
= promotion.p_promo_sk)
+----------------------------------------PhysicalProject
+------------------------------------------hashJoin[INNER_JOIN](item.i_item_sk
= catalog_sales.cs_item_sk)
+--------------------------------------------PhysicalDistribute
+----------------------------------------------PhysicalProject
+------------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_ship_date_sk
= d3.d_date_sk)(d3.d_date > cast((cast(d_date as BIGINT) + 5) as DATEV2))
+--------------------------------------------------PhysicalDistribute
+----------------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_bill_cdemo_sk
= customer_demographics.cd_demo_sk)
+------------------------------------------------------PhysicalDistribute
+--------------------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_sold_date_sk
= d1.d_date_sk)
+----------------------------------------------------------hashJoin[INNER_JOIN](catalog_sales.cs_bill_hdemo_sk
= household_demographics.hd_demo_sk)
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[catalog_sales]
+------------------------------------------------------------PhysicalDistribute
+--------------------------------------------------------------PhysicalProject
+----------------------------------------------------------------filter((cast(hd_buy_potential
as VARCHAR(*)) = '501-1000'))
+------------------------------------------------------------------PhysicalOlapScan[household_demographics]
+----------------------------------------------------------PhysicalDistribute
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------filter((d1.d_year
= 2002))
+----------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------------PhysicalDistribute
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------filter((cast(cd_marital_status
as VARCHAR(*)) = 'W'))
+------------------------------------------------------------PhysicalOlapScan[customer_demographics]
+--------------------------------------------------PhysicalDistribute
+----------------------------------------------------PhysicalProject
+------------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------------PhysicalDistribute
+----------------------------------------------PhysicalProject
+------------------------------------------------PhysicalOlapScan[item]
+----------------------------------------PhysicalDistribute
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[promotion]
+----------------------------------PhysicalDistribute
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalDistribute
--------------------------PhysicalProject
----------------------------hashJoin[INNER_JOIN](warehouse.w_warehouse_sk =
inventory.inv_warehouse_sk)
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query99.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query99.out
index 4f20f7bf82a..919fdb60190 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query99.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query99.out
@@ -8,10 +8,10 @@ PhysicalResultSink
----------PhysicalDistribute
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN](catalog_sales.cs_warehouse_sk =
warehouse.w_warehouse_sk)
+----------------hashJoin[INNER_JOIN](catalog_sales.cs_call_center_sk =
call_center.cc_call_center_sk)
------------------PhysicalProject
--------------------hashJoin[INNER_JOIN](catalog_sales.cs_ship_mode_sk =
ship_mode.sm_ship_mode_sk)
-----------------------hashJoin[INNER_JOIN](catalog_sales.cs_call_center_sk =
call_center.cc_call_center_sk)
+----------------------hashJoin[INNER_JOIN](catalog_sales.cs_warehouse_sk =
warehouse.w_warehouse_sk)
------------------------hashJoin[INNER_JOIN](catalog_sales.cs_ship_date_sk =
date_dim.d_date_sk)
--------------------------PhysicalProject
----------------------------PhysicalOlapScan[catalog_sales]
@@ -21,11 +21,11 @@ PhysicalResultSink
--------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalDistribute
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[call_center]
+----------------------------PhysicalOlapScan[warehouse]
----------------------PhysicalDistribute
------------------------PhysicalProject
--------------------------PhysicalOlapScan[ship_mode]
------------------PhysicalDistribute
--------------------PhysicalProject
-----------------------PhysicalOlapScan[warehouse]
+----------------------PhysicalOlapScan[call_center]
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q8.out
b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q8.out
index 45c0360124e..e699fc97c2b 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q8.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q8.out
@@ -16,33 +16,36 @@ PhysicalResultSink
--------------------------PhysicalOlapScan[supplier]
------------------------PhysicalDistribute
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN](n1.n_regionkey =
region.r_regionkey)
-------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN](customer.c_nationkey =
n1.n_nationkey)
-----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](orders.o_custkey =
customer.c_custkey)
+----------------------------hashJoin[INNER_JOIN](lineitem.l_orderkey =
orders.o_orderkey)
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN](orders.o_custkey =
customer.c_custkey)
+------------------------------------PhysicalDistribute
--------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[customer]
---------------------------------------PhysicalDistribute
-----------------------------------------hashJoin[INNER_JOIN](lineitem.l_orderkey
= orders.o_orderkey)
-------------------------------------------PhysicalProject
---------------------------------------------filter((orders.o_orderdate <=
'1996-12-31')(orders.o_orderdate >= '1995-01-01'))
-----------------------------------------------PhysicalOlapScan[orders]
-------------------------------------------PhysicalProject
---------------------------------------------hashJoin[INNER_JOIN](part.p_partkey
= lineitem.l_partkey)
+----------------------------------------filter((orders.o_orderdate <=
1996-12-31)(orders.o_orderdate >= 1995-01-01))
+------------------------------------------PhysicalOlapScan[orders]
+------------------------------------PhysicalDistribute
+--------------------------------------hashJoin[INNER_JOIN](customer.c_nationkey
= n1.n_nationkey)
+----------------------------------------PhysicalProject
+------------------------------------------PhysicalOlapScan[customer]
+----------------------------------------PhysicalDistribute
+------------------------------------------hashJoin[INNER_JOIN](n1.n_regionkey
= region.r_regionkey)
+--------------------------------------------PhysicalDistribute
----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[lineitem]
-----------------------------------------------PhysicalDistribute
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((part.p_type =
'ECONOMY ANODIZED STEEL'))
-----------------------------------------------------PhysicalOlapScan[part]
-----------------------------------PhysicalDistribute
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[nation]
+------------------------------------------------PhysicalOlapScan[nation]
+--------------------------------------------PhysicalDistribute
+----------------------------------------------PhysicalProject
+------------------------------------------------filter((region.r_name =
'AMERICA'))
+--------------------------------------------------PhysicalOlapScan[region]
------------------------------PhysicalDistribute
--------------------------------PhysicalProject
-----------------------------------filter((region.r_name = 'AMERICA'))
-------------------------------------PhysicalOlapScan[region]
+----------------------------------hashJoin[INNER_JOIN](part.p_partkey =
lineitem.l_partkey)
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[lineitem]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------filter((part.p_type = 'ECONOMY
ANODIZED STEEL'))
+------------------------------------------PhysicalOlapScan[part]
--------------------PhysicalDistribute
----------------------PhysicalProject
------------------------PhysicalOlapScan[nation]
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf18.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf18.groovy
index fd59182294c..7b6576ec21f 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf18.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf18.groovy
@@ -84,5 +84,5 @@ suite("ds_rf18") {
// File file = new File(outFile)
// file.write(getRuntimeFilters(plan))
-
assertEquals("RF5[c_current_cdemo_sk->[cd_demo_sk],RF4[cs_item_sk->[i_item_sk],RF3[ca_address_sk->[c_current_addr_sk],RF2[c_customer_sk->[cs_bill_customer_sk],RF1[d_date_sk->[cs_sold_date_sk],RF0[cd_demo_sk->[cs_bill_cdemo_sk]",
getRuntimeFilters(plan))
+
assertEquals("RF5[cs_item_sk->[i_item_sk],RF4[c_current_cdemo_sk->[cd_demo_sk],RF3[ca_address_sk->[c_current_addr_sk],RF2[c_customer_sk->[cs_bill_customer_sk],RF1[d_date_sk->[cs_sold_date_sk],RF0[cd_demo_sk->[cs_bill_cdemo_sk]",
getRuntimeFilters(plan))
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf59.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf59.groovy
index f6d114d4780..22a9561c3fc 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf59.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf59.groovy
@@ -32,47 +32,47 @@ suite("ds_rf59") {
sql 'set enable_pipeline_engine=true'
String stmt = '''
explain physical plan
- with wss as
- (select d_week_seq,
- ss_store_sk,
- sum(case when (d_day_name='Sunday') then ss_sales_price else null end)
sun_sales,
- sum(case when (d_day_name='Monday') then ss_sales_price else null end)
mon_sales,
- sum(case when (d_day_name='Tuesday') then ss_sales_price else null
end) tue_sales,
- sum(case when (d_day_name='Wednesday') then ss_sales_price else null
end) wed_sales,
- sum(case when (d_day_name='Thursday') then ss_sales_price else null
end) thu_sales,
- sum(case when (d_day_name='Friday') then ss_sales_price else null end)
fri_sales,
- sum(case when (d_day_name='Saturday') then ss_sales_price else null
end) sat_sales
- from store_sales,date_dim
- where d_date_sk = ss_sold_date_sk
- group by d_week_seq,ss_store_sk
- )
- select s_store_name1,s_store_id1,d_week_seq1
- ,sun_sales1/sun_sales2,mon_sales1/mon_sales2
- ,tue_sales1/tue_sales2,wed_sales1/wed_sales2,thu_sales1/thu_sales2
- ,fri_sales1/fri_sales2,sat_sales1/sat_sales2
- from
- (select s_store_name s_store_name1,wss.d_week_seq d_week_seq1
- ,s_store_id s_store_id1,sun_sales sun_sales1
- ,mon_sales mon_sales1,tue_sales tue_sales1
- ,wed_sales wed_sales1,thu_sales thu_sales1
- ,fri_sales fri_sales1,sat_sales sat_sales1
- from wss,store,date_dim d
- where d.d_week_seq = wss.d_week_seq and
- ss_store_sk = s_store_sk and
- d_month_seq between 1196 and 1196 + 11) y,
- (select s_store_name s_store_name2,wss.d_week_seq d_week_seq2
- ,s_store_id s_store_id2,sun_sales sun_sales2
- ,mon_sales mon_sales2,tue_sales tue_sales2
- ,wed_sales wed_sales2,thu_sales thu_sales2
- ,fri_sales fri_sales2,sat_sales sat_sales2
- from wss,store,date_dim d
- where d.d_week_seq = wss.d_week_seq and
- ss_store_sk = s_store_sk and
- d_month_seq between 1196+ 12 and 1196 + 23) x
- where s_store_id1=s_store_id2
- and d_week_seq1=d_week_seq2-52
- order by s_store_name1,s_store_id1,d_week_seq1
-limit 100;
+ with wss as
+ (select d_week_seq,
+ ss_store_sk,
+ sum(case when (d_day_name='Sunday') then ss_sales_price else null end)
sun_sales,
+ sum(case when (d_day_name='Monday') then ss_sales_price else null end)
mon_sales,
+ sum(case when (d_day_name='Tuesday') then ss_sales_price else null
end) tue_sales,
+ sum(case when (d_day_name='Wednesday') then ss_sales_price else null
end) wed_sales,
+ sum(case when (d_day_name='Thursday') then ss_sales_price else null
end) thu_sales,
+ sum(case when (d_day_name='Friday') then ss_sales_price else null end)
fri_sales,
+ sum(case when (d_day_name='Saturday') then ss_sales_price else null
end) sat_sales
+ from store_sales,date_dim
+ where d_date_sk = ss_sold_date_sk
+ group by d_week_seq,ss_store_sk
+ )
+ select s_store_name1,s_store_id1,d_week_seq1
+ ,sun_sales1/sun_sales2,mon_sales1/mon_sales2
+ ,tue_sales1/tue_sales2,wed_sales1/wed_sales2,thu_sales1/thu_sales2
+ ,fri_sales1/fri_sales2,sat_sales1/sat_sales2
+ from
+ (select s_store_name s_store_name1,wss.d_week_seq d_week_seq1
+ ,s_store_id s_store_id1,sun_sales sun_sales1
+ ,mon_sales mon_sales1,tue_sales tue_sales1
+ ,wed_sales wed_sales1,thu_sales thu_sales1
+ ,fri_sales fri_sales1,sat_sales sat_sales1
+ from wss,store,date_dim d
+ where d.d_week_seq = wss.d_week_seq and
+ ss_store_sk = s_store_sk and
+ d_month_seq between 1196 and 1196 + 11) y,
+ (select s_store_name s_store_name2,wss.d_week_seq d_week_seq2
+ ,s_store_id s_store_id2,sun_sales sun_sales2
+ ,mon_sales mon_sales2,tue_sales tue_sales2
+ ,wed_sales wed_sales2,thu_sales thu_sales2
+ ,fri_sales fri_sales2,sat_sales sat_sales2
+ from wss,store,date_dim d
+ where d.d_week_seq = wss.d_week_seq and
+ ss_store_sk = s_store_sk and
+ d_month_seq between 1196+ 12 and 1196 + 23) x
+ where s_store_id1=s_store_id2
+ and d_week_seq1=d_week_seq2-52
+ order by s_store_name1,s_store_id1,d_week_seq1
+limit 100;
'''
String plan = sql "${stmt}"
@@ -93,6 +93,5 @@ limit 100;
// def outFile =
"regression-test/suites/nereids_tpcds_shape_sf100_p0/ddl/rf/rf.59"
// File file = new File(outFile)
// file.write(getRuntimeFilters(plan))
-
assertEquals("RF0[d_date_sk->[ss_sold_date_sk],RF4[s_store_sk->[ss_store_sk],RF3[d_week_seq->[d_week_seq],RF2[s_store_sk->[ss_store_sk],RF1[d_week_seq->[d_week_seq]",
getRuntimeFilters(plan))
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf61.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf61.groovy
index 600c6b40142..656f3796149 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf61.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf61.groovy
@@ -32,47 +32,47 @@ suite("ds_rf61") {
sql 'set enable_pipeline_engine=true'
String stmt = '''
explain physical plan
- select promotions,total,cast(promotions as decimal(15,4))/cast(total as
decimal(15,4))*100
-from
- (select sum(ss_ext_sales_price) promotions
- from store_sales
- ,store
- ,promotion
- ,date_dim
- ,customer
- ,customer_address
- ,item
- where ss_sold_date_sk = d_date_sk
- and ss_store_sk = s_store_sk
- and ss_promo_sk = p_promo_sk
- and ss_customer_sk= c_customer_sk
- and ca_address_sk = c_current_addr_sk
- and ss_item_sk = i_item_sk
- and ca_gmt_offset = -7
- and i_category = 'Jewelry'
- and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
- and s_gmt_offset = -7
- and d_year = 1999
- and d_moy = 11) promotional_sales,
- (select sum(ss_ext_sales_price) total
- from store_sales
- ,store
- ,date_dim
- ,customer
- ,customer_address
- ,item
- where ss_sold_date_sk = d_date_sk
- and ss_store_sk = s_store_sk
- and ss_customer_sk= c_customer_sk
- and ca_address_sk = c_current_addr_sk
- and ss_item_sk = i_item_sk
- and ca_gmt_offset = -7
- and i_category = 'Jewelry'
- and s_gmt_offset = -7
- and d_year = 1999
- and d_moy = 11) all_sales
-order by promotions, total
-limit 100;
+ select promotions,total,cast(promotions as decimal(15,4))/cast(total as
decimal(15,4))*100
+from
+ (select sum(ss_ext_sales_price) promotions
+ from store_sales
+ ,store
+ ,promotion
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_promo_sk = p_promo_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Jewelry'
+ and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) promotional_sales,
+ (select sum(ss_ext_sales_price) total
+ from store_sales
+ ,store
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Jewelry'
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) all_sales
+order by promotions, total
+limit 100;
'''
String plan = sql "${stmt}"
@@ -93,6 +93,6 @@ limit 100;
// def outFile =
"regression-test/suites/nereids_tpcds_shape_sf100_p0/ddl/rf/rf.61"
// File file = new File(outFile)
// file.write(getRuntimeFilters(plan))
-
-
assertEquals("RF10[c_current_addr_sk->[ca_address_sk],RF9[ss_customer_sk->[c_customer_sk],RF8[ss_item_sk->[i_item_sk],RF7[p_promo_sk->[ss_promo_sk],RF6[d_date_sk->[ss_sold_date_sk],RF5[s_store_sk->[ss_store_sk],RF4[c_current_addr_sk->[ca_address_sk],RF3[ss_customer_sk->[c_customer_sk],RF2[ss_item_sk->[i_item_sk],RF1[d_date_sk->[ss_sold_date_sk],RF0[s_store_sk->[ss_store_sk]",
getRuntimeFilters(plan))
+
+
assertEquals("RF10[ss_item_sk->[i_item_sk],RF9[c_current_addr_sk->[ca_address_sk],RF8[ss_customer_sk->[c_customer_sk],RF7[d_date_sk->[ss_sold_date_sk],RF6[p_promo_sk->[ss_promo_sk],RF5[s_store_sk->[ss_store_sk],RF4[ss_item_sk->[i_item_sk],RF3[c_current_addr_sk->[ca_address_sk],RF2[ss_customer_sk->[c_customer_sk],RF1[d_date_sk->[ss_sold_date_sk],RF0[s_store_sk->[ss_store_sk]",
getRuntimeFilters(plan))
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf72.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf72.groovy
index e766cb12fce..2ce7b3f61e1 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf72.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf72.groovy
@@ -79,5 +79,5 @@ limit 100;
// File file = new File(outFile)
// file.write(getRuntimeFilters(plan))
-
assertEquals("RF10[w_warehouse_sk->[inv_warehouse_sk],RF8[d_date_sk->[inv_date_sk],RF9[cs_item_sk->[inv_item_sk],RF7[d_week_seq->[d_week_seq],RF5[cs_item_sk->[cr_item_sk],RF6[cs_order_number->[cr_order_number],RF4[i_item_sk->[cs_item_sk],RF3[d_date_sk->[cs_ship_date_sk],RF2[cd_demo_sk->[cs_bill_cdemo_sk],RF1[d_date_sk->[cs_sold_date_sk],RF0[hd_demo_sk->[cs_bill_hdemo_sk]",
getRuntimeFilters(plan))
+
assertEquals("RF8[w_warehouse_sk->[inv_warehouse_sk],RF6[d_date_sk->[inv_date_sk],RF7[cs_item_sk->[inv_item_sk],RF5[d_week_seq->[d_week_seq],RF4[i_item_sk->[cs_item_sk],RF3[d_date_sk->[cs_ship_date_sk],RF2[cd_demo_sk->[cs_bill_cdemo_sk],RF1[d_date_sk->[cs_sold_date_sk],RF0[hd_demo_sk->[cs_bill_hdemo_sk]",
getRuntimeFilters(plan))
}
diff --git
a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf99.groovy
b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf99.groovy
index 032b0dec3b3..cb54204e6f3 100644
--- a/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf99.groovy
+++ b/regression-test/suites/nereids_tpcds_shape_sf100_p0/rf/ds_rf99.groovy
@@ -85,5 +85,5 @@ limit 100;
// File file = new File(outFile)
// file.write(getRuntimeFilters(plan))
-
assertEquals("RF3[w_warehouse_sk->[cs_warehouse_sk],RF2[sm_ship_mode_sk->[cs_ship_mode_sk],RF1[cc_call_center_sk->[cs_call_center_sk],RF0[d_date_sk->[cs_ship_date_sk]",
getRuntimeFilters(plan))
+
assertEquals("RF3[cc_call_center_sk->[cs_call_center_sk],RF2[sm_ship_mode_sk->[cs_ship_mode_sk],RF1[w_warehouse_sk->[cs_warehouse_sk],RF0[d_date_sk->[cs_ship_date_sk]",
getRuntimeFilters(plan))
}
diff --git
a/regression-test/suites/nereids_tpch_shape_sf1000_p0/rf/h_rf8.groovy
b/regression-test/suites/nereids_tpch_shape_sf1000_p0/rf/h_rf8.groovy
index 2524227d46d..208a1385a12 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/rf/h_rf8.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/rf/h_rf8.groovy
@@ -104,5 +104,5 @@ order by
// def outFile =
"regression-test/suites/nereids_tpch_shape_sf1000_p0/ddl/rf/rf.8"
// File file = new File(outFile)
// file.write(getRuntimeFilters(plan))
-
assertEquals("RF6[n_nationkey->[s_nationkey],RF5[l_suppkey->[s_suppkey],RF4[r_regionkey->[n_regionkey],RF3[n_nationkey->[c_nationkey],RF2[o_custkey->[c_custkey],RF1[l_orderkey->[o_orderkey],RF0[p_partkey->[l_partkey]",
getRuntimeFilters(plan))
+
assertEquals("RF6[n_nationkey->[s_nationkey],RF5[l_suppkey->[s_suppkey],RF4[l_orderkey->[o_orderkey],RF3[c_custkey->[o_custkey],RF2[n_nationkey->[c_nationkey],RF1[r_regionkey->[n_regionkey],RF0[p_partkey->[l_partkey]",
getRuntimeFilters(plan))
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
