You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2023/02/07 13:47:28 UTC
[doris] branch branch-1.2-lts updated: [branch-1.2] remove all nereids test cases
This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch branch-1.2-lts
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-1.2-lts by this push:
new 581806802c [branch-1.2] remove all nereids test cases
581806802c is described below
commit 581806802c00f953526a2f1a626b292b87fd6398
Author: morningman <mo...@163.com>
AuthorDate: Tue Feb 7 21:45:35 2023 +0800
[branch-1.2] remove all nereids test cases
---
.../data/datatype_p0/date/test_weekofyear.out | 4 -
.../data/nereids_syntax_p0/agg_with_const.out | 10 -
regression-test/data/nereids_syntax_p0/cte.out | 69 ------
.../data/nereids_syntax_p0/function.out | 26 ---
.../data/nereids_syntax_p0/group_by_constant.out | 4 -
.../data/nereids_syntax_p0/grouping_sets.out | 216 ------------------
regression-test/data/nereids_syntax_p0/having.out | 56 -----
.../data/nereids_syntax_p0/inpredicate.out | 33 ---
regression-test/data/nereids_syntax_p0/join.out | 62 -----
.../data/nereids_syntax_p0/sub_query_alias.out | 26 ---
.../nereids_syntax_p0/sub_query_correlated.out | 171 --------------
regression-test/data/nereids_syntax_p0/view.out | 31 ---
.../account_p0/test_nereids_authentication.groovy | 83 -------
.../account_p0/test_nereids_row_policy.groovy | 106 ---------
.../suites/datatype_p0/date/test_weekofyear.groovy | 37 ---
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q1.groovy | 79 -------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q10.groovy | 104 ---------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q11.groovy | 92 --------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q12.groovy | 95 --------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q13.groovy | 79 -------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q14.groovy | 65 ------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q15.groovy | 97 --------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q16.groovy | 99 --------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q17.groovy | 75 -------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q18.groovy | 110 ---------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q19.groovy | 108 ---------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q2.groovy | 131 -----------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q20.groovy | 101 ---------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q21.groovy | 109 ---------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q22.groovy | 112 ---------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q3.groovy | 85 -------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q4.groovy | 79 -------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q5.groovy | 88 --------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q6.groovy | 56 -----
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q7.groovy | 117 ----------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q8.groovy | 114 ----------
.../datev2/tpch_sf1_p1/tpch_sf1/nereids/q9.groovy | 96 --------
.../suites/nereids_syntax_p0/agg_with_const.groovy | 54 -----
.../suites/nereids_syntax_p0/bitmap_agg.groovy | 115 ----------
.../suites/nereids_syntax_p0/cte.groovy | 250 ---------------------
.../suites/nereids_syntax_p0/empty_relation.groovy | 33 ---
.../suites/nereids_syntax_p0/explain.groovy | 50 -----
.../suites/nereids_syntax_p0/function.groovy | 94 --------
.../nereids_syntax_p0/group_by_constant.groovy | 32 ---
.../suites/nereids_syntax_p0/grouping_sets.groovy | 162 -------------
.../suites/nereids_syntax_p0/having.groovy | 60 -----
.../suites/nereids_syntax_p0/hll_agg.groovy | 85 -------
.../suites/nereids_syntax_p0/inpredicate.groovy | 69 ------
.../suites/nereids_syntax_p0/join.groovy | 81 -------
.../suites/nereids_syntax_p0/load.groovy | 184 ---------------
.../nereids_syntax_p0/one_row_relation.groovy | 35 ---
.../suites/nereids_syntax_p0/rollup.groovy | 99 --------
.../suites/nereids_syntax_p0/set_var.groovy | 45 ----
.../nereids_syntax_p0/sub_query_alias.groovy | 74 ------
.../nereids_syntax_p0/sub_query_correlated.groovy | 234 -------------------
.../suites/nereids_syntax_p0/view.groovy | 105 ---------
regression-test/suites/ssb_sf1/nereids/q1.1.groovy | 45 ----
regression-test/suites/ssb_sf1/nereids/q1.2.groovy | 45 ----
regression-test/suites/ssb_sf1/nereids/q1.3.groovy | 46 ----
regression-test/suites/ssb_sf1/nereids/q2.1.groovy | 47 ----
regression-test/suites/ssb_sf1/nereids/q2.2.groovy | 48 ----
regression-test/suites/ssb_sf1/nereids/q2.3.groovy | 47 ----
regression-test/suites/ssb_sf1/nereids/q3.1.groovy | 48 ----
regression-test/suites/ssb_sf1/nereids/q3.2.groovy | 49 ----
regression-test/suites/ssb_sf1/nereids/q3.3.groovy | 50 -----
regression-test/suites/ssb_sf1/nereids/q3.4.groovy | 50 -----
regression-test/suites/ssb_sf1/nereids/q4.1.groovy | 51 -----
regression-test/suites/ssb_sf1/nereids/q4.2.groovy | 51 -----
regression-test/suites/ssb_sf1/nereids/q4.3.groovy | 49 ----
.../suites/tpch_sf1_p1/nereids/q1.groovy | 79 -------
.../suites/tpch_sf1_p1/nereids/q10.groovy | 104 ---------
.../suites/tpch_sf1_p1/nereids/q11.groovy | 93 --------
.../suites/tpch_sf1_p1/nereids/q12.groovy | 95 --------
.../suites/tpch_sf1_p1/nereids/q13.groovy | 79 -------
.../suites/tpch_sf1_p1/nereids/q14.groovy | 65 ------
.../suites/tpch_sf1_p1/nereids/q15.groovy | 97 --------
.../suites/tpch_sf1_p1/nereids/q16.groovy | 99 --------
.../suites/tpch_sf1_p1/nereids/q17.groovy | 75 -------
.../suites/tpch_sf1_p1/nereids/q18.groovy | 110 ---------
.../suites/tpch_sf1_p1/nereids/q19.groovy | 108 ---------
.../suites/tpch_sf1_p1/nereids/q2.groovy | 131 -----------
.../suites/tpch_sf1_p1/nereids/q20.groovy | 101 ---------
.../suites/tpch_sf1_p1/nereids/q21.groovy | 109 ---------
.../suites/tpch_sf1_p1/nereids/q22.groovy | 112 ---------
.../suites/tpch_sf1_p1/nereids/q3.groovy | 84 -------
.../suites/tpch_sf1_p1/nereids/q4.groovy | 79 -------
.../suites/tpch_sf1_p1/nereids/q5.groovy | 88 --------
.../suites/tpch_sf1_p1/nereids/q6.groovy | 56 -----
.../suites/tpch_sf1_p1/nereids/q7.groovy | 117 ----------
.../suites/tpch_sf1_p1/nereids/q8.groovy | 114 ----------
.../suites/tpch_sf1_p1/nereids/q9.groovy | 96 --------
91 files changed, 7603 deletions(-)
diff --git a/regression-test/data/datatype_p0/date/test_weekofyear.out b/regression-test/data/datatype_p0/date/test_weekofyear.out
deleted file mode 100644
index 619d323de0..0000000000
--- a/regression-test/data/datatype_p0/date/test_weekofyear.out
+++ /dev/null
@@ -1,4 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !weekOfYear --
-20000101 2000-01-01
-
diff --git a/regression-test/data/nereids_syntax_p0/agg_with_const.out b/regression-test/data/nereids_syntax_p0/agg_with_const.out
deleted file mode 100644
index 3dc5fdcfd6..0000000000
--- a/regression-test/data/nereids_syntax_p0/agg_with_const.out
+++ /dev/null
@@ -1,10 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !select --
-2 1996
-
--- !select --
-1
-
--- !select --
-2 4
-
diff --git a/regression-test/data/nereids_syntax_p0/cte.out b/regression-test/data/nereids_syntax_p0/cte.out
deleted file mode 100644
index e9a1795428..0000000000
--- a/regression-test/data/nereids_syntax_p0/cte.out
+++ /dev/null
@@ -1,69 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !cte_1 --
-15 15
-15 29
-15 9
-29 15
-29 29
-29 9
-9 15
-9 29
-9 9
-
--- !cte_2 --
-15 15
-15 29
-15 9
-9 15
-9 29
-9 9
-
--- !cte_3 --
-15 9
-9 9
-
--- !cte_4 --
-15 15
-15 9
-29 15
-29 9
-9 15
-9 9
-
--- !cte_5 --
-15 9
-29 9
-9 9
-
--- !cte_6 --
-15 Supplier#000000015 15 Supplier#000000015
-15 Supplier#000000015 9 Supplier#000000009
-29 Supplier#000000029 15 Supplier#000000015
-29 Supplier#000000029 9 Supplier#000000009
-9 Supplier#000000009 15 Supplier#000000015
-9 Supplier#000000009 9 Supplier#000000009
-
--- !cte_7 --
-15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601 15 Supplier#000000015
-15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601 9 Supplier#000000009
-29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342 15 Supplier#000000015
-29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342 9 Supplier#000000009
-9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675 15 Supplier#000000015
-9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675 9 Supplier#000000009
-
--- !cte_8 --
-15 Supplier#000000015
-9 Supplier#000000009
-
--- !cte_9 --
-15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-
--- !cte_10 --
-AMERICA 1
-ASIA 1
-
--- !cte_11 --
-15 Supplier#000000015 15 Supplier#000000015
-9 Supplier#000000009 9 Supplier#000000009
-
diff --git a/regression-test/data/nereids_syntax_p0/function.out b/regression-test/data/nereids_syntax_p0/function.out
deleted file mode 100644
index 844816f3dd..0000000000
--- a/regression-test/data/nereids_syntax_p0/function.out
+++ /dev/null
@@ -1,26 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !max --
-10 2959704
-
--- !min --
-7 1705830
-
--- !max_and_min --
-2959704 7
-
--- !count --
-3 3
-
--- !distinct_count --
-1
-1
-1
-
--- !distinct_count_group_by_distributed_key --
-1303 1
-1309 1
-1312 1
-
--- !avg --
-5.0 2356811.0
-
diff --git a/regression-test/data/nereids_syntax_p0/group_by_constant.out b/regression-test/data/nereids_syntax_p0/group_by_constant.out
deleted file mode 100644
index e5e0a2ded8..0000000000
--- a/regression-test/data/nereids_syntax_p0/group_by_constant.out
+++ /dev/null
@@ -1,4 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !select_1 --
-str 21 1309892 8 1
-str 24 1310179 10 1
diff --git a/regression-test/data/nereids_syntax_p0/grouping_sets.out b/regression-test/data/nereids_syntax_p0/grouping_sets.out
deleted file mode 100644
index 78b5a0284a..0000000000
--- a/regression-test/data/nereids_syntax_p0/grouping_sets.out
+++ /dev/null
@@ -1,216 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !select --
-2 0
-3 0
-4 0
-
--- !select --
-\N 1
-\N 1
-\N 1
-\N 1
-\N 1
-\N 1
-\N 1
-2 0
-3 0
-4 0
-
--- !select --
-\N 1
-\N 1
-\N 1
-\N 1
-\N 1
-\N 1
-\N 1
-2 0
-3 0
-4 0
-
--- !select --
-2 0
-3 0
-4 0
-
--- !select --
-3 0
-8 0
-9 0
-
--- !select --
-12 0
-13 0
-7 0
-
--- !select --
-12 0
-12 1
-13 0
-13 1
-7 0
-7 1
-
--- !select --
-12 0
-13 0
-7 0
-
--- !select --
-12 0
-13 0
-7 0
-
--- !select --
-2 0
-3 0
-4 0
-
--- !select --
-\N 1
-\N 1
-\N 1
-\N 1
-\N 1
-\N 1
-\N 1
-2 0
-3 0
-4 0
-
--- !select --
-\N 1
-\N 1
-\N 1
-\N 1
-\N 1
-\N 1
-\N 1
-2 0
-3 0
-4 0
-
--- !select --
-2 0
-3 0
-4 0
-
--- !select --
-\N 2
-\N 2
-\N 2
-\N 2
-2 1
-3 1
-4 1
-
--- !select --
-3 0
-8 0
-9 0
-
--- !select --
-12 0
-13 0
-7 0
-
--- !select --
-12 0
-12 1
-13 0
-13 1
-7 0
-7 1
-
--- !select --
-12 0
-13 0
-7 0
-
--- !select --
-12 0
-13 0
-7 0
-
--- !select --
-\N \N 36
-\N 0 \N
-\N 1 6
-\N 2 12
-\N 3 18
-1 \N 6
-1 0 \N
-1 1 6
-2 \N 12
-2 2 12
-3 \N 18
-3 0 \N
-3 3 18
-
--- !select2 --
-\N \N 36
-2 \N 6
-2 0 \N
-2 1 6
-3 \N 12
-3 2 12
-4 \N 18
-4 0 \N
-4 3 18
-
--- !select3 --
-\N \N 24
-\N \N 6
-\N 1 1
-\N 2 3
-\N 3 4
-\N 4 2
-\N 6 5
-\N 9 3
-1 \N 24
-1 \N 6
-1 1 1
-1 2 3
-1 3 4
-1 4 2
-1 6 5
-1 9 3
-
--- !select4 --
-\N \N 24
-0 bb 1
-0 dd 3
-1 ab 1
-1 ac 1
-1 ad 1
-2 bc 2
-2 bd 2
-2 cb 2
-2 cc 2
-3 cd 3
-3 db 3
-3 dc 3
-
--- !select5 --
-\N 0 \N
-\N 1 6
-\N 2 12
-\N 3 18
-2 0 \N
-2 1 6
-3 2 12
-4 0 \N
-4 3 18
-
--- !select6 --
-\N \N 5
-2 1 3
-2 2 2
-3 3 0
-
--- !select --
-\N 6
-1 \N
-2 \N
-3 \N
-
diff --git a/regression-test/data/nereids_syntax_p0/having.out b/regression-test/data/nereids_syntax_p0/having.out
deleted file mode 100644
index 858c26cdc3..0000000000
--- a/regression-test/data/nereids_syntax_p0/having.out
+++ /dev/null
@@ -1,56 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !select --
-1
-2
-3
-
--- !select --
-1
-2
-3
-
--- !select --
-12
-18
-6
-
--- !select --
-1
-2
-3
-
--- !select --
-1 6
-2 12
-3 18
-
--- !select --
-1 6
-2 12
-3 18
-
--- !select --
-1 6
-2 12
-3 18
-
--- !select --
-1 6
-2 12
-3 18
-
--- !select --
-1 9
-2 18
-3 27
-
--- !select --
-1 9
-2 18
-3 27
-
--- !select --
-1
-2
-3
-
diff --git a/regression-test/data/nereids_syntax_p0/inpredicate.out b/regression-test/data/nereids_syntax_p0/inpredicate.out
deleted file mode 100644
index cee03178b5..0000000000
--- a/regression-test/data/nereids_syntax_p0/inpredicate.out
+++ /dev/null
@@ -1,33 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !in_predicate_1 --
-
--- !in_predicate_2 --
-15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
-9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-
--- !in_predicate_3 --
-
--- !in_predicate_4 --
-
--- !in_predicate_5 --
-
--- !in_predicate_6 --
-15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
-9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-
--- !in_predicate_7 --
-
--- !in_predicate_8 --
-15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
-9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-
--- !in_predicate_9 --
-15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-
--- !in_predicate_10 --
-29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
-9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-
diff --git a/regression-test/data/nereids_syntax_p0/join.out b/regression-test/data/nereids_syntax_p0/join.out
deleted file mode 100644
index 4859959046..0000000000
--- a/regression-test/data/nereids_syntax_p0/join.out
+++ /dev/null
@@ -1,62 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !inner_join_1 --
-1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK 15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL 9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB 29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
-
--- !inner_join_2 --
-1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK 15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL 9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB 29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
-
--- !left_outer_join_1 --
-1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK 15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL 9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB 29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
-
--- !left_outer_join_2 --
-1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK 15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL 9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB 29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
-
--- !right_outer_join_1 --
-1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK 15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL 9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB 29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
-
--- !right_outer_join_2 --
-1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK 15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL 9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB 29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
-
--- !left_semi_join --
-1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK
-1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL
-1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB
-
--- !right_semi_join --
-15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
-9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-
--- !left_anti_join --
-
--- !right_anti_join --
-
--- !cross_join --
-1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK 15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK 29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
-1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK 9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL 15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL 29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
-1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL 9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB 15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
-1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB 29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
-1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB 9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
-
--- !inner_join_with_other_condition --
-1310179 1455 1455 42
-
--- !outer_join_with_filter --
-1310179 1455 1455 42
-
diff --git a/regression-test/data/nereids_syntax_p0/sub_query_alias.out b/regression-test/data/nereids_syntax_p0/sub_query_alias.out
deleted file mode 100644
index bd9b380b01..0000000000
--- a/regression-test/data/nereids_syntax_p0/sub_query_alias.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_1 --
-1303 1303
-1303 1303
-1312 1312
-
--- !select_2 --
-1303 1303
-1303 1303
-1312 1312
-
--- !select_3 --
-1303 1303
-1303 1303
-1312 1312
-
--- !select_4 --
-1303 Customer#000001303 fQ Lp,FoozZe1 ETHIOPIA 3 ETHIOPIA AFRICA 15-658-234-7985 MACHINERY 1303 Customer#000001303 fQ Lp,FoozZe1 ETHIOPIA 3 ETHIOPIA AFRICA 15-658-234-7985 MACHINERY
-1309 Customer#000001309 vQcJGUXPHMH2 5OWs1XUP0kx IRAN 2 IRAN MIDDLE EAST 20-821-905-5952 AUTOMOBILE 1309 Customer#000001309 vQcJGUXPHMH2 5OWs1XUP0kx IRAN 2 IRAN MIDDLE EAST 20-821-905-5952 AUTOMOBILE
-1312 Customer#000001312 MVsKeqWejff8jQ30 CANADA 9 CANADA AMERICA 13-153-492-9898 BUILDING 1312 Customer#000001312 MVsKeqWejff8jQ30 CANADA 9 CANADA AMERICA 13-153-492-9898 BUILDING
-
--- !select_5 --
-1303 Customer#000001303 fQ Lp,FoozZe1 ETHIOPIA 3 ETHIOPIA AFRICA 15-658-234-7985 MACHINERY 1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK
-1303 Customer#000001303 fQ Lp,FoozZe1 ETHIOPIA 3 ETHIOPIA AFRICA 15-658-234-7985 MACHINERY 1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL
-1312 Customer#000001312 MVsKeqWejff8jQ30 CANADA 9 CANADA AMERICA 13-153-492-9898 BUILDING 1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB
-
diff --git a/regression-test/data/nereids_syntax_p0/sub_query_correlated.out b/regression-test/data/nereids_syntax_p0/sub_query_correlated.out
deleted file mode 100644
index 7ff313f20d..0000000000
--- a/regression-test/data/nereids_syntax_p0/sub_query_correlated.out
+++ /dev/null
@@ -1,171 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !scalar_less_than_corr --
-1 2
-1 3
-2 4
-3 3
-3 4
-
--- !scalar_not_equal_corr --
-1 2
-1 3
-2 4
-3 3
-3 4
-20 2
-22 3
-24 4
-
--- !scalar_equal_to_corr --
-
--- !not_in_corr --
-1 2
-1 3
-2 4
-2 5
-3 4
-20 2
-22 3
-24 4
-
--- !in_subquery_corr --
-3 3
-
--- !not_exist_corr --
-2 5
-
--- !exist_corr --
-1 2
-1 3
-2 4
-3 3
-3 4
-20 2
-22 3
-24 4
-
--- !in_with_in_and_scalar --
-
--- !exist_and_not_exist --
-
--- !scalar_unCorrelated --
-1 2
-1 3
-2 4
-2 5
-3 3
-3 4
-
--- !scalar_equal_to_uncorr --
-
--- !not_scalar_unCorrelated --
-1 2
-1 3
-2 4
-2 5
-3 3
-3 4
-20 2
-22 3
-24 4
-
--- !scalar_not_equal_uncorr --
-1 2
-1 3
-2 4
-2 5
-3 3
-3 4
-20 2
-22 3
-24 4
-
--- !in_unCorrelated --
-2 4
-2 5
-3 3
-3 4
-
--- !in_subquery_uncorr --
-2 4
-2 5
-3 3
-3 4
-
--- !not_in_unCorrelated --
-1 2
-1 3
-20 2
-22 3
-24 4
-
--- !not_in_uncorr --
-1 2
-1 3
-20 2
-22 3
-24 4
-
--- !exist_unCorrelated --
-1 2
-1 3
-2 4
-2 5
-3 3
-3 4
-20 2
-22 3
-24 4
-
--- !exist_uncorr --
-1 2
-1 3
-2 4
-2 5
-3 3
-3 4
-20 2
-22 3
-24 4
-
--- !alias_scalar --
-1 2
-1 3
-
--- !alias_in --
-3 3
-
--- !alias_not_in --
-1 2
-1 3
-2 4
-2 5
-3 4
-20 2
-22 3
-24 4
-
--- !alias_exist --
-1 2
-1 3
-2 4
-3 3
-3 4
-20 2
-22 3
-24 4
-
--- !alias_not_exist --
-2 5
-
--- !scalar_subquery --
-
--- !in_subquery --
-1 abc 2 3 4
-1 abcd 3 3 4
-
--- !exist_subquery --
-2 uvw 3 4 2
-2 uvw 3 4 2
-2 xyz 2 4 2
-
diff --git a/regression-test/data/nereids_syntax_p0/view.out b/regression-test/data/nereids_syntax_p0/view.out
deleted file mode 100644
index 64de8a53e6..0000000000
--- a/regression-test/data/nereids_syntax_p0/view.out
+++ /dev/null
@@ -1,31 +0,0 @@
--- This file is automatically generated. You should know what you did if you want to edit this
--- !select_1 --
-1303 Customer#000001303 fQ Lp,FoozZe1 ETHIOPIA 3 ETHIOPIA AFRICA 15-658-234-7985 MACHINERY
-1309 Customer#000001309 vQcJGUXPHMH2 5OWs1XUP0kx IRAN 2 IRAN MIDDLE EAST 20-821-905-5952 AUTOMOBILE
-1312 Customer#000001312 MVsKeqWejff8jQ30 CANADA 9 CANADA AMERICA 13-153-492-9898 BUILDING
-
--- !select_2 --
-1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK
-1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL
-1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB
-
--- !select_3 --
-1303 Customer#000001303 fQ Lp,FoozZe1 ETHIOPIA 3 ETHIOPIA AFRICA 15-658-234-7985 MACHINERY 1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK
-1303 Customer#000001303 fQ Lp,FoozZe1 ETHIOPIA 3 ETHIOPIA AFRICA 15-658-234-7985 MACHINERY 1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL
-1312 Customer#000001312 MVsKeqWejff8jQ30 CANADA 9 CANADA AMERICA 13-153-492-9898 BUILDING 1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB
-
--- !select_4 --
-1303 Customer#000001303 fQ Lp,FoozZe1 ETHIOPIA 3 ETHIOPIA AFRICA 15-658-234-7985 MACHINERY 1303 Customer#000001303 fQ Lp,FoozZe1 ETHIOPIA 3 ETHIOPIA AFRICA 15-658-234-7985 MACHINERY
-1309 Customer#000001309 vQcJGUXPHMH2 5OWs1XUP0kx IRAN 2 IRAN MIDDLE EAST 20-821-905-5952 AUTOMOBILE 1309 Customer#000001309 vQcJGUXPHMH2 5OWs1XUP0kx IRAN 2 IRAN MIDDLE EAST 20-821-905-5952 AUTOMOBILE
-1312 Customer#000001312 MVsKeqWejff8jQ30 CANADA 9 CANADA AMERICA 13-153-492-9898 BUILDING 1312 Customer#000001312 MVsKeqWejff8jQ30 CANADA 9 CANADA AMERICA 13-153-492-9898 BUILDING
-
--- !select_5 --
-1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK 1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK
-1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK 1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL
-1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL 1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK
-1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL 1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL
-1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB 1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB
-
--- !select_6 --
-1455 magenta blush MFGR#1 MFGR#13 MFGR#1324 blue LARGE BRUSHED STEEL 42 SM PACK 1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB 29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342 1312 Customer#000001312 MVsKeqWejff8jQ30 CANADA 9 CANADA AMERICA 13-153-492-9898 BUILDING 1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB
-
diff --git a/regression-test/suites/account_p0/test_nereids_authentication.groovy b/regression-test/suites/account_p0/test_nereids_authentication.groovy
deleted file mode 100644
index 6a2a980401..0000000000
--- a/regression-test/suites/account_p0/test_nereids_authentication.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("test_nereids_authentication", "query") {
- def create_table = { tableName ->
- sql "DROP TABLE IF EXISTS ${tableName}"
- sql """
- CREATE TABLE ${tableName} (
- `key` INT,
- value INT
- ) DUPLICATE KEY (`key`) DISTRIBUTED BY HASH (`key`) BUCKETS 1
- PROPERTIES ('replication_num' = '1')
- """
- }
-
- sql "set enable_nereids_planner = true"
-
- def dbName = "nereids_authentication"
- sql "DROP DATABASE IF EXISTS ${dbName}"
- sql "CREATE DATABASE ${dbName}"
- sql "USE ${dbName}"
-
- def tableName1 = "accessible_table";
- def tableName2 = "inaccessible_table";
- create_table.call(tableName1);
- create_table.call(tableName2);
-
- def user='nereids_user'
- try_sql "DROP USER ${user}"
- sql "CREATE USER ${user} IDENTIFIED BY '123456'"
- sql "GRANT SELECT_PRIV ON internal.${dbName}.${tableName1} TO ${user}"
-
- def tokens = context.config.jdbcUrl.split('/')
- def url=tokens[0] + "//" + tokens[2] + "/" + dbName + "?"
- def result = connect(user=user, password='123456', url=url) {
- sql "SELECT * FROM ${tableName1}"
- }
- assertEquals(result.size(), 0)
-
- connect(user=user, password='123456', url=url) {
- try {
- sql "SELECT * FROM ${tableName2}"
- fail()
- } catch (Exception e) {
- log.info(e.getMessage())
- assertTrue(e.getMessage().contains('SELECT command denied to user'))
- }
- }
-
- connect(user=user, password='123456', url=url) {
- try {
- sql "SELECT * FROM ${tableName1}, ${tableName2} WHERE ${tableName1}.`key` = ${tableName2}.`key`"
- fail()
- } catch (Exception e) {
- log.info(e.getMessage())
- assertTrue(e.getMessage().contains('SELECT command denied to user'))
- }
- }
-
- sql "GRANT SELECT_PRIV ON internal.${dbName}.${tableName2} TO ${user}"
- connect(user=user, password='123456', url=url) {
- sql "SELECT * FROM ${tableName2}"
- }
- assertEquals(result.size(), 0)
- connect(user=user, password='123456', url=url) {
- sql "SELECT * FROM ${tableName1}, ${tableName2} WHERE ${tableName1}.`key` = ${tableName2}.`key`"
- }
- assertEquals(result.size(), 0)
-}
diff --git a/regression-test/suites/account_p0/test_nereids_row_policy.groovy b/regression-test/suites/account_p0/test_nereids_row_policy.groovy
deleted file mode 100644
index 5860527306..0000000000
--- a/regression-test/suites/account_p0/test_nereids_row_policy.groovy
+++ /dev/null
@@ -1,106 +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("test_nereids_row_policy") {
- def dbName = context.config.getDbNameByFile(context.file)
- def tableName = "nereids_row_policy"
- def viewName = "view_" + tableName
- def user='row_policy_user'
- def tokens = context.config.jdbcUrl.split('/')
- def url=tokens[0] + "//" + tokens[2] + "/" + dbName + "?"
-
- def assertQueryResult = { size ->
- def result1 = connect(user=user, password='123456', url=url) {
- sql "set enable_nereids_planner = false"
- sql "SELECT * FROM ${tableName}"
- }
- def result2 = connect(user=user, password='123456', url=url) {
- sql "set enable_nereids_planner = true"
- sql "set enable_fallback_to_original_planner = false"
- sql "SELECT * FROM ${tableName}"
- }
- def result3 = connect(user=user, password='123456', url=url) {
- sql "set enable_nereids_planner = true"
- sql "set enable_fallback_to_original_planner = false"
- sql "SELECT * FROM ${viewName}"
- }
- assertEquals(size, result1.size())
- assertEquals(size, result2.size())
- assertEquals(size, result3.size())
- }
-
- def createPolicy = { name, predicate, type ->
- sql """
- CREATE ROW POLICY IF NOT EXISTS ${name} ON ${dbName}.${tableName}
- AS ${type} TO ${user} USING (${predicate})
- """
- }
-
- def dropPolciy = { name ->
- sql """
- DROP ROW POLICY IF EXISTS ${name} ON ${dbName}.${tableName} FOR ${user}
- """
- }
-
- // create table
- sql "DROP TABLE IF EXISTS ${tableName}"
- sql """
- CREATE TABLE ${tableName} (
- `k` INT,
- `v` INT
- ) DUPLICATE KEY (`k`) DISTRIBUTED BY HASH (`k`) BUCKETS 1
- PROPERTIES ('replication_num' = '1')
- """
- sql """
- insert into ${tableName} values (1,1), (2,1), (1,3);
- """
-
- // create view
- sql "DROP VIEW IF EXISTS ${viewName}"
- sql """
- CREATE VIEW ${viewName} AS SELECT * FROM ${tableName};
- """
-
- // create user
- sql "DROP USER IF EXISTS ${user}"
- sql "CREATE USER ${user} IDENTIFIED BY '123456'"
- sql "GRANT SELECT_PRIV ON internal.${dbName}.${tableName} TO ${user}"
-
-
- // no policy
- assertQueryResult 3
-
- // (k = 1)
- createPolicy"policy0", "k = 1", "RESTRICTIVE"
- assertQueryResult 2
-
- // (k = 1 and v = 1)
- createPolicy"policy1", "v = 1", "RESTRICTIVE"
- assertQueryResult 1
-
- // (v = 1)
- dropPolciy "policy0"
- assertQueryResult 2
-
- // (v = 1) and (k = 1)
- createPolicy"policy2", "k = 1", "PERMISSIVE"
- assertQueryResult 1
-
- // (v = 1) and (k = 1 or k = 2)
- createPolicy"policy3", "k = 2", "PERMISSIVE"
- assertQueryResult 2
-
-}
diff --git a/regression-test/suites/datatype_p0/date/test_weekofyear.groovy b/regression-test/suites/datatype_p0/date/test_weekofyear.groovy
deleted file mode 100644
index 8f45396561..0000000000
--- a/regression-test/suites/datatype_p0/date/test_weekofyear.groovy
+++ /dev/null
@@ -1,37 +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("test_weekofyear") {
- sql "DROP TABLE IF EXISTS woy"
-
- sql """
- CREATE TABLE IF NOT EXISTS woy (
- c0 int,
- c1 varchar(10)
- )
- DUPLICATE KEY(c0)
- DISTRIBUTED BY HASH(c0) BUCKETS 1 properties("replication_num" = "1")
- """
- sql "insert into woy values(20000101, '2000-01-01')"
- sql "insert into woy values(20000110, '2000-01-10')"
-
-
- sql "set enable_nereids_planner=true"
- sql "set enable_vectorized_engine=true"
- qt_weekOfYear "select * from woy where weekofyear(c0)=52 and weekofyear(c1)=52"
-}
diff --git a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q1.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q1.groovy
deleted file mode 100644
index 57d779fece..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q1.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("tpch_sf1_q1_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=false) */
- 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/datev2/tpch_sf1_p1/tpch_sf1/nereids/q10.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q10.groovy
deleted file mode 100644
index e50d57d63d..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q10.groovy
+++ /dev/null
@@ -1,104 +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("tpch_sf1_q10_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */
- c_custkey,
- c_name,
- sum(t1.l_extendedprice * (1 - t1.l_discount)) as revenue,
- c_acctbal,
- n_name,
- c_address,
- c_phone,
- c_comment
- from
- customer,
- (
- select o_custkey,l_extendedprice,l_discount from lineitem, orders
- where 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'
- ) t1,
- nation
- where
- c_custkey = t1.o_custkey
- 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/datev2/tpch_sf1_p1/tpch_sf1/nereids/q11.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q11.groovy
deleted file mode 100644
index b3420580c9..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q11.groovy
+++ /dev/null
@@ -1,92 +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("tpch_sf1_q11_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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.0001000000
- from
- partsupp,
- supplier,
- nation
- where
- ps_suppkey = s_suppkey
- and s_nationkey = n_nationkey
- and n_name = 'GERMANY'
- )
- order by
- value desc, ps_partkey;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
- ps_partkey,
- sum(ps_supplycost * ps_availqty) as value
- from
- partsupp,
- (
- select s_suppkey
- from supplier, nation
- where s_nationkey = n_nationkey and n_name = 'GERMANY'
- ) B
- where
- ps_suppkey = B.s_suppkey
- group by
- ps_partkey having
- sum(ps_supplycost * ps_availqty) > (
- select
- sum(ps_supplycost * ps_availqty) * 0.000002
- from
- partsupp,
- (select s_suppkey
- from supplier, nation
- where s_nationkey = n_nationkey and n_name = 'GERMANY'
- ) A
- where
- ps_suppkey = A.s_suppkey
- )
- order by
- value desc, ps_partkey;
- """
-
-}
diff --git a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q12.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q12.groovy
deleted file mode 100644
index 0e204b1de5..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q12.groovy
+++ /dev/null
@@ -1,95 +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("tpch_sf1_q12_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
- 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/datev2/tpch_sf1_p1/tpch_sf1/nereids/q13.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q13.groovy
deleted file mode 100644
index c6949f8395..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q13.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("tpch_sf1_q13_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */
- c_count,
- count(*) as custdist
- from
- (
- select
- c_custkey,
- count(o_orderkey) as c_count
- from
- orders right outer join customer 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/datev2/tpch_sf1_p1/tpch_sf1/nereids/q14.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q14.groovy
deleted file mode 100644
index ae13d446b1..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q14.groovy
+++ /dev/null
@@ -1,65 +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("tpch_sf1_q14_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */
- 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
- part,
- lineitem
-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/datev2/tpch_sf1_p1/tpch_sf1/nereids/q15.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q15.groovy
deleted file mode 100644
index 6c0a6c8add..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q15.groovy
+++ /dev/null
@@ -1,97 +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("tpch_sf1_q15_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- 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 'set enable_fallback_to_original_planner=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
- 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/datev2/tpch_sf1_p1/tpch_sf1/nereids/q16.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q16.groovy
deleted file mode 100644
index 9ce7badadd..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q16.groovy
+++ /dev/null
@@ -1,99 +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("tpch_sf1_q16_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
- 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/datev2/tpch_sf1_p1/tpch_sf1/nereids/q17.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q17.groovy
deleted file mode 100644
index 552e53104d..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q17.groovy
+++ /dev/null
@@ -1,75 +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("tpch_sf1_q17_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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
- );
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
- sum(l_extendedprice) / 7.0 as avg_yearly
- from
- lineitem join
- part p1 on p1.p_partkey = l_partkey
- where
- p1.p_brand = 'Brand#23'
- and p1.p_container = 'MED BOX'
- and l_quantity < (
- select
- 0.2 * avg(l_quantity)
- from
- lineitem join
- part p2 on p2.p_partkey = l_partkey
- where
- l_partkey = p1.p_partkey
- and p2.p_brand = 'Brand#23'
- and p2.p_container = 'MED BOX'
- );
-
- """
-
-}
diff --git a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q18.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q18.groovy
deleted file mode 100644
index 19ec0ae171..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q18.groovy
+++ /dev/null
@@ -1,110 +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("tpch_sf1_q18_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */
- c_name,
- c_custkey,
- t3.o_orderkey,
- t3.o_orderdate,
- t3.o_totalprice,
- sum(t3.l_quantity)
-from
-customer join
-(
- select * from
- lineitem join
- (
- select * from
- orders left semi join
- (
- select
- l_orderkey
- from
- lineitem
- group by
- l_orderkey having sum(l_quantity) > 300
- ) t1
- on o_orderkey = t1.l_orderkey
- ) t2
- on t2.o_orderkey = l_orderkey
-) t3
-on c_custkey = t3.o_custkey
-group by
- c_name,
- c_custkey,
- t3.o_orderkey,
- t3.o_orderdate,
- t3.o_totalprice
-order by
- t3.o_totalprice desc,
- t3.o_orderdate
-limit 100;
-
- """
-}
-
diff --git a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q19.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q19.groovy
deleted file mode 100644
index 03b31175a5..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q19.groovy
+++ /dev/null
@@ -1,108 +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("tpch_sf1_q19_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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'
- );
- """
-
- qt_select """
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true) */
- 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/datev2/tpch_sf1_p1/tpch_sf1/nereids/q2.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q2.groovy
deleted file mode 100644
index 172dfe0fb9..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q2.groovy
+++ /dev/null
@@ -1,131 +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("tpch_sf1_q2_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */
- s_acctbal,
- s_name,
- n_name,
- p_partkey,
- p_mfgr,
- s_address,
- s_phone,
- s_comment
-from
- partsupp join
- (
- select
- ps_partkey as a_partkey,
- min(ps_supplycost) as a_min
- from
- partsupp,
- part,
- 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'
- and p_size = 15
- and p_type like '%BRASS'
- group by ps_partkey
- ) A on ps_partkey = a_partkey and ps_supplycost=a_min ,
- part,
- supplier,
- 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'
-
-order by
- s_acctbal desc,
- n_name,
- s_name,
- p_partkey
-limit 100;
- """
-}
diff --git a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q20.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q20.groovy
deleted file mode 100644
index 6780f03f1c..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q20.groovy
+++ /dev/null
@@ -1,101 +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("tpch_sf1_q20_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */
-s_name, s_address from
-supplier left semi join
-(
- select * from
- (
- select l_partkey,l_suppkey, 0.5 * sum(l_quantity) as l_q
- from lineitem
- where l_shipdate >= date '1994-01-01'
- and l_shipdate < date '1994-01-01' + interval '1' year
- group by l_partkey,l_suppkey
- ) t2 join
- (
- select ps_partkey, ps_suppkey, ps_availqty
- from partsupp left semi join part
- on ps_partkey = p_partkey and p_name like 'forest%'
- ) t1
- on t2.l_partkey = t1.ps_partkey and t2.l_suppkey = t1.ps_suppkey
- and t1.ps_availqty > t2.l_q
-) t3
-on s_suppkey = t3.ps_suppkey
-join nation
-where s_nationkey = n_nationkey
- and n_name = 'CANADA'
-order by s_name;
-
- """
-
-}
diff --git a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q21.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q21.groovy
deleted file mode 100644
index f2c7dd5ef7..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q21.groovy
+++ /dev/null
@@ -1,109 +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("tpch_sf1_q21_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */
-s_name, count(*) as numwait
-from orders join
-(
- select * from
- lineitem l2 right semi join
- (
- select * from
- lineitem l3 right anti join
- (
- select * from
- lineitem l1 join
- (
- select * from
- supplier join nation
- where s_nationkey = n_nationkey
- and n_name = 'SAUDI ARABIA'
- ) t1
- where t1.s_suppkey = l1.l_suppkey and l1.l_receiptdate > l1.l_commitdate
- ) t2
- on l3.l_orderkey = t2.l_orderkey and l3.l_suppkey <> t2.l_suppkey and l3.l_receiptdate > l3.l_commitdate
- ) t3
- on l2.l_orderkey = t3.l_orderkey and l2.l_suppkey <> t3.l_suppkey
-) t4
-on o_orderkey = t4.l_orderkey and o_orderstatus = 'F'
-group by
- t4.s_name
-order by
- numwait desc,
- t4.s_name
-limit 100;
- """
-
-}
\ No newline at end of file
diff --git a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q22.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q22.groovy
deleted file mode 100644
index bbd1dfa8bb..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q22.groovy
+++ /dev/null
@@ -1,112 +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("tpch_sf1_q22_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
- 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/datev2/tpch_sf1_p1/tpch_sf1/nereids/q3.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q3.groovy
deleted file mode 100644
index 94ffd36280..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q3.groovy
+++ /dev/null
@@ -1,85 +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("tpch_sf1_q3_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */
- l_orderkey,
- sum(l_extendedprice * (1 - l_discount)) as revenue,
- o_orderdate,
- o_shippriority
- from
- (
- select l_orderkey, l_extendedprice, l_discount, o_orderdate, o_shippriority, o_custkey from
- lineitem join orders
- where l_orderkey = o_orderkey
- and o_orderdate < date '1995-03-15'
- and l_shipdate > date '1995-03-15'
- ) t1 join customer c
- on c.c_custkey = t1.o_custkey
- where c_mktsegment = 'BUILDING'
- group by
- l_orderkey,
- o_orderdate,
- o_shippriority
- order by
- revenue desc,
- o_orderdate
- limit 10;
-
- """
-
-}
diff --git a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q4.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q4.groovy
deleted file mode 100644
index 8f4c4cf30e..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q4.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("tpch_sf1_q4_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */
- o_orderpriority,
- count(*) as order_count
- from
- (
- select
- *
- from
- lineitem
- where l_commitdate < l_receiptdate
- ) t1
- right semi join orders
- on t1.l_orderkey = o_orderkey
- where
- o_orderdate >= date '1993-07-01'
- and o_orderdate < date '1993-07-01' + interval '3' month
- group by
- o_orderpriority
- order by
- o_orderpriority;
- """
-
-}
diff --git a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q5.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q5.groovy
deleted file mode 100644
index 2cf3f00d48..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q5.groovy
+++ /dev/null
@@ -1,88 +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("tpch_sf1_q5_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
-
- select /*+SET_VAR(exec_mem_limit=17179869184, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true) */
- 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/datev2/tpch_sf1_p1/tpch_sf1/nereids/q6.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q6.groovy
deleted file mode 100644
index 9ece6bccd3..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q6.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("tpch_sf1_q6_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true) */
- 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/datev2/tpch_sf1_p1/tpch_sf1/nereids/q7.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q7.groovy
deleted file mode 100644
index cf18a3aa2e..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q7.groovy
+++ /dev/null
@@ -1,117 +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("tpch_sf1_q7_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true) */
- 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/datev2/tpch_sf1_p1/tpch_sf1/nereids/q8.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q8.groovy
deleted file mode 100644
index e1d112ae08..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q8.groovy
+++ /dev/null
@@ -1,114 +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("tpch_sf1_q8_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
-
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */
- 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
- lineitem,
- orders,
- customer,
- supplier,
- part,
- 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/datev2/tpch_sf1_p1/tpch_sf1/nereids/q9.groovy b/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q9.groovy
deleted file mode 100644
index c5ba64452b..0000000000
--- a/regression-test/suites/datev2/tpch_sf1_p1/tpch_sf1/nereids/q9.groovy
+++ /dev/null
@@ -1,96 +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("tpch_sf1_q9_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select/*+SET_VAR(exec_mem_limit=17179869184, parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true, enable_runtime_filter_prune=true, runtime_filter_wait_time_ms=10000) */
- 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
- lineitem join orders on o_orderkey = l_orderkey
- join part on p_partkey = l_partkey
- join partsupp on ps_partkey = l_partkey
- join supplier on s_suppkey = l_suppkey
- join nation on s_nationkey = n_nationkey
- where
- ps_suppkey = l_suppkey 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_syntax_p0/agg_with_const.groovy b/regression-test/suites/nereids_syntax_p0/agg_with_const.groovy
deleted file mode 100644
index c5d6abe08f..0000000000
--- a/regression-test/suites/nereids_syntax_p0/agg_with_const.groovy
+++ /dev/null
@@ -1,54 +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("agg_with_const") {
-
- sql "SET enable_vectorized_engine=true"
- sql "SET enable_nereids_planner=true"
-
- sql """
- DROP TABLE IF EXISTS agg_with_const_tbl
- """
-
- sql """CREATE TABLE IF NOT EXISTS agg_with_const_tbl (col1 int not null, col2 int not null, col3 int not null)
- DISTRIBUTED BY HASH(col3)
- BUCKETS 1
- PROPERTIES(
- "replication_num"="1"
- )
- """
-
- sql """
- insert into agg_with_const_tbl values(1994, 1994, 1995)
- """
-
- sql "SET enable_fallback_to_original_planner=false"
-
- sql """sync"""
- qt_select """
- select count(2) + 1, sum(2) + sum(col1) from agg_with_const_tbl
- """
-
- qt_select """
- select count(*) from agg_with_const_tbl
- """
-
- qt_select """
- select count(2) + 1, sum(2) + sum(2) from agg_with_const_tbl
- """
-
-}
diff --git a/regression-test/suites/nereids_syntax_p0/bitmap_agg.groovy b/regression-test/suites/nereids_syntax_p0/bitmap_agg.groovy
deleted file mode 100644
index 3bea79edd3..0000000000
--- a/regression-test/suites/nereids_syntax_p0/bitmap_agg.groovy
+++ /dev/null
@@ -1,115 +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("bitmap_agg") {
- sql "SET enable_vectorized_engine=true"
- sql "SET enable_nereids_planner=true"
- sql "SET enable_fallback_to_original_planner=false"
-
- // bitmap_union, bitmap_union_count, bitmap_union_int
- test {
- sql """
- select
- bitmap_union_count(b),
- bitmap_to_string(bitmap_union(b)),
- bitmap_union_int(number)
- from
- (
- select to_bitmap(number) b, number from numbers(number = 10) tmp
- ) t
- """
- result([[10L, "0,1,2,3,4,5,6,7,8,9", 10L]])
- }
-
-
- // bitmap_intersect
- def test_bitmap_intersect_table = "test_bitmap_intersect_table"
-
- sql "drop table if exists ${test_bitmap_intersect_table}"
-
- sql """
- CREATE TABLE IF NOT EXISTS ${test_bitmap_intersect_table} (
- `id` int,
- `bitmap_string` string
- ) ENGINE=OLAP
- DISTRIBUTED BY HASH(`id`) BUCKETS 1
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1",
- "storage_format" = "V2"
- )
- """
-
- sql """insert into ${test_bitmap_intersect_table}
- values
- (1, '1,2,5'),
- (2, '2,5,7'),
- (3, '1,5,9')
- """
- test {
- sql """
- select bitmap_to_string(bitmap_intersect(bitmap_from_string(bitmap_string)))
- from ${test_bitmap_intersect_table}
- """
- result([["5"]])
- }
-
- test {
- sql """
- select bitmap_to_string(bitmap_intersect(bitmap_from_string(bitmap_string)))
- from ${test_bitmap_intersect_table}
- where id in (1, 2)
- """
- result([["2,5"]])
- }
-
- // group_bitmap_xor
-
- def test_group_bitmap_xor_table = "test_group_bitmap_xor_table"
-
- sql "drop table if exists ${test_group_bitmap_xor_table}"
-
- sql """
- CREATE TABLE IF NOT EXISTS ${test_group_bitmap_xor_table} (
- `id` int,
- `bitmap_string` string
- ) ENGINE=OLAP
- DISTRIBUTED BY HASH(`id`) BUCKETS 1
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1",
- "storage_format" = "V2"
- )
- """
-
- sql """insert into ${test_group_bitmap_xor_table}
- values
- (1, '4,7,8'),
- (1, '1,3,6,15'),
- (1, '4,7')
- """
-
- test {
- sql """
- select
- id,
- bitmap_to_string(group_bitmap_xor(bitmap_from_string(bitmap_string)))
- from ${test_group_bitmap_xor_table}
- group by id
- order by id
- """
- result([[1, "1,3,6,8,15"]])
- }
-}
diff --git a/regression-test/suites/nereids_syntax_p0/cte.groovy b/regression-test/suites/nereids_syntax_p0/cte.groovy
deleted file mode 100644
index f6864aca54..0000000000
--- a/regression-test/suites/nereids_syntax_p0/cte.groovy
+++ /dev/null
@@ -1,250 +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("cte") {
-
- sql "SET enable_vectorized_engine=true"
- sql "SET enable_nereids_planner=true"
-
- sql "DROP VIEW IF EXISTS cte_v1"
- sql "DROP VIEW IF EXISTS cte_v2"
-
- sql """
- CREATE VIEW cte_v1 AS
- SELECT *
- FROM supplier
- """
-
- sql """
- CREATE VIEW cte_v2 AS
- SELECT *
- FROM supplier
- """
-
- sql "SET enable_fallback_to_original_planner=false"
-
- order_qt_cte_1 """
- WITH cte1 AS (
- SELECT s_suppkey
- FROM supplier
- WHERE s_suppkey < 30
- )
- SELECT *
- FROM cte1 as t1, cte1 as t2
- """
-
- // test multiple CTEs
- order_qt_cte_2 """
- WITH cte1 AS (
- SELECT s_suppkey
- FROM supplier
- WHERE s_suppkey < 20
- ), cte2 AS (
- SELECT s_suppkey
- FROM supplier
- WHERE s_suppkey < 30
- ), cte3 AS (
- SELECT s_suppkey
- FROM supplier
- WHERE s_suppkey < 10
- )
- SELECT *
- FROM cte1, cte2
- """
-
- // test ordered reference between different tables
- order_qt_cte_3 """
- WITH cte1 AS (
- SELECT s_suppkey
- FROM supplier
- WHERE s_suppkey < 30
- ), cte2 AS (
- SELECT s_suppkey
- FROM cte1
- WHERE s_suppkey < 20
- ), cte3 AS (
- SELECT s_suppkey
- FROM cte2
- WHERE s_suppkey < 10
- )
- SELECT *
- FROM cte2, cte3
- """
-
- // if the CTE name is the same as an existing table name, the CTE's name will be chosen and used first
- order_qt_cte_4 """
- WITH part AS (
- SELECT s_suppkey
- FROM supplier
- WHERE s_suppkey < 30
- ), customer AS (
- SELECT s_suppkey
- FROM supplier
- WHERE s_suppkey < 20
- )
- SELECT *
- FROM part, customer
- """
-
- // the processing logic is similiar to cte_4 when the CTE name is the same as an existing view name
- order_qt_cte_5 """
- WITH v1 AS (
- SELECT s_suppkey
- FROM supplier
- WHERE s_suppkey < 30
- ), v2 AS (
- SELECT s_suppkey
- FROM supplier
- WHERE s_suppkey < 10
- )
- SELECT *
- FROM v1, v2
- """
-
- // test column aliases in CTE
- order_qt_cte_6 """
- WITH cte1 (skey, sname) AS (
- SELECT s_suppkey as sk, s_name
- FROM supplier
- WHERE s_suppkey < 30
- ), cte2 (skey2, sname2) AS (
- SELECT s_suppkey, s_name
- FROM supplier
- WHERE s_suppkey < 20
- )
- SELECT *
- FROM cte1, cte2
- """
-
- // if the size of column aliases is smaller than WithClause's outputSlots, we will replace the corresponding number of front slots with column aliases.
- order_qt_cte_7 """
- WITH cte1 (skey, sname) AS (
- SELECT *
- FROM supplier
- WHERE s_suppkey < 30
- ), cte2 (skey2) AS (
- SELECT s_suppkey, s_name
- FROM supplier
- WHERE s_suppkey < 20
- )
- SELECT *
- FROM cte1, cte2
- """
-
- // using CTE in From Clause
- order_qt_cte_8 """
- WITH cte1 (skey, sname) AS (
- SELECT *
- FROM supplier
- WHERE s_suppkey < 30
- ), cte2 (skey2) AS (
- SELECT s_suppkey, s_name
- FROM supplier
- WHERE s_suppkey < 20
- )
- SELECT *
- FROM (
- SELECT *
- FROM cte2
- ) t1
- """
-
- // using CTE in subqueries
- order_qt_cte_9 """
- WITH cte1 (skey, sname) AS (
- SELECT *
- FROM supplier
- WHERE s_suppkey < 30
- ), cte2 (skey2) AS (
- SELECT s_suppkey, s_name
- FROM supplier
- WHERE s_suppkey < 20
- )
- SELECT *
- FROM supplier
- WHERE s_suppkey in (
- SELECT skey2
- FROM cte2
- )
- """
-
- // using CTE in having clause
- order_qt_cte_10 """
- WITH cte1 (skey, sname) AS (
- SELECT *
- FROM supplier
- ), cte2 (region) AS (
- SELECT s_region
- FROM supplier
- WHERE s_region in ("ASIA", "AMERICA")
- )
- SELECT s_region, count(*) cnt
- FROM cte1
- GROUP BY s_region
- HAVING s_region in (
- SELECT region
- FROM cte2
- )
- """
-
- // reference CTE which contains aliases in WithClause repeatedly
- order_qt_cte_11 """
- WITH cte1 AS (
- SELECT s_suppkey as sk, s_name as sn
- FROM supplier
- ), cte2 AS (
- SELECT sk, sn
- FROM cte1
- WHERE sk < 20
- )
- SELECT *
- FROM cte1 JOIN cte2
- ON cte1.sk = cte2.sk
- """
-
- test {
- sql = "WITH cte1 (a1, A1) AS (SELECT * FROM supplier) SELECT * FROM cte1"
-
- exception = "Duplicated CTE column alias"
- }
-
- test {
- sql = "WITH cte1 (a1, a2) AS (SELECT s_suppkey FROM supplier) SELECT * FROM cte1"
-
- exception = "CTE [cte1] returns 2 columns, but 1 labels were specified"
- }
-
- test {
- sql = "WITH cte1 AS (SELECT * FROM cte2), cte2 AS (SELECT * FROM supplier) SELECT * FROM cte1, cte2"
-
- exception = "[cte2] does not exist in database"
- }
-
- test {
- sql = "WITH cte1 AS (SELECT * FROM not_existed_table) SELECT * FROM supplier"
-
- exception = "[not_existed_table] does not exist in database"
- }
-
- test {
- sql = "WITH cte1 AS (SELECT * FROM supplier), cte1 AS (SELECT * FROM part) SELECT * FROM cte1"
-
- exception = "[cte1] cannot be used more than once"
- }
-
-}
-
diff --git a/regression-test/suites/nereids_syntax_p0/empty_relation.groovy b/regression-test/suites/nereids_syntax_p0/empty_relation.groovy
deleted file mode 100644
index 4e1babd166..0000000000
--- a/regression-test/suites/nereids_syntax_p0/empty_relation.groovy
+++ /dev/null
@@ -1,33 +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("empty_relation") {
- // enable nereids and vectorized engine
- sql "SET enable_vectorized_engine=true"
- sql "SET enable_nereids_planner=true"
- sql "SET enable_fallback_to_original_planner=false"
-
- test {
- sql "select *, substring(s_name, 1, 2) from supplier limit 0"
- result([])
- }
-
- explain {
- sql "select *, substring(s_name, 1, 2) from supplier limit 0"
- contains ":VEMPTYSET"
- }
-}
diff --git a/regression-test/suites/nereids_syntax_p0/explain.groovy b/regression-test/suites/nereids_syntax_p0/explain.groovy
deleted file mode 100644
index f265bbebde..0000000000
--- a/regression-test/suites/nereids_syntax_p0/explain.groovy
+++ /dev/null
@@ -1,50 +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("nereids_explain") {
- sql """
- SET enable_vectorized_engine=true
- """
-
- sql """
- SET enable_nereids_planner=true
- """
-
- sql "SET enable_fallback_to_original_planner=false"
-
- explain {
- sql("select count(2) + 1, sum(2) + sum(lo_suppkey) from lineorder")
- contains "(sum(2) + sum(lo_suppkey))[#24]"
- contains "project output tuple id: 3"
- }
-
-
- explain {
- sql("physical plan select 100")
- contains "PhysicalOneRowRelation"
- }
-
- explain {
- sql("logical plan select 100")
- contains "LogicalOneRowRelation"
- }
-
- explain {
- sql("parsed plan select 100")
- contains "UnboundOneRowRelation"
- }
-}
diff --git a/regression-test/suites/nereids_syntax_p0/function.groovy b/regression-test/suites/nereids_syntax_p0/function.groovy
deleted file mode 100644
index c578619f7e..0000000000
--- a/regression-test/suites/nereids_syntax_p0/function.groovy
+++ /dev/null
@@ -1,94 +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("function") {
- sql "SET enable_vectorized_engine=true"
- sql "SET enable_nereids_planner=true"
- sql "SET enable_fallback_to_original_planner=false"
-
- order_qt_max """
- SELECT max(lo_discount), max(lo_extendedprice) AS max_extendedprice FROM lineorder;
- """
-
- order_qt_min """
- SELECT min(lo_discount), min(lo_extendedprice) AS min_extendedprice FROM lineorder;
- """
-
- order_qt_max_and_min """
- SELECT max(lo_extendedprice), min(lo_discount) FROM lineorder;
- """
-
- order_qt_count """
- SELECT count(c_city), count(*) AS custdist FROM customer;
- """
-
- order_qt_distinct_count """
- SELECT count(distinct c_custkey + 1) AS custdist FROM customer group by c_city;
- """
-
- order_qt_distinct_count_group_by_distributed_key """
- SELECT c_custkey, count(distinct c_custkey + 1) AS custdist FROM customer group by c_custkey;
- """
-
- order_qt_avg """
- SELECT avg(lo_tax), avg(lo_extendedprice) AS avg_extendedprice FROM lineorder;
- """
-
- // variable length function
- test {
- sql "select coalesce(1), coalesce(null, 2), coalesce(null, 3), coalesce(4, null, 2)"
- result([[1, 2, 3, 4]])
- }
-
- // nested function
- test {
- sql "select cast(date('1994-01-01') + interval '1' YEAR as varchar)"
- result([["1995-01-01 00:00:00"]])
- }
-
- test {
- sql "select substring(substring('1994-01-01', 5), 3)"
- result([["1-01"]])
- }
-
- // numbers: table valued function
- test {
- sql "select `number` from numbers(number = 10, backend_num = 1)"
- result([[0L], [1L], [2L], [3L], [4L], [5L], [6L], [7L], [8L], [9L]])
- }
-
- test {
- sql """select `number` from numbers("number" = -1, 'backend_num' = `1`)"""
- result([])
- }
-
- test {
- sql "select `number` from numbers(a=2)"
- exception "Can not build NumbersTableValuedFunction by numbers('a' = '2')"
- }
-
- test {
- sql """select a.`number` from numbers(number = 3) a"""
- result([[0L], [1L], [2L]])
- }
-
- test {
- sql """select b.number from (select * from numbers(number = 3) a)b"""
- result([[0L], [1L], [2L]])
- }
-}
-
diff --git a/regression-test/suites/nereids_syntax_p0/group_by_constant.groovy b/regression-test/suites/nereids_syntax_p0/group_by_constant.groovy
deleted file mode 100644
index c2bd39e648..0000000000
--- a/regression-test/suites/nereids_syntax_p0/group_by_constant.groovy
+++ /dev/null
@@ -1,32 +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("group_by_constant") {
- sql """
- SET enable_vectorized_engine=true
- """
-
- sql """
- SET enable_nereids_planner=true
- """
-
- sql "SET enable_fallback_to_original_planner=false"
-
- qt_select_1 """
- select 'str', sum(lo_tax), lo_orderkey, max(lo_discount), 1 from lineorder, customer group by 3, 5, 'str', 1, lo_orderkey order by lo_orderkey;
- """
-}
diff --git a/regression-test/suites/nereids_syntax_p0/grouping_sets.groovy b/regression-test/suites/nereids_syntax_p0/grouping_sets.groovy
deleted file mode 100644
index 1a93fbcf9c..0000000000
--- a/regression-test/suites/nereids_syntax_p0/grouping_sets.groovy
+++ /dev/null
@@ -1,162 +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("test_nereids_grouping_sets") {
-
- sql "SET enable_nereids_planner=true"
- sql "SET enable_vectorized_engine=true"
-
- sql "DROP TABLE IF EXISTS groupingSetsTable"
- sql "DROP TABLE IF EXISTS groupingSetsTableNotNullable"
-
- sql """
- CREATE TABLE `groupingSetsTable` (
- `k1` bigint(20) NULL,
- `k2` bigint(20) NULL,
- `k3` bigint(20) NULL,
- `k4` bigint(20) not null,
- `k5` varchar(10),
- `k6` varchar(10)
- ) ENGINE=OLAP
- DUPLICATE KEY(`k1`)
- DISTRIBUTED BY HASH(`k2`) BUCKETS 1
- PROPERTIES ('replication_num' = '1')
- """
-
- sql """
- CREATE TABLE `groupingSetsTableNotNullable` (
- `k1` bigint(20) NOT NULL,
- `k2` bigint(20) NOT NULL,
- `k3` bigint(20) NOT NULL
- ) ENGINE=OLAP
- DUPLICATE KEY(`k1`)
- COMMENT 'OLAP'
- DISTRIBUTED BY HASH(`k2`) BUCKETS 1
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1",
- "in_memory" = "false",
- "storage_format" = "V2",
- "disable_auto_compaction" = "false"
- );
- """
-
- sql """
- INSERT INTO groupingSetsTable VALUES
- (1, 1, 1, 3, 'a', 'b'),
- (1, 1, 2, 3, 'a', 'c'),
- (1, 1, 3, 4, 'a' , 'd'),
- (1, 0, null, 4, 'b' , 'b'),
- (2, 2, 2, 5, 'b', 'c'),
- (2, 2, 4, 5, 'b' , 'd'),
- (2, 2, 6, 4, 'c', 'b'),
- (2, 2, null, 4, 'c', 'c'),
- (3, 3, 3, 3, 'c', 'd'),
- (3, 3, 6, 3, 'd', 'b'),
- (3, 3, 9, 4, 'd', 'c'),
- (3, 0, null, 5, 'd', 'd')
- """
-
- sql """
- insert into groupingSetsTableNotNullable values
- (1, 0, 0),
- (1, 1, 3),
- (1, 1, 2),
- (1, 1, 1),
- (2, 2, 0),
- (2, 2, 6),
- (2, 2, 4),
- (2, 2, 2),
- (3, 0, 0),
- (3, 3, 9),
- (3, 3, 6),
- (3, 3, 3);
- """
-
- sql "SET enable_fallback_to_original_planner=false"
-
- // grouping
- order_qt_select "select k1+1, grouping(k1+1) from groupingSetsTable group by grouping sets((k1+1));";
- order_qt_select "select k1+1, grouping(k1+1) from groupingSetsTable group by grouping sets((k1+1), (k1), (k2));";
- order_qt_select "select k1+1, grouping(k1+1) from groupingSetsTable group by grouping sets((k1), (k1+1), (k2));";
- order_qt_select "select k1+1, grouping(k1) from groupingSetsTable group by grouping sets((k1));";
- order_qt_select "select sum(k2), grouping(k1+1) from groupingSetsTable group by grouping sets((k1+1));";
- order_qt_select "select sum(k2+1), grouping(k1+1) from groupingSetsTable group by grouping sets((k1+1));";
- order_qt_select "select sum(k2+1), grouping(k1+1) from groupingSetsTable group by grouping sets((k1+1), (k1));";
- order_qt_select "select sum(k2+1), grouping(k1+1) from groupingSetsTable group by grouping sets((k1+1)) having (k1+1) > 1;";
- order_qt_select "select sum(k2+1), grouping(k1+1) from groupingSetsTable group by grouping sets((k1+1), (k1)) having (k1+1) > 1;";
-
-
- // grouping_id
- order_qt_select "select k1+1, grouping_id(k1+1) from groupingSetsTable group by grouping sets((k1+1));";
- order_qt_select "select k1+1, grouping_id(k1+1) from groupingSetsTable group by grouping sets((k1+1), (k1), (k2));";
- order_qt_select "select k1+1, grouping_id(k1+1) from groupingSetsTable group by grouping sets((k1), (k1+1), (k2));";
- order_qt_select "select k1+1, grouping_id(k1) from groupingSetsTable group by grouping sets((k1));";
- order_qt_select "select k1+1, grouping_id(k1, k2) from groupingSetsTable group by grouping sets((k1), (k2));";
- order_qt_select "select sum(k2), grouping_id(k1+1) from groupingSetsTable group by grouping sets((k1+1));";
- order_qt_select "select sum(k2+1), grouping_id(k1+1) from groupingSetsTable group by grouping sets((k1+1));";
- order_qt_select "select sum(k2+1), grouping_id(k1+1) from groupingSetsTable group by grouping sets((k1+1), (k1));";
- order_qt_select "select sum(k2+1), grouping_id(k1+1) from groupingSetsTable group by grouping sets((k1+1)) having (k1+1) > 1;";
- order_qt_select "select sum(k2+1), grouping_id(k1+1) from groupingSetsTable group by grouping sets((k1+1), (k1)) having (k1+1) > 1;";
-
- // old grouping sets
- qt_select """
- SELECT k1, k2, SUM(k3) FROM groupingSetsTable
- GROUP BY GROUPING SETS ((k1, k2), (k1), (k2), ( ) ) order by k1, k2
- """
-
- qt_select2 """
- select (k1 + 1) k1_, k2, sum(k3) from groupingSetsTable group by
- rollup(k1_, k2) order by k1_, k2
- """
-
- qt_select3 "select 1 as k, k3, sum(k1) from groupingSetsTable group by cube(k, k3) order by k, k3"
-
- qt_select4 """
- select k2, concat(k5, k6) as k_concat, sum(k1) from groupingSetsTable group by
- grouping sets((k2, k_concat),()) order by k2, k_concat
- """
-
- qt_select5 """
- select k1_, k2_, sum(k3_) from (select (k1 + 1) k1_, k2 k2_, k3 k3_ from groupingSetsTable) as test
- group by grouping sets((k1_, k2_), (k2_)) order by k1_, k2_
- """
-
- qt_select6 """
- select if(k1 = 1, 2, k1) k_if, k1, sum(k2) k2_sum from groupingSetsTable where k3 is null or k2 = 1
- group by grouping sets((k_if, k1),()) order by k_if, k1, k2_sum
- """
-
- test {
- sql """
- SELECT k1, k2, SUM(k3) FROM groupingSetsTable
- GROUP BY GROUPING SETS ((k1, k2), (k1), (k2), ( ), (k3) ) order by k1, k2
- """
- exception "java.sql.SQLException: errCode = 2, detailMessage = Unexpected exception: column: k3 cannot both in select list and aggregate functions when using GROUPING SETS/CUBE/ROLLUP, please use union instead."
- }
-
- test {
- sql """
- SELECT k1, k2, SUM(k3)/(SUM(k3)+1) FROM groupingSetsTable
- GROUP BY GROUPING SETS ((k1, k2), (k1), (k2), ( ), (k3) ) order by k1, k2
- """
- exception "java.sql.SQLException: errCode = 2, detailMessage = Unexpected exception: column: k3 cannot both in select list and aggregate functions when using GROUPING SETS/CUBE/ROLLUP, please use union instead."
- }
-
- order_qt_select """
- select k1, sum(k2) from (select k1, k2, grouping(k1), grouping(k2) from groupingSetsTableNotNullable group by grouping sets((k1), (k2)))a group by k1
- """
-}
diff --git a/regression-test/suites/nereids_syntax_p0/having.groovy b/regression-test/suites/nereids_syntax_p0/having.groovy
deleted file mode 100644
index 981230058d..0000000000
--- a/regression-test/suites/nereids_syntax_p0/having.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("test_nereids_having") {
-
- sql "SET enable_nereids_planner=true"
- sql "SET enable_vectorized_engine=true"
-
- sql "DROP TABLE IF EXISTS test_nereids_having_tbl"
-
- sql """
- CREATE TABLE IF NOT EXISTS test_nereids_having_tbl (
- pk INT,
- a1 INT,
- a2 INT
- ) DUPLICATE KEY (pk) DISTRIBUTED BY HASH (pk)
- PROPERTIES ('replication_num' = '1')
- """
-
- sql """
- INSERT INTO test_nereids_having_tbl VALUES
- (1, 1, 1),
- (1, 1, 2),
- (1, 1, 3),
- (2, 2, 2),
- (2, 2, 4),
- (2, 2, 6),
- (3, 3, 3),
- (3, 3, 6),
- (3, 3, 9)
- """
-
- sql "SET enable_fallback_to_original_planner=false"
-
- order_qt_select "SELECT a1 as value FROM test_nereids_having_tbl GROUP BY a1 HAVING a1 > 0";
- order_qt_select "SELECT a1 as value FROM test_nereids_having_tbl GROUP BY a1 HAVING value > 0";
- order_qt_select "SELECT SUM(a2) FROM test_nereids_having_tbl GROUP BY a1 HAVING a1 > 0";
- order_qt_select "SELECT a1 FROM test_nereids_having_tbl GROUP BY a1 HAVING SUM(a2) > 0";
- order_qt_select "SELECT a1, SUM(a2) FROM test_nereids_having_tbl GROUP BY a1 HAVING SUM(a2) > 0";
- order_qt_select "SELECT a1, SUM(a2) as value FROM test_nereids_having_tbl GROUP BY a1 HAVING SUM(a2) > 0";
- order_qt_select "SELECT a1, SUM(a2) as value FROM test_nereids_having_tbl GROUP BY a1 HAVING value > 0";
- order_qt_select "SELECT a1, SUM(a2) FROM test_nereids_having_tbl GROUP BY a1 HAVING MIN(pk) > 0";
- order_qt_select "SELECT a1, SUM(a1 + a2) FROM test_nereids_having_tbl GROUP BY a1 HAVING SUM(a1 + a2) > 0";
- order_qt_select "SELECT a1, SUM(a1 + a2) FROM test_nereids_having_tbl GROUP BY a1 HAVING SUM(a1 + a2 + 3) > 0";
- order_qt_select "SELECT a1 FROM test_nereids_having_tbl GROUP BY a1 HAVING COUNT(*) > 0";
-}
diff --git a/regression-test/suites/nereids_syntax_p0/hll_agg.groovy b/regression-test/suites/nereids_syntax_p0/hll_agg.groovy
deleted file mode 100644
index c8bc10f12b..0000000000
--- a/regression-test/suites/nereids_syntax_p0/hll_agg.groovy
+++ /dev/null
@@ -1,85 +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("hll_agg") {
- sql "SET enable_vectorized_engine=true"
- sql "SET enable_nereids_planner=true"
- sql "SET enable_fallback_to_original_planner=false"
-
- // hll_union, hll_raw_agg, hll_union_agg
- test {
- sql """
- select
- hll_cardinality(hll_union(h)),
- hll_cardinality(hll_raw_agg(h)),
- hll_union_agg(h)
- from
- (
- select hll_hash(number) h, number from numbers(number = 10) tmp
- ) t
- """
- result([[10L, 10L, 10L]])
- }
-
- test {
- sql """
- select
- hll_union_agg(h)
- from
- (
- select hll_hash(number) h, number from numbers(number = 161) tmp
- ) t
- """
- result([[162L]])
- }
-
- test {
- sql """
- select
- hll_union_agg(h)
- from
- (
- select hll_hash(number) h, number from numbers(number = 162) tmp
- ) t
- """
- result([[163L]])
- }
-
- test {
- sql """
- select
- hll_union_agg(h)
- from
- (
- select hll_hash(number) h, number from numbers(number = 1000) tmp
- ) t
- """
- result([[1001L]])
- }
-
- test {
- sql """
- select
- hll_union_agg(h)
- from
- (
- select hll_hash(number) h, number from numbers(number = 2000) tmp
- ) t
- """
- result([[2007L]])
- }
-}
diff --git a/regression-test/suites/nereids_syntax_p0/inpredicate.groovy b/regression-test/suites/nereids_syntax_p0/inpredicate.groovy
deleted file mode 100644
index de73bb106b..0000000000
--- a/regression-test/suites/nereids_syntax_p0/inpredicate.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("inpredicate") {
- sql """
- SET enable_vectorized_engine=true
- """
-
- sql """
- SET enable_nereids_planner=true
- """
-
- sql "SET enable_fallback_to_original_planner=false"
-
- order_qt_in_predicate_1 """
- SELECT * FROM supplier WHERE s_suppkey in (1, 2, 3);
- """
-
- order_qt_in_predicate_2 """
- SELECT * FROM supplier WHERE s_suppkey not in (1, 2, 3);
- """
-
- order_qt_in_predicate_3 """
- SELECT * FROM supplier WHERE s_suppkey in (1, 2, 128, 129);
- """
-
- order_qt_in_predicate_4 """
- SELECT * FROM supplier WHERE s_suppkey in (1, 2, 128, 32768, 32769);
- """
-
- order_qt_in_predicate_5 """
- SELECT * FROM supplier WHERE s_suppkey in (1, 2, 128, 32768, 2147483648);
- """
-
- order_qt_in_predicate_6 """
- SELECT * FROM supplier WHERE s_suppkey not in (1, 2, 128, 32768, 2147483648);
- """
-
- order_qt_in_predicate_7 """
- SELECT * FROM supplier WHERE s_nation in ('PERU', 'ETHIOPIA');
- """
-
- order_qt_in_predicate_8 """
- SELECT * FROM supplier WHERE s_nation not in ('PERU', 'ETHIOPIA');
- """
-
- order_qt_in_predicate_9 """
- SELECT * FROM supplier WHERE s_suppkey in (15);
- """
-
- order_qt_in_predicate_10 """
- SELECT * FROM supplier WHERE s_suppkey not in (15);
- """
-}
-
diff --git a/regression-test/suites/nereids_syntax_p0/join.groovy b/regression-test/suites/nereids_syntax_p0/join.groovy
deleted file mode 100644
index 700fe1a49a..0000000000
--- a/regression-test/suites/nereids_syntax_p0/join.groovy
+++ /dev/null
@@ -1,81 +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("join") {
- sql """
- SET enable_vectorized_engine=true
- """
-
- sql """
- SET enable_nereids_planner=true
- """
-
- sql "SET enable_fallback_to_original_planner=false"
-
- order_qt_inner_join_1 """
- SELECT * FROM lineorder JOIN supplier ON lineorder.lo_suppkey = supplier.s_suppkey
- """
-
- order_qt_inner_join_2 """
- SELECT * FROM lineorder INNER JOIN supplier ON lineorder.lo_suppkey = supplier.s_suppkey
- """
-
- order_qt_left_outer_join_1 """
- SELECT * FROM lineorder LEFT JOIN supplier ON lineorder.lo_suppkey = supplier.s_suppkey
- """
-
- order_qt_left_outer_join_2 """
- SELECT * FROM lineorder LEFT OUTER JOIN supplier ON lineorder.lo_suppkey = supplier.s_suppkey
- """
-
- order_qt_right_outer_join_1 """
- SELECT * FROM lineorder RIGHT JOIN supplier ON lineorder.lo_suppkey = supplier.s_suppkey
- """
-
- order_qt_right_outer_join_2 """
- SELECT * FROM lineorder RIGHT OUTER JOIN supplier ON lineorder.lo_suppkey = supplier.s_suppkey
- """
-
- order_qt_left_semi_join """
- SELECT * FROM lineorder LEFT SEMI JOIN supplier ON lineorder.lo_suppkey = supplier.s_suppkey
- """
-
- order_qt_right_semi_join """
- SELECT * FROM lineorder RIGHT SEMI JOIN supplier ON lineorder.lo_suppkey = supplier.s_suppkey
- """
-
- order_qt_left_anti_join """
- SELECT * FROM lineorder LEFT ANTI JOIN supplier ON lineorder.lo_suppkey = supplier.s_suppkey
- """
-
- order_qt_right_anti_join """
- SELECT * FROM lineorder RIGHT ANTI JOIN supplier ON lineorder.lo_suppkey = supplier.s_suppkey
- """
-
- order_qt_cross_join """
- SELECT * FROM lineorder CROSS JOIN supplier;
- """
-
- order_qt_inner_join_with_other_condition """
- select lo_orderkey, lo_partkey, p_partkey, p_size from lineorder inner join part on lo_partkey = p_partkey where lo_orderkey - 1310000 > p_size;
- """
-
- order_qt_outer_join_with_filter """
- select lo_orderkey, lo_partkey, p_partkey, p_size from lineorder inner join part on lo_partkey = p_partkey where lo_orderkey - 1310000 > p_size;
- """
-}
-
diff --git a/regression-test/suites/nereids_syntax_p0/load.groovy b/regression-test/suites/nereids_syntax_p0/load.groovy
deleted file mode 100644
index 22103b19e3..0000000000
--- a/regression-test/suites/nereids_syntax_p0/load.groovy
+++ /dev/null
@@ -1,184 +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") {
- sql """
- DROP TABLE IF EXISTS `customer`;
- """
-
- sql """
- DROP TABLE IF EXISTS `dates`;
- """
-
- sql """
- DROP TABLE IF EXISTS `lineorder`;
- """
-
- sql """
- DROP TABLE IF EXISTS `part`;
- """
-
- sql """
- DROP TABLE IF EXISTS `supplier`;
- """
-
- sql """
- CREATE TABLE IF NOT EXISTS `customer` (
- `c_custkey` int(11) NOT NULL COMMENT "",
- `c_name` varchar(26) NOT NULL COMMENT "",
- `c_address` varchar(41) NOT NULL COMMENT "",
- `c_city` varchar(11) NOT NULL COMMENT "",
- `c_nation` varchar(16) NOT NULL COMMENT "",
- `c_region` varchar(13) NOT NULL COMMENT "",
- `c_phone` varchar(16) NOT NULL COMMENT "",
- `c_mktsegment` varchar(11) NOT NULL COMMENT ""
- )
- DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 10
- PROPERTIES (
- "replication_num" = "1"
- );
- """
-
- sql """
- CREATE TABLE IF NOT EXISTS `dates` (
- `d_datekey` int(11) NOT NULL COMMENT "",
- `d_date` varchar(20) NOT NULL COMMENT "",
- `d_dayofweek` varchar(10) NOT NULL COMMENT "",
- `d_month` varchar(11) NOT NULL COMMENT "",
- `d_year` int(11) NOT NULL COMMENT "",
- `d_yearmonthnum` int(11) NOT NULL COMMENT "",
- `d_yearmonth` varchar(9) NOT NULL COMMENT "",
- `d_daynuminweek` int(11) NOT NULL COMMENT "",
- `d_daynuminmonth` int(11) NOT NULL COMMENT "",
- `d_daynuminyear` int(11) NOT NULL COMMENT "",
- `d_monthnuminyear` int(11) NOT NULL COMMENT "",
- `d_weeknuminyear` int(11) NOT NULL COMMENT "",
- `d_sellingseason` varchar(14) NOT NULL COMMENT "",
- `d_lastdayinweekfl` int(11) NOT NULL COMMENT "",
- `d_lastdayinmonthfl` int(11) NOT NULL COMMENT "",
- `d_holidayfl` int(11) NOT NULL COMMENT "",
- `d_weekdayfl` int(11) NOT NULL COMMENT ""
- )
- DISTRIBUTED BY HASH(`d_datekey`) BUCKETS 1
- PROPERTIES (
- "replication_num" = "1"
- );
- """
-
- sql """
- CREATE TABLE IF NOT EXISTS `lineorder` (
- `lo_orderkey` bigint(20) NOT NULL COMMENT "",
- `lo_linenumber` bigint(20) NOT NULL COMMENT "",
- `lo_custkey` int(11) NOT NULL COMMENT "",
- `lo_partkey` int(11) NOT NULL COMMENT "",
- `lo_suppkey` int(11) NOT NULL COMMENT "",
- `lo_orderdate` int(11) NOT NULL COMMENT "",
- `lo_orderpriority` varchar(16) NOT NULL COMMENT "",
- `lo_shippriority` int(11) NOT NULL COMMENT "",
- `lo_quantity` bigint(20) NOT NULL COMMENT "",
- `lo_extendedprice` bigint(20) NOT NULL COMMENT "",
- `lo_ordtotalprice` bigint(20) NOT NULL COMMENT "",
- `lo_discount` bigint(20) NOT NULL COMMENT "",
- `lo_revenue` bigint(20) NOT NULL COMMENT "",
- `lo_supplycost` bigint(20) NOT NULL COMMENT "",
- `lo_tax` bigint(20) NOT NULL COMMENT "",
- `lo_commitdate` bigint(20) NOT NULL COMMENT "",
- `lo_shipmode` varchar(11) NOT NULL COMMENT ""
- )
- PARTITION BY RANGE(`lo_orderdate`)
- (PARTITION p1992 VALUES [("-2147483648"), ("19930101")),
- PARTITION p1993 VALUES [("19930101"), ("19940101")),
- PARTITION p1994 VALUES [("19940101"), ("19950101")),
- PARTITION p1995 VALUES [("19950101"), ("19960101")),
- PARTITION p1996 VALUES [("19960101"), ("19970101")),
- PARTITION p1997 VALUES [("19970101"), ("19980101")),
- PARTITION p1998 VALUES [("19980101"), ("19990101")))
- DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 48
- PROPERTIES (
- "replication_num" = "1"
- );
- """
-
- sql """
- CREATE TABLE IF NOT EXISTS `part` (
- `p_partkey` int(11) NOT NULL COMMENT "",
- `p_name` varchar(23) NOT NULL COMMENT "",
- `p_mfgr` varchar(7) NOT NULL COMMENT "",
- `p_category` varchar(8) NOT NULL COMMENT "",
- `p_brand` varchar(10) NOT NULL COMMENT "",
- `p_color` varchar(12) NOT NULL COMMENT "",
- `p_type` varchar(26) NOT NULL COMMENT "",
- `p_size` int(11) NOT NULL COMMENT "",
- `p_container` varchar(11) NOT NULL COMMENT ""
- )
- DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 10
- PROPERTIES (
- "replication_num" = "1"
- );
- """
-
- sql """
- CREATE TABLE IF NOT EXISTS `supplier` (
- `s_suppkey` int(11) NOT NULL COMMENT "",
- `s_name` varchar(26) NOT NULL COMMENT "",
- `s_address` varchar(26) NOT NULL COMMENT "",
- `s_city` varchar(11) NOT NULL COMMENT "",
- `s_nation` varchar(16) NOT NULL COMMENT "",
- `s_region` varchar(13) NOT NULL COMMENT "",
- `s_phone` varchar(16) NOT NULL COMMENT ""
- )
- DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 10
- PROPERTIES (
- "replication_num" = "1"
- );
- """
-
- sql """
- INSERT INTO customer VALUES
- (1303, "Customer#000001303", "fQ Lp,FoozZe1", "ETHIOPIA 3", "ETHIOPIA", "AFRICA", "15-658-234-7985", "MACHINERY"),
- (1309, "Customer#000001309", "vQcJGUXPHMH2 5OWs1XUP0kx", "IRAN 2", "IRAN", "MIDDLE EAST", "20-821-905-5952", "AUTOMOBILE"),
- (1312, "Customer#000001312", "MVsKeqWejff8jQ30", "CANADA 9", "CANADA", "AMERICA", "13-153-492-9898", "BUILDING");
- """
-
- sql """
- INSERT INTO dates VALUES
- (19920410, "April 10, 1992", "Saturday", "April", 1992, 199204, "Apr1992", 7, 10, 101, 4, 15, "Spring", 1, 1, 0, 0),
- (19920411, "April 11, 1992", "Sunday", "April", 1992, 199204, "Apr1992", 1, 11, 102, 4, 15, "Spring", 0, 1, 0, 0),
- (19920412, "April 12, 1992", "Monday", "April", 1992, 199204, "Apr1992", 2, 12, 103, 4, 15, "Spring", 0, 1, 0, 1);
- """
-
- sql """
- INSERT INTO part VALUES
- (1165, "linen midnight", "MFGR#3", "MFGR#31", "MFGR#3117", "seashell", "MEDIUM BURNISHED NICKEL", 1, "LG PACK"),
- (1432, "metallic bisque", "MFGR#3", "MFGR#35", "MFGR#352", "aquamarine", "LARGE BURNISHED TIN", 21, "MED PKG"),
- (1455, "magenta blush", "MFGR#1", "MFGR#13", "MFGR#1324", "blue", "LARGE BRUSHED STEEL", 42, "SM PACK");
- """
-
- sql """
- INSERT INTO supplier VALUES
- (9, "Supplier#000000009", ",gJ6K2MKveYxQT", "IRAN 6", "IRAN", "MIDDLE EAST", "20-338-906-3675"),
- (15, "Supplier#000000015", "DF35PepL5saAK", "INDIA 0", "INDIA", "ASIA", "18-687-542-7601"),
- (29, "Supplier#000000029", "VVSymB3fbwaN", "ARGENTINA4", "ARGENTINA", "AMERICA", "11-773-203-7342");
- """
-
- sql """
- INSERT INTO lineorder VALUES
- (1309892, 2, 1303, 1165, 9, 19920517, "3-MEDIUM", 0, 21, 2404899, 5119906, 8, 2212507, 68711, 7, 19920616, "RAIL"),
- (1309892, 1, 1303, 1432, 15, 19920517, "3-MEDIUM", 0, 24, 2959704, 5119906, 7, 2752524, 73992, 0, 19920619, "TRUCK"),
- (1310179, 6, 1312, 1455, 29, 19921110, "3-MEDIUM", 0, 15, 1705830, 20506457, 10, 1535247, 68233, 8, 19930114, "FOB");
- """
-}
diff --git a/regression-test/suites/nereids_syntax_p0/one_row_relation.groovy b/regression-test/suites/nereids_syntax_p0/one_row_relation.groovy
deleted file mode 100644
index fa9cd58ee7..0000000000
--- a/regression-test/suites/nereids_syntax_p0/one_row_relation.groovy
+++ /dev/null
@@ -1,35 +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("one_row_relation") {
- // enable nereids and vectorized engine
- sql "SET enable_vectorized_engine=true"
- sql "SET enable_nereids_planner=true"
- sql "SET enable_fallback_to_original_planner=false"
-
- test {
- sql "select 100, 'abc', substring('abc', 1, 2), substring(substring('abcdefg', 4, 3), 1, 2), null"
- result([[100, "abc", "ab", "de", null]])
- }
-
- test {
- sql """select * from (
- select 100, 'abc', substring('abc', 1, 2), substring(substring('abcdefg', 4, 3), 1, 2), null
- )a"""
- result([[100, "abc", "ab", "de", null]])
- }
-}
diff --git a/regression-test/suites/nereids_syntax_p0/rollup.groovy b/regression-test/suites/nereids_syntax_p0/rollup.groovy
deleted file mode 100644
index 2284b66b74..0000000000
--- a/regression-test/suites/nereids_syntax_p0/rollup.groovy
+++ /dev/null
@@ -1,99 +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("rollup") {
- sql """
- DROP TABLE IF EXISTS `rollup_t1`
- """
- sql """
- CREATE TABLE IF NOT EXISTS `rollup_t1` (
- `k1` int(11) NULL,
- `k2` int(11) NULL,
- `k3` int(11) NULL,
- `v1` int(11) SUM NULL
- ) ENGINE=OLAP
- AGGREGATE KEY(`k1`, `k2`, `k3`)
- COMMENT 'OLAP'
- DISTRIBUTED BY HASH(`k1`) BUCKETS 3
- PROPERTIES (
- "replication_allocation" = "tag.location.default: 1",
- "in_memory" = "false",
- "storage_format" = "V2",
- "disable_auto_compaction" = "false"
- );
- """
-
- sql "ALTER TABLE rollup_t1 ADD ROLLUP r1(k2, v1)"
-
-
- def getJobRollupState = { tableName ->
- def jobStateResult = sql """ SHOW ALTER TABLE ROLLUP WHERE TableName='${tableName}' ORDER BY CreateTime DESC LIMIT 1; """
- return jobStateResult[0][8]
- }
-
- int max_try_secs = 60
- while (max_try_secs--) {
- String res = getJobRollupState("rollup_t1")
- if (res == "FINISHED") {
- break
- } else {
- Thread.sleep(2000)
- if (max_try_secs < 1) {
- println "test timeout," + "state:" + res
- assertEquals("FINISHED",res)
- }
- }
- }
- Thread.sleep(2000)
-
- sql "insert into rollup_t1 values(1, 2, 3, 4)"
- sql "insert into rollup_t1 values(1, 2, 3, 2)"
- sql "insert into rollup_t1 values(2, 3, 4, 1)"
- sql "insert into rollup_t1 values(2, 3, 4, 3)"
-
- sql "set enable_vectorized_engine=true"
-
- sql "set enable_nereids_planner=true"
-
- sql "SET enable_fallback_to_original_planner=false"
-
- explain {
- sql("select k2, sum(v1) from rollup_t1 group by k2")
- contains("rollup_t1(r1)")
- contains("PREAGGREGATION: ON")
- }
-
- explain {
- sql("select k1, sum(v1) from rollup_t1 group by k1")
- contains("rollup_t1(rollup_t1)")
- contains("PREAGGREGATION: ON")
- }
-
- // TODO: add these qt tests back when nereids planner could get correct plan when
- // light schema change is enabled.
- // order_qt_rollup1 "select k2, sum(v1) from rollup_t1 group by k2"
-
- // order_qt_rollup2 "select k1, sum(v1) from rollup_t1 group by k1"
-
- explain {
- sql("select k1, max(v1) from rollup_t1 group by k1")
- contains("rollup_t1(rollup_t1)")
- contains("PREAGGREGATION: OFF")
- contains("Aggregate operator don't match, aggregate function: max(v1), column aggregate type: SUM")
- }
-}
diff --git a/regression-test/suites/nereids_syntax_p0/set_var.groovy b/regression-test/suites/nereids_syntax_p0/set_var.groovy
deleted file mode 100644
index d2d797d4f0..0000000000
--- a/regression-test/suites/nereids_syntax_p0/set_var.groovy
+++ /dev/null
@@ -1,45 +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("set_var") {
- // enable nereids and vectorized engine
- sql "SET enable_vectorized_engine=true"
- sql "SET enable_nereids_planner=true"
- sql "SET enable_fallback_to_original_planner=false"
-
- // test single set_var
- sql "select /*+ SET_VAR(query_timeout=1800) */ * from supplier limit 10"
-
- // test multi set_var, comma between hints is optional
- sql """ select
- /*+
- SET_VAR(query_timeout=1800, enable_vectorized_engine=true)
- SET_VAR(query_timeout=1800, enable_vectorized_engine=true),
- SET_VAR(query_timeout=1800, enable_vectorized_engine=true)
- */
- *
- from supplier
- limit 10
- """
-
- // test whether the hints are effective
- // set an invalid parameter, and throw an exception
- test {
- sql "select /*+SET_VAR(runtime_filter_type=10000)*/ * from supplier limit 10"
- exception "Unexpected exception: Can not set session variable"
- }
-}
diff --git a/regression-test/suites/nereids_syntax_p0/sub_query_alias.groovy b/regression-test/suites/nereids_syntax_p0/sub_query_alias.groovy
deleted file mode 100644
index e4cdcec5b5..0000000000
--- a/regression-test/suites/nereids_syntax_p0/sub_query_alias.groovy
+++ /dev/null
@@ -1,74 +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("sub_query_alias") {
- sql """
- SET enable_vectorized_engine=true
- """
-
- sql """
- SET enable_nereids_planner=true
- """
-
- sql "SET enable_fallback_to_original_planner=false"
-
- qt_select_1 """
- select t.c_custkey, t.lo_custkey
- from (
- select *
- from customer, lineorder
- where customer.c_custkey = lineorder.lo_custkey
- ) t
- order by t.c_custkey
- """
-
- qt_select_2 """
- select c.c_custkey, l.lo_custkey
- from customer c, lineorder l
- where c.c_custkey = l.lo_custkey
- order by c.c_custkey
- """
-
- qt_select_3 """
- select t.c_custkey, t.lo_custkey
- from (
- select *
- from customer c, lineorder l
- where c.c_custkey = l.lo_custkey
- ) t
- order by t.c_custkey
- """
-
- qt_select_4 """
- select *
- from customer c
- join customer c1
- on c.c_custkey = c1.c_custkey
- order by c.c_custkey
- """
-
- qt_select_5 """
- select *
- from customer c
- join (
- select *
- from lineorder l
- ) t on c.c_custkey = t.lo_custkey
- order by c.c_custkey
- """
-}
-
diff --git a/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy b/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
deleted file mode 100644
index 792157b390..0000000000
--- a/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
+++ /dev/null
@@ -1,234 +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 ("sub_query_correlated") {
- // enable nereids and vectorized engine
- sql """
- SET enable_vectorized_engine=true
- """
-
- sql """
- SET enable_nereids_planner=true
- """
-
- sql """
- SET enable_bucket_shuffle_join=false
- """
-
- sql """
- SET disable_colocate_plan=true
- """
-
- sql """
- DROP TABLE IF EXISTS `subquery1`
- """
-
- sql """
- DROP TABLE IF EXISTS `subquery2`
- """
-
- sql """
- DROP TABLE IF EXISTS `subquery3`
- """
-
- sql """
- DROP TABLE IF EXISTS `subquery4`
- """
-
- sql """
- create table if not exists subquery1
- (k1 bigint, k2 bigint)
- duplicate key(k1)
- distributed by hash(k2) buckets 1
- properties('replication_num' = '1')
- """
-
- sql """
- create table if not exists subquery2
- (k1 varchar(10), k2 bigint)
- partition by range(k2)
- (partition p1 values less than("10"))
- distributed by hash(k2) buckets 1
- properties('replication_num' = '1')
- """
-
- sql """
- create table if not exists subquery3
- (k1 int not null, k2 varchar(128), k3 bigint, v1 bigint, v2 bigint)
- distributed by hash(k2) buckets 1
- properties('replication_num' = '1')
- """
-
- sql """
- create table if not exists subquery4
- (k1 bigint, k2 bigint)
- duplicate key(k1)
- distributed by hash(k2) buckets 1
- properties('replication_num' = '1')
- """
-
- sql """
- insert into subquery1 values (1,2), (1,3), (2,4), (2,5), (3,3), (3,4), (20,2), (22,3), (24,4)
- """
-
- sql """
- insert into subquery2 values ("abc",2),("abc",3),("abcd",2),("abcde",4),("abcdef",5)
- """
-
- sql """
- insert into subquery3 values (1,"abc",2,3,4), (1,"abcd",3,3,4), (2,"xyz",2,4,2),
- (2,"uvw",3,4,2), (2,"uvw",3,4,2), (3,"abc",4,5,3), (3,"abc",4,5,3)
- """
-
- sql """
- insert into subquery4 values (5,4), (5,2), (8,3), (5,4), (6,7), (8,9)
- """
-
- sql "SET enable_fallback_to_original_planner=false"
-
- //------------------Correlated-----------------
- qt_scalar_less_than_corr """
- select * from subquery1 where subquery1.k1 < (select sum(subquery3.k3) from subquery3 where subquery3.v2 = subquery1.k2) order by k1, k2
- """
-
- qt_scalar_not_equal_corr """
- select * from subquery1 where subquery1.k1 != (select sum(subquery3.k3) from subquery3 where subquery3.v2 = subquery1.k2) order by k1, k2
- """
-
- qt_scalar_equal_to_corr """
- select * from subquery1 where subquery1.k1 = (select sum(subquery3.k3) from subquery3 where subquery3.v2 = subquery1.k2) order by k1, k2
- """
-
- qt_not_in_corr """
- select * from subquery1 where subquery1.k1 not in (select subquery3.k3 from subquery3 where subquery3.v2 = subquery1.k2) order by k1, k2
- """
-
- qt_in_subquery_corr """
- select * from subquery1 where subquery1.k1 in (select subquery3.k3 from subquery3 where subquery3.v2 = subquery1.k2) order by k1, k2
- """
-
- qt_not_exist_corr """
- select * from subquery1 where not exists (select subquery3.k3 from subquery3 where subquery1.k2 = subquery3.v2) order by k1, k2
- """
-
- qt_exist_corr """
- select * from subquery1 where exists (select subquery3.k3 from subquery3 where subquery1.k2 = subquery3.v2) order by k1, k2
- """
-
- qt_in_with_in_and_scalar """
- select * from subquery1 where subquery1.k1 in (
- select subquery3.k3 from subquery3 where
- subquery3.k3 in (select subquery4.k1 from subquery4 where subquery4.k1 = 3)
- and subquery3.v2 > (select sum(subquery2.k2) from subquery2 where subquery2.k2 = subquery3.v1)) order by k1, k2
- """
-
- qt_exist_and_not_exist """
- select * from subquery1 where exists (select subquery3.k3 from subquery3 where subquery1.k2 = subquery3.v2)
- and not exists (select subquery4.k2 from subquery4 where subquery1.k2 = subquery4.k2) order by k1, k2
- """
-
- //------------------unCorrelated-----------------
- qt_scalar_unCorrelated """
- select * from subquery1 where subquery1.k1 < (select sum(subquery3.k3) from subquery3 where subquery3.v2 = 2) order by k1, k2
- """
-
- qt_scalar_equal_to_uncorr """
- select * from subquery1 where subquery1.k1 = (select sum(subquery3.k3) from subquery3) order by k1, k2
- """
-
- qt_not_scalar_unCorrelated """
- select * from subquery1 where subquery1.k1 != (select sum(subquery3.k3) from subquery3 where subquery3.v2 = 2) order by k1, k2
- """
-
- qt_scalar_not_equal_uncorr """
- select * from subquery1 where subquery1.k1 != (select sum(subquery3.k3) from subquery3) order by k1, k2
- """
-
- qt_in_unCorrelated """
- select * from subquery1 where subquery1.k1 in (select subquery3.k3 from subquery3 where subquery3.v2 = 2) order by k1, k2
- """
-
- qt_in_subquery_uncorr """
- select * from subquery1 where subquery1.k1 in (select subquery3.k3 from subquery3) order by k1, k2
- """
-
- qt_not_in_unCorrelated """
- select * from subquery1 where subquery1.k1 not in (select subquery3.k3 from subquery3 where subquery3.v2 = 2) order by k1, k2
- """
-
- qt_not_in_uncorr """
- select * from subquery1 where subquery1.k1 not in (select subquery3.k3 from subquery3) order by k1, k2
- """
-
- qt_exist_unCorrelated """
- select * from subquery1 where exists (select subquery3.k3 from subquery3 where subquery3.v2 = 2) order by k1, k2
- """
-
- qt_exist_uncorr """
- select * from subquery1 where exists (select subquery3.k3 from subquery3) order by k1, k2
- """
-
- //----------with subquery alias----------
- qt_alias_scalar """
- select * from subquery1
- where subquery1.k1 < (select max(aa) from
- (select k1 as aa from subquery3 where subquery1.k2 = subquery3.v2) subquery3) order by k1, k2
- """
-
- qt_alias_in """
- select * from subquery1
- where subquery1.k1 in (select aa from
- (select k1 as aa from subquery3 where subquery1.k2 = subquery3.v2) subquery3) order by k1, k2
- """
-
- qt_alias_not_in """
- select * from subquery1
- where subquery1.k1 not in (select aa from
- (select k1 as aa from subquery3 where subquery1.k2 = subquery3.v2) subquery3) order by k1, k2
- """
-
- qt_alias_exist """
- select * from subquery1
- where exists (select aa from
- (select k1 as aa from subquery3 where subquery1.k2 = subquery3.v2) subquery3) order by k1, k2
- """
-
- qt_alias_not_exist """
- select * from subquery1
- where not exists (select aa from
- (select k1 as aa from subquery3 where subquery1.k2 = subquery3.v2) subquery3) order by k1, k2
- """
-
- //----------complex subqueries----------
- qt_scalar_subquery """
- select * from subquery1
- where k1 = (select sum(k1) from subquery3 where subquery1.k1 = subquery3.v1 and subquery3.v2 = 2)
- order by k1, k2
- """
-
- qt_in_subquery """
- select * from subquery3
- where (k1 = 1 or k1 = 2 or k1 = 3) and v1 in (select k1 from subquery1 where subquery1.k2 = subquery3.v2 and subquery1.k1 = 3)
- order by k1, k2
- """
-
- qt_exist_subquery """
- select * from subquery3
- where k1 = 2 and exists (select * from subquery1 where subquery1.k1 = subquery3.v2 and subquery1.k2 = 4)
- order by k1, k2
- """
-}
diff --git a/regression-test/suites/nereids_syntax_p0/view.groovy b/regression-test/suites/nereids_syntax_p0/view.groovy
deleted file mode 100644
index 75f4f2b86b..0000000000
--- a/regression-test/suites/nereids_syntax_p0/view.groovy
+++ /dev/null
@@ -1,105 +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("view") {
- sql """
- SET enable_vectorized_engine=true
- """
-
- sql """
- SET enable_nereids_planner=true
- """
-
- sql """
- SET enable_bucket_shuffle_join=false
- """
-
- sql """
- create view if not exists v1 as
- select *
- from customer
- """
-
- sql """
- create view if not exists v2 as
- select *
- from lineorder
- """
-
- sql """
- create view if not exists v3 as
- select *
- from v1 join (
- select *
- from v2
- ) t
- on v1.c_custkey = t.lo_custkey
- """
-
- sql "SET enable_fallback_to_original_planner=false"
-
- qt_select_1 """
- select *
- from v1
- order by v1.c_custkey
- """
-
- qt_select_2 """
- select *
- from v2
- order by v2.lo_custkey
- """
-
- qt_select_3 """
- select *
- from v3
- order by v3.c_custkey, v3.lo_orderkey
- """
-
- qt_select_4 """
- select *
- from customer c join (
- select *
- from v1
- ) t
- on c.c_custkey = t.c_custkey
- order by c.c_custkey, t.c_custkey
- """
-
- qt_select_5 """
- select *
- from lineorder l join (
- select *
- from v2
- ) t
- on l.lo_custkey = t.lo_custkey
- order by l.lo_custkey, t.lo_custkey, l.lo_linenumber, l.lo_tax
- """
-
- qt_select_6 """
- select * from (
- select *
- from part p
- join v2 on p.p_partkey = v2.lo_partkey) t1
- join (
- select *
- from supplier s
- join v3 on s.s_region = v3.c_region) t2
- on t1.p_partkey = t2.lo_partkey
- order by t1.lo_custkey, t1.p_partkey, t2.s_suppkey, t2.c_custkey, t2.lo_orderkey
- """
-}
diff --git a/regression-test/suites/ssb_sf1/nereids/q1.1.groovy b/regression-test/suites/ssb_sf1/nereids/q1.1.groovy
deleted file mode 100644
index 39a5a52f04..0000000000
--- a/regression-test/suites/ssb_sf1/nereids/q1.1.groovy
+++ /dev/null
@@ -1,45 +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("ssb_sf1_q1_1_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- // nereids need vectorized
- sql 'set enable_vectorized_engine=true'
-
- sql 'set exec_mem_limit=2147483648*2'
-
- test {
- // sql(new File(context.file.parentFile, "../sql/q1.1.sql").text)
- sql """SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1)*/
- SUM(lo_extendedprice*lo_discount) AS
- REVENUE
- FROM lineorder, date
- WHERE lo_orderdate = d_datekey
- AND d_year = 1993
- AND lo_discount BETWEEN 1 AND 3
- AND lo_quantity < 25;
- """
-
- resultFile(file = "../sql/q1.1.out", tag = "q1.1")
- }
-}
diff --git a/regression-test/suites/ssb_sf1/nereids/q1.2.groovy b/regression-test/suites/ssb_sf1/nereids/q1.2.groovy
deleted file mode 100644
index 1b4caf232d..0000000000
--- a/regression-test/suites/ssb_sf1/nereids/q1.2.groovy
+++ /dev/null
@@ -1,45 +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("ssb_sf1_q1_2_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- // nereids need vectorized
- sql 'set enable_vectorized_engine=true'
-
- sql 'set exec_mem_limit=2147483648*2'
-
- test {
- // sql(new File(context.file.parentFile, "../sql/q1.2.sql").text)
- sql """
- SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1)*/
- SUM(lo_extendedprice*lo_discount) AS
- REVENUE
- FROM lineorder, date
- WHERE lo_orderdate = d_datekey
- AND d_yearmonth = 'Jan1994'
- AND lo_discount BETWEEN 4 AND 6
- AND lo_quantity BETWEEN 26 AND 35;"""
-
- resultFile(file = "../sql/q1.2.out", tag = "q1.2")
- }
-}
diff --git a/regression-test/suites/ssb_sf1/nereids/q1.3.groovy b/regression-test/suites/ssb_sf1/nereids/q1.3.groovy
deleted file mode 100644
index 96f25f0d28..0000000000
--- a/regression-test/suites/ssb_sf1/nereids/q1.3.groovy
+++ /dev/null
@@ -1,46 +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("ssb_sf1_q1_3_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- // nereids need vectorized
- sql 'set enable_vectorized_engine=true'
-
- sql 'set exec_mem_limit=2147483648*2'
-
- test {
- // sql(new File(context.file.parentFile, "../sql/q1.3.sql").text)
- sql """SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1)*/
- SUM(lo_extendedprice*lo_discount) AS
- REVENUE
- FROM lineorder, date
- WHERE lo_orderdate = d_datekey
- AND d_weeknuminyear= 6
- AND d_year = 1994
- AND lo_discount BETWEEN 5 AND 7
- AND lo_quantity BETWEEN 26 AND 35;
- """
-
- resultFile(file = "../sql/q1.3.out", tag = "q1.3")
- }
-}
diff --git a/regression-test/suites/ssb_sf1/nereids/q2.1.groovy b/regression-test/suites/ssb_sf1/nereids/q2.1.groovy
deleted file mode 100644
index 122c2da7ec..0000000000
--- a/regression-test/suites/ssb_sf1/nereids/q2.1.groovy
+++ /dev/null
@@ -1,47 +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("ssb_sf1_q2_1_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- // nereids need vectorized
- sql 'set enable_vectorized_engine=true'
-
- sql 'set exec_mem_limit=2147483648*2'
-
- test {
- // sql(new File(context.file.parentFile, "../sql/q2.1.sql").text)
- sql """SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1)*/
- SUM(lo_revenue), d_year, p_brand
- FROM lineorder, date, part, supplier
- WHERE lo_orderdate = d_datekey
- AND lo_partkey = p_partkey
- AND lo_suppkey = s_suppkey
- AND p_category = 'MFGR#12'
- AND s_region = 'AMERICA'
- GROUP BY d_year, p_brand
- ORDER BY d_year, p_brand;
- """
-
- resultFile(file = "../sql/q2.1.out", tag = "q2.1")
- }
-}
diff --git a/regression-test/suites/ssb_sf1/nereids/q2.2.groovy b/regression-test/suites/ssb_sf1/nereids/q2.2.groovy
deleted file mode 100644
index 9a7b1d47ef..0000000000
--- a/regression-test/suites/ssb_sf1/nereids/q2.2.groovy
+++ /dev/null
@@ -1,48 +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("ssb_sf1_q2_2_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- // nereids need vectorized
- sql 'set enable_vectorized_engine=true'
-
- sql 'set exec_mem_limit=2147483648*2'
-
- test {
- // sql(new File(context.file.parentFile, "../sql/q2.2.sql").text)
- sql """SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1)*/
- SUM(lo_revenue), d_year, p_brand
- FROM lineorder, date, part, supplier
- WHERE lo_orderdate = d_datekey
- AND lo_partkey = p_partkey
- AND lo_suppkey = s_suppkey
- AND p_brand BETWEEN 'MFGR#2221'
- AND 'MFGR#2228'
- AND s_region = 'ASIA'
- GROUP BY d_year, p_brand
- ORDER BY d_year, p_brand;
- """
-
- resultFile(file = "../sql/q2.2.out", tag = "q2.2")
- }
-}
diff --git a/regression-test/suites/ssb_sf1/nereids/q2.3.groovy b/regression-test/suites/ssb_sf1/nereids/q2.3.groovy
deleted file mode 100644
index eebf4bc837..0000000000
--- a/regression-test/suites/ssb_sf1/nereids/q2.3.groovy
+++ /dev/null
@@ -1,47 +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("ssb_sf1_q2_3_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- // nereids need vectorized
- sql 'set enable_vectorized_engine=true'
-
- sql 'set exec_mem_limit=2147483648*2'
-
- test {
- // sql(new File(context.file.parentFile, "../sql/q2.3.sql").text)
- sql """SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1)*/
- SUM(lo_revenue), d_year, p_brand
- FROM lineorder, date, part, supplier
- WHERE lo_orderdate = d_datekey
- AND lo_partkey = p_partkey
- AND lo_suppkey = s_suppkey
- AND p_brand = 'MFGR#2239'
- AND s_region = 'EUROPE'
- GROUP BY d_year, p_brand
- ORDER BY d_year, p_brand;
- """
-
- resultFile(file = "../sql/q2.3.out", tag = "q2.3")
- }
-}
diff --git a/regression-test/suites/ssb_sf1/nereids/q3.1.groovy b/regression-test/suites/ssb_sf1/nereids/q3.1.groovy
deleted file mode 100644
index 27e988eb46..0000000000
--- a/regression-test/suites/ssb_sf1/nereids/q3.1.groovy
+++ /dev/null
@@ -1,48 +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("ssb_sf1_q3_1_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- // nereids need vectorized
- sql 'set enable_vectorized_engine=true'
-
- sql 'set exec_mem_limit=2147483648*16'
-
- test {
- // sql(new File(context.file.parentFile, "../sql/q3.1.sql").text)
- sql """SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1)*/
- c_nation, s_nation, d_year,
- SUM(lo_revenue) AS REVENUE
- FROM customer, lineorder, supplier, date
- WHERE lo_custkey = c_custkey
- AND lo_suppkey = s_suppkey
- AND lo_orderdate = d_datekey
- AND c_region = 'ASIA'
- AND s_region = 'ASIA'
- AND d_year >= 1992 AND d_year <= 1997
- GROUP BY c_nation, s_nation, d_year
- ORDER BY d_year ASC, REVENUE DESC;"""
-
- resultFile(file = "../sql/q3.1.out", tag = "q3.1")
- }
-}
diff --git a/regression-test/suites/ssb_sf1/nereids/q3.2.groovy b/regression-test/suites/ssb_sf1/nereids/q3.2.groovy
deleted file mode 100644
index 0a2a40579d..0000000000
--- a/regression-test/suites/ssb_sf1/nereids/q3.2.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("ssb_sf1_q3_2_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- // nereids need vectorized
- sql 'set enable_vectorized_engine=true'
-
- sql 'set exec_mem_limit=2147483648*16'
-
- test {
- // sql(new File(context.file.parentFile, "../sql/q3.2.sql").text)
- sql """SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1)*/
- c_city, s_city, d_year, sum(lo_revenue)
- AS REVENUE
- FROM customer, lineorder, supplier, date
- WHERE lo_custkey = c_custkey
- AND lo_suppkey = s_suppkey
- AND lo_orderdate = d_datekey
- AND c_nation = 'UNITED STATES'
- AND s_nation = 'UNITED STATES'
- AND d_year >= 1992 AND d_year <= 1997
- GROUP BY c_city, s_city, d_year
- ORDER BY d_year ASC, REVENUE DESC;
- """
-
- resultFile(file = "../sql/q3.2.out", tag = "q3.2")
- }
-}
diff --git a/regression-test/suites/ssb_sf1/nereids/q3.3.groovy b/regression-test/suites/ssb_sf1/nereids/q3.3.groovy
deleted file mode 100644
index f8a3d05a80..0000000000
--- a/regression-test/suites/ssb_sf1/nereids/q3.3.groovy
+++ /dev/null
@@ -1,50 +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("ssb_sf1_q3_3_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- // nereids need vectorized
- sql 'set enable_vectorized_engine=true'
-
- sql 'set exec_mem_limit=2147483648*16'
-
- test {
- // sql(new File(context.file.parentFile, "../sql/q3.3.sql").text)
- sql """SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1)*/
- c_city, s_city, d_year, SUM(lo_revenue)
- AS REVENUE
- FROM customer, lineorder, supplier, date
- WHERE lo_custkey = c_custkey
- AND lo_suppkey = s_suppkey
- AND lo_orderdate = d_datekey
- AND (c_city='UNITED KI1'
- OR c_city='UNITED KI5')
- AND (s_city='UNITED KI1'
- OR s_city='UNITED KI5')
- AND d_year >= 1992 AND d_year <= 1997
- GROUP BY c_city, s_city, d_year
- ORDER BY d_year ASC, REVENUE DESC;"""
-
- resultFile(file = "../sql/q3.3.out", tag = "q3.3")
- }
-}
diff --git a/regression-test/suites/ssb_sf1/nereids/q3.4.groovy b/regression-test/suites/ssb_sf1/nereids/q3.4.groovy
deleted file mode 100644
index ddaf28d255..0000000000
--- a/regression-test/suites/ssb_sf1/nereids/q3.4.groovy
+++ /dev/null
@@ -1,50 +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("ssb_sf1_q3_4_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- // nereids need vectorized
- sql 'set enable_vectorized_engine=true'
-
- sql 'set exec_mem_limit=2147483648*16'
-
- test {
- // sql(new File(context.file.parentFile, "../sql/q3.4.sql").text)
- sql """SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1)*/
- c_city, s_city, d_year, SUM(lo_revenue)
- AS REVENUE
- FROM customer, lineorder, supplier, date
- WHERE lo_custkey = c_custkey
- AND lo_suppkey = s_suppkey
- AND lo_orderdate = d_datekey
- AND (c_city='UNITED KI1'
- OR c_city='UNITED KI5')
- AND (s_city='UNITED KI1'
- OR s_city='UNITED KI5')
- AND d_yearmonth = 'Dec1997'
- GROUP BY c_city, s_city, d_year
- ORDER BY d_year ASC, REVENUE DESC;"""
-
- resultFile(file = "../sql/q3.4.out", tag = "q3.4")
- }
-}
diff --git a/regression-test/suites/ssb_sf1/nereids/q4.1.groovy b/regression-test/suites/ssb_sf1/nereids/q4.1.groovy
deleted file mode 100644
index 33495c838b..0000000000
--- a/regression-test/suites/ssb_sf1/nereids/q4.1.groovy
+++ /dev/null
@@ -1,51 +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("ssb_sf1_q4_1_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- // nereids need vectorized
- sql 'set enable_vectorized_engine=true'
-
- sql 'set exec_mem_limit=2147483648*16'
-
- test {
- // sql(new File(context.file.parentFile, "../sql/q4.1.sql").text)
- sql """SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1)*/
- d_year, c_nation,
- SUM(lo_revenue - lo_supplycost) AS PROFIT
- FROM date, customer, supplier, part, lineorder
- WHERE lo_custkey = c_custkey
- AND lo_suppkey = s_suppkey
- AND lo_partkey = p_partkey
- AND lo_orderdate = d_datekey
- AND c_region = 'AMERICA'
- AND s_region = 'AMERICA'
- AND (p_mfgr = 'MFGR#1'
- OR p_mfgr = 'MFGR#2')
- GROUP BY d_year, c_nation
- ORDER BY d_year, c_nation;
- """
-
- resultFile(file = "../sql/q4.1.out", tag = "q4.1")
- }
-}
diff --git a/regression-test/suites/ssb_sf1/nereids/q4.2.groovy b/regression-test/suites/ssb_sf1/nereids/q4.2.groovy
deleted file mode 100644
index be1a8bbd38..0000000000
--- a/regression-test/suites/ssb_sf1/nereids/q4.2.groovy
+++ /dev/null
@@ -1,51 +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("ssb_sf1_q4_2_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- // nereids need vectorized
- sql 'set enable_vectorized_engine=true'
-
- sql 'set exec_mem_limit=2147483648*16'
-
- test {
- // sql(new File(context.file.parentFile, "../sql/q4.2.sql").text)
- sql """SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1)*/
- d_year, s_nation, p_category,
- SUM(lo_revenue - lo_supplycost) AS PROFIT
- FROM date, customer, supplier, part, lineorder
- WHERE lo_custkey = c_custkey
- AND lo_suppkey = s_suppkey
- AND lo_partkey = p_partkey
- AND lo_orderdate = d_datekey
- AND c_region = 'AMERICA'
- AND s_region = 'AMERICA'
- AND (d_year = 1997 OR d_year = 1998)
- AND (p_mfgr = 'MFGR#1'
- OR p_mfgr = 'MFGR#2')
- GROUP BY d_year, s_nation, p_category
- ORDER BY d_year, s_nation, p_category;"""
-
- resultFile(file = "../sql/q4.2.out", tag = "q4.2")
- }
-}
diff --git a/regression-test/suites/ssb_sf1/nereids/q4.3.groovy b/regression-test/suites/ssb_sf1/nereids/q4.3.groovy
deleted file mode 100644
index 5634c516ec..0000000000
--- a/regression-test/suites/ssb_sf1/nereids/q4.3.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("ssb_sf1_q4_3_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- // nereids need vectorized
- sql 'set enable_vectorized_engine=true'
-
- sql 'set exec_mem_limit=2147483648*16'
-
- test {
- // sql(new File(context.file.parentFile, "../sql/q4.3.sql").text)
- sql """SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=1)*/
- d_year, s_city, p_brand,
- SUM(lo_revenue - lo_supplycost) AS PROFIT
- FROM date, customer, supplier, part, lineorder
- WHERE lo_custkey = c_custkey
- AND lo_suppkey = s_suppkey
- AND lo_partkey = p_partkey
- AND lo_orderdate = d_datekey
- AND s_nation = 'UNITED STATES'
- AND (d_year = 1997 OR d_year = 1998)
- AND p_category = 'MFGR#14'
- GROUP BY d_year, s_city, p_brand
- ORDER BY d_year, s_city, p_brand;"""
-
- resultFile(file = "../sql/q4.3.out", tag = "q4.3")
- }
-}
diff --git a/regression-test/suites/tpch_sf1_p1/nereids/q1.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q1.groovy
deleted file mode 100644
index 57d779fece..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q1.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("tpch_sf1_q1_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=false) */
- 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/tpch_sf1_p1/nereids/q10.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q10.groovy
deleted file mode 100644
index e50d57d63d..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q10.groovy
+++ /dev/null
@@ -1,104 +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("tpch_sf1_q10_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */
- c_custkey,
- c_name,
- sum(t1.l_extendedprice * (1 - t1.l_discount)) as revenue,
- c_acctbal,
- n_name,
- c_address,
- c_phone,
- c_comment
- from
- customer,
- (
- select o_custkey,l_extendedprice,l_discount from lineitem, orders
- where 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'
- ) t1,
- nation
- where
- c_custkey = t1.o_custkey
- 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/tpch_sf1_p1/nereids/q11.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q11.groovy
deleted file mode 100644
index 54ef9aa5ee..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q11.groovy
+++ /dev/null
@@ -1,93 +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("tpch_sf1_q11_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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.0001000000
- from
- partsupp,
- supplier,
- nation
- where
- ps_suppkey = s_suppkey
- and s_nationkey = n_nationkey
- and n_name = 'GERMANY'
- )
- order by
- value desc, ps_partkey;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
- ps_partkey,
- sum(ps_supplycost * ps_availqty) as value
- from
- partsupp,
- (
- select s_suppkey
- from supplier, nation
- where s_nationkey = n_nationkey and n_name = 'GERMANY'
- ) B
- where
- ps_suppkey = B.s_suppkey
- group by
- ps_partkey having
- sum(ps_supplycost * ps_availqty) > (
- select
- sum(ps_supplycost * ps_availqty) * 0.000002
- from
- partsupp,
- (select s_suppkey
- from supplier, nation
- where s_nationkey = n_nationkey and n_name = 'GERMANY'
- ) A
- where
- ps_suppkey = A.s_suppkey
- )
- order by
- value desc, ps_partkey;
- """
-
-}
diff --git a/regression-test/suites/tpch_sf1_p1/nereids/q12.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q12.groovy
deleted file mode 100644
index 0e204b1de5..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q12.groovy
+++ /dev/null
@@ -1,95 +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("tpch_sf1_q12_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
- 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/tpch_sf1_p1/nereids/q13.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q13.groovy
deleted file mode 100644
index c6949f8395..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q13.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("tpch_sf1_q13_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */
- c_count,
- count(*) as custdist
- from
- (
- select
- c_custkey,
- count(o_orderkey) as c_count
- from
- orders right outer join customer 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/tpch_sf1_p1/nereids/q14.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q14.groovy
deleted file mode 100644
index ae13d446b1..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q14.groovy
+++ /dev/null
@@ -1,65 +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("tpch_sf1_q14_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */
- 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
- part,
- lineitem
-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/tpch_sf1_p1/nereids/q15.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q15.groovy
deleted file mode 100644
index 6c0a6c8add..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q15.groovy
+++ /dev/null
@@ -1,97 +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("tpch_sf1_q15_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- 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 'set enable_fallback_to_original_planner=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
- 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/tpch_sf1_p1/nereids/q16.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q16.groovy
deleted file mode 100644
index 9ce7badadd..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q16.groovy
+++ /dev/null
@@ -1,99 +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("tpch_sf1_q16_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
- 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/tpch_sf1_p1/nereids/q17.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q17.groovy
deleted file mode 100644
index 552e53104d..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q17.groovy
+++ /dev/null
@@ -1,75 +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("tpch_sf1_q17_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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
- );
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
- sum(l_extendedprice) / 7.0 as avg_yearly
- from
- lineitem join
- part p1 on p1.p_partkey = l_partkey
- where
- p1.p_brand = 'Brand#23'
- and p1.p_container = 'MED BOX'
- and l_quantity < (
- select
- 0.2 * avg(l_quantity)
- from
- lineitem join
- part p2 on p2.p_partkey = l_partkey
- where
- l_partkey = p1.p_partkey
- and p2.p_brand = 'Brand#23'
- and p2.p_container = 'MED BOX'
- );
-
- """
-
-}
diff --git a/regression-test/suites/tpch_sf1_p1/nereids/q18.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q18.groovy
deleted file mode 100644
index 19ec0ae171..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q18.groovy
+++ /dev/null
@@ -1,110 +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("tpch_sf1_q18_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */
- c_name,
- c_custkey,
- t3.o_orderkey,
- t3.o_orderdate,
- t3.o_totalprice,
- sum(t3.l_quantity)
-from
-customer join
-(
- select * from
- lineitem join
- (
- select * from
- orders left semi join
- (
- select
- l_orderkey
- from
- lineitem
- group by
- l_orderkey having sum(l_quantity) > 300
- ) t1
- on o_orderkey = t1.l_orderkey
- ) t2
- on t2.o_orderkey = l_orderkey
-) t3
-on c_custkey = t3.o_custkey
-group by
- c_name,
- c_custkey,
- t3.o_orderkey,
- t3.o_orderdate,
- t3.o_totalprice
-order by
- t3.o_totalprice desc,
- t3.o_orderdate
-limit 100;
-
- """
-}
-
diff --git a/regression-test/suites/tpch_sf1_p1/nereids/q19.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q19.groovy
deleted file mode 100644
index 03b31175a5..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q19.groovy
+++ /dev/null
@@ -1,108 +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("tpch_sf1_q19_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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'
- );
- """
-
- qt_select """
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true) */
- 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/tpch_sf1_p1/nereids/q2.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q2.groovy
deleted file mode 100644
index fdf9ac790e..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q2.groovy
+++ /dev/null
@@ -1,131 +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("tpch_sf1_q2_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */
- s_acctbal,
- s_name,
- n_name,
- p_partkey,
- p_mfgr,
- s_address,
- s_phone,
- s_comment
-from
- partsupp join
- (
- select
- ps_partkey as a_partkey,
- min(ps_supplycost) as a_min
- from
- partsupp,
- part,
- 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'
- and p_size = 15
- and p_type like '%BRASS'
- group by ps_partkey
- ) A on ps_partkey = a_partkey and ps_supplycost=a_min ,
- part,
- supplier,
- 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'
-
-order by
- s_acctbal desc,
- n_name,
- s_name,
- p_partkey
-limit 100;
- """
-}
diff --git a/regression-test/suites/tpch_sf1_p1/nereids/q20.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q20.groovy
deleted file mode 100644
index 6780f03f1c..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q20.groovy
+++ /dev/null
@@ -1,101 +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("tpch_sf1_q20_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */
-s_name, s_address from
-supplier left semi join
-(
- select * from
- (
- select l_partkey,l_suppkey, 0.5 * sum(l_quantity) as l_q
- from lineitem
- where l_shipdate >= date '1994-01-01'
- and l_shipdate < date '1994-01-01' + interval '1' year
- group by l_partkey,l_suppkey
- ) t2 join
- (
- select ps_partkey, ps_suppkey, ps_availqty
- from partsupp left semi join part
- on ps_partkey = p_partkey and p_name like 'forest%'
- ) t1
- on t2.l_partkey = t1.ps_partkey and t2.l_suppkey = t1.ps_suppkey
- and t1.ps_availqty > t2.l_q
-) t3
-on s_suppkey = t3.ps_suppkey
-join nation
-where s_nationkey = n_nationkey
- and n_name = 'CANADA'
-order by s_name;
-
- """
-
-}
diff --git a/regression-test/suites/tpch_sf1_p1/nereids/q21.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q21.groovy
deleted file mode 100644
index f2c7dd5ef7..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q21.groovy
+++ /dev/null
@@ -1,109 +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("tpch_sf1_q21_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */
-s_name, count(*) as numwait
-from orders join
-(
- select * from
- lineitem l2 right semi join
- (
- select * from
- lineitem l3 right anti join
- (
- select * from
- lineitem l1 join
- (
- select * from
- supplier join nation
- where s_nationkey = n_nationkey
- and n_name = 'SAUDI ARABIA'
- ) t1
- where t1.s_suppkey = l1.l_suppkey and l1.l_receiptdate > l1.l_commitdate
- ) t2
- on l3.l_orderkey = t2.l_orderkey and l3.l_suppkey <> t2.l_suppkey and l3.l_receiptdate > l3.l_commitdate
- ) t3
- on l2.l_orderkey = t3.l_orderkey and l2.l_suppkey <> t3.l_suppkey
-) t4
-on o_orderkey = t4.l_orderkey and o_orderstatus = 'F'
-group by
- t4.s_name
-order by
- numwait desc,
- t4.s_name
-limit 100;
- """
-
-}
\ No newline at end of file
diff --git a/regression-test/suites/tpch_sf1_p1/nereids/q22.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q22.groovy
deleted file mode 100644
index bbd1dfa8bb..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q22.groovy
+++ /dev/null
@@ -1,112 +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("tpch_sf1_q22_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
- 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/tpch_sf1_p1/nereids/q3.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q3.groovy
deleted file mode 100644
index a484741f7c..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q3.groovy
+++ /dev/null
@@ -1,84 +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("tpch_sf1_q3_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */
- l_orderkey,
- sum(l_extendedprice * (1 - l_discount)) as revenue,
- o_orderdate,
- o_shippriority
- from
- (
- select l_orderkey, l_extendedprice, l_discount, o_orderdate, o_shippriority, o_custkey from
- lineitem join orders
- where l_orderkey = o_orderkey
- and o_orderdate < date '1995-03-15'
- and l_shipdate > date '1995-03-15'
- ) t1 join customer c
- on c.c_custkey = t1.o_custkey
- where c_mktsegment = 'BUILDING'
- group by
- l_orderkey,
- o_orderdate,
- o_shippriority
- order by
- revenue desc,
- o_orderdate
- limit 10;
-
- """
-
-}
diff --git a/regression-test/suites/tpch_sf1_p1/nereids/q4.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q4.groovy
deleted file mode 100644
index 8f4c4cf30e..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q4.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("tpch_sf1_q4_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */
- o_orderpriority,
- count(*) as order_count
- from
- (
- select
- *
- from
- lineitem
- where l_commitdate < l_receiptdate
- ) t1
- right semi join orders
- on t1.l_orderkey = o_orderkey
- where
- o_orderdate >= date '1993-07-01'
- and o_orderdate < date '1993-07-01' + interval '3' month
- group by
- o_orderpriority
- order by
- o_orderpriority;
- """
-
-}
diff --git a/regression-test/suites/tpch_sf1_p1/nereids/q5.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q5.groovy
deleted file mode 100644
index 9d5011dfe0..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q5.groovy
+++ /dev/null
@@ -1,88 +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("tpch_sf1_q5_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
-
- select /*+SET_VAR(exec_mem_limit=8589934592, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true) */
- 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/tpch_sf1_p1/nereids/q6.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q6.groovy
deleted file mode 100644
index 9ece6bccd3..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q6.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("tpch_sf1_q6_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true) */
- 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/tpch_sf1_p1/nereids/q7.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q7.groovy
deleted file mode 100644
index cf18a3aa2e..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q7.groovy
+++ /dev/null
@@ -1,117 +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("tpch_sf1_q7_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true) */
- 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/tpch_sf1_p1/nereids/q8.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q8.groovy
deleted file mode 100644
index e1d112ae08..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q8.groovy
+++ /dev/null
@@ -1,114 +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("tpch_sf1_q8_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
-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;
- """
-
- qt_select """
-
-select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */
- 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
- lineitem,
- orders,
- customer,
- supplier,
- part,
- 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/tpch_sf1_p1/nereids/q9.groovy b/regression-test/suites/tpch_sf1_p1/nereids/q9.groovy
deleted file mode 100644
index c5ba64452b..0000000000
--- a/regression-test/suites/tpch_sf1_p1/nereids/q9.groovy
+++ /dev/null
@@ -1,96 +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("tpch_sf1_q9_nereids") {
- String realDb = context.config.getDbNameByFile(context.file)
- // get parent directory's group
- realDb = realDb.substring(0, realDb.lastIndexOf("_"))
-
- sql "use ${realDb}"
-
- sql 'set enable_nereids_planner=true'
- sql 'set enable_fallback_to_original_planner=false'
- sql 'set exec_mem_limit=8589934592'
- sql 'set enable_nereids_stats_derive_v2=false'
-
- qt_select """
- 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;
- """
-
- qt_select """
- select/*+SET_VAR(exec_mem_limit=17179869184, parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true, enable_runtime_filter_prune=true, runtime_filter_wait_time_ms=10000) */
- 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
- lineitem join orders on o_orderkey = l_orderkey
- join part on p_partkey = l_partkey
- join partsupp on ps_partkey = l_partkey
- join supplier on s_suppkey = l_suppkey
- join nation on s_nationkey = n_nationkey
- where
- ps_suppkey = l_suppkey 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