asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ima...@apache.org
Subject [41/58] [abbrv] [partial] incubator-asterixdb git commit: Added support of typed indexes over open fields & indexes over nested fields
Date Fri, 24 Apr 2015 18:43:07 GMT
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-join_03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-join_03.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-join_03.aql
new file mode 100644
index 0000000..6261eb8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-join_03.aql
@@ -0,0 +1,28 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s primary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    fname : string,
+    lname : string
+}
+
+create type Name as open {
+    nested : Nametmp
+}
+
+create dataset Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-join_03.adm";
+
+for $emp1 in dataset('Names') 
+for $emp2 in dataset('Names') 
+where $emp1.nested.fname /*+ indexnl*/= $emp2.nested.fname and $emp1.nested.lname /*+ indexnl*/= $emp2.nested.lname
+return {"emp1": $emp1, "emp2": $emp2 }
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_01.aql
new file mode 100644
index 0000000..df3d948
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_01.aql
@@ -0,0 +1,28 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its primary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    fname : string,
+    lname : string
+}
+
+create type Name as open {
+    nested : Nametmp
+}
+
+create dataset Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-prefix-join_01.adm";
+
+for $emp1 in dataset('Names') 
+for $emp2 in dataset('Names') 
+where $emp1.nested.fname /*+ indexnl*/< $emp2.nested.fname and $emp1.nested.lname /*+ indexnl*/> $emp2.nested.lname
+return {"emp1": $emp1, "emp2": $emp2 }
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_02.aql
new file mode 100644
index 0000000..33730b2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_02.aql
@@ -0,0 +1,28 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its primary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    fname : string,
+    lname : string
+}
+
+create type Name as open {
+    nested : Nametmp
+}
+
+create dataset Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-prefix-join_02.adm";
+
+for $emp1 in dataset('Names') 
+for $emp2 in dataset('Names') 
+where $emp1.nested.fname /*+ indexnl*/> $emp2.nested.fname and $emp1.nested.lname /*+ indexnl*/< $emp2.nested.lname
+return {"emp1": $emp1, "emp2": $emp2 }
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_03.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_03.aql
new file mode 100644
index 0000000..e5c60ed
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_03.aql
@@ -0,0 +1,28 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its primary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    fname : string,
+    lname : string
+}
+
+create type Name as open {
+    nested : Nametmp
+}
+
+create dataset Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-prefix-join_03.adm";
+
+for $emp1 in dataset('Names') 
+for $emp2 in dataset('Names') 
+where $emp1.nested.fname /*+ indexnl*/> $emp2.nested.fname and $emp1.nested.lname /*+ indexnl*/= $emp2.nested.lname
+return {"emp1": $emp1, "emp2": $emp2 }
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_04.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_04.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_04.aql
new file mode 100644
index 0000000..376c18d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_04.aql
@@ -0,0 +1,28 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its primary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    fname : string,
+    lname : string
+}
+
+create type Name as open {
+    nested : Nametmp
+}
+
+create dataset Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-prefix-join_04.adm";
+
+for $emp1 in dataset('Names') 
+for $emp2 in dataset('Names') 
+where $emp1.nested.fname /*+ indexnl*/< $emp2.nested.fname and $emp1.nested.lname /*+ indexnl*/= $emp2.nested.lname
+return {"emp1": $emp1, "emp2": $emp2 }
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_05.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_05.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_05.aql
new file mode 100644
index 0000000..f881ed1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_05.aql
@@ -0,0 +1,28 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its primary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    fname : string,
+    lname : string
+}
+
+create type Name as open {
+    nested : Nametmp
+}
+
+create dataset Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-prefix-join_05.adm";
+
+for $emp1 in dataset('Names') 
+for $emp2 in dataset('Names') 
+where $emp1.nested.fname /*+ indexnl*/= $emp2.nested.fname and $emp1.nested.lname /*+ indexnl*/> $emp2.nested.lname
+return {"emp1": $emp1, "emp2": $emp2 }
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_06.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_06.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_06.aql
new file mode 100644
index 0000000..c81c8da
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-composite-key-prefix-join_06.aql
@@ -0,0 +1,28 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its primary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    fname : string,
+    lname : string
+}
+
+create type Name as open {
+    nested : Nametmp
+}
+
+create dataset Names(Name) primary key nested.fname,nested.lname;
+
+write output to nc1:"rttest/btree-index-join_primary-composite-key-prefix-prefix-join_06.adm";
+
+for $emp1 in dataset('Names') 
+for $emp2 in dataset('Names') 
+where $emp1.nested.fname /*+ indexnl*/= $emp2.nested.fname and $emp1.nested.lname /*+ indexnl*/< $emp2.nested.lname
+return {"emp1": $emp1, "emp2": $emp2 }
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join-multipred.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join-multipred.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join-multipred.aql
new file mode 100644
index 0000000..58ba13e
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join-multipred.aql
@@ -0,0 +1,56 @@
+/*
+ * Description    : Equi joins two datasets, Customers and Orders, based on the customer id.
+ *                  Given the 'indexnl' hint we expect the join to be transformed
+ *                  into an indexed nested-loop join using Customers' primary index.
+ *                  We expect the additional predicates to be put into a select above the
+ *                  primary index search.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type AddressType as closed {
+  number: int32,
+  street: string,
+  city: string
+}
+
+create type CustomerTypetmp as closed {
+  cid: int32,
+  name: string,
+  age: int32?,
+  address: AddressType?,
+  lastorder: {
+    oid: int32,
+    total: float
+  }
+}
+
+create type OrderTypetmp as closed {
+  oid: int32,
+  cid: int32,
+  orderstatus: string,
+  orderpriority: string,
+  clerk: string,
+  total: float
+}
+
+create type CustomerType as closed {
+  nested : CustomerTypetmp
+}
+
+create type OrderType as closed {
+  nested : OrderTypetmp
+}
+
+create dataset Customers(CustomerType) primary key nested.cid;
+create dataset Orders(OrderType) primary key nested.oid;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join-multipred.adm";
+
+for $c in dataset('Customers')
+for $o in dataset('Orders')
+where $c.nested.cid /*+ indexnl */ = $o.nested.cid and $c.nested.name < $o.nested.orderstatus and $c.nested.age < $o.nested.cid
+return {"customer":$c.nested, "order": $o.nested}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join-neg_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join-neg_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join-neg_01.aql
new file mode 100644
index 0000000..5808fa9
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join-neg_01.aql
@@ -0,0 +1,33 @@
+/*
+ * Description  : This is a negative test, mis-spelt/incorrect HINT should result in
+ *                a plan not using an indexed-nested loops join strategy. We expect a hash join.
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestTypetmp as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create type test1.TestType as open {
+          nested : TestTypetmp
+}
+
+create dataset test1.DsOne(TestType) primary key nested.key1;
+create dataset test1.DsTwo(TestType) primary key nested.key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join-neg_01.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.nested.key1 /*+ index */ = $y.nested.key2
+return $x
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_01.aql
new file mode 100644
index 0000000..59ce49d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_01.aql
@@ -0,0 +1,33 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan.
+ *              : We expect a plan that hash-exchanges internal dataset DsTwo, then probes internal dataset DsOne's primary index.
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestTypetmp as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create type test1.TestType as open {
+          nested : TestTypetmp
+}
+
+create dataset test1.DsOne(TestType) primary key nested.key1;
+create dataset test1.DsTwo(TestType) primary key nested.key1;
+
+// Please note content enclosed in the comment in the predicate is a HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join_01.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.nested.key1 /*+ indexnl */ = $y.nested.key2
+return $x
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_02.aql
new file mode 100644
index 0000000..f77a4fb
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_02.aql
@@ -0,0 +1,33 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan.
+ *              : We expect a plan that hash-exchanges internal dataset DsTwo, then probes internal dataset DsOne's primary index.
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestTypetmp as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create type test1.TestType as open {
+          nested : TestTypetmp
+}
+
+create dataset test1.DsOne(TestType) primary key nested.key1;
+create dataset test1.DsTwo(TestType) primary key nested.key1;
+
+// Please note content enclosed in the comment in the predicate is a HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join_02.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.nested.key2 /*+ indexnl */ = $y.nested.key1
+return $x
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_03.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_03.aql
new file mode 100644
index 0000000..5c07982
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_03.aql
@@ -0,0 +1,54 @@
+/*
+ * Description    : Equi joins two datasets, Customers and Orders, based on the customer id.
+ *                  Given the 'indexnl' hint we expect the join to be transformed
+ *                  into an indexed nested-loop join using Customers' primary index.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type AddressType as closed {
+  number: int32,
+  street: string,
+  city: string
+}
+
+create type CustomerTypetmp as closed {
+  cid: int32,
+  name: string,
+  age: int32?,
+  address: AddressType?,
+  lastorder: {
+    oid: int32,
+    total: float
+  }
+}
+
+create type OrderTypetmp as closed {
+  oid: int32,
+  cid: int32,
+  orderstatus: string,
+  orderpriority: string,
+  clerk: string,
+  total: float
+}
+
+create type CustomerType as closed {
+  nested : CustomerTypetmp
+}
+
+create type OrderType as closed {
+  nested : OrderTypetmp
+}
+
+create dataset Customers(CustomerType) primary key nested.cid;
+create dataset Orders(OrderType) primary key nested.oid;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join_04.adm";
+
+for $c in dataset('Customers')
+for $o in dataset('Orders')
+where $c.nested.cid /*+ indexnl */ = $o.nested.cid
+return {"customer":$c.nested, "order": $o.nested}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_04.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_04.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_04.aql
new file mode 100644
index 0000000..e20a44f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_04.aql
@@ -0,0 +1,54 @@
+/*
+ * Description    : Equi joins two datasets, Customers and Orders, based on the customer id.
+ *                  Given the 'indexnl' hint we expect the join to be transformed
+ *                  into an indexed nested-loop join using Customers' primary index.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type AddressType as closed {
+  number: int32,
+  street: string,
+  city: string
+}
+
+create type CustomerTypetmp as closed {
+  cid: int32,
+  name: string,
+  age: int32?,
+  address: AddressType?,
+  lastorder: {
+    oid: int32,
+    total: float
+  }
+}
+
+create type OrderTypetmp as closed {
+  oid: int32,
+  cid: int32,
+  orderstatus: string,
+  orderpriority: string,
+  clerk: string,
+  total: float
+}
+
+create type CustomerType as closed {
+  nested : CustomerTypetmp
+}
+
+create type OrderType as closed {
+  nested : OrderTypetmp
+}
+
+create dataset Customers(CustomerType) primary key nested.cid;
+create dataset Orders(OrderType) primary key nested.oid;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join_05.adm";
+
+for $o in dataset('Orders')
+for $c in dataset('Customers')
+where $o.nested.cid /*+ indexnl */ = $c.nested.cid
+return {"customer":$c.nested, "order": $o.nested}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_05.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_05.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_05.aql
new file mode 100644
index 0000000..53e191f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-equi-join_05.aql
@@ -0,0 +1,40 @@
+/*
+ * Description    : Self-equi joins a dataset, Customers, based on the customer id.
+ *                  Given the 'indexnl' hint we expect the join to be transformed
+ *                  into an indexed nested-loop join using Customers' primary index.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type AddressType as closed {
+  number: int32,
+  street: string,
+  city: string
+}
+
+create type CustomerTypetmp as closed {
+  cid: int32,
+  name: string,
+  age: int32?,
+  address: AddressType?,
+  lastorder: {
+    oid: int32,
+    total: float
+  }
+}
+
+create type CustomerType as closed {
+  nested : CustomerTypetmp
+}
+
+create dataset Customers(CustomerType) primary key nested.cid;
+
+write output to nc1:"rttest/btree-index-join_primary-equi-join_06.adm";
+
+for $c1 in dataset('Customers')
+for $c2 in dataset('Customers')
+where $c1.nested.cid /*+ indexnl */ = $c2.nested.cid
+return {"customer1":$c1.nested, "customer2":$c2.nested}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-ge-join_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-ge-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-ge-join_01.aql
new file mode 100644
index 0000000..a902f2f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-ge-join_01.aql
@@ -0,0 +1,33 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan.
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOne's primary index.
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestTypetmp as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create type test1.TestType as open {
+          nested : TestTypetmp
+}
+
+create dataset test1.DsOne(TestType) primary key nested.key1;
+create dataset test1.DsTwo(TestType) primary key nested.key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-ge-join_01.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.nested.key1 /*+ indexnl */ >= $y.nested.key2
+return $x
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-gt-join_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-gt-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-gt-join_01.aql
new file mode 100644
index 0000000..073fb1a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-gt-join_01.aql
@@ -0,0 +1,33 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan.
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOnes primary index.
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestTypetmp as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create type test1.TestType as open {
+          nested : TestTypetmp
+}
+
+create dataset test1.DsOne(TestType) primary key nested.key1;
+create dataset test1.DsTwo(TestType) primary key nested.key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-gt-join_01.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.nested.key1 /*+ indexnl */ > $y.nested.key2
+return $x
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-le-join_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-le-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-le-join_01.aql
new file mode 100644
index 0000000..edd14a3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-le-join_01.aql
@@ -0,0 +1,33 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan.
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOnes primary index.
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestTypetmp as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create type test1.TestType as open {
+          nested : TestTypetmp
+}
+
+create dataset test1.DsOne(TestType) primary key nested.key1;
+create dataset test1.DsTwo(TestType) primary key nested.key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-le-join_01.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.nested.key1 /*+ indexnl */ <= $y.nested.key2
+return $x
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-lt-join_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-lt-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-lt-join_01.aql
new file mode 100644
index 0000000..c9652fe
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/primary-lt-join_01.aql
@@ -0,0 +1,33 @@
+/*
+ * Description  : Notice the query hint to use an indexed nested-loops join plan.
+ *              : We expect a plan that broadcasts internal dataset DsTwo, then probes internal dataset DsOnes primary index.
+ * Expected Res : Success
+ * Date         : 29th November 2012
+ */
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+create type test1.TestTypetmp as open {
+          key1: int32,
+          key2: int32,
+          fname : string,
+          lname : string
+}
+
+create type test1.TestType as open {
+          nested : TestTypetmp
+}
+
+create dataset test1.DsOne(TestType) primary key nested.key1;
+create dataset test1.DsTwo(TestType) primary key nested.key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+write output to nc1:"rttest/btree-index-join_primary-lt-join_01.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.nested.key1 /*+ indexnl */ < $y.nested.key2
+return $x
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/secondary-equi-join-multiindex.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/secondary-equi-join-multiindex.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/secondary-equi-join-multiindex.aql
new file mode 100644
index 0000000..19db2bc
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/secondary-equi-join-multiindex.aql
@@ -0,0 +1,68 @@
+/*
+ * Description    : Equi joins two datasets, FacebookUsers and FacebookMessages, based on their user's id.
+ *                  We first expect FacebookUsers' primary index to be used
+ *                  to satisfy the range condition on it's primary key.
+ *                  FacebookMessages has a secondary btree index on author-id-copy, and given the 'indexnl' hint
+ *                  we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type EmploymentType as closed {
+  organization-name: string,
+  start-date: date,
+  end-date: date?
+}
+
+create type FacebookUserTypetmp as closed {
+  id: int32,
+  id-copy: int32,
+  alias: string,
+  name: string,
+  user-since: datetime,
+  user-since-copy: datetime,
+  friend-ids: {{ int32 }},
+  employment: [EmploymentType]
+}
+
+create type FacebookMessageTypetmp as closed {
+  message-id: int32,
+  message-id-copy: int32,
+  author-id: int32,
+  author-id-copy: int32,
+  in-response-to: int32?,
+  sender-location: point?,
+  message: string
+}
+
+create type FacebookUserType as closed {
+  nested : FacebookUserTypetmp
+}
+
+create type FacebookMessageType as closed {
+  nested : FacebookMessageTypetmp
+}
+
+create dataset FacebookUsers(FacebookUserType)
+primary key nested.id;
+
+create dataset FacebookMessages(FacebookMessageType)
+primary key nested.message-id;
+
+create index fbmIdxAutId if not exists on FacebookMessages(nested.author-id-copy);
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join-multiindex.adm";
+
+for $user in dataset('FacebookUsers')
+for $message in dataset('FacebookMessages')
+where $user.nested.id /*+ indexnl */ = $message.nested.author-id-copy
+and $user.nested.id >= 11000 and $user.nested.id <= 12000
+return {
+  "fbu-ID": $user.nested.id,
+  "fbm-auth-ID": $message.nested.author-id,
+  "uname": $user.nested.name,
+  "message": $message.nested.message
+}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/secondary-equi-join-multipred.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/secondary-equi-join-multipred.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/secondary-equi-join-multipred.aql
new file mode 100644
index 0000000..605aef1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/secondary-equi-join-multipred.aql
@@ -0,0 +1,49 @@
+/*
+ * Description    : Equi joins two datasets, DBLP and CSX, based on their title.
+ *                  DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ *                  we expect the join to be transformed into an indexed nested-loop join.
+ *                  We expect the additional predicates to be put into a select above the
+ *                  primary index search.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type DBLPTypetmp as closed {
+  id: int32,
+  dblpid: string,
+  title: string,
+  authors: string,
+  misc: string
+}
+
+create type CSXTypetmp as closed {
+  id: int32,
+  csxid: string,
+  title: string,
+  authors: string,
+  misc: string
+}
+
+create type DBLPType as closed {
+  nested : DBLPTypetmp
+}
+
+create type CSXType as closed {
+  nested : CSXTypetmp
+}
+
+create dataset DBLP(DBLPType) primary key nested.id;
+
+create dataset CSX(CSXType) primary key nested.id;
+
+create index title_index on DBLP(nested.title);
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join-multipred.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('CSX')
+where $a.nested.title /*+ indexnl */ = $b.nested.title and $a.nested.authors < $b.nested.authors and $a.nested.misc > $b.nested.misc
+return {"arec": $a, "brec": $b}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/secondary-equi-join_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/secondary-equi-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/secondary-equi-join_01.aql
new file mode 100644
index 0000000..0498510
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index-join/secondary-equi-join_01.aql
@@ -0,0 +1,47 @@
+/*
+ * Description    : Equi joins two datasets, DBLP and CSX, based on their title.
+ *                  DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ *                  we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type DBLPTypetmp as closed {
+  id: int32,
+  dblpid: string,
+  title: string,
+  authors: string,
+  misc: string
+}
+
+create type CSXTypetmp as closed {
+  id: int32,
+  csxid: string,
+  title: string,
+  authors: string,
+  misc: string
+}
+
+create type DBLPType as closed {
+  nested : DBLPTypetmp
+}
+
+create type CSXType as closed {
+  nested : CSXTypetmp
+}
+
+create dataset DBLP(DBLPType) primary key nested.id;
+
+create dataset CSX(CSXType) primary key nested.id;
+
+create index title_index on DBLP(nested.title);
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join_01.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('CSX')
+where $a.nested.title /*+ indexnl */ = $b.nested.title
+return {"arec": $a, "brec": $b}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-01.aql
new file mode 100644
index 0000000..627cbee
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-01.aql
@@ -0,0 +1,30 @@
+/*
+ *  Description     : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+// Please note this is a Negative test and the BTree index should NOT be used in the plan.
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-01.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname > "Roger"
+return $emp.nested

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-02.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-02.aql
new file mode 100644
index 0000000..4d332cf
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-02.aql
@@ -0,0 +1,30 @@
+/*
+ *  Description     : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+// This is a Negative test - prefix search, BTree index should not be used in the plan.
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-02.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname >= "Susan"
+return $emp.nested

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-03.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-03.aql
new file mode 100644
index 0000000..4acb41d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-03.aql
@@ -0,0 +1,30 @@
+/*
+ *  Description     : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+// Negative test - prefix search, BTree index should not be used.
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-03.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname < "Isa"
+return $emp.nested

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-04.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-04.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-04.aql
new file mode 100644
index 0000000..59f751e
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-04.aql
@@ -0,0 +1,30 @@
+/*
+ *  Description     : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+// Negative test - prefix search, BTree index should not be used in query plan
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-04.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname <= "Vanpatten"
+return $emp.nested

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-05.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-05.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-05.aql
new file mode 100644
index 0000000..a63538b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-05.aql
@@ -0,0 +1,30 @@
+/*
+ *  Description     : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+// Negative test - BTree index should NOT be used in query plan
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-05.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname != "Max"
+return $emp.nested

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-06.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-06.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-06.aql
new file mode 100644
index 0000000..f2c6961
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-06.aql
@@ -0,0 +1,30 @@
+/*
+ *  Description     : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+// Negative test - prefix search, BTree index should NOT be used in the query plan.
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-06.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname = "Julio"
+return $emp.nested

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-07.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-07.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-07.aql
new file mode 100644
index 0000000..c1b3b74
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-07.aql
@@ -0,0 +1,31 @@
+/*
+ *  Description     : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+// THE BTREE INDEX IN THIS CASE SHOULD NOT BE PICKED UP!!!!
+// Verify that the optimized query plan does not have the BTree search
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-07.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.lname = "Kim"
+return $emp.nested

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-08.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-08.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-08.aql
new file mode 100644
index 0000000..026a240
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-08.aql
@@ -0,0 +1,27 @@
+/*
+ *  Description     : This test is intended to verify that the primary BTree index is used in the optimized query plan
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-08.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname = "Young Seok" and $emp.nested.lname = "Kim"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-09.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-09.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-09.aql
new file mode 100644
index 0000000..30602d8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-09.aql
@@ -0,0 +1,31 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+// Negative test
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-09.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname = "Julio" or $emp.nested.lname = "Malaika"
+return $emp.nested

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-10.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-10.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-10.aql
new file mode 100644
index 0000000..1fba4a5
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-10.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification (usage) test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-10.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname > "Alex" and $emp.nested.lname < "Zach"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-11.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-11.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-11.aql
new file mode 100644
index 0000000..b82384c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-11.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan for predicates.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-11.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname > "Allan" and $emp.nested.lname < "Zubi"
+return $emp.nested

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-12.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-12.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-12.aql
new file mode 100644
index 0000000..f59d69f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-12.aql
@@ -0,0 +1,31 @@
+/*
+ *  Description     : BTree Index verification (usage) test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+// Negative test - prefix search
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-12.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname > "Allan" and $emp.nested.lname = "Xu"
+return $emp.nested

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-13.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-13.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-13.aql
new file mode 100644
index 0000000..4b30752
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-13.aql
@@ -0,0 +1,30 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+// Negative test - prefix search
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-13.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname = "Julio" and $emp.nested.lname < "Xu"
+return $emp.nested

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-14.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-14.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-14.aql
new file mode 100644
index 0000000..4624f50
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-14.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-14.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname >= "Michael" and $emp.nested.lname <= "Xu"
+return $emp.nested

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-15.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-15.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-15.aql
new file mode 100644
index 0000000..136249f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-15.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used 
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-15.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname > "Craig" and $emp.nested.lname > "Kevin" and $emp.nested.fname < "Mary" and $emp.nested.lname < "Tomes"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-16.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-16.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-16.aql
new file mode 100644
index 0000000..3ddb830
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-16.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-16.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst') 
+where $emp.nested.fname >= "Craig" and $emp.nested.lname >= "Kevin" and $emp.nested.fname <= "Mary" and $emp.nested.lname <= "Tomes"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-17.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-17.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-17.aql
new file mode 100644
index 0000000..9763020
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-17.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-17.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname <= "Craig" and $emp.nested.lname > "Kevin"
+return $emp.nested

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-18.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-18.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-18.aql
new file mode 100644
index 0000000..a481fbe
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-18.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is NOT used
+ *                  : in the optimized query plan
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-18.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname != "Michael" and $emp.nested.lname != "Carey"
+return $emp.nested

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-19.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-19.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-19.aql
new file mode 100644
index 0000000..024548d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-19.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-19.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname > "Craig" and $emp.nested.lname > "Kevin" and $emp.nested.fname <= "Mary" and $emp.nested.lname <= "Tomes"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-20.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-20.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-20.aql
new file mode 100644
index 0000000..d2ea1ed
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-20.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-20.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname >= "Craig" and $emp.nested.lname >= "Kevin" and $emp.nested.fname < "Mary" and $emp.nested.lname < "Tomes"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-21.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-21.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-21.aql
new file mode 100644
index 0000000..9028fd5
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-21.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-21.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname > "Max" 
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-22.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-22.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-22.aql
new file mode 100644
index 0000000..4806d6a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-22.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-22.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname >= "Sofia" 
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-23.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-23.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-23.aql
new file mode 100644
index 0000000..62eda47
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-23.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-23.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname < "Chen" 
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-24.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-24.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-24.aql
new file mode 100644
index 0000000..45cc15f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-24.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-24.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname <= "Julio"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-25.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-25.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-25.aql
new file mode 100644
index 0000000..cb1ef03
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-25.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-25.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname > "Neil" and $emp.nested.fname < "Roger"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-26.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-26.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-26.aql
new file mode 100644
index 0000000..4695196
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-26.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-26.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname >= "Max" and $emp.nested.fname <= "Roger"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-27.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-27.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-27.aql
new file mode 100644
index 0000000..67f3513
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-27.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 5th Feb 2013
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-27.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname > "Craig" and $emp.nested.lname > "Kevin" and $emp.nested.fname <= "Mary" and $emp.nested.lname < "Tomes"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-28.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-28.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-28.aql
new file mode 100644
index 0000000..6688add
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-28.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 5th Feb 2013
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-28.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname > "Craig" and $emp.nested.lname > "Kevin" and $emp.nested.fname < "Mary" and $emp.nested.lname <= "Tomes"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-29.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-29.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-29.aql
new file mode 100644
index 0000000..10b9370
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-29.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 5th Feb 2013
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-29.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname > "Craig" and $emp.nested.lname >= "Kevin" and $emp.nested.fname < "Mary" and $emp.nested.lname < "Tomes"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-30.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-30.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-30.aql
new file mode 100644
index 0000000..b054fa4
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-30.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 5th Feb 2013
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-30.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname >= "Craig" and $emp.nested.lname > "Kevin" and $emp.nested.fname < "Mary" and $emp.nested.lname < "Tomes"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-31.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-31.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-31.aql
new file mode 100644
index 0000000..1407fa7
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-31.aql
@@ -0,0 +1,31 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used 
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 11th Nov 2014
+ */
+
+// Positive test - prefix search
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-31.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst') 
+where $emp.nested.fname = "Julio" and $emp.nested.lname > "Xu"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-32.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-32.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-32.aql
new file mode 100644
index 0000000..685a8b7
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-primary-32.aql
@@ -0,0 +1,31 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the primary BTree index is used 
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 11th Nov 2014
+ */
+
+// Positive test - prefix search
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-32.adm";
+
+create type TestTypetmp as open {
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+// create internal dataset with primary index (composite key) defined on fname,lname fields
+create dataset testdst(TestType) primary key nested.fname,nested.lname;
+
+for $emp in dataset('testdst') 
+where $emp.nested.fname < "Julio" and $emp.nested.lname = "Xu"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-secondary-33.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-secondary-33.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-secondary-33.aql
new file mode 100644
index 0000000..ed6dd15
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-secondary-33.aql
@@ -0,0 +1,33 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the secondary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+// Please note this is a Negative test and the BTree index should NOT be used in the plan.
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-primary-31.adm";
+
+create type TestTypetmp as open {
+    id : int32,
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname,nested.lname);
+
+for $emp in dataset('testdst')
+where $emp.nested.fname > "Roger"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-secondary-34.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-secondary-34.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-secondary-34.aql
new file mode 100644
index 0000000..363ded6
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-secondary-34.aql
@@ -0,0 +1,33 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the secondary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+// This is a Negative test - prefix search, BTree index should not be used in the plan.
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-32.adm";
+
+create type TestTypetmp as open {
+    id : int32,
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname,nested.lname);
+
+for $emp in dataset('testdst')
+where $emp.nested.fname >= "Susan"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-secondary-35.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-secondary-35.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-secondary-35.aql
new file mode 100644
index 0000000..e2d8386
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-index/btree-index/btree-secondary-35.aql
@@ -0,0 +1,33 @@
+/*
+ *  Description     : BTree Index verification (usage) test
+ *                  : This test is intended to verify that the secondary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+// Negative test - prefix search, BTree index should not be used.
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-33.adm";
+
+create type TestTypetmp as open {
+    id : int32,
+    fname : string,
+    lname : string
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname,nested.lname);
+
+for $emp in dataset('testdst')
+where $emp.nested.fname < "Isa"
+return $emp


Mime
View raw message