Merged asterix_lsm_stabilization upto r1547
git-svn-id: https://asterixdb.googlecode.com/svn/trunk/asterix@1622 eaa15691-b419-025a-1212-ee371bd00084
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-multipred.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-multipred.aql
new file mode 100644
index 0000000..f235220
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-multipred.aql
@@ -0,0 +1,48 @@
+/*
+ * 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 CustomerType as closed {
+ cid: int32,
+ name: string,
+ age: int32?,
+ address: AddressType?,
+ lastorder: {
+ oid: int32,
+ total: float
+ }
+}
+
+create type OrderType as closed {
+ oid: int32,
+ cid: int32,
+ orderstatus: string,
+ orderpriority: string,
+ clerk: string,
+ total: float
+}
+
+create dataset Customers(CustomerType) primary key cid;
+create dataset Orders(OrderType) primary key 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.cid /*+ indexnl */ = $o.cid and $c.name < $o.orderstatus and $c.age < $o.cid
+return {"customer":$c, "order": $o}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-neg_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-neg_01.aql
new file mode 100644
index 0000000..8e8324f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-neg_01.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset test1.DsOne(TestType) primary key key1;
+create dataset test1.DsTwo(TestType) primary key 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.key1 /*+ index */ = $y.key2
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-neg_02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-neg_02.aql
new file mode 100644
index 0000000..8a62332
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join-neg_02.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset test1.DsOne(TestType) primary key key1;
+create dataset test1.DsTwo(TestType) primary key 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_02.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key2 /*+ index */ = $y.key1
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_01.aql
new file mode 100644
index 0000000..6950747
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_01.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset test1.DsOne(TestType) primary key key1;
+create dataset test1.DsTwo(TestType) primary key 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.key1 /*+ indexnl */ = $y.key2
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_02.aql
new file mode 100644
index 0000000..b7832fd
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_02.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset test1.DsOne(TestType) primary key key1;
+create dataset test1.DsTwo(TestType) primary key 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.key2 /*+ indexnl */ = $y.key1
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_03.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_03.aql
new file mode 100644
index 0000000..9268c2c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_03.aql
@@ -0,0 +1,46 @@
+/*
+ * 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 CustomerType as closed {
+ cid: int32,
+ name: string,
+ age: int32?,
+ address: AddressType?,
+ lastorder: {
+ oid: int32,
+ total: float
+ }
+}
+
+create type OrderType as closed {
+ oid: int32,
+ cid: int32,
+ orderstatus: string,
+ orderpriority: string,
+ clerk: string,
+ total: float
+}
+
+create dataset Customers(CustomerType) primary key cid;
+create dataset Orders(OrderType) primary key 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.cid /*+ indexnl */ = $o.cid
+return {"customer":$c, "order": $o}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_04.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_04.aql
new file mode 100644
index 0000000..3c07154
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_04.aql
@@ -0,0 +1,46 @@
+/*
+ * 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 CustomerType as closed {
+ cid: int32,
+ name: string,
+ age: int32?,
+ address: AddressType?,
+ lastorder: {
+ oid: int32,
+ total: float
+ }
+}
+
+create type OrderType as closed {
+ oid: int32,
+ cid: int32,
+ orderstatus: string,
+ orderpriority: string,
+ clerk: string,
+ total: float
+}
+
+create dataset Customers(CustomerType) primary key cid;
+create dataset Orders(OrderType) primary key 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.cid /*+ indexnl */ = $c.cid
+return {"customer":$c, "order": $o}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_05.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_05.aql
new file mode 100644
index 0000000..7da6b0c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-equi-join_05.aql
@@ -0,0 +1,36 @@
+/*
+ * 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 CustomerType as closed {
+ cid: int32,
+ name: string,
+ age: int32?,
+ address: AddressType?,
+ lastorder: {
+ oid: int32,
+ total: float
+ }
+}
+
+create dataset Customers(CustomerType) primary key 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.cid /*+ indexnl */ = $c2.cid
+return {"customer1":$c1, "customer2":$c2}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_01.aql
new file mode 100644
index 0000000..8bc306c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_01.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset test1.DsOne(TestType) primary key key1;
+create dataset test1.DsTwo(TestType) primary key 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.key1 /*+ indexnl */ >= $y.key2
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_02.aql
new file mode 100644
index 0000000..06be0e9
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-ge-join_02.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset test1.DsOne(TestType) primary key key1;
+create dataset test1.DsTwo(TestType) primary key 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_02.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key2 /*+ indexnl */ <= $y.key1
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_01.aql
new file mode 100644
index 0000000..67d75a1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_01.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset test1.DsOne(TestType) primary key key1;
+create dataset test1.DsTwo(TestType) primary key 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.key1 /*+ indexnl */ > $y.key2
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_02.aql
new file mode 100644
index 0000000..a7be083
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-gt-join_02.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset test1.DsOne(TestType) primary key key1;
+create dataset test1.DsTwo(TestType) primary key 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_02.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key2 /*+ indexnl */ < $y.key1
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_01.aql
new file mode 100644
index 0000000..66f2ace
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_01.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset test1.DsOne(TestType) primary key key1;
+create dataset test1.DsTwo(TestType) primary key 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.key1 /*+ indexnl */ <= $y.key2
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_02.aql
new file mode 100644
index 0000000..39c1d51
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-le-join_02.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset test1.DsOne(TestType) primary key key1;
+create dataset test1.DsTwo(TestType) primary key 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_02.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key2 /*+ indexnl */ >= $y.key1
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_01.aql
new file mode 100644
index 0000000..ef6a616
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_01.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset test1.DsOne(TestType) primary key key1;
+create dataset test1.DsTwo(TestType) primary key 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.key1 /*+ indexnl */ < $y.key2
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_02.aql
new file mode 100644
index 0000000..4924514
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/primary-lt-join_02.aql
@@ -0,0 +1,29 @@
+/*
+ * 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.TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset test1.DsOne(TestType) primary key key1;
+create dataset test1.DsTwo(TestType) primary key 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_02.adm";
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key2 /*+ indexnl */ > $y.key1
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join-multiindex.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join-multiindex.aql
new file mode 100644
index 0000000..c596984
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join-multiindex.aql
@@ -0,0 +1,60 @@
+/*
+ * 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 FacebookUserType 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 FacebookMessageType 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 dataset FacebookUsers(FacebookUserType)
+primary key id;
+
+create dataset FacebookMessages(FacebookMessageType)
+primary key message-id;
+
+create index fbmIdxAutId if not exists on FacebookMessages(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.id /*+ indexnl */ = $message.author-id-copy
+and $user.id >= 11000 and $user.id <= 12000
+return {
+ "fbu-ID": $user.id,
+ "fbm-auth-ID": $message.author-id,
+ "uname": $user.name,
+ "message": $message.message
+}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join-multipred.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join-multipred.aql
new file mode 100644
index 0000000..bb803b0
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join-multipred.aql
@@ -0,0 +1,41 @@
+/*
+ * 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 DBLPType as closed {
+ id: int32,
+ dblpid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create type CSXType as closed {
+ id: int32,
+ csxid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create dataset DBLP(DBLPType) primary key id;
+
+create dataset CSX(CSXType) primary key id;
+
+create index title_index on DBLP(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.title /*+ indexnl */ = $b.title and $a.authors < $b.authors and $a.misc > $b.misc
+return {"arec": $a, "brec": $b}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join_01.aql
new file mode 100644
index 0000000..8a4f056
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join_01.aql
@@ -0,0 +1,39 @@
+/*
+ * 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 DBLPType as closed {
+ id: int32,
+ dblpid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create type CSXType as closed {
+ id: int32,
+ csxid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create dataset DBLP(DBLPType) primary key id;
+
+create dataset CSX(CSXType) primary key id;
+
+create index title_index on DBLP(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.title /*+ indexnl */ = $b.title
+return {"arec": $a, "brec": $b}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join_02.aql
new file mode 100644
index 0000000..fa68cd2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join_02.aql
@@ -0,0 +1,39 @@
+/*
+ * Description : Equi joins two datasets, DBLP and CSX, based on their title.
+ * CSX 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 DBLPType as closed {
+ id: int32,
+ dblpid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create type CSXType as closed {
+ id: int32,
+ csxid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create dataset DBLP(DBLPType) primary key id;
+
+create dataset CSX(CSXType) primary key id;
+
+create index title_index on CSX(title);
+
+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.title /*+ indexnl */ = $b.title
+return {"arec": $a, "brec": $b}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join_03.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join_03.aql
new file mode 100644
index 0000000..3eedafd
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index-join/secondary-equi-join_03.aql
@@ -0,0 +1,29 @@
+/*
+ * Description : Equi self-joins a dataset, DBLP, based on its 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 DBLPType as closed {
+ id: int32,
+ dblpid: string,
+ title: string,
+ authors: string,
+ misc: string
+}
+
+create dataset DBLP(DBLPType) primary key id;
+
+create index title_index on DBLP(title);
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join_03.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('DBLP')
+where $a.title /*+ indexnl */ = $b.title
+return {"arec": $a, "brec": $b}