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 b93e04ab66 [test](Nereids) add regression test to check join order for
tpch queries (#18543)
b93e04ab66 is described below
commit b93e04ab666a61da8f5a233c34fe5a2a2e311897
Author: minghong <engle...@gmail.com>
AuthorDate: Wed Apr 12 15:43:21 2023 +0800
[test](Nereids) add regression test to check join order for tpch queries
(#18543)
by explain shape plan command, with stats injection, we add regression test
to check tpch queries' plan shape.
---
.../doris/nereids/stats/StatsCalculator.java | 1 +
.../doris/statistics/StatisticsRepository.java | 4 +-
.../data/nereids_tpchPlanShape_p0/shape/q1.out | 12 +
.../data/nereids_tpchPlanShape_p0/shape/q10.out | 27 ++
.../data/nereids_tpchPlanShape_p0/shape/q11.out | 39 ++
.../data/nereids_tpchPlanShape_p0/shape/q12.out | 16 +
.../data/nereids_tpchPlanShape_p0/shape/q13.out | 20 +
.../data/nereids_tpchPlanShape_p0/shape/q14.out | 15 +
.../data/nereids_tpchPlanShape_p0/shape/q15.out | 30 ++
.../data/nereids_tpchPlanShape_p0/shape/q16.out | 22 +
.../data/nereids_tpchPlanShape_p0/shape/q17.out | 20 +
.../data/nereids_tpchPlanShape_p0/shape/q18.out | 27 ++
.../data/nereids_tpchPlanShape_p0/shape/q19.out | 14 +
.../data/nereids_tpchPlanShape_p0/shape/q2.out | 30 ++
.../data/nereids_tpchPlanShape_p0/shape/q20.out | 32 ++
.../data/nereids_tpchPlanShape_p0/shape/q21.out | 32 ++
.../data/nereids_tpchPlanShape_p0/shape/q22.out | 26 ++
.../data/nereids_tpchPlanShape_p0/shape/q3.out | 21 +
.../data/nereids_tpchPlanShape_p0/shape/q4.out | 17 +
.../data/nereids_tpchPlanShape_p0/shape/q5.out | 35 ++
.../data/nereids_tpchPlanShape_p0/shape/q6.out | 9 +
.../data/nereids_tpchPlanShape_p0/shape/q7.out | 36 ++
.../data/nereids_tpchPlanShape_p0/shape/q8.out | 48 +++
.../data/nereids_tpchPlanShape_p0/shape/q9.out | 35 ++
.../suites/nereids_tpchPlanShape_p0/load.groovy | 467 +++++++++++++++++++++
.../nereids_tpchPlanShape_p0/shape/q1.groovy | 52 +++
.../nereids_tpchPlanShape_p0/shape/q10.groovy | 64 +++
.../nereids_tpchPlanShape_p0/shape/q11.groovy | 59 +++
.../nereids_tpchPlanShape_p0/shape/q12.groovy | 60 +++
.../nereids_tpchPlanShape_p0/shape/q13.groovy | 52 +++
.../nereids_tpchPlanShape_p0/shape/q14.groovy | 45 ++
.../nereids_tpchPlanShape_p0/shape/q15.groovy | 51 +++
.../nereids_tpchPlanShape_p0/shape/q16.groovy | 62 +++
.../nereids_tpchPlanShape_p0/shape/q17.groovy | 49 +++
.../nereids_tpchPlanShape_p0/shape/q18.groovy | 65 +++
.../nereids_tpchPlanShape_p0/shape/q19.groovy | 69 +++
.../nereids_tpchPlanShape_p0/shape/q2.groovy | 76 ++++
.../nereids_tpchPlanShape_p0/shape/q20.groovy | 70 +++
.../nereids_tpchPlanShape_p0/shape/q21.groovy | 72 ++++
.../nereids_tpchPlanShape_p0/shape/q22.groovy | 69 +++
.../nereids_tpchPlanShape_p0/shape/q3.groovy | 56 +++
.../nereids_tpchPlanShape_p0/shape/q4.groovy | 53 +++
.../nereids_tpchPlanShape_p0/shape/q5.groovy | 56 +++
.../nereids_tpchPlanShape_p0/shape/q6.groovy | 41 ++
.../nereids_tpchPlanShape_p0/shape/q7.groovy | 71 ++++
.../nereids_tpchPlanShape_p0/shape/q8.groovy | 69 +++
.../nereids_tpchPlanShape_p0/shape/q9.groovy | 64 +++
47 files changed, 2359 insertions(+), 1 deletion(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
index 75b637890d..5be38b5dde 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
@@ -438,6 +438,7 @@ public class StatsCalculator extends
DefaultPlanVisitor<Statistics, Void> {
columnStatisticMap.put(slotReference, cache);
continue;
}
+ rowCount = Math.max(rowCount, cache.count);
Histogram histogram =
Env.getCurrentEnv().getStatisticsCache().getHistogram(table.getId(), colName);
if (histogram != null) {
ColumnStatisticBuilder columnStatisticBuilder =
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/statistics/StatisticsRepository.java
b/fe/fe-core/src/main/java/org/apache/doris/statistics/StatisticsRepository.java
index 9a735cf98a..9199c74b25 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/statistics/StatisticsRepository.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/statistics/StatisticsRepository.java
@@ -233,7 +233,9 @@ public class StatisticsRepository {
builder.setCount(Double.parseDouble(rowCount));
}
if (ndv != null) {
- builder.setNdv(Double.parseDouble(ndv));
+ Double dNdv = Double.parseDouble(ndv);
+ builder.setNdv(dNdv);
+ builder.setOriginalNdv(dNdv);
}
if (nullCount != null) {
builder.setNumNulls(Double.parseDouble(nullCount));
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q1.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q1.out
new file mode 100644
index 0000000000..00a7cf50f8
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q1.out
@@ -0,0 +1,12 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+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_tpchPlanShape_p0/shape/q10.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q10.out
new file mode 100644
index 0000000000..5cdb232001
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q10.out
@@ -0,0 +1,27 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalTopN
+--PhysicalDistribute
+----PhysicalTopN
+------PhysicalProject
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN](lineitem.l_orderkey = orders.o_orderkey)
+------------------PhysicalProject
+--------------------filter((lineitem.l_returnflag = 'R'))
+----------------------PhysicalOlapScan[lineitem]
+------------------PhysicalDistribute
+--------------------hashJoin[INNER_JOIN](customer.c_nationkey =
nation.n_nationkey)
+----------------------hashJoin[INNER_JOIN](customer.c_custkey =
orders.o_custkey)
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[customer]
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter((orders.o_orderdate <
1994-01-01)(orders.o_orderdate >= 1993-10-01))
+------------------------------PhysicalOlapScan[orders]
+----------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[nation]
+
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q11.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q11.out
new file mode 100644
index 0000000000..b076fb1121
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q11.out
@@ -0,0 +1,39 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalQuickSort
+--PhysicalDistribute
+----PhysicalQuickSort
+------PhysicalProject
+--------NestedLoopJoin[INNER_JOIN](value > (sum((ps_supplycost * ps_availqty))
* 0.000002))
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN](partsupp.ps_suppkey = supplier.s_suppkey)
+----------------PhysicalProject
+------------------PhysicalOlapScan[partsupp]
+----------------PhysicalDistribute
+------------------hashJoin[INNER_JOIN](supplier.s_nationkey =
nation.n_nationkey)
+--------------------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)
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[partsupp]
+--------------------------PhysicalDistribute
+----------------------------hashJoin[INNER_JOIN](supplier.s_nationkey =
nation.n_nationkey)
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[supplier]
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------filter((nation.n_name = 'GERMANY'))
+------------------------------------PhysicalOlapScan[nation]
+
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q12.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q12.out
new file mode 100644
index 0000000000..6dd416cefa
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q12.out
@@ -0,0 +1,16 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalQuickSort
+--PhysicalDistribute
+----PhysicalQuickSort
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN](orders.o_orderkey = lineitem.l_orderkey)
+----------------PhysicalProject
+------------------PhysicalOlapScan[orders]
+----------------PhysicalProject
+------------------filter(((lineitem.l_shipmode = 'MAIL') OR
(lineitem.l_shipmode = 'SHIP'))(lineitem.l_shipdate <
lineitem.l_commitdate)(lineitem.l_receiptdate <
1995-01-01)(lineitem.l_receiptdate >= 1994-01-01)(lineitem.l_commitdate <
lineitem.l_receiptdate))
+--------------------PhysicalOlapScan[lineitem]
+
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q13.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q13.out
new file mode 100644
index 0000000000..6df8a92b08
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q13.out
@@ -0,0 +1,20 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalQuickSort
+--PhysicalDistribute
+----PhysicalQuickSort
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashAgg[GLOBAL]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------hashJoin[RIGHT_OUTER_JOIN](customer.c_custkey =
orders.o_custkey)
+----------------------PhysicalDistribute
+------------------------PhysicalProject
+--------------------------filter(( not (o_comment like '%special%requests%')))
+----------------------------PhysicalOlapScan[orders]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[customer]
+
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q14.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q14.out
new file mode 100644
index 0000000000..9ec9d4f3fa
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q14.out
@@ -0,0 +1,15 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalProject
+--hashAgg[GLOBAL]
+----PhysicalDistribute
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN](lineitem.l_partkey = part.p_partkey)
+------------PhysicalProject
+--------------PhysicalOlapScan[part]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------filter((lineitem.l_shipdate < 1995-10-01)(lineitem.l_shipdate
>= 1995-09-01))
+------------------PhysicalOlapScan[lineitem]
+
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q15.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q15.out
new file mode 100644
index 0000000000..da9dd396be
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q15.out
@@ -0,0 +1,30 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+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_tpchPlanShape_p0/shape/q16.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q16.out
new file mode 100644
index 0000000000..3b28129f40
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q16.out
@@ -0,0 +1,22 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalQuickSort
+--PhysicalDistribute
+----PhysicalQuickSort
+------hashAgg[DISTINCT_LOCAL]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[LEFT_ANTI_JOIN](partsupp.ps_suppkey =
supplier.s_suppkey)
+------------------hashJoin[INNER_JOIN](part.p_partkey = partsupp.ps_partkey)
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[partsupp]
+--------------------PhysicalProject
+----------------------filter(( not (p_type like 'MEDIUM POLISHED%'))( not
(p_brand = 'Brand#45'))p_size IN (3, 9, 14, 19, 23, 36, 45, 49))
+------------------------PhysicalOlapScan[part]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------filter((s_comment like '%Customer%Complaints%'))
+------------------------PhysicalOlapScan[supplier]
+
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q17.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q17.out
new file mode 100644
index 0000000000..833dd1a625
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q17.out
@@ -0,0 +1,20 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalProject
+--hashAgg[GLOBAL]
+----PhysicalDistribute
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------filter((cast(l_quantity as DECIMAL(27, 9)) < (avg(l_quantity)
OVER(PARTITION BY p_partkey) * 0.200000000)))
+------------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_tpchPlanShape_p0/shape/q18.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q18.out
new file mode 100644
index 0000000000..299b4bb581
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q18.out
@@ -0,0 +1,27 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalTopN
+--PhysicalDistribute
+----PhysicalTopN
+------hashAgg[GLOBAL]
+--------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.000000000))
+----------------------------hashAgg[GLOBAL]
+------------------------------hashAgg[LOCAL]
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[lineitem]
+
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q19.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q19.out
new file mode 100644
index 0000000000..f53d475bb5
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q19.out
@@ -0,0 +1,14 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+hashAgg[LOCAL]
+--PhysicalDistribute
+----PhysicalProject
+------hashJoin[INNER_JOIN](part.p_partkey =
lineitem.l_partkey)((((((part.p_brand = 'Brand#12') AND p_container IN ('SM
CASE', 'SM BOX', '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 PKG', 'MED PACK'))
AND ((lineitem.l_quantity >= 10.00) AND (lineitem.l_quantity <= 20.00))) AND
(part.p_size <= 10))) OR ((((part.p_brand = 'Brand#34') AND [...]
+--------PhysicalProject
+----------filter((lineitem.l_shipinstruct = 'DELIVER IN
PERSON')((((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)))((lineitem.l_shipmode = 'AIR') OR (lineitem.l_shipmode = 'AIR REG')))
+------------PhysicalOlapScan[lineitem]
+--------PhysicalDistribute
+----------PhysicalProject
+------------filter((((((part.p_brand = 'Brand#12') AND p_container IN ('SM
CASE', 'SM BOX', 'SM PACK', 'SM PKG')) AND (part.p_size <= 5)) OR
(((part.p_brand = 'Brand#23') AND p_container IN ('MED BAG', 'MED BOX', 'MED
PKG', 'MED PACK')) AND (part.p_size <= 10))) OR (((part.p_brand = 'Brand#34')
AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')) AND (part.p_size
<= 15)))(part.p_size >= 1))
+--------------PhysicalOlapScan[part]
+
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q2.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q2.out
new file mode 100644
index 0000000000..adca6a9c5a
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q2.out
@@ -0,0 +1,30 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalTopN
+--PhysicalDistribute
+----PhysicalTopN
+------PhysicalProject
+--------filter((cast(ps_supplycost as DECIMAL(27, 9)) = min(ps_supplycost)
OVER(PARTITION BY p_partkey)))
+----------PhysicalWindow
+------------PhysicalQuickSort
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN](supplier.s_suppkey = partsupp.ps_suppkey)
+------------------hashJoin[INNER_JOIN](part.p_partkey = partsupp.ps_partkey)
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[partsupp]
+--------------------PhysicalProject
+----------------------filter((part.p_size = 15)(p_type like '%BRASS'))
+------------------------PhysicalOlapScan[part]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](supplier.s_nationkey =
nation.n_nationkey)
+------------------------PhysicalOlapScan[supplier]
+------------------------PhysicalDistribute
+--------------------------hashJoin[INNER_JOIN](nation.n_regionkey =
region.r_regionkey)
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[nation]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------filter((region.r_name = 'EUROPE'))
+----------------------------------PhysicalOlapScan[region]
+
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q20.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q20.out
new file mode 100644
index 0000000000..cd28676ad6
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q20.out
@@ -0,0 +1,32 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalQuickSort
+--PhysicalDistribute
+----PhysicalQuickSort
+------PhysicalProject
+--------hashJoin[RIGHT_SEMI_JOIN](supplier.s_suppkey = partsupp.ps_suppkey)
+----------PhysicalDistribute
+------------PhysicalProject
+--------------hashJoin[RIGHT_SEMI_JOIN](lineitem.l_partkey =
partsupp.ps_partkey)(lineitem.l_suppkey = partsupp.ps_suppkey)(cast(ps_availqty
as DECIMAL(27, 9)) > (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]
+----------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_tpchPlanShape_p0/shape/q21.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q21.out
new file mode 100644
index 0000000000..1e26a0f353
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q21.out
@@ -0,0 +1,32 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalTopN
+--PhysicalDistribute
+----PhysicalTopN
+------hashAgg[LOCAL]
+--------PhysicalDistribute
+----------PhysicalProject
+------------hashJoin[RIGHT_SEMI_JOIN](l2.l_orderkey = l1.l_orderkey)( not
(l_suppkey = l_suppkey))
+--------------PhysicalProject
+----------------PhysicalOlapScan[lineitem]
+--------------hashJoin[INNER_JOIN](orders.o_orderkey = l1.l_orderkey)
+----------------PhysicalProject
+------------------filter((orders.o_orderstatus = 'F'))
+--------------------PhysicalOlapScan[orders]
+----------------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_tpchPlanShape_p0/shape/q22.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q22.out
new file mode 100644
index 0000000000..fd1ce6b324
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q22.out
@@ -0,0 +1,26 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalQuickSort
+--PhysicalDistribute
+----PhysicalQuickSort
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[RIGHT_ANTI_JOIN](orders.o_custkey = customer.c_custkey)
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------PhysicalOlapScan[orders]
+----------------NestedLoopJoin[INNER_JOIN](cast(c_acctbal as DECIMAL(27, 9)) >
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_tpchPlanShape_p0/shape/q3.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q3.out
new file mode 100644
index 0000000000..a8a3d52100
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q3.out
@@ -0,0 +1,21 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalTopN
+--PhysicalDistribute
+----PhysicalTopN
+------PhysicalProject
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN](lineitem.l_orderkey = orders.o_orderkey)
+--------------PhysicalProject
+----------------filter((lineitem.l_shipdate > 1995-03-15))
+------------------PhysicalOlapScan[lineitem]
+--------------hashJoin[INNER_JOIN](customer.c_custkey = orders.o_custkey)
+----------------PhysicalProject
+------------------filter((orders.o_orderdate < 1995-03-15))
+--------------------PhysicalOlapScan[orders]
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------filter((customer.c_mktsegment = 'BUILDING'))
+----------------------PhysicalOlapScan[customer]
+
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q4.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q4.out
new file mode 100644
index 0000000000..05c67b9b65
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q4.out
@@ -0,0 +1,17 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalQuickSort
+--PhysicalDistribute
+----PhysicalQuickSort
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[RIGHT_SEMI_JOIN](lineitem.l_orderkey =
orders.o_orderkey)
+----------------PhysicalProject
+------------------filter((lineitem.l_commitdate < lineitem.l_receiptdate))
+--------------------PhysicalOlapScan[lineitem]
+----------------PhysicalProject
+------------------filter((orders.o_orderdate >= 1993-07-01)(orders.o_orderdate
< 1993-10-01))
+--------------------PhysicalOlapScan[orders]
+
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q5.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q5.out
new file mode 100644
index 0000000000..e481a2b82e
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q5.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalQuickSort
+--PhysicalDistribute
+----PhysicalQuickSort
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN](customer.c_nationkey =
supplier.s_nationkey)(customer.c_custkey = orders.o_custkey)
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN](lineitem.l_suppkey = supplier.s_suppkey)
+--------------------hashJoin[INNER_JOIN](lineitem.l_orderkey =
orders.o_orderkey)
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[lineitem]
+----------------------PhysicalProject
+------------------------filter((orders.o_orderdate <
1995-01-01)(orders.o_orderdate >= 1994-01-01))
+--------------------------PhysicalOlapScan[orders]
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN](supplier.s_nationkey =
nation.n_nationkey)
+--------------------------PhysicalProject
+----------------------------PhysicalOlapScan[supplier]
+--------------------------PhysicalDistribute
+----------------------------hashJoin[INNER_JOIN](nation.n_regionkey =
region.r_regionkey)
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[nation]
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------filter((region.r_name = 'ASIA'))
+------------------------------------PhysicalOlapScan[region]
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------PhysicalOlapScan[customer]
+
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q6.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q6.out
new file mode 100644
index 0000000000..c936040e8d
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q6.out
@@ -0,0 +1,9 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+hashAgg[GLOBAL]
+--PhysicalDistribute
+----hashAgg[LOCAL]
+------PhysicalProject
+--------filter((lineitem.l_shipdate >= 1994-01-01)(lineitem.l_discount >=
0.050000000)(lineitem.l_discount <= 0.070000000)(lineitem.l_quantity <
24.00)(lineitem.l_shipdate < 1995-01-01))
+----------PhysicalOlapScan[lineitem]
+
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q7.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q7.out
new file mode 100644
index 0000000000..2c1b945352
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q7.out
@@ -0,0 +1,36 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalQuickSort
+--PhysicalDistribute
+----PhysicalQuickSort
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN](customer.c_nationkey =
n2.n_nationkey)(customer.c_custkey = orders.o_custkey)
+----------------PhysicalProject
+------------------PhysicalOlapScan[customer]
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN](orders.o_orderkey =
lineitem.l_orderkey)
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[orders]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN](supplier.s_suppkey =
lineitem.l_suppkey)
+--------------------------PhysicalProject
+----------------------------filter((lineitem.l_shipdate <=
1996-12-31)(lineitem.l_shipdate >= 1995-01-01))
+------------------------------PhysicalOlapScan[lineitem]
+--------------------------PhysicalDistribute
+----------------------------hashJoin[INNER_JOIN](supplier.s_nationkey =
n1.n_nationkey)
+------------------------------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(((n1.n_name = 'FRANCE') OR
(n1.n_name = 'GERMANY')))
+--------------------------------------PhysicalOlapScan[nation]
+----------------------------------PhysicalDistribute
+------------------------------------PhysicalProject
+--------------------------------------filter(((n2.n_name = 'GERMANY') OR
(n2.n_name = 'FRANCE')))
+----------------------------------------PhysicalOlapScan[nation]
+
diff --git a/regression-test/data/nereids_tpchPlanShape_p0/shape/q8.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q8.out
new file mode 100644
index 0000000000..cd339810e0
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q8.out
@@ -0,0 +1,48 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+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_tpchPlanShape_p0/shape/q9.out
b/regression-test/data/nereids_tpchPlanShape_p0/shape/q9.out
new file mode 100644
index 0000000000..181838fd9c
--- /dev/null
+++ b/regression-test/data/nereids_tpchPlanShape_p0/shape/q9.out
@@ -0,0 +1,35 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select --
+PhysicalQuickSort
+--PhysicalDistribute
+----PhysicalQuickSort
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN](partsupp.ps_suppkey =
lineitem.l_suppkey)(partsupp.ps_partkey = lineitem.l_partkey)
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN](orders.o_orderkey = lineitem.l_orderkey)
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[orders]
+--------------------PhysicalProject
+----------------------hashJoin[INNER_JOIN](supplier.s_suppkey =
lineitem.l_suppkey)
+------------------------PhysicalProject
+--------------------------hashJoin[INNER_JOIN](part.p_partkey =
lineitem.l_partkey)
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[lineitem]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------filter((p_name like '%green%'))
+----------------------------------PhysicalOlapScan[part]
+------------------------PhysicalDistribute
+--------------------------hashJoin[INNER_JOIN](supplier.s_nationkey =
nation.n_nationkey)
+----------------------------PhysicalProject
+------------------------------PhysicalOlapScan[supplier]
+----------------------------PhysicalDistribute
+------------------------------PhysicalProject
+--------------------------------PhysicalOlapScan[nation]
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------PhysicalOlapScan[partsupp]
+
diff --git a/regression-test/suites/nereids_tpchPlanShape_p0/load.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/load.groovy
new file mode 100644
index 0000000000..1ddd23a60c
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/load.groovy
@@ -0,0 +1,467 @@
+// 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;
+ '''
+
+ sql '''
+ alter table lineitem modify column l_receiptdate set stats ('ndv'='2567',
'avg_size'='16', 'max_size'='16', 'num_nulls'='0', 'min_value'='1992-01-04',
'max_value'='1998-12-31', 'row_count'='6001215')
+ '''
+
+ sql '''
+ alter table lineitem modify column l_returnflag set stats ('ndv'='3',
'avg_size'='1', 'max_size'='1', 'num_nulls'='0', 'min_value'='A',
'max_value'='R', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table lineitem modify column l_tax set stats ('ndv'='9',
'avg_size'='16', 'max_size'='16', 'num_nulls'='0', 'min_value'='0',
'max_value'='0.08', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table lineitem modify column l_shipmode set stats ('ndv'='7',
'avg_size'='4', 'max_size'='7', 'num_nulls'='0', 'min_value'='AIR',
'max_value'='TRUCK', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table lineitem modify column l_suppkey set stats ('ndv'='9990',
'avg_size'='4', 'max_size'='4', 'num_nulls'='0', 'min_value'='1',
'max_value'='10000', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table lineitem modify column l_shipdate set stats ('ndv'='2549',
'avg_size'='16', 'max_size'='16', 'num_nulls'='0', 'min_value'='1992-01-02',
'max_value'='1998-12-01', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table lineitem modify column l_commitdate set stats ('ndv'='2485',
'avg_size'='16', 'max_size'='16', 'num_nulls'='0', 'min_value'='1992-01-31',
'max_value'='1998-10-31', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table lineitem modify column l_partkey set stats ('ndv'='196099',
'avg_size'='4', 'max_size'='4', 'num_nulls'='0', 'min_value'='1',
'max_value'='10000', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table lineitem modify column l_orderkey set stats ('ndv'='1500000',
'avg_size'='8', 'max_size'='8', 'num_nulls'='0', 'min_value'='1',
'max_value'='10000', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table lineitem modify column l_quantity set stats ('ndv'='50',
'avg_size'='16', 'max_size'='16', 'num_nulls'='0', 'min_value'='1',
'max_value'='10000', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table lineitem modify column l_linestatus set stats ('ndv'='2',
'avg_size'='1', 'max_size'='1', 'num_nulls'='0', 'min_value'='F',
'max_value'='o', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table lineitem modify column l_comment set stats ('ndv'='4619207',
'avg_size'='26', 'max_size'='43', 'num_nulls'='0', 'min_value'='1',
'max_value'='10000', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table lineitem modify column l_extendedprice set stats
('ndv'='929697', 'avg_size'='16', 'max_size'='16', 'num_nulls'='0',
'min_value'='1', 'max_value'='10000', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table lineitem modify column l_linenumber set stats ('ndv'='7',
'avg_size'='4', 'max_size'='4', 'num_nulls'='0', 'min_value'='1',
'max_value'='7', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table lineitem modify column l_discount set stats ('ndv'='11',
'avg_size'='16', 'max_size'='16', 'num_nulls'='0', 'min_value'='0',
'max_value'='0.1', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table lineitem modify column l_shipinstruct set stats ('ndv'='4',
'avg_size'='12', 'max_size'='17', 'num_nulls'='0', 'min_value'='COLLECT COD',
'max_value'='TAKE BACK RETURN', 'row_count'='6001215');
+ '''
+
+ sql '''
+ alter table partsupp modify column ps_suppkey set stats ('ndv'='10009',
'avg_size'='4.0', 'max_size'='4', 'num_nulls'='0', 'min_value'='1',
'max_value'='10000', 'row_count'='800000');
+ '''
+
+ sql '''
+ alter table partsupp modify column ps_availqty set stats ('ndv'='10008',
'avg_size'='4.0', 'max_size'='4', 'num_nulls'='0', 'min_value'='1',
'max_value'='9999', 'row_count'='800000');
+ '''
+
+ sql '''
+ alter table partsupp modify column ps_comment set stats ('ndv'='794782',
'avg_size'='123.6', 'max_size'='198', 'num_nulls'='0', 'min_value'='Tiresias
according to the quiet courts sleep against the ironic', 'max_value'='zzle.
unusual decoys detec', 'row_count'='800000');
+ '''
+
+ sql '''
+ alter table partsupp modify column ps_partkey set stats ('ndv'='196099',
'avg_size'='4.0', 'max_size'='4', 'num_nulls'='0', 'min_value'='1',
'max_value'='200000', 'row_count'='800000');
+ '''
+
+ sql '''
+ alter table partsupp modify column ps_supplycost set stats
('ndv'='100274', 'avg_size'='16.0', 'max_size'='16', 'num_nulls'='0',
'min_value'='1', 'max_value'='1000', 'row_count'='800000');
+ '''
+
+ sql '''
+ alter table supplier modify column s_comment set stats ('ndv'='10039',
'avg_size'='62.56950', 'max_size'='100', 'num_nulls'='0', 'min_value'='about
the blithely express foxes. bli', 'max_value'='zzle furiously. bold accounts
haggle fu', 'row_count'='10000');
+ '''
+
+ sql '''
+ alter table supplier modify column s_phone set stats ('ndv'='10021',
'avg_size'='15.0', 'max_size'='15', 'num_nulls'='0',
'min_value'='10-102-116-6785', 'max_value'='34-998-900-4911',
'row_count'='10000');
+ '''
+
+ sql '''
+ alter table supplier modify column s_nationkey set stats ('ndv'='25',
'avg_size'='4.0', 'max_size'='4', 'num_nulls'='0', 'min_value'='0',
'max_value'='24', 'row_count'='10000');
+ '''
+
+ sql '''
+ alter table supplier modify column s_name set stats ('ndv'='10002',
'avg_size'='18.0', 'max_size'='18', 'num_nulls'='0',
'min_value'='Supplier#000000001', 'max_value'='Supplier#000010000',
'row_count'='10000');
+ '''
+
+ sql '''
+ alter table supplier modify column s_acctbal set stats ('ndv'='9954',
'avg_size'='16.0', 'max_size'='16', 'num_nulls'='0', 'min_value'='-998.22',
'max_value'='9999.72', 'row_count'='10000');
+ '''
+
+ sql '''
+ alter table supplier modify column s_address set stats ('ndv'='9888',
'avg_size'='24.9771', 'max_size'='40', 'num_nulls'='0', 'min_value'='
9aW1wwnBJJPnCx,n', 'max_value'='zzfDhdtZcvmVzA8rNFU,Yctj1zBN',
'row_count'='10000');
+ '''
+
+ sql '''
+ alter table supplier modify column s_suppkey set stats ('ndv'='10000',
'avg_size'='4.0', 'max_size'='4', 'num_nulls'='0', 'min_value'='1',
'max_value'='10000', 'row_count'='10000');
+ '''
+
+ sql '''
+ alter table part modify column p_partkey set stats ('ndv'='200000',
'avg_size'='4.0', 'max_size'='4', 'num_nulls'='0', 'min_value'='1',
'max_value'='200000', 'row_count'='200000');
+ '''
+
+ sql '''
+ alter table part modify column p_container set stats ('ndv'='40',
'avg_size'='7.57', 'max_size'='10', 'num_nulls'='0', 'min_value'='JUMBO BAG',
'max_value'='WRAP PKG', 'row_count'='200000');
+ '''
+
+ sql '''
+ alter table part modify column p_name set stats ('ndv'='200000',
'avg_size'='32.0', 'max_size'='50', 'num_nulls'='0', 'min_value'='almond
antique blue royal burnished', 'max_value'='yellow white seashell lavender
black', 'row_count'='200000');
+ '''
+
+ sql '''
+ alter table part modify column p_comment set stats ('ndv'='133106',
'avg_size'='13', 'max_size'='22', 'num_nulls'='0', 'min_value'='Tire',
'max_value'='zzle. quickly si', 'row_count'='200000');
+ '''
+
+ sql '''
+ alter table part modify column p_brand set stats ('ndv'='25',
'avg_size'='22', 'max_size'='22', 'num_nulls'='0', 'min_value'='Brand#11',
'max_value'='Brand#55', 'row_count'='200000');
+ '''
+
+ sql '''
+ alter table part modify column p_retailprice set stats ('ndv'='21096',
'avg_size'='16.0', 'max_size'='16', 'num_nulls'='0', 'min_value'='901',
'max_value'='2098.99', 'row_count'='200000');
+ '''
+
+ sql '''
+ alter table part modify column p_type set stats ('ndv'='150',
'avg_size'='20', 'max_size'='25', 'num_nulls'='0', 'min_value'='ECONOMY
ANODIZED BRASSe', 'max_value'='STANDARD POLISHED TIN ', 'row_count'='200000');
+ '''
+
+ sql '''
+ alter table part modify column p_size set stats ('ndv'='50',
'avg_size'='4', 'max_size'='4', 'num_nulls'='0', 'min_value'='1',
'max_value'='50', 'row_count'='200000');
+ '''
+
+ sql '''
+ alter table part modify column p_mfgr set stats ('ndv'='5',
'avg_size'='14', 'max_size'='14', 'num_nulls'='0',
'min_value'='Manufacturer#1', 'max_value'='Manufacturer#5',
'row_count'='200000');
+ '''
+
+ sql '''
+ alter table region modify column r_regionkey set stats ('ndv'='5',
'avg_size'='4', 'max_size'='4', 'num_nulls'='0', 'min_value'='0',
'max_value'='4', 'row_count'='5');
+ '''
+
+ sql '''
+ alter table region modify column r_name set stats ('ndv'='5',
'avg_size'='14', 'max_size'='14', 'num_nulls'='0', 'min_value'='AFRICA',
'max_value'='MIDDLE EAST', 'row_count'='5');
+ '''
+
+ sql '''
+ alter table region modify column r_comment set stats ('ndv'='5',
'avg_size'='14', 'max_size'='14', 'num_nulls'='0', 'min_value'='AFRICA',
'max_value'='MIDDLE EAST', 'row_count'='5');
+ '''
+
+ sql '''
+ alter table nation modify column n_nationkey set stats ('ndv'='25',
'avg_size'='4', 'max_size'='4', 'num_nulls'='0', 'min_value'='0',
'max_value'='24', 'row_count'='25');
+ '''
+
+ sql '''
+ alter table nation modify column n_regionkey set stats ('ndv'='5',
'avg_size'='4', 'max_size'='4', 'num_nulls'='0', 'min_value'='0',
'max_value'='4', 'row_count'='25');
+ '''
+
+ sql '''
+ alter table nation modify column n_name set stats ('ndv'='25',
'avg_size'='14', 'max_size'='14', 'num_nulls'='0', 'min_value'='ALGERIA',
'max_value'='VIETNAM', 'row_count'='25');
+ '''
+
+ sql '''
+ alter table nation modify column n_comment set stats ('ndv'='25',
'avg_size'='14', 'max_size'='14', 'num_nulls'='0', 'min_value'='ALGERIA',
'max_value'='VIETNAM', 'row_count'='25');
+ '''
+
+ sql '''
+ alter table customer modify column c_custkey set stats ('ndv'='150000',
'avg_size'='4', 'max_size'='4', 'num_nulls'='0', 'min_value'='1',
'max_value'='150000', 'row_count'='150000');
+ '''
+
+ sql '''
+ alter table customer modify column c_acctbal set stats ('ndv'='142496',
'avg_size'='16', 'max_size'='16', 'num_nulls'='0', 'min_value'='-999.99',
'max_value'='9999.99', 'row_count'='150000');
+ '''
+
+ sql '''
+ alter table customer modify column c_phone set stats ('ndv'='150000',
'avg_size'='15', 'max_size'='15', 'num_nulls'='0',
'min_value'='10-100-106-1617', 'max_value'='34-999-618-6881',
'row_count'='150000');
+ '''
+
+ sql '''
+ alter table customer modify column c_mktsegment set stats ('ndv'='5',
'avg_size'='8.9', 'max_size'='10', 'num_nulls'='0', 'min_value'='AUTOMOBILE',
'max_value'='MACHINERY', 'row_count'='150000');
+ '''
+
+ sql '''
+ alter table customer modify column c_address set stats ('ndv'='150000',
'avg_size'='25', 'max_size'='40', 'num_nulls'='0', 'min_value'=' 2uZwVhQvwA',
'max_value'='zzxGktzXTMKS1BxZlgQ9nqQ', 'row_count'='150000');
+ '''
+
+ sql '''
+ alter table customer modify column c_nationkey set stats ('ndv'='25',
'avg_size'='4', 'max_size'='4', 'num_nulls'='0', 'min_value'='0',
'max_value'='24', 'row_count'='150000');
+ '''
+
+ sql '''
+ alter table customer modify column c_name set stats ('ndv'='150000',
'avg_size'='25', 'max_size'='25', 'num_nulls'='0',
'min_value'='Customer#000000001', 'max_value'='Customer#000150000',
'row_count'='150000');
+ '''
+
+ sql '''
+ alter table customer modify column c_comment set stats ('ndv'='150000',
'avg_size'='72', 'max_size'='116', 'num_nulls'='0', 'min_value'=' Tiresias
according to the sly', 'max_value'='zzle. blithely regu0',
'row_count'='150000');
+ '''
+
+ sql '''
+ alter table orders modify column o_orderstatus set stats ('ndv'='3',
'avg_size'='1.0', 'max_size'='1', 'num_nulls'='0', 'min_value'='F',
'max_value'='P', 'row_count'='1500000');
+ '''
+
+ sql '''
+ alter table orders modify column o_clerk set stats ('ndv'='988',
'avg_size'='15.0', 'max_size'='15', 'num_nulls'='0',
'min_value'='Clerk#000000001', 'max_value'='Clerk#000001000',
'row_count'='1500000');
+ '''
+
+ sql '''
+ alter table orders modify column o_orderdate set stats ('ndv'='2417',
'avg_size'='16.0', 'max_size'='16', 'num_nulls'='0', 'min_value'='1992-01-01',
'max_value'='1998-08-02', 'row_count'='1500000');
+ '''
+
+ sql '''
+ alter table orders modify column o_shippriority set stats ('ndv'='1',
'avg_size'='4.0', 'max_size'='4', 'num_nulls'='0', 'min_value'='0',
'max_value'='0', 'row_count'='1500000');
+ '''
+
+ sql '''
+ alter table orders modify column o_custkey set stats ('ndv'='99149',
'avg_size'='4.0', 'max_size'='4', 'num_nulls'='0', 'min_value'='1',
'max_value'='149999', 'row_count'='1500000');
+ '''
+
+ sql '''
+ alter table orders modify column o_totalprice set stats ('ndv'='1462416',
'avg_size'='16.0', 'max_size'='16', 'num_nulls'='0', 'min_value'='857.71',
'max_value'='555285.16', 'row_count'='1500000');
+ '''
+
+ sql '''
+ alter table orders modify column o_orderkey set stats ('ndv'='1500000',
'avg_size'='8.0', 'max_size'='8', 'num_nulls'='0', 'min_value'='1',
'max_value'='6000000', 'row_count'='1500000');
+ '''
+
+ sql '''
+ alter table orders modify column o_comment set stats ('ndv'='1465415',
'avg_size'='48.51387', 'max_size'='78', 'num_nulls'='0', 'min_value'='Tiresias
about the blithely ironic a', 'max_value'='zzle? furiously ironic instructions
among the unusual t ', 'row_count'='1500000');
+ '''
+
+ sql '''
+ alter table orders modify column o_orderpriority set stats ('ndv'='5',
'avg_size'='8.4', 'max_size'='15', 'num_nulls'='0', 'min_value'='1-URGENT',
'max_value'='5-LOW', 'row_count'='1500000');
+ '''
+}
diff --git a/regression-test/suites/nereids_tpchPlanShape_p0/shape/q1.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q1.groovy
new file mode 100644
index 0000000000..a368d2451b
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q1.groovy
@@ -0,0 +1,52 @@
+/*
+ * 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'
+
+
+ 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_tpchPlanShape_p0/shape/q10.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q10.groovy
new file mode 100644
index 0000000000..e7a0ed87a9
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q10.groovy
@@ -0,0 +1,64 @@
+/*
+ * 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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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_tpchPlanShape_p0/shape/q11.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q11.groovy
new file mode 100644
index 0000000000..028c7529aa
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q11.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("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='GLOBAL'"
+ sql "set enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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
+ 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_tpchPlanShape_p0/shape/q12.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q12.groovy
new file mode 100644
index 0000000000..6221ca9c90
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q12.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("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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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_tpchPlanShape_p0/shape/q13.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q13.groovy
new file mode 100644
index 0000000000..460a316b17
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q13.groovy
@@ -0,0 +1,52 @@
+/*
+ * 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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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_tpchPlanShape_p0/shape/q14.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q14.groovy
new file mode 100644
index 0000000000..c8d28ef6ca
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q14.groovy
@@ -0,0 +1,45 @@
+/*
+ * 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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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_tpchPlanShape_p0/shape/q15.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q15.groovy
new file mode 100644
index 0000000000..b6888c2cc8
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q15.groovy
@@ -0,0 +1,51 @@
+/*
+ * 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='GLOBAL'"
+ sql "set enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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_tpchPlanShape_p0/shape/q16.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q16.groovy
new file mode 100644
index 0000000000..565cbc8dd2
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q16.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("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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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_tpchPlanShape_p0/shape/q17.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q17.groovy
new file mode 100644
index 0000000000..bf85f08e54
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q17.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("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='GLOBAL'"
+ sql "set enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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_tpchPlanShape_p0/shape/q18.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q18.groovy
new file mode 100644
index 0000000000..79ab60654d
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q18.groovy
@@ -0,0 +1,65 @@
+/*
+ * 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("q18") {
+ 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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ c_name,
+ c_custkey,
+ o_orderkey,
+ o_orderdate,
+ o_totalprice,
+ sum(l_quantity)
+ from
+ customer,
+ orders,
+ lineitem
+ where
+ o_orderkey in (
+ select
+ l_orderkey
+ from
+ lineitem
+ group by
+ l_orderkey having
+ sum(l_quantity) > 300
+ )
+ and c_custkey = o_custkey
+ and o_orderkey = l_orderkey
+ group by
+ c_name,
+ c_custkey,
+ o_orderkey,
+ o_orderdate,
+ o_totalprice
+ order by
+ o_totalprice desc,
+ o_orderdate
+ limit 100;
+ """
+}
diff --git a/regression-test/suites/nereids_tpchPlanShape_p0/shape/q19.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q19.groovy
new file mode 100644
index 0000000000..41b078eb40
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q19.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("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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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_tpchPlanShape_p0/shape/q2.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q2.groovy
new file mode 100644
index 0000000000..8476049746
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q2.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("q2") {
+ 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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ s_acctbal,
+ s_name,
+ n_name,
+ p_partkey,
+ p_mfgr,
+ s_address,
+ s_phone,
+ s_comment
+ from
+ part,
+ supplier,
+ partsupp,
+ nation,
+ region
+ where
+ p_partkey = ps_partkey
+ and s_suppkey = ps_suppkey
+ and p_size = 15
+ and p_type like '%BRASS'
+ and s_nationkey = n_nationkey
+ and n_regionkey = r_regionkey
+ and r_name = 'EUROPE'
+ and ps_supplycost = (
+ select
+ min(ps_supplycost)
+ from
+ partsupp,
+ supplier,
+ nation,
+ region
+ where
+ p_partkey = ps_partkey
+ and s_suppkey = ps_suppkey
+ and s_nationkey = n_nationkey
+ and n_regionkey = r_regionkey
+ and r_name = 'EUROPE'
+ )
+ order by
+ s_acctbal desc,
+ n_name,
+ s_name,
+ p_partkey
+ limit 100;
+ """
+}
diff --git a/regression-test/suites/nereids_tpchPlanShape_p0/shape/q20.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q20.groovy
new file mode 100644
index 0000000000..37c97ffe2f
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q20.groovy
@@ -0,0 +1,70 @@
+/*
+ * 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("q20") {
+ 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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ s_name,
+ s_address
+ from
+ supplier,
+ nation
+ where
+ s_suppkey in (
+ select
+ ps_suppkey
+ from
+ partsupp
+ where
+ ps_partkey in (
+ select
+ p_partkey
+ from
+ part
+ where
+ p_name like 'forest%'
+ )
+ and ps_availqty > (
+ select
+ 0.5 * sum(l_quantity)
+ from
+ lineitem
+ where
+ l_partkey = ps_partkey
+ and l_suppkey = ps_suppkey
+ and l_shipdate >= date '1994-01-01'
+ and l_shipdate < date '1994-01-01' + interval '1' year
+ )
+ )
+ and s_nationkey = n_nationkey
+ and n_name = 'CANADA'
+ order by
+ s_name;
+ """
+}
diff --git a/regression-test/suites/nereids_tpchPlanShape_p0/shape/q21.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q21.groovy
new file mode 100644
index 0000000000..6a17489bc3
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q21.groovy
@@ -0,0 +1,72 @@
+/*
+ * 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("q21") {
+ 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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ s_name,
+ count(*) as numwait
+ from
+ supplier,
+ lineitem l1,
+ orders,
+ nation
+ where
+ s_suppkey = l1.l_suppkey
+ and o_orderkey = l1.l_orderkey
+ and o_orderstatus = 'F'
+ and l1.l_receiptdate > l1.l_commitdate
+ and exists (
+ select
+ *
+ from
+ lineitem l2
+ where
+ l2.l_orderkey = l1.l_orderkey
+ and l2.l_suppkey <> l1.l_suppkey
+ )
+ and not exists (
+ select
+ *
+ from
+ lineitem l3
+ where
+ l3.l_orderkey = l1.l_orderkey
+ and l3.l_suppkey <> l1.l_suppkey
+ and l3.l_receiptdate > l3.l_commitdate
+ )
+ and s_nationkey = n_nationkey
+ and n_name = 'SAUDI ARABIA'
+ group by
+ s_name
+ order by
+ numwait desc,
+ s_name
+ limit 100;
+ """
+}
diff --git a/regression-test/suites/nereids_tpchPlanShape_p0/shape/q22.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q22.groovy
new file mode 100644
index 0000000000..6b9ed6713b
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q22.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("q22") {
+ 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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ cntrycode,
+ count(*) as numcust,
+ sum(c_acctbal) as totacctbal
+ from
+ (
+ select
+ substring(c_phone, 1, 2) as cntrycode,
+ c_acctbal
+ from
+ customer
+ where
+ substring(c_phone, 1, 2) in
+ ('13', '31', '23', '29', '30', '18', '17')
+ and c_acctbal > (
+ select
+ avg(c_acctbal)
+ from
+ customer
+ where
+ c_acctbal > 0.00
+ and substring(c_phone, 1, 2) in
+ ('13', '31', '23', '29', '30', '18', '17')
+ )
+ and not exists (
+ select
+ *
+ from
+ orders
+ where
+ o_custkey = c_custkey
+ )
+ ) as custsale
+ group by
+ cntrycode
+ order by
+ cntrycode;
+ """
+}
diff --git a/regression-test/suites/nereids_tpchPlanShape_p0/shape/q3.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q3.groovy
new file mode 100644
index 0000000000..4fd26f615f
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q3.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("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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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_tpchPlanShape_p0/shape/q4.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q4.groovy
new file mode 100644
index 0000000000..67e98d6d66
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q4.groovy
@@ -0,0 +1,53 @@
+/*
+ * 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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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_tpchPlanShape_p0/shape/q5.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q5.groovy
new file mode 100644
index 0000000000..a070bb76fa
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q5.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("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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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_tpchPlanShape_p0/shape/q6.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q6.groovy
new file mode 100644
index 0000000000..f846b4016a
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q6.groovy
@@ -0,0 +1,41 @@
+/*
+ * 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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ 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_tpchPlanShape_p0/shape/q7.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q7.groovy
new file mode 100644
index 0000000000..54e6eaf404
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q7.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("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='GLOBAL'"
+ sql "set enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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_tpchPlanShape_p0/shape/q8.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q8.groovy
new file mode 100644
index 0000000000..73fbca0524
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q8.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("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='GLOBAL'"
+ sql "set enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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_tpchPlanShape_p0/shape/q9.groovy
b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q9.groovy
new file mode 100644
index 0000000000..0e38b7bb7b
--- /dev/null
+++ b/regression-test/suites/nereids_tpchPlanShape_p0/shape/q9.groovy
@@ -0,0 +1,64 @@
+/*
+ * 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 enable_runtime_filter_prune=true"
+ sql 'set exec_mem_limit=21G'
+
+ qt_select """
+ explain shape plan
+ select
+ 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: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
