This is an automated email from the ASF dual-hosted git repository.
dheres pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new 65124377f9 Test sort merge join on TPC-H benchmark (#13572)
65124377f9 is described below
commit 65124377f9bacd39eaf40bf7514a093fb709309a
Author: Daniël Heres <[email protected]>
AuthorDate: Fri Nov 29 08:43:24 2024 +0100
Test sort merge join on TPC-H benchmark (#13572)
* Test merge join
* Add files
---
.../test_files/tpch/answers/q1.slt.part | 45 +++++++++
.../test_files/tpch/answers/q10.slt.part | 62 ++++++++++++
.../tpch/{tpch.slt => answers/q11.slt.part} | 44 ++++++++-
.../tpch/{tpch.slt => answers/q12.slt.part} | 38 +++++++-
.../tpch/{tpch.slt => answers/q13.slt.part} | 37 ++++++-
.../tpch/{tpch.slt => answers/q14.slt.part} | 20 +++-
.../tpch/{tpch.slt => answers/q15.slt.part} | 38 +++++++-
.../test_files/tpch/answers/q16.slt.part | 61 ++++++++++++
.../tpch/{tpch.slt => answers/q17.slt.part} | 24 ++++-
.../test_files/tpch/answers/q18.slt.part | 57 +++++++++++
.../test_files/tpch/answers/q19.slt.part | 56 +++++++++++
.../test_files/tpch/answers/q2.slt.part | 74 ++++++++++++++
.../test_files/tpch/answers/q20.slt.part | 66 +++++++++++++
.../test_files/tpch/answers/q21.slt.part | 106 +++++++++++++++++++++
.../test_files/tpch/answers/q22.slt.part | 64 +++++++++++++
.../tpch/{tpch.slt => answers/q3.slt.part} | 39 +++++++-
.../tpch/{tpch.slt => answers/q4.slt.part} | 32 ++++++-
.../tpch/{tpch.slt => answers/q5.slt.part} | 35 ++++++-
.../tpch/{tpch.slt => answers/q6.slt.part} | 16 +++-
.../test_files/tpch/answers/q7.slt.part | 63 ++++++++++++
.../test_files/tpch/answers/q8.slt.part | 59 ++++++++++++
.../test_files/tpch/answers/q9.slt.part | 63 ++++++++++++
.../test_files/tpch/{ => plans}/q1.slt.part | 30 +-----
.../test_files/tpch/{ => plans}/q10.slt.part | 47 ---------
.../test_files/tpch/{ => plans}/q11.slt.part | 42 --------
.../test_files/tpch/{ => plans}/q12.slt.part | 37 -------
.../test_files/tpch/{ => plans}/q13.slt.part | 36 -------
.../test_files/tpch/{ => plans}/q14.slt.part | 19 ----
.../test_files/tpch/{ => plans}/q15.slt.part | 35 -------
.../test_files/tpch/{ => plans}/q16.slt.part | 45 ---------
.../test_files/tpch/{ => plans}/q17.slt.part | 22 -----
.../test_files/tpch/{ => plans}/q18.slt.part | 42 --------
.../test_files/tpch/{ => plans}/q19.slt.part | 41 --------
.../test_files/tpch/{ => plans}/q2.slt.part | 59 ------------
.../test_files/tpch/{ => plans}/q20.slt.part | 50 ----------
.../test_files/tpch/{ => plans}/q21.slt.part | 91 ------------------
.../test_files/tpch/{ => plans}/q22.slt.part | 48 ----------
.../test_files/tpch/{ => plans}/q3.slt.part | 38 --------
.../test_files/tpch/{ => plans}/q4.slt.part | 31 ------
.../test_files/tpch/{ => plans}/q5.slt.part | 34 -------
.../test_files/tpch/{ => plans}/q6.slt.part | 15 ---
.../test_files/tpch/{ => plans}/q7.slt.part | 48 ----------
.../test_files/tpch/{ => plans}/q8.slt.part | 44 ---------
.../test_files/tpch/{ => plans}/q9.slt.part | 48 ----------
datafusion/sqllogictest/test_files/tpch/tpch.slt | 11 ++-
45 files changed, 1070 insertions(+), 942 deletions(-)
diff --git a/datafusion/sqllogictest/test_files/tpch/answers/q1.slt.part
b/datafusion/sqllogictest/test_files/tpch/answers/q1.slt.part
new file mode 100644
index 0000000000..bd8761bbb7
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q1.slt.part
@@ -0,0 +1,45 @@
+
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+query TTRRRRRRRI
+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-09-02'
+group by
+ l_returnflag,
+ l_linestatus
+order by
+ l_returnflag,
+ l_linestatus;
+----
+A F 3774200 5320753880.69 5054096266.6828 5256751331.449234 25.537587
36002.123829 0.050144 147790
+N F 95257 133737795.84 127132372.6512 132286291.229445 25.300664 35521.326916
0.049394 3765
+N O 7459297 10512270008.9 9986238338.3847 10385578376.585467 25.545537
36000.924688 0.050095 292000
+R F 3785523 5337950526.47 5071818532.942 5274405503.049367 25.525943
35994.029214 0.049989 148301
diff --git a/datafusion/sqllogictest/test_files/tpch/answers/q10.slt.part
b/datafusion/sqllogictest/test_files/tpch/answers/q10.slt.part
new file mode 100644
index 0000000000..8a8d1a00d9
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q10.slt.part
@@ -0,0 +1,62 @@
+
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+query ITRRTTTT
+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 '1994-01-01'
+ 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 10;
+----
+8242 Customer#000008242 622786.7297 6322.09 ETHIOPIA
P2n4nJhy,UqSo2s43YfSvYJDZ6lk 15-792-676-1184 slyly regular packages haggle
carefully ironic ideas. courts are furiously. furiously unusual theodolites
cajole. i
+7714 Customer#000007714 557400.3053 9799.98 IRAN SnnIGB,SkmnWpX3
20-922-418-6024 arhorses according to the blithely express re
+11032 Customer#000011032 512500.9641 8496.93 UNITED KINGDOM
WIKHC7K3Cn7156iNOyfVG3cZ7YqkgsR,Ly 33-102-772-3533 posits-- furiously ironic
accounts are again
+2455 Customer#000002455 481592.4053 2070.99 GERMANY RVn1ZSRtLqPlJLIZxvpmsbgC02
17-946-225-9977 al asymptotes. finally ironic accounts cajole furiously.
permanently unusual theodolites aro
+12106 Customer#000012106 479414.2133 5342.11 UNITED STATES wth3twOmu6vy
34-905-346-4472 ly after the blithely regular foxes. accounts haggle carefully
alongside of the blithely even ideas.
+8530 Customer#000008530 457855.9467 9734.95 MOROCCO GMQyte94oDM7eD7exnkj
4hH9yq3 25-736-932-5850 slyly asymptotes. quickly final deposits in
+13984 Customer#000013984 446316.5104 3482.28 IRAN qZXwuapCHvxbX
20-981-264-2952 y unusual courts could wake furiously
+1966 Customer#000001966 444059.0382 1937.72 ALGERIA jPv1
UHra5JLALR5Isci5u0636RoAu7t vH 10-973-269-8886 the blithely even accounts.
final deposits cajole around the blithely final packages.
+11026 Customer#000011026 417913.4142 7738.76 ALGERIA XorIktoJOAEJkpNNMx
10-184-163-4632 ly even dolphins eat along the blithely even instructions.
express attainments cajole slyly. busy dolphins in
+8501 Customer#000008501 412797.51 6906.7 ARGENTINA 776af4rOa mZ66hczs
11-317-552-5840 y final deposits after the fluffily even accounts are slyly
final, regular
diff --git a/datafusion/sqllogictest/test_files/tpch/tpch.slt
b/datafusion/sqllogictest/test_files/tpch/answers/q11.slt.part
similarity index 51%
copy from datafusion/sqllogictest/test_files/tpch/tpch.slt
copy to datafusion/sqllogictest/test_files/tpch/answers/q11.slt.part
index 9a876acf08..53fb6bca29 100644
--- a/datafusion/sqllogictest/test_files/tpch/tpch.slt
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q11.slt.part
@@ -1,5 +1,4 @@
-
# 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
@@ -17,6 +16,43 @@
# specific language governing permissions and limitations
# under the License.
-include ./create_tables.slt.part
-include ./q*.slt.part
-include ./drop_tables.slt.part
+query IR
+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.0001
+ from
+ partsupp,
+ supplier,
+ nation
+ where
+ ps_suppkey = s_suppkey
+ and s_nationkey = n_nationkey
+ and n_name = 'GERMANY'
+ )
+order by
+ value desc
+limit 10;
+----
+12098 16227681.21
+5134 15709338.52
+13334 15023662.41
+17052 14351644.2
+3452 14070870.14
+12552 13332469.18
+1084 13170428.29
+5797 13038622.72
+12633 12892561.61
+403 12856217.34
diff --git a/datafusion/sqllogictest/test_files/tpch/tpch.slt
b/datafusion/sqllogictest/test_files/tpch/answers/q12.slt.part
similarity index 51%
copy from datafusion/sqllogictest/test_files/tpch/tpch.slt
copy to datafusion/sqllogictest/test_files/tpch/answers/q12.slt.part
index 9a876acf08..4d8e9a1a7e 100644
--- a/datafusion/sqllogictest/test_files/tpch/tpch.slt
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q12.slt.part
@@ -1,5 +1,4 @@
-
# 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
@@ -17,6 +16,37 @@
# specific language governing permissions and limitations
# under the License.
-include ./create_tables.slt.part
-include ./q*.slt.part
-include ./drop_tables.slt.part
+query TII
+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
+ lineitem
+ join
+ orders
+ on
+ l_orderkey = o_orderkey
+where
+ 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 '1995-01-01'
+group by
+ l_shipmode
+order by
+ l_shipmode;
+----
+MAIL 647 945
+SHIP 620 943
diff --git a/datafusion/sqllogictest/test_files/tpch/tpch.slt
b/datafusion/sqllogictest/test_files/tpch/answers/q13.slt.part
similarity index 59%
copy from datafusion/sqllogictest/test_files/tpch/tpch.slt
copy to datafusion/sqllogictest/test_files/tpch/answers/q13.slt.part
index 9a876acf08..40971133d2 100644
--- a/datafusion/sqllogictest/test_files/tpch/tpch.slt
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q13.slt.part
@@ -1,5 +1,4 @@
-
# 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
@@ -17,6 +16,36 @@
# specific language governing permissions and limitations
# under the License.
-include ./create_tables.slt.part
-include ./q*.slt.part
-include ./drop_tables.slt.part
+query II
+select
+ c_count,
+ count(*) as custdist
+from
+ (
+ select
+ c_custkey,
+ count(o_orderkey)
+ 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 (c_custkey, c_count)
+group by
+ c_count
+order by
+ custdist desc,
+ c_count desc
+limit 10;
+----
+0 5000
+10 665
+9 657
+11 621
+12 567
+8 564
+13 492
+18 482
+7 480
+20 456
diff --git a/datafusion/sqllogictest/test_files/tpch/tpch.slt
b/datafusion/sqllogictest/test_files/tpch/answers/q14.slt.part
similarity index 64%
copy from datafusion/sqllogictest/test_files/tpch/tpch.slt
copy to datafusion/sqllogictest/test_files/tpch/answers/q14.slt.part
index 9a876acf08..e3a72548bc 100644
--- a/datafusion/sqllogictest/test_files/tpch/tpch.slt
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q14.slt.part
@@ -1,5 +1,4 @@
-
# 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
@@ -17,6 +16,19 @@
# specific language governing permissions and limitations
# under the License.
-include ./create_tables.slt.part
-include ./q*.slt.part
-include ./drop_tables.slt.part
+query R
+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-10-01';
+----
+16.283855689006
diff --git a/datafusion/sqllogictest/test_files/tpch/tpch.slt
b/datafusion/sqllogictest/test_files/tpch/answers/q15.slt.part
similarity index 58%
copy from datafusion/sqllogictest/test_files/tpch/tpch.slt
copy to datafusion/sqllogictest/test_files/tpch/answers/q15.slt.part
index 9a876acf08..bfee9de111 100644
--- a/datafusion/sqllogictest/test_files/tpch/tpch.slt
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q15.slt.part
@@ -1,5 +1,4 @@
-
# 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
@@ -17,6 +16,37 @@
# specific language governing permissions and limitations
# under the License.
-include ./create_tables.slt.part
-include ./q*.slt.part
-include ./drop_tables.slt.part
+query ITTTR
+with 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
+)
+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;
+----
+677 Supplier#000000677 8mhrffG7D2WJBSQbOGstQ 23-290-639-3315 1614410.2928
diff --git a/datafusion/sqllogictest/test_files/tpch/answers/q16.slt.part
b/datafusion/sqllogictest/test_files/tpch/answers/q16.slt.part
new file mode 100644
index 0000000000..4412eda050
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q16.slt.part
@@ -0,0 +1,61 @@
+
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+query TTII
+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
+limit 10;
+----
+Brand#14 SMALL ANODIZED NICKEL 45 12
+Brand#22 SMALL BURNISHED BRASS 19 12
+Brand#25 PROMO POLISHED COPPER 14 12
+Brand#35 LARGE ANODIZED STEEL 45 12
+Brand#35 PROMO BRUSHED COPPER 9 12
+Brand#51 ECONOMY ANODIZED STEEL 9 12
+Brand#53 LARGE BRUSHED NICKEL 45 12
+Brand#11 ECONOMY POLISHED COPPER 14 8
+Brand#11 LARGE PLATED STEEL 23 8
+Brand#11 PROMO POLISHED STEEL 23 8
diff --git a/datafusion/sqllogictest/test_files/tpch/tpch.slt
b/datafusion/sqllogictest/test_files/tpch/answers/q17.slt.part
similarity index 69%
copy from datafusion/sqllogictest/test_files/tpch/tpch.slt
copy to datafusion/sqllogictest/test_files/tpch/answers/q17.slt.part
index 9a876acf08..cdf7f1de83 100644
--- a/datafusion/sqllogictest/test_files/tpch/tpch.slt
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q17.slt.part
@@ -1,5 +1,4 @@
-
# 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
@@ -17,6 +16,23 @@
# specific language governing permissions and limitations
# under the License.
-include ./create_tables.slt.part
-include ./q*.slt.part
-include ./drop_tables.slt.part
+query R
+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
+);
+----
+23512.752857142856
diff --git a/datafusion/sqllogictest/test_files/tpch/answers/q18.slt.part
b/datafusion/sqllogictest/test_files/tpch/answers/q18.slt.part
new file mode 100644
index 0000000000..93da210f51
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q18.slt.part
@@ -0,0 +1,57 @@
+
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+query TIIDRR
+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;
+----
+Customer#000001639 1639 502886 1994-04-12 456423.88 312
+Customer#000006655 6655 29158 1995-10-21 452805.02 305
+Customer#000014110 14110 565574 1995-09-24 425099.85 301
+Customer#000001775 1775 6882 1997-04-09 408368.1 303
+Customer#000011459 11459 551136 1993-05-19 386812.74 308
diff --git a/datafusion/sqllogictest/test_files/tpch/answers/q19.slt.part
b/datafusion/sqllogictest/test_files/tpch/answers/q19.slt.part
new file mode 100644
index 0000000000..a8c6a5032d
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q19.slt.part
@@ -0,0 +1,56 @@
+
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+query R
+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'
+ );
+----
+168597.286
diff --git a/datafusion/sqllogictest/test_files/tpch/answers/q2.slt.part
b/datafusion/sqllogictest/test_files/tpch/answers/q2.slt.part
new file mode 100644
index 0000000000..db4966c47b
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q2.slt.part
@@ -0,0 +1,74 @@
+
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+query RTTITTTT
+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 10;
+----
+9828.21 Supplier#000000647 UNITED KINGDOM 13120 Manufacturer#5 x5U7MBZmwfG9
33-258-202-4782 s the slyly even ideas poach fluffily
+9508.37 Supplier#000000070 FRANCE 3563 Manufacturer#1
INWNH2w,OOWgNDq0BRCcBwOMQc6PdFDc4 16-821-608-1166 ests sleep quickly express
ideas. ironic ideas haggle about the final T
+9508.37 Supplier#000000070 FRANCE 17268 Manufacturer#4
INWNH2w,OOWgNDq0BRCcBwOMQc6PdFDc4 16-821-608-1166 ests sleep quickly express
ideas. ironic ideas haggle about the final T
+9453.01 Supplier#000000802 ROMANIA 10021 Manufacturer#5
,6HYXb4uaHITmtMBj4Ak57Pd 29-342-882-6463 gular frets. permanently special
multipliers believe blithely alongs
+9453.01 Supplier#000000802 ROMANIA 13275 Manufacturer#4
,6HYXb4uaHITmtMBj4Ak57Pd 29-342-882-6463 gular frets. permanently special
multipliers believe blithely alongs
+9192.1 Supplier#000000115 UNITED KINGDOM 13325 Manufacturer#1 nJ
2t0f7Ve,wL1,6WzGBJLNBUCKlsV 33-597-248-1220 es across the carefully express
accounts boost caref
+9032.15 Supplier#000000959 GERMANY 4958 Manufacturer#4 8grA EHBnwOZhO
17-108-642-3106 nding dependencies nag furiou
+8702.02 Supplier#000000333 RUSSIA 11810 Manufacturer#3 MaVf
XgwPdkiX4nfJGOis8Uu2zKiIZH 32-508-202-6136 oss the deposits cajole carefully
even pinto beans. regular foxes detect alo
+8615.5 Supplier#000000812 FRANCE 10551 Manufacturer#2
8qh4tezyScl5bidLAysvutB,,ZI2dn6xP 16-585-724-6633 y quickly regular deposits?
quickly pending packages after the caref
+8615.5 Supplier#000000812 FRANCE 13811 Manufacturer#4
8qh4tezyScl5bidLAysvutB,,ZI2dn6xP 16-585-724-6633 y quickly regular deposits?
quickly pending packages after the caref
diff --git a/datafusion/sqllogictest/test_files/tpch/answers/q20.slt.part
b/datafusion/sqllogictest/test_files/tpch/answers/q20.slt.part
new file mode 100644
index 0000000000..85711da7bd
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q20.slt.part
@@ -0,0 +1,66 @@
+
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+query TT
+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;
+----
+Supplier#000000157 ,mEGorBfVIm
+Supplier#000000197 YC2Acon6kjY3zj3Fbxs2k4Vdf7X0cd2F
+Supplier#000000287 7a9SP7qW5Yku5PvSg
+Supplier#000000378 FfbhyCxWvcPrO8ltp9
+Supplier#000000530 0qwCMwobKY OcmLyfRXlagA8ukENJv,
+Supplier#000000555 TfB,a5bfl3Ah 3Z 74GqnNs6zKVGM
+Supplier#000000557 jj0wUYh9K3fG5Jhdhrkuy ,4
+Supplier#000000729 pqck2ppy758TQpZCUAjPvlU55K3QjfL7Bi
+Supplier#000000935 ij98czM 2KzWe7dDTOxB8sq0UfCdvrX
diff --git a/datafusion/sqllogictest/test_files/tpch/answers/q21.slt.part
b/datafusion/sqllogictest/test_files/tpch/answers/q21.slt.part
new file mode 100644
index 0000000000..8d92dae05e
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q21.slt.part
@@ -0,0 +1,106 @@
+
+# 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.
+
+query TI
+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;
+----
+Supplier#000000445 16
+Supplier#000000825 16
+Supplier#000000709 15
+Supplier#000000762 15
+Supplier#000000357 14
+Supplier#000000399 14
+Supplier#000000496 14
+Supplier#000000977 13
+Supplier#000000144 12
+Supplier#000000188 12
+Supplier#000000415 12
+Supplier#000000472 12
+Supplier#000000633 12
+Supplier#000000708 12
+Supplier#000000889 12
+Supplier#000000380 11
+Supplier#000000602 11
+Supplier#000000659 11
+Supplier#000000821 11
+Supplier#000000929 11
+Supplier#000000262 10
+Supplier#000000460 10
+Supplier#000000486 10
+Supplier#000000669 10
+Supplier#000000718 10
+Supplier#000000778 10
+Supplier#000000167 9
+Supplier#000000578 9
+Supplier#000000673 9
+Supplier#000000687 9
+Supplier#000000074 8
+Supplier#000000565 8
+Supplier#000000648 8
+Supplier#000000918 8
+Supplier#000000427 7
+Supplier#000000503 7
+Supplier#000000610 7
+Supplier#000000670 7
+Supplier#000000811 7
+Supplier#000000114 6
+Supplier#000000379 6
+Supplier#000000436 6
+Supplier#000000500 6
+Supplier#000000660 6
+Supplier#000000788 6
+Supplier#000000846 6
+Supplier#000000920 4
diff --git a/datafusion/sqllogictest/test_files/tpch/answers/q22.slt.part
b/datafusion/sqllogictest/test_files/tpch/answers/q22.slt.part
new file mode 100644
index 0000000000..2233826912
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q22.slt.part
@@ -0,0 +1,64 @@
+
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+query TIR
+select
+ cntrycode,
+ count(*) as numcust,
+ sum(c_acctbal) as totacctbal
+from
+ (
+ select
+ substring(c_phone from 1 for 2) as cntrycode,
+ c_acctbal
+ from
+ customer
+ where
+ substring(c_phone from 1 for 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 from 1 for 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;
+----
+13 94 714035.05
+17 96 722560.15
+18 99 738012.52
+23 93 708285.25
+29 85 632693.46
+30 87 646748.02
+31 87 647372.5
diff --git a/datafusion/sqllogictest/test_files/tpch/tpch.slt
b/datafusion/sqllogictest/test_files/tpch/answers/q3.slt.part
similarity index 50%
copy from datafusion/sqllogictest/test_files/tpch/tpch.slt
copy to datafusion/sqllogictest/test_files/tpch/answers/q3.slt.part
index 9a876acf08..1e7fc0a961 100644
--- a/datafusion/sqllogictest/test_files/tpch/tpch.slt
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q3.slt.part
@@ -1,5 +1,4 @@
-
# 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
@@ -17,6 +16,38 @@
# specific language governing permissions and limitations
# under the License.
-include ./create_tables.slt.part
-include ./q*.slt.part
-include ./drop_tables.slt.part
+query IRDI
+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;
+----
+223140 355369.0698 1995-03-14 0
+584291 354494.7318 1995-02-21 0
+405063 353125.4577 1995-03-03 0
+573861 351238.277 1995-03-09 0
+554757 349181.7426 1995-03-14 0
+506021 321075.581 1995-03-10 0
+121604 318576.4154 1995-03-07 0
+108514 314967.0754 1995-02-20 0
+462502 312604.542 1995-03-08 0
+178727 309728.9306 1995-02-25 0
diff --git a/datafusion/sqllogictest/test_files/tpch/tpch.slt
b/datafusion/sqllogictest/test_files/tpch/answers/q4.slt.part
similarity index 61%
copy from datafusion/sqllogictest/test_files/tpch/tpch.slt
copy to datafusion/sqllogictest/test_files/tpch/answers/q4.slt.part
index 9a876acf08..a37e76f970 100644
--- a/datafusion/sqllogictest/test_files/tpch/tpch.slt
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q4.slt.part
@@ -1,5 +1,4 @@
-
# 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
@@ -17,6 +16,31 @@
# specific language governing permissions and limitations
# under the License.
-include ./create_tables.slt.part
-include ./q*.slt.part
-include ./drop_tables.slt.part
+query TI
+select
+ o_orderpriority,
+ count(*) as order_count
+from
+ orders
+where
+ o_orderdate >= '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;
+----
+1-URGENT 999
+2-HIGH 997
+3-MEDIUM 1031
+4-NOT SPECIFIED 989
+5-LOW 1077
diff --git a/datafusion/sqllogictest/test_files/tpch/tpch.slt
b/datafusion/sqllogictest/test_files/tpch/answers/q5.slt.part
similarity index 56%
copy from datafusion/sqllogictest/test_files/tpch/tpch.slt
copy to datafusion/sqllogictest/test_files/tpch/answers/q5.slt.part
index 9a876acf08..75e2fa316f 100644
--- a/datafusion/sqllogictest/test_files/tpch/tpch.slt
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q5.slt.part
@@ -1,5 +1,4 @@
-
# 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
@@ -17,6 +16,34 @@
# specific language governing permissions and limitations
# under the License.
-include ./create_tables.slt.part
-include ./q*.slt.part
-include ./drop_tables.slt.part
+query TR
+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 '1995-01-01'
+group by
+ n_name
+order by
+ revenue desc;
+----
+CHINA 7822103
+INDIA 6376121.5085
+JAPAN 6000077.2184
+INDONESIA 5580475.4027
+VIETNAM 4497840.5466
diff --git a/datafusion/sqllogictest/test_files/tpch/tpch.slt
b/datafusion/sqllogictest/test_files/tpch/answers/q6.slt.part
similarity index 75%
copy from datafusion/sqllogictest/test_files/tpch/tpch.slt
copy to datafusion/sqllogictest/test_files/tpch/answers/q6.slt.part
index 9a876acf08..57967f4d9e 100644
--- a/datafusion/sqllogictest/test_files/tpch/tpch.slt
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q6.slt.part
@@ -1,5 +1,4 @@
-
# 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
@@ -17,6 +16,15 @@
# specific language governing permissions and limitations
# under the License.
-include ./create_tables.slt.part
-include ./q*.slt.part
-include ./drop_tables.slt.part
+query R
+select
+ sum(l_extendedprice * l_discount) as revenue
+from
+ lineitem
+where
+ l_shipdate >= date '1994-01-01'
+ and l_shipdate < date '1995-01-01'
+ and l_discount between 0.06 - 0.01 and 0.06 + 0.01
+ and l_quantity < 24;
+----
+11803420.2534
diff --git a/datafusion/sqllogictest/test_files/tpch/answers/q7.slt.part
b/datafusion/sqllogictest/test_files/tpch/answers/q7.slt.part
new file mode 100644
index 0000000000..31923ffb5f
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q7.slt.part
@@ -0,0 +1,63 @@
+
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+query TTIR
+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;
+----
+FRANCE GERMANY 1995 4637235.1501
+FRANCE GERMANY 1996 5224779.5736
+GERMANY FRANCE 1995 6232818.7037
+GERMANY FRANCE 1996 5557312.1121
diff --git a/datafusion/sqllogictest/test_files/tpch/answers/q8.slt.part
b/datafusion/sqllogictest/test_files/tpch/answers/q8.slt.part
new file mode 100644
index 0000000000..270a7c912a
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q8.slt.part
@@ -0,0 +1,59 @@
+
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+query IR
+select
+ o_year,
+ cast(cast(sum(case
+ when nation = 'BRAZIL' then volume
+ else 0
+ end) as decimal(12,2)) / cast(sum(volume) as decimal(12,2)) as
decimal(15,2)) 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;
+----
+1995 0.03
+1996 0.02
diff --git a/datafusion/sqllogictest/test_files/tpch/answers/q9.slt.part
b/datafusion/sqllogictest/test_files/tpch/answers/q9.slt.part
new file mode 100644
index 0000000000..71ae90119b
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/tpch/answers/q9.slt.part
@@ -0,0 +1,63 @@
+
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+query TIR
+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
+limit 10;
+----
+ALGERIA 1998 2321785.3682
+ALGERIA 1997 3685016.8589
+ALGERIA 1996 4276597.4253
+ALGERIA 1995 4418370.4154
+ALGERIA 1994 3864849.9521
+ALGERIA 1993 3541051.3865
+ALGERIA 1992 4310013.3482
+ARGENTINA 1998 2685983.8005
+ARGENTINA 1997 4242147.8124
+ARGENTINA 1996 3907867.0103
diff --git a/datafusion/sqllogictest/test_files/tpch/q1.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q1.slt.part
similarity index 85%
rename from datafusion/sqllogictest/test_files/tpch/q1.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q1.slt.part
index 4d4323e93e..c60848cb56 100644
--- a/datafusion/sqllogictest/test_files/tpch/q1.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q1.slt.part
@@ -57,32 +57,4 @@ physical_plan
08)--------------ProjectionExec: expr=[l_extendedprice@1 * (Some(1),20,0 -
l_discount@2) as __common_expr_1, l_quantity@0 as l_quantity, l_extendedprice@1
as l_extendedprice, l_discount@2 as l_discount, l_tax@3 as l_tax,
l_returnflag@4 as l_returnflag, l_linestatus@5 as l_linestatus]
09)----------------CoalesceBatchesExec: target_batch_size=8192
10)------------------FilterExec: l_shipdate@6 <= 1998-09-02,
projection=[l_quantity@0, l_extendedprice@1, l_discount@2, l_tax@3,
l_returnflag@4, l_linestatus@5]
-11)--------------------CsvExec: file_groups={4 groups:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:0..18561749],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:18561749..37123498],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:37123498..55685247],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:55685247..74246996]]},
projection=[l_quantity, l_extendedprice, l_discount, l_tax, l [...]
-
-query TTRRRRRRRI
-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-09-02'
-group by
- l_returnflag,
- l_linestatus
-order by
- l_returnflag,
- l_linestatus;
-----
-A F 3774200 5320753880.69 5054096266.6828 5256751331.449234 25.537587
36002.123829 0.050144 147790
-N F 95257 133737795.84 127132372.6512 132286291.229445 25.300664 35521.326916
0.049394 3765
-N O 7459297 10512270008.9 9986238338.3847 10385578376.585467 25.545537
36000.924688 0.050095 292000
-R F 3785523 5337950526.47 5071818532.942 5274405503.049367 25.525943
35994.029214 0.049989 148301
+11)--------------------CsvExec: file_groups={4 groups:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:0..18561749],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:18561749..37123498],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:37123498..55685247],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:55685247..74246996]]},
projection=[l_quantity, l_extendedprice, l_discount, l_tax, l [...]
\ No newline at end of file
diff --git a/datafusion/sqllogictest/test_files/tpch/q10.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q10.slt.part
similarity index 79%
rename from datafusion/sqllogictest/test_files/tpch/q10.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q10.slt.part
index 73593a470c..e4376437ad 100644
--- a/datafusion/sqllogictest/test_files/tpch/q10.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q10.slt.part
@@ -104,50 +104,3 @@ physical_plan
33)--------------------RepartitionExec: partitioning=Hash([n_nationkey@0], 4),
input_partitions=4
34)----------------------RepartitionExec: partitioning=RoundRobinBatch(4),
input_partitions=1
35)------------------------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/nation.tbl]]},
projection=[n_nationkey, n_name], has_header=false
-
-
-
-query ITRRTTTT
-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 '1994-01-01'
- 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 10;
-----
-8242 Customer#000008242 622786.7297 6322.09 ETHIOPIA
P2n4nJhy,UqSo2s43YfSvYJDZ6lk 15-792-676-1184 slyly regular packages haggle
carefully ironic ideas. courts are furiously. furiously unusual theodolites
cajole. i
-7714 Customer#000007714 557400.3053 9799.98 IRAN SnnIGB,SkmnWpX3
20-922-418-6024 arhorses according to the blithely express re
-11032 Customer#000011032 512500.9641 8496.93 UNITED KINGDOM
WIKHC7K3Cn7156iNOyfVG3cZ7YqkgsR,Ly 33-102-772-3533 posits-- furiously ironic
accounts are again
-2455 Customer#000002455 481592.4053 2070.99 GERMANY RVn1ZSRtLqPlJLIZxvpmsbgC02
17-946-225-9977 al asymptotes. finally ironic accounts cajole furiously.
permanently unusual theodolites aro
-12106 Customer#000012106 479414.2133 5342.11 UNITED STATES wth3twOmu6vy
34-905-346-4472 ly after the blithely regular foxes. accounts haggle carefully
alongside of the blithely even ideas.
-8530 Customer#000008530 457855.9467 9734.95 MOROCCO GMQyte94oDM7eD7exnkj
4hH9yq3 25-736-932-5850 slyly asymptotes. quickly final deposits in
-13984 Customer#000013984 446316.5104 3482.28 IRAN qZXwuapCHvxbX
20-981-264-2952 y unusual courts could wake furiously
-1966 Customer#000001966 444059.0382 1937.72 ALGERIA jPv1
UHra5JLALR5Isci5u0636RoAu7t vH 10-973-269-8886 the blithely even accounts.
final deposits cajole around the blithely final packages.
-11026 Customer#000011026 417913.4142 7738.76 ALGERIA XorIktoJOAEJkpNNMx
10-184-163-4632 ly even dolphins eat along the blithely even instructions.
express attainments cajole slyly. busy dolphins in
-8501 Customer#000008501 412797.51 6906.7 ARGENTINA 776af4rOa mZ66hczs
11-317-552-5840 y final deposits after the fluffily even accounts are slyly
final, regular
diff --git a/datafusion/sqllogictest/test_files/tpch/q11.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q11.slt.part
similarity index 93%
rename from datafusion/sqllogictest/test_files/tpch/q11.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q11.slt.part
index adaf391de0..80f9919cb4 100644
--- a/datafusion/sqllogictest/test_files/tpch/q11.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q11.slt.part
@@ -124,45 +124,3 @@ physical_plan
49)--------------------------RepartitionExec: partitioning=RoundRobinBatch(4),
input_partitions=1
50)----------------------------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/nation.tbl]]},
projection=[n_nationkey, n_name], has_header=false
-
-
-query IR
-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.0001
- from
- partsupp,
- supplier,
- nation
- where
- ps_suppkey = s_suppkey
- and s_nationkey = n_nationkey
- and n_name = 'GERMANY'
- )
-order by
- value desc
-limit 10;
-----
-12098 16227681.21
-5134 15709338.52
-13334 15023662.41
-17052 14351644.2
-3452 14070870.14
-12552 13332469.18
-1084 13170428.29
-5797 13038622.72
-12633 12892561.61
-403 12856217.34
diff --git a/datafusion/sqllogictest/test_files/tpch/q12.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q12.slt.part
similarity index 89%
rename from datafusion/sqllogictest/test_files/tpch/q12.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q12.slt.part
index b0d0baba90..34e5dd69f4 100644
--- a/datafusion/sqllogictest/test_files/tpch/q12.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q12.slt.part
@@ -76,40 +76,3 @@ physical_plan
15)------------------CoalesceBatchesExec: target_batch_size=8192
16)--------------------RepartitionExec: partitioning=Hash([o_orderkey@0], 4),
input_partitions=4
17)----------------------CsvExec: file_groups={4 groups:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/orders.tbl:0..4223281],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/orders.tbl:4223281..8446562],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/orders.tbl:8446562..12669843],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/orders.tbl:12669843..16893122]]},
projection=[o_orderkey, o_orderpriority], has_header=false
-
-
-
-query TII
-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
- lineitem
- join
- orders
- on
- l_orderkey = o_orderkey
-where
- 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 '1995-01-01'
-group by
- l_shipmode
-order by
- l_shipmode;
-----
-MAIL 647 945
-SHIP 620 943
diff --git a/datafusion/sqllogictest/test_files/tpch/q13.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q13.slt.part
similarity index 89%
rename from datafusion/sqllogictest/test_files/tpch/q13.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q13.slt.part
index 2a9fb12a31..2667bb23a4 100644
--- a/datafusion/sqllogictest/test_files/tpch/q13.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q13.slt.part
@@ -73,39 +73,3 @@ physical_plan
18)--------------------------CoalesceBatchesExec: target_batch_size=8192
19)----------------------------FilterExec: o_comment@2 NOT LIKE
%special%requests%, projection=[o_orderkey@0, o_custkey@1]
20)------------------------------CsvExec: file_groups={4 groups:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/orders.tbl:0..4223281],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/orders.tbl:4223281..8446562],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/orders.tbl:8446562..12669843],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/orders.tbl:12669843..16893122]]},
projection=[o_orderkey, o_custkey, o_comment], has_header=false
-
-
-
-query II
-select
- c_count,
- count(*) as custdist
-from
- (
- select
- c_custkey,
- count(o_orderkey)
- 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 (c_custkey, c_count)
-group by
- c_count
-order by
- custdist desc,
- c_count desc
-limit 10;
-----
-0 5000
-10 665
-9 657
-11 621
-12 567
-8 564
-13 492
-18 482
-7 480
-20 456
diff --git a/datafusion/sqllogictest/test_files/tpch/q14.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q14.slt.part
similarity index 91%
rename from datafusion/sqllogictest/test_files/tpch/q14.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q14.slt.part
index eee10cb3f8..96cfbb02d0 100644
--- a/datafusion/sqllogictest/test_files/tpch/q14.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q14.slt.part
@@ -57,22 +57,3 @@ physical_plan
14)----------------RepartitionExec: partitioning=Hash([p_partkey@0], 4),
input_partitions=4
15)------------------RepartitionExec: partitioning=RoundRobinBatch(4),
input_partitions=1
16)--------------------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/part.tbl]]},
projection=[p_partkey, p_type], has_header=false
-
-
-
-query R
-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-10-01';
-----
-16.283855689006
diff --git a/datafusion/sqllogictest/test_files/tpch/q15.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q15.slt.part
similarity index 93%
rename from datafusion/sqllogictest/test_files/tpch/q15.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q15.slt.part
index 2374fd8430..4cf4c722ea 100644
--- a/datafusion/sqllogictest/test_files/tpch/q15.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q15.slt.part
@@ -104,38 +104,3 @@ physical_plan
31)----------------------------CoalesceBatchesExec: target_batch_size=8192
32)------------------------------FilterExec: l_shipdate@3 >= 1996-01-01 AND
l_shipdate@3 < 1996-04-01, projection=[l_suppkey@0, l_extendedprice@1,
l_discount@2]
33)--------------------------------CsvExec: file_groups={4 groups:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:0..18561749],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:18561749..37123498],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:37123498..55685247],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:55685247..74246996]]},
projection=[l_suppkey, l_extendedprice, l_discoun [...]
-
-query ITTTR
-with 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
-)
-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;
-----
-677 Supplier#000000677 8mhrffG7D2WJBSQbOGstQ 23-290-639-3315 1614410.2928
diff --git a/datafusion/sqllogictest/test_files/tpch/q16.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q16.slt.part
similarity index 88%
rename from datafusion/sqllogictest/test_files/tpch/q16.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q16.slt.part
index 6b2c2f7fdc..e9486c39f0 100644
--- a/datafusion/sqllogictest/test_files/tpch/q16.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q16.slt.part
@@ -98,48 +98,3 @@ physical_plan
31)----------------------------------RepartitionExec:
partitioning=RoundRobinBatch(4), input_partitions=1
32)------------------------------------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/supplier.tbl]]},
projection=[s_suppkey, s_comment], has_header=false
-
-
-query TTII
-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
-limit 10;
-----
-Brand#14 SMALL ANODIZED NICKEL 45 12
-Brand#22 SMALL BURNISHED BRASS 19 12
-Brand#25 PROMO POLISHED COPPER 14 12
-Brand#35 LARGE ANODIZED STEEL 45 12
-Brand#35 PROMO BRUSHED COPPER 9 12
-Brand#51 ECONOMY ANODIZED STEEL 9 12
-Brand#53 LARGE BRUSHED NICKEL 45 12
-Brand#11 ECONOMY POLISHED COPPER 14 8
-Brand#11 LARGE PLATED STEEL 23 8
-Brand#11 PROMO POLISHED STEEL 23 8
diff --git a/datafusion/sqllogictest/test_files/tpch/q17.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q17.slt.part
similarity index 94%
rename from datafusion/sqllogictest/test_files/tpch/q17.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q17.slt.part
index 058bcb5f49..f28a5ef548 100644
--- a/datafusion/sqllogictest/test_files/tpch/q17.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q17.slt.part
@@ -75,25 +75,3 @@ physical_plan
22)--------------------AggregateExec: mode=Partial, gby=[l_partkey@0 as
l_partkey], aggr=[avg(lineitem.l_quantity)]
23)----------------------CsvExec: file_groups={4 groups:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:0..18561749],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:18561749..37123498],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:37123498..55685247],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:55685247..74246996]]},
projection=[l_partkey, l_quantity], has_header=false
-
-
-query R
-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
-);
-----
-23512.752857142856
diff --git a/datafusion/sqllogictest/test_files/tpch/q18.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q18.slt.part
similarity index 89%
rename from datafusion/sqllogictest/test_files/tpch/q18.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q18.slt.part
index c80352c5d3..283de4dddf 100644
--- a/datafusion/sqllogictest/test_files/tpch/q18.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q18.slt.part
@@ -98,45 +98,3 @@ physical_plan
29)------------------------RepartitionExec: partitioning=Hash([l_orderkey@0],
4), input_partitions=4
30)--------------------------AggregateExec: mode=Partial, gby=[l_orderkey@0 as
l_orderkey], aggr=[sum(lineitem.l_quantity)]
31)----------------------------CsvExec: file_groups={4 groups:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:0..18561749],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:18561749..37123498],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:37123498..55685247],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:55685247..74246996]]},
projection=[l_orderkey, l_quantity], has_header=false
-
-
-
-query TIIDRR
-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;
-----
-Customer#000001639 1639 502886 1994-04-12 456423.88 312
-Customer#000006655 6655 29158 1995-10-21 452805.02 305
-Customer#000014110 14110 565574 1995-09-24 425099.85 301
-Customer#000001775 1775 6882 1997-04-09 408368.1 303
-Customer#000011459 11459 551136 1993-05-19 386812.74 308
diff --git a/datafusion/sqllogictest/test_files/tpch/q19.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q19.slt.part
similarity index 88%
rename from datafusion/sqllogictest/test_files/tpch/q19.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q19.slt.part
index 70465ea065..9eb1f86382 100644
--- a/datafusion/sqllogictest/test_files/tpch/q19.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q19.slt.part
@@ -81,44 +81,3 @@ physical_plan
15)------------------FilterExec: (p_brand@1 = Brand#12 AND Use p_container@3
IN (SET) ([Literal { value: Utf8("SM CASE") }, Literal { value: Utf8("SM BOX")
}, Literal { value: Utf8("SM PACK") }, Literal { value: Utf8("SM PKG") }]) AND
p_size@2 <= 5 OR p_brand@1 = Brand#23 AND Use p_container@3 IN (SET) ([Literal
{ value: Utf8("MED BAG") }, Literal { value: Utf8("MED BOX") }, Literal {
value: Utf8("MED PKG") }, Literal { value: Utf8("MED PACK") }]) AND p_size@2 <=
10 OR p_brand@1 = Brand# [...]
16)--------------------RepartitionExec: partitioning=RoundRobinBatch(4),
input_partitions=1
17)----------------------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/part.tbl]]},
projection=[p_partkey, p_brand, p_size, p_container], has_header=false
-
-
-
-query R
-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'
- );
-----
-168597.286
diff --git a/datafusion/sqllogictest/test_files/tpch/q2.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q2.slt.part
similarity index 86%
rename from datafusion/sqllogictest/test_files/tpch/q2.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q2.slt.part
index 23ffa0d226..8fdbb6884d 100644
--- a/datafusion/sqllogictest/test_files/tpch/q2.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q2.slt.part
@@ -178,62 +178,3 @@ physical_plan
77)----------------------------------FilterExec: r_name@1 = EUROPE,
projection=[r_regionkey@0]
78)------------------------------------RepartitionExec:
partitioning=RoundRobinBatch(4), input_partitions=1
79)--------------------------------------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/region.tbl]]},
projection=[r_regionkey, r_name], has_header=false
-
-
-
-query RTTITTTT
-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 10;
-----
-9828.21 Supplier#000000647 UNITED KINGDOM 13120 Manufacturer#5 x5U7MBZmwfG9
33-258-202-4782 s the slyly even ideas poach fluffily
-9508.37 Supplier#000000070 FRANCE 3563 Manufacturer#1
INWNH2w,OOWgNDq0BRCcBwOMQc6PdFDc4 16-821-608-1166 ests sleep quickly express
ideas. ironic ideas haggle about the final T
-9508.37 Supplier#000000070 FRANCE 17268 Manufacturer#4
INWNH2w,OOWgNDq0BRCcBwOMQc6PdFDc4 16-821-608-1166 ests sleep quickly express
ideas. ironic ideas haggle about the final T
-9453.01 Supplier#000000802 ROMANIA 10021 Manufacturer#5
,6HYXb4uaHITmtMBj4Ak57Pd 29-342-882-6463 gular frets. permanently special
multipliers believe blithely alongs
-9453.01 Supplier#000000802 ROMANIA 13275 Manufacturer#4
,6HYXb4uaHITmtMBj4Ak57Pd 29-342-882-6463 gular frets. permanently special
multipliers believe blithely alongs
-9192.1 Supplier#000000115 UNITED KINGDOM 13325 Manufacturer#1 nJ
2t0f7Ve,wL1,6WzGBJLNBUCKlsV 33-597-248-1220 es across the carefully express
accounts boost caref
-9032.15 Supplier#000000959 GERMANY 4958 Manufacturer#4 8grA EHBnwOZhO
17-108-642-3106 nding dependencies nag furiou
-8702.02 Supplier#000000333 RUSSIA 11810 Manufacturer#3 MaVf
XgwPdkiX4nfJGOis8Uu2zKiIZH 32-508-202-6136 oss the deposits cajole carefully
even pinto beans. regular foxes detect alo
-8615.5 Supplier#000000812 FRANCE 10551 Manufacturer#2
8qh4tezyScl5bidLAysvutB,,ZI2dn6xP 16-585-724-6633 y quickly regular deposits?
quickly pending packages after the caref
-8615.5 Supplier#000000812 FRANCE 13811 Manufacturer#4
8qh4tezyScl5bidLAysvutB,,ZI2dn6xP 16-585-724-6633 y quickly regular deposits?
quickly pending packages after the caref
diff --git a/datafusion/sqllogictest/test_files/tpch/q20.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q20.slt.part
similarity index 87%
rename from datafusion/sqllogictest/test_files/tpch/q20.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q20.slt.part
index 177e38e51c..e3918ba62b 100644
--- a/datafusion/sqllogictest/test_files/tpch/q20.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q20.slt.part
@@ -125,53 +125,3 @@ physical_plan
42)----------------------------FilterExec: l_shipdate@3 >= 1994-01-01 AND
l_shipdate@3 < 1995-01-01, projection=[l_partkey@0, l_suppkey@1, l_quantity@2]
43)------------------------------CsvExec: file_groups={4 groups:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:0..18561749],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:18561749..37123498],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:37123498..55685247],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:55685247..74246996]]},
projection=[l_partkey, l_suppkey, l_quantity, l_shi [...]
-
-
-query TT
-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;
-----
-Supplier#000000157 ,mEGorBfVIm
-Supplier#000000197 YC2Acon6kjY3zj3Fbxs2k4Vdf7X0cd2F
-Supplier#000000287 7a9SP7qW5Yku5PvSg
-Supplier#000000378 FfbhyCxWvcPrO8ltp9
-Supplier#000000530 0qwCMwobKY OcmLyfRXlagA8ukENJv,
-Supplier#000000555 TfB,a5bfl3Ah 3Z 74GqnNs6zKVGM
-Supplier#000000557 jj0wUYh9K3fG5Jhdhrkuy ,4
-Supplier#000000729 pqck2ppy758TQpZCUAjPvlU55K3QjfL7Bi
-Supplier#000000935 ij98czM 2KzWe7dDTOxB8sq0UfCdvrX
diff --git a/datafusion/sqllogictest/test_files/tpch/q21.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q21.slt.part
similarity index 85%
rename from datafusion/sqllogictest/test_files/tpch/q21.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q21.slt.part
index 93dcd4c680..939e01b178 100644
--- a/datafusion/sqllogictest/test_files/tpch/q21.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q21.slt.part
@@ -141,94 +141,3 @@ physical_plan
49)----------------------CoalesceBatchesExec: target_batch_size=8192
50)------------------------FilterExec: l_receiptdate@3 > l_commitdate@2,
projection=[l_orderkey@0, l_suppkey@1]
51)--------------------------CsvExec: file_groups={4 groups:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:0..18561749],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:18561749..37123498],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:37123498..55685247],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:55685247..74246996]]},
projection=[l_orderkey, l_suppkey, l_commitdate, l_rece [...]
-
-
-
-query TI
-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;
-----
-Supplier#000000445 16
-Supplier#000000825 16
-Supplier#000000709 15
-Supplier#000000762 15
-Supplier#000000357 14
-Supplier#000000399 14
-Supplier#000000496 14
-Supplier#000000977 13
-Supplier#000000144 12
-Supplier#000000188 12
-Supplier#000000415 12
-Supplier#000000472 12
-Supplier#000000633 12
-Supplier#000000708 12
-Supplier#000000889 12
-Supplier#000000380 11
-Supplier#000000602 11
-Supplier#000000659 11
-Supplier#000000821 11
-Supplier#000000929 11
-Supplier#000000262 10
-Supplier#000000460 10
-Supplier#000000486 10
-Supplier#000000669 10
-Supplier#000000718 10
-Supplier#000000778 10
-Supplier#000000167 9
-Supplier#000000578 9
-Supplier#000000673 9
-Supplier#000000687 9
-Supplier#000000074 8
-Supplier#000000565 8
-Supplier#000000648 8
-Supplier#000000918 8
-Supplier#000000427 7
-Supplier#000000503 7
-Supplier#000000610 7
-Supplier#000000670 7
-Supplier#000000811 7
-Supplier#000000114 6
-Supplier#000000379 6
-Supplier#000000436 6
-Supplier#000000500 6
-Supplier#000000660 6
-Supplier#000000788 6
-Supplier#000000846 6
-Supplier#000000920 4
diff --git a/datafusion/sqllogictest/test_files/tpch/q22.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q22.slt.part
similarity index 87%
rename from datafusion/sqllogictest/test_files/tpch/q22.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q22.slt.part
index 2955748160..a55d1e8fdf 100644
--- a/datafusion/sqllogictest/test_files/tpch/q22.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q22.slt.part
@@ -103,51 +103,3 @@ physical_plan
27)----------------------------FilterExec: c_acctbal@1 > Some(0),15,2 AND Use
substr(c_phone@0, 1, 2) IN (SET) ([Literal { value: Utf8("13") }, Literal {
value: Utf8("31") }, Literal { value: Utf8("23") }, Literal { value: Utf8("29")
}, Literal { value: Utf8("30") }, Literal { value: Utf8("18") }, Literal {
value: Utf8("17") }]), projection=[c_acctbal@1]
28)------------------------------RepartitionExec:
partitioning=RoundRobinBatch(4), input_partitions=1
29)--------------------------------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/customer.tbl]]},
projection=[c_phone, c_acctbal], has_header=false
-
-
-query TIR
-select
- cntrycode,
- count(*) as numcust,
- sum(c_acctbal) as totacctbal
-from
- (
- select
- substring(c_phone from 1 for 2) as cntrycode,
- c_acctbal
- from
- customer
- where
- substring(c_phone from 1 for 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 from 1 for 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;
-----
-13 94 714035.05
-17 96 722560.15
-18 99 738012.52
-23 93 708285.25
-29 85 632693.46
-30 87 646748.02
-31 87 647372.5
diff --git a/datafusion/sqllogictest/test_files/tpch/q3.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q3.slt.part
similarity index 89%
rename from datafusion/sqllogictest/test_files/tpch/q3.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q3.slt.part
index 289e9c7732..0f35239bfd 100644
--- a/datafusion/sqllogictest/test_files/tpch/q3.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q3.slt.part
@@ -87,41 +87,3 @@ physical_plan
27)----------------------CoalesceBatchesExec: target_batch_size=8192
28)------------------------FilterExec: l_shipdate@3 > 1995-03-15,
projection=[l_orderkey@0, l_extendedprice@1, l_discount@2]
29)--------------------------CsvExec: file_groups={4 groups:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:0..18561749],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:18561749..37123498],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:37123498..55685247],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:55685247..74246996]]},
projection=[l_orderkey, l_extendedprice, l_discount, l_ [...]
-
-
-
-query IRDI
-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;
-----
-223140 355369.0698 1995-03-14 0
-584291 354494.7318 1995-02-21 0
-405063 353125.4577 1995-03-03 0
-573861 351238.277 1995-03-09 0
-554757 349181.7426 1995-03-14 0
-506021 321075.581 1995-03-10 0
-121604 318576.4154 1995-03-07 0
-108514 314967.0754 1995-02-20 0
-462502 312604.542 1995-03-08 0
-178727 309728.9306 1995-02-25 0
diff --git a/datafusion/sqllogictest/test_files/tpch/q4.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q4.slt.part
similarity index 90%
rename from datafusion/sqllogictest/test_files/tpch/q4.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q4.slt.part
index a68b745c36..693d37a5b5 100644
--- a/datafusion/sqllogictest/test_files/tpch/q4.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q4.slt.part
@@ -72,34 +72,3 @@ physical_plan
17)----------------------CoalesceBatchesExec: target_batch_size=8192
18)------------------------FilterExec: l_receiptdate@2 > l_commitdate@1,
projection=[l_orderkey@0]
19)--------------------------CsvExec: file_groups={4 groups:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:0..18561749],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:18561749..37123498],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:37123498..55685247],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:55685247..74246996]]},
projection=[l_orderkey, l_commitdate, l_receiptdate], h [...]
-
-
-
-query TI
-select
- o_orderpriority,
- count(*) as order_count
-from
- orders
-where
- o_orderdate >= '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;
-----
-1-URGENT 999
-2-HIGH 997
-3-MEDIUM 1031
-4-NOT SPECIFIED 989
-5-LOW 1077
diff --git a/datafusion/sqllogictest/test_files/tpch/q5.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q5.slt.part
similarity index 94%
rename from datafusion/sqllogictest/test_files/tpch/q5.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q5.slt.part
index e59daf4943..0c03e0498d 100644
--- a/datafusion/sqllogictest/test_files/tpch/q5.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q5.slt.part
@@ -118,37 +118,3 @@ physical_plan
49)------------------------FilterExec: r_name@1 = ASIA,
projection=[r_regionkey@0]
50)--------------------------RepartitionExec: partitioning=RoundRobinBatch(4),
input_partitions=1
51)----------------------------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/region.tbl]]},
projection=[r_regionkey, r_name], has_header=false
-
-
-
-query TR
-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 '1995-01-01'
-group by
- n_name
-order by
- revenue desc;
-----
-CHINA 7822103
-INDIA 6376121.5085
-JAPAN 6000077.2184
-INDONESIA 5580475.4027
-VIETNAM 4497840.5466
diff --git a/datafusion/sqllogictest/test_files/tpch/q6.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q6.slt.part
similarity index 92%
rename from datafusion/sqllogictest/test_files/tpch/q6.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q6.slt.part
index 548d26972f..6c818a1e2f 100644
--- a/datafusion/sqllogictest/test_files/tpch/q6.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q6.slt.part
@@ -41,18 +41,3 @@ physical_plan
05)--------CoalesceBatchesExec: target_batch_size=8192
06)----------FilterExec: l_shipdate@3 >= 1994-01-01 AND l_shipdate@3 <
1995-01-01 AND l_discount@2 >= Some(5),15,2 AND l_discount@2 <= Some(7),15,2
AND l_quantity@0 < Some(2400),15,2, projection=[l_extendedprice@1, l_discount@2]
07)------------CsvExec: file_groups={4 groups:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:0..18561749],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:18561749..37123498],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:37123498..55685247],
[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/lineitem.tbl:55685247..74246996]]},
projection=[l_quantity, l_extendedprice, l_discount, l_shipdate], has [...]
-
-
-
-query R
-select
- sum(l_extendedprice * l_discount) as revenue
-from
- lineitem
-where
- l_shipdate >= date '1994-01-01'
- and l_shipdate < date '1995-01-01'
- and l_discount between 0.06 - 0.01 and 0.06 + 0.01
- and l_quantity < 24;
-----
-11803420.2534
diff --git a/datafusion/sqllogictest/test_files/tpch/q7.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q7.slt.part
similarity index 91%
rename from datafusion/sqllogictest/test_files/tpch/q7.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q7.slt.part
index 92ce48c286..023af0ea9f 100644
--- a/datafusion/sqllogictest/test_files/tpch/q7.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q7.slt.part
@@ -138,51 +138,3 @@ physical_plan
52)--------------------------FilterExec: n_name@1 = GERMANY OR n_name@1 =
FRANCE
53)----------------------------RepartitionExec:
partitioning=RoundRobinBatch(4), input_partitions=1
54)------------------------------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/nation.tbl]]},
projection=[n_nationkey, n_name], has_header=false
-
-
-
-query TTIR
-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;
-----
-FRANCE GERMANY 1995 4637235.1501
-FRANCE GERMANY 1996 5224779.5736
-GERMANY FRANCE 1995 6232818.7037
-GERMANY FRANCE 1996 5557312.1121
diff --git a/datafusion/sqllogictest/test_files/tpch/q8.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q8.slt.part
similarity index 93%
rename from datafusion/sqllogictest/test_files/tpch/q8.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q8.slt.part
index 225836a4b4..2bcab40dc9 100644
--- a/datafusion/sqllogictest/test_files/tpch/q8.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q8.slt.part
@@ -160,47 +160,3 @@ physical_plan
68)--------------------------FilterExec: r_name@1 = AMERICA,
projection=[r_regionkey@0]
69)----------------------------RepartitionExec:
partitioning=RoundRobinBatch(4), input_partitions=1
70)------------------------------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/region.tbl]]},
projection=[r_regionkey, r_name], has_header=false
-
-
-
-query IR
-select
- o_year,
- cast(cast(sum(case
- when nation = 'BRAZIL' then volume
- else 0
- end) as decimal(12,2)) / cast(sum(volume) as decimal(12,2)) as
decimal(15,2)) 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;
-----
-1995 0.03
-1996 0.02
diff --git a/datafusion/sqllogictest/test_files/tpch/q9.slt.part
b/datafusion/sqllogictest/test_files/tpch/plans/q9.slt.part
similarity index 91%
rename from datafusion/sqllogictest/test_files/tpch/q9.slt.part
rename to datafusion/sqllogictest/test_files/tpch/plans/q9.slt.part
index 8cde946db8..4a288893da 100644
--- a/datafusion/sqllogictest/test_files/tpch/q9.slt.part
+++ b/datafusion/sqllogictest/test_files/tpch/plans/q9.slt.part
@@ -124,51 +124,3 @@ physical_plan
47)----------------------RepartitionExec: partitioning=Hash([n_nationkey@0],
4), input_partitions=4
48)------------------------RepartitionExec: partitioning=RoundRobinBatch(4),
input_partitions=1
49)--------------------------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/tpch/data/nation.tbl]]},
projection=[n_nationkey, n_name], has_header=false
-
-
-
-query TIR
-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
-limit 10;
-----
-ALGERIA 1998 2321785.3682
-ALGERIA 1997 3685016.8589
-ALGERIA 1996 4276597.4253
-ALGERIA 1995 4418370.4154
-ALGERIA 1994 3864849.9521
-ALGERIA 1993 3541051.3865
-ALGERIA 1992 4310013.3482
-ARGENTINA 1998 2685983.8005
-ARGENTINA 1997 4242147.8124
-ARGENTINA 1996 3907867.0103
diff --git a/datafusion/sqllogictest/test_files/tpch/tpch.slt
b/datafusion/sqllogictest/test_files/tpch/tpch.slt
index 9a876acf08..127152daa5 100644
--- a/datafusion/sqllogictest/test_files/tpch/tpch.slt
+++ b/datafusion/sqllogictest/test_files/tpch/tpch.slt
@@ -18,5 +18,14 @@
# under the License.
include ./create_tables.slt.part
-include ./q*.slt.part
+include ./plans/q*.slt.part
+include ./answers/q*.slt.part
+
+# test answers with sort merge join
+statement ok
+set datafusion.optimizer.prefer_hash_join = false;
+
+include ./answers/q*.slt.part
+
include ./drop_tables.slt.part
+
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
