Some more fixed queries for TPC-H
(mostly contributed by Christopher)
diff --git a/asterix-benchmarks/src/main/resources/tpc-h/queries/q05_local_supplier_volume.3.query.aql b/asterix-benchmarks/src/main/resources/tpc-h/queries/q05_local_supplier_volume.3.query.aql
index e0e0563..342a85a 100644
--- a/asterix-benchmarks/src/main/resources/tpc-h/queries/q05_local_supplier_volume.3.query.aql
+++ b/asterix-benchmarks/src/main/resources/tpc-h/queries/q05_local_supplier_volume.3.query.aql
@@ -11,6 +11,7 @@
for $n in dataset('Nation')
for $r in dataset('Region')
where $n.n_regionkey = $r.r_regionkey
+ and $r.r_name = 'ASIA'
return {
"n_name": $n.n_name,
"n_nationkey": $n.n_nationkey
@@ -32,7 +33,7 @@
"s_nationkey": $s1.s_nationkey
}
)
- where $l1.l_orderkey = $o.o_orderkey and $o.o_orderdate >= '1990-01-01' and $o.o_orderdate < '1995-01-01'
+ where $l1.l_orderkey = $o.o_orderkey and $o.o_orderdate >= '1994-01-01' and $o.o_orderdate < '1995-01-01'
return {
"n_name": $l1.n_name,
"l_extendedprice": $l1.l_extendedprice,
diff --git a/asterix-benchmarks/src/main/resources/tpc-h/queries/q14_promotion_effect.3.query.aql b/asterix-benchmarks/src/main/resources/tpc-h/queries/q14_promotion_effect.3.query.aql
index 786637d..e6007e7 100644
--- a/asterix-benchmarks/src/main/resources/tpc-h/queries/q14_promotion_effect.3.query.aql
+++ b/asterix-benchmarks/src/main/resources/tpc-h/queries/q14_promotion_effect.3.query.aql
@@ -5,11 +5,16 @@
where $l.l_partkey = $p.p_partkey
and $l.l_shipdate >= '1995-09-01'
and $l.l_shipdate < '1995-10-01'
-group by $t:=1 with $l, $p
+let $lp := {
+ "l_extendedprice": $l.l_extendedprice,
+ "l_discount": $l.l_discount,
+ "p_type": $p.p_type
+ }
+group by $t:=1 with $lp
return 100.00 * sum(
- for $i in $l
- return switch-case(like($i.p_type, 'PROMO%'),
+ for $i in $lp
+ return switch-case(like($i.p_type, 'PROMO%'),
true, $i.l_extendedprice*(1-$i.l_discount),
false, 0.0)
- ) / sum(for $i in $l return $i.l_extendedprice * (1 - $i.l_discount)
+ ) / sum(for $i in $lp return $i.l_extendedprice * (1 - $i.l_discount)
)
diff --git a/asterix-benchmarks/src/main/resources/tpc-h/queries/q18_large_volume_customer.3.query.aql b/asterix-benchmarks/src/main/resources/tpc-h/queries/q18_large_volume_customer.3.query.aql
index 289d900..86b95cf 100644
--- a/asterix-benchmarks/src/main/resources/tpc-h/queries/q18_large_volume_customer.3.query.aql
+++ b/asterix-benchmarks/src/main/resources/tpc-h/queries/q18_large_volume_customer.3.query.aql
@@ -11,7 +11,7 @@
"t_sum_quantity": sum(for $i in $l return $i.l_quantity)
}
)
-where $o.o_orderkey = $t.l_orderkey and $t.t_sum_quantity > 30
+where $o.o_orderkey = $t.l_orderkey and $t.t_sum_quantity > 300
for $l in dataset('LineItem')
where $l.l_orderkey = $o.o_orderkey
group by $c_name := $c.c_name, $c_custkey := $c.c_custkey, $o_orderkey := $o.o_orderkey,
diff --git a/asterix-benchmarks/src/main/resources/tpc-h/queries/q19_discounted_revenue.3.query.aql b/asterix-benchmarks/src/main/resources/tpc-h/queries/q19_discounted_revenue.3.query.aql
index 7e26d94..9ef0088 100644
--- a/asterix-benchmarks/src/main/resources/tpc-h/queries/q19_discounted_revenue.3.query.aql
+++ b/asterix-benchmarks/src/main/resources/tpc-h/queries/q19_discounted_revenue.3.query.aql
@@ -8,24 +8,24 @@
where $p.p_partkey = $l.l_partkey
and ( (
$p.p_brand = 'Brand#12'
- and reg-exp($p.p_container,'SM CASE||SM BOX||SM PACK||SM PKG')
+ and reg-exp($p.p_container,'SM CASE|SM BOX|SM PACK|SM PKG')
and $l.l_quantity >= 1 and $l.l_quantity <= 11
and $p.p_size >= 1 and $p.p_size <= 5
- and reg-exp($l.l_shipmode, 'AIR||AIR REG')
+ and ($l.l_shipmode = 'AIR' or $l.l_shipmode = 'AIR REG')
and $l.l_shipinstruct = 'DELIVER IN PERSON'
) or (
$p.p_brand = 'Brand#23'
- and reg-exp($p.p_container, 'MED BAG||MED BOX||MED PKG||MED PACK')
+ and reg-exp($p.p_container, 'MED BAG|MED BOX|MED PKG|MED PACK')
and $l.l_quantity >= 10 and $l.l_quantity <= 20
and $p.p_size >= 1 and $p.p_size <= 10
- and reg-exp($l.l_shipmode, 'AIR||AIR REG')
+ and ($l.l_shipmode = 'AIR' or $l.l_shipmode = 'AIR REG')
and $l.l_shipinstruct = 'DELIVER IN PERSON'
) or (
$p.p_brand = 'Brand#34'
- and reg-exp($p.p_container, 'LG CASE||LG BOX||LG PACK||LG PKG')
+ and reg-exp($p.p_container, 'LG CASE|LG BOX|LG PACK|LG PKG')
and $l.l_quantity >= 20 and $l.l_quantity <= 30
and $p.p_size >= 1 and $p.p_size <= 15
- and reg-exp($l.l_shipmode, 'AIR||AIR REG')
+ and ($l.l_shipmode = 'AIR' or $l.l_shipmode = 'AIR REG')
and $l.l_shipinstruct = 'DELIVER IN PERSON'
)
)