Tests for issue 224
git-svn-id: https://asterixdb.googlecode.com/svn/branches/asterix_fix_issue_224@1119 eaa15691-b419-025a-1212-ee371bd00084
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-01.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-01.aql
new file mode 100644
index 0000000..2f8d7d6
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-01.aql
@@ -0,0 +1,26 @@
+/*
+ * 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) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+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/indexnl-02.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-02.aql
new file mode 100644
index 0000000..6961559
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-02.aql
@@ -0,0 +1,27 @@
+/*
+ * 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) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+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/indexnl-03.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-03.aql
new file mode 100644
index 0000000..2df7445
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-03.aql
@@ -0,0 +1,27 @@
+/*
+ * 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) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+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/indexnl-04.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-04.aql
new file mode 100644
index 0000000..8914c4c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-04.aql
@@ -0,0 +1,27 @@
+/*
+ * 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) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+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/indexnl-05.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-05.aql
new file mode 100644
index 0000000..b676cd5
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-05.aql
@@ -0,0 +1,27 @@
+/*
+ * 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) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+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/indexnl-06.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-06.aql
new file mode 100644
index 0000000..a6e5879
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-06.aql
@@ -0,0 +1,27 @@
+/*
+ * 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) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+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/indexnl-07.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-07.aql
new file mode 100644
index 0000000..a93fbc3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-07.aql
@@ -0,0 +1,35 @@
+/*
+ * 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
+ */
+
+/*
+ * Note : Index selection is arbitrary as of today, see creation date above.
+ * : There will be a time when the optimizer becomes more intelligent and deals with costs.
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+
+create type TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset DsOne(TestType) partitioned by key key1;
+create dataset DsTwo(TestType) partitioned by key key1;
+
+// create secondary B-Tree indexes on fname and lname attributes
+
+create index idx1 if not exists on DsOne(fname);
+create index idx2 if not exists on DsTwo(lname);
+
+for $x in dataset('DsOne')
+for $y in dataset('DsTwo')
+where $x.fname /*+ indexnl */ = $y.lname
+return $x
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-08.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-08.aql
new file mode 100644
index 0000000..8bddf8a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-08.aql
@@ -0,0 +1,26 @@
+/*
+ * Description : This is a negative test, mis-spelt/incorrect HINT should result in error
+ * 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) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+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/indexnl-11.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-11.aql
new file mode 100644
index 0000000..30a509d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-11.aql
@@ -0,0 +1,37 @@
+/*
+ * 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.
+ * : The two internal datasets are in two different dataverses.
+ * Expected Res : Success
+ * Date : 29th November 2012
+ */
+
+
+drop dataverse test1 if exists;
+create dataverse test1;
+
+drop dataverse test2 if exists;
+create dataverse test2;
+
+create type test1.TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create type test2.TestType as open {
+ key1: int32,
+ key2: int32,
+ fname : string,
+ lname : string
+}
+
+create dataset test1.DsOne(TestType) partitioned by key key1;
+create dataset test2.DsTwo(TestType) partitioned by key key1;
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test2.DsTwo')
+where $x.key2 /*+ indexnl */ = $y.key1
+return $x
+
diff --git a/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-22.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-22.aql
new file mode 100644
index 0000000..6961559
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-22.aql
@@ -0,0 +1,27 @@
+/*
+ * 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) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+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/indexnl-33.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-33.aql
new file mode 100644
index 0000000..89cb65a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-33.aql
@@ -0,0 +1,27 @@
+/*
+ * 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) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+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/indexnl-44.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-44.aql
new file mode 100644
index 0000000..37d0152
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-44.aql
@@ -0,0 +1,27 @@
+/*
+ * 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) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+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/indexnl-55.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-55.aql
new file mode 100644
index 0000000..dc553f5
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-55.aql
@@ -0,0 +1,27 @@
+/*
+ * 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) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+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/indexnl-66.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-66.aql
new file mode 100644
index 0000000..ed5bc0f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-66.aql
@@ -0,0 +1,27 @@
+/*
+ * 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) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+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/indexnl-88.aql b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-88.aql
new file mode 100644
index 0000000..358e9cd
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/btree-index/indexnl-88.aql
@@ -0,0 +1,26 @@
+/*
+ * Description : This is a negative test, mis-spelt/incorrect HINT should result in error
+ * 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) partitioned by key key1;
+create dataset test1.DsTwo(TestType) partitioned by key key1;
+
+// Please note content enclosed in the comment in the predicate is the HINT to the optimizer
+
+for $x in dataset('test1.DsOne')
+for $y in dataset('test1.DsTwo')
+where $x.key2 /*+ index */ = $y.key1
+return $x
+