You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@kyuubi.apache.org by ch...@apache.org on 2022/06/09 08:09:47 UTC
[incubator-kyuubi] branch master updated: [KYUUBI #2805] Add TPC-H queries verification
This is an automated email from the ASF dual-hosted git repository.
chengpan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-kyuubi.git
The following commit(s) were added to refs/heads/master by this push:
new 032d9ca78 [KYUUBI #2805] Add TPC-H queries verification
032d9ca78 is described below
commit 032d9ca78a5daf765776e39355f8ba30c8b49d39
Author: jiaoqingbo <11...@qq.com>
AuthorDate: Thu Jun 9 16:09:37 2022 +0800
[KYUUBI #2805] Add TPC-H queries verification
### _Why are the changes needed?_
fix #2805
### _How was this patch tested?_
- [x] Add some test cases that check the changes thoroughly including negative and positive cases if possible
- [ ] Add screenshots for manual tests if appropriate
- [x] [Run test](https://kyuubi.apache.org/docs/latest/develop_tools/testing.html#running-tests) locally before make a pull request
Closes #2806 from jiaoqingbo/kyuubi-2805.
Closes #2805
1e573503 [jiaoqingbo] code review
957f318b [jiaoqingbo] code review
a5018f9f [jiaoqingbo] add TpchTest tag to pom and yml
ff091ce2 [jiaoqingbo] [KYUUBI #2805] add TPC-H queries verification
Authored-by: jiaoqingbo <11...@qq.com>
Signed-off-by: Cheng Pan <ch...@apache.org>
---
.../main/resources/tpch/schema/q1.output.schema | 1 +
.../main/resources/tpch/schema/q10.output.schema | 1 +
.../main/resources/tpch/schema/q11.output.schema | 1 +
.../main/resources/tpch/schema/q12.output.schema | 1 +
.../main/resources/tpch/schema/q13.output.schema | 1 +
.../main/resources/tpch/schema/q14.output.schema | 1 +
.../main/resources/tpch/schema/q15.output.schema | 1 +
.../main/resources/tpch/schema/q16.output.schema | 1 +
.../main/resources/tpch/schema/q17.output.schema | 1 +
.../main/resources/tpch/schema/q18.output.schema | 1 +
.../main/resources/tpch/schema/q19.output.schema | 1 +
.../main/resources/tpch/schema/q2.output.schema | 1 +
.../main/resources/tpch/schema/q20.output.schema | 1 +
.../main/resources/tpch/schema/q21.output.schema | 1 +
.../main/resources/tpch/schema/q22.output.schema | 1 +
.../main/resources/tpch/schema/q3.output.schema | 1 +
.../main/resources/tpch/schema/q4.output.schema | 1 +
.../main/resources/tpch/schema/q5.output.schema | 1 +
.../main/resources/tpch/schema/q6.output.schema | 1 +
.../main/resources/tpch/schema/q7.output.schema | 1 +
.../main/resources/tpch/schema/q8.output.schema | 1 +
.../main/resources/tpch/schema/q9.output.schema | 1 +
.../src/main/resources/tpch/sql/q1.sql | 39 ++++++
.../src/main/resources/tpch/sql/q10.sql | 50 ++++++++
.../src/main/resources/tpch/sql/q11.sql | 45 +++++++
.../src/main/resources/tpch/sql/q12.sql | 46 +++++++
.../src/main/resources/tpch/sql/q13.sql | 38 ++++++
.../src/main/resources/tpch/sql/q14.sql | 31 +++++
.../src/main/resources/tpch/sql/q15.sql | 51 ++++++++
.../src/main/resources/tpch/sql/q16.sql | 48 ++++++++
.../src/main/resources/tpch/sql/q17.sql | 35 ++++++
.../src/main/resources/tpch/sql/q18.sql | 51 ++++++++
.../src/main/resources/tpch/sql/q19.sql | 53 ++++++++
.../src/main/resources/tpch/sql/q2.sql | 62 ++++++++++
.../src/main/resources/tpch/sql/q20.sql | 55 +++++++++
.../src/main/resources/tpch/sql/q21.sql | 58 +++++++++
.../src/main/resources/tpch/sql/q22.sql | 55 +++++++++
.../src/main/resources/tpch/sql/q3.sql | 41 +++++++
.../src/main/resources/tpch/sql/q4.sql | 39 ++++++
.../src/main/resources/tpch/sql/q5.sql | 42 +++++++
.../src/main/resources/tpch/sql/q6.sql | 27 +++++
.../src/main/resources/tpch/sql/q7.sql | 57 +++++++++
.../src/main/resources/tpch/sql/q8.sql | 55 +++++++++
.../src/main/resources/tpch/sql/q9.sql | 50 ++++++++
.../spark/connector/tpch/TPCHQuerySuite.scala | 133 +++++++++++++++++++++
45 files changed, 1183 insertions(+)
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q1.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q1.output.schema
new file mode 100644
index 000000000..24608b4f2
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q1.output.schema
@@ -0,0 +1 @@
+`l_returnflag` STRING,`l_linestatus` STRING,`sum_qty` DOUBLE,`sum_base_price` DOUBLE,`sum_disc_price` DOUBLE,`sum_charge` DOUBLE,`avg_qty` DOUBLE,`avg_price` DOUBLE,`avg_disc` DOUBLE,`count_order` BIGINT NOT NULL
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q10.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q10.output.schema
new file mode 100644
index 000000000..775a810f8
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q10.output.schema
@@ -0,0 +1 @@
+`c_custkey` BIGINT,`c_name` STRING,`revenue` DOUBLE,`c_acctbal` DOUBLE,`n_name` STRING,`c_address` STRING,`c_phone` STRING,`c_comment` STRING
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q11.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q11.output.schema
new file mode 100644
index 000000000..a673a5f03
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q11.output.schema
@@ -0,0 +1 @@
+`ps_partkey` BIGINT,`value` DOUBLE
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q12.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q12.output.schema
new file mode 100644
index 000000000..8a707380c
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q12.output.schema
@@ -0,0 +1 @@
+`l_shipmode` STRING,`high_line_count` BIGINT,`low_line_count` BIGINT
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q13.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q13.output.schema
new file mode 100644
index 000000000..0a3977b4a
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q13.output.schema
@@ -0,0 +1 @@
+`c_count` BIGINT NOT NULL,`custdist` BIGINT NOT NULL
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q14.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q14.output.schema
new file mode 100644
index 000000000..cb537ace8
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q14.output.schema
@@ -0,0 +1 @@
+`promo_revenue` DOUBLE
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q15.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q15.output.schema
new file mode 100644
index 000000000..67d8198c8
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q15.output.schema
@@ -0,0 +1 @@
+`s_suppkey` BIGINT,`s_name` STRING,`s_address` STRING,`s_phone` STRING,`total_revenue` DOUBLE
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q16.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q16.output.schema
new file mode 100644
index 000000000..7bf75e6f9
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q16.output.schema
@@ -0,0 +1 @@
+`p_brand` STRING,`p_type` STRING,`p_size` INT,`supplier_cnt` BIGINT NOT NULL
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q17.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q17.output.schema
new file mode 100644
index 000000000..0487c4a9a
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q17.output.schema
@@ -0,0 +1 @@
+`avg_yearly` DOUBLE
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q18.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q18.output.schema
new file mode 100644
index 000000000..b7fb427bb
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q18.output.schema
@@ -0,0 +1 @@
+`c_name` STRING,`c_custkey` BIGINT,`o_orderkey` BIGINT,`o_orderdate` DATE,`o_totalprice` DOUBLE,`sum(l_quantity)` DOUBLE
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q19.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q19.output.schema
new file mode 100644
index 000000000..ce7e68717
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q19.output.schema
@@ -0,0 +1 @@
+`revenue` DOUBLE
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q2.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q2.output.schema
new file mode 100644
index 000000000..becafef97
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q2.output.schema
@@ -0,0 +1 @@
+`s_acctbal` DOUBLE,`s_name` STRING,`n_name` STRING,`p_partkey` BIGINT,`p_mfgr` STRING,`s_address` STRING,`s_phone` STRING,`s_comment` STRING
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q20.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q20.output.schema
new file mode 100644
index 000000000..89436ef74
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q20.output.schema
@@ -0,0 +1 @@
+`s_name` STRING,`s_address` STRING
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q21.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q21.output.schema
new file mode 100644
index 000000000..fe1ee67db
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q21.output.schema
@@ -0,0 +1 @@
+`s_name` STRING,`numwait` BIGINT NOT NULL
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q22.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q22.output.schema
new file mode 100644
index 000000000..7f013ca6d
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q22.output.schema
@@ -0,0 +1 @@
+`cntrycode` STRING,`numcust` BIGINT NOT NULL,`totacctbal` DOUBLE
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q3.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q3.output.schema
new file mode 100644
index 000000000..efa0f35ee
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q3.output.schema
@@ -0,0 +1 @@
+`l_orderkey` BIGINT,`revenue` DOUBLE,`o_orderdate` DATE,`o_shippriority` INT
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q4.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q4.output.schema
new file mode 100644
index 000000000..05c1131ab
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q4.output.schema
@@ -0,0 +1 @@
+`o_orderpriority` STRING,`order_count` BIGINT NOT NULL
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q5.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q5.output.schema
new file mode 100644
index 000000000..101c0e593
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q5.output.schema
@@ -0,0 +1 @@
+`n_name` STRING,`revenue` DOUBLE
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q6.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q6.output.schema
new file mode 100644
index 000000000..ce7e68717
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q6.output.schema
@@ -0,0 +1 @@
+`revenue` DOUBLE
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q7.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q7.output.schema
new file mode 100644
index 000000000..c6db31df5
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q7.output.schema
@@ -0,0 +1 @@
+`supp_nation` STRING,`cust_nation` STRING,`l_year` INT,`revenue` DOUBLE
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q8.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q8.output.schema
new file mode 100644
index 000000000..dcc2e903b
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q8.output.schema
@@ -0,0 +1 @@
+`o_year` INT,`mkt_share` DOUBLE
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q9.output.schema b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q9.output.schema
new file mode 100644
index 000000000..74b90c39d
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/schema/q9.output.schema
@@ -0,0 +1 @@
+`nation` STRING,`o_year` INT,`sum_profit` DOUBLE
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q1.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q1.sql
new file mode 100644
index 000000000..5031eb86c
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q1.sql
@@ -0,0 +1,39 @@
+--
+-- 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.
+--
+-- using default substitutions
+
+select
+ l_returnflag,
+ l_linestatus,
+ sum(l_quantity) as sum_qty,
+ sum(l_extendedprice) as sum_base_price,
+ sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
+ sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
+ avg(l_quantity) as avg_qty,
+ avg(l_extendedprice) as avg_price,
+ avg(l_discount) as avg_disc,
+ count(*) as count_order
+from
+ lineitem
+where
+ l_shipdate <= date '1998-12-01' - interval '90' day
+group by
+ l_returnflag,
+ l_linestatus
+order by
+ l_returnflag,
+ l_linestatus
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q10.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q10.sql
new file mode 100644
index 000000000..87854a9ad
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q10.sql
@@ -0,0 +1,50 @@
+--
+-- 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.
+--
+-- using default substitutions
+
+select
+ c_custkey,
+ c_name,
+ sum(l_extendedprice * (1 - l_discount)) as revenue,
+ c_acctbal,
+ n_name,
+ c_address,
+ c_phone,
+ c_comment
+from
+ customer,
+ orders,
+ lineitem,
+ nation
+where
+ c_custkey = o_custkey
+ and l_orderkey = o_orderkey
+ and o_orderdate >= date '1993-10-01'
+ and o_orderdate < date '1993-10-01' + interval '3' month
+ and l_returnflag = 'R'
+ and c_nationkey = n_nationkey
+group by
+ c_custkey,
+ c_name,
+ c_acctbal,
+ c_phone,
+ n_name,
+ c_address,
+ c_comment
+order by
+ revenue desc
+limit 20
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q11.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q11.sql
new file mode 100644
index 000000000..183dc3155
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q11.sql
@@ -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.
+--
+-- using default substitutions
+
+select
+ ps_partkey,
+ sum(ps_supplycost * ps_availqty) as value
+from
+ partsupp,
+ supplier,
+ nation
+where
+ ps_suppkey = s_suppkey
+ and s_nationkey = n_nationkey
+ and n_name = 'GERMANY'
+group by
+ ps_partkey having
+ sum(ps_supplycost * ps_availqty) > (
+ select
+ sum(ps_supplycost * ps_availqty) * 0.0001000000
+ from
+ partsupp,
+ supplier,
+ nation
+ where
+ ps_suppkey = s_suppkey
+ and s_nationkey = n_nationkey
+ and n_name = 'GERMANY'
+ )
+order by
+ value desc
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q12.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q12.sql
new file mode 100644
index 000000000..48f31b3da
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q12.sql
@@ -0,0 +1,46 @@
+--
+-- 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.
+--
+-- using default substitutions
+
+select
+ l_shipmode,
+ sum(case
+ when o_orderpriority = '1-URGENT'
+ or o_orderpriority = '2-HIGH'
+ then 1
+ else 0
+ end) as high_line_count,
+ sum(case
+ when o_orderpriority <> '1-URGENT'
+ and o_orderpriority <> '2-HIGH'
+ then 1
+ else 0
+ end) as low_line_count
+from
+ orders,
+ lineitem
+where
+ o_orderkey = l_orderkey
+ and l_shipmode in ('MAIL', 'SHIP')
+ and l_commitdate < l_receiptdate
+ and l_shipdate < l_commitdate
+ and l_receiptdate >= date '1994-01-01'
+ and l_receiptdate < date '1994-01-01' + interval '1' year
+group by
+ l_shipmode
+order by
+ l_shipmode
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q13.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q13.sql
new file mode 100644
index 000000000..13dd34860
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q13.sql
@@ -0,0 +1,38 @@
+--
+-- 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.
+--
+-- using default substitutions
+
+select
+ c_count,
+ count(*) as custdist
+from
+ (
+ select
+ c_custkey,
+ count(o_orderkey) as c_count
+ from
+ customer left outer join orders on
+ c_custkey = o_custkey
+ and o_comment not like '%special%requests%'
+ group by
+ c_custkey
+ ) as c_orders
+group by
+ c_count
+order by
+ custdist desc,
+ c_count desc
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q14.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q14.sql
new file mode 100644
index 000000000..4c5c485fe
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q14.sql
@@ -0,0 +1,31 @@
+--
+-- 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.
+--
+-- using default substitutions
+
+select
+ 100.00 * sum(case
+ when p_type like 'PROMO%'
+ then l_extendedprice * (1 - l_discount)
+ else 0
+ end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
+from
+ lineitem,
+ part
+where
+ l_partkey = p_partkey
+ and l_shipdate >= date '1995-09-01'
+ and l_shipdate < date '1995-09-01' + interval '1' month
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q15.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q15.sql
new file mode 100644
index 000000000..52519586d
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q15.sql
@@ -0,0 +1,51 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+-- using default substitutions
+
+with revenue0 as
+ (select
+ l_suppkey as supplier_no,
+ sum(l_extendedprice * (1 - l_discount)) as total_revenue
+ 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
+
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q16.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q16.sql
new file mode 100644
index 000000000..276368193
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q16.sql
@@ -0,0 +1,48 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+-- using default substitutions
+
+select
+ p_brand,
+ p_type,
+ p_size,
+ count(distinct ps_suppkey) as supplier_cnt
+from
+ partsupp,
+ part
+where
+ p_partkey = ps_partkey
+ and p_brand <> 'Brand#45'
+ and p_type not like 'MEDIUM POLISHED%'
+ and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
+ and ps_suppkey not in (
+ select
+ s_suppkey
+ from
+ supplier
+ where
+ s_comment like '%Customer%Complaints%'
+ )
+group by
+ p_brand,
+ p_type,
+ p_size
+order by
+ supplier_cnt desc,
+ p_brand,
+ p_type,
+ p_size
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q17.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q17.sql
new file mode 100644
index 000000000..cf33e0779
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q17.sql
@@ -0,0 +1,35 @@
+--
+-- 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.
+--
+-- using default substitutions
+
+select
+ sum(l_extendedprice) / 7.0 as avg_yearly
+from
+ lineitem,
+ part
+where
+ p_partkey = l_partkey
+ and p_brand = 'Brand#23'
+ and p_container = 'MED BOX'
+ and l_quantity < (
+ select
+ 0.2 * avg(l_quantity)
+ from
+ lineitem
+ where
+ l_partkey = p_partkey
+ )
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q18.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q18.sql
new file mode 100644
index 000000000..49dbc8d2c
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q18.sql
@@ -0,0 +1,51 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+-- using default substitutions
+
+select
+ c_name,
+ c_custkey,
+ o_orderkey,
+ o_orderdate,
+ o_totalprice,
+ sum(l_quantity)
+from
+ customer,
+ orders,
+ lineitem
+where
+ o_orderkey in (
+ select
+ l_orderkey
+ from
+ lineitem
+ group by
+ l_orderkey having
+ sum(l_quantity) > 300
+ )
+ and c_custkey = o_custkey
+ and o_orderkey = l_orderkey
+group by
+ c_name,
+ c_custkey,
+ o_orderkey,
+ o_orderdate,
+ o_totalprice
+order by
+ o_totalprice desc,
+ o_orderdate
+limit 100
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q19.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q19.sql
new file mode 100644
index 000000000..49457501a
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q19.sql
@@ -0,0 +1,53 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+-- using default substitutions
+
+select
+ sum(l_extendedprice* (1 - l_discount)) as revenue
+from
+ lineitem,
+ part
+where
+ (
+ p_partkey = l_partkey
+ and p_brand = 'Brand#12'
+ and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
+ and l_quantity >= 1 and l_quantity <= 1 + 10
+ and p_size between 1 and 5
+ and l_shipmode in ('AIR', 'AIR REG')
+ and l_shipinstruct = 'DELIVER IN PERSON'
+ )
+ or
+ (
+ p_partkey = l_partkey
+ and p_brand = 'Brand#23'
+ and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
+ and l_quantity >= 10 and l_quantity <= 10 + 10
+ and p_size between 1 and 10
+ and l_shipmode in ('AIR', 'AIR REG')
+ and l_shipinstruct = 'DELIVER IN PERSON'
+ )
+ or
+ (
+ p_partkey = l_partkey
+ and p_brand = 'Brand#34'
+ and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
+ and l_quantity >= 20 and l_quantity <= 20 + 10
+ and p_size between 1 and 15
+ and l_shipmode in ('AIR', 'AIR REG')
+ and l_shipinstruct = 'DELIVER IN PERSON'
+ )
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q2.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q2.sql
new file mode 100644
index 000000000..bb338bd8b
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q2.sql
@@ -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.
+--
+-- using default substitutions
+
+select
+ s_acctbal,
+ s_name,
+ n_name,
+ p_partkey,
+ p_mfgr,
+ s_address,
+ s_phone,
+ s_comment
+from
+ part,
+ supplier,
+ partsupp,
+ nation,
+ region
+where
+ p_partkey = ps_partkey
+ and s_suppkey = ps_suppkey
+ and p_size = 15
+ and p_type like '%BRASS'
+ and s_nationkey = n_nationkey
+ and n_regionkey = r_regionkey
+ and r_name = 'EUROPE'
+ and ps_supplycost = (
+ select
+ min(ps_supplycost)
+ from
+ partsupp,
+ supplier,
+ nation,
+ region
+ where
+ p_partkey = ps_partkey
+ and s_suppkey = ps_suppkey
+ and s_nationkey = n_nationkey
+ and n_regionkey = r_regionkey
+ and r_name = 'EUROPE'
+ )
+order by
+ s_acctbal desc,
+ n_name,
+ s_name,
+ p_partkey
+limit 100
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q20.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q20.sql
new file mode 100644
index 000000000..6656a892c
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q20.sql
@@ -0,0 +1,55 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+-- using default substitutions
+
+select
+ s_name,
+ s_address
+from
+ supplier,
+ nation
+where
+ s_suppkey in (
+ select
+ ps_suppkey
+ from
+ partsupp
+ where
+ ps_partkey in (
+ select
+ p_partkey
+ from
+ part
+ where
+ p_name like 'forest%'
+ )
+ and ps_availqty > (
+ select
+ 0.5 * sum(l_quantity)
+ from
+ lineitem
+ where
+ l_partkey = ps_partkey
+ and l_suppkey = ps_suppkey
+ and l_shipdate >= date '1994-01-01'
+ and l_shipdate < date '1994-01-01' + interval '1' year
+ )
+ )
+ and s_nationkey = n_nationkey
+ and n_name = 'CANADA'
+order by
+ s_name
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q21.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q21.sql
new file mode 100644
index 000000000..a582342c1
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q21.sql
@@ -0,0 +1,58 @@
+--
+-- 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.
+--
+-- using default substitutions
+
+select
+ s_name,
+ count(*) as numwait
+from
+ supplier,
+ lineitem l1,
+ orders,
+ nation
+where
+ s_suppkey = l1.l_suppkey
+ and o_orderkey = l1.l_orderkey
+ and o_orderstatus = 'F'
+ and l1.l_receiptdate > l1.l_commitdate
+ and exists (
+ select
+ *
+ from
+ lineitem l2
+ where
+ l2.l_orderkey = l1.l_orderkey
+ and l2.l_suppkey <> l1.l_suppkey
+ )
+ and not exists (
+ select
+ *
+ from
+ lineitem l3
+ where
+ l3.l_orderkey = l1.l_orderkey
+ and l3.l_suppkey <> l1.l_suppkey
+ and l3.l_receiptdate > l3.l_commitdate
+ )
+ and s_nationkey = n_nationkey
+ and n_name = 'SAUDI ARABIA'
+group by
+ s_name
+order by
+ numwait desc,
+ s_name
+limit 100
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q22.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q22.sql
new file mode 100644
index 000000000..a9dea75f7
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q22.sql
@@ -0,0 +1,55 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+-- using default substitutions
+
+select
+ cntrycode,
+ count(*) as numcust,
+ sum(c_acctbal) as totacctbal
+from
+ (
+ select
+ substring(c_phone, 1, 2) as cntrycode,
+ c_acctbal
+ from
+ customer
+ where
+ substring(c_phone, 1, 2) in
+ ('13', '31', '23', '29', '30', '18', '17')
+ and c_acctbal > (
+ select
+ avg(c_acctbal)
+ from
+ customer
+ where
+ c_acctbal > 0.00
+ and substring(c_phone, 1, 2) in
+ ('13', '31', '23', '29', '30', '18', '17')
+ )
+ and not exists (
+ select
+ *
+ from
+ orders
+ where
+ o_custkey = c_custkey
+ )
+ ) as custsale
+group by
+ cntrycode
+order by
+ cntrycode
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q3.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q3.sql
new file mode 100644
index 000000000..a2386f66f
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q3.sql
@@ -0,0 +1,41 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+-- using default substitutions
+
+select
+ l_orderkey,
+ sum(l_extendedprice * (1 - l_discount)) as revenue,
+ o_orderdate,
+ o_shippriority
+from
+ customer,
+ orders,
+ lineitem
+where
+ c_mktsegment = 'BUILDING'
+ and c_custkey = o_custkey
+ and l_orderkey = o_orderkey
+ and o_orderdate < date '1995-03-15'
+ and l_shipdate > date '1995-03-15'
+group by
+ l_orderkey,
+ o_orderdate,
+ o_shippriority
+order by
+ revenue desc,
+ o_orderdate
+limit 10
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q4.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q4.sql
new file mode 100644
index 000000000..0fc361b96
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q4.sql
@@ -0,0 +1,39 @@
+--
+-- 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.
+--
+-- using default substitutions
+
+select
+ o_orderpriority,
+ count(*) as order_count
+from
+ orders
+where
+ o_orderdate >= date '1993-07-01'
+ and o_orderdate < date '1993-07-01' + interval '3' month
+ and exists (
+ select
+ *
+ from
+ lineitem
+ where
+ l_orderkey = o_orderkey
+ and l_commitdate < l_receiptdate
+ )
+group by
+ o_orderpriority
+order by
+ o_orderpriority
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q5.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q5.sql
new file mode 100644
index 000000000..fc998961c
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q5.sql
@@ -0,0 +1,42 @@
+--
+-- 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.
+--
+-- using default substitutions
+
+select
+ n_name,
+ sum(l_extendedprice * (1 - l_discount)) as revenue
+from
+ customer,
+ orders,
+ lineitem,
+ supplier,
+ nation,
+ region
+where
+ c_custkey = o_custkey
+ and l_orderkey = o_orderkey
+ and l_suppkey = s_suppkey
+ and c_nationkey = s_nationkey
+ and s_nationkey = n_nationkey
+ and n_regionkey = r_regionkey
+ and r_name = 'ASIA'
+ and o_orderdate >= date '1994-01-01'
+ and o_orderdate < date '1994-01-01' + interval '1' year
+group by
+ n_name
+order by
+ revenue desc
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q6.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q6.sql
new file mode 100644
index 000000000..efd8a5011
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q6.sql
@@ -0,0 +1,27 @@
+--
+-- 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.
+--
+-- using default substitutions
+
+select
+ sum(l_extendedprice * l_discount) as revenue
+from
+ lineitem
+where
+ l_shipdate >= date '1994-01-01'
+ and l_shipdate < date '1994-01-01' + interval '1' year
+ and l_discount between .06 - 0.01 and .06 + 0.01
+ and l_quantity < 24
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q7.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q7.sql
new file mode 100644
index 000000000..3d932e660
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q7.sql
@@ -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.
+--
+-- using default substitutions
+
+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,
+ year(l_shipdate) as l_year,
+ l_extendedprice * (1 - l_discount) as volume
+ from
+ supplier,
+ lineitem,
+ orders,
+ customer,
+ nation n1,
+ nation n2
+ where
+ s_suppkey = l_suppkey
+ and o_orderkey = l_orderkey
+ and c_custkey = o_custkey
+ and s_nationkey = n1.n_nationkey
+ and c_nationkey = n2.n_nationkey
+ and (
+ (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
+ or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
+ )
+ and l_shipdate between date '1995-01-01' and date '1996-12-31'
+ ) as shipping
+group by
+ supp_nation,
+ cust_nation,
+ l_year
+order by
+ supp_nation,
+ cust_nation,
+ l_year
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q8.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q8.sql
new file mode 100644
index 000000000..2c96752b8
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q8.sql
@@ -0,0 +1,55 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+--
+-- using default substitutions
+
+select
+ o_year,
+ sum(case
+ when nation = 'BRAZIL' then volume
+ else 0
+ end) / sum(volume) as mkt_share
+from
+ (
+ select
+ year(o_orderdate) as o_year,
+ l_extendedprice * (1 - l_discount) as volume,
+ n2.n_name as nation
+ from
+ part,
+ supplier,
+ lineitem,
+ orders,
+ customer,
+ nation n1,
+ nation n2,
+ region
+ where
+ p_partkey = l_partkey
+ and s_suppkey = l_suppkey
+ and l_orderkey = o_orderkey
+ and o_custkey = c_custkey
+ and c_nationkey = n1.n_nationkey
+ and n1.n_regionkey = r_regionkey
+ and r_name = 'AMERICA'
+ and s_nationkey = n2.n_nationkey
+ and o_orderdate between date '1995-01-01' and date '1996-12-31'
+ and p_type = 'ECONOMY ANODIZED STEEL'
+ ) as all_nations
+group by
+ o_year
+order by
+ o_year
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q9.sql b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q9.sql
new file mode 100644
index 000000000..a5f2d89cb
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/main/resources/tpch/sql/q9.sql
@@ -0,0 +1,50 @@
+--
+-- 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.
+--
+-- using default substitutions
+
+select
+ nation,
+ o_year,
+ sum(amount) as sum_profit
+from
+ (
+ select
+ n_name as nation,
+ year(o_orderdate) as o_year,
+ l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
+ from
+ part,
+ supplier,
+ lineitem,
+ partsupp,
+ orders,
+ nation
+ where
+ s_suppkey = l_suppkey
+ and ps_suppkey = l_suppkey
+ and ps_partkey = l_partkey
+ and p_partkey = l_partkey
+ and o_orderkey = l_orderkey
+ and s_nationkey = n_nationkey
+ and p_name like '%green%'
+ ) as profit
+group by
+ nation,
+ o_year
+order by
+ nation,
+ o_year desc
diff --git a/extensions/spark/kyuubi-spark-connector-tpch/src/test/scala/org/apache/kyuubi/spark/connector/tpch/TPCHQuerySuite.scala b/extensions/spark/kyuubi-spark-connector-tpch/src/test/scala/org/apache/kyuubi/spark/connector/tpch/TPCHQuerySuite.scala
new file mode 100644
index 000000000..67f65df5e
--- /dev/null
+++ b/extensions/spark/kyuubi-spark-connector-tpch/src/test/scala/org/apache/kyuubi/spark/connector/tpch/TPCHQuerySuite.scala
@@ -0,0 +1,133 @@
+/*
+ * 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.
+ */
+
+package org.apache.kyuubi.spark.connector.tpch
+
+import java.nio.charset.StandardCharsets
+import java.nio.file.{Files, Path, Paths}
+
+import scala.collection.JavaConverters._
+import scala.io.{Codec, Source}
+
+import org.apache.spark.SparkConf
+import org.apache.spark.sql.SparkSession
+
+import org.apache.kyuubi.KyuubiFunSuite
+import org.apache.kyuubi.spark.connector.common.LocalSparkSession.withSparkSession
+import org.apache.kyuubi.spark.connector.common.SparkUtils
+
+// scalastyle:off line.size.limit
+/**
+ * To run this test suite:
+ * {{{
+ * build/mvn clean install \
+ * -Dmaven.plugin.scalatest.exclude.tags="" \
+ * -Dtest=none -DwildcardSuites=org.apache.kyuubi.spark.connector.tpch.TPCHQuerySuite
+ * }}}
+ *
+ * To re-generate golden files for this suite:
+ * {{{
+ * KYUUBI_UPDATE=1 build/mvn clean install \
+ * -Dmaven.plugin.scalatest.exclude.tags="" \
+ * -Dtest=none -DwildcardSuites=org.apache.kyuubi.spark.connector.tpch.TPCHQuerySuite
+ * }}}
+ */
+// scalastyle:on line.size.limit
+
+class TPCHQuerySuite extends KyuubiFunSuite {
+
+ private val regenerateGoldenFiles = sys.env.get("KYUUBI_UPDATE").contains("1")
+
+ val baseResourcePath: Path =
+ Paths.get("src", "main", "resources")
+
+ val queries: Set[String] = (1 to 22).map(i => s"q$i").toSet
+
+ val queryToSumHash: Map[String, Long] = Map(
+ "q1" -> -2130215201L,
+ "q2" -> 2296723790L,
+ "q3" -> -4118618460L,
+ "q4" -> 1856406098L,
+ "q5" -> 3717321142L,
+ "q6" -> 2062248569L,
+ "q7" -> -1955579146L,
+ "q8" -> 453239528L,
+ "q9" -> 10861514367L,
+ "q10" -> -4090660469L,
+ "q11" -> -21773379672L,
+ "q12" -> 2455990065L,
+ "q13" -> -1379884230L,
+ "q14" -> 47333415L,
+ "q15" -> -2021679095L,
+ "q16" -> 326972717L,
+ "q17" -> 42L,
+ "q18" -> 1596157524L,
+ "q19" -> -1061725826L,
+ "q20" -> -35851308L,
+ "q21" -> 1407638530L,
+ "q22" -> 2111900859L)
+
+ private def fileToString(file: Path): String = {
+ new String(Files.readAllBytes(file), StandardCharsets.UTF_8)
+ }
+
+ test("run query on tiny") {
+ assume(SparkUtils.isSparkVersionEqualTo("3.2"))
+ val viewSuffix = "view";
+ val sparkConf = new SparkConf().setMaster("local[*]")
+ .set("spark.ui.enabled", "false")
+ .set("spark.sql.catalogImplementation", "in-memory")
+ .set("spark.sql.catalog.tpch", classOf[TPCHCatalog].getName)
+ withSparkSession(SparkSession.builder.config(sparkConf).getOrCreate()) { spark =>
+ spark.sql("USE tpch.tiny")
+ queries.map { queryName =>
+ val in = getClass.getClassLoader.getResourceAsStream(
+ s"tpch/sql/$queryName.sql")
+ val queryContent: String = Source.fromInputStream(in)(Codec.UTF8).mkString
+ in.close()
+ queryName -> queryContent
+ }.foreach { case (name, sql) =>
+ try {
+ val result = spark.sql(sql).collect()
+ val schema = spark.sql(sql).schema
+ val schemaDDL = schema.toDDL + "\n"
+ spark.createDataFrame(result.toList.asJava, schema).createTempView(s"$name$viewSuffix")
+ val sumHashResult =
+ spark.sql(s"select sum(hash(*)) from $name$viewSuffix").collect().head.get(0)
+
+ val goldenFile = Paths.get(
+ baseResourcePath.toFile.getAbsolutePath,
+ "tpch",
+ "schema",
+ s"${name.stripSuffix(".sql")}.output.schema")
+
+ if (regenerateGoldenFiles) {
+ Files.write(goldenFile, schemaDDL.getBytes)
+ }
+ val expectedSchema = fileToString(goldenFile)
+
+ assert(schemaDDL == expectedSchema)
+ assert(sumHashResult == queryToSumHash(name))
+
+ } catch {
+ case cause: Throwable =>
+ fail(name, cause)
+ }
+ }
+ }
+ }
+}