You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by kx...@apache.org on 2023/06/09 16:18:03 UTC
[doris] 11/13: [regression](nereids) check tpch sf1T and sf500 plan shape on 3 BE environment #20610
This is an automated email from the ASF dual-hosted git repository.
kxiao pushed a commit to branch branch-2.0-beta
in repository https://gitbox.apache.org/repos/asf/doris.git
commit 05877f555c1843c8810f963ddf27248bbc46c1b6
Author: minghong <en...@gmail.com>
AuthorDate: Fri Jun 9 22:46:40 2023 +0800
[regression](nereids) check tpch sf1T and sf500 plan shape on 3 BE environment #20610
---
.../org/apache/doris/nereids/cost/CostModelV1.java | 5 +-
.../org/apache/doris/system/SystemInfoService.java | 9 +
.../data/nereids_tpch_shape_sf1000_p0/shape/q2.out | 19 +-
.../data/nereids_tpch_shape_sf1_p0/shape/q1.out | 12 -
.../data/nereids_tpch_shape_sf1_p0/shape/q10.out | 25 --
.../data/nereids_tpch_shape_sf1_p0/shape/q11.out | 39 --
.../data/nereids_tpch_shape_sf1_p0/shape/q12.out | 16 -
.../data/nereids_tpch_shape_sf1_p0/shape/q13.out | 19 -
.../data/nereids_tpch_shape_sf1_p0/shape/q14.out | 15 -
.../data/nereids_tpch_shape_sf1_p0/shape/q15.out | 30 --
.../data/nereids_tpch_shape_sf1_p0/shape/q16.out | 22 -
.../data/nereids_tpch_shape_sf1_p0/shape/q17.out | 20 -
.../data/nereids_tpch_shape_sf1_p0/shape/q18.out | 25 --
.../data/nereids_tpch_shape_sf1_p0/shape/q19.out | 15 -
.../data/nereids_tpch_shape_sf1_p0/shape/q2.out | 30 --
.../data/nereids_tpch_shape_sf1_p0/shape/q20.out | 32 --
.../data/nereids_tpch_shape_sf1_p0/shape/q21.out | 33 --
.../data/nereids_tpch_shape_sf1_p0/shape/q22.out | 26 --
.../data/nereids_tpch_shape_sf1_p0/shape/q3.out | 22 -
.../data/nereids_tpch_shape_sf1_p0/shape/q4.out | 17 -
.../data/nereids_tpch_shape_sf1_p0/shape/q5.out | 35 --
.../data/nereids_tpch_shape_sf1_p0/shape/q6.out | 9 -
.../data/nereids_tpch_shape_sf1_p0/shape/q7.out | 36 --
.../data/nereids_tpch_shape_sf1_p0/shape/q8.out | 48 ---
.../data/nereids_tpch_shape_sf1_p0/shape/q9.out | 35 --
.../data/nereids_tpch_shape_sf500_p0/shape/q2.out | 19 +-
.../nereids_tpch_shape_sf1000_p0/shape/q1.groovy | 114 +++--
.../nereids_tpch_shape_sf1000_p0/shape/q10.groovy | 136 +++---
.../nereids_tpch_shape_sf1000_p0/shape/q11.groovy | 130 +++---
.../nereids_tpch_shape_sf1000_p0/shape/q12.groovy | 126 +++---
.../nereids_tpch_shape_sf1000_p0/shape/q13.groovy | 118 +++---
.../nereids_tpch_shape_sf1000_p0/shape/q14.groovy | 98 +++--
.../nereids_tpch_shape_sf1000_p0/shape/q15.groovy | 118 +++---
.../nereids_tpch_shape_sf1000_p0/shape/q16.groovy | 140 +++---
.../nereids_tpch_shape_sf1000_p0/shape/q17.groovy | 114 +++--
.../nereids_tpch_shape_sf1000_p0/shape/q18.groovy | 146 ++++---
.../nereids_tpch_shape_sf1000_p0/shape/q19.groovy | 154 ++++---
.../nereids_tpch_shape_sf1000_p0/shape/q2.groovy | 6 +-
.../nereids_tpch_shape_sf1000_p0/shape/q20.groovy | 156 ++++---
.../nereids_tpch_shape_sf1000_p0/shape/q21.groovy | 160 ++++---
.../nereids_tpch_shape_sf1000_p0/shape/q22.groovy | 154 ++++---
.../nereids_tpch_shape_sf1000_p0/shape/q3.groovy | 128 +++---
.../nereids_tpch_shape_sf1000_p0/shape/q4.groovy | 118 +++---
.../nereids_tpch_shape_sf1000_p0/shape/q5.groovy | 128 +++---
.../nereids_tpch_shape_sf1000_p0/shape/q6.groovy | 98 +++--
.../nereids_tpch_shape_sf1000_p0/shape/q7.groovy | 158 ++++---
.../nereids_tpch_shape_sf1000_p0/shape/q8.groovy | 154 ++++---
.../nereids_tpch_shape_sf1000_p0/shape/q9.groovy | 144 +++----
.../suites/nereids_tpch_shape_sf1_p0/load.groovy | 467 ---------------------
.../nereids_tpch_shape_sf1_p0/shape/q1.groovy | 56 ---
.../nereids_tpch_shape_sf1_p0/shape/q10.groovy | 71 ----
.../nereids_tpch_shape_sf1_p0/shape/q11.groovy | 66 ---
.../nereids_tpch_shape_sf1_p0/shape/q12.groovy | 67 ---
.../nereids_tpch_shape_sf1_p0/shape/q13.groovy | 59 ---
.../nereids_tpch_shape_sf1_p0/shape/q14.groovy | 52 ---
.../nereids_tpch_shape_sf1_p0/shape/q15.groovy | 58 ---
.../nereids_tpch_shape_sf1_p0/shape/q16.groovy | 69 ---
.../nereids_tpch_shape_sf1_p0/shape/q17.groovy | 56 ---
.../nereids_tpch_shape_sf1_p0/shape/q18.groovy | 72 ----
.../nereids_tpch_shape_sf1_p0/shape/q19.groovy | 76 ----
.../nereids_tpch_shape_sf1_p0/shape/q2.groovy | 83 ----
.../nereids_tpch_shape_sf1_p0/shape/q20.groovy | 77 ----
.../nereids_tpch_shape_sf1_p0/shape/q21.groovy | 79 ----
.../nereids_tpch_shape_sf1_p0/shape/q22.groovy | 76 ----
.../nereids_tpch_shape_sf1_p0/shape/q3.groovy | 63 ---
.../nereids_tpch_shape_sf1_p0/shape/q4.groovy | 60 ---
.../nereids_tpch_shape_sf1_p0/shape/q5.groovy | 64 ---
.../nereids_tpch_shape_sf1_p0/shape/q6.groovy | 49 ---
.../nereids_tpch_shape_sf1_p0/shape/q7.groovy | 79 ----
.../nereids_tpch_shape_sf1_p0/shape/q8.groovy | 76 ----
.../nereids_tpch_shape_sf1_p0/shape/q9.groovy | 71 ----
.../nereids_tpch_shape_sf500_p0/shape/q1.groovy | 116 +++--
.../nereids_tpch_shape_sf500_p0/shape/q10.groovy | 144 +++----
.../nereids_tpch_shape_sf500_p0/shape/q11.groovy | 134 +++---
.../nereids_tpch_shape_sf500_p0/shape/q12.groovy | 136 +++---
.../nereids_tpch_shape_sf500_p0/shape/q13.groovy | 114 +++--
.../nereids_tpch_shape_sf500_p0/shape/q14.groovy | 100 +++--
.../nereids_tpch_shape_sf500_p0/shape/q15.groovy | 118 +++---
.../nereids_tpch_shape_sf500_p0/shape/q16.groovy | 140 +++---
.../nereids_tpch_shape_sf500_p0/shape/q17.groovy | 114 +++--
.../nereids_tpch_shape_sf500_p0/shape/q18.groovy | 146 ++++---
.../nereids_tpch_shape_sf500_p0/shape/q19.groovy | 154 ++++---
.../nereids_tpch_shape_sf500_p0/shape/q2.groovy | 6 +-
.../nereids_tpch_shape_sf500_p0/shape/q20.groovy | 156 ++++---
.../nereids_tpch_shape_sf500_p0/shape/q21.groovy | 160 ++++---
.../nereids_tpch_shape_sf500_p0/shape/q22.groovy | 154 ++++---
.../nereids_tpch_shape_sf500_p0/shape/q3.groovy | 134 +++---
.../nereids_tpch_shape_sf500_p0/shape/q4.groovy | 126 +++---
.../nereids_tpch_shape_sf500_p0/shape/q5.groovy | 122 +++---
.../nereids_tpch_shape_sf500_p0/shape/q6.groovy | 92 ++--
.../nereids_tpch_shape_sf500_p0/shape/q7.groovy | 152 ++++---
.../nereids_tpch_shape_sf500_p0/shape/q8.groovy | 146 ++++---
.../nereids_tpch_shape_sf500_p0/shape/q9.groovy | 138 +++---
93 files changed, 2742 insertions(+), 5417 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
index fd587a42c2..43246b1e02 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
@@ -173,10 +173,7 @@ class CostModelV1 extends PlanVisitor<Cost, PlanContext> {
// replicate
if (spec instanceof DistributionSpecReplicated) {
- int beNumber = ConnectContext.get().getEnv().getClusterInfo().getAllBackendIds(true).size();
- if (ConnectContext.get().getSessionVariable().getBeNumberForTest() != -1) {
- beNumber = ConnectContext.get().getSessionVariable().getBeNumberForTest();
- }
+ int beNumber = ConnectContext.get().getEnv().getClusterInfo().getBackendsNumber(true);
int instanceNumber = ConnectContext.get().getSessionVariable().getParallelExecInstanceNum();
beNumber = Math.max(1, beNumber);
double memLimit = ConnectContext.get().getSessionVariable().getMaxExecMemByte();
diff --git a/fe/fe-core/src/main/java/org/apache/doris/system/SystemInfoService.java b/fe/fe-core/src/main/java/org/apache/doris/system/SystemInfoService.java
index 4d10bb82e9..e2d4811a4b 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/system/SystemInfoService.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/system/SystemInfoService.java
@@ -33,6 +33,7 @@ import org.apache.doris.common.UserException;
import org.apache.doris.common.io.CountingDataOutputStream;
import org.apache.doris.common.util.NetUtils;
import org.apache.doris.metric.MetricRepo;
+import org.apache.doris.qe.ConnectContext;
import org.apache.doris.resource.Tag;
import org.apache.doris.thrift.TNodeInfo;
import org.apache.doris.thrift.TPaloNodesInfo;
@@ -356,6 +357,14 @@ public class SystemInfoService {
return getAllBackendIds(false);
}
+ public int getBackendsNumber(boolean needAlive) {
+ int beNumber = ConnectContext.get().getSessionVariable().getBeNumberForTest();
+ if (beNumber == -1) {
+ beNumber = getAllBackendIds(needAlive).size();
+ }
+ return beNumber;
+ }
+
public List<Long> getAllBackendIds(boolean needAlive) {
ImmutableMap<Long, Backend> idToBackend = idToBackendRef;
List<Long> backendIds = Lists.newArrayList(idToBackend.keySet());
diff --git a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q2.out b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q2.out
index f3091de749..f109430d4a 100644
--- a/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q2.out
+++ b/regression-test/data/nereids_tpch_shape_sf1000_p0/shape/q2.out
@@ -7,15 +7,16 @@ PhysicalTopN
--------filter((partsupp.ps_supplycost = 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
+--------------PhysicalDistribute
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN](supplier.s_suppkey = partsupp.ps_suppkey)
+--------------------PhysicalDistribute
+----------------------hashJoin[INNER_JOIN](part.p_partkey = partsupp.ps_partkey)
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[partsupp]
+------------------------PhysicalProject
+--------------------------filter((part.p_size = 15)(p_type like '%BRASS'))
+----------------------------PhysicalOlapScan[part]
--------------------PhysicalProject
----------------------hashJoin[INNER_JOIN](supplier.s_nationkey = nation.n_nationkey)
------------------------PhysicalOlapScan[supplier]
diff --git a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q1.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q1.out
deleted file mode 100644
index 00a7cf50f8..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q1.out
+++ /dev/null
@@ -1,12 +0,0 @@
--- 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_tpch_shape_sf1_p0/shape/q10.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q10.out
deleted file mode 100644
index cf30f47eb7..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q10.out
+++ /dev/null
@@ -1,25 +0,0 @@
--- 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](customer.c_nationkey = nation.n_nationkey)
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN](customer.c_custkey = orders.o_custkey)
-------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
-------------------PhysicalDistribute
---------------------hashJoin[INNER_JOIN](lineitem.l_orderkey = orders.o_orderkey)
-----------------------PhysicalProject
-------------------------filter((lineitem.l_returnflag = 'R'))
---------------------------PhysicalOlapScan[lineitem]
-----------------------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_tpch_shape_sf1_p0/shape/q11.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q11.out
deleted file mode 100644
index cf27cdd93f..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q11.out
+++ /dev/null
@@ -1,39 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !select --
-PhysicalQuickSort
---PhysicalDistribute
-----PhysicalQuickSort
-------PhysicalProject
---------NestedLoopJoin[INNER_JOIN](cast(value as DOUBLE) > cast((sum((ps_supplycost * ps_availqty)) * 0.000002) as DOUBLE))
-----------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_tpch_shape_sf1_p0/shape/q12.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q12.out
deleted file mode 100644
index 6dd416cefa..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q12.out
+++ /dev/null
@@ -1,16 +0,0 @@
--- 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_tpch_shape_sf1_p0/shape/q13.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q13.out
deleted file mode 100644
index d196ba817e..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q13.out
+++ /dev/null
@@ -1,19 +0,0 @@
--- 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[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_tpch_shape_sf1_p0/shape/q14.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q14.out
deleted file mode 100644
index 9ec9d4f3fa..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q14.out
+++ /dev/null
@@ -1,15 +0,0 @@
--- 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_tpch_shape_sf1_p0/shape/q15.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q15.out
deleted file mode 100644
index da9dd396be..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q15.out
+++ /dev/null
@@ -1,30 +0,0 @@
--- 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_tpch_shape_sf1_p0/shape/q16.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q16.out
deleted file mode 100644
index 3b28129f40..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q16.out
+++ /dev/null
@@ -1,22 +0,0 @@
--- 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_tpch_shape_sf1_p0/shape/q17.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q17.out
deleted file mode 100644
index 90b6d8a190..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q17.out
+++ /dev/null
@@ -1,20 +0,0 @@
--- 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 DECIMALV3(38, 5)) < (0.2 * avg(cast(l_quantity as DECIMALV3(17, 4))) OVER(PARTITION BY p_partkey))))
-------------PhysicalWindow
---------------PhysicalQuickSort
-----------------PhysicalDistribute
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN](part.p_partkey = lineitem.l_partkey)
-----------------------PhysicalProject
-------------------------PhysicalOlapScan[lineitem]
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------filter((part.p_container = 'MED BOX')(part.p_brand = 'Brand#23'))
-----------------------------PhysicalOlapScan[part]
-
diff --git a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q18.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q18.out
deleted file mode 100644
index e91c302aaa..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q18.out
+++ /dev/null
@@ -1,25 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !select --
-PhysicalTopN
---PhysicalDistribute
-----PhysicalTopN
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN](orders.o_orderkey = lineitem.l_orderkey)
-------------PhysicalProject
---------------PhysicalOlapScan[lineitem]
-------------PhysicalDistribute
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN](customer.c_custkey = orders.o_custkey)
-------------------PhysicalProject
---------------------PhysicalOlapScan[customer]
-------------------PhysicalDistribute
---------------------hashJoin[LEFT_SEMI_JOIN](orders.o_orderkey = lineitem.l_orderkey)
-----------------------PhysicalProject
-------------------------PhysicalOlapScan[orders]
-----------------------PhysicalProject
-------------------------filter((sum(l_quantity) > 300.00))
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------PhysicalOlapScan[lineitem]
-
diff --git a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q19.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q19.out
deleted file mode 100644
index 1b877ad328..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q19.out
+++ /dev/null
@@ -1,15 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !select --
-hashAgg[GLOBAL]
---PhysicalDistribute
-----hashAgg[LOCAL]
-------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') AN [...]
-----------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_tpch_shape_sf1_p0/shape/q2.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q2.out
deleted file mode 100644
index f3091de749..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q2.out
+++ /dev/null
@@ -1,30 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !select --
-PhysicalTopN
---PhysicalDistribute
-----PhysicalTopN
-------PhysicalProject
---------filter((partsupp.ps_supplycost = 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_tpch_shape_sf1_p0/shape/q20.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q20.out
deleted file mode 100644
index 4142233ae3..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q20.out
+++ /dev/null
@@ -1,32 +0,0 @@
--- 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 DECIMALV3(38, 3)) > (0.5 * sum(l_quantity)))
-----------------PhysicalProject
-------------------hashAgg[GLOBAL]
---------------------PhysicalDistribute
-----------------------hashAgg[LOCAL]
-------------------------PhysicalProject
---------------------------filter((lineitem.l_shipdate < 1995-01-01)(lineitem.l_shipdate >= 1994-01-01))
-----------------------------PhysicalOlapScan[lineitem]
-----------------PhysicalDistribute
-------------------hashJoin[LEFT_SEMI_JOIN](partsupp.ps_partkey = part.p_partkey)
---------------------PhysicalProject
-----------------------PhysicalOlapScan[partsupp]
---------------------PhysicalProject
-----------------------filter((p_name like 'forest%'))
-------------------------PhysicalOlapScan[part]
-----------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_tpch_shape_sf1_p0/shape/q21.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q21.out
deleted file mode 100644
index 1ebbb4a839..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q21.out
+++ /dev/null
@@ -1,33 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !select --
-PhysicalTopN
---PhysicalDistribute
-----PhysicalTopN
-------hashAgg[GLOBAL]
---------PhysicalDistribute
-----------hashAgg[LOCAL]
-------------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_tpch_shape_sf1_p0/shape/q22.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q22.out
deleted file mode 100644
index c41229b490..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q22.out
+++ /dev/null
@@ -1,26 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !select --
-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 DECIMALV3(38, 4)) > avg(c_acctbal))
-------------------PhysicalProject
---------------------filter(substring(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17'))
-----------------------PhysicalOlapScan[customer]
-------------------PhysicalDistribute
---------------------PhysicalAssertNumRows
-----------------------hashAgg[GLOBAL]
-------------------------PhysicalDistribute
---------------------------hashAgg[LOCAL]
-----------------------------PhysicalProject
-------------------------------filter((customer.c_acctbal > 0.00)substring(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17'))
---------------------------------PhysicalOlapScan[customer]
-
diff --git a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q3.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q3.out
deleted file mode 100644
index aff6e8dcd1..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q3.out
+++ /dev/null
@@ -1,22 +0,0 @@
--- 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]
---------------PhysicalProject
-----------------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_tpch_shape_sf1_p0/shape/q4.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q4.out
deleted file mode 100644
index 05c67b9b65..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q4.out
+++ /dev/null
@@ -1,17 +0,0 @@
--- 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_tpch_shape_sf1_p0/shape/q5.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q5.out
deleted file mode 100644
index f8211c8fa8..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q5.out
+++ /dev/null
@@ -1,35 +0,0 @@
--- 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_custkey = orders.o_custkey)(customer.c_nationkey = supplier.s_nationkey)
-----------------PhysicalProject
-------------------PhysicalOlapScan[customer]
-----------------PhysicalDistribute
-------------------PhysicalProject
---------------------hashJoin[INNER_JOIN](lineitem.l_orderkey = orders.o_orderkey)
-----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN](lineitem.l_suppkey = supplier.s_suppkey)
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[lineitem]
---------------------------PhysicalDistribute
-----------------------------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]
-----------------------PhysicalProject
-------------------------filter((orders.o_orderdate < 1995-01-01)(orders.o_orderdate >= 1994-01-01))
---------------------------PhysicalOlapScan[orders]
-
diff --git a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q6.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q6.out
deleted file mode 100644
index da938bd9de..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q6.out
+++ /dev/null
@@ -1,9 +0,0 @@
--- 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.07)(lineitem.l_discount >= 0.05)(lineitem.l_quantity < 24.00)(lineitem.l_shipdate < 1995-01-01))
-----------PhysicalOlapScan[lineitem]
-
diff --git a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q7.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q7.out
deleted file mode 100644
index 8969786cfa..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q7.out
+++ /dev/null
@@ -1,36 +0,0 @@
--- 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_custkey = orders.o_custkey)(customer.c_nationkey = n2.n_nationkey)
-----------------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_tpch_shape_sf1_p0/shape/q8.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q8.out
deleted file mode 100644
index cd339810e0..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q8.out
+++ /dev/null
@@ -1,48 +0,0 @@
--- 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_tpch_shape_sf1_p0/shape/q9.out b/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q9.out
deleted file mode 100644
index eeed1b1e9c..0000000000
--- a/regression-test/data/nereids_tpch_shape_sf1_p0/shape/q9.out
+++ /dev/null
@@ -1,35 +0,0 @@
--- 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_partkey = lineitem.l_partkey)(partsupp.ps_suppkey = lineitem.l_suppkey)
-----------------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/data/nereids_tpch_shape_sf500_p0/shape/q2.out b/regression-test/data/nereids_tpch_shape_sf500_p0/shape/q2.out
index f3091de749..f109430d4a 100644
--- a/regression-test/data/nereids_tpch_shape_sf500_p0/shape/q2.out
+++ b/regression-test/data/nereids_tpch_shape_sf500_p0/shape/q2.out
@@ -7,15 +7,16 @@ PhysicalTopN
--------filter((partsupp.ps_supplycost = 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
+--------------PhysicalDistribute
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN](supplier.s_suppkey = partsupp.ps_suppkey)
+--------------------PhysicalDistribute
+----------------------hashJoin[INNER_JOIN](part.p_partkey = partsupp.ps_partkey)
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[partsupp]
+------------------------PhysicalProject
+--------------------------filter((part.p_size = 15)(p_type like '%BRASS'))
+----------------------------PhysicalOlapScan[part]
--------------------PhysicalProject
----------------------hashJoin[INNER_JOIN](supplier.s_nationkey = nation.n_nationkey)
------------------------PhysicalOlapScan[supplier]
diff --git a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q1.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q1.groovy
index 6148e2af08..d2f3aa11ab 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q1.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q1.groovy
@@ -1,59 +1,55 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("q1") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
-
- 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;
- """
-}
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+suite("q1") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+sql 'set be_number_for_test=3'
+
+
+ qt_select """
+ explain shape plan
+ select
+ l_returnflag,
+ l_linestatus,
+ sum(l_quantity) as sum_qty,
+ sum(l_extendedprice) as sum_base_price,
+ sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
+ sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
+ avg(l_quantity) as avg_qty,
+ avg(l_extendedprice) as avg_price,
+ avg(l_discount) as avg_disc,
+ count(*) as count_order
+ from
+ lineitem
+ where
+ l_shipdate <= date '1998-12-01' - interval '90' day
+ group by
+ l_returnflag,
+ l_linestatus
+ order by
+ l_returnflag,
+ l_linestatus;
+ """
+}
diff --git a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q10.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q10.groovy
index 72b0f3e8bb..f4101b5124 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q10.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q10.groovy
@@ -1,70 +1,66 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+sql 'set be_number_for_test=3'
+ 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_tpch_shape_sf1000_p0/shape/q11.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q11.groovy
index a60b8c9b86..d681e574cd 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q11.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q11.groovy
@@ -1,67 +1,63 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
-
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+sql 'set be_number_for_test=3'
+
+
+
+ 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_tpch_shape_sf1000_p0/shape/q12.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q12.groovy
index f026f73628..2a6ca361ab 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q12.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q12.groovy
@@ -1,65 +1,61 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+sql 'set be_number_for_test=3'
+ 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_tpch_shape_sf1000_p0/shape/q13.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q13.groovy
index 54b2645611..7462bbbe28 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q13.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q13.groovy
@@ -1,61 +1,57 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+ 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_tpch_shape_sf1000_p0/shape/q14.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q14.groovy
index 93c8f2d570..d46a2e1d79 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q14.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q14.groovy
@@ -1,51 +1,47 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+sql 'set be_number_for_test=3'
+ 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_tpch_shape_sf1000_p0/shape/q15.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q15.groovy
index f2259026e6..5e2c80533c 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q15.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q15.groovy
@@ -1,61 +1,57 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf1000_p0/shape/q16.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q16.groovy
index 05d1fb9200..85a237891d 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q16.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q16.groovy
@@ -1,72 +1,68 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf1000_p0/shape/q17.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q17.groovy
index 9036a55125..6cb3aa3564 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q17.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q17.groovy
@@ -1,59 +1,55 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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
- );
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf1000_p0/shape/q18.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q18.groovy
index 869c24d6ca..0f0ab18fbf 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q18.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q18.groovy
@@ -1,75 +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("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf1000_p0/shape/q19.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q19.groovy
index f5bf47cde7..c08f50defa 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q19.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q19.groovy
@@ -1,79 +1,75 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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'
- );
-
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf1000_p0/shape/q2.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q2.groovy
index 2ae79b790b..67f4f81b1d 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q2.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q2.groovy
@@ -30,11 +30,7 @@ suite("q2") {
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
+sql 'set be_number_for_test=3'
qt_select """
explain shape plan
diff --git a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q20.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q20.groovy
index 2844dcbf49..831761d391 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q20.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q20.groovy
@@ -1,80 +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("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf1000_p0/shape/q21.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q21.groovy
index 135effeb5a..3cd8e20304 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q21.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q21.groovy
@@ -1,82 +1,78 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf1000_p0/shape/q22.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q22.groovy
index 0077bc586e..6f3e4d7aa4 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q22.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q22.groovy
@@ -1,79 +1,75 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf1000_p0/shape/q3.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q3.groovy
index 817e24170e..97104534c7 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q3.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q3.groovy
@@ -1,66 +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("q3") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- // db = "tpch"
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql "set runtime_filter_mode='GLOBAL'"
-
- sql 'set exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+suite("q3") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ // db = "tpch"
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql "set runtime_filter_mode='GLOBAL'"
+
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf1000_p0/shape/q4.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q4.groovy
index ef98fd5dc7..7b13b47df9 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q4.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q4.groovy
@@ -1,61 +1,57 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("q4") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql "set runtime_filter_mode='GLOBAL'"
- sql 'set exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
-
-
-
- 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;
- """
-}
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+suite("q4") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql "set runtime_filter_mode='GLOBAL'"
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+sql 'set be_number_for_test=3'
+
+
+
+
+ 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_tpch_shape_sf1000_p0/shape/q5.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q5.groovy
index 53be64b070..0a9c786326 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q5.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q5.groovy
@@ -1,66 +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("q5") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql "set runtime_filter_mode='GLOBAL'"
-
- sql 'set exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+suite("q5") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql "set runtime_filter_mode='GLOBAL'"
+
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf1000_p0/shape/q6.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q6.groovy
index cf9620f532..43b7a36b8c 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q6.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q6.groovy
@@ -1,51 +1,47 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ qt_select """
+ explain shape plan
+ select
+ sum(l_extendedprice * l_discount) as revenue
+ from
+ lineitem
+ where
+ l_shipdate >= date '1994-01-01'
+ and l_shipdate < date '1994-01-01' + interval '1' year
+ and l_discount between .06 - 0.01 and .06 + 0.01
+ and l_quantity < 24;
+ """
+}
diff --git a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q7.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q7.groovy
index 9339aae5ff..44def54304 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q7.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q7.groovy
@@ -1,81 +1,77 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf1000_p0/shape/q8.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q8.groovy
index 22242d2344..ed92d555ab 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q8.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q8.groovy
@@ -1,79 +1,75 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf1000_p0/shape/q9.groovy b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q9.groovy
index 98637ccc63..44e968ed18 100644
--- a/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q9.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf1000_p0/shape/q9.groovy
@@ -1,74 +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("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ qt_select """
+ explain shape plan
+ select
+ nation,
+ o_year,
+ sum(amount) as sum_profit
+ from
+ (
+ select
+ 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;
+ """
+}
diff --git a/regression-test/suites/nereids_tpch_shape_sf1_p0/load.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/load.groovy
deleted file mode 100644
index 1ddd23a60c..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/load.groovy
+++ /dev/null
@@ -1,467 +0,0 @@
-// Licensed to the Apache Software Foundation (ASF) under one
-// or more contributor license agreements. See the NOTICE file
-// distributed with this work for additional information
-// regarding copyright ownership. The ASF licenses this file
-// to you under the Apache License, Version 2.0 (the
-// "License"); you may not use this file except in compliance
-// with the License. You may obtain a copy of the License at
-//
-// http://www.apache.org/licenses/LICENSE-2.0
-//
-// Unless required by applicable law or agreed to in writing,
-// software distributed under the License is distributed on an
-// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-// KIND, either express or implied. See the License for the
-// specific language governing permissions and limitations
-// under the License.
-
-suite("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_tpch_shape_sf1_p0/shape/q1.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q1.groovy
deleted file mode 100644
index c8ff628b89..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q1.groovy
+++ /dev/null
@@ -1,56 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("q1") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- return;
- }
- 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_tpch_shape_sf1_p0/shape/q10.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q10.groovy
deleted file mode 100644
index a0b1a5e0df..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q10.groovy
+++ /dev/null
@@ -1,71 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q11.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q11.groovy
deleted file mode 100644
index 735f5a93c5..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q11.groovy
+++ /dev/null
@@ -1,66 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q12.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q12.groovy
deleted file mode 100644
index 8926ab6fa8..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q12.groovy
+++ /dev/null
@@ -1,67 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q13.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q13.groovy
deleted file mode 100644
index bc2554b86f..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q13.groovy
+++ /dev/null
@@ -1,59 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q14.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q14.groovy
deleted file mode 100644
index d70b63d06e..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q14.groovy
+++ /dev/null
@@ -1,52 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q15.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q15.groovy
deleted file mode 100644
index 4749d570b0..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q15.groovy
+++ /dev/null
@@ -1,58 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q16.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q16.groovy
deleted file mode 100644
index 1e114b55c5..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q16.groovy
+++ /dev/null
@@ -1,69 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q17.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q17.groovy
deleted file mode 100644
index 50cb1f52ed..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q17.groovy
+++ /dev/null
@@ -1,56 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q18.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q18.groovy
deleted file mode 100644
index 6ed14189e9..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q18.groovy
+++ /dev/null
@@ -1,72 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q19.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q19.groovy
deleted file mode 100644
index 726ff7167f..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q19.groovy
+++ /dev/null
@@ -1,76 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q2.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q2.groovy
deleted file mode 100644
index a987cb06aa..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q2.groovy
+++ /dev/null
@@ -1,83 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q20.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q20.groovy
deleted file mode 100644
index 4d93560cc8..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q20.groovy
+++ /dev/null
@@ -1,77 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q21.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q21.groovy
deleted file mode 100644
index 4ff1fbb411..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q21.groovy
+++ /dev/null
@@ -1,79 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q22.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q22.groovy
deleted file mode 100644
index 734bb4bb02..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q22.groovy
+++ /dev/null
@@ -1,76 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q3.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q3.groovy
deleted file mode 100644
index 8964734f53..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q3.groovy
+++ /dev/null
@@ -1,63 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("q3") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- // db = "tpch"
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql "set runtime_filter_mode='GLOBAL'"
-
- sql 'set exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q4.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q4.groovy
deleted file mode 100644
index 0a16300b91..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q4.groovy
+++ /dev/null
@@ -1,60 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("q4") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql "set runtime_filter_mode='GLOBAL'"
-
- sql 'set exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q5.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q5.groovy
deleted file mode 100644
index 11cc405653..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q5.groovy
+++ /dev/null
@@ -1,64 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("q5") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql "set runtime_filter_mode='GLOBAL'"
-
- sql 'set exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
-
- 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_tpch_shape_sf1_p0/shape/q6.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q6.groovy
deleted file mode 100644
index fd55371b7b..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q6.groovy
+++ /dev/null
@@ -1,49 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
-
- 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_tpch_shape_sf1_p0/shape/q7.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q7.groovy
deleted file mode 100644
index 498bbf813e..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q7.groovy
+++ /dev/null
@@ -1,79 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
-
- 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_tpch_shape_sf1_p0/shape/q8.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q8.groovy
deleted file mode 100644
index 0aa58cfff0..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q8.groovy
+++ /dev/null
@@ -1,76 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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_tpch_shape_sf1_p0/shape/q9.groovy b/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q9.groovy
deleted file mode 100644
index 9ee28d15b2..0000000000
--- a/regression-test/suites/nereids_tpch_shape_sf1_p0/shape/q9.groovy
+++ /dev/null
@@ -1,71 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
diff --git a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q1.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q1.groovy
index ba8e2c15ff..27fe9ac08f 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q1.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q1.groovy
@@ -1,60 +1,56 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("q1") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
-
- 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;
- """
-}
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+suite("q1") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+sql 'set be_number_for_test=3'
+
+
+ qt_select """
+ explain shape plan
+ select
+ l_returnflag,
+ l_linestatus,
+ sum(l_quantity) as sum_qty,
+ sum(l_extendedprice) as sum_base_price,
+ sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
+ sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
+ avg(l_quantity) as avg_qty,
+ avg(l_extendedprice) as avg_price,
+ avg(l_discount) as avg_disc,
+ count(*) as count_order
+ from
+ lineitem
+ where
+ l_shipdate <= date '1998-12-01' - interval '90' day
+ group by
+ l_returnflag,
+ l_linestatus
+ order by
+ l_returnflag,
+ l_linestatus;
+ """
+}
diff --git a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q10.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q10.groovy
index 06e91aabb8..408312e626 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q10.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q10.groovy
@@ -1,74 +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("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q11.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q11.groovy
index a6781cbf4e..9820445dfa 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q11.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q11.groovy
@@ -1,69 +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("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q12.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q12.groovy
index d6c4996b4e..c0ff16a269 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q12.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q12.groovy
@@ -1,70 +1,66 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q13.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q13.groovy
index 0400905aeb..ab6e68aaea 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q13.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q13.groovy
@@ -1,59 +1,55 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q14.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q14.groovy
index a3cd7561ff..8d052771dd 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q14.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q14.groovy
@@ -1,52 +1,48 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q15.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q15.groovy
index 51c9c8f240..dfc5e4917b 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q15.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q15.groovy
@@ -1,61 +1,57 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q16.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q16.groovy
index 7035e0b58b..12cbb7c236 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q16.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q16.groovy
@@ -1,72 +1,68 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q17.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q17.groovy
index 0a1c061441..075b5186a8 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q17.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q17.groovy
@@ -1,59 +1,55 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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
- );
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q18.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q18.groovy
index 2e13db34b3..dc7bae3584 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q18.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q18.groovy
@@ -1,75 +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("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q19.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q19.groovy
index ab624fa467..800352e269 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q19.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q19.groovy
@@ -1,79 +1,75 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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'
- );
-
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q2.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q2.groovy
index 3f53b17d53..088cd1b04a 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q2.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q2.groovy
@@ -30,11 +30,7 @@ suite("q2") {
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
+sql 'set be_number_for_test=3'
qt_select """
diff --git a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q20.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q20.groovy
index 8f045343cc..1a1f70c2dc 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q20.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q20.groovy
@@ -1,80 +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("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q21.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q21.groovy
index c5995655d2..b150b32013 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q21.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q21.groovy
@@ -1,82 +1,78 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q22.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q22.groovy
index 61c4f5edf9..37e89e871f 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q22.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q22.groovy
@@ -1,79 +1,75 @@
-/*
- * 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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q3.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q3.groovy
index df679dd4ac..9485dded9d 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q3.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q3.groovy
@@ -1,69 +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("q3") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- // db = "tpch"
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql "set runtime_filter_mode='GLOBAL'"
-
- sql 'set exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
-
- 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;
- """
-}
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+suite("q3") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ // db = "tpch"
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql "set runtime_filter_mode='GLOBAL'"
+
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+
+
+sql 'set be_number_for_test=3'
+
+
+ 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_tpch_shape_sf500_p0/shape/q4.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q4.groovy
index b799c684b6..3b24cfad87 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q4.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q4.groovy
@@ -1,65 +1,61 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("q4") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql "set runtime_filter_mode='GLOBAL'"
-
- sql 'set exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
-
-
-
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
-
- 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;
- """
-}
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+suite("q4") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql "set runtime_filter_mode='GLOBAL'"
+
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+
+
+
+
+sql 'set be_number_for_test=3'
+
+
+ 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_tpch_shape_sf500_p0/shape/q5.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q5.groovy
index 4c1f11df7d..2c6b6b1b0c 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q5.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q5.groovy
@@ -1,63 +1,59 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("q5") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql "set runtime_filter_mode='GLOBAL'"
-
- sql 'set exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+suite("q5") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql "set runtime_filter_mode='GLOBAL'"
+
+ sql 'set exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q6.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q6.groovy
index 197044b152..e217da7d0b 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q6.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q6.groovy
@@ -1,48 +1,44 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("q6") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql "set runtime_filter_mode='GLOBAL'"
-
- sql 'set global exec_mem_limit = 21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+suite("q6") {
+ String db = context.config.getDbNameByFile(new File(context.file.parent))
+ sql "use ${db}"
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql "set runtime_filter_mode='GLOBAL'"
+
+ sql 'set global exec_mem_limit = 21G'
+ sql 'SET enable_pipeline_engine = true'
+
+sql 'set be_number_for_test=3'
+
+ qt_select """
+ explain shape plan
+ select
+ sum(l_extendedprice * l_discount) as revenue
+ from
+ lineitem
+ where
+ l_shipdate >= date '1994-01-01'
+ and l_shipdate < date '1994-01-01' + interval '1' year
+ and l_discount between .06 - 0.01 and .06 + 0.01
+ and l_quantity < 24;
+ """
+}
diff --git a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q7.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q7.groovy
index 3bdab06070..8de4955cf3 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q7.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q7.groovy
@@ -1,78 +1,74 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("q7") {
- String db = context.config.getDbNameByFile(new File(context.file.parent))
- sql "use ${db}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql "set runtime_filter_mode='GLOBAL'"
-
- sql 'set exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q8.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q8.groovy
index 99355e881b..fd259539a7 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q8.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q8.groovy
@@ -1,75 +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("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+sql 'set be_number_for_test=3'
+
+ 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_tpch_shape_sf500_p0/shape/q9.groovy b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q9.groovy
index 208d8cebe7..0d4f6207af 100644
--- a/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q9.groovy
+++ b/regression-test/suites/nereids_tpch_shape_sf500_p0/shape/q9.groovy
@@ -1,71 +1,67 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing,
- * software distributed under the License is distributed on an
- * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
- * KIND, either express or implied. See the License for the
- * specific language governing permissions and limitations
- * under the License.
- */
-
-suite("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 exec_mem_limit=21G'
- sql 'SET enable_pipeline_engine = true'
-
- def result = sql "show backends;"
- if (result.size() != 1) {
- print("backends num: ${result.size()}");
- return;
- }
-
- 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;
- """
-}
+/*
+ * 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 exec_mem_limit=21G'
+ sql 'SET enable_pipeline_engine = true'
+
+sql 'set be_number_for_test=3'
+
+ qt_select """
+ explain shape plan
+ select
+ nation,
+ o_year,
+ sum(amount) as sum_profit
+ from
+ (
+ select
+ 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-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org