This is an automated email from the ASF dual-hosted git repository.
morrysnow 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 3b9ae91910d [Fix](Nereids) fix test leading suite and add tpch shape
checking base on leading (#25842)
3b9ae91910d is described below
commit 3b9ae91910db8752cc6292235d625b4ecf45ecbf
Author: LiBinfeng <[email protected]>
AuthorDate: Wed Oct 25 14:10:14 2023 +0800
[Fix](Nereids) fix test leading suite and add tpch shape checking base on
leading (#25842)
- fix test leading suite caused by sessionvariable setting error
- add tpch shape checking base on leading
---
.../org/apache/doris/nereids/hint/LeadingHint.java | 2 +-
.../java/org/apache/doris/qe/SessionVariable.java | 2 +-
.../data/nereids_hint_tpch_p0/shape/q1.out | 13 ++
.../data/nereids_hint_tpch_p0/shape/q10.out | 34 ++++
.../data/nereids_hint_tpch_p0/shape/q11.out | 46 +++++
.../data/nereids_hint_tpch_p0/shape/q12.out | 21 ++
.../data/nereids_hint_tpch_p0/shape/q13.out | 25 +++
.../data/nereids_hint_tpch_p0/shape/q14.out | 20 ++
.../data/nereids_hint_tpch_p0/shape/q15.out | 31 +++
.../data/nereids_hint_tpch_p0/shape/q16.out | 27 +++
.../data/nereids_hint_tpch_p0/shape/q17.out | 21 ++
.../data/nereids_hint_tpch_p0/shape/q18.out | 26 +++
.../data/nereids_hint_tpch_p0/shape/q19.out | 21 ++
.../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 | 28 +++
.../data/nereids_hint_tpch_p0/shape/q4.out | 22 ++
.../data/nereids_hint_tpch_p0/shape/q5.out | 48 +++++
.../data/nereids_hint_tpch_p0/shape/q6.out | 10 +
.../data/nereids_hint_tpch_p0/shape/q7.out | 44 ++++
.../data/nereids_hint_tpch_p0/shape/q8.out | 49 +++++
.../data/nereids_hint_tpch_p0/shape/q9.out | 46 +++++
.../data/nereids_p0/hint/test_leading.out | 50 ++---
.../suites/nereids_hint_tpch_p0/load.groovy | 223 +++++++++++++++++++++
.../suites/nereids_hint_tpch_p0/shape/q1.groovy | 56 ++++++
.../suites/nereids_hint_tpch_p0/shape/q10.groovy | 71 +++++++
.../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/q16.groovy | 69 +++++++
.../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 | 60 ++++++
.../suites/nereids_hint_tpch_p0/shape/q6.groovy | 44 ++++
.../suites/nereids_hint_tpch_p0/shape/q9.groovy | 68 +++++++
38 files changed, 1661 insertions(+), 27 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 af5c4f500fc..c063c08a324 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
@@ -484,7 +484,7 @@ public class LeadingHint extends Hint {
this.setErrorMessage("duplicated table");
return totalBitmap;
}
- if (getTablelist().size() != tables.size()) {
+ if (tables != null && getTablelist().size() != tables.size()) {
this.setStatus(HintStatus.SYNTAX_ERROR);
this.setErrorMessage("tables should be same as join tables");
return totalBitmap;
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
index e5047953ab0..70882c3bb65 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
@@ -2676,7 +2676,7 @@ public class SessionVariable implements Serializable,
Writable {
return;
}
setIsSingleSetVar(true);
- VariableMgr.setVar(this, new
SetVar(SessionVariable.DISABLE_JOIN_REORDER, new StringLiteral("false")));
+ VariableMgr.setVar(this, new
SetVar(SessionVariable.DISABLE_JOIN_REORDER, new StringLiteral("true")));
}
// return number of variables by given variable annotation
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q1.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q1.out
new file mode 100644
index 00000000000..6d8f63d340c
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q1.out
@@ -0,0 +1,13 @@
+-- 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
+----------------filter((lineitem.l_shipdate <= '1998-09-02'))
+------------------PhysicalOlapScan[lineitem]
+
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q10.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q10.out
new file mode 100644
index 00000000000..8a594b075b7
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q10.out
@@ -0,0 +1,34 @@
+-- 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] hashCondition=((lineitem.l_orderkey =
orders.o_orderkey))otherCondition=()
+------------------PhysicalProject
+--------------------filter((lineitem.l_returnflag = 'R'))
+----------------------PhysicalOlapScan[lineitem]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_nationkey = nation.n_nationkey))otherCondition=()
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN]
hashCondition=((customer.c_custkey = orders.o_custkey))otherCondition=()
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[customer]
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------filter((orders.o_orderdate < '1994-01-01')
and (orders.o_orderdate >= '1993-10-01'))
+------------------------------------PhysicalOlapScan[orders]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[nation]
+
+Used: leading(lineitem { { customer orders } 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
new file mode 100644
index 00000000000..eea366f8423
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q11.out
@@ -0,0 +1,46 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalResultSink
+--PhysicalQuickSort
+----PhysicalDistribute
+------PhysicalQuickSort
+--------PhysicalProject
+----------NestedLoopJoin[INNER_JOIN](cast(value as DOUBLE) >
cast((sum((ps_supplycost * ps_availqty)) * 0.000002) as DOUBLE))
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN](partsupp.ps_suppkey =
supplier.s_suppkey)
+----------------------PhysicalDistribute
+------------------------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]
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](partsupp.ps_suppkey =
supplier.s_suppkey)
+----------------------------PhysicalDistribute
+------------------------------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]
+
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q12.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q12.out
new file mode 100644
index 00000000000..6c73fa0d7fd
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q12.out
@@ -0,0 +1,21 @@
+-- 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[INNER_JOIN] hashCondition=((orders.o_orderkey =
lineitem.l_orderkey))otherCondition=()
+------------------PhysicalProject
+--------------------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)
+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
new file mode 100644
index 00000000000..1f0c4ce70b5
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q13.out
@@ -0,0 +1,25 @@
+-- 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
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------hashJoin[RIGHT_OUTER_JOIN]
hashCondition=((customer.c_custkey = orders.o_custkey))otherCondition=()
+----------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------filter(( not (o_comment like '%special%requests%')))
+----------------------------PhysicalOlapScan[orders]
+----------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[customer]
+
+Used: leading(orders 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
new file mode 100644
index 00000000000..2477855e425
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q14.out
@@ -0,0 +1,20 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((lineitem.l_partkey =
part.p_partkey))otherCondition=()
+--------------PhysicalProject
+----------------PhysicalOlapScan[part]
+--------------PhysicalDistribute
+----------------PhysicalProject
+------------------filter((lineitem.l_shipdate < '1995-10-01') and
(lineitem.l_shipdate >= '1995-09-01'))
+--------------------PhysicalOlapScan[lineitem]
+
+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
new file mode 100644
index 00000000000..4106594748a
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q15.out
@@ -0,0 +1,31 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalResultSink
+--PhysicalQuickSort
+----PhysicalDistribute
+------PhysicalQuickSort
+--------PhysicalProject
+----------hashJoin[INNER_JOIN](supplier.s_suppkey = revenue0.supplier_no)
+------------PhysicalProject
+--------------PhysicalOlapScan[supplier]
+------------PhysicalDistribute
+--------------hashJoin[INNER_JOIN](revenue0.total_revenue = max(total_revenue))
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------filter((lineitem.l_shipdate >=
1996-01-01)(lineitem.l_shipdate < 1996-04-01))
+--------------------------PhysicalOlapScan[lineitem]
+----------------PhysicalDistribute
+------------------PhysicalAssertNumRows
+--------------------hashAgg[GLOBAL]
+----------------------PhysicalDistribute
+------------------------hashAgg[LOCAL]
+--------------------------PhysicalProject
+----------------------------hashAgg[GLOBAL]
+------------------------------PhysicalDistribute
+--------------------------------hashAgg[LOCAL]
+----------------------------------PhysicalProject
+------------------------------------filter((lineitem.l_shipdate >=
1996-01-01)(lineitem.l_shipdate < 1996-04-01))
+--------------------------------------PhysicalOlapScan[lineitem]
+
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q16.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q16.out
new file mode 100644
index 00000000000..642ee4dc370
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q16.out
@@ -0,0 +1,27 @@
+-- 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]
+--------------hashJoin[LEFT_ANTI_JOIN] hashCondition=((partsupp.ps_suppkey =
supplier.s_suppkey))otherCondition=()
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN] hashCondition=((part.p_partkey =
partsupp.ps_partkey))otherCondition=()
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[partsupp]
+----------------------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
new file mode 100644
index 00000000000..65bad6d5980
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q17.out
@@ -0,0 +1,21 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalResultSink
+--PhysicalProject
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------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
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](part.p_partkey = lineitem.l_partkey)
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[lineitem]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter((part.p_container = 'MED BOX')(part.p_brand
= 'Brand#23'))
+------------------------------PhysicalOlapScan[part]
+
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q18.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q18.out
new file mode 100644
index 00000000000..ae401ef1cd4
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q18.out
@@ -0,0 +1,26 @@
+-- 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
new file mode 100644
index 00000000000..b4f4ba8c309
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q19.out
@@ -0,0 +1,21 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----PhysicalDistribute
+------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))) O [...]
+------------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]
+------------PhysicalDistribute
+--------------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)
+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
new file mode 100644
index 00000000000..ad98be8fe33
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q2.out
@@ -0,0 +1,39 @@
+-- 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
new file mode 100644
index 00000000000..6114877bc9d
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q20.out
@@ -0,0 +1,34 @@
+-- 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
new file mode 100644
index 00000000000..2f4348619b2
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q21.out
@@ -0,0 +1,34 @@
+-- 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
new file mode 100644
index 00000000000..09e3c287f6a
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q22.out
@@ -0,0 +1,28 @@
+-- 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
new file mode 100644
index 00000000000..cd78e320cf8
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q3.out
@@ -0,0 +1,28 @@
+-- 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] hashCondition=((lineitem.l_orderkey =
orders.o_orderkey))otherCondition=()
+--------------PhysicalProject
+----------------filter((lineitem.l_shipdate > '1995-03-15'))
+------------------PhysicalOlapScan[lineitem]
+--------------PhysicalDistribute
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN] hashCondition=((customer.c_custkey =
orders.o_custkey))otherCondition=()
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------filter((orders.o_orderdate < '1995-03-15'))
+--------------------------PhysicalOlapScan[orders]
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------filter((customer.c_mktsegment = 'BUILDING'))
+--------------------------PhysicalOlapScan[customer]
+
+Used: leading(lineitem { orders 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
new file mode 100644
index 00000000000..fdd19b87189
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q4.out
@@ -0,0 +1,22 @@
+-- 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_SEMI_JOIN] hashCondition=((lineitem.l_orderkey
= orders.o_orderkey))otherCondition=()
+------------------PhysicalProject
+--------------------filter((lineitem.l_commitdate < lineitem.l_receiptdate))
+----------------------PhysicalOlapScan[lineitem]
+------------------PhysicalProject
+--------------------filter((orders.o_orderdate < '1993-10-01') and
(orders.o_orderdate >= '1993-07-01'))
+----------------------PhysicalOlapScan[orders]
+
+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
new file mode 100644
index 00000000000..099a47a4923
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q5.out
@@ -0,0 +1,48 @@
+-- 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[INNER_JOIN] hashCondition=((customer.c_custkey =
orders.o_custkey) and (customer.c_nationkey =
supplier.s_nationkey))otherCondition=()
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN] hashCondition=((lineitem.l_orderkey
= orders.o_orderkey))otherCondition=()
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN]
hashCondition=((lineitem.l_suppkey = supplier.s_suppkey))otherCondition=()
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[lineitem]
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN]
hashCondition=((supplier.s_nationkey = nation.n_nationkey))otherCondition=()
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[supplier]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------hashJoin[INNER_JOIN]
hashCondition=((nation.n_regionkey = region.r_regionkey))otherCondition=()
+------------------------------------------PhysicalDistribute
+--------------------------------------------PhysicalProject
+----------------------------------------------PhysicalOlapScan[nation]
+------------------------------------------PhysicalDistribute
+--------------------------------------------PhysicalProject
+----------------------------------------------filter((region.r_name = 'ASIA'))
+------------------------------------------------PhysicalOlapScan[region]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter((orders.o_orderdate < '1995-01-01') and
(orders.o_orderdate >= '1994-01-01'))
+------------------------------PhysicalOlapScan[orders]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[customer]
+
+Used: leading(lineitem { supplier { nation region } } orders 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
new file mode 100644
index 00000000000..7976f47c6b4
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q6.out
@@ -0,0 +1,10 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalResultSink
+--hashAgg[GLOBAL]
+----PhysicalDistribute
+------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'))
+------------PhysicalOlapScan[lineitem]
+
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q7.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q7.out
new file mode 100644
index 00000000000..6facc13fb77
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q7.out
@@ -0,0 +1,44 @@
+-- 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[INNER_JOIN] hashCondition=((customer.c_custkey =
orders.o_custkey) and (customer.c_nationkey = n2.n_nationkey))otherCondition=()
+------------------PhysicalProject
+--------------------PhysicalOlapScan[customer]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN] hashCondition=((orders.o_orderkey =
lineitem.l_orderkey))otherCondition=()
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[orders]
+------------------------PhysicalDistribute
+--------------------------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
+------------------------------------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]
+
+Used:
+UnUsed:
+SyntaxError: leading(customer { orders { lineitem { supplier { n1 n2 } } } })
Msg:tables should be same as join tables
+
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q8.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q8.out
new file mode 100644
index 00000000000..07a9cd9ca20
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q8.out
@@ -0,0 +1,49 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalResultSink
+--PhysicalQuickSort
+----PhysicalDistribute
+------PhysicalQuickSort
+--------PhysicalProject
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN](supplier.s_nationkey = n2.n_nationkey)
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](supplier.s_suppkey =
lineitem.l_suppkey)
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[supplier]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN](n1.n_regionkey =
region.r_regionkey)
+------------------------------PhysicalProject
+--------------------------------hashJoin[INNER_JOIN](customer.c_nationkey =
n1.n_nationkey)
+----------------------------------PhysicalProject
+------------------------------------hashJoin[INNER_JOIN](orders.o_custkey =
customer.c_custkey)
+--------------------------------------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)
+----------------------------------------------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
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[nation]
+
diff --git a/regression-test/data/nereids_hint_tpch_p0/shape/q9.out
b/regression-test/data/nereids_hint_tpch_p0/shape/q9.out
new file mode 100644
index 00000000000..e442d4a9a3f
--- /dev/null
+++ b/regression-test/data/nereids_hint_tpch_p0/shape/q9.out
@@ -0,0 +1,46 @@
+-- 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[INNER_JOIN] hashCondition=((partsupp.ps_partkey =
lineitem.l_partkey) and (partsupp.ps_suppkey =
lineitem.l_suppkey))otherCondition=()
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN] hashCondition=((orders.o_orderkey =
lineitem.l_orderkey))otherCondition=()
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[orders]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------hashJoin[INNER_JOIN]
hashCondition=((supplier.s_suppkey = lineitem.l_suppkey))otherCondition=()
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN]
hashCondition=((part.p_partkey = lineitem.l_partkey))otherCondition=()
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[lineitem]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------filter((p_name like '%green%'))
+------------------------------------------PhysicalOlapScan[part]
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------hashJoin[INNER_JOIN]
hashCondition=((supplier.s_nationkey = nation.n_nationkey))otherCondition=()
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[supplier]
+------------------------------------PhysicalDistribute
+--------------------------------------PhysicalProject
+----------------------------------------PhysicalOlapScan[nation]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[partsupp]
+
+Used: leading(orders { { lineitem part } { supplier nation } } 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 ea760d8b2c6..3379b4a54eb 100644
--- a/regression-test/data/nereids_p0/hint/test_leading.out
+++ b/regression-test/data/nereids_p0/hint/test_leading.out
@@ -46,12 +46,12 @@ PhysicalResultSink
--PhysicalDistribute
----PhysicalProject
------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))otherCondition=()
---------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))otherCondition=()
-----------PhysicalOlapScan[t2]
-----------PhysicalDistribute
-------------PhysicalOlapScan[t3]
+--------PhysicalOlapScan[t1]
--------PhysicalDistribute
-----------PhysicalOlapScan[t1]
+----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))otherCondition=()
+------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalOlapScan[t3]
Used: leading(t1 { t2 t3 })
UnUsed:
@@ -63,12 +63,12 @@ PhysicalResultSink
----PhysicalProject
------hashJoin[INNER_JOIN] hashCondition=((t3.c3 = t4.c4))otherCondition=()
--------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))otherCondition=()
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))otherCondition=()
-------------PhysicalOlapScan[t2]
-------------PhysicalDistribute
---------------PhysicalOlapScan[t3]
+----------PhysicalOlapScan[t1]
----------PhysicalDistribute
-------------PhysicalOlapScan[t1]
+------------hashJoin[INNER_JOIN] hashCondition=((t2.c2 =
t3.c3))otherCondition=()
+--------------PhysicalOlapScan[t2]
+--------------PhysicalDistribute
+----------------PhysicalOlapScan[t3]
--------PhysicalDistribute
----------PhysicalOlapScan[t4]
@@ -191,12 +191,12 @@ PhysicalResultSink
--PhysicalDistribute
----PhysicalProject
------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))otherCondition=()
---------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))otherCondition=()
-----------PhysicalOlapScan[t2]
-----------PhysicalDistribute
-------------PhysicalOlapScan[t3]
+--------PhysicalOlapScan[t1]
--------PhysicalDistribute
-----------PhysicalOlapScan[t1]
+----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))otherCondition=()
+------------PhysicalOlapScan[t2]
+------------PhysicalDistribute
+--------------PhysicalOlapScan[t3]
Used: leading(t1 { t2 t3 })
UnUsed:
@@ -244,11 +244,11 @@ PhysicalResultSink
PhysicalResultSink
--PhysicalDistribute
----PhysicalProject
-------hashJoin[LEFT_SEMI_JOIN] hashCondition=((t1.c1 = t2.c2))otherCondition=()
---------PhysicalOlapScan[t1]
+------hashJoin[RIGHT_SEMI_JOIN] hashCondition=((t1.c1 =
t2.c2))otherCondition=()
+--------PhysicalProject
+----------PhysicalOlapScan[t2]
--------PhysicalDistribute
-----------PhysicalProject
-------------PhysicalOlapScan[t2]
+----------PhysicalOlapScan[t1]
Used: leading(t2 t1)
UnUsed:
@@ -272,10 +272,10 @@ PhysicalResultSink
--PhysicalDistribute
----PhysicalProject
------NestedLoopJoin[CROSS_JOIN]
---------PhysicalOlapScan[t1]
+--------PhysicalProject
+----------PhysicalOlapScan[t2]
--------PhysicalDistribute
-----------PhysicalProject
-------------PhysicalOlapScan[t2]
+----------PhysicalOlapScan[t1]
Used: leading(t2 t1)
UnUsed:
@@ -355,7 +355,7 @@ PhysicalResultSink
Used:
UnUsed:
-SyntaxError: leading(t2 cte t1) Msg:Leading alias can only be table name alias
+SyntaxError: leading(t2 cte t1) Msg:tables should be same as join tables
-- !select25 --
PhysicalResultSink
@@ -423,7 +423,7 @@ PhysicalResultSink
--------PhysicalOlapScan[t1]
--------PhysicalOlapScan[t1]
-Used: leading(t1 t_1)
+Used:
UnUsed:
-SyntaxError:
+SyntaxError: leading(t1 t_1) Msg:tables should be same as join tables
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/q1.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q1.groovy
new file mode 100644
index 00000000000..c3770a0ee25
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q1.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("q1") {
+ 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 be_number_for_test=3'
+
+
+ qt_select """
+ explain shape plan
+ select
+ l_returnflag,
+ l_linestatus,
+ sum(l_quantity) as sum_qty,
+ sum(l_extendedprice) as sum_base_price,
+ sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
+ sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
+ avg(l_quantity) as avg_qty,
+ avg(l_extendedprice) as avg_price,
+ avg(l_discount) as avg_disc,
+ count(*) as count_order
+ from
+ lineitem
+ where
+ l_shipdate <= date '1998-12-01' - interval '90' day
+ group by
+ l_returnflag,
+ l_linestatus
+ order by
+ l_returnflag,
+ l_linestatus;
+ """
+}
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..cb32d84dcc6
--- /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='GLOBAL'"
+ 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 {{customer orders} 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/q12.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q12.groovy
new file mode 100644
index 00000000000..dea674fbcba
--- /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='GLOBAL'"
+ 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..21cef2b8d89
--- /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='GLOBAL'"
+ 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 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..c4d4724d947
--- /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='GLOBAL'"
+ 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/q16.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q16.groovy
new file mode 100644
index 00000000000..c9eda9f2235
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q16.groovy
@@ -0,0 +1,69 @@
+/*
+ * 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("q16") {
+ 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='GLOBAL'"
+ 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(partsupp part supplier) */
+ p_brand,
+ p_type,
+ p_size,
+ count(distinct ps_suppkey) as supplier_cnt
+ from
+ partsupp,
+ part
+ where
+ p_partkey = ps_partkey
+ and p_brand <> 'Brand#45'
+ and p_type not like 'MEDIUM POLISHED%'
+ and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
+ and ps_suppkey not in (
+ select
+ s_suppkey
+ from
+ supplier
+ where
+ s_comment like '%Customer%Complaints%'
+ )
+ group by
+ p_brand,
+ p_type,
+ p_size
+ order by
+ supplier_cnt desc,
+ p_brand,
+ p_type,
+ p_size;
+ """
+}
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..9fb43f53b4c
--- /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='GLOBAL'"
+ 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 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..01ba4c19fb4
--- /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 runtime_filter_mode='GLOBAL'"
+
+ 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 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..f07588153ee
--- /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 runtime_filter_mode='GLOBAL'"
+
+ 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) */
+ 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..606e5a951fb
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q5.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("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='GLOBAL'"
+
+ 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 {supplier {nation region}} orders 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/q6.groovy
b/regression-test/suites/nereids_hint_tpch_p0/shape/q6.groovy
new file mode 100644
index 00000000000..09d3c26e0ce
--- /dev/null
+++ b/regression-test/suites/nereids_hint_tpch_p0/shape/q6.groovy
@@ -0,0 +1,44 @@
+/*
+ * 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("q6") {
+ 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='GLOBAL'"
+
+ sql 'set global 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
+ sum(l_extendedprice * l_discount) as revenue
+ from
+ lineitem
+ where
+ l_shipdate >= date '1994-01-01'
+ and l_shipdate < date '1994-01-01' + interval '1' year
+ and l_discount between .06 - 0.01 and .06 + 0.01
+ and l_quantity < 24;
+ """
+}
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..b5fe1ea9533
--- /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='GLOBAL'"
+ 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 part} {supplier nation}} partsupp) */
+ nation,
+ o_year,
+ sum(amount) as sum_profit
+ from
+ (
+ select
+ 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]
