You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by al...@apache.org on 2018/10/16 04:18:49 UTC
[19/21] asterixdb git commit: [ASTERIXDB-2286][COMP][FUN][HYR]
Parallel Sort Optimization
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.sqlpp
new file mode 100644
index 0000000..5b88828
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.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 btree index in index subtree.
+ * Issue : 730, 741
+ * Expected Res : Success
+ * Date : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ `screen-name` : string,
+ lang : string,
+ `friends-count` : integer,
+ `statuses-count` : integer,
+ name : string,
+ `followers-count` : integer
+};
+
+create type test.TweetMessageNestedType as
+{
+ tweetid : bigint,
+ user : TwitterUserType,
+ `sender-location` : point,
+ `send-time` : datetime,
+ `referred-topics` : {{string}},
+ `message-text` : string,
+ countA : integer
+};
+
+create type test.TweetMessageType as
+{
+ nested : TweetMessageNestedType
+};
+
+create dataset TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create index msgCountBIx on TweetMessages (nested.countB:integer?) type btree enforced;
+
+write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_01.adm";
+set `compiler.sort.parallel` "true";
+
+select element {'tweetid1':t1.nested.tweetid,'count1':t1.nested.countA,'t2info':(
+ select element {'tweetid2':t2.nested.tweetid,'count2':t2.nested.countB}
+ from TweetMessages as t2
+ where (t1.nested.countA /*+ indexnl */ = t2.nested.countB)
+ order by t2.nested.tweetid
+ )}
+from TweetMessages as t1
+where (t1.nested.tweetid < test.bigint('10'))
+order by t1.nested.tweetid
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp
new file mode 100644
index 0000000..bb50b07
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp
@@ -0,0 +1,75 @@
+/*
+ * 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 btree index in index subtree.
+ * Issue : 730, 741
+ * Expected Res : Success
+ * Date : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ `screen-name` : string,
+ lang : string,
+ `friends-count` : integer,
+ `statuses-count` : integer,
+ name : string,
+ `followers-count` : integer
+};
+
+create type test.TweetMessageNestedType as
+{
+ tweetid : bigint,
+ user : TwitterUserType,
+ `sender-location` : point,
+ `send-time` : datetime,
+ `referred-topics` : {{string}},
+ `message-text` : string
+};
+
+create type test.TweetMessageType as
+{
+ nested : TweetMessageNestedType
+};
+
+create dataset TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create index msgCountAIx on TweetMessages (nested.countA:integer?) type btree enforced;
+
+create index msgCountBIx on TweetMessages (nested.countB:integer?) type btree enforced;
+
+write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_01.adm";
+set `compiler.sort.parallel` "true";
+
+select element {'tweetid1':t1.nested.tweetid,'count1':t1.nested.countA,'t2info':(
+ select element {'tweetid2':t2.nested.tweetid,'count2':t2.nested.countB}
+ from TweetMessages as t2
+ where (t1.nested.countA /*+ indexnl */ = t2.nested.countB)
+ order by t2.nested.tweetid
+ )}
+from TweetMessages as t1
+where (t1.nested.tweetid < test.bigint('10'))
+order by t1.nested.tweetid
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.sqlpp
new file mode 100644
index 0000000..dbf7c40
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.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 btree index in index subtree.
+ * Issue : 730, 741
+ * Expected Res : Success
+ * Date : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ `screen-name` : string,
+ lang : string,
+ `friends-count` : integer,
+ `statuses-count` : integer,
+ name : string,
+ `followers-count` : integer
+};
+
+create type test.TweetMessageNestedType as
+{
+ tweetid : bigint,
+ user : TwitterUserType,
+ `sender-location` : point,
+ `send-time` : datetime,
+ `referred-topics` : {{string}},
+ `message-text` : string,
+ countA : integer
+};
+
+create type test.TweetMessageType as
+{
+ nested : TweetMessageNestedType
+};
+
+create dataset TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create index msgCountBIx on TweetMessages (nested.countB:integer?) type btree enforced;
+
+write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_02.adm";
+set `compiler.sort.parallel` "true";
+
+select element {'tweetid1':t1.nested.tweetid,'count1':t1.nested.countA,'t2info':(
+ select element {'tweetid2':t2.nested.tweetid,'count2':t2.nested.countB}
+ from TweetMessages as t2
+ where ((t1.nested.countA /*+ indexnl */ = t2.nested.countB) and (t1.nested.tweetid != t2.nested.tweetid))
+ order by t2.nested.tweetid
+ )}
+from TweetMessages as t1
+where (t1.nested.tweetid < test.bigint('10'))
+order by t1.nested.tweetid
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp
new file mode 100644
index 0000000..df41b65
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp
@@ -0,0 +1,75 @@
+/*
+ * 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 btree index in index subtree.
+ * Issue : 730, 741
+ * Expected Res : Success
+ * Date : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ `screen-name` : string,
+ lang : string,
+ `friends-count` : integer,
+ `statuses-count` : integer,
+ name : string,
+ `followers-count` : integer
+};
+
+create type test.TweetMessageNestedType as
+{
+ tweetid : bigint,
+ user : TwitterUserType,
+ `sender-location` : point,
+ `send-time` : datetime,
+ `referred-topics` : {{string}},
+ `message-text` : string
+};
+
+create type test.TweetMessageType as
+{
+ nested : TweetMessageNestedType
+};
+
+create dataset TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create index msgCountAIx on TweetMessages (nested.countA:integer?) type btree enforced;
+
+create index msgCountBIx on TweetMessages (nested.countB:integer?) type btree enforced;
+
+write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_02.adm";
+set `compiler.sort.parallel` "true";
+
+select element {'tweetid1':t1.nested.tweetid,'count1':t1.nested.countA,'t2info':(
+ select element {'tweetid2':t2.nested.tweetid,'count2':t2.nested.countB}
+ from TweetMessages as t2
+ where ((t1.nested.countA /*+ indexnl */ = t2.nested.countB) and (t1.nested.tweetid != t2.nested.tweetid))
+ order by t2.nested.tweetid
+ )}
+from TweetMessages as t1
+where (t1.nested.tweetid < test.bigint('10'))
+order by t1.nested.tweetid
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains-panic_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains-panic_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains-panic_ps.sqlpp
new file mode 100644
index 0000000..1d22a19
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains-panic_ps.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 an ngram_index is applied to optimize a selection query using the contains function.
+ * The index should *not* be applied (see below).
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+{
+ id : integer,
+ dblpid : string,
+ authors : string,
+ misc : string
+};
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+};
+
+create dataset DBLP(DBLPType) primary key nested.id;
+
+create index ngram_index on DBLP (nested.title:string?) type ngram (3) enforced;
+
+write output to asterix_nc1:"rttest/inverted-index-basic_ngram-contains-panic.adm";
+set `compiler.sort.parallel` "true";
+
+select element o
+from DBLP as o
+where test.contains(o.nested.title,'Mu')
+order by o.nested.id
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains_ps.sqlpp
new file mode 100644
index 0000000..7e98edf
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/ngram-contains_ps.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 an ngram_index is applied to optimize a selection query using the contains function.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+{
+ id : integer,
+ dblpid : string,
+ authors : string,
+ misc : string
+};
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+};
+
+create dataset DBLP(DBLPType) primary key nested.id;
+
+create index ngram_index on DBLP (nested.title:string?) type ngram (3) enforced;
+
+write output to asterix_nc1:"rttest/inverted-index-basic_ngram-contains.adm";
+set `compiler.sort.parallel` "true";
+
+select element o
+from DBLP as o
+where test.contains(o.nested.title,'Multimedia')
+order by o.nested.id
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/word-contains_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/word-contains_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/word-contains_ps.sqlpp
new file mode 100644
index 0000000..b6407d5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-basic/word-contains_ps.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 contains function.
+ * The index should *not* be applied (see below).
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+{
+ id : integer,
+ dblpid : string,
+ authors : string,
+ misc : string
+};
+
+create type test.DBLPType as
+ closed {
+ nested : DBLPTypetmp
+};
+
+create dataset DBLP(DBLPType) primary key nested.id;
+
+create index keyword_index on DBLP (nested.title:string?) type keyword enforced;
+
+write output to asterix_nc1:"rttest/inverted-index-basic_word-contains.adm";
+set `compiler.sort.parallel` "true";
+
+select element o
+from DBLP as o
+where test.contains(o.nested.title,'Multimedia')
+order by o.nested.id
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp
new file mode 100644
index 0000000..0a55178
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp
@@ -0,0 +1,75 @@
+/*
+ * 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 dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ `screen-name` : string,
+ lang : string,
+ `friends-count` : integer,
+ `statuses-count` : integer,
+ name : string,
+ `followers-count` : integer
+};
+
+create type test.TweetMessageNestedType as
+{
+ tweetid : bigint,
+ user : TwitterUserType,
+ `sender-location` : point,
+ `send-time` : datetime,
+ `referred-topics` : {{string}},
+ countA : integer,
+ countB : integer
+};
+
+create type test.TweetMessageType as
+{
+ nested : TweetMessageNestedType
+};
+
+create dataset TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create index msgNgramIx on TweetMessages (nested.`message-text`:string?) type ngram (3) enforced;
+
+write output to asterix_nc1:"rttest/inverted-index-join_leftouterjoin-probe-pidx-with-join-edit-distance-check_idx_01.adm";
+set `compiler.sort.parallel` "true";
+
+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.bigint('240'))
+order by t1.nested.tweetid
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_01_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_01_ps.sqlpp
new file mode 100644
index 0000000..fe6c65d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_01_ps.sqlpp
@@ -0,0 +1,61 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Tests whether an ngram_index is applied to optimize a join query using the contains function.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+ id : integer,
+ dblpid : string,
+ misc : string
+};
+
+create type test.CSXType as
+ closed {
+ id : integer,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+};
+
+create dataset DBLP(DBLPType) primary key id;
+
+create dataset CSX(CSXType) primary key id;
+
+create index ngram_index on DBLP (title:string?) type ngram (3) enforced;
+
+write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains-01.adm";
+set `compiler.sort.parallel` "true";
+
+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/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_02_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_02_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_02_ps.sqlpp
new file mode 100644
index 0000000..955f38c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_02_ps.sqlpp
@@ -0,0 +1,61 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Tests whether an ngram_index is applied to optimize a join query using the contains function.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+ id : integer,
+ dblpid : string,
+ title : string,
+ misc : string
+};
+
+create type test.CSXType as
+{
+ id : integer,
+ csxid : string,
+ authors : string,
+ misc : string
+};
+
+create dataset DBLP(DBLPType) primary key id;
+
+create dataset CSX(CSXType) primary key id;
+
+create index ngram_index on CSX (title:string?) type ngram (3) enforced;
+
+write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains-02.adm";
+set `compiler.sort.parallel` "true";
+
+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/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_03_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_03_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_03_ps.sqlpp
new file mode 100644
index 0000000..eb6fe2c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_03_ps.sqlpp
@@ -0,0 +1,51 @@
+/*
+ * 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 dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+ id : integer,
+ dblpid : string,
+ authors : string,
+ misc : string
+};
+
+create dataset DBLP(DBLPType) primary key id;
+
+create index ngram_index on DBLP (title:string?) type ngram (3) enforced;
+
+write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains-03.adm";
+set `compiler.sort.parallel` "true";
+
+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/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_04_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_04_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_04_ps.sqlpp
new file mode 100644
index 0000000..a924e19
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/inverted-index-join/ngram-contains_04_ps.sqlpp
@@ -0,0 +1,62 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Tests whether an ngram_index is applied to optimize a join query using the contains function.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+ id : integer,
+ dblpid : string,
+ misc : string
+};
+
+create type test.CSXType as
+{
+ id : integer,
+ csxid : string,
+ authors : string,
+ misc : string
+};
+
+create dataset DBLP(DBLPType) primary key id;
+
+create dataset 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 asterix_nc1:"rttest/inverted-index-join_ngram-contains-04.adm";
+set `compiler.sort.parallel` "true";
+
+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/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp
new file mode 100644
index 0000000..19143d3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp
@@ -0,0 +1,81 @@
+/*
+ * 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 dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ `screen-name` : string,
+ lang : string,
+ `friends-count` : integer,
+ `statuses-count` : integer,
+ name : string,
+ `followers-count` : integer
+};
+
+create type test.TweetMessageNestedType as
+{
+ tweetid : bigint,
+ user : TwitterUserType,
+ `send-time` : datetime,
+ `referred-topics` : {{string}},
+ `message-text` : string,
+ countA : integer,
+ countB : integer
+};
+
+create type test.TweetMessageType as
+{
+ nested : TweetMessageNestedType
+};
+
+create dataset TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create index twmSndLocIx on TweetMessages (nested.`sender-location`:point?) type rtree enforced;
+
+create index msgCountAIx on TweetMessages (nested.countA) type btree;
+
+create index msgCountBIx on TweetMessages (nested.countB) type btree;
+
+create index msgTextIx on TweetMessages (nested.`message-text`) type keyword;
+
+write output to asterix_nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_01.adm";
+set `compiler.sort.parallel` "true";
+
+select element {'tweetid1':t1.nested.tweetid,'loc1':t1.nested.`sender-location`,'nearby-message':(
+ select element {'tweetid2':t2.nested.tweetid,'loc2':t2.nested.`sender-location`}
+ from TweetMessages as t2
+ where test.`spatial-intersect`(t2.nested.`sender-location`,n)
+ order by t2.tweetid
+ )}
+from TweetMessages as t1
+with n as test.`create-circle`(t1.nested.`sender-location`,0.5)
+where (t1.nested.tweetid < test.bigint('10'))
+order by t1.nested.tweetid
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp
new file mode 100644
index 0000000..0471760
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/nested-open-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp
@@ -0,0 +1,81 @@
+/*
+ * 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 dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ `screen-name` : string,
+ lang : string,
+ `friends-count` : integer,
+ `statuses-count` : integer,
+ name : string,
+ `followers-count` : integer
+};
+
+create type test.TweetMessageNestedType as
+{
+ tweetid : bigint,
+ user : TwitterUserType,
+ `send-time` : datetime,
+ `referred-topics` : {{string}},
+ `message-text` : string,
+ countA : integer,
+ countB : integer
+};
+
+create type test.TweetMessageType as
+{
+ nested : TweetMessageNestedType
+};
+
+create dataset TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create index twmSndLocIx on TweetMessages (nested.`sender-location`:point?) type rtree enforced;
+
+create index msgCountAIx on TweetMessages (nested.countA) type btree;
+
+create index msgCountBIx on TweetMessages (nested.countB) type btree;
+
+create index msgTextIx on TweetMessages (nested.`message-text`) type keyword;
+
+write output to asterix_nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_02.adm";
+set `compiler.sort.parallel` "true";
+
+select element {'tweetid1':t1.nested.tweetid,'loc1':t1.nested.`sender-location`,'nearby-message':(
+ select element {'tweetid2':t2.nested.tweetid,'loc2':t2.nested.`sender-location`}
+ from TweetMessages as t2
+ where (test.`spatial-intersect`(t2.nested.`sender-location`,n) and (t1.nested.tweetid != t2.nested.tweetid))
+ order by t2.nested.tweetid
+ )}
+from TweetMessages as t1
+with n as test.`create-circle`(t1.nested.`sender-location`,0.5)
+where (t1.nested.tweetid < test.bigint('10'))
+order by t1.nested.tweetid
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.sqlpp
new file mode 100644
index 0000000..2514206
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1_ps.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 : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary btree index in index subtree.
+ * Issue : 730, 741
+ * Expected Res : Success
+ * Date : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ `screen-name` : string,
+ lang : string,
+ `friends-count` : integer,
+ `statuses-count` : integer,
+ name : string,
+ `followers-count` : integer
+};
+
+create type test.TweetMessageType as
+{
+ tweetid : bigint,
+ user : TwitterUserType,
+ `sender-location` : point,
+ `send-time` : datetime,
+ `referred-topics` : {{string}},
+ `message-text` : string,
+ countA : integer
+};
+
+create dataset TweetMessages(TweetMessageType) primary key tweetid;
+
+create index msgCountBIx on TweetMessages (countB:integer?) type btree enforced;
+
+write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_01.adm";
+set `compiler.sort.parallel` "true";
+
+select element {'tweetid1':t1.tweetid,'count1':t1.countA,'t2info':(
+ select element {'tweetid2':t2.tweetid,'count2':t2.countB}
+ from TweetMessages as t2
+ where (t1.countA /*+ indexnl */ = t2.countB)
+ order by t2.tweetid
+ )}
+from TweetMessages as t1
+where (t1.tweetid < test.bigint('10'))
+order by t1.tweetid
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.sqlpp
new file mode 100644
index 0000000..abe7d59
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2_ps.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 : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary btree index in index subtree.
+ * Issue : 730, 741
+ * Expected Res : Success
+ * Date : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ `screen-name` : string,
+ lang : string,
+ `friends-count` : integer,
+ `statuses-count` : integer,
+ name : string,
+ `followers-count` : integer
+};
+
+create type test.TweetMessageType as
+{
+ tweetid : bigint,
+ user : TwitterUserType,
+ `sender-location` : point,
+ `send-time` : datetime,
+ `referred-topics` : {{string}},
+ `message-text` : string
+};
+
+create dataset TweetMessages(TweetMessageType) primary key tweetid;
+
+create index msgCountAIx on TweetMessages (countA:integer?) type btree enforced;
+
+create index msgCountBIx on TweetMessages (countB:integer?) type btree enforced;
+
+write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_01.adm";
+set `compiler.sort.parallel` "true";
+
+select element {'tweetid1':t1.tweetid,'count1':t1.countA,'t2info':(
+ select element {'tweetid2':t2.tweetid,'count2':t2.countB}
+ from TweetMessages as t2
+ where (t1.countA /*+ indexnl */ = t2.countB)
+ order by t2.tweetid
+ )}
+from TweetMessages as t1
+where (t1.tweetid < test.bigint('10'))
+order by t1.tweetid
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.sqlpp
new file mode 100644
index 0000000..1f0c190
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1_ps.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 : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary btree index in index subtree.
+ * Issue : 730, 741
+ * Expected Res : Success
+ * Date : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ `screen-name` : string,
+ lang : string,
+ `friends-count` : integer,
+ `statuses-count` : integer,
+ name : string,
+ `followers-count` : integer
+};
+
+create type test.TweetMessageType as
+{
+ tweetid : bigint,
+ user : TwitterUserType,
+ `sender-location` : point,
+ `send-time` : datetime,
+ `referred-topics` : {{string}},
+ `message-text` : string,
+ countA : integer
+};
+
+create dataset TweetMessages(TweetMessageType) primary key tweetid;
+
+create index msgCountBIx on TweetMessages (countB:integer?) type btree enforced;
+
+write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_02.adm";
+set `compiler.sort.parallel` "true";
+
+select element {'tweetid1':t1.tweetid,'count1':t1.countA,'t2info':(
+ select element {'tweetid2':t2.tweetid,'count2':t2.countB}
+ from TweetMessages as t2
+ where ((t1.countA /*+ indexnl */ = t2.countB) and (t1.tweetid != t2.tweetid))
+ order by t2.tweetid
+ )}
+from TweetMessages as t1
+where (t1.tweetid < test.bigint('10'))
+order by t1.tweetid
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.sqlpp
new file mode 100644
index 0000000..fd29e2e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2_ps.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 : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary btree index in index subtree.
+ * Issue : 730, 741
+ * Expected Res : Success
+ * Date : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ `screen-name` : string,
+ lang : string,
+ `friends-count` : integer,
+ `statuses-count` : integer,
+ name : string,
+ `followers-count` : integer
+};
+
+create type test.TweetMessageType as
+{
+ tweetid : bigint,
+ user : TwitterUserType,
+ `sender-location` : point,
+ `send-time` : datetime,
+ `referred-topics` : {{string}},
+ `message-text` : string
+};
+
+create dataset TweetMessages(TweetMessageType) primary key tweetid;
+
+create index msgCountAIx on TweetMessages (countA:integer?) type btree enforced;
+
+create index msgCountBIx on TweetMessages (countB:integer?) type btree enforced;
+
+write output to asterix_nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_02.adm";
+set `compiler.sort.parallel` "true";
+
+select element {'tweetid1':t1.tweetid,'count1':t1.countA,'t2info':(
+ select element {'tweetid2':t2.tweetid,'count2':t2.countB}
+ from TweetMessages as t2
+ where ((t1.countA /*+ indexnl */ = t2.countB) and (t1.tweetid != t2.tweetid))
+ order by t2.tweetid
+ )}
+from TweetMessages as t1
+where (t1.tweetid < test.bigint('10'))
+order by t1.tweetid
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains-panic_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains-panic_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains-panic_ps.sqlpp
new file mode 100644
index 0000000..dbb430a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains-panic_ps.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Tests whether an ngram_index is applied to optimize a selection query using the contains function.
+ * The index should *not* be applied (see below).
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+ id : integer,
+ dblpid : string,
+ authors : string,
+ misc : string
+};
+
+create dataset DBLP(DBLPType) primary key id;
+
+create index ngram_index on DBLP (title:string?) type ngram (3) enforced;
+
+write output to asterix_nc1:"rttest/inverted-index-basic_ngram-contains-panic.adm";
+set `compiler.sort.parallel` "true";
+
+select element o
+from DBLP as o
+where test.contains(o.title,'Mu')
+order by o.id
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains_ps.sqlpp
new file mode 100644
index 0000000..060465d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/ngram-contains_ps.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Tests whether an ngram_index is applied to optimize a selection query using the contains function.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+ id : integer,
+ dblpid : string,
+ authors : string,
+ misc : string
+};
+
+create dataset DBLP(DBLPType) primary key id;
+
+create index ngram_index on DBLP (title:string?) type ngram (3) enforced;
+
+write output to asterix_nc1:"rttest/inverted-index-basic_ngram-contains.adm";
+set `compiler.sort.parallel` "true";
+
+select element o
+from DBLP as o
+where test.contains(o.title,'Multimedia')
+order by o.id
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/word-contains_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/word-contains_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/word-contains_ps.sqlpp
new file mode 100644
index 0000000..9caf6bd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-basic/word-contains_ps.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Tests whether a keyword index is applied to optimize a selection query using the contains function.
+ * The index should *not* be applied (see below).
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+ id : integer,
+ dblpid : string,
+ authors : string,
+ misc : string
+};
+
+create dataset DBLP(DBLPType) primary key id;
+
+create index keyword_index on DBLP (title:string?) type keyword enforced;
+
+write output to asterix_nc1:"rttest/inverted-index-basic_word-contains.adm";
+set `compiler.sort.parallel` "true";
+
+select element o
+from DBLP as o
+where test.contains(o.title,'Multimedia')
+order by o.id
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.sqlpp
new file mode 100644
index 0000000..2d9b15b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/leftouterjoin-probe-pidx-with-join-edit-distance-check-idx_01_ps.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 : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary rtree index in index subtree.
+ * Issue : 730, 741
+ * Expected Res : Success
+ * Date : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ `screen-name` : string,
+ lang : string,
+ `friends-count` : integer,
+ `statuses-count` : integer,
+ name : string,
+ `followers-count` : integer
+};
+
+create type test.TweetMessageType as
+{
+ tweetid : bigint,
+ user : TwitterUserType,
+ `sender-location` : point,
+ `send-time` : datetime,
+ `referred-topics` : {{string}},
+ countA : integer,
+ countB : integer
+};
+
+create dataset TweetMessages(TweetMessageType) primary key tweetid;
+
+create index msgNgramIx on TweetMessages (`message-text`:string?) type ngram (3) enforced;
+
+write output to asterix_nc1:"rttest/inverted-index-join_leftouterjoin-probe-pidx-with-join-edit-distance-check_idx_01.adm";
+set `compiler.sort.parallel` "true";
+
+select element {'tweet':{'id':t1.tweetid,'topics':t1.`message-text`},'similar-tweets':(
+ select element {'id':t2.tweetid,'topics':t2.`message-text`}
+ from TweetMessages as t2
+ with sim as test.`edit-distance-check`(t1.`message-text`,t2.`message-text`,7)
+ where (sim[0] and (t2.tweetid != t1.tweetid))
+ order by t2.tweetid
+ )}
+from TweetMessages as t1
+where (t1.tweetid > test.bigint('240'))
+order by t1.tweetid
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_01_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_01_ps.sqlpp
new file mode 100644
index 0000000..fe6c65d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_01_ps.sqlpp
@@ -0,0 +1,61 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Tests whether an ngram_index is applied to optimize a join query using the contains function.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+ id : integer,
+ dblpid : string,
+ misc : string
+};
+
+create type test.CSXType as
+ closed {
+ id : integer,
+ csxid : string,
+ title : string,
+ authors : string,
+ misc : string
+};
+
+create dataset DBLP(DBLPType) primary key id;
+
+create dataset CSX(CSXType) primary key id;
+
+create index ngram_index on DBLP (title:string?) type ngram (3) enforced;
+
+write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains-01.adm";
+set `compiler.sort.parallel` "true";
+
+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/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_02_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_02_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_02_ps.sqlpp
new file mode 100644
index 0000000..1cb8237
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_02_ps.sqlpp
@@ -0,0 +1,61 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Tests whether an ngram_index is applied to optimize a join query using the contains function.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+ id : integer,
+ dblpid : string,
+ title : string,
+ misc : string
+};
+
+create type test.CSXType as
+{
+ id : integer,
+ csxid : string,
+ authors : string,
+ misc : string
+};
+
+create dataset DBLP(DBLPType) primary key id;
+
+create dataset CSX(CSXType) primary key id;
+
+create index ngram_index on CSX (title:string?) type ngram (3) enforced;
+
+write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains-02.adm";
+set `compiler.sort.parallel` "true";
+
+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/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_03_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_03_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_03_ps.sqlpp
new file mode 100644
index 0000000..eb6fe2c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_03_ps.sqlpp
@@ -0,0 +1,51 @@
+/*
+ * 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 dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+ id : integer,
+ dblpid : string,
+ authors : string,
+ misc : string
+};
+
+create dataset DBLP(DBLPType) primary key id;
+
+create index ngram_index on DBLP (title:string?) type ngram (3) enforced;
+
+write output to asterix_nc1:"rttest/inverted-index-join_ngram-contains-03.adm";
+set `compiler.sort.parallel` "true";
+
+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/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_04_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_04_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_04_ps.sqlpp
new file mode 100644
index 0000000..a924e19
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/inverted-index-join/ngram-contains_04_ps.sqlpp
@@ -0,0 +1,62 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description : Tests whether an ngram_index is applied to optimize a join query using the contains function.
+ * The index should be applied.
+ * Success : Yes
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.DBLPType as
+{
+ id : integer,
+ dblpid : string,
+ misc : string
+};
+
+create type test.CSXType as
+{
+ id : integer,
+ csxid : string,
+ authors : string,
+ misc : string
+};
+
+create dataset DBLP(DBLPType) primary key id;
+
+create dataset 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 asterix_nc1:"rttest/inverted-index-join_ngram-contains-04.adm";
+set `compiler.sort.parallel` "true";
+
+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/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.sqlpp
new file mode 100644
index 0000000..3fd898e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01_ps.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 : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary rtree index in index subtree.
+ * Issue : 730, 741
+ * Expected Res : Success
+ * Date : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ `screen-name` : string,
+ lang : string,
+ `friends-count` : integer,
+ `statuses-count` : integer,
+ name : string,
+ `followers-count` : integer
+};
+
+create type test.TweetMessageType as
+{
+ tweetid : bigint,
+ user : TwitterUserType,
+ `send-time` : datetime,
+ `referred-topics` : {{string}},
+ `message-text` : string,
+ countA : integer,
+ countB : integer
+};
+
+create dataset TweetMessages(TweetMessageType) primary key tweetid;
+
+create index twmSndLocIx on TweetMessages (`sender-location`:point?) type rtree enforced;
+
+create index msgCountAIx on TweetMessages (countA) type btree;
+
+create index msgCountBIx on TweetMessages (countB) type btree;
+
+create index msgTextIx on TweetMessages (`message-text`) type keyword;
+
+write output to asterix_nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_01.adm";
+set `compiler.sort.parallel` "true";
+
+select element {'tweetid1':t1.tweetid,'loc1':t1.`sender-location`,'nearby-message':(
+ select element {'tweetid2':t2.tweetid,'loc2':t2.`sender-location`}
+ from TweetMessages as t2
+ where test.`spatial-intersect`(t2.`sender-location`,n)
+ order by t2.tweetid
+ )}
+from TweetMessages as t1
+with n as test.`create-circle`(t1.`sender-location`,0.5)
+where (t1.tweetid < test.bigint('10'))
+order by t1.tweetid
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.sqlpp
new file mode 100644
index 0000000..c59dba4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-enforced/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02_ps.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 : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary rtree index in index subtree.
+ * Issue : 730, 741
+ * Expected Res : Success
+ * Date : 8th May 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+ `screen-name` : string,
+ lang : string,
+ `friends-count` : integer,
+ `statuses-count` : integer,
+ name : string,
+ `followers-count` : integer
+};
+
+create type test.TweetMessageType as
+{
+ tweetid : bigint,
+ user : TwitterUserType,
+ `send-time` : datetime,
+ `referred-topics` : {{string}},
+ `message-text` : string,
+ countA : integer,
+ countB : integer
+};
+
+create dataset TweetMessages(TweetMessageType) primary key tweetid;
+
+create index twmSndLocIx on TweetMessages (`sender-location`:point?) type rtree enforced;
+
+create index msgCountAIx on TweetMessages (countA) type btree;
+
+create index msgCountBIx on TweetMessages (countB) type btree;
+
+create index msgTextIx on TweetMessages (`message-text`) type keyword;
+
+write output to asterix_nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_02.adm";
+set `compiler.sort.parallel` "true";
+
+select element {'tweetid1':t1.tweetid,'loc1':t1.`sender-location`,'nearby-message':(
+ select element {'tweetid2':t2.tweetid,'loc2':t2.`sender-location`}
+ from TweetMessages as t2
+ where (test.`spatial-intersect`(t2.`sender-location`,n) and (t1.tweetid != t2.tweetid))
+ order by t2.tweetid
+ )}
+from TweetMessages as t1
+with n as test.`create-circle`(t1.`sender-location`,0.5)
+where (t1.tweetid < test.bigint('10'))
+order by t1.tweetid
+;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-05_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-05_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-05_ps.sqlpp
new file mode 100644
index 0000000..8c3f6dd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-05_ps.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * 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 : No index join because there's no hint and the probe type is unknown. p_sort enabled.
+ * Expected Res : Success
+ * Date : 20 Jun 2017
+ */
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+write output to asterix_nc1:"rttest/btree-equi-join-non-enforced_btree-equi-join-non-enforced-05.adm";
+
+create type TestOpenType as open {
+ c_id: int64
+};
+
+create dataset TestOpen1(TestOpenType) primary key c_id;
+create dataset TestOpen2(TestOpenType) primary key c_id;
+create index idx_t2_s on TestOpen2(c_s:string);
+create index idx_t2_i64 on TestOpen2(c_i64:int64);
+create index idx_t2_i8 on TestOpen2(c_i8:int8);
+create index idx_t2_d on TestOpen2(c_d:double);
+
+set `compiler.sort.parallel` "true";
+
+select t1.c_x as c1, t2.c_x as c2
+from TestOpen1 as t1, TestOpen2 as t2
+where t1.c_s = t2.c_s
+order by t1.c_x, t2.c_x;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-06_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-06_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-06_ps.sqlpp
new file mode 100644
index 0000000..7ba14c9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-06_ps.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * 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 : No index join because the probe type is unknown. p_sort enabled.
+ * Expected Res : Success
+ * Date : 20 Jun 2017
+ */
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+write output to asterix_nc1:"rttest/btree-equi-join-non-enforced_btree-equi-join-non-enforced-06.adm";
+
+create type TestOpenType as open {
+ c_id: int64
+};
+
+create dataset TestOpen1(TestOpenType) primary key c_id;
+create dataset TestOpen2(TestOpenType) primary key c_id;
+create index idx_t2_s on TestOpen2(c_s:string);
+create index idx_t2_i64 on TestOpen2(c_i64:int64);
+create index idx_t2_i8 on TestOpen2(c_i8:int8);
+create index idx_t2_d on TestOpen2(c_d:double);
+
+set `compiler.sort.parallel` "true";
+
+select t1.c_x as c1, t2.c_x as c2
+from TestOpen1 as t1, TestOpen2 as t2
+where t1.c_s /*+ indexnl */ = t2.c_s
+order by t1.c_x, t2.c_x;
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/80225e2c/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-07_ps.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-07_ps.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-07_ps.sqlpp
new file mode 100644
index 0000000..403a75e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/open-index-non-enforced/btree-equi-join-non-enforced/btree-equi-join-non-enforced-07_ps.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * 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 : No index join because there's no hint. p_sort enabled.
+ * Expected Res : Success
+ * Date : 20 Jun 2017
+ */
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+write output to asterix_nc1:"rttest/btree-equi-join-non-enforced_btree-equi-join-non-enforced-07.adm";
+
+create type TestOpenType as open {
+ c_id: int64
+};
+
+create dataset TestOpen1(TestOpenType) primary key c_id;
+create dataset TestOpen2(TestOpenType) primary key c_id;
+create index idx_t2_s on TestOpen2(c_s:string);
+create index idx_t2_i64 on TestOpen2(c_i64:int64);
+create index idx_t2_i8 on TestOpen2(c_i8:int8);
+create index idx_t2_d on TestOpen2(c_d:double);
+
+set `compiler.sort.parallel` "true";
+
+
+select t1.c_x as c1, t2.c_x as c2
+from TestOpen1 as t1, TestOpen2 as t2
+where to_string(t1.c_s) = t2.c_s
+order by t1.c_x, t2.c_x;
\ No newline at end of file