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:39 UTC
[31/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-open-index/inverted-index-basic/word-fuzzyeq-jaccard.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-basic/word-fuzzyeq-jaccard.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-basic/word-fuzzyeq-jaccard.sqlpp
new file mode 100644
index 0000000..ef5da6c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-basic/word-fuzzyeq-jaccard.sqlpp
@@ -0,0 +1,56 @@
+/*
+ * 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 is applied to optimize a selection query with ~= using Jaccard on word tokens.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+{
+ id : int32,
+ dblpid : 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:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-basic_word-fuzzyeq-jaccard.adm"
+set "simfunction" "jaccard";
+
+set "simthreshold" "0.5f";
+
+select element o
+from DBLP as o
+where (test."word-tokens"(o.nested.title) ~= test."word-tokens"('Transactions for Cooperative Environments'))
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-basic/word-jaccard-check.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-basic/word-jaccard-check.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-basic/word-jaccard-check.sqlpp
new file mode 100644
index 0000000..dcba84e
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-basic/word-jaccard-check.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 : Tests whether a keyword index is applied to optimize a selection query using the similarity-jaccard-check function on word tokens.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+{
+ id : int32,
+ dblpid : 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:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-basic_word-jaccard-check.adm"
+select element o
+from DBLP as o
+where test."similarity-jaccard-check"(test."word-tokens"(o.nested.title),test."word-tokens"('Transactions for Cooperative Environments'),0.500000f)[0]
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-basic/word-jaccard.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-basic/word-jaccard.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-basic/word-jaccard.sqlpp
new file mode 100644
index 0000000..e6bb7d7
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-basic/word-jaccard.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 : Tests whether a keyword index is applied to optimize a selection query using the similarity-jaccard function on word tokens.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+{
+ id : int32,
+ dblpid : 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:string) type keyword enforced;
+
+write output to nc1:"rttest/inverted-index-basic_word-jaccard.adm"
+select element o
+from DBLP as o
+where (test."similarity-jaccard"(test."word-tokens"(o.nested.title),test."word-tokens"('Transactions for Cooperative Environments')) >= 0.500000f)
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let-panic-nopanic_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let-panic-nopanic_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let-panic-nopanic_01.sqlpp
new file mode 100644
index 0000000..7845d93
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let-panic-nopanic_01.sqlpp
@@ -0,0 +1,56 @@
+/*
+ * 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 an ngram_index is applied to optimize a selection query using
+ * two edit-distance-check function of which only the first can be optimized with an index.
+ * 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
+{
+ id : int32,
+ dblpid : string,
+ title : 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:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-complex_ngram-edit-distance-check-let-panic-nopanic_01.adm"
+select element o
+from DBLP as o
+with eda as test."edit-distance-check"(o.nested.authors,'Amihay Motro',3),
+ edb as test."edit-distance-check"(o.nested.authors,'Amihay Motro',5)
+where (eda[0] and edb[0])
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let-panic-nopanic_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let-panic-nopanic_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let-panic-nopanic_02.sqlpp
new file mode 100644
index 0000000..dc939c1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let-panic-nopanic_02.sqlpp
@@ -0,0 +1,56 @@
+/*
+ * 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 an ngram_index is applied to optimize a selection query using
+ * two edit-distance-check function of which only the second can be optimized with an index.
+ * 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
+{
+ id : int32,
+ dblpid : string,
+ title : 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:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-complex_ngram-edit-distance-check-let-panic-nopanic_01.adm"
+select element o
+from DBLP as o
+with edb as test."edit-distance-check"(o.nested.authors,'Amihay Motro',5),
+ eda as test."edit-distance-check"(o.nested.authors,'Amihay Motro',3)
+where (edb[0] and eda[0])
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let-panic.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let-panic.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let-panic.sqlpp
new file mode 100644
index 0000000..9668866
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let-panic.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 : Tests whether an ngram_index is applied to optimize a selection query using the edit-distance-check function on strings.
+ * Tests that the optimizer rule correctly drills through the let clauses.
+ * The index should *not* be applied (see below).
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+{
+ id : int32,
+ dblpid : string,
+ title : 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:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-complex_ngram-edit-distance-check-let-panic.adm"
+select element o
+from DBLP as o
+with ed as test."edit-distance-check"(o.nested.authors,'Amihay Motro',5)
+where ed[0]
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let.sqlpp
new file mode 100644
index 0000000..732c11d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-let.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 : Tests whether an ngram_index is applied to optimize a selection query using the edit-distance-check function on strings.
+ * 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
+{
+ id : int32,
+ dblpid : string,
+ title : 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:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-complex_ngram-edit-distance-check-let.adm"
+select element o
+from DBLP as o
+with ed as test."edit-distance-check"(o.nested.authors,'Amihay Motro',1)
+where ed[0]
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-substring.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-substring.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-substring.sqlpp
new file mode 100644
index 0000000..d96fbdf
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-substring.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * 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 an ngram_index index is applied to optimize a selection query using the similarity-edit-distance-check function on the substring of the field.
+ * Tests that the optimizer rule correctly drills through the substring function.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPNestedType as
+{
+ id : int32,
+ dblpid : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPNestedType
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create index ngram_index on DBLP (nested.title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-complex_ngram-edit-distance-check-substring.adm"
+select element {'id':paper.nested.id,'title':paper.nested.title}
+from DBLP as paper
+where test."edit-distance-check"(test.substring(paper.nested.title,0,8),'datbase',1)[0]
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-word-tokens.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-word-tokens.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-word-tokens.sqlpp
new file mode 100644
index 0000000..12fb658
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-edit-distance-check-word-tokens.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 : Tests whether an ngram_index index is applied to optimize a selection query using the similarity-edit-distance-check function on individual word tokens.
+ * Tests that the optimizer rule correctly drills through the word-tokens function and existential query.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPNestedType as
+{
+ id : int32,
+ dblpid : string,
+ authors : string,
+ misc : string
+}
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPNestedType
+}
+
+create table DBLP(DBLPType) primary key nested.id;
+
+create index ngram_index on DBLP (nested.title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-complex_ngram-edit-distance-check-word-tokens.adm"
+select element {'id':paper.nested.id,'title':paper.nested.title}
+from DBLP as paper,
+ test."word-tokens"(paper.nested.title) as word
+where test."edit-distance-check"(word,'Multmedia',1)[0]
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-jaccard-check-let.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-jaccard-check-let.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-jaccard-check-let.sqlpp
new file mode 100644
index 0000000..b6bb119
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-jaccard-check-let.sqlpp
@@ -0,0 +1,56 @@
+/*
+ * 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 an ngram_index is applied to optimize a selection query using the similarity-jaccard-check function on 3-gram 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;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPTypetmp as
+{
+ id : int32,
+ dblpid : 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:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-complex_ngram-jaccard-check-let.adm"
+select element o
+from DBLP as o
+with jacc as test."similarity-jaccard-check"(test."gram-tokens"(o.nested.title,3,false),test."gram-tokens"('Transactions for Cooperative Environments',3,false),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-open-index/inverted-index-complex/ngram-jaccard-check-multi-let.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-jaccard-check-multi-let.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-jaccard-check-multi-let.sqlpp
new file mode 100644
index 0000000..fd2a1bb
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/ngram-jaccard-check-multi-let.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * 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 an ngram_index is applied to optimize a selection query using the similarity-jaccard-check function on 3-gram 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;
+
+
+set "import-private-functions" "true";
+
+create type test.DBLPTypetmp as
+{
+ id : int32,
+ dblpid : 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:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-complex_ngram-jaccard-check-multi-let.adm"
+select element {'Paper':paper_tokens,'Query':query_tokens}
+from DBLP as paper
+with paper_tokens as test."gram-tokens"(paper.nested.title,3,false),
+ query_tokens as test."gram-tokens"('Transactions for Cooperative Environments',3,false),
+ jacc as test."similarity-jaccard-check"(paper_tokens,query_tokens,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-open-index/inverted-index-complex/word-jaccard-check-let.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/word-jaccard-check-let.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/word-jaccard-check-let.sqlpp
new file mode 100644
index 0000000..0c4929b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/word-jaccard-check-let.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 : 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
+{
+ id : int32,
+ dblpid : 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:string) type keyword enforced;
+
+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-open-index/inverted-index-complex/word-jaccard-check-multi-let.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/word-jaccard-check-multi-let.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/word-jaccard-check-multi-let.sqlpp
new file mode 100644
index 0000000..8164077
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-complex/word-jaccard-check-multi-let.sqlpp
@@ -0,0 +1,56 @@
+/*
+ * 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
+{
+ id : int32,
+ dblpid : 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:string) type keyword enforced;
+
+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-open-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-open-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-open-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01.sqlpp
new file mode 100644
index 0000000..fccb8d7
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01.sqlpp
@@ -0,0 +1,73 @@
+/*
+ * 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
+{
+ tweetid : int64,
+ user : TwitterUserType,
+ "sender-location" : point,
+ "send-time" : datetime,
+ "referred-topics" : {{string}},
+ countA : int32,
+ countB : int32
+}
+
+create type test.TweetMessageType as
+{
+ nested : TweetMessageNestedType
+}
+
+create table TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create index msgNgramIx on TweetMessages (nested."message-text":string) type ngram (3) enforced;
+
+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-open-index/inverted-index-join/ngram-contains_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_01.sqlpp
new file mode 100644
index 0000000..34dd9fa
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_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 : Tests whether an ngram_index is applied to optimize a join query using the contains function.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+ id : int32,
+ dblpid : 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-contains-01.adm"
+select element {'title1':o1.title,'title2':o2.title}
+from DBLP as o1,
+ CSX as o2
+where (test.contains(o1.title,o2.title) and (o1.id < o2.id))
+order by o1.id,o2.id
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_02.sqlpp
new file mode 100644
index 0000000..f6447c8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_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 : Tests whether an ngram_index is applied to optimize a join query using the contains function.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+ id : int32,
+ dblpid : string,
+ title : 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-contains-02.adm"
+select element {'title1':o1.title,'title2':o2.title}
+from CSX as o1,
+ DBLP as o2
+where (test.contains(o1.title,o2.title) and (o1.id < o2.id))
+order by o1.id,o2.id
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_03.sqlpp
new file mode 100644
index 0000000..58ca48a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_03.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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 an ngram_index is applied to optimize a join query using the contains function.
+ * The index should be applied.
+ * 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 ngram_index on DBLP (title:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-contains-03.adm"
+select element {'title1':o1.title,'title2':o2.title}
+from DBLP as o1,
+ DBLP as o2
+where (test.contains(o1.title,o2.title) and (o1.id < o2.id))
+order by o1.id,o2.id
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_04.sqlpp
new file mode 100644
index 0000000..b1a581f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-contains_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 : Tests whether an ngram_index is applied to optimize a join query using the contains function.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop database test if exists;
+create database test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+ id : int32,
+ dblpid : 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-contains-04.adm"
+select element {'title1':o1.title,'title2':o2.title}
+from DBLP as o1,
+ CSX as o2
+where (test.contains(o1.title,o2.title) and (o1.id < o2.id))
+order by o1.id,o2.id
+;
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_01.sqlpp
new file mode 100644
index 0000000..98a552e
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_01.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 : 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
+{
+ id : int32,
+ dblpid : string,
+ title : 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:string) type ngram (3) enforced;
+
+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-open-index/inverted-index-join/ngram-edit-distance-check_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_02.sqlpp
new file mode 100644
index 0000000..6d908cf
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_02.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 : 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
+{
+ id : int32,
+ csxid : string,
+ title : 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:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-edit-distance-check_01.adm"
+select element {'arec':a,'brec':b}
+from CSX as a,
+ DBLP 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-open-index/inverted-index-join/ngram-edit-distance-check_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_03.sqlpp
new file mode 100644
index 0000000..a6b1cd2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_03.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * 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 dataset DBLP, 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
+{
+ id : int32,
+ dblpid : string,
+ title : 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:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-edit-distance-check_01.adm"
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ DBLP 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-open-index/inverted-index-join/ngram-edit-distance-check_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_04.sqlpp
new file mode 100644
index 0000000..2550577
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_04.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
+{
+ id : int32,
+ dblpid : string,
+ title : string,
+ misc : string
+}
+
+create type test.CSXTypetmp as
+{
+ id : int32,
+ csxid : string,
+ title : 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_DBLP on DBLP (nested.authors:string) type ngram (3) enforced;
+
+create index ngram_index_CSX on CSX (nested.authors:string) type ngram (3) enforced;
+
+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-open-index/inverted-index-join/ngram-edit-distance-check_05.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_05.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_05.sqlpp
new file mode 100644
index 0000000..1688938
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-check_05.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, DBLP and CSX, based on the edit-distance-check function of their authors.
+ * DBLP has a 3-gram index on authors, and we *do not* 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
+{
+ id : int32,
+ dblpid : string,
+ title : string,
+ misc : string
+}
+
+create type test.CSXTypetmp as
+{
+ id : int32,
+ csxid : string,
+ title : 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:string) type ngram (3) enforced;
+
+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-open-index/inverted-index-join/ngram-edit-distance-contains.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-contains.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-contains.sqlpp
new file mode 100644
index 0000000..163f151
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-contains.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 : 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
+{
+ id : int32,
+ dblpid : string,
+ title : 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:string) type ngram (3) enforced;
+
+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-open-index/inverted-index-join/ngram-edit-distance-inline.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-inline.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-inline.sqlpp
new file mode 100644
index 0000000..356d816
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance-inline.sqlpp
@@ -0,0 +1,56 @@
+/*
+ * 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
+{
+ id : int32,
+ dblpid : string,
+ title : 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:string) type ngram (3) enforced;
+
+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-open-index/inverted-index-join/ngram-edit-distance_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_01.sqlpp
new file mode 100644
index 0000000..429958a
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_01.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 : 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
+{
+ id : int32,
+ dblpid : string,
+ title : 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:string) type ngram (3) enforced;
+
+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-open-index/inverted-index-join/ngram-edit-distance_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_02.sqlpp
new file mode 100644
index 0000000..b9e2bb9
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_02.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 : 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
+{
+ id : int32,
+ csxid : string,
+ title : 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:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-edit-distance_01.adm"
+select element {'arec':a,'brec':b}
+from CSX as a,
+ DBLP 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-open-index/inverted-index-join/ngram-edit-distance_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_03.sqlpp
new file mode 100644
index 0000000..dc53ff4
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_03.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * 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 dataset DBLP, 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
+{
+ id : int32,
+ dblpid : string,
+ title : 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:string) type ngram (3) enforced;
+
+write output to nc1:"rttest/inverted-index-join_ngram-edit-distance_01.adm"
+select element {'arec':a,'brec':b}
+from DBLP as a,
+ DBLP 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-open-index/inverted-index-join/ngram-edit-distance_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_04.sqlpp
new file mode 100644
index 0000000..ac087b7
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_04.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
+{
+ id : int32,
+ dblpid : string,
+ title : string,
+ misc : string
+}
+
+create type test.CSXTypetmp as
+{
+ id : int32,
+ csxid : string,
+ title : 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_DBLP on DBLP (nested.authors:string) type ngram (3) enforced;
+
+create index ngram_index_CSX on CSX (nested.authors:string) type ngram (3) enforced;
+
+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-open-index/inverted-index-join/ngram-edit-distance_05.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_05.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_05.sqlpp
new file mode 100644
index 0000000..28b0bfa
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-edit-distance_05.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, DBLP and CSX, based on the edit-distance function of their authors.
+ * DBLP has a 3-gram index on authors, and we *do not* 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
+{
+ id : int32,
+ dblpid : string,
+ title : string,
+ misc : string
+}
+
+create type test.CSXTypetmp as
+{
+ id : int32,
+ csxid : string,
+ title : 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:string) type ngram (3) enforced;
+
+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-open-index/inverted-index-join/ngram-fuzzyeq-edit-distance_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-fuzzyeq-edit-distance_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-fuzzyeq-edit-distance_01.sqlpp
new file mode 100644
index 0000000..4c6646c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-fuzzyeq-edit-distance_01.sqlpp
@@ -0,0 +1,73 @@
+/*
+ * 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
+{
+ id : int32,
+ dblpid : string,
+ title : 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:string) type ngram (3) enforced;
+
+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-open-index/inverted-index-join/ngram-fuzzyeq-edit-distance_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-fuzzyeq-edit-distance_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-fuzzyeq-edit-distance_02.sqlpp
new file mode 100644
index 0000000..4f391e1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-fuzzyeq-edit-distance_02.sqlpp
@@ -0,0 +1,73 @@
+/*
+ * 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
+{
+ id : int32,
+ csxid : string,
+ title : 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:string) type ngram (3) enforced;
+
+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 CSX as a,
+ DBLP 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-open-index/inverted-index-join/ngram-fuzzyeq-edit-distance_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-fuzzyeq-edit-distance_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-fuzzyeq-edit-distance_03.sqlpp
new file mode 100644
index 0000000..d34082f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/inverted-index-join/ngram-fuzzyeq-edit-distance_03.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 : Self joins dataset DBLP, 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
+{
+ id : int32,
+ dblpid : string,
+ title : 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:string) type ngram (3) enforced;
+
+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,
+ DBLP as b
+where ((a.nested.authors ~= b.nested.authors) and (a.nested.id < b.nested.id))
+;