You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by bu...@apache.org on 2015/10/31 00:16:34 UTC
[26/51] [partial] incubator-asterixdb git commit: SQL++ parser: 1. refactored asterix-aql to become asterix-lang-common and asterix-lang-aql, where the former is the common part for different languages; 2. added asterix-lang-sqlpp on top of asterix-lang-

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-fuzzyeq-jaccard_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-fuzzyeq-jaccard_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-fuzzyeq-jaccard_02.sqlpp
new file mode 100644
index 0000000..dadeb9d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-fuzzyeq-jaccard_02.sqlpp
@@ -0,0 +1,65 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, closed DBLP and open CSX, based on ~= using Jaccard their titles' 3-gram tokens.
+ *                  CSX has a 3-gram enforced open index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPType as
+ closed {
+  id : int32,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+{
+  id : int32,
+  csxid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index ngram_index  on CSX (title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-fuzzyeq-jaccard_02.adm"
+set "simfunction" "jaccard";
+
+set "simthreshold" "0.5f";
+
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where ((test."gram-tokens"(a.title,3,false) ~= test."gram-tokens"(b.title,3,false)) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-fuzzyeq-jaccard_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-fuzzyeq-jaccard_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-fuzzyeq-jaccard_03.sqlpp
new file mode 100644
index 0000000..a3d8a69
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-fuzzyeq-jaccard_03.sqlpp
@@ -0,0 +1,54 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy self joins a dataset, DBLP, based on ~= using Jaccard of its titles' 3-gram tokens.
+ *                  DBLP has a 3-gram enforced open index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  index ngram_index  on DBLP (title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-fuzzyeq-jaccard_03.adm"
+set "simfunction" "jaccard";
+
+set "simthreshold" "0.5f";
+
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      DBLP as b
+where ((test."gram-tokens"(a.title,3,false) ~= test."gram-tokens"(b.title,3,false)) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-fuzzyeq-jaccard_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-fuzzyeq-jaccard_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-fuzzyeq-jaccard_04.sqlpp
new file mode 100644
index 0000000..441e382
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-fuzzyeq-jaccard_04.sqlpp
@@ -0,0 +1,66 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on ~= using Jaccard of their titles' 3-gram tokens.
+ *                  DBLP and CSX both have a 3-gram enforced open index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+{
+  id : int32,
+  csxid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index ngram_index_DBLP  on DBLP (title:string) type ngram (3) enforced;
+
+create  index ngram_index_CSX  on CSX (title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-fuzzyeq-jaccard_01.adm"
+set "simfunction" "jaccard";
+
+set "simthreshold" "0.5f";
+
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where ((test."gram-tokens"(a.title,3,false) ~= test."gram-tokens"(b.title,3,false)) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_01.sqlpp
new file mode 100644
index 0000000..67e43d5
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_01.sqlpp
@@ -0,0 +1,61 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, open DBLP and closed CSX, based on the similarity-jaccard-check function of their titles' 3-gram tokens.
+ *                  DBLP has a 3-gram enforced open index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+ closed {
+  id : int32,
+  csxid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index ngram_index  on DBLP (title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard-check_01.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where (test."similarity-jaccard-check"(test."gram-tokens"(a.title,3,false),test."gram-tokens"(b.title,3,false),0.500000f)[0] and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_02.sqlpp
new file mode 100644
index 0000000..dfd8194
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_02.sqlpp
@@ -0,0 +1,61 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, closed DBLP and open CSX, based the similarity-jaccard-check function of their titles' 3-gram tokens.
+ *                  CSX has a 3-gram enforced open index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPType as
+ closed {
+  id : int32,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+{
+  id : int32,
+  csxid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index ngram_index  on CSX (title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard-check_02.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where (test."similarity-jaccard-check"(test."gram-tokens"(a.title,3,false),test."gram-tokens"(b.title,3,false),0.500000f)[0] and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_03.sqlpp
new file mode 100644
index 0000000..526fb3a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_03.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy self joins an open dataset DBLP, based on the similarity-jaccard-check function of its titles' 3-gram tokens.
+ *                  DBLP has a 3-gram enforced open index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  index ngram_index  on DBLP (title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard-check_03.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      DBLP as b
+where (test."similarity-jaccard-check"(test."gram-tokens"(a.title,3,false),test."gram-tokens"(b.title,3,false),0.500000f)[0] and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_04.sqlpp
new file mode 100644
index 0000000..2b06b29
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_04.sqlpp
@@ -0,0 +1,62 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based the similarity-jaccard-check function of their titles' 3-gram tokens.
+ *                  DBLP and CSX both have a 3-gram enforced open index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+{
+  id : int32,
+  csxid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index ngram_index_DBLP  on DBLP (title:string) type ngram (3) enforced;
+
+create  index ngram_index_CSX  on CSX (title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard-check_02.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where (test."similarity-jaccard-check"(test."gram-tokens"(a.title,3,false),test."gram-tokens"(b.title,3,false),0.500000f)[0] and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_inline_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_inline_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_inline_03.sqlpp
new file mode 100644
index 0000000..a085a61
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard-check_inline_03.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard-check function of its titles' 3-gram tokens.
+ *                  DBLP has a 3-gram enforced open 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.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  index ngram_index  on DBLP (title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard-check_04.adm"
+select element {'arec':a,'brec':b,'jacc':jacc[1]}
+from  DBLP as a,
+      DBLP as b
+with  jacc as test."similarity-jaccard-check"(test."gram-tokens"(a.title,3,false),test."gram-tokens"(b.title,3,false),0.500000f)
+where (jacc[0] and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_01.sqlpp
new file mode 100644
index 0000000..1c681ec
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_01.sqlpp
@@ -0,0 +1,61 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * 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 enforced open index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+ closed {
+  id : int32,
+  csxid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index ngram_index  on DBLP (title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard_01.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where ((test."similarity-jaccard"(test."gram-tokens"(a.title,3,false),test."gram-tokens"(b.title,3,false)) >= 0.500000f) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_02.sqlpp
new file mode 100644
index 0000000..e4db9e6
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_02.sqlpp
@@ -0,0 +1,61 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based the similarity-jaccard function of their titles' 3-gram tokens.
+ *                  CSX has a 3-gram enforced open index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPType as
+ closed {
+  id : int32,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+{
+  id : int32,
+  csxid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index ngram_index  on CSX (title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard_02.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where ((test."similarity-jaccard"(test."gram-tokens"(a.title,3,false),test."gram-tokens"(b.title,3,false)) >= 0.500000f) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_03.sqlpp
new file mode 100644
index 0000000..d57a38b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_03.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard function of its titles' 3-gram tokens.
+ *                  DBLP has a 3-gram enforced open index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  index ngram_index  on DBLP (title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard_03.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      DBLP as b
+where ((test."similarity-jaccard"(test."gram-tokens"(a.title,3,false),test."gram-tokens"(b.title,3,false)) >= 0.500000f) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_04.sqlpp
new file mode 100644
index 0000000..59f521e
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_04.sqlpp
@@ -0,0 +1,62 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based the similarity-jaccard function of their titles' 3-gram tokens.
+ *                  DBLP and CSX both have a 3-gram enforced open index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+{
+  id : int32,
+  csxid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index ngram_index_DBLP  on DBLP (title:string) type ngram (3) enforced;
+
+create  index ngram_index_CSX  on CSX (title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard_02.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where ((test."similarity-jaccard"(test."gram-tokens"(a.title,3,false),test."gram-tokens"(b.title,3,false)) >= 0.500000f) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_inline_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_inline_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_inline_03.sqlpp
new file mode 100644
index 0000000..7cc9969
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/ngram-jaccard_inline_03.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard function of its titles' 3-gram tokens.
+ *                  DBLP has a 3-gram enforced open 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.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  index ngram_index  on DBLP (title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-jaccard_04.adm"
+select element {'arec':a,'brec':b,'jacc':jacc}
+from  DBLP as a,
+      DBLP as b
+with  jacc as test."similarity-jaccard"(test."gram-tokens"(a.title,3,false),test."gram-tokens"(b.title,3,false))
+where ((jacc >= 0.500000f) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_01.sqlpp
new file mode 100644
index 0000000..4e35c80
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_01.sqlpp
@@ -0,0 +1,63 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on ~= using Jaccard of their titles' word tokens.
+ *                  DBLP has an enforced open keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+ closed {
+  id : int32,
+  csxid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index keyword_index  on DBLP (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";
+
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where ((test."word-tokens"(a.title) ~= test."word-tokens"(b.title)) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_02.sqlpp
new file mode 100644
index 0000000..a33e9d0
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_02.sqlpp
@@ -0,0 +1,63 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on ~= using Jaccard of their titles' word tokens.
+ *                  CSX has an enforced open keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+  id : int32,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+{
+  id : int32,
+  csxid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index keyword_index  on CSX (title:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-fuzzyeq-jaccard_02.adm"
+set "simfunction" "jaccard";
+
+set "simthreshold" "0.5f";
+
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where ((test."word-tokens"(a.title) ~= test."word-tokens"(b.title)) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_03.sqlpp
new file mode 100644
index 0000000..9f09553
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_03.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy self joins a dataset, DBLP, based on ~= using Jaccard of its titles' word tokens.
+ *                  DBLP has an enforced open keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  index keyword_index  on DBLP (title:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-fuzzyeq-jaccard_03.adm"
+set "simfunction" "jaccard";
+
+set "simthreshold" "0.5f";
+
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      DBLP as b
+where ((test."word-tokens"(a.title) ~= test."word-tokens"(b.title)) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_04.sqlpp
new file mode 100644
index 0000000..6816885
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-fuzzyeq-jaccard_04.sqlpp
@@ -0,0 +1,64 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on ~= using Jaccard of their titles' word tokens.
+ *                  DBLP and CSX both have an enforced open keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+{
+  id : int32,
+  csxid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index keyword_index_DBLP  on DBLP (title:string) type keyword enforced;
+
+create  index keyword_index_CSX  on CSX (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";
+
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where ((test."word-tokens"(a.title) ~= test."word-tokens"(b.title)) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check-after-btree-access.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check-after-btree-access.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check-after-btree-access.sqlpp
new file mode 100644
index 0000000..5620d1a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check-after-btree-access.sqlpp
@@ -0,0 +1,70 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * 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  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+  "screen-name" : string,
+  lang : string,
+  "friends-count" : int32,
+  "statuses-count" : int32,
+  name : string,
+  "followers-count" : int32
+}
+
+create type test.TweetMessageType as
+{
+  tweetid : int64,
+  user : TwitterUserType,
+  "sender-location" : point,
+  "send-time" : datetime,
+  "referred-topics" : {{string}},
+  countA : int32,
+  countB : int32
+}
+
+create  table TweetMessages(TweetMessageType) primary key tweetid;
+
+create  index twmSndLocIx  on TweetMessages ("sender-location") type rtree;
+
+create  index msgCountAIx  on TweetMessages (countA) type btree;
+
+create  index msgCountBIx  on TweetMessages (countB) type btree;
+
+create  index msgTextIx  on TweetMessages ("message-text":string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check-after-btree-access.adm"
+select element {'t1':t1.tweetid,'t2':t2.tweetid,'sim':sim[1]}
+from  TweetMessages as t1,
+      TweetMessages as t2
+with  sim as test."similarity-jaccard-check"(test."word-tokens"(t1."message-text"),test."word-tokens"(t2."message-text"),0.600000f)
+where (sim[0] and (t1.tweetid < test.int64('20')) and (t2.tweetid != t1.tweetid))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_01.sqlpp
new file mode 100644
index 0000000..b4df5ac
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_01.sqlpp
@@ -0,0 +1,59 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard-check function of their titles' word tokens.
+ *                  DBLP has an enforced open keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+ closed {
+  id : int32,
+  csxid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index keyword_index  on DBLP (title:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check_01.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where (test."similarity-jaccard-check"(test."word-tokens"(a.title),test."word-tokens"(b.title),0.500000f)[0] and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_02.sqlpp
new file mode 100644
index 0000000..54576d9
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_02.sqlpp
@@ -0,0 +1,59 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard-check function of their titles' word tokens.
+ *                  CSX has an enforced open keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+  id : int32,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+{
+  id : int32,
+  csxid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index keyword_index  on CSX (title:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check_02.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where (test."similarity-jaccard-check"(test."word-tokens"(a.title),test."word-tokens"(b.title),0.500000f)[0] and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_03.sqlpp
new file mode 100644
index 0000000..e6b89bf
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_03.sqlpp
@@ -0,0 +1,48 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard-check function of its titles' word tokens.
+ *                  DBLP has an enforced open keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  index keyword_index  on DBLP (title:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check_03.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      DBLP as b
+where (test."similarity-jaccard-check"(test."word-tokens"(a.title),test."word-tokens"(b.title),0.500000f)[0] and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_04.sqlpp
new file mode 100644
index 0000000..c911894
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_04.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard-check function of their titles' word tokens.
+ *                  DBLP and CSX both have an enforced open keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+{
+  id : int32,
+  csxid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index keyword_index_DBLP  on DBLP (title:string) type keyword enforced;
+
+create  index keyword_index_CSX  on CSX (title:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check_01.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where (test."similarity-jaccard-check"(test."word-tokens"(a.title),test."word-tokens"(b.title),0.500000f)[0] and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_inline_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_inline_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_inline_03.sqlpp
new file mode 100644
index 0000000..ad51a9d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard-check_inline_03.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard-check function of its titles' word tokens.
+ *                  DBLP has an enforced open 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.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  index keyword_index  on DBLP (title:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check_04.adm"
+select element {'arec':a,'brec':b,'jacc':jacc[1]}
+from  DBLP as a,
+      DBLP as b
+with  jacc as test."similarity-jaccard-check"(test."word-tokens"(a.title),test."word-tokens"(b.title),0.500000f)
+where (jacc[0] and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_01.sqlpp
new file mode 100644
index 0000000..c59290b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_01.sqlpp
@@ -0,0 +1,59 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard function of their titles' word tokens.
+ *                  DBLP has an enforced open keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+ closed {
+  id : int32,
+  csxid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index keyword_index  on DBLP (title:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard_01.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where ((test."similarity-jaccard"(test."word-tokens"(a.title),test."word-tokens"(b.title)) >= 0.500000f) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_02.sqlpp
new file mode 100644
index 0000000..21a1b75
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_02.sqlpp
@@ -0,0 +1,59 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard function of their titles' word tokens.
+ *                  CSX has an enforced open keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+  id : int32,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+{
+  id : int32,
+  csxid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index keyword_index  on CSX (title:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard_02.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where ((test."similarity-jaccard"(test."word-tokens"(a.title),test."word-tokens"(b.title)) >= 0.500000f) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_03.sqlpp
new file mode 100644
index 0000000..597d60c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_03.sqlpp
@@ -0,0 +1,48 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard function of its titles' word tokens.
+ *                  DBLP has an enforced open keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  index keyword_index  on DBLP (title:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard_03.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      DBLP as b
+where ((test."similarity-jaccard"(test."word-tokens"(a.title),test."word-tokens"(b.title)) >= 0.500000f) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_04.sqlpp
new file mode 100644
index 0000000..8bfbcc1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_04.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy joins two datasets, DBLP and CSX, based on the similarity-jaccard function of their titles' word tokens.
+ *                  DBLP and CSX both have an enforced open keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXType as
+{
+  id : int32,
+  csxid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  table CSX(CSXType) primary key id;
+
+create  index keyword_index_DBLP  on DBLP (title:string) type keyword enforced;
+
+create  index keyword_index_CSX  on CSX (title:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard_01.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where ((test."similarity-jaccard"(test."word-tokens"(a.title),test."word-tokens"(b.title)) >= 0.500000f) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_inline_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_inline_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_inline_03.sqlpp
new file mode 100644
index 0000000..c1cd43d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/inverted-index-join/word-jaccard_inline_03.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard function of its titles' word tokens.
+ *                  DBLP has an enforced open 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.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+  id : int32,
+  dblpid : string,
+  authors : string,
+  misc : string
+}
+
+create  table DBLP(DBLPType) primary key id;
+
+create  index keyword_index  on DBLP (title:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard_04.adm"
+select element {'arec':a,'brec':b,'jacc':jacc}
+from  DBLP as a,
+      DBLP as b
+with  jacc as test."similarity-jaccard"(test."word-tokens"(a.title),test."word-tokens"(b.title))
+where ((jacc >= 0.500000f) and (a.id < b.id))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01.sqlpp
new file mode 100644
index 0000000..0df4508
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01.sqlpp
@@ -0,0 +1,74 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * 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  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+  "screen-name" : string,
+  lang : string,
+  "friends-count" : int32,
+  "statuses-count" : int32,
+  name : string,
+  "followers-count" : int32
+}
+
+create type test.TweetMessageType as
+{
+  tweetid : int64,
+  user : TwitterUserType,
+  "send-time" : datetime,
+  "referred-topics" : {{string}},
+  "message-text" : string,
+  countA : int32,
+  countB : int32
+}
+
+create  table TweetMessages(TweetMessageType) primary key tweetid;
+
+create  index twmSndLocIx  on TweetMessages ("sender-location":point) type rtree enforced;
+
+create  index msgCountAIx  on TweetMessages (countA) type btree;
+
+create  index msgCountBIx  on TweetMessages (countB) type btree;
+
+create  index msgTextIx  on TweetMessages ("message-text") type keyword;
+
+write output to nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_01.adm"
+select element {'tweetid1':t1.tweetid,'loc1':t1."sender-location",'nearby-message':(
+        select element {'tweetid2':t2.tweetid,'loc2':t2."sender-location"}
+        from  TweetMessages as t2
+        where test."spatial-intersect"(t2."sender-location",n)
+        order by t2.tweetid
+    )}
+from  TweetMessages as t1
+with  n as test."create-circle"(t1."sender-location",0.5)
+where (t1.tweetid < test.int64('10'))
+order by t1.tweetid
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.sqlpp
new file mode 100644
index 0000000..09a8e9d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.sqlpp
@@ -0,0 +1,74 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * 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  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+  "screen-name" : string,
+  lang : string,
+  "friends-count" : int32,
+  "statuses-count" : int32,
+  name : string,
+  "followers-count" : int32
+}
+
+create type test.TweetMessageType as
+{
+  tweetid : int64,
+  user : TwitterUserType,
+  "send-time" : datetime,
+  "referred-topics" : {{string}},
+  "message-text" : string,
+  countA : int32,
+  countB : int32
+}
+
+create  table TweetMessages(TweetMessageType) primary key tweetid;
+
+create  index twmSndLocIx  on TweetMessages ("sender-location":point) type rtree enforced;
+
+create  index msgCountAIx  on TweetMessages (countA) type btree;
+
+create  index msgCountBIx  on TweetMessages (countB) type btree;
+
+create  index msgTextIx  on TweetMessages ("message-text") type keyword;
+
+write output to nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_02.adm"
+select element {'tweetid1':t1.tweetid,'loc1':t1."sender-location",'nearby-message':(
+        select element {'tweetid2':t2.tweetid,'loc2':t2."sender-location"}
+        from  TweetMessages as t2
+        where (test."spatial-intersect"(t2."sender-location",n) and (t1.tweetid != t2.tweetid))
+        order by t2.tweetid
+    )}
+from  TweetMessages as t1
+with  n as test."create-circle"(t1."sender-location",0.5)
+where (t1.tweetid < test.int64('10'))
+order by t1.tweetid
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/spatial-intersect-point_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/spatial-intersect-point_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/spatial-intersect-point_01.sqlpp
new file mode 100644
index 0000000..853cbcc
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/spatial-intersect-point_01.sqlpp
@@ -0,0 +1,66 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * 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  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.MyRecord as
+ closed {
+  id : int32,
+  point : point,
+  kwds : string,
+  line1 : line,
+  line2 : line,
+  poly1 : polygon,
+  poly2 : polygon,
+  rec : rectangle
+}
+
+create type test.MyRecordOpen as
+{
+  id : int32,
+  kwds : string,
+  line1 : line,
+  line2 : line,
+  poly1 : polygon,
+  poly2 : polygon,
+  rec : rectangle
+}
+
+create  table MyData1(MyRecordOpen) primary key id;
+
+create  table MyData2(MyRecord) primary key id;
+
+create  index rtree_index  on MyData1 (point:point) type rtree enforced;
+
+write output to nc1:"rttest/index-join_rtree-spatial-intersect-point.adm"
+select element {'a':a,'b':b}
+from  MyData1 as a,
+      MyData2 as b
+where test."spatial-intersect"(a.point,b.point)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/spatial-intersect-point_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/spatial-intersect-point_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/spatial-intersect-point_02.sqlpp
new file mode 100644
index 0000000..d6aaacb
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/spatial-intersect-point_02.sqlpp
@@ -0,0 +1,66 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * 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  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.MyRecord as
+ closed {
+  id : int32,
+  point : point,
+  kwds : string,
+  line1 : line,
+  line2 : line,
+  poly1 : polygon,
+  poly2 : polygon,
+  rec : rectangle
+}
+
+create type test.MyRecordOpen as
+{
+  id : int32,
+  kwds : string,
+  line1 : line,
+  line2 : line,
+  poly1 : polygon,
+  poly2 : polygon,
+  rec : rectangle
+}
+
+create  table MyData1(MyRecord) primary key id;
+
+create  table MyData2(MyRecordOpen) primary key id;
+
+create  index rtree_index  on MyData2 (point:point) type rtree enforced;
+
+write output to nc1:"rttest/rtree-index-join_spatial-intersect-point_02.adm"
+select element {'a':a,'b':b}
+from  MyData1 as a,
+      MyData2 as b
+where test."spatial-intersect"(a.point,b.point)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/spatial-intersect-point_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/spatial-intersect-point_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/spatial-intersect-point_03.sqlpp
new file mode 100644
index 0000000..7a0ff8a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/open-index-enforced/rtree-index-join/spatial-intersect-point_03.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * 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  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.MyRecord as
+{
+  id : int32,
+  kwds : string,
+  line1 : line,
+  line2 : line,
+  poly1 : polygon,
+  poly2 : polygon,
+  rec : rectangle
+}
+
+create  table MyData(MyRecord) primary key id;
+
+create  index rtree_index  on MyData (point:point) type rtree enforced;
+
+write output to nc1:"rttest/rtree-index-join_spatial-intersect-point_03.adm"
+select element {'a':a,'b':b}
+from  MyData as a,
+      MyData as b
+where test."spatial-intersect"(a.point,b.point)
+;