Added asterix project
git-svn-id: https://asterixdb.googlecode.com/svn/trunk/asterix@12 eaa15691-b419-025a-1212-ee371bd00084
diff --git a/asterix-app/src/test/resources/optimizerts/queries/cell-aggregation-with-filtering.aql b/asterix-app/src/test/resources/optimizerts/queries/cell-aggregation-with-filtering.aql
new file mode 100644
index 0000000..a328a1b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/cell-aggregation-with-filtering.aql
@@ -0,0 +1,37 @@
+drop dataverse twitter if exists;
+create dataverse twitter;
+use dataverse twitter;
+
+create type Tweet as closed {
+ id: int32,
+ tweetid: int64,
+ loc: point,
+ time: datetime,
+ text: string
+}
+
+create nodegroup group1 if exists on nc1, nc2;
+
+create dataset TwitterData(Tweet)
+ partitioned by key id on group1;
+
+
+load dataset TwitterData
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/twitter/extrasmalltweets.txt"),("format"="adm")) pre-sorted;
+
+create index rtree_index_point on TwitterData(loc) type rtree;
+
+write output to nc1:"rttest/spatial_cell-aggregation-with-filtering.adm";
+
+for $t in dataset('TwitterData')
+let $region := polygon("
+ 33.80503407287759,-126.41235263538363
+ 44.9090773200516,-126.41235263538363
+ 44.9090773200516,-87.65258701038363
+ 33.80503407287759,-87.65258701038363")
+
+where spatial-intersect($t.loc, $region)
+group by $c := spatial-cell($t.loc, create-point(24.5,-125.5), 3.0, 3.0) with $t
+order by count($t)
+return { "cell": $c, "count": count($t) }
diff --git a/asterix-app/src/test/resources/optimizerts/queries/const-folding.aql b/asterix-app/src/test/resources/optimizerts/queries/const-folding.aql
new file mode 100644
index 0000000..1fe4495
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/const-folding.aql
@@ -0,0 +1,10 @@
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/divide.adm";
+
+let $x := [ "foo", "bar" ]
+let $r := { "a": 1+2, "b": $x[0] }
+let $f := $r.b
+return $f
diff --git a/asterix-app/src/test/resources/optimizerts/queries/count-tweets.aql b/asterix-app/src/test/resources/optimizerts/queries/count-tweets.aql
new file mode 100644
index 0000000..5e4cde1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/count-tweets.aql
@@ -0,0 +1,24 @@
+drop dataverse twitter if exists;
+create dataverse twitter;
+use dataverse twitter;
+
+create type Tweet as open {
+ id: int32,
+ tweetid: int64,
+ loc: point,
+ time: string,
+ text: string
+}
+
+create external dataset TwitterData(Tweet)
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/twitter/smalltweets.txt"),("format"="adm"));
+
+write output to nc1:"/tmp/count-tweets.adm";
+
+for $t in dataset('TwitterData')
+let $tokens := word-tokens($t.text)
+for $token in $tokens
+group by $tok := $token with $token
+return { "word": $tok, "count": count($token) }
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/cust_group_no_agg.aql b/asterix-app/src/test/resources/optimizerts/queries/cust_group_no_agg.aql
new file mode 100644
index 0000000..6fdae88
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/cust_group_no_agg.aql
@@ -0,0 +1,28 @@
+drop dataverse group_no_agg if exists;
+
+create dataverse group_no_agg;
+
+use dataverse group_no_agg;
+
+create type CustomerType as closed {
+ c_custkey: int32,
+ c_name: string,
+ c_address: string,
+ c_nationkey: int32,
+ c_phone: string,
+ c_acctbal: double,
+ c_mktsegment: string,
+ c_comment: string
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset Customers(CustomerType)
+ partitioned by key c_custkey on group1;
+
+write output to nc1:"/tmp/.adm";
+
+for $c in dataset('Customers')
+group by $name := $c.c_name with $c
+return $name
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/denorm-cust-order.aql b/asterix-app/src/test/resources/optimizerts/queries/denorm-cust-order.aql
new file mode 100644
index 0000000..acad4a1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/denorm-cust-order.aql
@@ -0,0 +1,50 @@
+drop dataverse custorder if exists;
+
+create dataverse custorder;
+
+use dataverse custorder;
+
+
+create type AddressType as closed {
+ number: int32,
+ street: string,
+ city: string
+}
+
+create type CustomerType as closed {
+ cid: int32,
+ name: string,
+ age: int32?,
+ address: AddressType?,
+ lastorder: {
+ oid: int32,
+ total: float
+ }
+}
+
+
+
+create type OrderType as closed {
+ oid: int32,
+ cid: int32,
+ orderstatus: string,
+ orderpriority: string,
+ clerk: string,
+ total: float
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset Customers(CustomerType)
+ partitioned by key cid on group1;
+create dataset Orders(OrderType)
+ partitioned by key oid on group1;
+
+write output to nc1:"/tmp/custorder.adm";
+
+for $c in dataset('Customers')
+for $o in dataset('Orders')
+where $c.cid = $o.cid
+group by $cid := $c.cid decor $cust := $c with $o
+return {"cid":$cid, "cust":$cust, "cnt-orders": count($o), "orders":$o}
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/distinct_aggregate.aql b/asterix-app/src/test/resources/optimizerts/queries/distinct_aggregate.aql
new file mode 100644
index 0000000..a4784c1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/distinct_aggregate.aql
@@ -0,0 +1,50 @@
+drop dataverse tpch if exists;
+create dataverse tpch;
+use dataverse tpch;
+
+create type LineItemType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32,
+ l_linenumber: int32,
+ l_quantity: double,
+ l_extendedprice: double,
+ l_discount: double,
+ l_tax: double,
+ l_returnflag: string,
+ l_linestatus: string,
+ l_shipdate: string,
+ l_commitdate: string,
+ l_receiptdate: string,
+ l_shipinstruct: string,
+ l_shipmode: string,
+ l_comment: string
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset LineItems_q1(LineItemType)
+ partitioned by key l_orderkey, l_linenumber on group1;
+
+write output to nc1:"rttest/tpch_q1_pricing_summary_report_nt.adm";
+
+for $g in
+(
+ for $l in dataset('LineItems_q1')
+ where $l.l_shipdate <= '1998-09-02'
+ group by $l_returnflag := $l.l_returnflag, $l_linestatus := $l.l_linestatus,
+ $l_suppkey := $l.l_suppkey with $l
+ return {
+ "l_returnflag": $l_returnflag,
+ "l_linestatus": $l_linestatus,
+ "l_suppkey": $l_suppkey
+ }
+)
+group by $l_returnflag := $g.l_returnflag, $l_linestatus := $g.l_linestatus
+ with $g
+order by $l_returnflag, $l_linestatus
+return {
+ "l_returnflag": $l_returnflag,
+ "l_linestatus": $l_linestatus,
+ "count_suppkey": count($g)
+}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/fj-dblp-csx.aql b/asterix-app/src/test/resources/optimizerts/queries/fj-dblp-csx.aql
new file mode 100644
index 0000000..2898058
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/fj-dblp-csx.aql
@@ -0,0 +1,76 @@
+drop dataverse fj-dblp-csx if exists;
+
+create dataverse fj-dblp-csx;
+
+use dataverse fj-dblp-csx;
+
+create type DBLPType as open {
+ id: int32,
+ dblpid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create type CSXType as open {
+ id: int32,
+ csxid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset DBLP(DBLPType) partitioned by key id on group1;
+create dataset CSX(CSXType) partitioned by key id on group1;
+
+write output to nc1:'rttest/fj-dblp-csx.adm';
+
+ for $paperDBLP in dataset('DBLP')
+ let $idDBLP := $paperDBLP.id
+ let $unrankedTokensDBLP := counthashed-word-tokens($paperDBLP.title)
+ let $tokensDBLP :=
+ for $token in $unrankedTokensDBLP
+ for $tokenRanked at $i in
+ //
+ // -- - Stage 1 - --
+ //
+ // for $paper in dataset('DBLP')
+ // for $token in counthashed-word-tokens($paper.title)
+ // group by $tokenGroupped := $token with $paper
+ // order by count($paper), $tokenGroupped
+ // return $tokenGroupped
+ for $paper in dataset('DBLP')
+ return $paper.title
+ where $token = $tokenRanked
+ order by $i
+ return $i
+
+ for $prefixTokenDBLP in $tokensDBLP
+
+ for $paperCSX in dataset('CSX')
+ let $idCSX := $paperCSX.id
+ let $unrankedTokensCSX := counthashed-word-tokens($paperCSX.title)
+ let $tokensCSX :=
+ for $token in $unrankedTokensCSX
+ for $tokenRanked at $i in
+ //
+ // -- - Stage 1 - --
+ //
+ // for $paper in dataset('DBLP')
+ // for $token in counthashed-word-tokens($paper.title)
+ // group by $tokenGroupped := $token with $paper
+ // order by count($paper), $tokenGroupped
+ // return $tokenGroupped
+ for $paper in dataset('DBLP')
+ return $paper.title
+ where $token = $tokenRanked
+ order by $i
+ return $i
+
+ for $prefixTokenCSX in $tokensCSX
+ where $prefixTokenDBLP = $prefixTokenCSX
+ group by $idDBLP := $idDBLP, $idCSX := $idCSX with $paperDBLP
+ order by $idDBLP, $idCSX
+ return {'idDBLP': $idDBLP, 'idCSX': $idCSX}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/fj-phase1.aql b/asterix-app/src/test/resources/optimizerts/queries/fj-phase1.aql
new file mode 100644
index 0000000..778232a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/fj-phase1.aql
@@ -0,0 +1,46 @@
+drop dataverse rares03 if exists;
+
+create dataverse rares03;
+
+use dataverse rares03;
+
+create type UserType as open {
+ uid: int32,
+ name: string,
+ lottery_numbers: {{int32}}
+}
+
+create type VisitorType as open {
+ vid: int32,
+ name: string,
+ lottery_numbers: {{int32}}
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset Users(UserType)
+ partitioned by key uid on group1;
+
+create dataset Visitors(VisitorType)
+ partitioned by key vid on group1;
+
+
+// set simfunction "jaccard";
+// set simthreshold ".8";
+
+write output to nc1:"/tmp/rares03.adm";
+
+for $user in dataset('Users')
+let $tokens :=
+ for $lottery_number in $user.lottery_numbers
+ for $token at $i in
+ for $user in dataset('Users')
+ for $lottery_number in $user.lottery_numbers
+ group by $item := $lottery_number with $user
+ let $count := count($user)
+ order by $count desc
+ return $item
+ where $lottery_number = $token
+ order by $token
+ return $i
+return {"uid": $user.uid, "tokens": $tokens}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/fj-phase2-with-hints.aql b/asterix-app/src/test/resources/optimizerts/queries/fj-phase2-with-hints.aql
new file mode 100644
index 0000000..866c027
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/fj-phase2-with-hints.aql
@@ -0,0 +1,44 @@
+drop dataverse fuzzyjoin_078 if exists;
+
+create dataverse fuzzyjoin_078;
+
+use dataverse fuzzyjoin_078;
+
+create type DBLPType as open {
+ id: int32,
+ dblpid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset DBLP_fuzzyjoin_078(DBLPType)
+ partitioned by key id on group1;
+
+write output to nc1:'rttest/fuzzyjoin_078.adm';
+
+ //
+ // -- - Stage 2 - --
+ //
+ for $paperDBLP in dataset('DBLP_fuzzyjoin_078')
+ let $unrankedTokensDBLP := counthashed-word-tokens($paperDBLP.title)
+ let $tokensDBLP :=
+ for $token in $unrankedTokensDBLP
+ for $tokenRanked at $i in
+ //
+ // -- - Stage 1 - --
+ //
+ for $paper in dataset('DBLP_fuzzyjoin_078')
+ for $token in counthashed-word-tokens($paper.title)
+ /*+ hash */
+ group by $tokenGroupped := $token with $paper
+ /*+ inmem 1 302 */
+ order by count($paper), $tokenGroupped
+ return $tokenGroupped
+ where $token = /*+ bcast */ $tokenRanked
+ order by $i
+ return $i
+ order by $paperDBLP.id
+ return {'id': $paperDBLP.id, 'tokens':$tokensDBLP}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/inline-funs.aql b/asterix-app/src/test/resources/optimizerts/queries/inline-funs.aql
new file mode 100644
index 0000000..c220b9c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/inline-funs.aql
@@ -0,0 +1,15 @@
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"/tmp/inline_funs.adm";
+
+declare function f1() { 1 + f2() }
+
+declare function f2() { 2 + f3() + f3() + f3() + f4() }
+
+declare function f3() { 0 - 1 }
+
+declare function f4() { 1001 }
+
+f1()
diff --git a/asterix-app/src/test/resources/optimizerts/queries/inlined_q18_large_volume_customer.aql b/asterix-app/src/test/resources/optimizerts/queries/inlined_q18_large_volume_customer.aql
new file mode 100644
index 0000000..49c4157
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/inlined_q18_large_volume_customer.aql
@@ -0,0 +1,89 @@
+drop dataverse inlined_q18_large_volume_customer if exists;
+
+create dataverse inlined_q18_large_volume_customer;
+
+use dataverse inlined_q18_large_volume_customer;
+
+create type LineItemType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32,
+ l_linenumber: int32,
+ l_quantity: double,
+ l_extendedprice: double,
+ l_discount: double,
+ l_tax: double,
+ l_returnflag: string,
+ l_linestatus: string,
+ l_shipdate: string,
+ l_commitdate: string,
+ l_receiptdate: string,
+ l_shipinstruct: string,
+ l_shipmode: string,
+ l_comment: string
+}
+
+create type OrderType as closed {
+ o_orderkey: int32,
+ o_custkey: int32,
+ o_orderstatus: string,
+ o_totalprice: double,
+ o_orderdate: string,
+ o_orderpriority: string,
+ o_clerk: string,
+ o_shippriority: int32,
+ o_comment: string
+}
+
+create type CustomerType as closed {
+ c_custkey: int32,
+ c_name: string,
+ c_address: string,
+ c_nationkey: int32,
+ c_phone: string,
+ c_acctbal: double,
+ c_mktsegment: string,
+ c_comment: string
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset LineItems(LineItemType)
+ partitioned by key l_orderkey, l_linenumber on group1;
+create dataset Orders(OrderType)
+ partitioned by key o_orderkey on group1;
+create dataset Customers(CustomerType)
+ partitioned by key c_custkey on group1;
+
+write output to nc1:"/tmp/inlined_q18_large_volume_customer.adm";
+
+for $c in dataset('Customers')
+for $o in dataset('Orders')
+where
+ $c.c_custkey = $o.o_custkey
+for $t in
+(
+ for $l in dataset('LineItems')
+ 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) }
+)
+where
+ $o.o_orderkey = $t.l_orderkey and $t.t_sum_quantity > 300
+for $l in dataset('LineItems')
+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
+limit 100
+return {
+ "c_name": $c_name,
+ "c_custkey": $c_custkey,
+ "o_orderkey": $o_orderkey,
+ "o_orderdate": $o_orderdate,
+ "o_totalprice": $o_totalprice,
+ "sum_quantity": sum(for $j in $l return $j.l_quantity)
+}
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/introhashpartitionmerge.aql b/asterix-app/src/test/resources/optimizerts/queries/introhashpartitionmerge.aql
new file mode 100644
index 0000000..9427ab5
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/introhashpartitionmerge.aql
@@ -0,0 +1,23 @@
+drop dataverse fuzzyjoin if exists;
+create dataverse fuzzyjoin;
+use dataverse fuzzyjoin;
+
+create type TOKENSRANKEDADMType as closed {
+ token: int32,
+ rank: int32
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset TOKENSRANKEDADM(TOKENSRANKEDADMType) partitioned by key rank on group1;
+
+write output to nc1:'rttest/introhashpartitionmerge.adm';
+
+ for $token1 in dataset('TOKENSRANKEDADM')
+ for $token2 in
+ for $tokenRanked in dataset('TOKENSRANKEDADM')
+ order by $tokenRanked.rank
+ return $tokenRanked
+ where $token1.token = $token2.token
+ order by $token2.rank
+ return $token2.rank
diff --git a/asterix-app/src/test/resources/optimizerts/queries/join-super-key_01.aql b/asterix-app/src/test/resources/optimizerts/queries/join-super-key_01.aql
new file mode 100644
index 0000000..f0251ad
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/join-super-key_01.aql
@@ -0,0 +1,82 @@
+drop dataverse join-super-key_1 if exists;
+
+create dataverse join-super-key_1;
+
+use dataverse join-super-key_1;
+
+create type SupplierType as closed {
+ s_suppkey: int32,
+ s_name: string,
+ s_address: string,
+ s_nationkey: int32,
+ s_phone: string,
+ s_acctbal: double,
+ s_comment: string
+}
+
+create type NationType as closed {
+ n_nationkey: int32,
+ n_name: string,
+ n_regionkey: int32,
+ n_comment: string
+}
+
+create type LineItemType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32,
+ l_linenumber: int32,
+ l_quantity: double,
+ l_extendedprice: double,
+ l_discount: double,
+ l_tax: double,
+ l_returnflag: string,
+ l_linestatus: string,
+ l_shipdate: string,
+ l_commitdate: string,
+ l_receiptdate: string,
+ l_shipinstruct: string,
+ l_shipmode: string,
+ l_comment: string
+}
+
+create type PartType as closed {
+ p_partkey: int32,
+ p_name: string,
+ p_mfgr: string,
+ p_brand: string,
+ p_type: string,
+ p_size: int32,
+ p_container: string,
+ p_retailprice: double,
+ p_comment: string
+}
+
+create type PartSuppType as closed {
+ ps_partkey: int32,
+ ps_suppkey: int32,
+ ps_availqty: int32,
+ ps_supplycost: double,
+ ps_comment: string
+}
+
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+write output to nc1:"/tmp/join-super-key_01.adm";
+
+create dataset LineItems(LineItemType)
+ partitioned by key l_partkey, l_linenumber on group1;
+create dataset PartSupp(PartSuppType)
+ partitioned by key ps_partkey, ps_suppkey on group1;
+
+
+for $li in dataset('LineItems')
+for $ps in dataset('PartSupp')
+where $li.l_partkey = $ps.ps_partkey and $li.l_suppkey = $ps.ps_suppkey and
+ $li.l_extendedprice = $ps.ps_supplycost
+return {
+ "l_partkey": $li.l_partkey
+}
+
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/join-super-key_02.aql b/asterix-app/src/test/resources/optimizerts/queries/join-super-key_02.aql
new file mode 100644
index 0000000..0904f37
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/join-super-key_02.aql
@@ -0,0 +1,80 @@
+drop dataverse join-super-key_01 if exists;
+
+create dataverse join-super-key_01;
+
+use dataverse join-super-key_01;
+
+create type SupplierType as closed {
+ s_suppkey: int32,
+ s_name: string,
+ s_address: string,
+ s_nationkey: int32,
+ s_phone: string,
+ s_acctbal: double,
+ s_comment: string
+}
+
+create type NationType as closed {
+ n_nationkey: int32,
+ n_name: string,
+ n_regionkey: int32,
+ n_comment: string
+}
+
+create type LineItemType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32,
+ l_linenumber: int32,
+ l_quantity: double,
+ l_extendedprice: double,
+ l_discount: double,
+ l_tax: double,
+ l_returnflag: string,
+ l_linestatus: string,
+ l_shipdate: string,
+ l_commitdate: string,
+ l_receiptdate: string,
+ l_shipinstruct: string,
+ l_shipmode: string,
+ l_comment: string
+}
+
+create type PartType as closed {
+ p_partkey: int32,
+ p_name: string,
+ p_mfgr: string,
+ p_brand: string,
+ p_type: string,
+ p_size: int32,
+ p_container: string,
+ p_retailprice: double,
+ p_comment: string
+}
+
+create type PartSuppType as closed {
+ ps_partkey: int32,
+ ps_suppkey: int32,
+ ps_availqty: int32,
+ ps_supplycost: double,
+ ps_comment: string
+}
+
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+write output to nc1:"/tmp/join-super-key_01.adm";
+
+create dataset LineItems(LineItemType)
+ partitioned by key l_partkey, l_linenumber on group1;
+create dataset PartSupp(PartSuppType)
+ partitioned by key ps_partkey, ps_suppkey on group1;
+
+
+for $ps in dataset('PartSupp')
+for $li in dataset('LineItems')
+where $li.l_partkey = $ps.ps_partkey and $li.l_suppkey = $ps.ps_suppkey and
+ $li.l_extendedprice = $ps.ps_supplycost
+return {
+ "l_partkey": $li.l_partkey
+}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/loj-super-key_01.aql b/asterix-app/src/test/resources/optimizerts/queries/loj-super-key_01.aql
new file mode 100644
index 0000000..1f43f73
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/loj-super-key_01.aql
@@ -0,0 +1,86 @@
+drop dataverse loj-super-key_01 if exists;
+
+create dataverse loj-super-key_01;
+
+use dataverse loj-super-key_01;
+
+create type SupplierType as closed {
+ s_suppkey: int32,
+ s_name: string,
+ s_address: string,
+ s_nationkey: int32,
+ s_phone: string,
+ s_acctbal: double,
+ s_comment: string
+}
+
+create type NationType as closed {
+ n_nationkey: int32,
+ n_name: string,
+ n_regionkey: int32,
+ n_comment: string
+}
+
+create type LineItemType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32,
+ l_linenumber: int32,
+ l_quantity: double,
+ l_extendedprice: double,
+ l_discount: double,
+ l_tax: double,
+ l_returnflag: string,
+ l_linestatus: string,
+ l_shipdate: string,
+ l_commitdate: string,
+ l_receiptdate: string,
+ l_shipinstruct: string,
+ l_shipmode: string,
+ l_comment: string
+}
+
+create type PartType as closed {
+ p_partkey: int32,
+ p_name: string,
+ p_mfgr: string,
+ p_brand: string,
+ p_type: string,
+ p_size: int32,
+ p_container: string,
+ p_retailprice: double,
+ p_comment: string
+}
+
+create type PartSuppType as closed {
+ ps_partkey: int32,
+ ps_suppkey: int32,
+ ps_availqty: int32,
+ ps_supplycost: double,
+ ps_comment: string
+}
+
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+write output to nc1:"/tmp/loj-super-key_01.adm";
+
+create dataset LineItems(LineItemType)
+ partitioned by key l_partkey, l_linenumber on group1;
+create dataset PartSupp(PartSuppType)
+ partitioned by key ps_partkey, ps_suppkey on group1;
+
+
+for $li in dataset('LineItems')
+let $partsupp :=
+ for $ps in dataset('PartSupp')
+ where $li.l_partkey = $ps.ps_partkey
+ and $li.l_suppkey = $ps.ps_suppkey
+ and $li.l_extendedprice = $ps.ps_supplycost
+ return $ps
+return {
+ "li": $li,
+ "partsupp": $partsupp
+}
+
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/loj-super-key_02.aql b/asterix-app/src/test/resources/optimizerts/queries/loj-super-key_02.aql
new file mode 100644
index 0000000..ceab193
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/loj-super-key_02.aql
@@ -0,0 +1,86 @@
+drop dataverse loj-super-key_02 if exists;
+
+create dataverse loj-super-key_02;
+
+use dataverse loj-super-key_02;
+
+create type SupplierType as closed {
+ s_suppkey: int32,
+ s_name: string,
+ s_address: string,
+ s_nationkey: int32,
+ s_phone: string,
+ s_acctbal: double,
+ s_comment: string
+}
+
+create type NationType as closed {
+ n_nationkey: int32,
+ n_name: string,
+ n_regionkey: int32,
+ n_comment: string
+}
+
+create type LineItemType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32,
+ l_linenumber: int32,
+ l_quantity: double,
+ l_extendedprice: double,
+ l_discount: double,
+ l_tax: double,
+ l_returnflag: string,
+ l_linestatus: string,
+ l_shipdate: string,
+ l_commitdate: string,
+ l_receiptdate: string,
+ l_shipinstruct: string,
+ l_shipmode: string,
+ l_comment: string
+}
+
+create type PartType as closed {
+ p_partkey: int32,
+ p_name: string,
+ p_mfgr: string,
+ p_brand: string,
+ p_type: string,
+ p_size: int32,
+ p_container: string,
+ p_retailprice: double,
+ p_comment: string
+}
+
+create type PartSuppType as closed {
+ ps_partkey: int32,
+ ps_suppkey: int32,
+ ps_availqty: int32,
+ ps_supplycost: double,
+ ps_comment: string
+}
+
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+write output to nc1:"/tmp/loj-super-key_01.adm";
+
+create dataset LineItems(LineItemType)
+ partitioned by key l_partkey, l_linenumber on group1;
+create dataset PartSupp(PartSuppType)
+ partitioned by key ps_partkey, ps_suppkey on group1;
+
+
+for $ps in dataset('PartSupp')
+let $items :=
+ for $li in dataset('LineItems')
+ where $li.l_partkey = $ps.ps_partkey
+ and $li.l_suppkey = $ps.ps_suppkey
+ and $li.l_extendedprice = $ps.ps_supplycost
+ return $li
+return {
+ "partsupp": $ps,
+ "li": $items
+}
+
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested_loj2.aql b/asterix-app/src/test/resources/optimizerts/queries/nested_loj2.aql
new file mode 100644
index 0000000..0b77f55
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested_loj2.aql
@@ -0,0 +1,73 @@
+drop dataverse tpch if exists;
+create dataverse tpch;
+use dataverse tpch;
+
+create type LineItemType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32,
+ l_linenumber: int32,
+ l_quantity: double,
+ l_extendedprice: double,
+ l_discount: double,
+ l_tax: double,
+ l_returnflag: string,
+ l_linestatus: string,
+ l_shipdate: string,
+ l_commitdate: string,
+ l_receiptdate: string,
+ l_shipinstruct: string,
+ l_shipmode: string,
+ l_comment: string
+}
+
+create type OrderType as closed {
+ o_orderkey: int32,
+ o_custkey: int32,
+ o_orderstatus: string,
+ o_totalprice: double,
+ o_orderdate: string,
+ o_orderpriority: string,
+ o_clerk: string,
+ o_shippriority: int32,
+ o_comment: string
+}
+
+create type CustomerType as closed {
+ c_custkey: int32,
+ c_name: string,
+ c_address: string,
+ c_nationkey: int32,
+ c_phone: string,
+ c_acctbal: double,
+ c_mktsegment: string,
+ c_comment: string
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset LineItems(LineItemType)
+ partitioned by key l_orderkey, l_linenumber on group1;
+create dataset Orders(OrderType)
+ partitioned by key o_orderkey on group1;
+create dataset Customers(CustomerType)
+ partitioned by key c_custkey on group1;
+
+write output to nc1:"/tmp/nested_loj.adm";
+
+for $c in dataset('Customers')
+let $orders :=
+ for $o in dataset('Orders')
+ where $o.o_custkey = $c.c_custkey
+ let $items :=
+ for $l in dataset('LineItems')
+ where $l.l_orderkey = $o.o_orderkey
+ return $l
+ return {
+ "order": $o,
+ "items": $items
+ }
+return {
+ "cust": $c,
+ "orders": $orders
+}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested_loj3.aql b/asterix-app/src/test/resources/optimizerts/queries/nested_loj3.aql
new file mode 100644
index 0000000..3a1ef04
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested_loj3.aql
@@ -0,0 +1,92 @@
+drop dataverse tpch if exists;
+create dataverse tpch;
+use dataverse tpch;
+
+create type LineItemType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32,
+ l_linenumber: int32,
+ l_quantity: double,
+ l_extendedprice: double,
+ l_discount: double,
+ l_tax: double,
+ l_returnflag: string,
+ l_linestatus: string,
+ l_shipdate: string,
+ l_commitdate: string,
+ l_receiptdate: string,
+ l_shipinstruct: string,
+ l_shipmode: string,
+ l_comment: string
+}
+
+create type OrderType as closed {
+ o_orderkey: int32,
+ o_custkey: int32,
+ o_orderstatus: string,
+ o_totalprice: double,
+ o_orderdate: string,
+ o_orderpriority: string,
+ o_clerk: string,
+ o_shippriority: int32,
+ o_comment: string
+}
+
+create type CustomerType as closed {
+ c_custkey: int32,
+ c_name: string,
+ c_address: string,
+ c_nationkey: int32,
+ c_phone: string,
+ c_acctbal: double,
+ c_mktsegment: string,
+ c_comment: string
+}
+
+
+create type PartSuppType as closed {
+ ps_partkey: int32,
+ ps_suppkey: int32,
+ ps_availqty: int32,
+ ps_supplycost: double,
+ ps_comment: string
+}
+
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset LineItems(LineItemType)
+ partitioned by key l_orderkey, l_linenumber on group1;
+create dataset Orders(OrderType)
+ partitioned by key o_orderkey on group1;
+create dataset Customers(CustomerType)
+ partitioned by key c_custkey on group1;
+create dataset PartSupp(PartSuppType)
+ partitioned by key ps_partkey, ps_suppkey on group1;
+
+write output to nc1:"/tmp/nested_loj.adm";
+
+for $c in dataset('Customers')
+let $orders :=
+ for $o in dataset('Orders')
+ where $o.o_custkey = $c.c_custkey
+ let $items :=
+ for $l in dataset('LineItems')
+ where $l.l_orderkey = $o.o_orderkey
+ let $partsupp :=
+ for $ps in dataset('PartSupp')
+ where $ps.ps_partkey = $l.l_partkey and $ps.ps_suppkey = $l.l_suppkey
+ return $ps
+ return {
+ "item": $l,
+ "part_supplier": $partsupp
+ }
+ return {
+ "order": $o,
+ "items": $items
+ }
+return {
+ "cust": $c,
+ "orders": $orders
+}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/noncollocated.aql b/asterix-app/src/test/resources/optimizerts/queries/noncollocated.aql
new file mode 100644
index 0000000..3aa1855
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/noncollocated.aql
@@ -0,0 +1,35 @@
+drop dataverse colocated if exists;
+
+create dataverse colocated;
+
+use dataverse colocated;
+
+create type UserType as open {
+ uid: int32,
+ name: string,
+ lottery_numbers: {{int32}}
+}
+
+create type VisitorType as open {
+ vid: int32,
+ name: string,
+ lottery_numbers: {{int32}}
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create nodegroup group2 if not exists on nc2;
+
+create dataset Users(UserType)
+ partitioned by key uid on group1;
+
+create dataset Visitors(VisitorType)
+ partitioned by key vid on group2;
+
+
+write output to nc1:"/tmp/fuzzy1.adm";
+
+for $user in dataset('Users')
+for $visitor in dataset('Visitors')
+where $user.uid = $visitor.vid
+return { "user_name" : $user.name, "visitor_name" : $visitor.name }
diff --git a/asterix-app/src/test/resources/optimizerts/queries/orderby-desc-using-gby.aql b/asterix-app/src/test/resources/optimizerts/queries/orderby-desc-using-gby.aql
new file mode 100644
index 0000000..682800c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/orderby-desc-using-gby.aql
@@ -0,0 +1,35 @@
+drop dataverse gby-using-orderby-desc if exists;
+
+create dataverse gby-using-orderby-desc;
+
+use dataverse gby-using-orderby-desc;
+
+create type AddressType as closed {
+ number: int32,
+ street: string,
+ city: string
+}
+
+create type CustomerType as closed {
+ cid: int32,
+ name: string,
+ age: int32?,
+ address: AddressType?,
+ lastorder: {
+ oid: int32,
+ total: float
+ }
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset Customers(CustomerType)
+ partitioned by key cid on group1;
+
+write output to nc1:"rttest/gby-using-orderby-desc.adm";
+
+for $c in dataset('Customers')
+group by $name := $c.name decor $age := $c.age with $c
+order by $name desc, $age asc
+return { "name": $name, "age": $age }
+
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/optimizerts/queries/orders-aggreg.aql b/asterix-app/src/test/resources/optimizerts/queries/orders-aggreg.aql
new file mode 100644
index 0000000..c2f96f6
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/orders-aggreg.aql
@@ -0,0 +1,27 @@
+drop dataverse orders-aggreg if exists;
+
+create dataverse orders-aggreg;
+
+use dataverse orders-aggreg;
+
+create type OrderType as closed {
+ oid: int32,
+ cid: int32,
+ orderstatus: string,
+ orderpriority: string,
+ clerk: string,
+ total: float
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset Orders(OrderType)
+ partitioned by key oid on group1;
+
+write output to nc1:"/tmp/orders-aggreg.adm";
+
+for $o in dataset('Orders')
+group by $cid := $o.cid with $o
+return { "cid": $cid , "ordpercust": count($o),
+ "totalcust": sum(for $i in $o return $i.total),
+ "avgcust": avg(for $i in $o return $i.total) }
diff --git a/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01.aql b/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01.aql
new file mode 100644
index 0000000..f5b5067
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_01.aql
@@ -0,0 +1,40 @@
+drop dataverse tpch if exists;
+create dataverse tpch;
+use dataverse tpch;
+
+
+create type OrderType as closed {
+ o_orderkey: int32,
+ o_custkey: int32,
+ o_orderstatus: string,
+ o_totalprice: double,
+ o_orderdate: string,
+ o_orderpriority: string,
+ o_clerk: string,
+ o_shippriority: int32,
+ o_comment: string
+}
+
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset Orders(OrderType)
+ partitioned by key o_orderkey on group1;
+
+load dataset Orders
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1:///data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;
+
+create index idx_Orders_Custkey on Orders(o_custkey);
+
+write output to nc1:"/tmp/index_search-conjunctive.adm";
+
+
+for $o in dataset('Orders')
+where
+ $o.o_custkey = 40 and $o.o_totalprice > 150000.0
+order by $o.o_orderkey
+return {
+ "o_orderkey": $o.o_orderkey,
+ "o_custkey": $o.o_custkey
+}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02.aql b/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02.aql
new file mode 100644
index 0000000..fed390c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/orders-index-search-conjunctive_02.aql
@@ -0,0 +1,41 @@
+drop dataverse tpch if exists;
+create dataverse tpch;
+use dataverse tpch;
+
+
+create type OrderType as closed {
+ o_orderkey: int32,
+ o_custkey: int32,
+ o_orderstatus: string,
+ o_totalprice: double,
+ o_orderdate: string,
+ o_orderpriority: string,
+ o_clerk: string,
+ o_shippriority: int32,
+ o_comment: string
+}
+
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset Orders(OrderType)
+ partitioned by key o_orderkey on group1;
+
+load dataset Orders
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;
+
+create index idx_Orders_Custkey on Orders(o_custkey);
+
+write output to nc1:"/tmp/index_search-conjunctive.adm";
+
+
+for $o in dataset('Orders')
+where
+ $o.o_custkey = 40 and $o.o_totalprice > 150000.0
+order by $o.o_orderkey
+return {
+ "o_orderkey": $o.o_orderkey,
+ "o_custkey": $o.o_custkey,
+ "o_totalprice": $o.o_totalprice
+}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/orders-index-search.aql b/asterix-app/src/test/resources/optimizerts/queries/orders-index-search.aql
new file mode 100644
index 0000000..75be90b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/orders-index-search.aql
@@ -0,0 +1,35 @@
+drop dataverse index_search if exists;
+
+create dataverse index_search;
+
+use dataverse index_search;
+
+create type OrderType as closed {
+ o_orderkey: int32,
+ o_custkey: int32,
+ o_orderstatus: string,
+ o_totalprice: double,
+ o_orderdate: string,
+ o_orderpriority: string,
+ o_clerk: string,
+ o_shippriority: int32,
+ o_comment: string
+}
+
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset Orders(OrderType)
+ partitioned by key o_orderkey on group1;
+
+create index idx_Orders_Custkey on Orders(o_custkey);
+
+write output to nc1:"/tmp/index_search.adm";
+
+for $o in dataset('Orders')
+where
+ $o.o_custkey = 40
+return {
+ "o_orderkey": $o.o_orderkey,
+ "o_custkey": $o.o_custkey
+}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/prim-idx-search.aql b/asterix-app/src/test/resources/optimizerts/queries/prim-idx-search.aql
new file mode 100644
index 0000000..7a22597
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/prim-idx-search.aql
@@ -0,0 +1,36 @@
+drop dataverse prim_index_search if exists;
+
+create dataverse prim_index_search;
+
+use dataverse prim_index_search;
+
+
+create type OrderType as closed {
+ o_orderkey: int32,
+ o_custkey: int32,
+ o_orderstatus: string,
+ o_totalprice: double,
+ o_orderdate: string,
+ o_orderpriority: string,
+ o_clerk: string,
+ o_shippriority: int32,
+ o_comment: string
+}
+
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset Orders(OrderType)
+ partitioned by key o_orderkey on group1;
+
+
+write output to nc1:"/tmp/prim_index_search.adm";
+
+
+for $o in dataset('Orders')
+where
+ $o.o_orderkey = 34
+return {
+ "o_orderkey": $o.o_orderkey,
+ "o_custkey": $o.o_custkey
+}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/pull_select_above_eq_join.aql b/asterix-app/src/test/resources/optimizerts/queries/pull_select_above_eq_join.aql
new file mode 100644
index 0000000..dfc05ff
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/pull_select_above_eq_join.aql
@@ -0,0 +1,36 @@
+drop dataverse pull-select-above-eq-join if exists;
+
+create dataverse pull-select-above-eq-join;
+
+use dataverse pull-select-above-eq-join;
+
+create type UserType as open {
+ uid: int32,
+ name: string,
+ city: string,
+ lottery_numbers: {{int32}}
+}
+
+create type VisitorType as open {
+ vid: int32,
+ name: string,
+ city: string,
+ lottery_numbers: {{int32}}
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset Users(UserType)
+ partitioned by key uid on group1;
+
+create dataset Visitors(VisitorType)
+ partitioned by key vid on group1;
+
+
+write output to nc1:"/tmp/pull-select-above-eq-join.adm";
+
+for $user in dataset('Users')
+for $visitor in dataset('Visitors')
+where $user.name = $visitor.name
+ and $user.city != $visitor.city
+return {"uid": $user.uid, "vid": $visitor.vid}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/push-project-through-group.aql b/asterix-app/src/test/resources/optimizerts/queries/push-project-through-group.aql
new file mode 100644
index 0000000..5dd229b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/push-project-through-group.aql
@@ -0,0 +1,28 @@
+drop dataverse fuzzyjoin_080 if exists;
+
+create dataverse fuzzyjoin_080;
+
+use dataverse fuzzyjoin_080;
+
+create type DBLPType as open {
+ id: int32,
+ dblpid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset DBLP(DBLPType)
+ partitioned by key id on group1;
+
+
+write output to nc1:'rttest/fuzzyjoin_080.adm';
+
+ for $paperDBLP in dataset('DBLP')
+ let $matches :=
+ for $paper in dataset('DBLP')
+ where $paper.authors = $paperDBLP.authors
+ return $paper.title
+ return {'id': $paperDBLP.id, 'matches':$matches}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/push_limit.aql b/asterix-app/src/test/resources/optimizerts/queries/push_limit.aql
new file mode 100644
index 0000000..a9c4fe7
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/push_limit.aql
@@ -0,0 +1,28 @@
+drop dataverse tpch if exists;
+create dataverse tpch;
+use dataverse tpch;
+
+create type OrderType as closed {
+ o_orderkey: int32,
+ o_custkey: int32,
+ o_orderstatus: string,
+ o_totalprice: double,
+ o_orderdate: string,
+ o_orderpriority: string,
+ o_clerk: string,
+ o_shippriority: int32,
+ o_comment: string
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+create dataset Orders(OrderType)
+ partitioned by key o_orderkey on group1;
+
+write output to nc1:"/tmp/push_limit.adm";
+
+for $o in dataset('Orders')
+where $o.o_totalprice > 100
+order by $o.o_orderkey
+limit 10 offset 5
+return { "price": $o.o_totalprice, "date": $o.o_orderdate }
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/q1.aql b/asterix-app/src/test/resources/optimizerts/queries/q1.aql
new file mode 100644
index 0000000..8f62292
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/q1.aql
@@ -0,0 +1,37 @@
+drop dataverse events if exists;
+create dataverse events;
+use dataverse events;
+
+
+create type AddressType as closed {
+ street: string,
+ city: string,
+ zip: string,
+ latlong: point
+}
+
+create type UserType as open {
+ name: string,
+ email: string,
+ interests: {{string}},
+ address: AddressType,
+ member_of: {{
+ {
+ sig_name: string,
+ chapter_name: string,
+ member_since: date
+ }
+ }}
+}
+
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset User(UserType) partitioned by key name on group1;
+
+write output to nc1:"/tmp/q1.adm";
+
+for $user in dataset('User')
+where some $i in $user.interests
+ satisfies $i = "movies"
+return {"name": $user.name}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/q2.aql b/asterix-app/src/test/resources/optimizerts/queries/q2.aql
new file mode 100644
index 0000000..17b1f86
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/q2.aql
@@ -0,0 +1,52 @@
+drop dataverse events if exists;
+create dataverse events;
+use dataverse events;
+
+
+create type AddressType as closed {
+ street: string,
+ city: string,
+ zip: string
+ //, latlong: point
+}
+
+create type EventType as closed {
+ name: string,
+ location: AddressType ?,
+ organizers: {{
+ {
+ name: string,
+ role: string
+ }
+ }},
+ sponsoring_sigs: [
+ {
+ sig_name: string,
+ chapter_name: string
+ }
+ ],
+ interest_keywords: {{string}},
+ price: double?,
+ start_time: datetime,
+ end_time: datetime
+}
+
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset Event(EventType) partitioned by key name on group1;
+
+write output to nc1:"/tmp/q2.adm";
+
+for $event in dataset('Event')
+for $sponsor in $event.sponsoring_sigs
+let $es := { "event": $event, "sponsor": $sponsor }
+group by $sig_name := $sponsor.sig_name with $es
+let $sig_sponsorship_count := count($es)
+let $by_chapter :=
+ for $e in $es
+ group by $chapter_name := $e.sponsor.chapter_name with $e
+ return { "chapter_name": $chapter_name, "escount" : count($e) }
+order by $sig_sponsorship_count desc
+limit 5
+return { "sig_name": $sig_name, "total_count": $sig_sponsorship_count, "chapter_breakdown": $by_chapter }
diff --git a/asterix-app/src/test/resources/optimizerts/queries/q3_shipping_priority.aql b/asterix-app/src/test/resources/optimizerts/queries/q3_shipping_priority.aql
new file mode 100644
index 0000000..89c6fd9
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/q3_shipping_priority.aql
@@ -0,0 +1,83 @@
+drop dataverse q3_shipping_priority if exists;
+
+create dataverse q3_shipping_priority;
+
+use dataverse q3_shipping_priority;
+
+create type LineItemType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32,
+ l_linenumber: int32,
+ l_quantity: double,
+ l_extendedprice: double,
+ l_discount: double,
+ l_tax: double,
+ l_returnflag: string,
+ l_linestatus: string,
+ l_shipdate: string,
+ l_commitdate: string,
+ l_receiptdate: string,
+ l_shipinstruct: string,
+ l_shipmode: string,
+ l_comment: string
+}
+
+create type OrderType as closed {
+ o_orderkey: int32,
+ o_custkey: int32,
+ o_orderstatus: string,
+ o_totalprice: double,
+ o_orderdate: string,
+ o_orderpriority: string,
+ o_clerk: string,
+ o_shippriority: int32,
+ o_comment: string
+}
+
+create type CustomerType as closed {
+ c_custkey: int32,
+ c_name: string,
+ c_address: string,
+ c_nationkey: int32,
+ c_phone: string,
+ c_acctbal: double,
+ c_mktsegment: string,
+ c_comment: string
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset LineItems(LineItemType)
+ partitioned by key l_orderkey, l_linenumber on group1;
+create dataset Orders(OrderType)
+ partitioned by key o_orderkey on group1;
+create dataset Customers(CustomerType)
+ partitioned by key c_custkey on group1;
+
+write output to nc1:"/tmp/q3_shipping_priority.adm";
+
+for $c in dataset('Customers')
+for $o in dataset('Orders')
+where
+ $c.c_mktsegment = 'BUILDING' and $c.c_custkey = $o.o_custkey
+for $l in dataset('LineItems')
+where
+ $l.l_orderkey = $o.o_orderkey and
+ $o.o_orderdate < '1995-03-15' and $l.l_shipdate > '1995-03-15'
+ /*+ hash */
+group by $l_orderkey := $l.l_orderkey, $o_orderdate := $o.o_orderdate, $o_shippriority := $o.o_shippriority
+ with $l
+let $revenue := sum (
+ for $i in $l
+ return
+ $i.l_extendedprice * (1 - $i.l_discount)
+)
+order by $revenue desc, $o_orderdate
+limit 10
+return {
+ "l_orderkey": $l_orderkey,
+ "revenue": $revenue,
+ "o_orderdate": $o_orderdate,
+ "o_shippriority": $o_shippriority
+}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/q5_local_supplier_volume.aql b/asterix-app/src/test/resources/optimizerts/queries/q5_local_supplier_volume.aql
new file mode 100644
index 0000000..6b5ccfa
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/q5_local_supplier_volume.aql
@@ -0,0 +1,122 @@
+drop dataverse q5_local_supplier if exists;
+
+create dataverse q5_local_supplier;
+
+use dataverse q5_local_supplier;
+
+create type LineItemType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32,
+ l_linenumber: int32,
+ l_quantity: double,
+ l_extendedprice: double,
+ l_discount: double,
+ l_tax: double,
+ l_returnflag: string,
+ l_linestatus: string,
+ l_shipdate: string,
+ l_commitdate: string,
+ l_receiptdate: string,
+ l_shipinstruct: string,
+ l_shipmode: string,
+ l_comment: string
+}
+
+create type OrderType as closed {
+ o_orderkey: int32,
+ o_custkey: int32,
+ o_orderstatus: string,
+ o_totalprice: double,
+ o_orderdate: string,
+ o_orderpriority: string,
+ o_clerk: string,
+ o_shippriority: int32,
+ o_comment: string
+}
+
+create type CustomerType as closed {
+ c_custkey: int32,
+ c_name: string,
+ c_address: string,
+ c_nationkey: int32,
+ c_phone: string,
+ c_acctbal: double,
+ c_mktsegment: string,
+ c_comment: string
+}
+
+create type SupplierType as closed {
+ s_suppkey: int32,
+ s_name: string,
+ s_address: string,
+ s_nationkey: int32,
+ s_phone: string,
+ s_acctbal: double,
+ s_comment: string
+}
+
+create type NationType as closed {
+ n_nationkey: int32,
+ n_name: string,
+ n_regionkey: int32,
+ n_comment: string
+}
+
+create type RegionType as closed {
+ r_regionkey: int32,
+ r_name: string,
+ r_comment: string
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset LineItems(LineItemType)
+ partitioned by key l_orderkey, l_linenumber on group1;
+create dataset Orders(OrderType)
+ partitioned by key o_orderkey on group1;
+create dataset Customers(CustomerType)
+ partitioned by key c_custkey on group1;
+create dataset Suppliers(SupplierType)
+ partitioned by key s_suppkey on group1;
+create dataset Nations(NationType)
+ partitioned by key n_nationkey on group1;
+create dataset Regions(RegionType)
+ partitioned by key r_regionkey on group1;
+
+write output to nc1:"/tmp/q5_local_supplier.adm";
+
+
+for $c in dataset('Customers')
+for $o1 in
+( for $o in dataset('Orders')
+ for $l1 in (
+ for $l in dataset('LineItems')
+ for $s1 in
+ ( for $s in dataset('Suppliers')
+ for $n1 in
+ ( for $n in dataset('Nations')
+ for $r in dataset('Regions')
+ where $n.n_regionkey = $r.r_regionkey and $r.r_name = 'ASIA'
+ 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 >= '1994-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
+let $revenue := sum (
+ for $i in $o1
+ return
+ $i.l_extendedprice * (1 - $i.l_discount)
+)
+order by $revenue desc
+return
+ { "n_name": $n_name, "revenue": $revenue }
diff --git a/asterix-app/src/test/resources/optimizerts/queries/record_access.aql b/asterix-app/src/test/resources/optimizerts/queries/record_access.aql
new file mode 100644
index 0000000..f69f28c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/record_access.aql
@@ -0,0 +1,9 @@
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"/tmp/rec_access.adm";
+
+let $r := { "a": 2 }
+let $a := $r.a
+return $a
diff --git a/asterix-app/src/test/resources/optimizerts/queries/rtree-secondary-index.aql b/asterix-app/src/test/resources/optimizerts/queries/rtree-secondary-index.aql
new file mode 100644
index 0000000..d525458
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/rtree-secondary-index.aql
@@ -0,0 +1,32 @@
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type MyRecord as closed {
+ id: int32,
+ point: point,
+ kwds: string,
+ line1: line,
+ line2: line,
+ poly1: polygon,
+ poly2: polygon,
+ rec: rectangle
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset MyData(MyRecord)
+ partitioned by key id on group1;
+
+load dataset MyData
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/spatial/spatialData.json"),("format"="adm")) pre-sorted;
+
+create index rtree_index_point on MyData(point) type rtree;
+
+
+write output to nc1:"rttest/index_rtree-secondary-index.adm";
+
+for $o in dataset('MyData')
+where spatial-intersect($o.point, create-polygon(create-point(4.0,1.0), create-point(4.0,4.0), create-point(12.0,4.0), create-point(12.0,1.0)))
+return {"id":$o.id}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/scan-delete-all.aql b/asterix-app/src/test/resources/optimizerts/queries/scan-delete-all.aql
new file mode 100644
index 0000000..d30b7f8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/scan-delete-all.aql
@@ -0,0 +1,38 @@
+drop dataverse tpch if exists;
+create dataverse tpch;
+use dataverse tpch;
+
+create type LineItemType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32,
+ l_linenumber: int32,
+ l_quantity: double,
+ l_extendedprice: double,
+ l_discount: double,
+ l_tax: double,
+ l_returnflag: string,
+ l_linestatus: string,
+ l_shipdate: string,
+ l_commitdate: string,
+ l_receiptdate: string,
+ l_shipinstruct: string,
+ l_shipmode: string,
+ l_comment: string
+}
+
+create type LineIDType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset LineItems_q1(LineItemType)
+ partitioned by key l_orderkey on group1;
+
+create dataset LineID(LineIDType)
+ partitioned by key l_orderkey on group1;
+
+delete $l from dataset LineItems_q1;
diff --git a/asterix-app/src/test/resources/optimizerts/queries/scan-delete-rtree-secondary-index.aql b/asterix-app/src/test/resources/optimizerts/queries/scan-delete-rtree-secondary-index.aql
new file mode 100644
index 0000000..5f1e8d3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/scan-delete-rtree-secondary-index.aql
@@ -0,0 +1,27 @@
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type MyRecord as closed {
+ id: int32,
+ point: point,
+ kwds: string,
+ line1: line,
+ line2: line,
+ poly1: polygon,
+ poly2: polygon,
+ rec: rectangle
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset MyData(MyRecord)
+ partitioned by key id on group1;
+
+load dataset MyData
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/spatial/spatialData.json"),("format"="adm")) pre-sorted;
+
+create index rtree_index_point on MyData(point) type rtree;
+
+delete $m from dataset MyData where $m.id>5;
diff --git a/asterix-app/src/test/resources/optimizerts/queries/scan-delete.aql b/asterix-app/src/test/resources/optimizerts/queries/scan-delete.aql
new file mode 100644
index 0000000..9bf6a4c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/scan-delete.aql
@@ -0,0 +1,39 @@
+drop dataverse tpch if exists;
+create dataverse tpch;
+use dataverse tpch;
+
+create type LineItemType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32,
+ l_linenumber: int32,
+ l_quantity: double,
+ l_extendedprice: double,
+ l_discount: double,
+ l_tax: double,
+ l_returnflag: string,
+ l_linestatus: string,
+ l_shipdate: string,
+ l_commitdate: string,
+ l_receiptdate: string,
+ l_shipinstruct: string,
+ l_shipmode: string,
+ l_comment: string
+}
+
+
+create type LineIDType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset LineItems_q1(LineItemType)
+ partitioned by key l_orderkey on group1;
+
+create dataset LineID(LineIDType)
+ partitioned by key l_orderkey on group1;
+
+delete $l from dataset LineItems_q1 where $l.l_shipdate <= '1998-09-02';
diff --git a/asterix-app/src/test/resources/optimizerts/queries/scan-insert-secondary-index.aql b/asterix-app/src/test/resources/optimizerts/queries/scan-insert-secondary-index.aql
new file mode 100644
index 0000000..afecc04
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/scan-insert-secondary-index.aql
@@ -0,0 +1,49 @@
+drop dataverse tpch if exists;
+create dataverse tpch;
+use dataverse tpch;
+
+create type LineItemType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32,
+ l_linenumber: int32,
+ l_quantity: double,
+ l_extendedprice: double,
+ l_discount: double,
+ l_tax: double,
+ l_returnflag: string,
+ l_linestatus: string,
+ l_shipdate: string,
+ l_commitdate: string,
+ l_receiptdate: string,
+ l_shipinstruct: string,
+ l_shipmode: string,
+ l_comment: string
+}
+
+create type LineIDType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset LineItems_q1(LineItemType)
+ partitioned by key l_orderkey on group1;
+
+create dataset LineID(LineIDType)
+ partitioned by key l_orderkey on group1;
+
+create index idx_LineID_partkey on LineID(l_partkey);
+create index idx_LineID_suppkey on LineID(l_suppkey);
+
+insert into dataset LineID (
+for $l in dataset('LineItems_q1')
+// where $l.l_shipdate <= '1998-09-02'
+ return {
+ "l_orderkey": $l.l_orderkey,
+ "l_partkey": $l.l_partkey,
+ "l_suppkey": $l.l_partkey
+ }
+);
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/optimizerts/queries/scan-insert.aql b/asterix-app/src/test/resources/optimizerts/queries/scan-insert.aql
new file mode 100644
index 0000000..beb73a7
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/scan-insert.aql
@@ -0,0 +1,46 @@
+drop dataverse tpch if exists;
+create dataverse tpch;
+use dataverse tpch;
+
+create type LineItemType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32,
+ l_linenumber: int32,
+ l_quantity: double,
+ l_extendedprice: double,
+ l_discount: double,
+ l_tax: double,
+ l_returnflag: string,
+ l_linestatus: string,
+ l_shipdate: string,
+ l_commitdate: string,
+ l_receiptdate: string,
+ l_shipinstruct: string,
+ l_shipmode: string,
+ l_comment: string
+}
+
+create type LineIDType as closed {
+ l_orderkey: int32,
+ l_partkey: int32,
+ l_suppkey: int32
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset LineItems_q1(LineItemType)
+ partitioned by key l_orderkey on group1;
+
+create dataset LineID(LineIDType)
+ partitioned by key l_orderkey on group1;
+
+insert into dataset LineID (
+for $l in dataset('LineItems_q1')
+// where $l.l_shipdate <= '1998-09-02'
+ return {
+ "l_orderkey": $l.l_orderkey,
+ "l_partkey": $l.l_partkey,
+ "l_suppkey": $l.l_partkey
+ }
+);
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/optimizerts/queries/sort-cust.aql b/asterix-app/src/test/resources/optimizerts/queries/sort-cust.aql
new file mode 100644
index 0000000..ec4f6f1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/sort-cust.aql
@@ -0,0 +1,37 @@
+drop dataverse custlimit if exists;
+
+create dataverse custlimit;
+
+use dataverse custlimit;
+
+create type AddressType as closed {
+ number: int32,
+ street: string,
+ city: string
+}
+
+create type CustomerType as closed {
+ cid: int32,
+ name: string,
+ age: int32?,
+ address: AddressType?,
+ lastorder: {
+ oid: int32,
+ total: float
+ }
+}
+
+
+
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset Customers(CustomerType)
+ partitioned by key cid on group1;
+
+write output to nc1:"/tmp/custlimit.adm";
+
+for $c in dataset('Customers')
+order by $c.age
+limit 10 // offset 10000
+return {"custname":$c.name, "custage":$c.age}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/unnest_list_in_subplan.aql b/asterix-app/src/test/resources/optimizerts/queries/unnest_list_in_subplan.aql
new file mode 100644
index 0000000..5a943f1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/unnest_list_in_subplan.aql
@@ -0,0 +1,37 @@
+drop dataverse fuzzyjoin if exists;
+create dataverse fuzzyjoin;
+use dataverse fuzzyjoin;
+
+create type DBLPType as closed {
+ id: int32,
+ dblpid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create type TOKENSRANKEDADMType as closed {
+ token: int32,
+ rank: int32
+}
+
+create nodegroup group1 if not exists on nc1, nc2;
+
+create dataset DBLP(DBLPType) partitioned by key id on group1;
+create dataset TOKENSRANKEDADM(TOKENSRANKEDADMType) partitioned by key rank on group1;
+
+write output to nc1:'rttest/unnest_list_in_subplan.adm';
+
+ //
+ // -- - Stage 2 - --
+ //
+ for $paperDBLP in dataset('DBLP')
+ let $idDBLP := $paperDBLP.id
+ let $tokensUnrankedDBLP := counthashed-word-tokens($paperDBLP.title)
+ let $tokensDBLP :=
+ for $tokenUnranked in $tokensUnrankedDBLP
+ for $tokenRanked in dataset('TOKENSRANKEDADM')
+ where $tokenUnranked = $tokenRanked.token
+ order by $tokenRanked.rank
+ return $tokenRanked.rank
+ return {'id': $idDBLP, 'tokens': $tokensDBLP}