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:42 UTC
[34/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/nested-index/inverted-index-complex/olist-jaccard-check-let.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/olist-jaccard-check-let.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/olist-jaccard-check-let.sqlpp
new file mode 100644
index 0000000..fb9e5c2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/olist-jaccard-check-let.sqlpp
@@ -0,0 +1,67 @@
+/*
+ * 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 : Tests whether a keyword index is applied to optimize a selection query using the similarity-jaccard-check function on lists.
+ * Tests that the optimizer rule correctly drills through the let clauses.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : [string],
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create index interests_index on Customers (nested.interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-complex_olist-jaccard-check-let.adm"
+select element c
+from Customers as c
+with jacc as test."similarity-jaccard-check"(c.nested.interests,['databases','computers','wine'],0.700000f)
+where jacc[0]
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/ulist-jaccard-check-let.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/ulist-jaccard-check-let.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/ulist-jaccard-check-let.sqlpp
new file mode 100644
index 0000000..0adc2ba
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/ulist-jaccard-check-let.sqlpp
@@ -0,0 +1,69 @@
+/*
+ * 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 : Tests whether a keyword index is applied to optimize a selection query using the similarity-jaccard-check function on lists.
+ * Tests that the optimizer rule correctly drills through the let clauses.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : {{string}},
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create table Customers2(CustomerType) primary key nested.cid;
+
+create index interests_index on Customers (nested.interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-complex_ulist-jaccard-check-let.adm"
+select element c
+from Customers as c
+with jacc as test."similarity-jaccard-check"(c.nested.interests,['databases','computers','wine'],0.700000f)
+where jacc[0]
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/word-jaccard-check-let.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/word-jaccard-check-let.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/word-jaccard-check-let.sqlpp
new file mode 100644
index 0000000..c2bc04a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/word-jaccard-check-let.sqlpp
@@ -0,0 +1,55 @@
+/*
+ * 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 : Tests whether a keyword index is applied to optimize a selection query using the similarity-jaccard-check function on word tokens.
+ * Tests that the optimizer rule correctly drills through the let clauses.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create index keyword_index on DBLP (nested.title) type keyword;
+
+write output to nc1:"rttest/inverted-index-complex_word-jaccard-check-let.adm"
+select element o
+from DBLP as o
+with jacc as test."similarity-jaccard-check"(test."word-tokens"(o.nested.title),test."word-tokens"('Transactions for Cooperative Environments'),0.500000f)
+where jacc[0]
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/word-jaccard-check-multi-let.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/word-jaccard-check-multi-let.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/word-jaccard-check-multi-let.sqlpp
new file mode 100644
index 0000000..fff2dc6
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-complex/word-jaccard-check-multi-let.sqlpp
@@ -0,0 +1,57 @@
+/*
+ * 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 : Tests whether a keyword index is applied to optimize a selection query using the similarity-jaccard-check function on word tokens.
+ * Tests that the optimizer rule correctly drills through the let clauses.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create index keyword_index on DBLP (nested.title) type keyword;
+
+write output to nc1:"rttest/inverted-index-complex_word-jaccard-check-multi-let.adm"
+select element {'Paper':paper_tokens,'Query':query_tokens}
+from DBLP as paper
+with paper_tokens as test."word-tokens"(paper.nested.title),
+ query_tokens as test."word-tokens"('Transactions for Cooperative Environments'),
+ jacc as test."similarity-jaccard-check"(paper_tokens,query_tokens,0.800000f)
+where jacc[0]
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01.sqlpp
new file mode 100644
index 0000000..f4fe12d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_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.TweetMessageNestedType as
+ closed {
+ tweetid : int64,
+ user : TwitterUserType,
+ "sender-location" : point,
+ "send-time" : datetime,
+ "referred-topics" : {{string}},
+ "message-text" : string,
+ countA : int32,
+ countB : int32
+}
+
+create type test.TweetMessageType as
+ closed {
+ nested : TweetMessageNestedType
+}
+
+create table TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create index msgNgramIx on TweetMessages (nested."message-text") type ngram (3);
+
+write output to nc1:"rttest/inverted-index-join_leftouterjoin-probe-pidx-with-join-edit-distance-check_idx_01.adm"
+select element {'tweet':{'id':t1.nested.tweetid,'topics':t1.nested."message-text"},'similar-tweets':(
+ select element {'id':t2.nested.tweetid,'topics':t2.nested."message-text"}
+ from TweetMessages as t2
+ with sim as test."edit-distance-check"(t1.nested."message-text",t2.nested."message-text",7)
+ where (sim[0] and (t2.nested.tweetid != t1.nested.tweetid))
+ order by t2.nested.tweetid
+ )}
+from TweetMessages as t1
+where (t1.nested.tweetid > test.int64('240'))
+order by t1.nested.tweetid
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-jaccard-check-idx_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-jaccard-check-idx_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-jaccard-check-idx_01.sqlpp
new file mode 100644
index 0000000..a60538b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-jaccard-check-idx_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.TweetMessageNestedType as
+ closed {
+ tweetid : int64,
+ user : TwitterUserType,
+ "sender-location" : point,
+ "send-time" : datetime,
+ "referred-topics" : {{string}},
+ "message-text" : string,
+ countA : int32,
+ countB : int32
+}
+
+create type test.TweetMessageType as
+ closed {
+ nested : TweetMessageNestedType
+}
+
+create table TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create index topicKeywordIx on TweetMessages (nested."referred-topics") type keyword;
+
+write output to nc1:"rttest/inverted-index-join_leftouterjoin-probe-pidx-with-join-jaccard-check_idx_01.adm"
+select element {'tweet':{'id':t1.nested.tweetid,'topics':t1.nested."referred-topics"},'similar-tweets':(
+ select element {'id':t2.nested.tweetid,'topics':t2.nested."referred-topics"}
+ from TweetMessages as t2
+ with sim as test."similarity-jaccard-check"(t1.nested."referred-topics",t2.nested."referred-topics",0.500000f)
+ where (sim[0] and (t2.nested.tweetid != t1.nested.tweetid))
+ order by t2.nested.tweetid
+ )}
+from TweetMessages as t1
+where (t1.nested.tweetid > test.int64('240'))
+order by t1.nested.tweetid
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance-check_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance-check_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance-check_01.sqlpp
new file mode 100644
index 0000000..6308bb6
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance-check_01.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 joins two datasets, DBLP and CSX, based on the edit-distance-check function of their authors.
+ * DBLP has a 3-gram index on authors, 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.DBLPTypetmp as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXTypetmp as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+}
+
+create type test.CSXType as
+ closed {
+ nested : CSXTypetmp
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create table CSX(CSXType) primary key nested.id;
+
+create index ngram_index on DBLP (nested.authors) type ngram (3);
+
+write output to nc1:"rttest/inverted-index-join_ngram-edit-distance-check_01.adm"
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ CSX as b
+where (test."edit-distance-check"(a.nested.authors,b.nested.authors,3)[0] and (a.nested.id < b.nested.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance-contains.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance-contains.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance-contains.sqlpp
new file mode 100644
index 0000000..53b87e5
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance-contains.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 joins two datasets, DBLP and CSX, based on the edit-distance-contains function of their authors.
+ * DBLP has a 3-gram index on authors, 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.DBLPTypetmp as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXTypetmp as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+}
+
+create type test.CSXType as
+ closed {
+ nested : CSXTypetmp
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create table CSX(CSXType) primary key nested.id;
+
+create index ngram_index on DBLP (nested.authors) type ngram (3);
+
+write output to nc1:"rttest/inverted-index-join_ngram-edit-distance-contains.adm"
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ CSX as b
+where (test."edit-distance-contains"(a.nested.authors,b.nested.authors,3)[0] and (a.nested.id < b.nested.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance-inline.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance-inline.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance-inline.sqlpp
new file mode 100644
index 0000000..2dac7e2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance-inline.sqlpp
@@ -0,0 +1,57 @@
+/*
+ * 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 edit-distance function of its authors.
+ * DBLP has a 3-gram index on authors, 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 database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create index ngram_index on DBLP (nested.authors) type ngram (3);
+
+write output to nc1:"rttest/inverted-index-join-noeqjoin_ngram-edit-distance-inline.adm"
+select element {'aauthors':a.nested.authors,'bauthors':b.nested.authors,'ed':ed}
+from DBLP as a,
+ DBLP as b
+with ed as test."edit-distance"(a.nested.authors,b.nested.authors)
+where ((ed < 3) and (a.nested.id < b.nested.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance_01.sqlpp
new file mode 100644
index 0000000..1006106
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-edit-distance_01.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 joins two datasets, DBLP and CSX, based on the edit-distance function of their authors.
+ * DBLP has a 3-gram index on authors, 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.DBLPTypetmp as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXTypetmp as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+}
+
+create type test.CSXType as
+ closed {
+ nested : CSXTypetmp
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create table CSX(CSXType) primary key nested.id;
+
+create index ngram_index on DBLP (nested.authors) type ngram (3);
+
+write output to nc1:"rttest/inverted-index-join_ngram-edit-distance_01.adm"
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ CSX as b
+where ((test."edit-distance"(a.nested.authors,b.nested.authors) < 3) and (a.nested.id < b.nested.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-fuzzyeq-edit-distance_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-fuzzyeq-edit-distance_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-fuzzyeq-edit-distance_01.sqlpp
new file mode 100644
index 0000000..ac2c821
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-fuzzyeq-edit-distance_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 : Fuzzy joins two datasets, DBLP and CSX, based on ~= using edit distance of their authors.
+ * DBLP has a 3-gram index on authors, 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.DBLPTypetmp as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXTypetmp as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+}
+
+create type test.CSXType as
+ closed {
+ nested : CSXTypetmp
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create table CSX(CSXType) primary key nested.id;
+
+create index ngram_index on CSX (nested.authors) type ngram (3);
+
+write output to nc1:"rttest/inverted-index-join_ngram-fuzzyeq-edit-distance_01.adm"
+set "simfunction" "edit-distance";
+
+set "simthreshold" "3";
+
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ CSX as b
+where ((a.nested.authors ~= b.nested.authors) and (a.nested.id < b.nested.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-fuzzyeq-jaccard_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-fuzzyeq-jaccard_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-fuzzyeq-jaccard_01.sqlpp
new file mode 100644
index 0000000..23e6458
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-fuzzyeq-jaccard_01.sqlpp
@@ -0,0 +1,76 @@
+/*
+ * 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 has a 3-gram 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.DBLPTypetmp as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXTypetmp as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+}
+
+create type test.CSXType as
+ closed {
+ nested : CSXTypetmp
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create table CSX(CSXType) primary key nested.id;
+
+create index ngram_index on CSX (nested.title) type ngram (3);
+
+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.nested.title,3,false) ~= test."gram-tokens"(b.nested.title,3,false)) and (a.nested.id < b.nested.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-jaccard-check_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-jaccard-check_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-jaccard-check_01.sqlpp
new file mode 100644
index 0000000..acdc1e1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-jaccard-check_01.sqlpp
@@ -0,0 +1,72 @@
+/*
+ * 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' 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 database test if exists;
+create database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPTypetmp as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXTypetmp as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+}
+
+create type test.CSXType as
+ closed {
+ nested : CSXTypetmp
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create table CSX(CSXType) primary key nested.id;
+
+create index ngram_index on CSX (nested.title) type ngram (3);
+
+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.nested.title,3,false),test."gram-tokens"(b.nested.title,3,false),0.500000f)[0] and (a.nested.id < b.nested.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-jaccard-inline.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-jaccard-inline.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-jaccard-inline.sqlpp
new file mode 100644
index 0000000..c829209
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-jaccard-inline.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 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 database test if exists;
+create database test;
+
+use test;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPTypetmp as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create index ngram_index on DBLP (nested.title) type ngram (3);
+
+write output to nc1:"rttest/inverted-index-join-noeqjoin_ngram-jaccard-inline.adm"
+select element {'atitle':a.nested.title,'btitle':b.nested.title,'jacc':jacc}
+from DBLP as a,
+ DBLP as b
+with jacc as test."similarity-jaccard"(test."gram-tokens"(a.nested.title,3,false),test."gram-tokens"(b.nested.title,3,false))
+where ((jacc >= 0.500000f) and (a.nested.id < b.nested.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-jaccard_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-jaccard_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-jaccard_01.sqlpp
new file mode 100644
index 0000000..94970e7
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ngram-jaccard_01.sqlpp
@@ -0,0 +1,72 @@
+/*
+ * 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 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.DBLPTypetmp as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXTypetmp as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+}
+
+create type test.CSXType as
+ closed {
+ nested : CSXTypetmp
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create table CSX(CSXType) primary key nested.id;
+
+create index ngram_index on CSX (nested.title) type ngram (3);
+
+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.nested.title,3,false),test."gram-tokens"(b.nested.title,3,false)) >= 0.500000f) and (a.nested.id < b.nested.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-edit-distance-check_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-edit-distance-check_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-edit-distance-check_01.sqlpp
new file mode 100644
index 0000000..013ee34
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-edit-distance-check_01.sqlpp
@@ -0,0 +1,68 @@
+/*
+ * 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, Customer and Customer2, based on the edit-distance-check function of their interest lists.
+ * Customers has a keyword index on interests, 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.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : [string],
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create table Customers2(CustomerType) primary key nested.cid;
+
+create index interests_index on Customers (nested.interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_olist-edit-distance-check_01.adm"
+select element {'arec':a,'brec':b}
+from Customers as a,
+ Customers2 as b
+where (test."edit-distance-check"(a.nested.interests,b.nested.interests,3)[0] and (a.nested.cid < b.nested.cid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-edit-distance-inline.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-edit-distance-inline.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-edit-distance-inline.sqlpp
new file mode 100644
index 0000000..c218e34
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-edit-distance-inline.sqlpp
@@ -0,0 +1,71 @@
+/*
+ * 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, Customers, based on the edit-distance function of its interest lists.
+ * Customers has a keyword index on interests, 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 database test if exists;
+create database test;
+
+use test;
+
+
+create type test.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : [string],
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create table Customers2(CustomerType) primary key nested.cid;
+
+create index interests_index on Customers (nested.interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-join-noeqjoin_olist-edit-distance-inline.adm"
+select element {'ainterests':a.nested.interests,'binterests':b.nested.interests,'ed':ed}
+from Customers as a,
+ Customers as b
+with ed as test."edit-distance"(a.nested.interests,b.nested.interests)
+where ((ed <= 2) and (a.nested.cid < b.nested.cid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-edit-distance_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-edit-distance_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-edit-distance_01.sqlpp
new file mode 100644
index 0000000..827c194
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-edit-distance_01.sqlpp
@@ -0,0 +1,68 @@
+/*
+ * 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, Customer and Customer2, based on the edit-distance function of their interest lists.
+ * Customers has a keyword index on interests, 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.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : [string],
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create table Customers2(CustomerType) primary key nested.cid;
+
+create index interests_index on Customers (nested.interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_olist-edit-distance_01.adm"
+select element {'arec':a,'brec':b}
+from Customers as a,
+ Customers2 as b
+where ((test."edit-distance"(a.nested.interests,b.nested.interests) <= 2) and (a.nested.cid < b.nested.cid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-fuzzyeq-edit-distance_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-fuzzyeq-edit-distance_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-fuzzyeq-edit-distance_01.sqlpp
new file mode 100644
index 0000000..20ac47d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-fuzzyeq-edit-distance_01.sqlpp
@@ -0,0 +1,72 @@
+/*
+ * 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, Customer and Customer2, based on ~= using edit distance of their interest lists.
+ * Customers has a keyword index on interests, 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.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : [string],
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create table Customers2(CustomerType) primary key nested.cid;
+
+create index interests_index on Customers (nested.interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_olist-fuzzyeq-jaccard_01.adm"
+set "simfunction" "edit-distance";
+
+set "simthreshold" "3";
+
+select element {'arec':a,'brec':b}
+from Customers as a,
+ Customers2 as b
+where ((a.nested.interests ~= b.nested.interests) and (a.nested.cid < b.nested.cid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-fuzzyeq-jaccard_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-fuzzyeq-jaccard_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-fuzzyeq-jaccard_01.sqlpp
new file mode 100644
index 0000000..b4cd886
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-fuzzyeq-jaccard_01.sqlpp
@@ -0,0 +1,72 @@
+/*
+ * 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, Customer and Customer2, based on ~= using Jaccard of their interest lists.
+ * Customers has a keyword index on interests, 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.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : [string],
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create table Customers2(CustomerType) primary key nested.cid;
+
+create index interests_index on Customers (nested.interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_olist-fuzzyeq-jaccard_01.adm"
+set "simfunction" "jaccard";
+
+set "simthreshold" "0.7f";
+
+select element {'arec':a,'brec':b}
+from Customers as a,
+ Customers2 as b
+where ((a.nested.interests /*+ indexnl */ ~= b.nested.interests) and (a.nested.cid < b.nested.cid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-jaccard-check_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-jaccard-check_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-jaccard-check_01.sqlpp
new file mode 100644
index 0000000..40d94ea
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-jaccard-check_01.sqlpp
@@ -0,0 +1,68 @@
+/*
+ * 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, Customer and Customer2, based on the similarity-jaccard-check function of their interest lists.
+ * Customers has a keyword index on interests, 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.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : [string],
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create table Customers2(CustomerType) primary key nested.cid;
+
+create index interests_index on Customers (nested.interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_olist-jaccard-check_01.adm"
+select element {'arec':a,'brec':b}
+from Customers as a,
+ Customers2 as b
+where ( /*+ indexnl */ test."similarity-jaccard-check"(a.nested.interests,b.nested.interests,0.700000f)[0] and (a.nested.cid < b.nested.cid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-jaccard-inline.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-jaccard-inline.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-jaccard-inline.sqlpp
new file mode 100644
index 0000000..3890206
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-jaccard-inline.sqlpp
@@ -0,0 +1,71 @@
+/*
+ * 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, Customers, based on the similarity-jaccard function of its interest lists.
+ * Customers has a keyword index on interests, 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 database test if exists;
+create database test;
+
+use test;
+
+
+create type test.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : [string],
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create table Customers2(CustomerType) primary key nested.cid;
+
+create index interests_index on Customers (nested.interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-join-noeqjoin_olist-jaccard-inline.adm"
+select element {'ainterests':a.nested.interests,'binterests':b.nested.interests,'jacc':jacc}
+from Customers as a,
+ Customers as b
+with jacc as /*+ indexnl */ test."similarity-jaccard"(a.nested.interests,b.nested.interests)
+where ((jacc >= 0.700000f) and (a.nested.cid < b.nested.cid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-jaccard_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-jaccard_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-jaccard_01.sqlpp
new file mode 100644
index 0000000..8a9d98a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/olist-jaccard_01.sqlpp
@@ -0,0 +1,68 @@
+/*
+ * 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, Customer and Customer2, based on the similarity-jaccard function of their interest lists.
+ * Customers has a keyword index on interests, 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.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : [string],
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create table Customers2(CustomerType) primary key nested.cid;
+
+create index interests_index on Customers (nested.interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_olist-jaccard_01.adm"
+select element {'arec':a,'brec':b}
+from Customers as a,
+ Customers2 as b
+where (( /*+ indexnl */ test."similarity-jaccard"(a.nested.interests,b.nested.interests) >= 0.700000f) and (a.nested.cid < b.nested.cid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-fuzzyeq-jaccard_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-fuzzyeq-jaccard_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-fuzzyeq-jaccard_01.sqlpp
new file mode 100644
index 0000000..b2007ad
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-fuzzyeq-jaccard_01.sqlpp
@@ -0,0 +1,72 @@
+/*
+ * 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, Customer and Customer2, based on ~= using Jaccard of their interest sets.
+ * Customers has a keyword index on interests, 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.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : {{string}},
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create table Customers2(CustomerType) primary key nested.cid;
+
+create index interests_index on Customers (nested.interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_ulist-fuzzyeq-jaccard_01.adm"
+set "simfunction" "jaccard";
+
+set "simthreshold" "0.7f";
+
+select element {'arec':a,'brec':b}
+from Customers as a,
+ Customers2 as b
+where ((a.nested.interests /*+ indexnl */ ~= b.nested.interests) and (a.nested.cid < b.nested.cid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-jaccard-check_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-jaccard-check_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-jaccard-check_01.sqlpp
new file mode 100644
index 0000000..4267ccd
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-jaccard-check_01.sqlpp
@@ -0,0 +1,68 @@
+/*
+ * 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, Customer and Customer2, based on the similarity-jaccard-check function of their interest sets.
+ * Customers has a keyword index on interests, 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.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : {{string}},
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create table Customers2(CustomerType) primary key nested.cid;
+
+create index interests_index on Customers (nested.interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_ulist-jaccard-check_01.adm"
+select element {'arec':a,'brec':b}
+from Customers as a,
+ Customers2 as b
+where ( /*+ indexnl */ test."similarity-jaccard-check"(a.nested.interests,b.nested.interests,0.700000f)[0] and (a.nested.cid < b.nested.cid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-jaccard-inline.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-jaccard-inline.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-jaccard-inline.sqlpp
new file mode 100644
index 0000000..80ad630
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-jaccard-inline.sqlpp
@@ -0,0 +1,71 @@
+/*
+ * 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, Customers, based on the similarity-jaccard function of its interest sets.
+ * Customers has a keyword index on interests, 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 database test if exists;
+create database test;
+
+use test;
+
+
+create type test.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : {{string}},
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create table Customers2(CustomerType) primary key nested.cid;
+
+create index interests_index on Customers (nested.interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-join-noeqjoin_ulist-jaccard-inline.adm"
+select element {'ainterests':a.nested.interests,'binterests':b.nested.interests,'jacc':jacc}
+from Customers as a,
+ Customers as b
+with jacc as /*+ indexnl */ test."similarity-jaccard"(a.nested.interests,b.nested.interests)
+where ((jacc >= 0.700000f) and (a.nested.cid < b.nested.cid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-jaccard_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-jaccard_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-jaccard_01.sqlpp
new file mode 100644
index 0000000..9252836
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/ulist-jaccard_01.sqlpp
@@ -0,0 +1,68 @@
+/*
+ * 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, Customer and Customer2, based on the similarity-jaccard function of their interest sets.
+ * Customers has a keyword index on interests, 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.AddressType as
+ closed {
+ number : int32,
+ street : string,
+ city : string
+}
+
+create type test.CustomerTypetmp as
+ closed {
+ cid : int32,
+ name : string,
+ age : int32?,
+ address : AddressType?,
+ interests : {{string}},
+ children : [{
+ name : string,
+ age : int32?
+ }
+]
+}
+
+create type test.CustomerType as
+ closed {
+ nested : CustomerTypetmp
+}
+
+create table Customers(CustomerType) primary key nested.cid;
+
+create table Customers2(CustomerType) primary key nested.cid;
+
+create index interests_index on Customers (nested.interests) type keyword;
+
+write output to nc1:"rttest/inverted-index-join_ulist-jaccard_01.adm"
+select element {'arec':a,'brec':b}
+from Customers as a,
+ Customers2 as b
+where (( /*+ indexnl */ test."similarity-jaccard"(a.nested.interests,b.nested.interests) >= 0.700000f) and (a.nested.cid < b.nested.cid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-fuzzyeq-jaccard_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-fuzzyeq-jaccard_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-fuzzyeq-jaccard_01.sqlpp
new file mode 100644
index 0000000..f4630b4
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-fuzzyeq-jaccard_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 : 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 database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXTypetmp as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+}
+
+create type test.CSXType as
+ closed {
+ nested : CSXTypetmp
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create table CSX(CSXType) primary key nested.id;
+
+create index keyword_index on DBLP (nested.title) type keyword;
+
+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.nested.title) ~= test."word-tokens"(b.nested.title)) and (a.nested.id < b.nested.id))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-jaccard-check-after-btree-access.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-jaccard-check-after-btree-access.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-jaccard-check-after-btree-access.sqlpp
new file mode 100644
index 0000000..143f848
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-jaccard-check-after-btree-access.sqlpp
@@ -0,0 +1,76 @@
+/*
+ * 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.TweetMessageNestedType as
+ closed {
+ tweetid : int64,
+ user : TwitterUserType,
+ "sender-location" : point,
+ "send-time" : datetime,
+ "referred-topics" : {{string}},
+ "message-text" : string,
+ countA : int32,
+ countB : int32
+}
+
+create type test.TweetMessageType as
+ closed {
+ nested : TweetMessageNestedType
+}
+
+create table 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") type keyword;
+
+write output to nc1:"rttest/inverted-index-join_word-jaccard-check-after-btree-access.adm"
+select element {'t1':t1.nested.tweetid,'t2':t2.nested.tweetid,'sim':sim[1]}
+from TweetMessages as t1,
+ TweetMessages as t2
+with sim as test."similarity-jaccard-check"(test."word-tokens"(t1.nested."message-text"),test."word-tokens"(t2.nested."message-text"),0.600000f)
+where (sim[0] and (t1.nested.tweetid < test.int64('20')) and (t2.nested.tweetid != t1.nested.tweetid))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-jaccard-check_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-jaccard-check_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-jaccard-check_01.sqlpp
new file mode 100644
index 0000000..e6180ee
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-jaccard-check_01.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 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 database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.CSXTypetmp as
+ closed {
+ id : int32,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+}
+
+create type test.CSXType as
+ closed {
+ nested : CSXTypetmp
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create table CSX(CSXType) primary key nested.id;
+
+create index keyword_index on DBLP (nested.title) type keyword;
+
+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.nested.title),test."word-tokens"(b.nested.title),0.500000f)[0] and (a.nested.id < b.nested.id))
+;