This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 208d21b01d [tools](tpch) use origin TPCH qurries (#19479)
208d21b01d is described below
commit 208d21b01d192b035bb94750e7209bfb7f1fc89c
Author: Gabriel <[email protected]>
AuthorDate: Wed May 10 14:29:45 2023 +0800
[tools](tpch) use origin TPCH qurries (#19479)
---
tools/tpch-tools/queries/q1.sql | 2 +-
tools/tpch-tools/queries/q10.sql | 21 ++++++--------
tools/tpch-tools/queries/q11.sql | 37 ++++++++++++-----------
tools/tpch-tools/queries/q12.sql | 2 +-
tools/tpch-tools/queries/q13.sql | 6 ++--
tools/tpch-tools/queries/q14.sql | 8 ++---
tools/tpch-tools/queries/q15.sql | 2 +-
tools/tpch-tools/queries/q16.sql | 2 +-
tools/tpch-tools/queries/q17.sql | 18 +++++-------
tools/tpch-tools/queries/q18.sql | 63 ++++++++++++++++++----------------------
tools/tpch-tools/queries/q19.sql | 2 +-
tools/tpch-tools/queries/q2.sql | 42 +++++++++++----------------
tools/tpch-tools/queries/q20.sql | 62 ++++++++++++++++++++++-----------------
tools/tpch-tools/queries/q21.sql | 62 ++++++++++++++++++++++-----------------
tools/tpch-tools/queries/q22.sql | 35 +++++++++++++---------
tools/tpch-tools/queries/q3.sql | 22 +++++++-------
tools/tpch-tools/queries/q4.sql | 21 +++++++-------
tools/tpch-tools/queries/q5.sql | 2 +-
tools/tpch-tools/queries/q6.sql | 2 +-
tools/tpch-tools/queries/q7.sql | 4 +--
tools/tpch-tools/queries/q8.sql | 8 ++---
tools/tpch-tools/queries/q9.sql | 24 ++++++++-------
22 files changed, 224 insertions(+), 223 deletions(-)
diff --git a/tools/tpch-tools/queries/q1.sql b/tools/tpch-tools/queries/q1.sql
index 2949a9ede3..488bd9a4e3 100644
--- a/tools/tpch-tools/queries/q1.sql
+++ b/tools/tpch-tools/queries/q1.sql
@@ -15,7 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=16, batch_size=4096,
disable_join_reorder=false, enable_cost_based_join_reorder=false,
enable_projection=false) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
diff --git a/tools/tpch-tools/queries/q10.sql b/tools/tpch-tools/queries/q10.sql
index 8513b1863f..3e68b96f96 100644
--- a/tools/tpch-tools/queries/q10.sql
+++ b/tools/tpch-tools/queries/q10.sql
@@ -15,12 +15,10 @@
-- specific language governing permissions and limitations
-- under the License.
--- Modified
-
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=16, batch_size=4096,
disable_join_reorder=true, enable_cost_based_join_reorder=false,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
c_custkey,
c_name,
- sum(t1.l_extendedprice * (1 - t1.l_discount)) as revenue,
+ sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal,
n_name,
c_address,
@@ -28,16 +26,15 @@ select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num
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,
+ orders,
+ lineitem,
nation
where
- c_custkey = t1.o_custkey
+ 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,
diff --git a/tools/tpch-tools/queries/q11.sql b/tools/tpch-tools/queries/q11.sql
index 750016f943..e17cb3cd79 100644
--- a/tools/tpch-tools/queries/q11.sql
+++ b/tools/tpch-tools/queries/q11.sql
@@ -15,31 +15,30 @@
-- specific language governing permissions and limitations
-- under the License.
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=2, batch_size=4096,
disable_join_reorder=false, enable_cost_based_join_reorder=true,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=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
+ supplier,
+ nation
where
- ps_suppkey = B.s_suppkey
+ ps_suppkey = s_suppkey
+ and s_nationkey = n_nationkey
+ and n_name = 'GERMANY'
group by
ps_partkey having
- sum(ps_supplycost * ps_availqty) > (
- select
- sum(ps_supplycost * ps_availqty) * 0.000002
- from
- partsupp,
- (select s_suppkey
- from supplier, nation
- where s_nationkey = n_nationkey and n_name = 'GERMANY'
- ) A
- where
- ps_suppkey = A.s_suppkey
- )
+ sum(ps_supplycost * ps_availqty) > (
+ select
+ sum(ps_supplycost * ps_availqty) * 0.000002
+ from
+ partsupp,
+ supplier,
+ nation
+ where
+ ps_suppkey = s_suppkey
+ and s_nationkey = n_nationkey
+ and n_name = 'GERMANY'
+ )
order by
value desc;
\ No newline at end of file
diff --git a/tools/tpch-tools/queries/q12.sql b/tools/tpch-tools/queries/q12.sql
index 30d5fdf43d..7e9e723fc0 100644
--- a/tools/tpch-tools/queries/q12.sql
+++ b/tools/tpch-tools/queries/q12.sql
@@ -15,7 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=2, batch_size=4096,
disable_join_reorder=true, enable_cost_based_join_reorder=true,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
diff --git a/tools/tpch-tools/queries/q13.sql b/tools/tpch-tools/queries/q13.sql
index 82326ee527..e934a7e0ed 100644
--- a/tools/tpch-tools/queries/q13.sql
+++ b/tools/tpch-tools/queries/q13.sql
@@ -15,9 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
--- Modified
-
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=16, batch_size=4096,
disable_join_reorder=true, enable_cost_based_join_reorder=true,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
c_count,
count(*) as custdist
from
@@ -26,7 +24,7 @@ from
c_custkey,
count(o_orderkey) as c_count
from
- orders right outer join customer on
+ customer left outer join orders on
c_custkey = o_custkey
and o_comment not like '%special%requests%'
group by
diff --git a/tools/tpch-tools/queries/q14.sql b/tools/tpch-tools/queries/q14.sql
index a04251ffc3..4f72e3eb5d 100644
--- a/tools/tpch-tools/queries/q14.sql
+++ b/tools/tpch-tools/queries/q14.sql
@@ -15,17 +15,15 @@
-- specific language governing permissions and limitations
-- under the License.
--- Modified
-
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, batch_size=4096,
disable_join_reorder=true, enable_cost_based_join_reorder=true,
enable_projection=true, runtime_filter_mode=OFF) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=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
+ lineitem,
+ part
where
l_partkey = p_partkey
and l_shipdate >= date '1995-09-01'
diff --git a/tools/tpch-tools/queries/q15.sql b/tools/tpch-tools/queries/q15.sql
index e3c55ca5c2..696526a099 100644
--- a/tools/tpch-tools/queries/q15.sql
+++ b/tools/tpch-tools/queries/q15.sql
@@ -15,7 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, batch_size=4096,
disable_join_reorder=false, enable_cost_based_join_reorder=true,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
s_suppkey,
s_name,
s_address,
diff --git a/tools/tpch-tools/queries/q16.sql b/tools/tpch-tools/queries/q16.sql
index e641749cdd..30fa1e1a8a 100644
--- a/tools/tpch-tools/queries/q16.sql
+++ b/tools/tpch-tools/queries/q16.sql
@@ -15,7 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, batch_size=4096,
disable_join_reorder=false, enable_cost_based_join_reorder=true,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
p_brand,
p_type,
p_size,
diff --git a/tools/tpch-tools/queries/q17.sql b/tools/tpch-tools/queries/q17.sql
index 5d6e8e9ce9..eb312b6fa6 100644
--- a/tools/tpch-tools/queries/q17.sql
+++ b/tools/tpch-tools/queries/q17.sql
@@ -17,22 +17,20 @@
-- Modified
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=1, batch_size=4096,
disable_join_reorder=false, enable_cost_based_join_reorder=true,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
sum(l_extendedprice) / 7.0 as avg_yearly
from
- lineitem join [broadcast]
- part p1 on p1.p_partkey = l_partkey
+ lineitem,
+ part
where
- p1.p_brand = 'Brand#23'
- and p1.p_container = 'MED BOX'
+ 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 join [broadcast]
- part p2 on p2.p_partkey = l_partkey
+ lineitem
where
- l_partkey = p1.p_partkey
- and p2.p_brand = 'Brand#23'
- and p2.p_container = 'MED BOX'
+ l_partkey = p_partkey
);
diff --git a/tools/tpch-tools/queries/q18.sql b/tools/tpch-tools/queries/q18.sql
index 08ca55c4e3..6b2eea1d3b 100644
--- a/tools/tpch-tools/queries/q18.sql
+++ b/tools/tpch-tools/queries/q18.sql
@@ -15,44 +15,37 @@
-- specific language governing permissions and limitations
-- under the License.
--- Modified
-
-select /*+SET_VAR(exec_mem_limit=16589934592,
parallel_fragment_exec_instance_num=16, batch_size=4096,
disable_join_reorder=true, enable_cost_based_join_reorder=true,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
c_name,
c_custkey,
- t3.o_orderkey,
- t3.o_orderdate,
- t3.o_totalprice,
- sum(t3.l_quantity)
+ o_orderkey,
+ o_orderdate,
+ o_totalprice,
+ sum(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
+ 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,
- t3.o_orderkey,
- t3.o_orderdate,
- t3.o_totalprice
-order by
- t3.o_totalprice desc,
- t3.o_orderdate
-limit 100;
+ o_orderkey,
+ o_orderdate,
+ o_totalprice
+order by
+ o_totalprice desc,
+ o_orderdate
+limit 100;
diff --git a/tools/tpch-tools/queries/q19.sql b/tools/tpch-tools/queries/q19.sql
index 6d92d078e2..d3750eddcb 100644
--- a/tools/tpch-tools/queries/q19.sql
+++ b/tools/tpch-tools/queries/q19.sql
@@ -15,7 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=2, batch_size=4096,
disable_join_reorder=false, enable_cost_based_join_reorder=false,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem,
diff --git a/tools/tpch-tools/queries/q2.sql b/tools/tpch-tools/queries/q2.sql
index 7262d5e4ff..6e8f2ecffa 100644
--- a/tools/tpch-tools/queries/q2.sql
+++ b/tools/tpch-tools/queries/q2.sql
@@ -15,9 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
--- Modified
-
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=1, batch_size=4096,
disable_join_reorder=true, enable_cost_based_join_reorder=false,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
s_acctbal,
s_name,
n_name,
@@ -27,29 +25,9 @@ select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num
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 a_partkey
- ) A on ps_partkey = a_partkey and ps_supplycost=a_min ,
part,
supplier,
+ partsupp,
nation,
region
where
@@ -60,7 +38,21 @@ where
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,
diff --git a/tools/tpch-tools/queries/q20.sql b/tools/tpch-tools/queries/q20.sql
index abcdd0bc20..e693f36ad3 100644
--- a/tools/tpch-tools/queries/q20.sql
+++ b/tools/tpch-tools/queries/q20.sql
@@ -15,30 +15,40 @@
-- specific language governing permissions and limitations
-- under the License.
--- Modified
-
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=2, batch_size=4096,
disable_join_reorder=true, enable_cost_based_join_reorder=true,
enable_projection=true, runtime_bloom_filter_size=551943) */
-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
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
+ 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;
+order by
+ s_name;
diff --git a/tools/tpch-tools/queries/q21.sql b/tools/tpch-tools/queries/q21.sql
index b1aa0ab258..146fe1fd82 100644
--- a/tools/tpch-tools/queries/q21.sql
+++ b/tools/tpch-tools/queries/q21.sql
@@ -15,35 +15,43 @@
-- specific language governing permissions and limitations
-- under the License.
--- Modified
-
-
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=16, batch_size=4096,
disable_join_reorder=true, enable_cost_based_join_reorder=true,
enable_projection=true) */
-s_name, count(*) as numwait
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
+ s_name,
+ count(*) as numwait
from
- lineitem l2 right semi join
- (
- select * from
- lineitem l3 right anti join
- (
- select * from
- orders join lineitem l1 on l1.l_orderkey = o_orderkey and o_orderstatus
= 'F'
- 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
-
+ 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
- t3.s_name
+ s_name
order by
numwait desc,
- t3.s_name
+ s_name
limit 100;
\ No newline at end of file
diff --git a/tools/tpch-tools/queries/q22.sql b/tools/tpch-tools/queries/q22.sql
index 13940f2993..498307535a 100644
--- a/tools/tpch-tools/queries/q22.sql
+++ b/tools/tpch-tools/queries/q22.sql
@@ -15,29 +15,38 @@
-- specific language governing permissions and limitations
-- under the License.
-with tmp as (select
- avg(c_acctbal) as av
- from
- customer
- where
- c_acctbal > 0.00
- and substring(c_phone, 1, 2) in
- ('13', '31', '23', '29', '30', '18', '17'))
-
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=4,runtime_bloom_filter_size=4194304) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
cntrycode,
count(*) as numcust,
sum(c_acctbal) as totacctbal
from
(
- select
+ select
substring(c_phone, 1, 2) as cntrycode,
c_acctbal
from
- orders right anti join customer c on o_custkey = c.c_custkey
join tmp on c.c_acctbal > tmp.av
+ customer
where
substring(c_phone, 1, 2) in
- ('13', '31', '23', '29', '30', '18', '17')
+ ('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
diff --git a/tools/tpch-tools/queries/q3.sql b/tools/tpch-tools/queries/q3.sql
index cbe98c4902..f0f97f8e07 100644
--- a/tools/tpch-tools/queries/q3.sql
+++ b/tools/tpch-tools/queries/q3.sql
@@ -15,23 +15,21 @@
-- specific language governing permissions and limitations
-- under the License.
--- Modified
-
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, batch_size=4096,
disable_join_reorder=true, enable_cost_based_join_reorder=false,
enable_projection=true, runtime_filter_wait_time_ms=10000) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=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'
+ 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,
diff --git a/tools/tpch-tools/queries/q4.sql b/tools/tpch-tools/queries/q4.sql
index 9a71cee620..0fd4f871a3 100644
--- a/tools/tpch-tools/queries/q4.sql
+++ b/tools/tpch-tools/queries/q4.sql
@@ -15,24 +15,23 @@
-- specific language governing permissions and limitations
-- under the License.
--- Modified
-
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=4, batch_size=4096,
disable_join_reorder=true, enable_cost_based_join_reorder=false,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
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_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
+ where
+ l_orderkey = o_orderkey
+ and l_commitdate < l_receiptdate
+ )
group by
o_orderpriority
order by
diff --git a/tools/tpch-tools/queries/q5.sql b/tools/tpch-tools/queries/q5.sql
index a9238269a5..f43d7ab542 100644
--- a/tools/tpch-tools/queries/q5.sql
+++ b/tools/tpch-tools/queries/q5.sql
@@ -15,7 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, batch_size=4096,
disable_join_reorder=false, enable_cost_based_join_reorder=false,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
diff --git a/tools/tpch-tools/queries/q6.sql b/tools/tpch-tools/queries/q6.sql
index e12868682e..65f9d90112 100644
--- a/tools/tpch-tools/queries/q6.sql
+++ b/tools/tpch-tools/queries/q6.sql
@@ -15,7 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=1, batch_size=4096,
disable_join_reorder=false, enable_cost_based_join_reorder=false,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
sum(l_extendedprice * l_discount) as revenue
from
lineitem
diff --git a/tools/tpch-tools/queries/q7.sql b/tools/tpch-tools/queries/q7.sql
index 12358588b7..5939c82523 100644
--- a/tools/tpch-tools/queries/q7.sql
+++ b/tools/tpch-tools/queries/q7.sql
@@ -15,7 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, batch_size=4096,
disable_join_reorder=false, enable_cost_based_join_reorder=false,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
supp_nation,
cust_nation,
l_year,
@@ -53,4 +53,4 @@ group by
order by
supp_nation,
cust_nation,
- l_year;
+ l_year;
\ No newline at end of file
diff --git a/tools/tpch-tools/queries/q8.sql b/tools/tpch-tools/queries/q8.sql
index fab6ef1c26..1d031cc0f1 100644
--- a/tools/tpch-tools/queries/q8.sql
+++ b/tools/tpch-tools/queries/q8.sql
@@ -15,9 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
--- Modified
-
-select /*+SET_VAR(exec_mem_limit=8589934592,
parallel_fragment_exec_instance_num=8, batch_size=4096,
disable_join_reorder=true, enable_cost_based_join_reorder=false,
enable_projection=true) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
o_year,
sum(case
when nation = 'BRAZIL' then volume
@@ -30,11 +28,11 @@ from
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
+ part,
+ supplier,
lineitem,
orders,
customer,
- supplier,
- part,
nation n1,
nation n2,
region
diff --git a/tools/tpch-tools/queries/q9.sql b/tools/tpch-tools/queries/q9.sql
index 154c411615..a4f58b34df 100644
--- a/tools/tpch-tools/queries/q9.sql
+++ b/tools/tpch-tools/queries/q9.sql
@@ -15,9 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
--- Modified
-
-select/*+SET_VAR(exec_mem_limit=37179869184,
parallel_fragment_exec_instance_num=8, 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) */
+select /*+SET_VAR(enable_nereids_planner=true,enable_pipeline_engine=true) */
nation,
o_year,
sum(amount) as sum_profit
@@ -28,14 +26,20 @@ from
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[shuffle] part on p_partkey = l_partkey
- join[shuffle] partsupp on ps_partkey = l_partkey
- join[shuffle] supplier on s_suppkey = l_suppkey
- join[broadcast] nation on s_nationkey = n_nationkey
+ part,
+ supplier,
+ lineitem,
+ partsupp,
+ orders,
+ nation
where
- ps_suppkey = l_suppkey and
- p_name like '%green%'
+ 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,
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
