You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by mb...@apache.org on 2021/03/26 11:10:25 UTC
[asterixdb] branch master updated: [NO ISSUE] Fix TPC-H queries
This is an automated email from the ASF dual-hosted git repository.
mblow pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/asterixdb.git
The following commit(s) were added to refs/heads/master by this push:
new 5f96514 [NO ISSUE] Fix TPC-H queries
5f96514 is described below
commit 5f96514e6f3fcbfb4ef99d72902b5fe885aab6d4
Author: Rui Guo <ru...@uci.edu>
AuthorDate: Tue Mar 9 15:24:55 2021 -0800
[NO ISSUE] Fix TPC-H queries
The SQLPP syntax is updated in the past a few years, and in this commit
we fix the previous TPC-H SQLPP queries so that they can work with the
latest master branch.
Change-Id: Ib29c1de968a1f4b0d5c4252855bca1af887e3039
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/10343
Integration-Tests: Jenkins <je...@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <je...@fulliautomatix.ics.uci.edu>
Reviewed-by: Dmitry Lychagin <dm...@couchbase.com>
---
.../src/main/resources/benchmarks/tpch/queries/q11.sqlpp | 3 ++-
.../src/main/resources/benchmarks/tpch/queries/q20.sqlpp.dis | 8 ++++----
.../src/main/resources/benchmarks/tpch/queries/q21.sqlpp.dis | 6 +++---
3 files changed, 9 insertions(+), 8 deletions(-)
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q11.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q11.sqlpp
index a6c0308..b3673f2 100644
--- a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q11.sqlpp
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q11.sqlpp
@@ -32,7 +32,7 @@ WITH sum AS (
)[0]
-SELECT ps_partkey, SUM(ps.ps_supplycost * ps.ps_availqty) AS part_value
+SELECT ps_partkey, part_value
FROM Partsupp ps,
(
SELECT s.s_suppkey
@@ -42,6 +42,7 @@ FROM Partsupp ps,
) sn
WHERE ps.ps_suppkey = sn.s_suppkey
GROUP BY ps.ps_partkey
+LET part_value = SUM(ps.ps_supplycost * ps.ps_availqty)
HAVING part_value > sum * 0.0001000
ORDER BY part_value DESC
;
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q20.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q20.sqlpp.dis
index 45c4740..230c19a 100644
--- a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q20.sqlpp.dis
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q20.sqlpp.dis
@@ -38,12 +38,12 @@ q20_tmp2 AS
,
q20_tmp3 AS
(
- SELECT ps_suppkey, ps_availqty, t2.sum_quantity
- FROM Partsupp
+ SELECT ps.ps_suppkey, ps.ps_availqty, t2.sum_quantity
+ FROM Partsupp ps
JOIN q20_tmp1 t1
- ON ps_partkey = t1.p_partkey
+ ON ps.ps_partkey = t1.p_partkey
JOIN q20_tmp2 t2
- ON ps_partkey = t2.l_partkey and ps_suppkey = t2.l_suppkey
+ ON ps.ps_partkey = t2.l_partkey and ps.ps_suppkey = t2.l_suppkey
)
,
q20_tmp4 AS
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q21.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q21.sqlpp.dis
index 768ad47..6e46f8c 100644
--- a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q21.sqlpp.dis
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q21.sqlpp.dis
@@ -35,7 +35,7 @@ WITH tmp1 AS
),
tmp2 AS
(
- SELECT l2.l_orderkey,
+ SELECT l_orderkey,
COUNT(l_suppkey) AS count_suppkey,
MAX(l_suppkey) AS max_suppkey
FROM (
@@ -49,7 +49,7 @@ tmp2 AS
SELECT t4.s_name, COUNT(*) AS numwait
FROM (
- SELECT t3.s_name, l_suppkey, t2.l_orderkey, count_suppkey, max_suppkey
+ SELECT t3.s_name, t3.l_suppkey, t2.l_orderkey, t3.count_suppkey, t3.max_suppkey
FROM (
SELECT ns.s_name, t1.l_orderkey, t1.l_suppkey
FROM LineItem l,
@@ -64,7 +64,7 @@ FROM (
AND o.o_orderkey = t1.l_orderkey AND l.l_orderkey = t1.l_orderkey
AND o.o_orderstatus = 'F'
) AS t3
- JOIN tmp2 AS t2 ON count_suppkey >= 0 AND t3.l_orderkey = t2.l_orderkey
+ JOIN tmp2 AS t2 ON t3.count_suppkey >= 0 AND t3.l_orderkey = t2.l_orderkey
) AS t4
GROUP BY t4.s_name
ORDER BY numwait DESC, t4.s_name