formatting fixes
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q10_returned_item/q10_returned_item.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q10_returned_item/q10_returned_item.3.query.aql
index c867b31..19a2b44 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q10_returned_item/q10_returned_item.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q10_returned_item/q10_returned_item.3.query.aql
@@ -1,55 +1,55 @@
use dataverse tpch;
for $locn in (
-for $l in dataset('LineItem')
-for $ocn in (
- for $o in dataset('Orders')
- for $c in dataset('Customer')
- where $c.c_custkey = $o.o_custkey and $o.o_orderdate >= '1993-10-01' and $o.o_orderdate < '1994-01-01'
- for $n in dataset('Nation')
- where $c.c_nationkey = $n.n_nationkey
- return {
- "c_custkey": $c.c_custkey,
- "c_name": $c.c_name,
- "c_acctbal": $c.c_acctbal,
- "n_name": $n.n_name,
- "c_address": $c.c_address,
- "c_phone": $c.c_phone,
- "c_comment": $c.c_comment,
- "o_orderkey": $o.o_orderkey
- }
-)
-where
- $l.l_orderkey = $ocn.o_orderkey and $l.l_returnflag = 'R'
- return {
- "c_custkey": $ocn.c_custkey,
- "c_name": $ocn.c_name,
- "c_acctbal": $ocn.c_acctbal,
- "n_name": $ocn.n_name,
- "c_address": $ocn.c_address,
- "c_phone": $ocn.c_phone,
- "c_comment": $ocn.c_comment,
- "l_extendedprice": $l.l_extendedprice,
- "l_discount": $l.l_discount
- }
+ for $l in dataset('LineItem')
+ for $ocn in (
+ for $o in dataset('Orders')
+ for $c in dataset('Customer')
+ where $c.c_custkey = $o.o_custkey and $o.o_orderdate >= '1993-10-01'
+ and $o.o_orderdate < '1994-01-01'
+ for $n in dataset('Nation')
+ where $c.c_nationkey = $n.n_nationkey
+ return {
+ "c_custkey": $c.c_custkey,
+ "c_name": $c.c_name,
+ "c_acctbal": $c.c_acctbal,
+ "n_name": $n.n_name,
+ "c_address": $c.c_address,
+ "c_phone": $c.c_phone,
+ "c_comment": $c.c_comment,
+ "o_orderkey": $o.o_orderkey
+ }
+ )
+ where $l.l_orderkey = $ocn.o_orderkey and $l.l_returnflag = 'R'
+ return {
+ "c_custkey": $ocn.c_custkey,
+ "c_name": $ocn.c_name,
+ "c_acctbal": $ocn.c_acctbal,
+ "n_name": $ocn.n_name,
+ "c_address": $ocn.c_address,
+ "c_phone": $ocn.c_phone,
+ "c_comment": $ocn.c_comment,
+ "l_extendedprice": $l.l_extendedprice,
+ "l_discount": $l.l_discount
+ }
)
group by $c_custkey:=$locn.c_custkey,
- $c_name:=$locn.c_name,
- $c_acctbal:=$locn.c_acctbal, $c_phone:=$locn.c_phone,
- $n_name:=$locn.n_name, $c_address:=$locn.c_address, $c_comment:=$locn.c_comment
- with $locn
+ $c_name:=$locn.c_name,
+ $c_acctbal:=$locn.c_acctbal, $c_phone:=$locn.c_phone,
+ $n_name:=$locn.n_name, $c_address:=$locn.c_address, $c_comment:=$locn.c_comment
+ with $locn
let $revenue := sum(for $i in $locn return $i.l_extendedprice * (1 - $i.l_discount))
order by $revenue desc
limit 20
return {
- "c_custkey": $c_custkey,
- "c_name": $c_name,
- "revenue": $revenue,
- "c_acctbal": $c_acctbal,
- "n_name": $n_name,
- "c_address": $c_address,
- "c_phone": $c_phone,
- "c_comment": $c_comment
+ "c_custkey": $c_custkey,
+ "c_name": $c_name,
+ "revenue": $revenue,
+ "c_acctbal": $c_acctbal,
+ "n_name": $n_name,
+ "c_address": $c_address,
+ "c_phone": $c_phone,
+ "c_comment": $c_comment
}
-
-
+
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q10_returned_item_int64/q10_returned_item_int64.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q10_returned_item_int64/q10_returned_item_int64.3.query.aql
index c867b31..19a2b44 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q10_returned_item_int64/q10_returned_item_int64.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q10_returned_item_int64/q10_returned_item_int64.3.query.aql
@@ -1,55 +1,55 @@
use dataverse tpch;
for $locn in (
-for $l in dataset('LineItem')
-for $ocn in (
- for $o in dataset('Orders')
- for $c in dataset('Customer')
- where $c.c_custkey = $o.o_custkey and $o.o_orderdate >= '1993-10-01' and $o.o_orderdate < '1994-01-01'
- for $n in dataset('Nation')
- where $c.c_nationkey = $n.n_nationkey
- return {
- "c_custkey": $c.c_custkey,
- "c_name": $c.c_name,
- "c_acctbal": $c.c_acctbal,
- "n_name": $n.n_name,
- "c_address": $c.c_address,
- "c_phone": $c.c_phone,
- "c_comment": $c.c_comment,
- "o_orderkey": $o.o_orderkey
- }
-)
-where
- $l.l_orderkey = $ocn.o_orderkey and $l.l_returnflag = 'R'
- return {
- "c_custkey": $ocn.c_custkey,
- "c_name": $ocn.c_name,
- "c_acctbal": $ocn.c_acctbal,
- "n_name": $ocn.n_name,
- "c_address": $ocn.c_address,
- "c_phone": $ocn.c_phone,
- "c_comment": $ocn.c_comment,
- "l_extendedprice": $l.l_extendedprice,
- "l_discount": $l.l_discount
- }
+ for $l in dataset('LineItem')
+ for $ocn in (
+ for $o in dataset('Orders')
+ for $c in dataset('Customer')
+ where $c.c_custkey = $o.o_custkey and $o.o_orderdate >= '1993-10-01'
+ and $o.o_orderdate < '1994-01-01'
+ for $n in dataset('Nation')
+ where $c.c_nationkey = $n.n_nationkey
+ return {
+ "c_custkey": $c.c_custkey,
+ "c_name": $c.c_name,
+ "c_acctbal": $c.c_acctbal,
+ "n_name": $n.n_name,
+ "c_address": $c.c_address,
+ "c_phone": $c.c_phone,
+ "c_comment": $c.c_comment,
+ "o_orderkey": $o.o_orderkey
+ }
+ )
+ where $l.l_orderkey = $ocn.o_orderkey and $l.l_returnflag = 'R'
+ return {
+ "c_custkey": $ocn.c_custkey,
+ "c_name": $ocn.c_name,
+ "c_acctbal": $ocn.c_acctbal,
+ "n_name": $ocn.n_name,
+ "c_address": $ocn.c_address,
+ "c_phone": $ocn.c_phone,
+ "c_comment": $ocn.c_comment,
+ "l_extendedprice": $l.l_extendedprice,
+ "l_discount": $l.l_discount
+ }
)
group by $c_custkey:=$locn.c_custkey,
- $c_name:=$locn.c_name,
- $c_acctbal:=$locn.c_acctbal, $c_phone:=$locn.c_phone,
- $n_name:=$locn.n_name, $c_address:=$locn.c_address, $c_comment:=$locn.c_comment
- with $locn
+ $c_name:=$locn.c_name,
+ $c_acctbal:=$locn.c_acctbal, $c_phone:=$locn.c_phone,
+ $n_name:=$locn.n_name, $c_address:=$locn.c_address, $c_comment:=$locn.c_comment
+ with $locn
let $revenue := sum(for $i in $locn return $i.l_extendedprice * (1 - $i.l_discount))
order by $revenue desc
limit 20
return {
- "c_custkey": $c_custkey,
- "c_name": $c_name,
- "revenue": $revenue,
- "c_acctbal": $c_acctbal,
- "n_name": $n_name,
- "c_address": $c_address,
- "c_phone": $c_phone,
- "c_comment": $c_comment
+ "c_custkey": $c_custkey,
+ "c_name": $c_name,
+ "revenue": $revenue,
+ "c_acctbal": $c_acctbal,
+ "n_name": $n_name,
+ "c_address": $c_address,
+ "c_phone": $c_phone,
+ "c_comment": $c_comment
}
-
-
+
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q11_important_stock/q11_important_stock.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q11_important_stock/q11_important_stock.3.query.aql
index 42a86d1..fdb01ca 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q11_important_stock/q11_important_stock.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q11_important_stock/q11_important_stock.3.query.aql
@@ -1,31 +1,34 @@
use dataverse tpch;
let $sum := sum (
-for $ps in dataset('Partsupp')
-for $sn in (
- for $s in dataset('Supplier')
- for $n in dataset('Nation')
- where $s.s_nationkey = $n.n_nationkey
- return {"s_suppkey": $s.s_suppkey}
- )
-where $ps.ps_suppkey = $sn.s_suppkey
-return $ps.ps_supplycost * $ps.ps_availqty)
-
-for $t1 in
-(
-for $ps in dataset('Partsupp')
-for $sn in (
- for $s in dataset('Supplier')
- for $n in dataset('Nation')
- where $s.s_nationkey = $n.n_nationkey
- return {"s_suppkey": $s.s_suppkey}
+ for $ps in dataset('Partsupp')
+ for $sn in (
+ for $s in dataset('Supplier')
+ for $n in dataset('Nation')
+ where $s.s_nationkey = $n.n_nationkey
+ return { "s_suppkey": $s.s_suppkey }
+ )
+ where $ps.ps_suppkey = $sn.s_suppkey
+ return $ps.ps_supplycost * $ps.ps_availqty
)
-where $ps.ps_suppkey = $sn.s_suppkey
-group by $ps_partkey := $ps.ps_partkey with $ps
-return {"ps_partkey": $ps_partkey,
-"part_value": sum(for $i in $ps return $i.ps_supplycost * $i.ps_availqty)}
+for $t1 in (
+ for $ps in dataset('Partsupp')
+ for $sn in (
+ for $s in dataset('Supplier')
+ for $n in dataset('Nation')
+ where $s.s_nationkey = $n.n_nationkey
+ return { "s_suppkey": $s.s_suppkey }
+ )
+ where $ps.ps_suppkey = $sn.s_suppkey
+ group by $ps_partkey := $ps.ps_partkey with $ps
+ return {
+ "ps_partkey": $ps_partkey,
+ "part_value": sum(for $i in $ps return $i.ps_supplycost * $i.ps_availqty)
+ }
)
-
where $t1.part_value > $sum * 0.00001
order by $t1.part_value desc
-return {"partkey": $t1.ps_partkey, "part_value":$t1.part_value}
+return {
+ "partkey": $t1.ps_partkey,
+ "part_value": $t1.part_value
+}
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q12_shipping/q12_shipping.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q12_shipping/q12_shipping.3.query.aql
index b6d4ab0..c836a48 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q12_shipping/q12_shipping.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q12_shipping/q12_shipping.3.query.aql
@@ -2,27 +2,26 @@
for $l in dataset('LineItem')
for $o in dataset('Orders')
-
-where
-$o.o_orderkey = $l.l_orderkey
-and $l.l_commitdate < $l.l_receiptdate
-and $l.l_shipdate < $l.l_commitdate
-and $l.l_receiptdate >= '1994-01-01'
-and $l.l_receiptdate < '1995-01-01'
-and ($l.l_shipmode = 'MAIL' or $l.l_shipmode = 'SHIP')
-
+where $o.o_orderkey = $l.l_orderkey
+ and $l.l_commitdate < $l.l_receiptdate
+ and $l.l_shipdate < $l.l_commitdate
+ and $l.l_receiptdate >= '1994-01-01'
+ and $l.l_receiptdate < '1995-01-01'
+ and ($l.l_shipmode = 'MAIL' or $l.l_shipmode = 'SHIP')
group by $l_shipmode := $l.l_shipmode with $o
order by $l_shipmode
return {
-"l_shipmode": $l_shipmode,
-"high_line_count": sum(for $i in $o
- return switch-case( $i.o_orderpriority ='1-URGENT' or $i.o_orderpriority ='2-HIGH',
- true, 1,
- false, 0)),
-"low_line_count": sum(for $i in $o
- return switch-case( $i.o_orderpriority ='1-URGENT' or $i.o_orderpriority ='2-HIGH',
- true, 0,
- false, 1))
+ "l_shipmode": $l_shipmode,
+ "high_line_count": sum(
+ for $i in $o
+ return
+ switch-case($i.o_orderpriority ='1-URGENT' or $i.o_orderpriority ='2-HIGH',
+ true, 1, false, 0)
+ ),
+ "low_line_count": sum(
+ for $i in $o
+ return switch-case($i.o_orderpriority ='1-URGENT' or $i.o_orderpriority ='2-HIGH',
+ true, 0, false, 1)
+ )
}
-
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q13_customer_distribution/q13_customer_distribution.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q13_customer_distribution/q13_customer_distribution.3.query.aql
index dd65443..a7b7b62 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q13_customer_distribution/q13_customer_distribution.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q13_customer_distribution/q13_customer_distribution.3.query.aql
@@ -2,26 +2,28 @@
set import-private-functions 'true';
for $gco in (
- for $co in
- (
- for $c in dataset('Customer')
- return
- {
- "c_custkey": $c.c_custkey,
- "o_orderkey_count": count(
- for $o in dataset('Orders')
- where $c.c_custkey = $o.o_custkey and not(like($o.o_comment,'%special%requests%'))
- return $o.o_orderkey)
- }
- )
- group by $c_custkey := $co.c_custkey with $co
- return{
- "c_custkey": $c_custkey, "c_count": sum(for $i in $co return $i.o_orderkey_count)
- }
+ for $co in (
+ for $c in dataset('Customer')
+ return {
+ "c_custkey": $c.c_custkey,
+ "o_orderkey_count": count(
+ for $o in dataset('Orders')
+ where $c.c_custkey = $o.o_custkey and not(like($o.o_comment,'%special%requests%'))
+ return $o.o_orderkey
+ )
+ }
+ )
+ group by $c_custkey := $co.c_custkey with $co
+ return {
+ "c_custkey": $c_custkey,
+ "c_count": sum(for $i in $co return $i.o_orderkey_count)
+ }
)
-
group by $c_count := $gco.c_count with $gco
let $custdist := count($gco)
order by $custdist desc, $c_count desc
-return {"c_count": $c_count, "custdist": $custdist}
+return {
+ "c_count": $c_count,
+ "custdist": $custdist
+}
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q14_promotion_effect/q14_promotion_effect.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q14_promotion_effect/q14_promotion_effect.3.query.aql
index 040e4ec..786637d 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q14_promotion_effect/q14_promotion_effect.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q14_promotion_effect/q14_promotion_effect.3.query.aql
@@ -2,10 +2,14 @@
for $l in dataset('LineItem')
for $p in dataset('Part')
- where $l.l_partkey = $p.p_partkey and $l.l_shipdate >= '1995-09-01' and $l.l_shipdate < '1995-10-01'
+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
-return 100.00 * sum( for $i in $l
- 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))
+return 100.00 * sum(
+ for $i in $l
+ 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)
+)
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q15_top_supplier/q15_top_supplier.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q15_top_supplier/q15_top_supplier.3.query.aql
index 9f1080d..45b0fad 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q15_top_supplier/q15_top_supplier.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q15_top_supplier/q15_top_supplier.3.query.aql
@@ -1,27 +1,27 @@
use dataverse tpch;
-declare function revenue(){
-for $l in dataset('LineItem')
-where $l.l_shipdate >= '1996-01-01' and $l.l_shipdate < '1996-04-01'
-group by $l_suppkey := $l.l_suppkey with $l
-return {
-"supplier_no": $l_suppkey,
-"total_revenue": sum(for $i in $l return $i.l_extendedprice * (1 - $i.l_discount))
-}
+declare function revenue() {
+ for $l in dataset('LineItem')
+ where $l.l_shipdate >= '1996-01-01' and $l.l_shipdate < '1996-04-01'
+ group by $l_suppkey := $l.l_suppkey with $l
+ return {
+ "supplier_no": $l_suppkey,
+ "total_revenue": sum(for $i in $l return $i.l_extendedprice * (1 - $i.l_discount))
+ }
}
let $m := max(
- for $r2 in revenue()
- return $r2.total_revenue
+ for $r2 in revenue()
+ return $r2.total_revenue
)
for $s in dataset('Supplier')
for $r in revenue()
- where $s.s_suppkey = $r.supplier_no and $r.total_revenue<$m+0.000000001 and $r.total_revenue>$m-0.000000001
+where $s.s_suppkey = $r.supplier_no and $r.total_revenue<$m+0.000000001 and $r.total_revenue>$m-0.000000001
return {
-"s_suppkey": $s.s_suppkey,
-"s_name": $s.s_name,
-"s_address": $s.s_address,
-"s_phone": $s.s_phone,
-"total_revenue": $r.total_revenue
+ "s_suppkey": $s.s_suppkey,
+ "s_name": $s.s_name,
+ "s_address": $s.s_address,
+ "s_phone": $s.s_phone,
+ "total_revenue": $r.total_revenue
}
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.aql
index e5c928e..014ceff 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q16_parts_supplier_relationship/q16_parts_supplier_relationship.3.query.aql
@@ -1,54 +1,48 @@
use dataverse tpch;
declare function tmp(){
-for $psp in (
- for $ps in dataset('Partsupp')
- for $p in dataset('Part')
- where $p.p_partkey = $ps.ps_partkey and $p.p_brand != 'Brand#45'
- and not(like($p.p_type, 'MEDIUM POLISHED%'))
- return
- {
- "p_brand": $p.p_brand,
- "p_type": $p.p_type,
- "p_size": $p.p_size,
- "ps_suppkey": $ps.ps_suppkey
- }
-)
-for $s in dataset('Supplier')
- where $psp.ps_suppkey = $s.s_suppkey and not(like($s.s_comment, '%Customer%Complaints%'))
-return
-{
- "p_brand": $psp.p_brand,
- "p_type": $psp.p_type,
- "p_size": $psp.p_size,
- "ps_suppkey": $psp.ps_suppkey
-}
+ for $psp in (
+ for $ps in dataset('Partsupp')
+ for $p in dataset('Part')
+ where $p.p_partkey = $ps.ps_partkey and $p.p_brand != 'Brand#45'
+ and not(like($p.p_type, 'MEDIUM POLISHED%'))
+ return {
+ "p_brand": $p.p_brand,
+ "p_type": $p.p_type,
+ "p_size": $p.p_size,
+ "ps_suppkey": $ps.ps_suppkey
+ }
+ )
+ for $s in dataset('Supplier')
+ where $psp.ps_suppkey = $s.s_suppkey and not(like($s.s_comment, '%Customer%Complaints%'))
+ return {
+ "p_brand": $psp.p_brand,
+ "p_type": $psp.p_type,
+ "p_size": $psp.p_size,
+ "ps_suppkey": $psp.ps_suppkey
+ }
}
-for $t2 in
-(
-for $t in tmp()
- where $t.p_size = 49 or $t.p_size = 14 or $t.p_size = 23 or
- $t.p_size = 45 or $t.p_size = 19 or $t.p_size = 3 or
- $t.p_size = 36 or $t.p_size = 9
-group by $p_brand1:= $t.p_brand, $p_type1 := $t.p_type,
- $p_size1:= $t.p_size, $ps_suppkey1:=$t.ps_suppkey with $t
-return
-{
-"p_brand": $p_brand1,
- "p_type": $p_type1,
- "p_size": $p_size1,
- "ps_suppkey": $ps_suppkey1
-} )
+for $t2 in (
+ for $t in tmp()
+ where $t.p_size = 49 or $t.p_size = 14 or $t.p_size = 23
+ or $t.p_size = 45 or $t.p_size = 19 or $t.p_size = 3
+ or $t.p_size = 36 or $t.p_size = 9
+ group by $p_brand1:= $t.p_brand, $p_type1 := $t.p_type,
+ $p_size1:= $t.p_size, $ps_suppkey1:=$t.ps_suppkey with $t
+ return {
+ "p_brand": $p_brand1,
+ "p_type": $p_type1,
+ "p_size": $p_size1,
+ "ps_suppkey": $ps_suppkey1
+ }
+)
group by $p_brand := $t2.p_brand, $p_type := $t2.p_type, $p_size := $t2.p_size with $t2
let $supplier_cnt := count(for $i in $t2 return $i.ps_suppkey)
order by $supplier_cnt desc, $p_brand, $p_type, $p_size
return {
-"p_brand": $p_brand,
-"p_type": $p_type,
-"p_size": $p_size,
-"supplier_cnt": $supplier_cnt
+ "p_brand": $p_brand,
+ "p_type": $p_type,
+ "p_size": $p_size,
+ "supplier_cnt": $supplier_cnt
}
-
-
-
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.aql
index 67644d9..99f32c6 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q17_small_quantity_order_revenue/q17_small_quantity_order_revenue.3.query.aql
@@ -1,21 +1,21 @@
use dataverse tpch;
declare function tmp(){
-for $l in dataset('LineItem')
-group by $l_partkey := $l.l_partkey with $l
-return {
-"t_partkey": $l_partkey,
-"t_avg_quantity": 0.2 * avg(for $i in $l return $i.l_quantity)
-}
+ for $l in dataset('LineItem')
+ group by $l_partkey := $l.l_partkey with $l
+ return {
+ "t_partkey": $l_partkey,
+ "t_avg_quantity": 0.2 * avg(for $i in $l return $i.l_quantity)
+ }
}
sum(
-for $l in dataset('LineItem')
-for $p in dataset('Part')
-where $p.p_partkey = $l.l_partkey and $p.p_container = 'MED BOX'
-
-for $t in tmp()
-where $l.l_partkey = $t.t_partkey and $l.l_quantity < $t.t_avg_quantity
-return $l.l_extendedprice
+ for $l in dataset('LineItem')
+ for $p in dataset('Part')
+ where $p.p_partkey = $l.l_partkey and $p.p_container = 'MED BOX'
+ for $t in tmp()
+ where $l.l_partkey = $t.t_partkey
+ and $l.l_quantity < $t.t_avg_quantity
+ return $l.l_extendedprice
)/7.0
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q18_large_volume_customer/q18_large_volume_customer.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q18_large_volume_customer/q18_large_volume_customer.3.query.aql
index df6d9f0..289d900 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q18_large_volume_customer/q18_large_volume_customer.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q18_large_volume_customer/q18_large_volume_customer.3.query.aql
@@ -2,21 +2,18 @@
for $c in dataset('Customer')
for $o in dataset('Orders')
-where
- $c.c_custkey = $o.o_custkey
-for $t in
-(
+where $c.c_custkey = $o.o_custkey
+for $t in (
for $l in dataset('LineItem')
group by $l_orderkey := $l.l_orderkey with $l
- return
- { "l_orderkey": $l_orderkey,
- "t_sum_quantity": sum(for $i in $l return $i.l_quantity) }
+ return {
+ "l_orderkey": $l_orderkey,
+ "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 > 30
for $l in dataset('LineItem')
-where
- $l.l_orderkey = $o.o_orderkey
+where $l.l_orderkey = $o.o_orderkey
group by $c_name := $c.c_name, $c_custkey := $c.c_custkey, $o_orderkey := $o.o_orderkey,
$o_orderdate := $o.o_orderdate, $o_totalprice := $o.o_totalprice with $l
order by $o_totalprice desc, $o_orderdate
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q19_discounted_revenue/q19_discounted_revenue.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q19_discounted_revenue/q19_discounted_revenue.3.query.aql
index ec10939..7e26d94 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q19_discounted_revenue/q19_discounted_revenue.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q19_discounted_revenue/q19_discounted_revenue.3.query.aql
@@ -1,37 +1,33 @@
use dataverse tpch;
+
set import-private-functions 'true';
sum(
-for $l in dataset('LineItem')
-for $p in dataset('Part')
-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 $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_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 $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_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 $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_shipinstruct = 'DELIVER IN PERSON'
- )
- )
-return $l.l_extendedprice * (1 - $l.l_discount)
+ for $l in dataset('LineItem')
+ for $p in dataset('Part')
+ 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 $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_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 $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_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 $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_shipinstruct = 'DELIVER IN PERSON'
+ )
+ )
+ return $l.l_extendedprice * (1 - $l.l_discount)
)
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q1_pricing_summary_report_nt/q1_pricing_summary_report_nt.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q1_pricing_summary_report_nt/q1_pricing_summary_report_nt.3.query.aql
index c27be0a..599f51e 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q1_pricing_summary_report_nt/q1_pricing_summary_report_nt.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q1_pricing_summary_report_nt/q1_pricing_summary_report_nt.3.query.aql
@@ -4,7 +4,8 @@
for $l in dataset('LineItem')
where $l.l_shipdate <= '1998-09-02'
/*+ hash*/
-group by $l_returnflag := $l.l_returnflag, $l_linestatus := $l.l_linestatus
+group by $l_returnflag := $l.l_returnflag,
+ $l_linestatus := $l.l_linestatus
with $l
order by $l_returnflag, $l_linestatus
return {
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q20_potential_part_promotion/q20_potential_part_promotion.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q20_potential_part_promotion/q20_potential_part_promotion.3.query.aql
index a5a5bbe..b023966 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q20_potential_part_promotion/q20_potential_part_promotion.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q20_potential_part_promotion/q20_potential_part_promotion.3.query.aql
@@ -1,39 +1,48 @@
use dataverse tpch;
for $t3 in (
-for $t2 in (
- for $l in dataset('LineItem')
- group by $l_partkey:=$l.l_partkey, $l_suppkey:=$l.l_suppkey with $l
- return {
- "l_partkey": $l_partkey,
- "l_suppkey": $l_suppkey,
- "sum_quantity": 0.5 * sum(for $i in $l return $i.l_quantity)
-})
-for $pst1 in (
- for $ps in dataset('Partsupp')
- for $t1 in
- (
- for $p in dataset('Part')
- distinct by $p.p_partkey
- return {"p_partkey": $p.p_partkey}
- )
- where $ps.ps_partkey = $t1.p_partkey
- return {"ps_suppkey": $ps.ps_suppkey, "ps_partkey": $ps.ps_partkey, "ps_availqty": $ps.ps_availqty}
+ for $t2 in (
+ for $l in dataset('LineItem')
+ group by $l_partkey:=$l.l_partkey, $l_suppkey:=$l.l_suppkey with $l
+ return {
+ "l_partkey": $l_partkey,
+ "l_suppkey": $l_suppkey,
+ "sum_quantity": 0.5 * sum(for $i in $l return $i.l_quantity)
+ }
+ )
+ for $pst1 in (
+ for $ps in dataset('Partsupp')
+ for $t1 in (
+ for $p in dataset('Part')
+ distinct by $p.p_partkey
+ return { "p_partkey": $p.p_partkey }
+ )
+ where $ps.ps_partkey = $t1.p_partkey
+ return {
+ "ps_suppkey": $ps.ps_suppkey,
+ "ps_partkey": $ps.ps_partkey,
+ "ps_availqty": $ps.ps_availqty
+ }
+ )
+ where $pst1.ps_partkey = $t2.l_partkey and $pst1.ps_suppkey = $t2.l_suppkey
+ and $pst1.ps_availqty > $t2.sum_quantity
+ distinct by $pst1.ps_suppkey
+ return { "ps_suppkey": $pst1.ps_suppkey }
)
-where $pst1.ps_partkey = $t2.l_partkey and $pst1.ps_suppkey = $t2.l_suppkey
- and $pst1.ps_availqty > $t2.sum_quantity
-distinct by $pst1.ps_suppkey
-return {"ps_suppkey": $pst1.ps_suppkey}
-)
-
for $t4 in (
- for $n in dataset('Nation')
- for $s in dataset('Supplier')
- where $s.s_nationkey = $n.n_nationkey
- return {"s_name": $s.s_name, "s_address": $s.s_address, "s_suppkey": $s.s_suppkey}
+ for $n in dataset('Nation')
+ for $s in dataset('Supplier')
+ where $s.s_nationkey = $n.n_nationkey
+ return {
+ "s_name": $s.s_name,
+ "s_address": $s.s_address,
+ "s_suppkey": $s.s_suppkey
+ }
)
-
where $t3.ps_suppkey = $t4.s_suppkey
order by $t4.s_name
-return {"s_name": $t4.s_name, "s_address": $t4.s_address}
+return {
+ "s_name": $t4.s_name,
+ "s_address": $t4.s_address
+}
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.aql
index eb94678..e99dea2 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q21_suppliers_who_kept_orders_waiting/q21_suppliers_who_kept_orders_waiting.3.query.aql
@@ -1,78 +1,76 @@
use dataverse tpch;
-declare function tmp1()
-{
-for $l2 in
-(
- for $l in dataset('LineItem')
- group by $l_orderkey1 := $l.l_orderkey, $l_suppkey1 := $l.l_suppkey with $l
- return {
- "l_orderkey": $l_orderkey1,
- "l_suppkey": $l_suppkey1
- }
-)
-group by $l_orderkey := $l2.l_orderkey with $l2
-return {
- "l_orderkey": $l_orderkey,
- "count_suppkey": count(for $i in $l2 return $i.l_suppkey),
- "max_suppkey": max(for $i in $l2 return $i.l_suppkey)
-}
+declare function tmp1() {
+ for $l2 in (
+ for $l in dataset('LineItem')
+ group by $l_orderkey1 := $l.l_orderkey, $l_suppkey1 := $l.l_suppkey with $l
+ return {
+ "l_orderkey": $l_orderkey1,
+ "l_suppkey": $l_suppkey1
+ }
+ )
+ group by $l_orderkey := $l2.l_orderkey with $l2
+ return {
+ "l_orderkey": $l_orderkey,
+ "count_suppkey": count(for $i in $l2 return $i.l_suppkey),
+ "max_suppkey": max(for $i in $l2 return $i.l_suppkey)
+ }
}
-declare function tmp2()
-{
-for $l2 in
-(
- for $l in dataset('LineItem')
- where $l.l_receiptdate > $l.l_commitdate
- group by $l_orderkey1 := $l.l_orderkey, $l_suppkey1 := $l.l_suppkey with $l
- return {
- "l_orderkey": $l_orderkey1,
- "l_suppkey": $l_suppkey1
- }
-)
-group by $l_orderkey := $l2.l_orderkey with $l2
-return {
-"l_orderkey": $l_orderkey,
-"count_suppkey": count(for $i in $l2 return $i.l_suppkey),
-"max_suppkey": max(for $i in $l2 return $i.l_suppkey)
-}
+declare function tmp2() {
+ for $l2 in (
+ for $l in dataset('LineItem')
+ where $l.l_receiptdate > $l.l_commitdate
+ group by $l_orderkey1 := $l.l_orderkey, $l_suppkey1 := $l.l_suppkey with $l
+ return {
+ "l_orderkey": $l_orderkey1,
+ "l_suppkey": $l_suppkey1
+ }
+ )
+ group by $l_orderkey := $l2.l_orderkey with $l2
+ return {
+ "l_orderkey": $l_orderkey,
+ "count_suppkey": count(for $i in $l2 return $i.l_suppkey),
+ "max_suppkey": max(for $i in $l2 return $i.l_suppkey)
+ }
}
for $t4 in (
-for $t3 in (
- for $l in dataset('LineItem')
- for $ns in (
- for $n in dataset('Nation')
- for $s in dataset('Supplier')
- where $s.s_nationkey = $n.n_nationkey
- return {"s_name": $s.s_name, "s_suppkey": $s.s_suppkey}
- )
- where $ns.s_suppkey = $l.l_suppkey and $l.l_receiptdate > $l.l_commitdate
-
- for $o in dataset('Orders')
- where $o.o_orderkey = $l.l_orderkey
-
- for $t1 in tmp1()
- where $l.l_orderkey = $t1.l_orderkey
-
- return {"s_name": $ns.s_name, "l_orderkey": $t1.l_orderkey, "l_suppkey": $l.l_suppkey}
+ for $t3 in (
+ for $l in dataset('LineItem')
+ for $ns in (
+ for $n in dataset('Nation')
+ for $s in dataset('Supplier')
+ where $s.s_nationkey = $n.n_nationkey
+ return {
+ "s_name": $s.s_name,
+ "s_suppkey": $s.s_suppkey
+ }
+ )
+ where $ns.s_suppkey = $l.l_suppkey and $l.l_receiptdate > $l.l_commitdate
+ for $o in dataset('Orders')
+ where $o.o_orderkey = $l.l_orderkey
+ for $t1 in tmp1()
+ where $l.l_orderkey = $t1.l_orderkey
+ return {
+ "s_name": $ns.s_name,
+ "l_orderkey": $t1.l_orderkey,
+ "l_suppkey": $l.l_suppkey}
+ )
+ for $t2 in tmp2()
+ where $t2.count_suppkey >= 0 and $t3.l_orderkey = $t2.l_orderkey
+ return {
+ "s_name": $t3.s_name,
+ "l_suppkey": $t3.l_suppkey,
+ "l_orderkey": $t2.l_orderkey,
+ "count_suppkey": $t2.count_suppkey,
+ "max_suppkey": $t2.max_suppkey
+ }
)
-
-for $t2 in tmp2()
- where $t2.count_suppkey >= 0 and $t3.l_orderkey = $t2.l_orderkey
-
-return {
-"s_name": $t3.s_name,
-"l_suppkey": $t3.l_suppkey,
-"l_orderkey": $t2.l_orderkey,
-"count_suppkey": $t2.count_suppkey,
-"max_suppkey": $t2.max_suppkey
-} )
group by $s_name := $t4.s_name with $t4
let $numwait := count($t4)
order by $numwait desc, $s_name
return {
-"s_name": $s_name,
-"numwait": $numwait
+ "s_name": $s_name,
+ "numwait": $numwait
}
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.aql
index a07e4ad..8913818 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q22_global_sales_opportunity/q22_global_sales_opportunity.3.query.aql
@@ -1,30 +1,25 @@
use dataverse tpch;
-declare function q22_customer_tmp(){
-for $c in dataset('Customer')
-return {
-"c_acctbal": $c.c_acctbal,
-"c_custkey": $c.c_custkey,
-"cntrycode": substring($c.c_phone, 1, 2)
-}
+declare function q22_customer_tmp() {
+ for $c in dataset('Customer')
+ return {
+ "c_acctbal": $c.c_acctbal,
+ "c_custkey": $c.c_custkey,
+ "cntrycode": substring($c.c_phone, 1, 2)
+ }
}
-
-let $avg := avg( for $c in dataset('Customer')
- where $c.c_acctbal > 0.00
- return $c.c_acctbal
- )
+let $avg := avg(
+ for $c in dataset('Customer')
+ where $c.c_acctbal > 0.00
+ return $c.c_acctbal
+)
for $ct in q22_customer_tmp()
where $ct.c_acctbal > $avg
-
group by $cntrycode := $ct.cntrycode with $ct
order by $cntrycode
-
return {
-"cntrycode": $cntrycode,
-"numcust": count($ct),
-"totacctbal": sum(for $i in $ct return $i.c_acctbal)
+ "cntrycode": $cntrycode,
+ "numcust": count($ct),
+ "totacctbal": sum(for $i in $ct return $i.c_acctbal)
}
-
-
-
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q2_minimum_cost_supplier/q2_minimum_cost_supplier.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q2_minimum_cost_supplier/q2_minimum_cost_supplier.3.query.aql
index c13dbb9..16cf8b2 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q2_minimum_cost_supplier/q2_minimum_cost_supplier.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q2_minimum_cost_supplier/q2_minimum_cost_supplier.3.query.aql
@@ -1,88 +1,102 @@
use dataverse tpch;
-declare function tmp1(){
-for $p in dataset('Part')
-for $pssrn in
-(
- for $ps in dataset('Partsupp')
- for $srn in
- (
- for $s in dataset('Supplier')
- for $rn in
- (
- for $r in dataset('Region')
- for $n in dataset('Nation')
- where $n.n_regionkey = $r.r_regionkey and $r.r_name = 'EUROPE'
- return {"n_nationkey": $n.n_nationkey, "n_name": $n.n_name}
- )
- where $s.s_nationkey = $rn.n_nationkey
- return {"s_suppkey": $s.s_suppkey, "n_name": $rn.n_name,
- "s_name": $s.s_name,
- "s_acctbal": $s.s_acctbal,
- "s_address": $s.s_address,
- "s_phone": $s.s_phone,
- "s_comment": $s.s_comment}
- )
- where $srn.s_suppkey = $ps.ps_suppkey
- return {"n_name": $srn.n_name, "p_partkey": $ps.ps_partkey, "ps_supplycost": $ps.ps_supplycost,
- "s_name": $srn.s_name,
- "s_acctbal": $srn.s_acctbal,
- "s_address": $srn.s_address,
- "s_phone": $srn.s_phone,
- "s_comment": $srn.s_comment}
-)
-where $p.p_partkey = $pssrn.p_partkey and like($p.p_type, '%BRASS')
-return
-{"s_acctbal": $pssrn.s_acctbal,
-"s_name": $pssrn.s_name,
-"n_name": $pssrn.n_name,
-"p_partkey": $p.p_partkey,
-"ps_supplycost": $pssrn.ps_supplycost,
-"p_mfgr": $p.p_mfgr,
-"s_address": $pssrn.s_address,
-"s_phone": $pssrn.s_phone,
-"s_comment": $pssrn.s_comment
-}
+declare function tmp1() {
+ for $p in dataset('Part')
+ for $pssrn in (
+ for $ps in dataset('Partsupp')
+ for $srn in (
+ for $s in dataset('Supplier')
+ for $rn in (
+ for $r in dataset('Region')
+ for $n in dataset('Nation')
+ where $n.n_regionkey = $r.r_regionkey and $r.r_name = 'EUROPE'
+ return {
+ "n_nationkey": $n.n_nationkey,
+ "n_name": $n.n_name
+ }
+ )
+ where $s.s_nationkey = $rn.n_nationkey
+ return {
+ "s_suppkey": $s.s_suppkey,
+ "n_name": $rn.n_name,
+ "s_name": $s.s_name,
+ "s_acctbal": $s.s_acctbal,
+ "s_address": $s.s_address,
+ "s_phone": $s.s_phone,
+ "s_comment": $s.s_comment
+ }
+ )
+ where $srn.s_suppkey = $ps.ps_suppkey
+ return {
+ "n_name": $srn.n_name,
+ "p_partkey": $ps.ps_partkey,
+ "ps_supplycost": $ps.ps_supplycost,
+ "s_name": $srn.s_name,
+ "s_acctbal": $srn.s_acctbal,
+ "s_address": $srn.s_address,
+ "s_phone": $srn.s_phone,
+ "s_comment": $srn.s_comment
+ }
+ )
+ where $p.p_partkey = $pssrn.p_partkey and like($p.p_type, '%BRASS')
+ return {
+ "s_acctbal": $pssrn.s_acctbal,
+ "s_name": $pssrn.s_name,
+ "n_name": $pssrn.n_name,
+ "p_partkey": $p.p_partkey,
+ "ps_supplycost": $pssrn.ps_supplycost,
+ "p_mfgr": $p.p_mfgr,
+ "s_address": $pssrn.s_address,
+ "s_phone": $pssrn.s_phone,
+ "s_comment": $pssrn.s_comment
+ }
}
declare function tmp2(){
-for $p in dataset('Part')
-for $pssrn in
-(
- for $ps in dataset('Partsupp')
- for $srn in
- (
- for $s in dataset('Supplier')
- for $rn in
- (
- for $r in dataset('Region')
- for $n in dataset('Nation')
- where $n.n_regionkey = $r.r_regionkey and $r.r_name = 'EUROPE'
- return {"n_nationkey": $n.n_nationkey, "n_name": $n.n_name}
- )
- where $s.s_nationkey = $rn.n_nationkey
- return {"s_suppkey": $s.s_suppkey, "n_name": $rn.n_name,
- "s_name": $s.s_name,
- "s_acctbal": $s.s_acctbal,
- "s_address": $s.s_address,
- "s_phone": $s.s_phone,
- "s_comment": $s.s_comment}
- )
- where $srn.s_suppkey = $ps.ps_suppkey
- return {"n_name": $srn.n_name, "p_partkey": $ps.ps_partkey, "ps_supplycost": $ps.ps_supplycost,
- "s_name": $srn.s_name,
- "s_acctbal": $srn.s_acctbal,
- "s_address": $srn.s_address,
- "s_phone": $srn.s_phone,
- "s_comment": $srn.s_comment}
-)
-where $p.p_partkey = $pssrn.p_partkey and like($p.p_type, '%BRASS')
-/*+ hash*/
-group by $p_partkey := $pssrn.p_partkey
-with $pssrn
-return {"p_partkey": $p_partkey,
-"ps_min_supplycost": min(for $i in $pssrn return $i.ps_supplycost)
-}
+ for $p in dataset('Part')
+ for $pssrn in (
+ for $ps in dataset('Partsupp')
+ for $srn in (
+ for $s in dataset('Supplier')
+ for $rn in (
+ for $r in dataset('Region')
+ for $n in dataset('Nation')
+ where $n.n_regionkey = $r.r_regionkey and $r.r_name = 'EUROPE'
+ return {
+ "n_nationkey": $n.n_nationkey,
+ "n_name": $n.n_name
+ }
+ )
+ where $s.s_nationkey = $rn.n_nationkey
+ return {
+ "s_suppkey": $s.s_suppkey,
+ "n_name": $rn.n_name,
+ "s_name": $s.s_name,
+ "s_acctbal": $s.s_acctbal,
+ "s_address": $s.s_address,
+ "s_phone": $s.s_phone,
+ "s_comment": $s.s_comment
+ }
+ )
+ where $srn.s_suppkey = $ps.ps_suppkey
+ return {
+ "n_name": $srn.n_name,
+ "p_partkey": $ps.ps_partkey,
+ "ps_supplycost": $ps.ps_supplycost,
+ "s_name": $srn.s_name,
+ "s_acctbal": $srn.s_acctbal,
+ "s_address": $srn.s_address,
+ "s_phone": $srn.s_phone,
+ "s_comment": $srn.s_comment
+ }
+ )
+ where $p.p_partkey = $pssrn.p_partkey and like($p.p_type, '%BRASS')
+ /*+ hash*/
+ group by $p_partkey := $pssrn.p_partkey with $pssrn
+ return {
+ "p_partkey": $p_partkey,
+ "ps_min_supplycost": min(for $i in $pssrn return $i.ps_supplycost)
+ }
}
for $t2 in tmp2()
@@ -92,12 +106,12 @@
limit 100
return
{
-"s_acctbal":$t1.s_acctbal,
-"s_name":$t1.s_name,
-"n_name":$t1.n_name,
-"p_partkey":$t1.p_partkey,
-"p_mfgr":$t1.p_mfgr,
-"s_address":$t1.s_address,
-"s_phone":$t1.s_phone,
-"s_comment":$t1.s_comment
+ "s_acctbal": $t1.s_acctbal,
+ "s_name": $t1.s_name,
+ "n_name": $t1.n_name,
+ "p_partkey": $t1.p_partkey,
+ "p_mfgr": $t1.p_mfgr,
+ "s_address": $t1.s_address,
+ "s_phone": $t1.s_phone,
+ "s_comment": $t1.s_comment
}
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q4_order_priority/q4_order_priority.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q4_order_priority/q4_order_priority.3.query.aql
index 4079a25..9912c85 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q4_order_priority/q4_order_priority.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q4_order_priority/q4_order_priority.3.query.aql
@@ -2,23 +2,20 @@
declare function tmp()
{
-for $l in dataset('LineItem')
-where $l.l_commitdate<$l.l_receiptdate
-distinct by $l.l_orderkey
-return {"o_orderkey": $l.l_orderkey}
+ for $l in dataset('LineItem')
+ where $l.l_commitdate < $l.l_receiptdate
+ distinct by $l.l_orderkey
+ return { "o_orderkey": $l.l_orderkey }
}
for $o in dataset('Orders')
for $t in tmp()
-where
-$o.o_orderkey = $t.o_orderkey and
-$o.o_orderdate >= '1993-07-01' and $o.o_orderdate < '1993-10-01'
-group by $o_orderpriority := $o.o_orderpriority
-with $o
+where $o.o_orderkey = $t.o_orderkey and
+ $o.o_orderdate >= '1993-07-01' and $o.o_orderdate < '1993-10-01'
+group by $o_orderpriority := $o.o_orderpriority with $o
order by $o_orderpriority
-return
-{
-"order_priority": $o_orderpriority,
-"count": count($o)
+return {
+ "order_priority": $o_orderpriority,
+ "count": count($o)
}
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q5_local_supplier_volume/q5_local_supplier_volume.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q5_local_supplier_volume/q5_local_supplier_volume.3.query.aql
index d654ce1..e0e0563 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q5_local_supplier_volume/q5_local_supplier_volume.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q5_local_supplier_volume/q5_local_supplier_volume.3.query.aql
@@ -1,27 +1,46 @@
use dataverse tpch;
for $c in dataset('Customer')
-for $o1 in
-( for $o in dataset('Orders')
- for $l1 in (
- for $l in dataset('LineItem')
- for $s1 in
- ( for $s in dataset('Supplier')
- for $n1 in
- ( for $n in dataset('Nation')
- for $r in dataset('Region')
- where $n.n_regionkey = $r.r_regionkey
- return
- {"n_name": $n.n_name, "n_nationkey": $n.n_nationkey} )
- where $s.s_nationkey = $n1.n_nationkey
- return
- { "n_name": $n1.n_name, "s_suppkey": $s.s_suppkey, "s_nationkey": $s.s_nationkey } )
- where $l.l_suppkey = $s1.s_suppkey
- return
- { "n_name": $s1.n_name, "l_extendedprice": $l.l_extendedprice, "l_discount": $l.l_discount, "l_orderkey": $l.l_orderkey, "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'
- return
- { "n_name": $l1.n_name, "l_extendedprice": $l1.l_extendedprice, "l_discount": $l1.l_discount, "s_nationkey": $l1.s_nationkey, "o_custkey": $o.o_custkey } )
+for $o1 in (
+ for $o in dataset('Orders')
+ for $l1 in (
+ for $l in dataset('LineItem')
+ for $s1 in (
+ for $s in dataset('Supplier')
+ for $n1 in (
+ for $n in dataset('Nation')
+ for $r in dataset('Region')
+ where $n.n_regionkey = $r.r_regionkey
+ return {
+ "n_name": $n.n_name,
+ "n_nationkey": $n.n_nationkey
+ }
+ )
+ where $s.s_nationkey = $n1.n_nationkey
+ return {
+ "n_name": $n1.n_name,
+ "s_suppkey": $s.s_suppkey,
+ "s_nationkey": $s.s_nationkey
+ }
+ )
+ where $l.l_suppkey = $s1.s_suppkey
+ return {
+ "n_name": $s1.n_name,
+ "l_extendedprice": $l.l_extendedprice,
+ "l_discount": $l.l_discount,
+ "l_orderkey": $l.l_orderkey,
+ "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'
+ return {
+ "n_name": $l1.n_name,
+ "l_extendedprice": $l1.l_extendedprice,
+ "l_discount": $l1.l_discount,
+ "s_nationkey": $l1.s_nationkey,
+ "o_custkey": $o.o_custkey
+ }
+)
where $c.c_nationkey = $o1.s_nationkey and $c.c_custkey = $o1.o_custkey
/*+ hash*/
group by $n_name := $o1.n_name with $o1
@@ -31,5 +50,7 @@
$i.l_extendedprice * (1 - $i.l_discount)
)
order by $revenue desc
-return
- { "n_name": $n_name, "revenue": $revenue }
+return {
+ "n_name": $n_name,
+ "revenue": $revenue
+}
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q6_forecast_revenue_change/q6_forecast_revenue_change.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q6_forecast_revenue_change/q6_forecast_revenue_change.3.query.aql
index 8ac1613..213b41d 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q6_forecast_revenue_change/q6_forecast_revenue_change.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q6_forecast_revenue_change/q6_forecast_revenue_change.3.query.aql
@@ -1,14 +1,13 @@
use dataverse tpch;
let $revenue := sum(
-for $l in dataset('LineItem')
-where
- $l.l_shipdate >= '1994-01-01'
- and $l.l_shipdate < '1995-01-01'
- and $l.l_discount >= 0.05 and $l.l_discount <= 0.07
- and $l.l_quantity < 24
-return $l.l_extendedprice * $l.l_discount
+ for $l in dataset('LineItem')
+ where $l.l_shipdate >= '1994-01-01'
+ and $l.l_shipdate < '1995-01-01'
+ and $l.l_discount >= 0.05 and $l.l_discount <= 0.07
+ and $l.l_quantity < 24
+ return $l.l_extendedprice * $l.l_discount
)
return {
-"revenue": $revenue
+ "revenue": $revenue
}
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q7_volume_shipping/q7_volume_shipping.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q7_volume_shipping/q7_volume_shipping.3.query.aql
index 843204a..7b9e2b9 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q7_volume_shipping/q7_volume_shipping.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q7_volume_shipping/q7_volume_shipping.3.query.aql
@@ -1,70 +1,63 @@
use dataverse tpch;
-declare function q7_volume_shipping_tmp(){
-for $n1 in dataset('Nation')
-for $n2 in dataset('Nation')
- where $n2.n_name='GERMANY' or $n1.n_name='GERMANY'
-return
-{
- "supp_nation": $n1.n_name,
- "cust_nation": $n2.n_name,
- "s_nationkey": $n1.n_nationkey,
+declare function q7_volume_shipping_tmp() {
+ for $n1 in dataset('Nation')
+ for $n2 in dataset('Nation')
+ where $n2.n_name='GERMANY' or $n1.n_name='GERMANY'
+ return {
+ "supp_nation": $n1.n_name,
+ "cust_nation": $n2.n_name,
+ "s_nationkey": $n1.n_nationkey,
"c_nationkey": $n2.n_nationkey
-}
+ }
}
for $locs in (
- for $loc in (
- for $lo in
- (
- for $l in dataset('LineItem')
- for $o in dataset('Orders')
- where
- $o.o_orderkey = $l.l_orderkey and $l.l_shipdate >= '1992-01-01'
- and $l.l_shipdate <= '1996-12-31'
- return
- {
- "l_shipdate": $l.l_shipdate,
- "l_extendedprice": $l.l_extendedprice,
- "l_discount": $l.l_discount,
- "l_suppkey": $l.l_suppkey,
- "o_custkey": $o.o_custkey
- }
- )
- for $c in dataset('Customer')
- where
- $c.c_custkey = $lo.o_custkey
- return
- {
- "l_shipdate": $lo.l_shipdate,
- "l_extendedprice": $lo.l_extendedprice,
- "l_discount": $lo.l_discount,
- "l_suppkey": $lo.l_suppkey,
- "c_nationkey": $c.c_nationkey
- }
- )
- for $s in dataset('Supplier')
- where
- $s.s_suppkey = $loc.l_suppkey
- return {
- "l_shipdate": $loc.l_shipdate,
- "l_extendedprice": $loc.l_extendedprice,
- "l_discount": $loc.l_discount,
- "c_nationkey": $loc.c_nationkey,
- "s_nationkey": $s.s_nationkey
- }
+ for $loc in (
+ for $lo in (
+ for $l in dataset('LineItem')
+ for $o in dataset('Orders')
+ where $o.o_orderkey = $l.l_orderkey and $l.l_shipdate >= '1992-01-01'
+ and $l.l_shipdate <= '1996-12-31'
+ return {
+ "l_shipdate": $l.l_shipdate,
+ "l_extendedprice": $l.l_extendedprice,
+ "l_discount": $l.l_discount,
+ "l_suppkey": $l.l_suppkey,
+ "o_custkey": $o.o_custkey
+ }
+ )
+ for $c in dataset('Customer')
+ where $c.c_custkey = $lo.o_custkey
+ return {
+ "l_shipdate": $lo.l_shipdate,
+ "l_extendedprice": $lo.l_extendedprice,
+ "l_discount": $lo.l_discount,
+ "l_suppkey": $lo.l_suppkey,
+ "c_nationkey": $c.c_nationkey
+ }
+ )
+ for $s in dataset('Supplier')
+ where $s.s_suppkey = $loc.l_suppkey
+ return {
+ "l_shipdate": $loc.l_shipdate,
+ "l_extendedprice": $loc.l_extendedprice,
+ "l_discount": $loc.l_discount,
+ "c_nationkey": $loc.c_nationkey,
+ "s_nationkey": $s.s_nationkey
+ }
)
for $t in q7_volume_shipping_tmp()
-where
- $locs.c_nationkey = $t.c_nationkey and $locs.s_nationkey = $t.s_nationkey
+where $locs.c_nationkey = $t.c_nationkey
+ and $locs.s_nationkey = $t.s_nationkey
let $l_year0 := get-year($locs.l_shipdate)
group by $supp_nation := $t.supp_nation, $cust_nation := $t.cust_nation, $l_year := $l_year0
with $locs
let $revenue := sum(for $i in $locs return $i.l_extendedprice * (1 - $i.l_discount))
order by $supp_nation, $cust_nation, $l_year
return {
- "supp_nation": $supp_nation,
- "cust_nation": $cust_nation,
- "l_year": $l_year,
- "revenue": $revenue
+ "supp_nation": $supp_nation,
+ "cust_nation": $cust_nation,
+ "l_year": $l_year,
+ "revenue": $revenue
}
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q8_national_market_share/q8_national_market_share.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q8_national_market_share/q8_national_market_share.3.query.aql
index cac1248..3b28d11 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q8_national_market_share/q8_national_market_share.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q8_national_market_share/q8_national_market_share.3.query.aql
@@ -1,72 +1,73 @@
use dataverse tpch;
for $t in (
-for $slnrcop in
-(for $s in dataset("Supplier")
-for $lnrcop in
-(
-for $lnrco in
-(
-for $l in dataset('LineItem')
-for $nrco in (
- for $o in dataset('Orders')
- for $nrc in (
- for $c in dataset('Customer')
- for $nr in (
- for $n1 in dataset('Nation')
- for $r1 in dataset('Region')
- where $n1.n_regionkey = $r1.r_regionkey and $r1.r_name = 'AMERICA'
- return {"n_nationkey": $n1.n_nationkey}
- )
- where $c.c_nationkey = $nr.n_nationkey
- return {"c_custkey": $c.c_custkey}
- )
- where $nrc.c_custkey = $o.o_custkey
- return {
- "o_orderdate" : $o.o_orderdate,
- "o_orderkey": $o.o_orderkey
- }
+ for $slnrcop in (
+ for $s in dataset("Supplier")
+ for $lnrcop in (
+ for $lnrco in (
+ for $l in dataset('LineItem')
+ for $nrco in (
+ for $o in dataset('Orders')
+ for $nrc in (
+ for $c in dataset('Customer')
+ for $nr in (
+ for $n1 in dataset('Nation')
+ for $r1 in dataset('Region')
+ where $n1.n_regionkey = $r1.r_regionkey and $r1.r_name = 'AMERICA'
+ return { "n_nationkey": $n1.n_nationkey }
+ )
+ where $c.c_nationkey = $nr.n_nationkey
+ return { "c_custkey": $c.c_custkey }
+ )
+ where $nrc.c_custkey = $o.o_custkey
+ return {
+ "o_orderdate" : $o.o_orderdate,
+ "o_orderkey": $o.o_orderkey
+ }
+ )
+ where $l.l_orderkey = $nrco.o_orderkey
+ and $nrco.o_orderdate >= '1995-01-01'
+ and $nrco.o_orderdate < '1996-12-31'
+ return {
+ "o_orderdate": $nrco.o_orderdate,
+ "l_partkey": $l.l_partkey,
+ "l_discount": $l.l_discount,
+ "l_extendedprice": $l.l_extendedprice,
+ "l_suppkey": $l.l_suppkey
+ }
+ )
+ for $p in dataset("Part")
+ where $p.p_partkey = $lnrco.l_partkey and $p.p_type = 'ECONOMY ANODIZED STEEL'
+ return {
+ "o_orderdate": $lnrco.o_orderdate,
+ "l_discount": $lnrco.l_discount,
+ "l_extendedprice": $lnrco.l_extendedprice,
+ "l_suppkey": $lnrco.l_suppkey
+ }
+ )
+ where $s.s_suppkey = $lnrcop.l_suppkey
+ return {
+ "o_orderdate": $lnrcop.o_orderdate,
+ "l_discount": $lnrcop.l_discount,
+ "l_extendedprice": $lnrcop.l_extendedprice,
+ "l_suppkey": $lnrcop.l_suppkey,
+ "s_nationkey": $s.s_nationkey
+ }
+ )
+ for $n2 in dataset('Nation')
+ where $slnrcop.s_nationkey = $n2.n_nationkey
+ let $o_year := get-year($slnrcop.o_orderdate)
+ return {
+ "year": $o_year,
+ "revenue": $slnrcop.l_extendedprice *(1-$slnrcop.l_discount),
+ "s_name": $n2.n_name
+ }
)
-where $l.l_orderkey = $nrco.o_orderkey and $nrco.o_orderdate >= '1995-01-01'
- and $nrco.o_orderdate < '1996-12-31'
-return {
- "o_orderdate": $nrco.o_orderdate,
- "l_partkey": $l.l_partkey,
- "l_discount": $l.l_discount,
- "l_extendedprice": $l.l_extendedprice,
- "l_suppkey": $l.l_suppkey
-})
-for $p in dataset("Part")
-where $p.p_partkey = $lnrco.l_partkey and $p.p_type = 'ECONOMY ANODIZED STEEL'
-return {
- "o_orderdate": $lnrco.o_orderdate,
- "l_discount": $lnrco.l_discount,
- "l_extendedprice": $lnrco.l_extendedprice,
- "l_suppkey": $lnrco.l_suppkey
-}
-)
-where $s.s_suppkey = $lnrcop.l_suppkey
-return {
- "o_orderdate": $lnrcop.o_orderdate,
- "l_discount": $lnrcop.l_discount,
- "l_extendedprice": $lnrcop.l_extendedprice,
- "l_suppkey": $lnrcop.l_suppkey,
- "s_nationkey": $s.s_nationkey
-})
-
-for $n2 in dataset('Nation')
-where $slnrcop.s_nationkey = $n2.n_nationkey
-let $o_year := get-year($slnrcop.o_orderdate)
-return {
- "year": $o_year,
- "revenue": $slnrcop.l_extendedprice *(1-$slnrcop.l_discount),
- "s_name": $n2.n_name
-})
group by $year := $t.year with $t
order by $year
return {
- "year": $year,
- "mkt_share":sum(for $i in $t return switch-case($i.s_name='BRAZIL', true, $i.revenue, false, 0.0))/
- sum(for $i in $t return $i.revenue)
+ "year": $year,
+ "mkt_share": sum(for $i in $t return switch-case($i.s_name='BRAZIL', true, $i.revenue, false, 0.0))/
+ sum(for $i in $t return $i.revenue)
}
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/q9_product_type_profit_nt/q9_product_type_profit_nt.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/q9_product_type_profit_nt/q9_product_type_profit_nt.3.query.aql
index 2e54083..68eb8f9 100644
--- a/asterix-app/src/test/resources/runtimets/queries/tpch/q9_product_type_profit_nt/q9_product_type_profit_nt.3.query.aql
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/q9_product_type_profit_nt/q9_product_type_profit_nt.3.query.aql
@@ -1,40 +1,67 @@
use dataverse tpch;
-
-for $profit in
-( for $o in dataset('Orders')
- for $l3 in
- ( for $p in dataset('Part')
- for $l2 in
- ( for $ps in dataset('PartSupp')
- for $l1 in
- ( for $s1 in
- ( for $s in dataset('Supplier')
- for $n in dataset('Nation')
- where $n.n_nationkey = $s.s_nationkey
- return
- { "s_suppkey": $s.s_suppkey, "n_name": $n.n_name} )
- for $l in dataset('LineItem')
- where $s1.s_suppkey = $l.l_suppkey
- return
- { "l_suppkey": $l.l_suppkey, "l_extendedprice": $l.l_extendedprice, "l_discount": $l.l_discount,
- "l_quantity": $l.l_quantity, "l_partkey": $l.l_partkey, "l_orderkey": $l.l_orderkey, "n_name": $s1.n_name } )
- where $ps.ps_suppkey = $l1.l_suppkey and $ps.ps_partkey = $l1.l_partkey
- return
- { "l_extendedprice": $l1.l_extendedprice, "l_discount": $l1.l_discount, "l_quantity": $l1.l_quantity,
- "l_partkey": $l1.l_partkey, "l_orderkey": $l1.l_orderkey, "n_name": $l1.n_name, "ps_supplycost": $ps.ps_supplycost } )
- where contains($p.p_name, 'green') and $p.p_partkey = $l2.l_partkey
- return
- { "l_extendedprice": $l2.l_extendedprice, "l_discount": $l2.l_discount, "l_quantity": $l2.l_quantity,
- "l_orderkey": $l2.l_orderkey, "n_name": $l2.n_name, "ps_supplycost": $l2.ps_supplycost } )
- where $o.o_orderkey = $l3.l_orderkey
- let $amount := $l3.l_extendedprice * (1 - $l3.l_discount) - $l3.ps_supplycost * $l3.l_quantity
- let $o_year := get-year($o.o_orderdate)
- return
- { "nation": $l3.n_name, "o_year": $o_year, "amount": $amount } )
- group by $nation := $profit.nation, $o_year := $profit.o_year with $profit
- order by $nation, $o_year desc
- return
- { "nation": $nation,
- "o_year": $o_year,
- "sum_profit": sum( for $pr in $profit return $pr.amount ) }
-
+
+for $profit in (
+ for $o in dataset('Orders')
+ for $l3 in (
+ for $p in dataset('Part')
+ for $l2 in (
+ for $ps in dataset('PartSupp')
+ for $l1 in (
+ for $s1 in (
+ for $s in dataset('Supplier')
+ for $n in dataset('Nation')
+ where $n.n_nationkey = $s.s_nationkey
+ return {
+ "s_suppkey": $s.s_suppkey,
+ "n_name": $n.n_name
+ }
+ )
+ for $l in dataset('LineItem')
+ where $s1.s_suppkey = $l.l_suppkey
+ return {
+ "l_suppkey": $l.l_suppkey,
+ "l_extendedprice": $l.l_extendedprice,
+ "l_discount": $l.l_discount,
+ "l_quantity": $l.l_quantity,
+ "l_partkey": $l.l_partkey,
+ "l_orderkey": $l.l_orderkey,
+ "n_name": $s1.n_name
+ }
+ )
+ where $ps.ps_suppkey = $l1.l_suppkey and $ps.ps_partkey = $l1.l_partkey
+ return {
+ "l_extendedprice": $l1.l_extendedprice,
+ "l_discount": $l1.l_discount,
+ "l_quantity": $l1.l_quantity,
+ "l_partkey": $l1.l_partkey,
+ "l_orderkey": $l1.l_orderkey,
+ "n_name": $l1.n_name,
+ "ps_supplycost": $ps.ps_supplycost
+ }
+ )
+ where contains($p.p_name, 'green') and $p.p_partkey = $l2.l_partkey
+ return {
+ "l_extendedprice": $l2.l_extendedprice,
+ "l_discount": $l2.l_discount,
+ "l_quantity": $l2.l_quantity,
+ "l_orderkey": $l2.l_orderkey,
+ "n_name": $l2.n_name,
+ "ps_supplycost": $l2.ps_supplycost
+ }
+ )
+ where $o.o_orderkey = $l3.l_orderkey
+ let $amount := $l3.l_extendedprice * (1 - $l3.l_discount) - $l3.ps_supplycost * $l3.l_quantity
+ let $o_year := get-year($o.o_orderdate)
+ return {
+ "nation": $l3.n_name,
+ "o_year": $o_year,
+ "amount": $amount
+ }
+)
+group by $nation := $profit.nation, $o_year := $profit.o_year with $profit
+order by $nation, $o_year desc
+return {
+ "nation": $nation,
+ "o_year": $o_year,
+ "sum_profit": sum( for $pr in $profit return $pr.amount )
+}