asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ima...@apache.org
Subject [36/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:02 GMT
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard-check_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard-check_02.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard-check_02.aql
new file mode 100644
index 0000000..8bd6d46
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard-check_02.aql
@@ -0,0 +1,47 @@
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard-check function of their titles' 3-gram tokens.
+ *                  DBLP has a 3-gram index on title, and 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;
+set import-private-functions 'true';
+
+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 ngram_index on CSX(nested.title: string) type ngram(3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard-check_01.adm";
+
+for $a in dataset('CSX')
+for $b in dataset('DBLP')
+where similarity-jaccard-check(gram-tokens($a.nested.title, 3, false), gram-tokens($b.nested.title, 3, false), 0.5f)[0]
+      and $a.nested.id < $b.nested.id
+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/inverted-index-join/ngram-jaccard-check_03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard-check_03.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard-check_03.aql
new file mode 100644
index 0000000..af8fddf
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard-check_03.aql
@@ -0,0 +1,33 @@
+/*
+ * Description    : Self joins dataset DBLP, based on the similarity-jaccard-check function of their titles' 3-gram tokens.
+ *                  DBLP has a 3-gram index on title, and 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;
+set import-private-functions 'true';
+
+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-join_ngram-jaccard-check_01.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('DBLP')
+where similarity-jaccard-check(gram-tokens($a.nested.title, 3, false), gram-tokens($b.nested.title, 3, false), 0.5f)[0]
+      and $a.nested.id < $b.nested.id
+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/inverted-index-join/ngram-jaccard-check_04.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard-check_04.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard-check_04.aql
new file mode 100644
index 0000000..7d13f6c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard-check_04.aql
@@ -0,0 +1,48 @@
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard-check function of their titles' 3-gram tokens.
+ *                  DBLP has a 3-gram index on title, and 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;
+set import-private-functions 'true';
+
+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 ngram_index_DBLP on DBLP(nested.title: string) type ngram(3) enforced;
+
+create index ngram_index_CSX on CSX(nested.title: string) type ngram(3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard-check_01.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('CSX')
+where similarity-jaccard-check(gram-tokens($a.nested.title, 3, false), gram-tokens($b.nested.title, 3, false), 0.5f)[0]
+      and $a.nested.id < $b.nested.id
+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/inverted-index-join/ngram-jaccard-inline.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard-inline.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard-inline.aql
new file mode 100644
index 0000000..3cc4dc4
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard-inline.aql
@@ -0,0 +1,35 @@
+/*
+ * Description    : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard function of its titles' 3-gram tokens.
+ *                  DBLP has a 3-gram index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ *                  We test the inlining of variables that enable the select to be pushed into the join for subsequent optimization with an index.
+ *                  We expect the top-level equi join introduced because of surrogate optimization to be removed, since it is not necessary.
+ * Success        : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use dataverse test;
+set import-private-functions 'true';
+
+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-join-noeqjoin_ngram-jaccard-inline.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('DBLP')
+let $jacc := similarity-jaccard(gram-tokens($a.nested.title, 3, false), gram-tokens($b.nested.title, 3, false))
+where $jacc >= 0.5f and $a.nested.id < $b.nested.id
+return {"atitle": $a.nested.title, "btitle": $b.nested.title, "jacc": $jacc}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard_01.aql
new file mode 100644
index 0000000..32ae0d8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard_01.aql
@@ -0,0 +1,47 @@
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard function of their titles' 3-gram tokens.
+ *                  DBLP has a 3-gram index on title, and 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;
+set import-private-functions 'true';
+
+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 ngram_index on DBLP(nested.title: string) type ngram(3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard_01.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('CSX')
+where similarity-jaccard(gram-tokens($a.nested.title, 3, false), gram-tokens($b.nested.title, 3, false)) >= 0.5f
+      and $a.nested.id < $b.nested.id
+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/inverted-index-join/ngram-jaccard_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard_02.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard_02.aql
new file mode 100644
index 0000000..ffc4098
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard_02.aql
@@ -0,0 +1,47 @@
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard function of their titles' 3-gram tokens.
+ *                  DBLP has a 3-gram index on title, and 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;
+set import-private-functions 'true';
+
+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 ngram_index on CSX(nested.title: string) type ngram(3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard_01.adm";
+
+for $a in dataset('CSX')
+for $b in dataset('DBLP')
+where similarity-jaccard(gram-tokens($a.nested.title, 3, false), gram-tokens($b.nested.title, 3, false)) >= 0.5f
+      and $a.nested.id < $b.nested.id
+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/inverted-index-join/ngram-jaccard_03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard_03.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard_03.aql
new file mode 100644
index 0000000..e7549cf
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard_03.aql
@@ -0,0 +1,33 @@
+/*
+ * Description    : Self joins dataset DBLP, based on the similarity-jaccard function of their titles' 3-gram tokens.
+ *                  DBLP has a 3-gram index on title, and 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;
+set import-private-functions 'true';
+
+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-join_ngram-jaccard_01.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('DBLP')
+where similarity-jaccard(gram-tokens($a.nested.title, 3, false), gram-tokens($b.nested.title, 3, false)) >= 0.5f
+      and $a.nested.id < $b.nested.id
+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/inverted-index-join/ngram-jaccard_04.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard_04.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard_04.aql
new file mode 100644
index 0000000..b285301
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-jaccard_04.aql
@@ -0,0 +1,48 @@
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard function of their titles' 3-gram tokens.
+ *                  DBLP has a 3-gram index on title, and 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;
+set import-private-functions 'true';
+
+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 ngram_index_DBLP on DBLP(nested.title: string) type ngram(3) enforced;
+
+create index ngram_index_CSX on CSX(nested.title: string) type ngram(3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard_01.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('CSX')
+where similarity-jaccard(gram-tokens($a.nested.title, 3, false), gram-tokens($b.nested.title, 3, false)) >= 0.5f
+      and $a.nested.id < $b.nested.id
+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/inverted-index-join/word-fuzzyeq-jaccard_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-fuzzyeq-jaccard_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-fuzzyeq-jaccard_01.aql
new file mode 100644
index 0000000..27ab0f3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-fuzzyeq-jaccard_01.aql
@@ -0,0 +1,48 @@
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on ~= using Jaccard of their titles' word tokens.
+ *                  DBLP has a keyword index on title, and 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 keyword_index on DBLP(nested.title: string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-fuzzyeq-jaccard_01.adm";
+
+set simfunction 'jaccard';
+set simthreshold '0.5f';
+
+for $a in dataset('DBLP')
+for $b in dataset('CSX')
+where word-tokens($a.nested.title) ~= word-tokens($b.nested.title) and $a.nested.id < $b.nested.id
+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/inverted-index-join/word-fuzzyeq-jaccard_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-fuzzyeq-jaccard_02.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-fuzzyeq-jaccard_02.aql
new file mode 100644
index 0000000..968e7de
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-fuzzyeq-jaccard_02.aql
@@ -0,0 +1,48 @@
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on ~= using Jaccard of their titles' word tokens.
+ *                  DBLP has a keyword index on title, and 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 keyword_index on CSX(nested.title: string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-fuzzyeq-jaccard_01.adm";
+
+set simfunction 'jaccard';
+set simthreshold '0.5f';
+
+for $a in dataset('CSX')
+for $b in dataset('DBLP')
+where word-tokens($a.nested.title) ~= word-tokens($b.nested.title) and $a.nested.id < $b.nested.id
+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/inverted-index-join/word-fuzzyeq-jaccard_03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-fuzzyeq-jaccard_03.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-fuzzyeq-jaccard_03.aql
new file mode 100644
index 0000000..ee59b25
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-fuzzyeq-jaccard_03.aql
@@ -0,0 +1,33 @@
+/*
+ * Description    : Self joins dataset DBLP, based on ~= using Jaccard of their titles' word tokens.
+ *                  DBLP has a keyword index on title, and 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 closed {
+  nested : DBLPTypetmp
+}
+
+create dataset DBLP(DBLPType) primary key nested.id;
+
+create index keyword_index on DBLP(nested.title: string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-fuzzyeq-jaccard_01.adm";
+
+set simfunction 'jaccard';
+set simthreshold '0.5f';
+
+for $a in dataset('DBLP')
+for $b in dataset('DBLP')
+where word-tokens($a.nested.title) ~= word-tokens($b.nested.title) and $a.nested.id < $b.nested.id
+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/inverted-index-join/word-fuzzyeq-jaccard_04.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-fuzzyeq-jaccard_04.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-fuzzyeq-jaccard_04.aql
new file mode 100644
index 0000000..edefec1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-fuzzyeq-jaccard_04.aql
@@ -0,0 +1,49 @@
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on ~= using Jaccard of their titles' word tokens.
+ *                  DBLP has a keyword index on title, and 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 keyword_index_DBLP on DBLP(nested.title: string) type keyword enforced;
+
+create index keyword_index_CSX on CSX(nested.title: string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-fuzzyeq-jaccard_01.adm";
+
+set simfunction 'jaccard';
+set simthreshold '0.5f';
+
+for $a in dataset('DBLP')
+for $b in dataset('CSX')
+where word-tokens($a.nested.title) ~= word-tokens($b.nested.title) and $a.nested.id < $b.nested.id
+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/inverted-index-join/word-jaccard-check-after-btree-access.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check-after-btree-access.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check-after-btree-access.aql
new file mode 100644
index 0000000..6149035
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check-after-btree-access.aql
@@ -0,0 +1,54 @@
+/*
+ * Description    : Fuzzy self joins a dataset, TweetMessages, based on the similarity-jaccard-check function of its text-messages' word tokens.
+ *                  TweetMessages has a keyword index on text-message and btree index on the primary key tweetid, and we expect the join to be
+ *					transformed into btree and inverted indexed nested-loop joins. We test whether the join condition can be transformed into
+ *					multiple indexed nested loop joins of various type of indexes.
+ * Success        : Yes
+ */
+
+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 }},
+	countA: int32,
+	countB: int32
+}
+
+create type TweetMessageType as closed {
+	nested:  TweetMessageNestedType
+}
+
+create dataset TweetMessages(TweetMessageType)
+primary key nested.tweetid;
+
+create index twmSndLocIx on TweetMessages(nested.sender-location) type rtree;
+create index msgCountAIx on TweetMessages(nested.countA) type btree;
+create index msgCountBIx on TweetMessages(nested.countB) type btree;
+create index msgTextIx on TweetMessages(nested.message-text: string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check-after-btree-access.adm";
+
+for $t1 in dataset('TweetMessages')
+for $t2 in dataset('TweetMessages')
+let $sim := similarity-jaccard-check(word-tokens($t1.nested.message-text), word-tokens($t2.nested.message-text), 0.6f)
+where $sim[0] and $t1.nested.tweetid < int64("20") and $t2.nested.tweetid != $t1.nested.tweetid
+return {
+    "t1": $t1.nested.tweetid,
+    "t2": $t2.nested.tweetid,
+    "sim": $sim[1]
+}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check_01.aql
new file mode 100644
index 0000000..80000e2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check_01.aql
@@ -0,0 +1,46 @@
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard-check function of their titles' word tokens.
+ *                  DBLP has a keyword index on title, and 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 keyword_index on DBLP(nested.title: string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check_01.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('CSX')
+where similarity-jaccard-check(word-tokens($a.nested.title), word-tokens($b.nested.title), 0.5f)[0]
+      and $a.nested.id < $b.nested.id
+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/inverted-index-join/word-jaccard-check_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check_02.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check_02.aql
new file mode 100644
index 0000000..f0c7d50
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check_02.aql
@@ -0,0 +1,46 @@
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard-check function of their titles' word tokens.
+ *                  DBLP has a keyword index on title, and 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 keyword_index on CSX(nested.title: string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check_01.adm";
+
+for $a in dataset('CSX')
+for $b in dataset('DBLP')
+where similarity-jaccard-check(word-tokens($a.nested.title), word-tokens($b.nested.title), 0.5f)[0]
+      and $a.nested.id < $b.nested.id
+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/inverted-index-join/word-jaccard-check_03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check_03.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check_03.aql
new file mode 100644
index 0000000..b3b1249
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check_03.aql
@@ -0,0 +1,32 @@
+/*
+ * Description    : Selg joins dataset DBLP, based on the similarity-jaccard-check function of their titles' word tokens.
+ *                  DBLP has a keyword index on title, and 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 closed {
+  nested : DBLPTypetmp
+}
+
+create dataset DBLP(DBLPType) primary key nested.id;
+
+create index keyword_index_DBLP on DBLP(nested.title: string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check_01.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('DBLP')
+where similarity-jaccard-check(word-tokens($a.nested.title), word-tokens($b.nested.title), 0.5f)[0]
+      and $a.nested.id < $b.nested.id
+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/inverted-index-join/word-jaccard-check_04.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check_04.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check_04.aql
new file mode 100644
index 0000000..9626b5a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-check_04.aql
@@ -0,0 +1,47 @@
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard-check function of their titles' word tokens.
+ *                  DBLP has a keyword index on title, and 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,
+  dblpid: 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 keyword_index on DBLP(nested.title: string) type keyword enforced;
+
+create index keyword_index on CSX(nested.title: string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check_01.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('CSX')
+where similarity-jaccard-check(word-tokens($a.nested.title), word-tokens($b.nested.title), 0.5f)[0]
+      and $a.nested.id < $b.nested.id
+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/inverted-index-join/word-jaccard-inline.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-inline.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-inline.aql
new file mode 100644
index 0000000..45d8171
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard-inline.aql
@@ -0,0 +1,34 @@
+/*
+ * Description    : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard function of its titles' word tokens.
+ *                  DBLP has a keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ *                  We test the inlining of variables that enable the select to be pushed into the join for subsequent optimization with an index.
+ *                  We expect the top-level equi join introduced because of surrogate optimization to be removed, since it is not necessary.
+ * 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 keyword_index on DBLP(nested.title: string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join-noeqjoin_word-jaccard-inline.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('DBLP')
+let $jacc := similarity-jaccard(word-tokens($a.nested.title), word-tokens($b.nested.title))
+where $jacc >= 0.5f and $a.nested.id < $b.nested.id
+return {"atitle": $a.nested.title, "btitle": $b.nested.title, "jacc": $jacc}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard_01.aql
new file mode 100644
index 0000000..d5bb05a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard_01.aql
@@ -0,0 +1,47 @@
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard function of their titles' word tokens.
+ *                  DBLP has a keyword index on title, and 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 keyword_index on DBLP(nested.title: string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard_01.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('CSX')
+where similarity-jaccard(word-tokens($a.nested.title), word-tokens($b.nested.title)) >= 0.5f
+      and $a.nested.id < $b.nested.id
+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/inverted-index-join/word-jaccard_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard_02.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard_02.aql
new file mode 100644
index 0000000..dfc21f2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard_02.aql
@@ -0,0 +1,47 @@
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard function of their titles' word tokens.
+ *                  DBLP has a keyword index on title, and 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 keyword_index on CSX(nested.title: string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard_01.adm";
+
+for $a in dataset('CSX')
+for $b in dataset('DBLP')
+where similarity-jaccard(word-tokens($a.nested.title), word-tokens($b.nested.title)) >= 0.5f
+      and $a.nested.id < $b.nested.id
+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/inverted-index-join/word-jaccard_03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard_03.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard_03.aql
new file mode 100644
index 0000000..51d16fb
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard_03.aql
@@ -0,0 +1,33 @@
+/*
+ * Description    : Self joins dataset DBLP, based on the similarity-jaccard function of their titles' word tokens.
+ *                  DBLP has a keyword index on title, and 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 closed {
+  nested : DBLPTypetmp
+}
+
+create dataset DBLP(DBLPType) primary key nested.id;
+
+create index keyword_index on DBLP(nested.title: string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard_01.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('DBLP')
+where similarity-jaccard(word-tokens($a.nested.title), word-tokens($b.nested.title)) >= 0.5f
+      and $a.nested.id < $b.nested.id
+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/inverted-index-join/word-jaccard_04.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard_04.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard_04.aql
new file mode 100644
index 0000000..3b08be3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/word-jaccard_04.aql
@@ -0,0 +1,48 @@
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard function of their titles' word tokens.
+ *                  DBLP has a keyword index on title, and 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 keyword_index on DBLP(nested.title: string) type keyword enforced;
+
+create index keyword_index on CSX(nested.title: string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard_01.adm";
+
+for $a in dataset('DBLP')
+for $b in dataset('CSX')
+where similarity-jaccard(word-tokens($a.nested.title), word-tokens($b.nested.title)) >= 0.5f
+      and $a.nested.id < $b.nested.id
+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/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01.aql
new file mode 100644
index 0000000..7cbead7
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01.aql
@@ -0,0 +1,56 @@
+/*
+ * Description  : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary rtree 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,
+	send-time: datetime,
+        referred-topics: {{ string }},
+	message-text: string,
+	countA: int32,
+	countB: int32
+}
+
+create type TweetMessageType as open {
+	nested: TweetMessageNestedType
+}
+
+create dataset TweetMessages(TweetMessageType)
+primary key nested.tweetid;
+
+create index twmSndLocIx on TweetMessages(nested.sender-location: point) type rtree enforced;
+create index msgCountAIx on TweetMessages(nested.countA) type btree;
+create index msgCountBIx on TweetMessages(nested.countB) type btree;
+create index msgTextIx on TweetMessages(nested.message-text) type keyword;
+
+write output to nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_01.adm";
+
+for $t1 in dataset('TweetMessages')
+let $n :=  create-circle($t1.nested.sender-location, 0.5)
+where $t1.nested.tweetid < int64("10")
+order by $t1.nested.tweetid
+return {
+"tweetid1": $t1.nested.tweetid,
+"loc1":$t1.nested.sender-location,
+"nearby-message": for $t2 in dataset('TweetMessages')
+                             where spatial-intersect($t2.nested.sender-location, $n)
+                             order by $t2.tweetid
+                             return {"tweetid2":$t2.nested.tweetid, "loc2":$t2.nested.sender-location}
+};
\ 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/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.aql
new file mode 100644
index 0000000..d59f589
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.aql
@@ -0,0 +1,56 @@
+/*
+ * Description  : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary rtree 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,
+	send-time: datetime,
+        referred-topics: {{ string }},
+	message-text: string,
+	countA: int32,
+	countB: int32
+}
+
+create type TweetMessageType as open {
+	nested: TweetMessageNestedType
+}
+
+create dataset TweetMessages(TweetMessageType)
+primary key nested.tweetid;
+
+create index twmSndLocIx on TweetMessages(nested.sender-location: point) type rtree enforced;
+create index msgCountAIx on TweetMessages(nested.countA) type btree;
+create index msgCountBIx on TweetMessages(nested.countB) type btree;
+create index msgTextIx on TweetMessages(nested.message-text) type keyword;
+
+write output to nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_02.adm";
+
+for $t1 in dataset('TweetMessages')
+let $n :=  create-circle($t1.nested.sender-location, 0.5)
+where $t1.nested.tweetid < int64("10")
+order by $t1.nested.tweetid
+return {
+"tweetid1": $t1.nested.tweetid,
+"loc1":$t1.nested.sender-location,
+"nearby-message": for $t2 in dataset('TweetMessages')
+                             where spatial-intersect($t2.nested.sender-location, $n) and $t1.nested.tweetid != $t2.nested.tweetid
+                             order by $t2.nested.tweetid
+                             return {"tweetid2":$t2.nested.tweetid, "loc2":$t2.nested.sender-location}
+};
\ 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/rtree-index-join/spatial-intersect-point_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_01.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_01.aql
new file mode 100644
index 0000000..653ab55
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_01.aql
@@ -0,0 +1,37 @@
+/*
+ * Description    : Joins two datasets on the intersection of their point attributes.
+ *                  The dataset 'MyData1' has an enforced open RTree index, and 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 MyRecord as closed {
+  id: int32,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle
+}
+
+create type MyRecordNested as closed {
+  nested: MyRecord
+}
+
+create dataset MyData1(MyRecordNested) primary key nested.id;
+create dataset MyData2(MyRecord) primary key id;
+
+create index rtree_index on MyData1(nested.point) type rtree;
+
+write output to nc1:"rttest/index-join_rtree-spatial-intersect-point.adm";
+
+for $a in dataset('MyData1')
+for $b in dataset('MyData2')
+where spatial-intersect($a.nested.point, $b.point)
+return {"a": $a, "b": $b}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_02.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_02.aql
new file mode 100644
index 0000000..c5de5bb
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_02.aql
@@ -0,0 +1,37 @@
+/*
+ * Description    : Joins two datasets on the intersection of their point attributes.
+ *                  The dataset 'MyData2' has an enforced open RTree index, and 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 MyRecord as closed {
+  id: int32,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle
+}
+
+create type MyRecordNested as closed {
+  nested: MyRecord
+}
+
+create dataset MyData1(MyRecordNested) primary key nested.id;
+create dataset MyData2(MyRecord) primary key id;
+
+create index rtree_index on MyData2(point) type rtree;
+
+write output to nc1:"rttest/rtree-index-join_spatial-intersect-point_02.adm";
+
+for $a in dataset('MyData1')
+for $b in dataset('MyData2')
+where spatial-intersect($a.nested.point, $b.point)
+return {"a": $a, "b": $b}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_03.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_03.aql
new file mode 100644
index 0000000..8ba72d7
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_03.aql
@@ -0,0 +1,35 @@
+/*
+ * Description    : Self-joins a dataset on the intersection of its point attribute.
+ *                  The dataset has an enforced open RTree index, and 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 MyRecord as closed {
+  id: int32,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle
+}
+
+create type MyRecordNested as closed {
+  nested: MyRecord
+}
+create dataset MyData(MyRecordNested) primary key nested.id;
+
+create index rtree_index on MyData(nested.point) type rtree;
+
+write output to nc1:"rttest/rtree-index-join_spatial-intersect-point_03.adm";
+
+for $a in dataset('MyData')
+for $b in dataset('MyData')
+where spatial-intersect($a.nested.point, $b.nested.point)
+return {"a": $a, "b": $b}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_04.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_04.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_04.aql
new file mode 100644
index 0000000..97ce08a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_04.aql
@@ -0,0 +1,38 @@
+/*
+ * Description    : Joins two datasets on the intersection of their point attributes.
+ *                  Both datasets 'MyData' and 'MyData2' have an enforced open RTree index, and 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 MyRecordNestedOpen as open {
+  id: int32,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle
+}
+
+create type MyRecordOpen as closed {
+  nested: MyRecordNestedOpen
+}
+
+create dataset MyData1(MyRecordOpen) primary key nested.id;
+create dataset MyData2(MyRecordOpen) primary key nested.id;
+
+create index rtree_index on MyData1(nested.point:point) type rtree enforced;
+
+create index rtree_index2 on MyData2(nested.point:point) type rtree enforced;
+
+write output to nc1:"rttest/rtree-index-join_spatial-intersect-point_02.adm";
+
+for $a in dataset('MyData1')
+for $b in dataset('MyData2')
+where spatial-intersect($a.nested.point, $b.nested.point)
+return {"a": $a, "b": $b}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_05.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_05.aql b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_05.aql
new file mode 100644
index 0000000..25c72d6
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/spatial-intersect-point_05.aql
@@ -0,0 +1,36 @@
+/*
+ * Description    : Joins two datasets on the intersection of their point attributes.
+ *                  Only dataset 'MyData1' has an enforced open RTree index, hence 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 MyRecordNestedOpen as open {
+  id: int32,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle
+}
+
+create type MyRecordOpen as closed {
+  nested: MyRecordNestedOpen
+}
+
+create dataset MyData1(MyRecordOpen) primary key nested.id;
+create dataset MyData2(MyRecordOpen) primary key nested.id;
+
+create index rtree_index on MyData1(nested.point:point) type rtree enforced;
+
+write output to nc1:"rttest/rtree-index-join_spatial-intersect-point_02.adm";
+
+for $a in dataset('MyData1')
+for $b in dataset('MyData2')
+where spatial-intersect($a.nested.point, $b.nested.point)
+return {"a": $a, "b": $b}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/disjunction-to-join.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/disjunction-to-join.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/disjunction-to-join.aql
new file mode 100644
index 0000000..7022344
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/disjunction-to-join.aql
@@ -0,0 +1,21 @@
+/*
+ * 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 TestType as open {
+  "id" : string,
+  "no-idx" : string
+};
+
+create dataset TestSet(TestType) primary key "id";
+create index TestSetIndex on TestSet(idx: string) enforced;
+
+for $x in dataset TestSet 
+where $x.idx = "one" or $x.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/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1.aql
new file mode 100644
index 0000000..73b7b4f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1.aql
@@ -0,0 +1,49 @@
+/*
+ * 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 TweetMessageType as open {
+	tweetid: int64,
+    user: TwitterUserType,
+    sender-location: point,
+	send-time: datetime,
+    referred-topics: {{ string }},
+    message-text: string,
+    countA: int32
+}
+
+create dataset TweetMessages(TweetMessageType)
+primary key tweetid;
+
+create index msgCountBIx on TweetMessages(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.tweetid < int64("10")
+order by $t1.tweetid
+return {
+"tweetid1": $t1.tweetid,
+"count1":$t1.countA,
+"t2info": for $t2 in dataset('TweetMessages')
+          where $t1.countA /* +indexnl */= $t2.countB
+          order by $t2.tweetid
+          return {"tweetid2": $t2.tweetid,
+                  "count2":$t2.countB}
+};

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2.aql
new file mode 100644
index 0000000..5054bbe
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2.aql
@@ -0,0 +1,49 @@
+/*
+ * 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 TweetMessageType as open {
+	tweetid: int64,
+    user: TwitterUserType,
+    sender-location: point,
+	send-time: datetime,
+    referred-topics: {{ string }},
+    message-text: string
+}
+
+create dataset TweetMessages(TweetMessageType)
+primary key tweetid;
+
+create index msgCountAIx on TweetMessages(countA: int32) type btree enforced;
+create index msgCountBIx on TweetMessages(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.tweetid < int64("10")
+order by $t1.tweetid
+return {
+"tweetid1": $t1.tweetid,
+"count1":$t1.countA,
+"t2info": for $t2 in dataset('TweetMessages')
+          where $t1.countA /* +indexnl */= $t2.countB
+          order by $t2.tweetid
+          return {"tweetid2": $t2.tweetid,
+                  "count2":$t2.countB}
+};

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1.aql
new file mode 100644
index 0000000..d62049a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1.aql
@@ -0,0 +1,51 @@
+/*
+ * 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 TweetMessageType as open {
+	tweetid: int64,
+        user: TwitterUserType,
+        sender-location: point,
+	send-time: datetime,
+        referred-topics: {{ string }},
+	message-text: string,
+	countA: int32
+}
+
+create dataset TweetMessages(TweetMessageType)
+primary key tweetid;
+
+create index msgCountBIx on TweetMessages(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.tweetid < int64("10")
+order by $t1.tweetid
+return {
+"tweetid1": $t1.tweetid,
+"count1":$t1.countA,
+"t2info": for $t2 in dataset('TweetMessages')
+                        where $t1.countA /* +indexnl */= $t2.countB and
+                        $t1.tweetid != $t2.tweetid
+                        order by $t2.tweetid
+                        return {"tweetid2": $t2.tweetid,
+                                       "count2":$t2.countB}
+};
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2.aql
new file mode 100644
index 0000000..ff78fee
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2.aql
@@ -0,0 +1,51 @@
+/*
+ * 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 TweetMessageType as open {
+	tweetid: int64,
+        user: TwitterUserType,
+        sender-location: point,
+	send-time: datetime,
+        referred-topics: {{ string }},
+	message-text: string
+}
+
+create dataset TweetMessages(TweetMessageType)
+primary key tweetid;
+
+create index msgCountAIx on TweetMessages(countA: int32) type btree enforced;
+create index msgCountBIx on TweetMessages(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.tweetid < int64("10")
+order by $t1.tweetid
+return {
+"tweetid1": $t1.tweetid,
+"count1":$t1.countA,
+"t2info": for $t2 in dataset('TweetMessages')
+                        where $t1.countA /* +indexnl */= $t2.countB and
+                        $t1.tweetid != $t2.tweetid
+                        order by $t2.tweetid
+                        return {"tweetid2": $t2.tweetid,
+                                       "count2":$t2.countB}
+};
+

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-join_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-join_01.aql
new file mode 100644
index 0000000..e62114d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-join_01.aql
@@ -0,0 +1,24 @@
+/*
+ * 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 Name as open {
+	id: int32
+}
+
+create dataset Names(Name) primary key id;
+create index Name_idx on Names(fname: string,lname: 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.fname /*+ indexnl*/> $emp2.fname and $emp1.lname /*+ indexnl*/> $emp2.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/open-index-enforced/btree-index-join/secondary-composite-key-join_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-join_02.aql
new file mode 100644
index 0000000..b26ade4
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-join_02.aql
@@ -0,0 +1,24 @@
+/*
+ * 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 Name as open {
+	id: int32
+}
+
+create dataset Names(Name) primary key id;
+create index Name_idx on Names(fname: string,lname: 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.fname /*+ indexnl*/< $emp2.fname and $emp1.lname /*+ indexnl*/< $emp2.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/open-index-enforced/btree-index-join/secondary-composite-key-join_03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-join_03.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-join_03.aql
new file mode 100644
index 0000000..f89e9fa
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-join_03.aql
@@ -0,0 +1,24 @@
+/*
+ * 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 Name as open {
+	id: int32
+}
+
+create dataset Names(Name) primary key id;
+create index Name_idx on Names(fname: string,lname: 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.fname /*+ indexnl*/= $emp2.fname and $emp1.lname /*+ indexnl*/= $emp2.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/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_01.aql
new file mode 100644
index 0000000..2e77fb1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_01.aql
@@ -0,0 +1,24 @@
+/*
+ * 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 Name as open {
+	id: int32
+}
+
+create dataset Names(Name) primary key id;
+create index Name_idx on Names(fname: string,lname: 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.fname /*+ indexnl*/< $emp2.fname and $emp1.lname /*+ indexnl*/> $emp2.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/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_02.aql
new file mode 100644
index 0000000..391e26e
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_02.aql
@@ -0,0 +1,24 @@
+/*
+ * 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 Name as open {
+	id: int32
+}
+
+create dataset Names(Name) primary key id;
+create index Name_idx on Names(fname: string,lname: 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.fname /*+ indexnl*/> $emp2.fname and $emp1.lname /*+ indexnl*/< $emp2.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/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_03.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_03.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_03.aql
new file mode 100644
index 0000000..6919795
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_03.aql
@@ -0,0 +1,24 @@
+/*
+ * 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 Name as open {
+	id: int32
+}
+
+create dataset Names(Name) primary key id;
+create index Name_idx on Names(fname: string,lname: 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.fname /*+ indexnl*/> $emp2.fname and $emp1.lname /*+ indexnl*/= $emp2.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/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_04.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_04.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_04.aql
new file mode 100644
index 0000000..e8afed6
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_04.aql
@@ -0,0 +1,24 @@
+/*
+ * 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 Name as open {
+	id: int32
+}
+
+create dataset Names(Name) primary key id;
+create index Name_idx on Names(fname: string,lname: 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.fname /*+ indexnl*/< $emp2.fname and $emp1.lname /*+ indexnl*/= $emp2.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/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_05.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_05.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_05.aql
new file mode 100644
index 0000000..0706712
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_05.aql
@@ -0,0 +1,24 @@
+/*
+ * 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 Name as open {
+	id: int32
+}
+
+create dataset Names(Name) primary key id;
+create index Name_idx on Names(fname: string,lname: 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.fname /*+ indexnl*/= $emp2.fname and $emp1.lname /*+ indexnl*/> $emp2.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/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_06.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_06.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_06.aql
new file mode 100644
index 0000000..744990b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-composite-key-prefix-join_06.aql
@@ -0,0 +1,24 @@
+/*
+ * 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 Name as open {
+	id: int32
+}
+
+create dataset Names(Name) primary key id;
+create index Name_idx on Names(fname: string,lname: 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.fname /*+ indexnl*/= $emp2.fname and $emp1.lname /*+ indexnl*/< $emp2.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/open-index-enforced/btree-index-join/secondary-equi-join-multiindex.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-equi-join-multiindex.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-equi-join-multiindex.aql
new file mode 100644
index 0000000..3d5722f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-equi-join-multiindex.aql
@@ -0,0 +1,59 @@
+/*
+ * 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 open 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 open {
+  message-id: int32,
+  message-id-copy: int32,
+  author-id: 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: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.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
+}

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/04b2b77a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-equi-join-multipred.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-equi-join-multipred.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-equi-join-multipred.aql
new file mode 100644
index 0000000..963c7fb
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-equi-join-multipred.aql
@@ -0,0 +1,40 @@
+/*
+ * 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 open {
+  id: int32,
+  dblpid: 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: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.title /*+ indexnl */ = $b.title and $a.authors < $b.authors and $a.misc > $b.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/open-index-enforced/btree-index-join/secondary-equi-join_01.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-equi-join_01.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-equi-join_01.aql
new file mode 100644
index 0000000..e98ec3f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-equi-join_01.aql
@@ -0,0 +1,38 @@
+/*
+ * Description    : Equi joins two datasets, DBLP and CSX, based on their title.
+ *                  DBLP has a secondary btree open 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 open {
+  id: int32,
+  dblpid: 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: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.title /*+ indexnl */ = $b.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/open-index-enforced/btree-index-join/secondary-equi-join_02.aql
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-equi-join_02.aql b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-equi-join_02.aql
new file mode 100644
index 0000000..22c8c49
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/secondary-equi-join_02.aql
@@ -0,0 +1,38 @@
+/*
+ * Description    : Equi joins two datasets, DBLP and CSX, based on their title.
+ *                  CSX has a secondary btree open 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 open {
+  id: int32,
+  csxid: 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: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.title /*+ indexnl */ = $b.title
+return {"arec": $a, "brec": $b}


Mime
View raw message