[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 <jenkins@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Reviewed-by: Dmitry Lychagin <dmitry.lychagin@couchbase.com>
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 @@
)[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 @@
) 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_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 @@
),
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 @@
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 @@
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