asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ima...@apache.org
Subject [38/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:04 GMT
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/disjunction-to-join.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/disjunction-to-join.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/disjunction-to-join.aql
new file mode 100644
index 0000000..cc9df21
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/disjunction-to-join.aql
@@ -0,0 +1,26 @@
+/*
+ * Description    : Disjunctive predicate should be transformed into collection scan.
+ *                  Secondary index should be used to probe the values retrieved from collection.
+ * Success        : Yes
+ */
+ 
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type NestedTestType as open {
+};
+
+create type TestType as open {
+  "id" : string,
+  "no-idx" : string,
+  "nested" : NestedTestType
+};
+
+
+create dataset TestSet(TestType) primary key "id";
+create index TestSetIndex on TestSet(nested.idx: string) enforced;
+
+for $x in dataset TestSet 
+where $x.nested.idx = "one" or $x.nested.idx = "two"
+return $x
\ 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-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1.aql
new file mode 100644
index 0000000..286e852
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1.aql
@@ -0,0 +1,53 @@
+/*
+ * Description  : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary btree index in index subtree.
+ * Issue        : 730, 741                
+ * Expected Res : Success
+ * Date         : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type TwitterUserType as closed {
+	screen-name: string,
+	lang: string,
+	friends-count: int32,
+	statuses-count: int32,
+	name: string,
+	followers-count: int32
+}
+
+create type TweetMessageNestedType as open {
+	tweetid: int64,
+        user: TwitterUserType,
+        sender-location: point,
+	send-time: datetime,
+        referred-topics: {{ string }},
+        message-text: string,
+	countA: int32
+}
+
+create type TweetMessageType as open {
+	nested:  TweetMessageNestedType
+}
+
+create dataset TweetMessages(TweetMessageType)
+primary key nested.tweetid;
+
+create index msgCountBIx on TweetMessages(nested.countB: int32) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_01.adm";
+
+for $t1 in dataset('TweetMessages')
+where $t1.nested.tweetid < int64("10")
+order by $t1.nested.tweetid
+return {
+"tweetid1": $t1.nested.tweetid,
+"count1":$t1.nested.countA,
+"t2info": for $t2 in dataset('TweetMessages')
+          where $t1.nested.countA /* +indexnl */= $t2.nested.countB
+          order by $t2.nested.tweetid
+          return {"tweetid2": $t2.nested.tweetid,
+                  "count2":$t2.nested.countB}
+};

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2.aql
new file mode 100644
index 0000000..5500cf0
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2.aql
@@ -0,0 +1,53 @@
+/*
+ * Description  : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary btree index in index subtree.
+ * Issue        : 730, 741                
+ * Expected Res : Success
+ * Date         : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type TwitterUserType as closed {
+	screen-name: string,
+	lang: string,
+	friends-count: int32,
+	statuses-count: int32,
+	name: string,
+	followers-count: int32
+}
+
+create type TweetMessageNestedType as open {
+	tweetid: int64,
+        user: TwitterUserType,
+        sender-location: point,
+	send-time: datetime,
+        referred-topics: {{ string }},
+        message-text: string
+}
+
+create type TweetMessageType as open {
+	nested:  TweetMessageNestedType
+}
+
+create dataset TweetMessages(TweetMessageType)
+primary key nested.tweetid;
+
+create index msgCountAIx on TweetMessages(nested.countA: int32) type btree enforced;
+create index msgCountBIx on TweetMessages(nested.countB: int32) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_01.adm";
+
+for $t1 in dataset('TweetMessages')
+where $t1.nested.tweetid < int64("10")
+order by $t1.nested.tweetid
+return {
+"tweetid1": $t1.nested.tweetid,
+"count1":$t1.nested.countA,
+"t2info": for $t2 in dataset('TweetMessages')
+          where $t1.nested.countA /* +indexnl */= $t2.nested.countB
+          order by $t2.nested.tweetid
+          return {"tweetid2": $t2.nested.tweetid,
+                  "count2":$t2.nested.countB}
+};

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1.aql
new file mode 100644
index 0000000..2520111
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1.aql
@@ -0,0 +1,55 @@
+/*
+ * Description  : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary btree index in index subtree.
+ * Issue        : 730, 741                
+ * Expected Res : Success
+ * Date         : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type TwitterUserType as closed {
+	screen-name: string,
+	lang: string,
+	friends-count: int32,
+	statuses-count: int32,
+	name: string,
+	followers-count: int32
+}
+
+create type TweetMessageNestedType as open {
+	tweetid: int64,
+        user: TwitterUserType,
+        sender-location: point,
+	send-time: datetime,
+        referred-topics: {{ string }},
+        message-text: string,
+	countA: int32
+}
+
+create type TweetMessageType as open {
+	nested: TweetMessageNestedType
+}
+
+create dataset TweetMessages(TweetMessageType)
+primary key nested.tweetid;
+
+create index msgCountBIx on TweetMessages(nested.countB: int32) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_02.adm";
+
+for $t1 in dataset('TweetMessages')
+where $t1.nested.tweetid < int64("10")
+order by $t1.nested.tweetid
+return {
+"tweetid1": $t1.nested.tweetid,
+"count1":$t1.nested.countA,
+"t2info": for $t2 in dataset('TweetMessages')
+                        where $t1.nested.countA /* +indexnl */= $t2.nested.countB and
+                        $t1.nested.tweetid != $t2.nested.tweetid
+                        order by $t2.nested.tweetid
+                        return {"tweetid2": $t2.nested.tweetid,
+                                       "count2":$t2.nested.countB}
+};
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2.aql
new file mode 100644
index 0000000..3003edc
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2.aql
@@ -0,0 +1,55 @@
+/*
+ * Description  : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary btree index in index subtree.
+ * Issue        : 730, 741                
+ * Expected Res : Success
+ * Date         : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type TwitterUserType as closed {
+	screen-name: string,
+	lang: string,
+	friends-count: int32,
+	statuses-count: int32,
+	name: string,
+	followers-count: int32
+}
+
+create type TweetMessageNestedType as open {
+	tweetid: int64,
+        user: TwitterUserType,
+        sender-location: point,
+	send-time: datetime,
+        referred-topics: {{ string }},
+        message-text: string
+}
+
+create type TweetMessageType as open {
+	nested: TweetMessageNestedType
+}
+
+create dataset TweetMessages(TweetMessageType)
+primary key nested.tweetid;
+
+create index msgCountAIx on TweetMessages(nested.countA: int32) type btree enforced;
+create index msgCountBIx on TweetMessages(nested.countB: int32) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_02.adm";
+
+for $t1 in dataset('TweetMessages')
+where $t1.nested.tweetid < int64("10")
+order by $t1.nested.tweetid
+return {
+"tweetid1": $t1.nested.tweetid,
+"count1":$t1.nested.countA,
+"t2info": for $t2 in dataset('TweetMessages')
+                        where $t1.nested.countA /* +indexnl */= $t2.nested.countB and
+                        $t1.nested.tweetid != $t2.nested.tweetid
+                        order by $t2.nested.tweetid
+                        return {"tweetid2": $t2.nested.tweetid,
+                                       "count2":$t2.nested.countB}
+};
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-join_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-join_01.aql
new file mode 100644
index 0000000..a9d4a87
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-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 secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    id : int32
+}
+
+create type NameType as open {
+    nested : Nametmp
+}
+
+create dataset Names(NameType) primary key nested.id;
+create index Name_idx on Names(nested.fname: string,lnested.name: string) enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-composite-key-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-open-index/btree-index-join/secondary-composite-key-join_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-join_02.aql
new file mode 100644
index 0000000..e681698
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-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 secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    id : int32
+}
+
+create type NameType as open {
+    nested : Nametmp
+}
+
+create dataset Names(NameType) primary key nested.id;
+create index Name_idx on Names(nested.fname: string,lnested.name: string) enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-composite-key-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-open-index/btree-index-join/secondary-composite-key-join_03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-join_03.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-join_03.aql
new file mode 100644
index 0000000..655a0ab
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-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 secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    id : int32
+}
+
+create type NameType as open {
+    nested : Nametmp
+}
+
+create dataset Names(NameType) primary key nested.id;
+create index Name_idx on Names(nested.fname: string,lnested.name: string) enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-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-open-index/btree-index-join/secondary-composite-key-prefix-join_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_01.aql
new file mode 100644
index 0000000..f18d7e3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-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 secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    id : int32
+}
+
+create type NameType as open {
+    nested : Nametmp
+}
+
+create dataset Names(NameType) primary key nested.id;
+create index Name_idx on Names(nested.fname: string,lnested.name: string) enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-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-open-index/btree-index-join/secondary-composite-key-prefix-join_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_02.aql
new file mode 100644
index 0000000..cd1e3f2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-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 secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    id : int32
+}
+
+create type NameType as open {
+    nested : Nametmp
+}
+
+create dataset Names(NameType) primary key nested.id;
+create index Name_idx on Names(nested.fname: string,lnested.name: string) enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-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-open-index/btree-index-join/secondary-composite-key-prefix-join_03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_03.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_03.aql
new file mode 100644
index 0000000..555a20d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-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 secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    id : int32
+}
+
+create type NameType as open {
+    nested : Nametmp
+}
+
+create dataset Names(NameType) primary key nested.id;
+create index Name_idx on Names(nested.fname: string,lnested.name: string) enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-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-open-index/btree-index-join/secondary-composite-key-prefix-join_04.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_04.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_04.aql
new file mode 100644
index 0000000..786e669
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-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 secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    id : int32
+}
+
+create type NameType as open {
+    nested : Nametmp
+}
+
+create dataset Names(NameType) primary key nested.id;
+create index Name_idx on Names(nested.fname: string,lnested.name: string) enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-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-open-index/btree-index-join/secondary-composite-key-prefix-join_05.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_05.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_05.aql
new file mode 100644
index 0000000..b324693
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-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 secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    id : int32
+}
+
+create type NameType as open {
+    nested : Nametmp
+}
+
+create dataset Names(NameType) primary key nested.id;
+create index Name_idx on Names(nested.fname: string,lnested.name: string) enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-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-open-index/btree-index-join/secondary-composite-key-prefix-join_06.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_06.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_06.aql
new file mode 100644
index 0000000..cb1d071
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-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 secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type Nametmp as open {
+    id : int32
+}
+
+create type NameType as open {
+    nested : Nametmp
+}
+
+create dataset Names(NameType) primary key nested.id;
+create index Name_idx on Names(nested.fname: string,lnested.name: string) enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-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-open-index/btree-index-join/secondary-equi-join-multiindex.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join-multiindex.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join-multiindex.aql
new file mode 100644
index 0000000..5c6c9d8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join-multiindex.aql
@@ -0,0 +1,67 @@
+/*
+ * 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 open {
+  message-id: int32,
+  message-id-copy: int32,
+  author-id: 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: int32) enforced;
+
+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-open-index/btree-index-join/secondary-equi-join-multipred.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join-multipred.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join-multipred.aql
new file mode 100644
index 0000000..851bb8c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join-multipred.aql
@@ -0,0 +1,48 @@
+/*
+ * 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 open {
+  id: int32,
+  dblpid: 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:string) enforced;
+
+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-open-index/btree-index-join/secondary-equi-join_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_01.aql
new file mode 100644
index 0000000..a1edd13
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_01.aql
@@ -0,0 +1,46 @@
+/*
+ * 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 open {
+  id: int32,
+  dblpid: 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: string) enforced;
+
+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-open-index/btree-index-join/secondary-equi-join_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_02.aql
new file mode 100644
index 0000000..bc090c9
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_02.aql
@@ -0,0 +1,46 @@
+/*
+ * Description    : Equi joins two datasets, closed DBLP and open 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 open {
+  id: int32,
+  csxid: 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 CSX(nested.title: string) enforced;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join_02.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-open-index/btree-index-join/secondary-equi-join_03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_03.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_03.aql
new file mode 100644
index 0000000..973a9c5
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_03.aql
@@ -0,0 +1,47 @@
+/*
+ * Description    : Equi joins two open 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 open {
+  id: int32,
+  dblpid: string,
+  authors: string,
+  misc: string
+}
+
+create type CSXTypetmp as open {
+  id: int32,
+  csxid: 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_DBLP on DBLP(nested.title: string) enforced;
+
+create index title_index_CSX on CSX(nested.title: string) enforced;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join_03.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-open-index/btree-index-join/secondary-equi-join_04.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_04.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_04.aql
new file mode 100644
index 0000000..ca18e75
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_04.aql
@@ -0,0 +1,32 @@
+/*
+ * Description    : Self-joins dataset DBLP, based on it's 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 open {
+  id: int32,
+  dblpid: string,
+  authors: string,
+  misc: string
+}
+
+create type DBLPType as open {
+  nested : DBLPTypetmp
+}
+
+create dataset DBLP(DBLPType) primary key nested.id;
+
+create index title_index on DBLP(nested.title: string) enforced;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join_04.adm";
+
+for $a in dataset('DBLP')
+for $a2 in dataset('DBLP')
+where $a.nested.title /*+ indexnl */ = $a2.nested.title
+return {"arec": $a, "arec2": $a2}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_05.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_05.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_05.aql
new file mode 100644
index 0000000..f572fca
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/secondary-equi-join_05.aql
@@ -0,0 +1,45 @@
+/*
+ * Description    : Equi joins two open datasets, open DBLP and closed CSX, based on their title.
+ *                  DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ *                  we *do not* 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 open {
+  id: int32,
+  dblpid: string,
+  authors: string,
+  misc: string
+}
+
+create type CSXTypetmp as open {
+  id: int32,
+  csxid: 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: string) enforced;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join_05.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-open-index/btree-index/btree-secondary-33.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-33.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-33.aql
new file mode 100644
index 0000000..c811c09
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-33.aql
@@ -0,0 +1,31 @@
+/*
+ *  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-39.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname: string,nested.lname: string) enforced;
+
+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-open-index/btree-index/btree-secondary-34.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-34.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-34.aql
new file mode 100644
index 0000000..8bb6e5e
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-34.aql
@@ -0,0 +1,31 @@
+/*
+ *  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
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+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-open-index/btree-index/btree-secondary-35.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-35.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-35.aql
new file mode 100644
index 0000000..374bf68
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-35.aql
@@ -0,0 +1,31 @@
+/*
+ *  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
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname < "Isa"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-36.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-36.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-36.aql
new file mode 100644
index 0000000..3d90625
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-36.aql
@@ -0,0 +1,31 @@
+/*
+ *  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
+ */
+
+// 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-secondary-34.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname <= "Vanpatten"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-37.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-37.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-37.aql
new file mode 100644
index 0000000..edf2a7c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-37.aql
@@ -0,0 +1,31 @@
+/*
+ *  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
+ */
+
+// 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-secondary-35.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+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-open-index/btree-index/btree-secondary-38.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-38.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-38.aql
new file mode 100644
index 0000000..0e2cb2b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-38.aql
@@ -0,0 +1,31 @@
+/*
+ *  Description     : BTree Index verification 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 in the query plan.
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-36.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+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-open-index/btree-index/btree-secondary-39.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-39.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-39.aql
new file mode 100644
index 0000000..3c8e320
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-39.aql
@@ -0,0 +1,31 @@
+/*
+ *  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
+ */
+
+// 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-secondary-37.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+for $emp in dataset('testdst')
+where $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-open-index/btree-index/btree-secondary-40.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-40.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-40.aql
new file mode 100644
index 0000000..ae4f7b2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-40.aql
@@ -0,0 +1,28 @@
+/*
+ *  Description     : BTree Index verification 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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-38.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+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-open-index/btree-index/btree-secondary-41.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-41.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-41.aql
new file mode 100644
index 0000000..e486ac4
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-41.aql
@@ -0,0 +1,31 @@
+/*
+ *  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
+ */
+
+// Negative test
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-39.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname = "Julio" or $emp.nested.lname = "Malaika"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-42.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-42.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-42.aql
new file mode 100644
index 0000000..3802e97
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-42.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification 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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-40.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+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-open-index/btree-index/btree-secondary-43.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-43.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-43.aql
new file mode 100644
index 0000000..3892edf
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-43.aql
@@ -0,0 +1,29 @@
+/*
+ *  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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-41.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname > "Allan" and $emp.nested.lname < "Zubi"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-44.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-44.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-44.aql
new file mode 100644
index 0000000..3803014
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-44.aql
@@ -0,0 +1,31 @@
+/*
+ *  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
+ */
+
+// Negative test - prefix search
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-42.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname > "Allan" 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-open-index/btree-index/btree-secondary-45.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-45.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-45.aql
new file mode 100644
index 0000000..cc21502
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-45.aql
@@ -0,0 +1,31 @@
+/*
+ *  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
+ */
+
+// Negative test - prefix search
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-43.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+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-open-index/btree-index/btree-secondary-46.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-46.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-46.aql
new file mode 100644
index 0000000..74235a5
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-46.aql
@@ -0,0 +1,29 @@
+/*
+ *  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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-44.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname >= "Michael" 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-open-index/btree-index/btree-secondary-47.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-47.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-47.aql
new file mode 100644
index 0000000..1c3dd96
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-47.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification 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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-45.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+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-open-index/btree-index/btree-secondary-48.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-48.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-48.aql
new file mode 100644
index 0000000..5f20608
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-48.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification 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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-46.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+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-open-index/btree-index/btree-secondary-49.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-49.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-49.aql
new file mode 100644
index 0000000..15e1fc4
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-49.aql
@@ -0,0 +1,29 @@
+/*
+ *  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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-47.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname <= "Craig" and $emp.nested.lname > "Kevin"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-50.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-50.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-50.aql
new file mode 100644
index 0000000..9dbfd4b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-50.aql
@@ -0,0 +1,29 @@
+/*
+ *  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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-48.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+for $emp in dataset('testdst')
+where $emp.nested.fname != "Michael" and $emp.nested.lname != "Carey"
+return $emp

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-51.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-51.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-51.aql
new file mode 100644
index 0000000..71790ed
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-51.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification 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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-49.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+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-open-index/btree-index/btree-secondary-52.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-52.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-52.aql
new file mode 100644
index 0000000..1538ebd
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-52.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification 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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-50.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+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-open-index/btree-index/btree-secondary-53.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-53.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-53.aql
new file mode 100644
index 0000000..72a3d14
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-53.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification 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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-51.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname:string,nested.lname:string) enforced;
+
+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-open-index/btree-index/btree-secondary-54.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-54.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-54.aql
new file mode 100644
index 0000000..bdd1f31
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-54.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : 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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-52.adm";
+
+create type TestTypetmp as open {
+    id : int32,
+    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: string) enforced;
+
+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-open-index/btree-index/btree-secondary-55.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-55.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-55.aql
new file mode 100644
index 0000000..4577955
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-55.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : 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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-53.adm";
+
+create type TestTypetmp as open {
+    id : int32,
+    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: string) enforced;
+
+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-open-index/btree-index/btree-secondary-56.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-56.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-56.aql
new file mode 100644
index 0000000..6cc0868
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-56.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : 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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-54.adm";
+
+create type TestTypetmp as open {
+    id : int32,
+    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: string) enforced;
+
+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-open-index/btree-index/btree-secondary-57.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-57.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-57.aql
new file mode 100644
index 0000000..2f38459
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-57.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : 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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-55.adm";
+
+create type TestTypetmp as open {
+    id : int32,
+    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: string) enforced;
+
+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-open-index/btree-index/btree-secondary-58.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-58.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-58.aql
new file mode 100644
index 0000000..cc47c77
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-58.aql
@@ -0,0 +1,29 @@
+/*
+ *  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-56.adm";
+
+create type TestTypetmp as open {
+    id : int32,
+    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: string) enforced;
+
+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-open-index/btree-index/btree-secondary-59.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-59.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-59.aql
new file mode 100644
index 0000000..202eb7e
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-59.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : 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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-57.adm";
+
+create type TestTypetmp as open {
+    id : int32,
+    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: string) enforced;
+
+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-open-index/btree-index/btree-secondary-60.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-60.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-60.aql
new file mode 100644
index 0000000..a01ee79
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-60.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : 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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-58.adm";
+
+create type TestTypetmp as open {
+    id : int32,
+    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: string) enforced;
+
+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-open-index/btree-index/btree-secondary-61.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-61.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-61.aql
new file mode 100644
index 0000000..1781fcb
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-61.aql
@@ -0,0 +1,29 @@
+/*
+ *  Description     : BTree Index verification 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
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+write output to nc1:"rttest/btree-index_btree-secondary-59.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname: string,nested.lname: string) enforced;
+
+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-open-index/btree-index/btree-secondary-62.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-62.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-62.aql
new file mode 100644
index 0000000..34478c0
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-62.aql
@@ -0,0 +1,31 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the secondary 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-secondary-62.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname: string,nested.lname: string) enforced;
+
+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-open-index/btree-index/btree-secondary-63.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-63.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-63.aql
new file mode 100644
index 0000000..e869e90
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index/btree-secondary-63.aql
@@ -0,0 +1,31 @@
+/*
+ *  Description     : BTree Index verification test
+ *                  : This test is intended to verify that the secondary 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-secondary-63.adm";
+
+create type TestTypetmp as open {
+    id : int32
+}
+
+create type TestType as open {
+    nested : TestTypetmp
+}
+
+create dataset testdst(TestType) primary key nested.id;
+
+create index sec_Idx on testdst(nested.fname: string,nested.lname: string) enforced;
+
+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-open-index/inverted-index-basic/ngram-contains-panic.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains-panic.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains-panic.aql
new file mode 100644
index 0000000..6cb5e33
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains-panic.aql
@@ -0,0 +1,32 @@
+/*
+ * Description    : Tests whether an ngram_index is applied to optimize a selection query using the contains function.
+ *                  The index should *not* be applied (see below).
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type DBLPTypetmp as open {
+  id: int32,
+  dblpid: string,
+  authors: string,
+  misc: string
+}
+
+create type DBLPType as closed {
+  nested : DBLPTypetmp
+}
+
+create dataset DBLP(DBLPType) primary key nested.id;
+
+create index ngram_index on DBLP(nested.title: string) type ngram(3) enforced;
+
+write output to nc1:"rttest/inverted-index-basic_ngram-contains-panic.adm";
+
+// Cannot optimize this query because the string constant is shorter than the gram length.
+for $o in dataset('DBLP')
+where contains($o.nested.title, "Mu")
+order by $o.nested.id
+return $o
\ 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-open-index/inverted-index-basic/ngram-contains.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains.aql
new file mode 100644
index 0000000..36aebcd
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains.aql
@@ -0,0 +1,31 @@
+/*
+ * Description    : Tests whether an ngram_index is applied to optimize a selection query using the contains function.
+ *                  The index should be applied.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type DBLPTypetmp as open {
+  id: int32,
+  dblpid: string,
+  authors: string,
+  misc: string
+}
+
+create type DBLPType as closed {
+  nested : DBLPTypetmp
+}
+
+create dataset DBLP(DBLPType) primary key nested.id;
+
+create index ngram_index on DBLP(nested.title: string) type ngram(3) enforced;
+
+write output to nc1:"rttest/inverted-index-basic_ngram-contains.adm";
+
+for $o in dataset('DBLP')
+where contains($o.nested.title, "Multimedia")
+order by $o.nested.id
+return $o
\ No newline at end of file


Mime
View raw message