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 cb2ce1e8ccd [case](Nereids) add leading tpc-h (#30405)
cb2ce1e8ccd is described below
commit cb2ce1e8ccd02dc14ef496d7689954aa76312d62
Author: LiBinfeng <[email protected]>
AuthorDate: Fri Mar 8 08:26:34 2024 +0800
[case](Nereids) add leading tpc-h (#30405)
add tpc-h shape cases using leading hint
except:
single table without join q1 q6
not support feature include tables after subquery unnested q2 q16 q18 q20
q21 q22
---
.../org/apache/doris/nereids/hint/LeadingHint.java | 2 +-
.../data/nereids_hint_tpcds_p0/shape/query24.out | 4 +-
.../data/nereids_hint_tpcds_p0/shape/query72.out | 11 +-
.../data/nereids_hint_tpch_p0/shape/q1.out | 4 +-
.../data/nereids_hint_tpch_p0/shape/q10.out | 39 ++--
.../data/nereids_hint_tpch_p0/shape/q11.out | 64 +++---
.../data/nereids_hint_tpch_p0/shape/q12.out | 11 +-
.../data/nereids_hint_tpch_p0/shape/q13.out | 15 +-
.../data/nereids_hint_tpch_p0/shape/q14.out | 11 +-
.../data/nereids_hint_tpch_p0/shape/q15.out | 41 ++--
.../data/nereids_hint_tpch_p0/shape/q16.out | 26 ---
.../data/nereids_hint_tpch_p0/shape/q17.out | 17 +-
.../data/nereids_hint_tpch_p0/shape/q18.out | 26 ---
.../data/nereids_hint_tpch_p0/shape/q19.out | 16 +-
.../data/nereids_hint_tpch_p0/shape/q2.out | 39 ----
.../data/nereids_hint_tpch_p0/shape/q20.out | 34 ----
.../data/nereids_hint_tpch_p0/shape/q21.out | 34 ----
.../data/nereids_hint_tpch_p0/shape/q22.out | 28 ---
.../data/nereids_hint_tpch_p0/shape/q3.out | 38 ++--
.../data/nereids_hint_tpch_p0/shape/q4.out | 11 +-
.../data/nereids_hint_tpch_p0/shape/q5.out | 53 +++--
.../data/nereids_hint_tpch_p0/shape/q6.out | 2 +-
.../data/nereids_hint_tpch_p0/shape/q7.out | 65 +++---
.../data/nereids_hint_tpch_p0/shape/q8.out | 65 +++---
.../data/nereids_hint_tpch_p0/shape/q9.out | 53 ++---
.../data/nereids_p0/hint/test_leading.out | 84 ++++----
.../suites/nereids_hint_tpch_p0/load.groovy | 223 +++++++++++++++++++++
.../suites/nereids_hint_tpch_p0/shape/q10.groovy | 71 +++++++
.../suites/nereids_hint_tpch_p0/shape/q11.groovy | 67 +++++++
.../suites/nereids_hint_tpch_p0/shape/q12.groovy | 67 +++++++
.../suites/nereids_hint_tpch_p0/shape/q13.groovy | 56 ++++++
.../suites/nereids_hint_tpch_p0/shape/q14.groovy | 49 +++++
.../suites/nereids_hint_tpch_p0/shape/q15.groovy | 60 ++++++
.../suites/nereids_hint_tpch_p0/shape/q17.groovy | 58 ++++++
.../suites/nereids_hint_tpch_p0/shape/q19.groovy | 76 +++++++
.../suites/nereids_hint_tpch_p0/shape/q3.groovy | 66 ++++++
.../suites/nereids_hint_tpch_p0/shape/q4.groovy | 62 ++++++
.../suites/nereids_hint_tpch_p0/shape/q5.groovy | 59 ++++++
.../suites/nereids_hint_tpch_p0/shape/q7.groovy | 74 +++++++
.../suites/nereids_hint_tpch_p0/shape/q8.groovy | 78 +++++++
.../suites/nereids_hint_tpch_p0/shape/q9.groovy | 68 +++++++
41 files changed, 1462 insertions(+), 465 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/hint/LeadingHint.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/hint/LeadingHint.java
index 9087dd83e83..6cefb8c6088 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/hint/LeadingHint.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/hint/LeadingHint.java
@@ -529,7 +529,7 @@ public class LeadingHint extends Hint {
return null;
}
logicalPlan = makeFilterPlanIfExist(getFilters(), logicalPlan);
- stack.push(Pair.of(currentLevel, Pair.of(logicalPlan, index)));
+ stack.push(Pair.of(currentLevel, Pair.of(logicalPlan, index -
1)));
stackTopLevel = currentLevel;
}
}
diff --git a/regression-test/data/nereids_hint_tpcds_p0/shape/query24.out
b/regression-test/data/nereids_hint_tpcds_p0/shape/query24.out
index 01ffbef3010..92e60cfa818 100644
--- a/regression-test/data/nereids_hint_tpcds_p0/shape/query24.out
+++ b/regression-test/data/nereids_hint_tpcds_p0/shape/query24.out
@@ -59,7 +59,7 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------------------------PhysicalCteConsumer ( cteId=CTEId#0 )
Hint log:
-Used: leading(store_sales broadcast store { customer shuffle customer_address
} shuffle item shuffle store_returns )
-UnUsed: [shuffle]_3
+Used: leading(store_sales broadcast store shuffle { customer shuffle
customer_address } shuffle item shuffle store_returns )
+UnUsed:
SyntaxError:
diff --git a/regression-test/data/nereids_hint_tpcds_p0/shape/query72.out
b/regression-test/data/nereids_hint_tpcds_p0/shape/query72.out
index 0b68e3e7f8d..bdd5a5d4d17 100644
--- a/regression-test/data/nereids_hint_tpcds_p0/shape/query72.out
+++ b/regression-test/data/nereids_hint_tpcds_p0/shape/query72.out
@@ -34,9 +34,10 @@ PhysicalResultSink
----------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((catalog_sales.cs_bill_hdemo_sk =
household_demographics.hd_demo_sk)) otherCondition=() build RFs:RF2
hd_demo_sk->[cs_bill_hdemo_sk]
------------------------------------------------------PhysicalProject
--------------------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((catalog_sales.cs_ship_date_sk = d3.d_date_sk) and
(catalog_sales.cs_sold_date_sk = d1.d_date_sk)) otherCondition=() build RFs:RF0
d_date_sk->[cs_ship_date_sk];RF1 d_date_sk->[cs_sold_date_sk]
-----------------------------------------------------------PhysicalProject
-------------------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF0 RF1 RF2 RF3 RF4
-----------------------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------------------------PhysicalProject
+--------------------------------------------------------------PhysicalOlapScan[catalog_sales]
apply RFs: RF0 RF1 RF2 RF3 RF4
+----------------------------------------------------------PhysicalDistribute[DistributionSpecHash]
------------------------------------------------------------PhysicalProject
--------------------------------------------------------------NestedLoopJoin[INNER_JOIN](d3.d_date
> days_add(d_date, INTERVAL 5 DAY))
----------------------------------------------------------------PhysicalProject
@@ -67,7 +68,7 @@ PhysicalResultSink
----------------------PhysicalOlapScan[warehouse]
Hint log:
-Used: leading(inventory { catalog_returns shuffle { catalog_sales shuffle {
d3 broadcast d1 } broadcast household_demographics shuffle
customer_demographics broadcast promotion shuffle item } broadcast d2 }
broadcast warehouse )
-UnUsed: [shuffle]_2
+Used: leading(inventory shuffle { catalog_returns shuffle { catalog_sales
shuffle { d3 d1 } broadcast household_demographics shuffle
customer_demographics broadcast promotion shuffle item } broadcast d2 }
broadcast warehouse )
+UnUsed:
SyntaxError:
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q1.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q1.out
index 3eb68a3c453..22f0777694a 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q1.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q1.out
@@ -2,10 +2,10 @@
-- !select --
PhysicalResultSink
--PhysicalQuickSort[MERGE_SORT]
-----PhysicalDistribute
+----PhysicalDistribute[DistributionSpecGather]
------PhysicalQuickSort[LOCAL_SORT]
--------hashAgg[GLOBAL]
-----------PhysicalDistribute
+----------PhysicalDistribute[DistributionSpecHash]
------------hashAgg[LOCAL]
--------------PhysicalProject
----------------filter((lineitem.l_shipdate <= '1998-09-02'))
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q10.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q10.out
index 10f56f2b5c3..b46fa9784e5 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q10.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q10.out
@@ -2,33 +2,34 @@
-- !select --
PhysicalResultSink
--PhysicalTopN[MERGE_SORT]
-----PhysicalDistribute
+----PhysicalDistribute[DistributionSpecGather]
------PhysicalTopN[LOCAL_SORT]
---------hashAgg[GLOBAL]
-----------PhysicalDistribute
-------------hashAgg[LOCAL]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN] hashCondition=((lineitem.l_orderkey =
orders.o_orderkey)) otherCondition=() build RFs:RF2 o_orderkey->[l_orderkey]
-------------------PhysicalProject
---------------------filter((lineitem.l_returnflag = 'R'))
-----------------------PhysicalOlapScan[lineitem] apply RFs: RF2
-------------------PhysicalDistribute
+--------PhysicalProject
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute[DistributionSpecHash]
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN] hashCondition=((lineitem.l_orderkey =
orders.o_orderkey)) otherCondition=()
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_nationkey = nation.n_nationkey)) otherCondition=()
build RFs:RF1 n_nationkey->[c_nationkey]
-------------------------PhysicalDistribute
+----------------------filter((lineitem.l_returnflag = 'R'))
+------------------------PhysicalOlapScan[lineitem]
+--------------------PhysicalDistribute[DistributionSpecHash]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_nationkey = nation.n_nationkey)) otherCondition=()
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_custkey = orders.o_custkey)) otherCondition=() build
RFs:RF0 o_custkey->[c_custkey]
+----------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_custkey = orders.o_custkey)) otherCondition=()
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[customer] apply RFs: RF0 RF1
-------------------------------PhysicalDistribute
+--------------------------------PhysicalOlapScan[customer]
+------------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------------PhysicalProject
----------------------------------filter((orders.o_orderdate < '1994-01-01')
and (orders.o_orderdate >= '1993-10-01'))
------------------------------------PhysicalOlapScan[orders]
-------------------------PhysicalDistribute
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[nation]
+--------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[nation]
-Used: leading(lineitem { { customer orders } nation })
+Hint log:
+Used: leading(lineitem shuffle { { customer shuffle orders } broadcast
nation } )
UnUsed:
SyntaxError:
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q11.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q11.out
index eea366f8423..8a357ce7cea 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q11.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q11.out
@@ -1,46 +1,52 @@
-- This file is automatically generated. You should know what you did if you
want to edit this
-- !select --
PhysicalResultSink
---PhysicalQuickSort
-----PhysicalDistribute
-------PhysicalQuickSort
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
--------PhysicalProject
-----------NestedLoopJoin[INNER_JOIN](cast(value as DOUBLE) >
cast((sum((ps_supplycost * ps_availqty)) * 0.000002) as DOUBLE))
+----------NestedLoopJoin[INNER_JOIN](cast(value as DOUBLE) >
cast((sum((ps_supplycost * cast(ps_availqty as DECIMALV3(10, 0)))) * 0.000002)
as DOUBLE))
------------hashAgg[GLOBAL]
---------------PhysicalDistribute
+--------------PhysicalDistribute[DistributionSpecHash]
----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------hashJoin[INNER_JOIN](partsupp.ps_suppkey =
supplier.s_suppkey)
-----------------------PhysicalDistribute
+--------------------hashJoin[INNER_JOIN] hashCondition=((partsupp.ps_suppkey =
supplier.s_suppkey)) otherCondition=()
+----------------------PhysicalDistribute[DistributionSpecHash]
------------------------PhysicalProject
--------------------------PhysicalOlapScan[partsupp]
-----------------------PhysicalDistribute
-------------------------hashJoin[INNER_JOIN](supplier.s_nationkey =
nation.n_nationkey)
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[supplier]
---------------------------PhysicalDistribute
-----------------------------PhysicalProject
-------------------------------filter((nation.n_name = 'GERMANY'))
---------------------------------PhysicalOlapScan[nation]
-------------PhysicalDistribute
---------------PhysicalAssertNumRows
-----------------PhysicalProject
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute
-----------------------hashAgg[LOCAL]
+----------------------PhysicalDistribute[DistributionSpecHash]
------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN](partsupp.ps_suppkey =
supplier.s_suppkey)
-----------------------------PhysicalDistribute
+--------------------------hashJoin[INNER_JOIN]
hashCondition=((supplier.s_nationkey = nation.n_nationkey)) otherCondition=()
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[supplier]
+----------------------------PhysicalDistribute[DistributionSpecHash]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[partsupp]
-----------------------------PhysicalDistribute
-------------------------------hashJoin[INNER_JOIN](supplier.s_nationkey =
nation.n_nationkey)
---------------------------------PhysicalDistribute
+--------------------------------filter((nation.n_name = 'GERMANY'))
+----------------------------------PhysicalOlapScan[nation]
+------------PhysicalDistribute[DistributionSpecReplicated]
+--------------PhysicalProject
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecGather]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN]
hashCondition=((partsupp.ps_suppkey = supplier.s_suppkey)) otherCondition=()
+--------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[partsupp]
+--------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------PhysicalProject
+------------------------------hashJoin[INNER_JOIN]
hashCondition=((supplier.s_nationkey = nation.n_nationkey)) otherCondition=()
+--------------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------------PhysicalProject
------------------------------------PhysicalOlapScan[supplier]
---------------------------------PhysicalDistribute
+--------------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------------PhysicalProject
------------------------------------filter((nation.n_name = 'GERMANY'))
--------------------------------------PhysicalOlapScan[nation]
+Hint log:
+Used: leading(partsupp { supplier nation } ) leading(partsupp { supplier
nation } )
+UnUsed:
+SyntaxError:
+
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q12.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q12.out
index b98f7425954..5d71591ef7e 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q12.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q12.out
@@ -2,20 +2,21 @@
-- !select --
PhysicalResultSink
--PhysicalQuickSort[MERGE_SORT]
-----PhysicalDistribute
+----PhysicalDistribute[DistributionSpecGather]
------PhysicalQuickSort[LOCAL_SORT]
--------hashAgg[GLOBAL]
-----------PhysicalDistribute
+----------PhysicalDistribute[DistributionSpecHash]
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN] hashCondition=((orders.o_orderkey =
lineitem.l_orderkey)) otherCondition=() build RFs:RF0 l_orderkey->[o_orderkey]
+----------------hashJoin[INNER_JOIN] hashCondition=((orders.o_orderkey =
lineitem.l_orderkey)) otherCondition=()
------------------PhysicalProject
---------------------PhysicalOlapScan[orders] apply RFs: RF0
+--------------------PhysicalOlapScan[orders]
------------------PhysicalProject
--------------------filter((lineitem.l_commitdate < lineitem.l_receiptdate)
and (lineitem.l_receiptdate < '1995-01-01') and (lineitem.l_receiptdate >=
'1994-01-01') and (lineitem.l_shipdate < lineitem.l_commitdate) and l_shipmode
IN ('MAIL', 'SHIP'))
----------------------PhysicalOlapScan[lineitem]
-Used: leading(orders lineitem)
+Hint log:
+Used: leading(orders lineitem )
UnUsed:
SyntaxError:
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q13.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q13.out
index 9521bbf2e76..0ac0a4bc50e 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q13.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q13.out
@@ -2,24 +2,25 @@
-- !select --
PhysicalResultSink
--PhysicalQuickSort[MERGE_SORT]
-----PhysicalDistribute
+----PhysicalDistribute[DistributionSpecGather]
------PhysicalQuickSort[LOCAL_SORT]
--------hashAgg[GLOBAL]
-----------PhysicalDistribute
+----------PhysicalDistribute[DistributionSpecHash]
------------hashAgg[LOCAL]
--------------PhysicalProject
----------------hashAgg[LOCAL]
------------------PhysicalProject
---------------------hashJoin[RIGHT_OUTER_JOIN]
hashCondition=((customer.c_custkey = orders.o_custkey)) otherCondition=() build
RFs:RF0 c_custkey->[o_custkey]
-----------------------PhysicalDistribute
+--------------------hashJoin[RIGHT_OUTER_JOIN]
hashCondition=((customer.c_custkey = orders.o_custkey)) otherCondition=()
+----------------------PhysicalDistribute[DistributionSpecHash]
------------------------PhysicalProject
--------------------------filter(( not (o_comment like '%special%requests%')))
-----------------------------PhysicalOlapScan[orders] apply RFs: RF0
-----------------------PhysicalDistribute
+----------------------------PhysicalOlapScan[orders]
+----------------------PhysicalDistribute[DistributionSpecHash]
------------------------PhysicalProject
--------------------------PhysicalOlapScan[customer]
-Used: leading(orders customer)
+Hint log:
+Used: leading(orders shuffle customer )
UnUsed:
SyntaxError:
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q14.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q14.out
index 34e85bcda66..69e3197354f 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q14.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q14.out
@@ -3,18 +3,19 @@
PhysicalResultSink
--PhysicalProject
----hashAgg[GLOBAL]
-------PhysicalDistribute
+------PhysicalDistribute[DistributionSpecGather]
--------hashAgg[LOCAL]
----------PhysicalProject
-------------hashJoin[INNER_JOIN] hashCondition=((lineitem.l_partkey =
part.p_partkey)) otherCondition=() build RFs:RF0 l_partkey->[p_partkey]
+------------hashJoin[INNER_JOIN] hashCondition=((lineitem.l_partkey =
part.p_partkey)) otherCondition=()
--------------PhysicalProject
-----------------PhysicalOlapScan[part] apply RFs: RF0
---------------PhysicalDistribute
+----------------PhysicalOlapScan[part]
+--------------PhysicalDistribute[DistributionSpecHash]
----------------PhysicalProject
------------------filter((lineitem.l_shipdate < '1995-10-01') and
(lineitem.l_shipdate >= '1995-09-01'))
--------------------PhysicalOlapScan[lineitem]
-Used: leading(part lineitem)
+Hint log:
+Used: leading(part lineitem )
UnUsed:
SyntaxError:
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q15.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q15.out
index 4106594748a..87cf9364346 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q15.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q15.out
@@ -1,31 +1,36 @@
-- This file is automatically generated. You should know what you did if you
want to edit this
-- !select --
PhysicalResultSink
---PhysicalQuickSort
-----PhysicalDistribute
-------PhysicalQuickSort
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
--------PhysicalProject
-----------hashJoin[INNER_JOIN](supplier.s_suppkey = revenue0.supplier_no)
+----------hashJoin[INNER_JOIN] hashCondition=((supplier.s_suppkey =
revenue0.supplier_no)) otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[supplier]
-------------PhysicalDistribute
---------------hashJoin[INNER_JOIN](revenue0.total_revenue = max(total_revenue))
+------------PhysicalDistribute[DistributionSpecHash]
+--------------hashJoin[INNER_JOIN] hashCondition=((revenue0.total_revenue =
max(total_revenue))) otherCondition=()
----------------hashAgg[GLOBAL]
-------------------PhysicalDistribute
+------------------PhysicalDistribute[DistributionSpecGather]
--------------------hashAgg[LOCAL]
----------------------PhysicalProject
-------------------------filter((lineitem.l_shipdate >=
1996-01-01)(lineitem.l_shipdate < 1996-04-01))
---------------------------PhysicalOlapScan[lineitem]
-----------------PhysicalDistribute
-------------------PhysicalAssertNumRows
+------------------------hashAgg[GLOBAL]
+--------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------hashAgg[LOCAL]
+------------------------------PhysicalProject
+--------------------------------filter((lineitem.l_shipdate < '1996-04-01')
and (lineitem.l_shipdate >= '1996-01-01'))
+----------------------------------PhysicalOlapScan[lineitem]
+----------------PhysicalDistribute[DistributionSpecReplicated]
+------------------PhysicalProject
--------------------hashAgg[GLOBAL]
-----------------------PhysicalDistribute
+----------------------PhysicalDistribute[DistributionSpecHash]
------------------------hashAgg[LOCAL]
--------------------------PhysicalProject
-----------------------------hashAgg[GLOBAL]
-------------------------------PhysicalDistribute
---------------------------------hashAgg[LOCAL]
-----------------------------------PhysicalProject
-------------------------------------filter((lineitem.l_shipdate >=
1996-01-01)(lineitem.l_shipdate < 1996-04-01))
---------------------------------------PhysicalOlapScan[lineitem]
+----------------------------filter((lineitem.l_shipdate < '1996-04-01') and
(lineitem.l_shipdate >= '1996-01-01'))
+------------------------------PhysicalOlapScan[lineitem]
+
+Hint log:
+Used: leading(revenue0 supplier )
+UnUsed:
+SyntaxError:
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q16.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q16.out
deleted file mode 100644
index c99acb83ba1..00000000000
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q16.out
+++ /dev/null
@@ -1,26 +0,0 @@
--- This file is automatically generated. You should know what you did if you
want to edit this
--- !select --
-PhysicalResultSink
---PhysicalQuickSort[MERGE_SORT]
-----PhysicalDistribute
-------PhysicalQuickSort[LOCAL_SORT]
---------hashAgg[GLOBAL]
-----------PhysicalDistribute
-------------hashAgg[LOCAL]
---------------hashJoin[LEFT_ANTI_JOIN] hashCondition=((partsupp.ps_suppkey =
supplier.s_suppkey)) otherCondition=()
-----------------PhysicalProject
-------------------hashJoin[INNER_JOIN] hashCondition=((part.p_partkey =
partsupp.ps_partkey)) otherCondition=() build RFs:RF0 p_partkey->[ps_partkey]
---------------------PhysicalProject
-----------------------PhysicalOlapScan[partsupp] apply RFs: RF0
---------------------PhysicalProject
-----------------------filter(( not (p_brand = 'Brand#45')) and ( not (p_type
like 'MEDIUM POLISHED%')) and p_size IN (14, 19, 23, 3, 36, 45, 49, 9))
-------------------------PhysicalOlapScan[part]
-----------------PhysicalDistribute
-------------------PhysicalProject
---------------------filter((s_comment like '%Customer%Complaints%'))
-----------------------PhysicalOlapScan[supplier]
-
-Used: leading(partsupp part supplier)
-UnUsed:
-SyntaxError:
-
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q17.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q17.out
index 65bad6d5980..406f9fecbf6 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q17.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q17.out
@@ -3,19 +3,24 @@
PhysicalResultSink
--PhysicalProject
----hashAgg[GLOBAL]
-------PhysicalDistribute
+------PhysicalDistribute[DistributionSpecGather]
--------hashAgg[LOCAL]
----------PhysicalProject
------------filter((cast(l_quantity as DECIMALV3(38, 5)) < (0.2 *
avg(cast(l_quantity as DECIMALV3(17, 4))) OVER(PARTITION BY p_partkey))))
--------------PhysicalWindow
-----------------PhysicalQuickSort
-------------------PhysicalDistribute
+----------------PhysicalQuickSort[LOCAL_SORT]
+------------------PhysicalDistribute[DistributionSpecHash]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN](part.p_partkey = lineitem.l_partkey)
+----------------------hashJoin[INNER_JOIN] hashCondition=((part.p_partkey =
lineitem.l_partkey)) otherCondition=()
------------------------PhysicalProject
--------------------------PhysicalOlapScan[lineitem]
-------------------------PhysicalDistribute
+------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------PhysicalProject
-----------------------------filter((part.p_container = 'MED BOX')(part.p_brand
= 'Brand#23'))
+----------------------------filter((part.p_brand = 'Brand#23') and
(part.p_container = 'MED BOX'))
------------------------------PhysicalOlapScan[part]
+Hint log:
+Used: leading(lineitem broadcast part )
+UnUsed:
+SyntaxError:
+
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q18.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q18.out
deleted file mode 100644
index ae401ef1cd4..00000000000
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q18.out
+++ /dev/null
@@ -1,26 +0,0 @@
--- This file is automatically generated. You should know what you did if you
want to edit this
--- !select --
-PhysicalResultSink
---PhysicalTopN
-----PhysicalDistribute
-------PhysicalTopN
---------hashAgg[LOCAL]
-----------PhysicalProject
-------------hashJoin[INNER_JOIN](orders.o_orderkey = lineitem.l_orderkey)
---------------PhysicalProject
-----------------PhysicalOlapScan[lineitem]
---------------PhysicalDistribute
-----------------PhysicalProject
-------------------hashJoin[INNER_JOIN](customer.c_custkey = orders.o_custkey)
---------------------PhysicalProject
-----------------------PhysicalOlapScan[customer]
---------------------PhysicalDistribute
-----------------------hashJoin[LEFT_SEMI_JOIN](orders.o_orderkey =
lineitem.l_orderkey)
-------------------------PhysicalProject
---------------------------PhysicalOlapScan[orders]
-------------------------PhysicalProject
---------------------------filter((sum(l_quantity) > 300.00))
-----------------------------hashAgg[LOCAL]
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[lineitem]
-
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q19.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q19.out
index 06890457532..d69c1002f27 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q19.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q19.out
@@ -2,20 +2,20 @@
-- !select --
PhysicalResultSink
--hashAgg[GLOBAL]
-----PhysicalDistribute
+----PhysicalDistribute[DistributionSpecGather]
------hashAgg[LOCAL]
--------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((part.p_partkey =
lineitem.l_partkey)) otherCondition=(((((((part.p_brand = 'Brand#12') AND
p_container IN ('SM BOX', 'SM CASE', 'SM PACK', 'SM PKG')) AND
((lineitem.l_quantity >= 1.00) AND (lineitem.l_quantity <= 11.00))) AND
(part.p_size <= 5)) OR ((((part.p_brand = 'Brand#23') AND p_container IN ('MED
BAG', 'MED BOX', 'MED PACK', 'MED PKG')) AND ((lineitem.l_quantity >= 10.00)
AND (lineitem.l_quantity <= 20.00))) AND (part.p_size <= 10))) [...]
-------------PhysicalDistribute
---------------PhysicalProject
-----------------filter(((((lineitem.l_quantity >= 1.00) AND
(lineitem.l_quantity <= 11.00)) OR ((lineitem.l_quantity >= 10.00) AND
(lineitem.l_quantity <= 20.00))) OR ((lineitem.l_quantity >= 20.00) AND
(lineitem.l_quantity <= 30.00))) and (lineitem.l_shipinstruct = 'DELIVER IN
PERSON') and l_shipmode IN ('AIR REG', 'AIR'))
-------------------PhysicalOlapScan[lineitem] apply RFs: RF0
-------------PhysicalDistribute
+----------hashJoin[INNER_JOIN] hashCondition=((part.p_partkey =
lineitem.l_partkey)) otherCondition=(((((((part.p_brand = 'Brand#12') AND
p_container IN ('SM BOX', 'SM CASE', 'SM PACK', 'SM PKG')) AND
((lineitem.l_quantity >= 1.00) AND (lineitem.l_quantity <= 11.00))) AND
(part.p_size <= 5)) OR ((((part.p_brand = 'Brand#23') AND p_container IN ('MED
BAG', 'MED BOX', 'MED PACK', 'MED PKG')) AND ((lineitem.l_quantity >= 10.00)
AND (lineitem.l_quantity <= 20.00))) AND (part.p_size <= 10))) [...]
+------------PhysicalProject
+--------------filter((lineitem.l_quantity <= 30.00) and (lineitem.l_quantity
>= 1.00) and (lineitem.l_shipinstruct = 'DELIVER IN PERSON') and l_shipmode IN
('AIR REG', 'AIR'))
+----------------PhysicalOlapScan[lineitem]
+------------PhysicalDistribute[DistributionSpecReplicated]
--------------PhysicalProject
----------------filter((((((part.p_brand = 'Brand#12') AND p_container IN ('SM
BOX', 'SM CASE', 'SM PACK', 'SM PKG')) AND (part.p_size <= 5)) OR
(((part.p_brand = 'Brand#23') AND p_container IN ('MED BAG', 'MED BOX', 'MED
PACK', 'MED PKG')) AND (part.p_size <= 10))) OR (((part.p_brand = 'Brand#34')
AND p_container IN ('LG BOX', 'LG CASE', 'LG PACK', 'LG PKG')) AND (part.p_size
<= 15))) and (part.p_size >= 1))
------------------PhysicalOlapScan[part]
-Used: leading(lineitem part)
+Hint log:
+Used: leading(lineitem broadcast part )
UnUsed:
SyntaxError:
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q2.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q2.out
deleted file mode 100644
index ad98be8fe33..00000000000
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q2.out
+++ /dev/null
@@ -1,39 +0,0 @@
--- This file is automatically generated. You should know what you did if you
want to edit this
--- !select --
-PhysicalResultSink
---PhysicalTopN
-----PhysicalDistribute
-------PhysicalTopN
---------PhysicalProject
-----------filter((partsupp.ps_supplycost = min(ps_supplycost) OVER(PARTITION
BY p_partkey)))
-------------PhysicalWindow
---------------PhysicalQuickSort
-----------------PhysicalDistribute
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN](nation.n_regionkey =
region.r_regionkey)
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------hashJoin[INNER_JOIN](supplier.s_nationkey =
nation.n_nationkey)
-----------------------------PhysicalDistribute
-------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN](part.p_partkey =
partsupp.ps_partkey)(supplier.s_suppkey = partsupp.ps_suppkey)
-----------------------------------NestedLoopJoin[CROSS_JOIN]
-------------------------------------PhysicalProject
---------------------------------------filter((part.p_size = 15)(p_type like
'%BRASS'))
-----------------------------------------PhysicalOlapScan[part]
-------------------------------------PhysicalDistribute
---------------------------------------PhysicalOlapScan[supplier]
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[partsupp]
-----------------------------PhysicalDistribute
-------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[nation]
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------filter((region.r_name = 'EUROPE'))
-----------------------------PhysicalOlapScan[region]
-
-Used: leading({ supplier { nation region } } { partsupp part })
-UnUsed:
-SyntaxError:
-
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q20.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q20.out
deleted file mode 100644
index 6114877bc9d..00000000000
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q20.out
+++ /dev/null
@@ -1,34 +0,0 @@
--- This file is automatically generated. You should know what you did if you
want to edit this
--- !select --
-PhysicalResultSink
---PhysicalQuickSort
-----PhysicalDistribute
-------PhysicalQuickSort
---------PhysicalProject
-----------hashJoin[RIGHT_SEMI_JOIN](supplier.s_suppkey = partsupp.ps_suppkey)
-------------PhysicalDistribute
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN](lineitem.l_partkey =
partsupp.ps_partkey)(lineitem.l_suppkey = partsupp.ps_suppkey)(cast(ps_availqty
as DECIMALV3(38, 3)) > (0.5 * sum(l_quantity)))
-------------------PhysicalProject
---------------------hashAgg[GLOBAL]
-----------------------PhysicalDistribute
-------------------------hashAgg[LOCAL]
---------------------------PhysicalProject
-----------------------------filter((lineitem.l_shipdate <
1995-01-01)(lineitem.l_shipdate >= 1994-01-01))
-------------------------------PhysicalOlapScan[lineitem]
-------------------PhysicalDistribute
---------------------hashJoin[LEFT_SEMI_JOIN](partsupp.ps_partkey =
part.p_partkey)
-----------------------PhysicalProject
-------------------------PhysicalOlapScan[partsupp]
-----------------------PhysicalProject
-------------------------filter((p_name like 'forest%'))
---------------------------PhysicalOlapScan[part]
-------------PhysicalDistribute
---------------hashJoin[INNER_JOIN](supplier.s_nationkey = nation.n_nationkey)
-----------------PhysicalProject
-------------------PhysicalOlapScan[supplier]
-----------------PhysicalDistribute
-------------------PhysicalProject
---------------------filter((nation.n_name = 'CANADA'))
-----------------------PhysicalOlapScan[nation]
-
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q21.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q21.out
deleted file mode 100644
index 2f4348619b2..00000000000
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q21.out
+++ /dev/null
@@ -1,34 +0,0 @@
--- This file is automatically generated. You should know what you did if you
want to edit this
--- !select --
-PhysicalResultSink
---PhysicalTopN
-----PhysicalDistribute
-------PhysicalTopN
---------hashAgg[GLOBAL]
-----------PhysicalDistribute
-------------hashAgg[LOCAL]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN](orders.o_orderkey = l1.l_orderkey)
-------------------PhysicalProject
---------------------filter((orders.o_orderstatus = 'F'))
-----------------------PhysicalOlapScan[orders]
-------------------hashJoin[RIGHT_SEMI_JOIN](l2.l_orderkey = l1.l_orderkey)(
not (l_suppkey = l_suppkey))
---------------------PhysicalProject
-----------------------PhysicalOlapScan[lineitem]
---------------------hashJoin[RIGHT_ANTI_JOIN](l3.l_orderkey = l1.l_orderkey)(
not (l_suppkey = l_suppkey))
-----------------------PhysicalProject
-------------------------filter((l3.l_receiptdate > l3.l_commitdate))
---------------------------PhysicalOlapScan[lineitem]
-----------------------hashJoin[INNER_JOIN](supplier.s_suppkey = l1.l_suppkey)
-------------------------PhysicalProject
---------------------------filter((l1.l_receiptdate > l1.l_commitdate))
-----------------------------PhysicalOlapScan[lineitem]
-------------------------PhysicalDistribute
---------------------------hashJoin[INNER_JOIN](supplier.s_nationkey =
nation.n_nationkey)
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[supplier]
-----------------------------PhysicalDistribute
-------------------------------PhysicalProject
---------------------------------filter((nation.n_name = 'SAUDI ARABIA'))
-----------------------------------PhysicalOlapScan[nation]
-
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q22.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q22.out
deleted file mode 100644
index 09e3c287f6a..00000000000
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q22.out
+++ /dev/null
@@ -1,28 +0,0 @@
--- This file is automatically generated. You should know what you did if you
want to edit this
--- !select --
-PhysicalResultSink
---PhysicalQuickSort
-----PhysicalDistribute
-------PhysicalQuickSort
---------hashAgg[GLOBAL]
-----------PhysicalDistribute
-------------hashAgg[LOCAL]
---------------PhysicalProject
-----------------hashJoin[RIGHT_ANTI_JOIN](orders.o_custkey =
customer.c_custkey)
-------------------PhysicalDistribute
---------------------PhysicalProject
-----------------------PhysicalOlapScan[orders]
-------------------PhysicalDistribute
---------------------NestedLoopJoin[INNER_JOIN](cast(c_acctbal as DECIMALV3(38,
4)) > avg(c_acctbal))
-----------------------PhysicalProject
-------------------------filter(substring(c_phone, 1, 2) IN ('13', '31', '23',
'29', '30', '18', '17'))
---------------------------PhysicalOlapScan[customer]
-----------------------PhysicalDistribute
-------------------------PhysicalAssertNumRows
---------------------------hashAgg[GLOBAL]
-----------------------------PhysicalDistribute
-------------------------------hashAgg[LOCAL]
---------------------------------PhysicalProject
-----------------------------------filter((customer.c_acctbal >
0.00)substring(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17'))
-------------------------------------PhysicalOlapScan[customer]
-
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q3.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q3.out
index 25edf8184c0..a1c0695e752 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q3.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q3.out
@@ -2,27 +2,29 @@
-- !select --
PhysicalResultSink
--PhysicalTopN[MERGE_SORT]
-----PhysicalDistribute
+----PhysicalDistribute[DistributionSpecGather]
------PhysicalTopN[LOCAL_SORT]
---------hashAgg[LOCAL]
-----------PhysicalProject
-------------hashJoin[INNER_JOIN] hashCondition=((lineitem.l_orderkey =
orders.o_orderkey)) otherCondition=() build RFs:RF1 o_orderkey->[l_orderkey]
---------------PhysicalProject
-----------------filter((lineitem.l_shipdate > '1995-03-15'))
-------------------PhysicalOlapScan[lineitem] apply RFs: RF1
---------------PhysicalDistribute
+--------PhysicalProject
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((lineitem.l_orderkey =
orders.o_orderkey)) otherCondition=()
----------------PhysicalProject
-------------------hashJoin[INNER_JOIN] hashCondition=((customer.c_custkey =
orders.o_custkey)) otherCondition=() build RFs:RF0 c_custkey->[o_custkey]
---------------------PhysicalDistribute
-----------------------PhysicalProject
-------------------------filter((orders.o_orderdate < '1995-03-15'))
---------------------------PhysicalOlapScan[orders] apply RFs: RF0
---------------------PhysicalDistribute
-----------------------PhysicalProject
-------------------------filter((customer.c_mktsegment = 'BUILDING'))
---------------------------PhysicalOlapScan[customer]
+------------------filter((lineitem.l_shipdate > '1995-03-15'))
+--------------------PhysicalOlapScan[lineitem]
+----------------PhysicalDistribute[DistributionSpecHash]
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN] hashCondition=((customer.c_custkey =
orders.o_custkey)) otherCondition=()
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------PhysicalProject
+--------------------------filter((orders.o_orderdate < '1995-03-15'))
+----------------------------PhysicalOlapScan[orders]
+----------------------PhysicalDistribute[DistributionSpecHash]
+------------------------PhysicalProject
+--------------------------filter((customer.c_mktsegment = 'BUILDING'))
+----------------------------PhysicalOlapScan[customer]
-Used: leading(lineitem { orders customer })
+Hint log:
+Used: leading(lineitem { orders shuffle customer } )
UnUsed:
SyntaxError:
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q4.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q4.out
index afdd554649a..1eff5041831 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q4.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q4.out
@@ -2,21 +2,22 @@
-- !select --
PhysicalResultSink
--PhysicalQuickSort[MERGE_SORT]
-----PhysicalDistribute
+----PhysicalDistribute[DistributionSpecGather]
------PhysicalQuickSort[LOCAL_SORT]
--------hashAgg[GLOBAL]
-----------PhysicalDistribute
+----------PhysicalDistribute[DistributionSpecHash]
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------hashJoin[RIGHT_SEMI_JOIN] hashCondition=((lineitem.l_orderkey
= orders.o_orderkey)) otherCondition=() build RFs:RF0 o_orderkey->[l_orderkey]
+----------------hashJoin[RIGHT_SEMI_JOIN] hashCondition=((lineitem.l_orderkey
= orders.o_orderkey)) otherCondition=()
------------------PhysicalProject
--------------------filter((lineitem.l_commitdate < lineitem.l_receiptdate))
-----------------------PhysicalOlapScan[lineitem] apply RFs: RF0
+----------------------PhysicalOlapScan[lineitem]
------------------PhysicalProject
--------------------filter((orders.o_orderdate < '1993-10-01') and
(orders.o_orderdate >= '1993-07-01'))
----------------------PhysicalOlapScan[orders]
-Used: leading(lineitem orders)
+Hint log:
+Used: leading(lineitem orders )
UnUsed:
SyntaxError:
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q5.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q5.out
index 9113b8d8f10..fe9e8f7afdc 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q5.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q5.out
@@ -2,24 +2,43 @@
-- !select --
PhysicalResultSink
--PhysicalQuickSort[MERGE_SORT]
-----PhysicalQuickSort[LOCAL_SORT]
-------hashAgg[GLOBAL]
---------hashAgg[LOCAL]
-----------hashJoin[INNER_JOIN] hashCondition=((customer.c_custkey =
orders.o_custkey) and (customer.c_nationkey = supplier.s_nationkey))
otherCondition=() build RFs:RF4 c_nationkey->[s_nationkey];RF5
c_custkey->[o_custkey]
-------------hashJoin[INNER_JOIN] hashCondition=((lineitem.l_orderkey =
orders.o_orderkey)) otherCondition=() build RFs:RF3 o_orderkey->[l_orderkey]
---------------hashJoin[INNER_JOIN] hashCondition=((lineitem.l_suppkey =
supplier.s_suppkey)) otherCondition=() build RFs:RF2 s_suppkey->[l_suppkey]
-----------------PhysicalOlapScan[lineitem] apply RFs: RF2 RF3
-----------------hashJoin[INNER_JOIN] hashCondition=((supplier.s_nationkey =
nation.n_nationkey)) otherCondition=() build RFs:RF1 n_nationkey->[s_nationkey]
-------------------PhysicalOlapScan[supplier] apply RFs: RF1 RF4
-------------------hashJoin[INNER_JOIN] hashCondition=((nation.n_regionkey =
region.r_regionkey)) otherCondition=() build RFs:RF0 r_regionkey->[n_regionkey]
---------------------PhysicalOlapScan[nation] apply RFs: RF0
---------------------filter((region.r_name = 'ASIA'))
-----------------------PhysicalOlapScan[region]
---------------filter((orders.o_orderdate < '1995-01-01') and
(orders.o_orderdate >= '1994-01-01'))
-----------------PhysicalOlapScan[orders] apply RFs: RF5
-------------PhysicalOlapScan[customer]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute[DistributionSpecHash]
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN] hashCondition=((customer.c_custkey =
orders.o_custkey) and (customer.c_nationkey = supplier.s_nationkey))
otherCondition=()
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN] hashCondition=((lineitem.l_suppkey
= supplier.s_suppkey)) otherCondition=()
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN]
hashCondition=((lineitem.l_orderkey = orders.o_orderkey)) otherCondition=()
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[lineitem]
+----------------------------PhysicalProject
+------------------------------filter((orders.o_orderdate < '1995-01-01') and
(orders.o_orderdate >= '1994-01-01'))
+--------------------------------PhysicalOlapScan[orders]
+------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN]
hashCondition=((supplier.s_nationkey = nation.n_nationkey)) otherCondition=()
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[supplier]
+------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN]
hashCondition=((nation.n_regionkey = region.r_regionkey)) otherCondition=()
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[nation]
+------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------PhysicalProject
+----------------------------------------filter((region.r_name = 'ASIA'))
+------------------------------------------PhysicalOlapScan[region]
+------------------PhysicalDistribute[DistributionSpecHash]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[customer]
-Used: leading(lineitem { supplier { nation region } } orders customer)
+Hint log:
+Used: leading(lineitem orders broadcast { supplier broadcast { nation
broadcast region } } shuffle customer )
UnUsed:
SyntaxError:
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q6.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q6.out
index 7976f47c6b4..f1f764bec09 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q6.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q6.out
@@ -2,7 +2,7 @@
-- !select --
PhysicalResultSink
--hashAgg[GLOBAL]
-----PhysicalDistribute
+----PhysicalDistribute[DistributionSpecGather]
------hashAgg[LOCAL]
--------PhysicalProject
----------filter((lineitem.l_discount <= 0.07) and (lineitem.l_discount >=
0.05) and (lineitem.l_quantity < 24.00) and (lineitem.l_shipdate <
'1995-01-01') and (lineitem.l_shipdate >= '1994-01-01'))
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q7.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q7.out
index 6facc13fb77..3230a65000d 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q7.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q7.out
@@ -1,44 +1,45 @@
-- This file is automatically generated. You should know what you did if you
want to edit this
-- !select --
PhysicalResultSink
---PhysicalQuickSort
-----PhysicalDistribute
-------PhysicalQuickSort
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
--------hashAgg[GLOBAL]
-----------PhysicalDistribute
+----------PhysicalDistribute[DistributionSpecHash]
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN] hashCondition=((customer.c_custkey =
orders.o_custkey) and (customer.c_nationkey = n2.n_nationkey))otherCondition=()
+----------------hashJoin[INNER_JOIN] hashCondition=((orders.o_orderkey =
lineitem.l_orderkey) and (supplier.s_suppkey = lineitem.l_suppkey))
otherCondition=()
------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
-------------------PhysicalDistribute
---------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN] hashCondition=((orders.o_orderkey =
lineitem.l_orderkey))otherCondition=()
-------------------------PhysicalProject
---------------------------PhysicalOlapScan[orders]
-------------------------PhysicalDistribute
+--------------------filter((lineitem.l_shipdate <= '1996-12-31') and
(lineitem.l_shipdate >= '1995-01-01'))
+----------------------PhysicalOlapScan[lineitem]
+------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------NestedLoopJoin[INNER_JOIN](((n1.n_name = 'FRANCE') AND
(n2.n_name = 'GERMANY')) OR ((n1.n_name = 'GERMANY') AND (n2.n_name =
'FRANCE')))
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN]
hashCondition=((supplier.s_nationkey = n1.n_nationkey)) otherCondition=()
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN]
hashCondition=((supplier.s_suppkey = lineitem.l_suppkey))otherCondition=()
-------------------------------PhysicalDistribute
---------------------------------hashJoin[INNER_JOIN]
hashCondition=((supplier.s_nationkey = n1.n_nationkey))otherCondition=()
-----------------------------------PhysicalDistribute
+----------------------------PhysicalOlapScan[supplier]
+--------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------------PhysicalProject
+------------------------------filter(n_name IN ('FRANCE', 'GERMANY'))
+--------------------------------PhysicalOlapScan[nation]
+----------------------PhysicalDistribute[DistributionSpecReplicated]
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_custkey = orders.o_custkey)) otherCondition=()
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[orders]
+----------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_nationkey = n2.n_nationkey)) otherCondition=()
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[customer]
+----------------------------------PhysicalDistribute[DistributionSpecReplicated]
------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[supplier]
-----------------------------------PhysicalDistribute
-------------------------------------NestedLoopJoin[INNER_JOIN](((n1.n_name =
'FRANCE') AND (n2.n_name = 'GERMANY')) OR ((n1.n_name = 'GERMANY') AND
(n2.n_name = 'FRANCE')))
---------------------------------------PhysicalProject
-----------------------------------------filter(((n2.n_name = 'GERMANY') OR
(n2.n_name = 'FRANCE')))
-------------------------------------------PhysicalOlapScan[nation]
---------------------------------------PhysicalDistribute
-----------------------------------------PhysicalProject
-------------------------------------------filter(((n1.n_name = 'FRANCE') OR
(n1.n_name = 'GERMANY')))
---------------------------------------------PhysicalOlapScan[nation]
-------------------------------PhysicalDistribute
---------------------------------PhysicalProject
-----------------------------------filter((lineitem.l_shipdate <= '1996-12-31')
and (lineitem.l_shipdate >= '1995-01-01'))
-------------------------------------PhysicalOlapScan[lineitem]
+--------------------------------------filter(n_name IN ('FRANCE', 'GERMANY'))
+----------------------------------------PhysicalOlapScan[nation]
-Used:
+Hint log:
+Used: leading(lineitem broadcast { supplier broadcast n1 } { orders shuffle
{ customer broadcast n2 } } )
UnUsed:
-SyntaxError: leading(customer { orders { lineitem { supplier { n1 n2 } } } })
Msg:tables should be same as join tables
+SyntaxError:
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q8.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q8.out
index 07a9cd9ca20..22b5a4dde59 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q8.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q8.out
@@ -1,49 +1,54 @@
-- This file is automatically generated. You should know what you did if you
want to edit this
-- !select --
PhysicalResultSink
---PhysicalQuickSort
-----PhysicalDistribute
-------PhysicalQuickSort
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
--------PhysicalProject
----------hashAgg[GLOBAL]
-------------PhysicalDistribute
+------------PhysicalDistribute[DistributionSpecHash]
--------------hashAgg[LOCAL]
----------------PhysicalProject
-------------------hashJoin[INNER_JOIN](supplier.s_nationkey = n2.n_nationkey)
+------------------hashJoin[INNER_JOIN] hashCondition=((supplier.s_nationkey =
n2.n_nationkey)) otherCondition=()
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN](supplier.s_suppkey =
lineitem.l_suppkey)
+----------------------hashJoin[INNER_JOIN] hashCondition=((supplier.s_suppkey
= lineitem.l_suppkey)) otherCondition=()
------------------------PhysicalProject
--------------------------PhysicalOlapScan[supplier]
-------------------------PhysicalDistribute
+------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN](n1.n_regionkey =
region.r_regionkey)
+----------------------------hashJoin[INNER_JOIN]
hashCondition=((lineitem.l_orderkey = orders.o_orderkey) and (orders.o_custkey
= customer.c_custkey)) otherCondition=()
------------------------------PhysicalProject
---------------------------------hashJoin[INNER_JOIN](customer.c_nationkey =
n1.n_nationkey)
+--------------------------------filter((orders.o_orderdate <= '1996-12-31')
and (orders.o_orderdate >= '1995-01-01'))
+----------------------------------PhysicalOlapScan[orders]
+------------------------------NestedLoopJoin[CROSS_JOIN]
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN]
hashCondition=((part.p_partkey = lineitem.l_partkey)) otherCondition=()
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[lineitem]
+------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+--------------------------------------PhysicalProject
+----------------------------------------filter((part.p_type = 'ECONOMY
ANODIZED STEEL'))
+------------------------------------------PhysicalOlapScan[part]
+--------------------------------PhysicalDistribute[DistributionSpecReplicated]
----------------------------------PhysicalProject
-------------------------------------hashJoin[INNER_JOIN](orders.o_custkey =
customer.c_custkey)
+------------------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_nationkey = n1.n_nationkey)) otherCondition=()
--------------------------------------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)
+--------------------------------------PhysicalDistribute[DistributionSpecReplicated]
+----------------------------------------PhysicalProject
+------------------------------------------hashJoin[INNER_JOIN]
hashCondition=((n1.n_regionkey = region.r_regionkey)) otherCondition=()
+--------------------------------------------PhysicalProject
+----------------------------------------------PhysicalOlapScan[nation]
+--------------------------------------------PhysicalDistribute[DistributionSpecReplicated]
----------------------------------------------PhysicalProject
-------------------------------------------------PhysicalOlapScan[lineitem]
-----------------------------------------------PhysicalDistribute
-------------------------------------------------PhysicalProject
---------------------------------------------------filter((part.p_type =
'ECONOMY ANODIZED STEEL'))
-----------------------------------------------------PhysicalOlapScan[part]
-----------------------------------PhysicalDistribute
-------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[nation]
-------------------------------PhysicalDistribute
---------------------------------PhysicalProject
-----------------------------------filter((region.r_name = 'AMERICA'))
-------------------------------------PhysicalOlapScan[region]
---------------------PhysicalDistribute
+------------------------------------------------filter((region.r_name =
'AMERICA'))
+--------------------------------------------------PhysicalOlapScan[region]
+--------------------PhysicalDistribute[DistributionSpecReplicated]
----------------------PhysicalProject
------------------------PhysicalOlapScan[nation]
+Hint log:
+Used: leading(supplier { orders { lineitem broadcast part } { customer
broadcast { n1 broadcast region } } } broadcast n2 )
+UnUsed:
+SyntaxError:
+
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q9.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q9.out
index 9026d438674..4aa9900d440 100644
--- a/regression-test/data/nereids_hint_tpch_p0/shape/q9.out
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q9.out
@@ -2,44 +2,45 @@
-- !select --
PhysicalResultSink
--PhysicalQuickSort[MERGE_SORT]
-----PhysicalDistribute
+----PhysicalDistribute[DistributionSpecGather]
------PhysicalQuickSort[LOCAL_SORT]
--------hashAgg[GLOBAL]
-----------PhysicalDistribute
+----------PhysicalDistribute[DistributionSpecHash]
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN] hashCondition=((partsupp.ps_partkey =
lineitem.l_partkey) and (partsupp.ps_suppkey = lineitem.l_suppkey))
otherCondition=() build RFs:RF4 ps_partkey->[l_partkey];RF5
ps_suppkey->[l_suppkey]
-------------------PhysicalDistribute
+----------------hashJoin[INNER_JOIN] hashCondition=((partsupp.ps_partkey =
lineitem.l_partkey) and (partsupp.ps_suppkey = lineitem.l_suppkey))
otherCondition=()
+------------------PhysicalDistribute[DistributionSpecHash]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN] hashCondition=((orders.o_orderkey =
lineitem.l_orderkey)) otherCondition=() build RFs:RF3 l_orderkey->[o_orderkey]
+----------------------hashJoin[INNER_JOIN] hashCondition=((orders.o_orderkey =
lineitem.l_orderkey)) otherCondition=()
------------------------PhysicalProject
---------------------------PhysicalOlapScan[orders] apply RFs: RF3
-------------------------PhysicalDistribute
+--------------------------PhysicalOlapScan[orders]
+------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN]
hashCondition=((supplier.s_suppkey = lineitem.l_suppkey)) otherCondition=()
build RFs:RF2 s_suppkey->[l_suppkey]
-------------------------------PhysicalDistribute
---------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN]
hashCondition=((part.p_partkey = lineitem.l_partkey)) otherCondition=() build
RFs:RF1 p_partkey->[l_partkey]
+----------------------------hashJoin[INNER_JOIN]
hashCondition=((supplier.s_nationkey = nation.n_nationkey)) otherCondition=()
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN]
hashCondition=((supplier.s_suppkey = lineitem.l_suppkey)) otherCondition=()
+----------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------PhysicalProject
+--------------------------------------hashJoin[INNER_JOIN]
hashCondition=((part.p_partkey = lineitem.l_partkey)) otherCondition=()
+----------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------PhysicalProject
+--------------------------------------------PhysicalOlapScan[lineitem]
+----------------------------------------PhysicalDistribute[DistributionSpecHash]
+------------------------------------------PhysicalProject
+--------------------------------------------filter((p_name like '%green%'))
+----------------------------------------------PhysicalOlapScan[part]
+----------------------------------PhysicalDistribute[DistributionSpecHash]
------------------------------------PhysicalProject
---------------------------------------PhysicalOlapScan[lineitem] apply RFs:
RF1 RF2 RF4 RF5
-------------------------------------PhysicalDistribute
---------------------------------------PhysicalProject
-----------------------------------------filter((p_name like '%green%'))
-------------------------------------------PhysicalOlapScan[part]
-------------------------------PhysicalDistribute
+--------------------------------------PhysicalOlapScan[supplier]
+------------------------------PhysicalDistribute[DistributionSpecReplicated]
--------------------------------PhysicalProject
-----------------------------------hashJoin[INNER_JOIN]
hashCondition=((supplier.s_nationkey = nation.n_nationkey)) otherCondition=()
build RFs:RF0 n_nationkey->[s_nationkey]
-------------------------------------PhysicalDistribute
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[supplier] apply RFs:
RF0
-------------------------------------PhysicalDistribute
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[nation]
-------------------PhysicalDistribute
+----------------------------------PhysicalOlapScan[nation]
+------------------PhysicalDistribute[DistributionSpecHash]
--------------------PhysicalProject
----------------------PhysicalOlapScan[partsupp]
-Used: leading(orders { { lineitem part } { supplier nation } } partsupp)
+Hint log:
+Used: leading(orders shuffle { lineitem shuffle part } shuffle { supplier
broadcast nation } shuffle partsupp )
UnUsed:
SyntaxError:
diff --git a/regression-test/data/nereids_p0/hint/test_leading.out
b/regression-test/data/nereids_p0/hint/test_leading.out
index 7274d409312..53195035880 100644
--- a/regression-test/data/nereids_p0/hint/test_leading.out
+++ b/regression-test/data/nereids_p0/hint/test_leading.out
@@ -2407,7 +2407,7 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t1]
-------------PhysicalDistribute[DistributionSpecReplicated]
+------------PhysicalDistribute[DistributionSpecHash]
--------------PhysicalProject
----------------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
------------------PhysicalProject
@@ -2417,8 +2417,8 @@ PhysicalResultSink
----------------------PhysicalOlapScan[t3]
Hint log:
-Used: leading(t1 { t2 broadcast t3 } )
-UnUsed: [shuffle]_2
+Used: leading(t1 shuffle { t2 broadcast t3 } )
+UnUsed:
SyntaxError:
-- !select92_3 --
@@ -2430,7 +2430,7 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t1]
-------------PhysicalDistribute[DistributionSpecReplicated]
+------------PhysicalDistribute[DistributionSpecHash]
--------------PhysicalProject
----------------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
------------------PhysicalProject
@@ -2440,8 +2440,8 @@ PhysicalResultSink
----------------------PhysicalOlapScan[t2]
Hint log:
-Used: leading(t1 { t3 broadcast t2 } )
-UnUsed: [shuffle]_2
+Used: leading(t1 shuffle { t3 broadcast t2 } )
+UnUsed:
SyntaxError:
-- !select92_4 --
@@ -2476,7 +2476,7 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2) and (t2.c2 =
t3.c3)) otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecReplicated]
+------------PhysicalDistribute[DistributionSpecHash]
--------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t3.c3))
otherCondition=()
----------------PhysicalProject
------------------PhysicalOlapScan[t1]
@@ -2485,8 +2485,8 @@ PhysicalResultSink
--------------------PhysicalOlapScan[t3]
Hint log:
-Used: leading(t2 { t1 broadcast t3 } )
-UnUsed: [shuffle]_2
+Used: leading(t2 shuffle { t1 broadcast t3 } )
+UnUsed:
SyntaxError:
-- !select92_6 --
@@ -2498,7 +2498,7 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2) and (t2.c2 =
t3.c3)) otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecReplicated]
+------------PhysicalDistribute[DistributionSpecHash]
--------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t3.c3))
otherCondition=()
----------------PhysicalProject
------------------PhysicalOlapScan[t3]
@@ -2507,8 +2507,8 @@ PhysicalResultSink
--------------------PhysicalOlapScan[t1]
Hint log:
-Used: leading(t2 { t3 broadcast t1 } )
-UnUsed: [shuffle]_2
+Used: leading(t2 shuffle { t3 broadcast t1 } )
+UnUsed:
SyntaxError:
-- !select93_1 --
@@ -2543,7 +2543,7 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t1]
-------------PhysicalDistribute[DistributionSpecReplicated]
+------------PhysicalDistribute[DistributionSpecHash]
--------------PhysicalProject
----------------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
------------------PhysicalProject
@@ -2566,7 +2566,7 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t1]
-------------PhysicalDistribute[DistributionSpecReplicated]
+------------PhysicalDistribute[DistributionSpecHash]
--------------PhysicalProject
----------------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
------------------PhysicalProject
@@ -2612,7 +2612,7 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2) and (t2.c2 =
t3.c3)) otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecReplicated]
+------------PhysicalDistribute[DistributionSpecHash]
--------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t3.c3))
otherCondition=()
----------------PhysicalProject
------------------PhysicalOlapScan[t1]
@@ -2634,7 +2634,7 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2) and (t2.c2 =
t3.c3)) otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecReplicated]
+------------PhysicalDistribute[DistributionSpecHash]
--------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t3.c3))
otherCondition=()
----------------PhysicalProject
------------------PhysicalOlapScan[t3]
@@ -2689,8 +2689,8 @@ PhysicalResultSink
----------------------PhysicalOlapScan[t3]
Hint log:
-Used: leading(t1 { t2 t3 } )
-UnUsed: [shuffle]_2
+Used: leading(t1 shuffle { t2 t3 } )
+UnUsed:
SyntaxError:
-- !select94_2 --
@@ -2712,8 +2712,8 @@ PhysicalResultSink
----------------------PhysicalOlapScan[t2]
Hint log:
-Used: leading(t1 { t3 t2 } )
-UnUsed: [shuffle]_2
+Used: leading(t1 shuffle { t3 t2 } )
+UnUsed:
SyntaxError:
-- !select94_2 --
@@ -2757,8 +2757,8 @@ PhysicalResultSink
--------------------PhysicalOlapScan[t3]
Hint log:
-Used: leading(t2 { t1 t3 } )
-UnUsed: [shuffle]_2
+Used: leading(t2 shuffle { t1 t3 } )
+UnUsed:
SyntaxError:
-- !select94_2 --
@@ -2779,8 +2779,8 @@ PhysicalResultSink
--------------------PhysicalOlapScan[t1]
Hint log:
-Used: leading(t2 { t3 t1 } )
-UnUsed: [shuffle]_2
+Used: leading(t2 shuffle { t3 t1 } )
+UnUsed:
SyntaxError:
-- !select95_1 --
@@ -2953,7 +2953,7 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t3]
-------------PhysicalDistribute[DistributionSpecHash]
+------------PhysicalDistribute[DistributionSpecReplicated]
--------------PhysicalProject
----------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
------------------PhysicalProject
@@ -2963,8 +2963,8 @@ PhysicalResultSink
----------------------PhysicalOlapScan[t2]
Hint log:
-Used: leading(t3 { t1 t2 } )
-UnUsed: [broadcast]_2
+Used: leading(t3 broadcast { t1 t2 } )
+UnUsed:
SyntaxError:
-- !select95_9 --
@@ -2976,7 +2976,7 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t3]
-------------PhysicalDistribute[DistributionSpecHash]
+------------PhysicalDistribute[DistributionSpecReplicated]
--------------PhysicalProject
----------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
------------------PhysicalProject
@@ -2986,8 +2986,8 @@ PhysicalResultSink
----------------------PhysicalOlapScan[t1]
Hint log:
-Used: leading(t3 { t2 t1 } )
-UnUsed: [broadcast]_2
+Used: leading(t3 broadcast { t2 t1 } )
+UnUsed:
SyntaxError:
-- !select96_1 --
@@ -3160,7 +3160,7 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t3]
-------------PhysicalDistribute[DistributionSpecReplicated]
+------------PhysicalDistribute[DistributionSpecHash]
--------------PhysicalProject
----------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
------------------PhysicalProject
@@ -3170,8 +3170,8 @@ PhysicalResultSink
----------------------PhysicalOlapScan[t2]
Hint log:
-Used: leading(t3 { t1 broadcast t2 } )
-UnUsed: [shuffle]_2
+Used: leading(t3 shuffle { t1 broadcast t2 } )
+UnUsed:
SyntaxError:
-- !select96_9 --
@@ -3183,7 +3183,7 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t3]
-------------PhysicalDistribute[DistributionSpecReplicated]
+------------PhysicalDistribute[DistributionSpecHash]
--------------PhysicalProject
----------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
------------------PhysicalProject
@@ -3193,8 +3193,8 @@ PhysicalResultSink
----------------------PhysicalOlapScan[t1]
Hint log:
-Used: leading(t3 { t2 broadcast t1 } )
-UnUsed: [shuffle]_2
+Used: leading(t3 shuffle { t2 broadcast t1 } )
+UnUsed:
SyntaxError:
-- !select97_1 --
@@ -3368,7 +3368,7 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t3]
-------------PhysicalDistribute[DistributionSpecHash]
+------------PhysicalDistribute[DistributionSpecReplicated]
--------------PhysicalProject
----------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
------------------PhysicalProject
@@ -3378,8 +3378,8 @@ PhysicalResultSink
----------------------PhysicalOlapScan[t2]
Hint log:
-Used: leading(t3 { t1 shuffle t2 } )
-UnUsed: [broadcast]_2
+Used: leading(t3 broadcast { t1 shuffle t2 } )
+UnUsed:
SyntaxError:
-- !select97_9 --
@@ -3391,7 +3391,7 @@ PhysicalResultSink
----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
------------PhysicalProject
--------------PhysicalOlapScan[t3]
-------------PhysicalDistribute[DistributionSpecHash]
+------------PhysicalDistribute[DistributionSpecReplicated]
--------------PhysicalProject
----------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
------------------PhysicalProject
@@ -3401,8 +3401,8 @@ PhysicalResultSink
----------------------PhysicalOlapScan[t1]
Hint log:
-Used: leading(t3 { t2 shuffle t1 } )
-UnUsed: [broadcast]_2
+Used: leading(t3 broadcast { t2 shuffle t1 } )
+UnUsed:
SyntaxError:
-- !select100_0 --
diff --git a/regression-test/suites/nereids_hint_tpch_p0/load.groovy
b/regression-test/suites/nereids_hint_tpch_p0/load.groovy
new file mode 100644
index 00000000000..0f78a93f5fd
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/load.groovy
@@ -0,0 +1,223 @@
+// 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("load") {
+ String database = context.config.getDbNameByFile(context.file)
+ sql "drop database if exists ${database}"
+ sql "create database ${database}"
+ sql "use ${database}"
+ sql """
+ drop table if exists lineitem;
+ """
+ sql """
+ CREATE TABLE lineitem (
+ l_shipdate DATEV2 NOT NULL,
+ l_orderkey bigint NOT NULL,
+ l_linenumber int not null,
+ l_partkey int NOT NULL,
+ l_suppkey int not null,
+ l_quantity decimal(15, 2) NOT NULL,
+ l_extendedprice decimal(15, 2) NOT NULL,
+ l_discount decimal(15, 2) NOT NULL,
+ l_tax decimal(15, 2) NOT NULL,
+ l_returnflag VARCHAR(1) NOT NULL,
+ l_linestatus VARCHAR(1) NOT NULL,
+ l_commitdate DATEV2 NOT NULL,
+ l_receiptdate DATEV2 NOT NULL,
+ l_shipinstruct VARCHAR(25) NOT NULL,
+ l_shipmode VARCHAR(10) NOT NULL,
+ l_comment VARCHAR(44) NOT NULL
+ )ENGINE=OLAP
+ DUPLICATE KEY(`l_shipdate`, `l_orderkey`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`l_orderkey`) BUCKETS 96
+ PROPERTIES (
+ "replication_num" = "1",
+ "colocate_with" = "lineitem_orders"
+ );
+ """
+
+ sql """
+ drop table if exists orders;
+ """
+
+ sql '''
+ CREATE TABLE orders (
+ o_orderkey bigint NOT NULL,
+ o_orderdate DATEV2 NOT NULL,
+ o_custkey int NOT NULL,
+ o_orderstatus VARCHAR(1) NOT NULL,
+ o_totalprice decimal(15, 2) NOT NULL,
+ o_orderpriority VARCHAR(15) NOT NULL,
+ o_clerk VARCHAR(15) NOT NULL,
+ o_shippriority int NOT NULL,
+ o_comment VARCHAR(79) NOT NULL
+ )ENGINE=OLAP
+ DUPLICATE KEY(`o_orderkey`, `o_orderdate`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`o_orderkey`) BUCKETS 96
+ PROPERTIES (
+ "replication_num" = "1",
+ "colocate_with" = "lineitem_orders"
+ ); '''
+
+ sql '''
+ drop table if exists partsupp;
+ '''
+
+ sql '''
+ CREATE TABLE partsupp (
+ ps_partkey int NOT NULL,
+ ps_suppkey int NOT NULL,
+ ps_availqty int NOT NULL,
+ ps_supplycost decimal(15, 2) NOT NULL,
+ ps_comment VARCHAR(199) NOT NULL
+ )ENGINE=OLAP
+ DUPLICATE KEY(`ps_partkey`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`ps_partkey`) BUCKETS 24
+ PROPERTIES (
+ "replication_num" = "1",
+ "colocate_with" = "part_partsupp"
+ );
+ '''
+
+ sql '''
+ drop table if exists part;
+ '''
+
+ sql '''
+ CREATE TABLE part (
+ p_partkey int NOT NULL,
+ p_name VARCHAR(55) NOT NULL,
+ p_mfgr VARCHAR(25) NOT NULL,
+ p_brand VARCHAR(10) NOT NULL,
+ p_type VARCHAR(25) NOT NULL,
+ p_size int NOT NULL,
+ p_container VARCHAR(10) NOT NULL,
+ p_retailprice decimal(15, 2) NOT NULL,
+ p_comment VARCHAR(23) NOT NULL
+ )ENGINE=OLAP
+ DUPLICATE KEY(`p_partkey`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 24
+ PROPERTIES (
+ "replication_num" = "1",
+ "colocate_with" = "part_partsupp"
+ );
+ '''
+
+ sql '''
+ drop table if exists customer;
+ '''
+
+ sql '''
+ CREATE TABLE customer (
+ c_custkey int NOT NULL,
+ c_name VARCHAR(25) NOT NULL,
+ c_address VARCHAR(40) NOT NULL,
+ c_nationkey int NOT NULL,
+ c_phone VARCHAR(15) NOT NULL,
+ c_acctbal decimal(15, 2) NOT NULL,
+ c_mktsegment VARCHAR(10) NOT NULL,
+ c_comment VARCHAR(117) NOT NULL
+ )ENGINE=OLAP
+ DUPLICATE KEY(`c_custkey`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 24
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ '''
+
+ sql '''
+ drop table if exists supplier
+ '''
+
+ sql '''
+ CREATE TABLE supplier (
+ s_suppkey int NOT NULL,
+ s_name VARCHAR(25) NOT NULL,
+ s_address VARCHAR(40) NOT NULL,
+ s_nationkey int NOT NULL,
+ s_phone VARCHAR(15) NOT NULL,
+ s_acctbal decimal(15, 2) NOT NULL,
+ s_comment VARCHAR(101) NOT NULL
+ )ENGINE=OLAP
+ DUPLICATE KEY(`s_suppkey`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 12
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ '''
+
+ sql '''
+ drop table if exists nation;
+ '''
+
+ sql '''
+ CREATE TABLE `nation` (
+ `n_nationkey` int(11) NOT NULL,
+ `n_name` varchar(25) NOT NULL,
+ `n_regionkey` int(11) NOT NULL,
+ `n_comment` varchar(152) NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`N_NATIONKEY`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`N_NATIONKEY`) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ '''
+
+ sql '''
+ drop table if exists region;
+ '''
+
+ sql '''
+ CREATE TABLE region (
+ r_regionkey int NOT NULL,
+ r_name VARCHAR(25) NOT NULL,
+ r_comment VARCHAR(152)
+ )ENGINE=OLAP
+ DUPLICATE KEY(`r_regionkey`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`r_regionkey`) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ '''
+
+ sql '''
+ drop view if exists revenue0;
+ '''
+
+ sql '''
+ create view revenue0 (supplier_no, total_revenue) as
+ select
+ l_suppkey,
+ sum(l_extendedprice * (1 - l_discount))
+ from
+ lineitem
+ where
+ l_shipdate >= date '1996-01-01'
+ and l_shipdate < date '1996-01-01' + interval '3' month
+ group by
+ l_suppkey;
+ '''
+}
diff --git a/regression-test/suites/nereids_hint_tpch_p0/shape/q10.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q10.groovy
new file mode 100644
index 00000000000..d553139c1c0
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q10.groovy
@@ -0,0 +1,71 @@
+/*
+ * 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("q10") {
+ 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 runtime_filter_mode=OFF'
+ sql 'set parallel_pipeline_task_num=8'
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ qt_select """
+ explain shape plan
+ select
+ /*+ leading(lineitem shuffle {{customer shuffle orders} broadcast nation})
*/
+ c_custkey,
+ c_name,
+ sum(l_extendedprice * (1 - l_discount)) as revenue,
+ c_acctbal,
+ n_name,
+ c_address,
+ c_phone,
+ c_comment
+ from
+ customer,
+ orders,
+ lineitem,
+ nation
+ where
+ c_custkey = o_custkey
+ and l_orderkey = o_orderkey
+ and o_orderdate >= date '1993-10-01'
+ and o_orderdate < date '1993-10-01' + interval '3' month
+ and l_returnflag = 'R'
+ and c_nationkey = n_nationkey
+ group by
+ c_custkey,
+ c_name,
+ c_acctbal,
+ c_phone,
+ n_name,
+ c_address,
+ c_comment
+ order by
+ revenue desc
+ limit 20;
+ """
+}
diff --git a/regression-test/suites/nereids_hint_tpch_p0/shape/q11.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q11.groovy
new file mode 100644
index 00000000000..feae344ee97
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q11.groovy
@@ -0,0 +1,67 @@
+/*
+ * 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("q11") {
+ 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 runtime_filter_mode=OFF'
+ sql 'set parallel_pipeline_task_num=8'
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+ sql 'set be_number_for_test=3'
+ sql "set runtime_filter_type=8"
+ sql 'set enable_runtime_filter_prune=false'
+
+
+
+ qt_select """
+ explain shape plan
+ select
+ /*+ leading(partsupp {supplier nation}) */
+ ps_partkey,
+ sum(ps_supplycost * ps_availqty) as value
+ from
+ partsupp,
+ supplier,
+ nation
+ where
+ ps_suppkey = s_suppkey
+ and s_nationkey = n_nationkey
+ and n_name = 'GERMANY'
+ group by
+ ps_partkey having
+ sum(ps_supplycost * ps_availqty) > (
+ select
+ /*+ leading(partsupp {supplier nation}) */
+ sum(ps_supplycost * ps_availqty) * 0.000002
+ from
+ partsupp,
+ supplier,
+ nation
+ where
+ ps_suppkey = s_suppkey
+ and s_nationkey = n_nationkey
+ and n_name = 'GERMANY'
+ )
+ order by
+ value desc;
+ """
+}
diff --git a/regression-test/suites/nereids_hint_tpch_p0/shape/q12.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q12.groovy
new file mode 100644
index 00000000000..c9d1f3a9096
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q12.groovy
@@ -0,0 +1,67 @@
+/*
+ * 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("q12") {
+ 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 runtime_filter_mode=OFF'
+ sql 'set parallel_pipeline_task_num=8'
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ qt_select """
+ explain shape plan
+ select
+ /*+ leading(orders lineitem) */
+ l_shipmode,
+ sum(case
+ when o_orderpriority = '1-URGENT'
+ or o_orderpriority = '2-HIGH'
+ then 1
+ else 0
+ end) as high_line_count,
+ sum(case
+ when o_orderpriority <> '1-URGENT'
+ and o_orderpriority <> '2-HIGH'
+ then 1
+ else 0
+ end) as low_line_count
+ from
+ orders,
+ lineitem
+ where
+ o_orderkey = l_orderkey
+ and l_shipmode in ('MAIL', 'SHIP')
+ and l_commitdate < l_receiptdate
+ and l_shipdate < l_commitdate
+ and l_receiptdate >= date '1994-01-01'
+ and l_receiptdate < date '1994-01-01' + interval '1' year
+ group by
+ l_shipmode
+ order by
+ l_shipmode;
+ """
+}
diff --git a/regression-test/suites/nereids_hint_tpch_p0/shape/q13.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q13.groovy
new file mode 100644
index 00000000000..d1633cff50d
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q13.groovy
@@ -0,0 +1,56 @@
+/*
+ * 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("q13") {
+ 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 runtime_filter_mode=OFF'
+ sql 'set parallel_pipeline_task_num=8'
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+sql 'set be_number_for_test=3'
+
+ qt_select """
+ explain shape plan
+ select
+ /*+ leading(orders shuffle customer) */
+ c_count,
+ count(*) as custdist
+ from
+ (
+ select
+ c_custkey,
+ count(o_orderkey) as c_count
+ from
+ customer left outer join orders on
+ c_custkey = o_custkey
+ and o_comment not like '%special%requests%'
+ group by
+ c_custkey
+ ) as c_orders
+ group by
+ c_count
+ order by
+ custdist desc,
+ c_count desc;
+ """
+}
diff --git a/regression-test/suites/nereids_hint_tpch_p0/shape/q14.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q14.groovy
new file mode 100644
index 00000000000..fd8c7f12b04
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q14.groovy
@@ -0,0 +1,49 @@
+/*
+ * 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("q14") {
+ 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 runtime_filter_mode=OFF'
+ sql 'set parallel_pipeline_task_num=8'
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+sql 'set be_number_for_test=3'
+
+ qt_select """
+ explain shape plan
+ select
+ /*+ leading(part lineitem) */
+ 100.00 * sum(case
+ when p_type like 'PROMO%'
+ then l_extendedprice * (1 - l_discount)
+ else 0
+ end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
+ from
+ lineitem,
+ part
+ where
+ l_partkey = p_partkey
+ and l_shipdate >= date '1995-09-01'
+ and l_shipdate < date '1995-09-01' + interval '1' month;
+ """
+}
diff --git a/regression-test/suites/nereids_hint_tpch_p0/shape/q15.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q15.groovy
new file mode 100644
index 00000000000..23cf07c71e1
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q15.groovy
@@ -0,0 +1,60 @@
+/*
+ * 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("q15") {
+ 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 runtime_filter_mode=OFF'
+
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+ sql 'set parallel_pipeline_task_num=8'
+
+
+
+ sql 'set be_number_for_test=3'
+ sql "set runtime_filter_type=8"
+ sql 'set enable_runtime_filter_prune=false'
+
+ qt_select """
+ explain shape plan
+ select
+ /*+ leading(revenue0 supplier) */
+ s_suppkey,
+ s_name,
+ s_address,
+ s_phone,
+ total_revenue
+ from
+ supplier,
+ revenue0
+ where
+ s_suppkey = supplier_no
+ and total_revenue = (
+ select
+ max(total_revenue)
+ from
+ revenue0
+ )
+ order by
+ s_suppkey;
+ """
+}
diff --git a/regression-test/suites/nereids_hint_tpch_p0/shape/q17.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q17.groovy
new file mode 100644
index 00000000000..849f120910a
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q17.groovy
@@ -0,0 +1,58 @@
+/*
+ * 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("q17") {
+ 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 runtime_filter_mode=OFF'
+
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+ sql 'set parallel_pipeline_task_num=8'
+
+
+
+ sql 'set be_number_for_test=3'
+ sql "set runtime_filter_type=8"
+ sql 'set enable_runtime_filter_prune=false'
+
+ qt_select """
+ explain shape plan
+ select
+ /*+ leading(lineitem broadcast part) */
+ sum(l_extendedprice) / 7.0 as avg_yearly
+ from
+ lineitem,
+ part
+ where
+ p_partkey = l_partkey
+ and p_brand = 'Brand#23'
+ and p_container = 'MED BOX'
+ and l_quantity < (
+ select
+ 0.2 * avg(l_quantity)
+ from
+ lineitem
+ where
+ l_partkey = p_partkey
+ );
+ """
+}
diff --git a/regression-test/suites/nereids_hint_tpch_p0/shape/q19.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q19.groovy
new file mode 100644
index 00000000000..db30a98aff9
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q19.groovy
@@ -0,0 +1,76 @@
+/*
+ * 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("q19") {
+ 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 runtime_filter_mode=OFF'
+ sql 'set parallel_pipeline_task_num=8'
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ qt_select """
+ explain shape plan
+ select
+ /*+ leading(lineitem broadcast part) */
+ sum(l_extendedprice* (1 - l_discount)) as revenue
+ from
+ lineitem,
+ part
+ where
+ (
+ p_partkey = l_partkey
+ and p_brand = 'Brand#12'
+ and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
+ and l_quantity >= 1 and l_quantity <= 1 + 10
+ and p_size between 1 and 5
+ and l_shipmode in ('AIR', 'AIR REG')
+ and l_shipinstruct = 'DELIVER IN PERSON'
+ )
+ or
+ (
+ p_partkey = l_partkey
+ and p_brand = 'Brand#23'
+ and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED
PACK')
+ and l_quantity >= 10 and l_quantity <= 10 + 10
+ and p_size between 1 and 10
+ and l_shipmode in ('AIR', 'AIR REG')
+ and l_shipinstruct = 'DELIVER IN PERSON'
+ )
+ or
+ (
+ p_partkey = l_partkey
+ and p_brand = 'Brand#34'
+ and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
+ and l_quantity >= 20 and l_quantity <= 20 + 10
+ and p_size between 1 and 15
+ and l_shipmode in ('AIR', 'AIR REG')
+ and l_shipinstruct = 'DELIVER IN PERSON'
+ );
+
+ """
+}
diff --git a/regression-test/suites/nereids_hint_tpch_p0/shape/q3.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q3.groovy
new file mode 100644
index 00000000000..d6219050707
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q3.groovy
@@ -0,0 +1,66 @@
+/*
+ * 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("q3") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ // db = "tpch"
+ 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 enable_pipeline_engine = true'
+ sql 'set parallel_pipeline_task_num=8'
+ sql 'set runtime_filter_mode=OFF'
+
+
+
+
+
+sql 'set be_number_for_test=3'
+
+
+ qt_select """
+ explain shape plan
+ select
+ /*+ leading(lineitem {orders shuffle customer}) */
+ l_orderkey,
+ sum(l_extendedprice * (1 - l_discount)) as revenue,
+ o_orderdate,
+ o_shippriority
+ from
+ customer,
+ orders,
+ lineitem
+ where
+ c_mktsegment = 'BUILDING'
+ and c_custkey = o_custkey
+ and l_orderkey = o_orderkey
+ and o_orderdate < date '1995-03-15'
+ and l_shipdate > date '1995-03-15'
+ group by
+ l_orderkey,
+ o_orderdate,
+ o_shippriority
+ order by
+ revenue desc,
+ o_orderdate
+ limit 10;
+ """
+}
diff --git a/regression-test/suites/nereids_hint_tpch_p0/shape/q4.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q4.groovy
new file mode 100644
index 00000000000..fbe312ef45b
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q4.groovy
@@ -0,0 +1,62 @@
+/*
+ * 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("q4") {
+ 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 enable_pipeline_engine = true'
+ sql 'set parallel_pipeline_task_num=8'
+ sql 'set runtime_filter_mode=OFF'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+
+ qt_select """
+ explain shape plan
+ select
+ /*+ leading(lineitem orders) */
+ o_orderpriority,
+ count(*) as order_count
+ from
+ orders
+ where
+ o_orderdate >= date '1993-07-01'
+ and o_orderdate < date '1993-07-01' + interval '3' month
+ and exists (
+ select
+ *
+ from
+ lineitem
+ where
+ l_orderkey = o_orderkey
+ and l_commitdate < l_receiptdate
+ )
+ group by
+ o_orderpriority
+ order by
+ o_orderpriority;
+ """
+}
diff --git a/regression-test/suites/nereids_hint_tpch_p0/shape/q5.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q5.groovy
new file mode 100644
index 00000000000..f70de90b0e1
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q5.groovy
@@ -0,0 +1,59 @@
+/*
+ * 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("q5") {
+ 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 runtime_filter_mode=OFF'
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+ sql 'set parallel_pipeline_task_num=8'
+sql 'set be_number_for_test=3'
+
+ qt_select """
+ explain shape plan
+ select
+ /*+ leading(lineitem orders broadcast {supplier broadcast {nation
broadcast region}} shuffle customer) */
+ n_name,
+ sum(l_extendedprice * (1 - l_discount)) as revenue
+ from
+ customer,
+ orders,
+ lineitem,
+ supplier,
+ nation,
+ region
+ where
+ c_custkey = o_custkey
+ and l_orderkey = o_orderkey
+ and l_suppkey = s_suppkey
+ and c_nationkey = s_nationkey
+ and s_nationkey = n_nationkey
+ and n_regionkey = r_regionkey
+ and r_name = 'ASIA'
+ and o_orderdate >= date '1994-01-01'
+ and o_orderdate < date '1994-01-01' + interval '1' year
+ group by
+ n_name
+ order by
+ revenue desc;
+ """
+}
diff --git a/regression-test/suites/nereids_hint_tpch_p0/shape/q7.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q7.groovy
new file mode 100644
index 00000000000..502b23873bf
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q7.groovy
@@ -0,0 +1,74 @@
+/*
+ * 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("q7") {
+ 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 runtime_filter_mode=OFF'
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+ sql 'set parallel_pipeline_task_num=8'
+sql 'set be_number_for_test=3'
+
+ qt_select """
+ explain shape plan
+ select
+ /*+ leading( lineitem broadcast {supplier broadcast n1} {orders shuffle
{customer broadcast n2}}) */
+ supp_nation,
+ cust_nation,
+ l_year,
+ sum(volume) as revenue
+ from
+ (
+ select
+ n1.n_name as supp_nation,
+ n2.n_name as cust_nation,
+ extract(year from l_shipdate) as l_year,
+ l_extendedprice * (1 - l_discount) as volume
+ from
+ supplier,
+ lineitem,
+ orders,
+ customer,
+ nation n1,
+ nation n2
+ where
+ s_suppkey = l_suppkey
+ and o_orderkey = l_orderkey
+ and c_custkey = o_custkey
+ and s_nationkey = n1.n_nationkey
+ and c_nationkey = n2.n_nationkey
+ and (
+ (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
+ or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
+ )
+ and l_shipdate between date '1995-01-01' and date '1996-12-31'
+ ) as shipping
+ group by
+ supp_nation,
+ cust_nation,
+ l_year
+ order by
+ supp_nation,
+ cust_nation,
+ l_year;
+ """
+}
diff --git a/regression-test/suites/nereids_hint_tpch_p0/shape/q8.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q8.groovy
new file mode 100644
index 00000000000..a0a8dc0c35d
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q8.groovy
@@ -0,0 +1,78 @@
+/*
+ * 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("q8") {
+ 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 runtime_filter_mode=OFF'
+
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+ sql 'set parallel_pipeline_task_num=8'
+
+
+
+ sql 'set be_number_for_test=3'
+ sql "set runtime_filter_type=8"
+ sql 'set enable_runtime_filter_prune=false'
+
+ qt_select """
+ explain shape plan
+ select
+ /*+ leading( supplier { orders {lineitem broadcast part} {customer
broadcast {n1 broadcast region}}} broadcast n2) */
+ o_year,
+ sum(case
+ when nation = 'BRAZIL' then volume
+ else 0
+ end) / sum(volume) as mkt_share
+ from
+ (
+ select
+ extract(year from o_orderdate) as o_year,
+ l_extendedprice * (1 - l_discount) as volume,
+ n2.n_name as nation
+ from
+ part,
+ supplier,
+ lineitem,
+ orders,
+ customer,
+ nation n1,
+ nation n2,
+ region
+ where
+ p_partkey = l_partkey
+ and s_suppkey = l_suppkey
+ and l_orderkey = o_orderkey
+ and o_custkey = c_custkey
+ and c_nationkey = n1.n_nationkey
+ and n1.n_regionkey = r_regionkey
+ and r_name = 'AMERICA'
+ and s_nationkey = n2.n_nationkey
+ and o_orderdate between date '1995-01-01' and date '1996-12-31'
+ and p_type = 'ECONOMY ANODIZED STEEL'
+ ) as all_nations
+ group by
+ o_year
+ order by
+ o_year;
+ """
+}
diff --git a/regression-test/suites/nereids_hint_tpch_p0/shape/q9.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q9.groovy
new file mode 100644
index 00000000000..2cb21f62ced
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q9.groovy
@@ -0,0 +1,68 @@
+/*
+ * 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("q9") {
+ 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 runtime_filter_mode=OFF'
+ sql 'set parallel_pipeline_task_num=8'
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+sql 'set be_number_for_test=3'
+
+ qt_select """
+ explain shape plan
+ select
+ nation,
+ o_year,
+ sum(amount) as sum_profit
+ from
+ (
+ select
+ /*+ leading(orders shuffle {lineitem shuffle part} shuffle
{supplier broadcast nation} shuffle partsupp) */
+ n_name as nation,
+ extract(year from o_orderdate) as o_year,
+ l_extendedprice * (1 - l_discount) - ps_supplycost *
l_quantity as amount
+ from
+ part,
+ supplier,
+ lineitem,
+ partsupp,
+ orders,
+ nation
+ where
+ s_suppkey = l_suppkey
+ and ps_suppkey = l_suppkey
+ and ps_partkey = l_partkey
+ and p_partkey = l_partkey
+ and o_orderkey = l_orderkey
+ and s_nationkey = n_nationkey
+ and p_name like '%green%'
+ ) as profit
+ group by
+ nation,
+ o_year
+ order by
+ nation,
+ o_year desc;
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
