asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From buyin...@apache.org
Subject [33/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-
Date Fri, 30 Oct 2015 23:16:41 GMT
http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-jaccard-inline.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-jaccard-inline.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-jaccard-inline.sqlpp
new file mode 100644
index 0000000..55a3381
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/inverted-index-join/word-jaccard-inline.sqlpp
@@ -0,0 +1,57 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Fuzzy self joins a dataset, DBLP, based on the similarity-jaccard function of its titles' word tokens.
+ *                  DBLP has a keyword index on title, and we expect the join to be transformed into an indexed nested-loop join.
+ *                  We test the inlining of variables that enable the select to be pushed into the join for subsequent optimization with an index.
+ *                  We expect the top-level equi join introduced because of surrogate optimization to be removed, since it is not necessary.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.DBLPTypetmp as
+ closed {
+  id : int32,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+}
+
+create type test.DBLPType as
+ closed {
+  nested : DBLPTypetmp
+}
+
+create  table DBLP(DBLPType) primary key nested.id;
+
+create  index keyword_index  on DBLP (nested.title) type keyword;
+
+write output to nc1:"rttest/inverted-index-join-noeqjoin_word-jaccard-inline.adm"
+select element {'atitle':a.nested.title,'btitle':b.nested.title,'jacc':jacc}
+from  DBLP as a,
+      DBLP as b
+with  jacc as test."similarity-jaccard"(test."word-tokens"(a.nested.title),test."word-tokens"(b.nested.title))
+where ((jacc >= 0.500000f) and (a.nested.id < b.nested.id))
+;

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

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01.sqlpp
new file mode 100644
index 0000000..7cb8b59
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_01.sqlpp
@@ -0,0 +1,80 @@
+/*
+ * 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}},
+  "message-text" : string,
+  countA : int32,
+  countB : int32
+}
+
+create type test.TweetMessageType as
+{
+  nested : TweetMessageNestedType
+}
+
+create  table TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create  index twmSndLocIx  on TweetMessages (nested."sender-location") type rtree;
+
+create  index msgCountAIx  on TweetMessages (nested.countA) type btree;
+
+create  index msgCountBIx  on TweetMessages (nested.countB) type btree;
+
+create  index msgTextIx  on TweetMessages (nested."message-text") type keyword;
+
+write output to nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_01.adm"
+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.int64('10'))
+order by t1.nested.tweetid
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.sqlpp
new file mode 100644
index 0000000..03e3a34
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/leftouterjoin-probe-pidx-with-join-rtree-sidx_02.sqlpp
@@ -0,0 +1,80 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and another for secondary rtree index in index subtree.
+ * Issue        : 730, 741                
+ * Expected Res : Success
+ * Date         : 8th May 2014
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.TwitterUserType as
+ closed {
+  "screen-name" : string,
+  lang : string,
+  "friends-count" : int32,
+  "statuses-count" : int32,
+  name : string,
+  "followers-count" : int32
+}
+
+create type test.TweetMessageNestedType as
+ closed {
+  tweetid : int64,
+  user : TwitterUserType,
+  "sender-location" : point,
+  "send-time" : datetime,
+  "referred-topics" : {{string}},
+  "message-text" : string,
+  countA : int32,
+  countB : int32
+}
+
+create type test.TweetMessageType as
+{
+  nested : TweetMessageNestedType
+}
+
+create  table TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create  index twmSndLocIx  on TweetMessages (nested."sender-location") type rtree;
+
+create  index msgCountAIx  on TweetMessages (nested.countA) type btree;
+
+create  index msgCountBIx  on TweetMessages (nested.countB) type btree;
+
+create  index msgTextIx  on TweetMessages (nested."message-text") type keyword;
+
+write output to nc1:"rttest/rtree-index-join_leftouterjoin-probe-pidx-with-join-rtree-sidx_02.adm"
+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.int64('10'))
+order by t1.nested.tweetid
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/spatial-intersect-point_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/spatial-intersect-point_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/spatial-intersect-point_01.sqlpp
new file mode 100644
index 0000000..0458c9f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/spatial-intersect-point_01.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    : Joins two datasets on the intersection of their point attributes.
+ *                  The dataset 'MyData1' has an enforced open RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.MyRecord as
+ closed {
+  id : int32,
+  point : point,
+  kwds : string,
+  line1 : line,
+  line2 : line,
+  poly1 : polygon,
+  poly2 : polygon,
+  rec : rectangle
+}
+
+create type test.MyRecordNested as
+ closed {
+  nested : MyRecord
+}
+
+create  table MyData1(MyRecordNested) primary key nested.id;
+
+create  table MyData2(MyRecord) primary key id;
+
+create  index rtree_index  on MyData1 (nested.point) type rtree;
+
+write output to nc1:"rttest/index-join_rtree-spatial-intersect-point.adm"
+select element {'a':a,'b':b}
+from  MyData1 as a,
+      MyData2 as b
+where test."spatial-intersect"(a.nested.point,b.point)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/spatial-intersect-point_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/spatial-intersect-point_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/spatial-intersect-point_02.sqlpp
new file mode 100644
index 0000000..7893c9b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/spatial-intersect-point_02.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    : Joins two datasets on the intersection of their point attributes.
+ *                  The dataset 'MyData2' has an enforced open RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.MyRecord as
+ closed {
+  id : int32,
+  point : point,
+  kwds : string,
+  line1 : line,
+  line2 : line,
+  poly1 : polygon,
+  poly2 : polygon,
+  rec : rectangle
+}
+
+create type test.MyRecordNested as
+ closed {
+  nested : MyRecord
+}
+
+create  table MyData1(MyRecordNested) primary key nested.id;
+
+create  table MyData2(MyRecord) primary key id;
+
+create  index rtree_index  on MyData2 (point) type rtree;
+
+write output to nc1:"rttest/rtree-index-join_spatial-intersect-point_02.adm"
+select element {'a':a,'b':b}
+from  MyData1 as a,
+      MyData2 as b
+where test."spatial-intersect"(a.nested.point,b.point)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/spatial-intersect-point_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/spatial-intersect-point_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/spatial-intersect-point_03.sqlpp
new file mode 100644
index 0000000..35c6196
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-index/rtree-index-join/spatial-intersect-point_03.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    : Self-joins a dataset on the intersection of its point attribute.
+ *                  The dataset has an enforced open RTree index, and we expect the
+ *                  join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.MyRecord as
+ closed {
+  id : int32,
+  point : point,
+  kwds : string,
+  line1 : line,
+  line2 : line,
+  poly1 : polygon,
+  poly2 : polygon,
+  rec : rectangle
+}
+
+create type test.MyRecordNested as
+ closed {
+  nested : MyRecord
+}
+
+create  table MyData(MyRecordNested) primary key nested.id;
+
+create  index rtree_index  on MyData (nested.point) type rtree;
+
+write output to nc1:"rttest/rtree-index-join_spatial-intersect-point_03.adm"
+select element {'a':a,'b':b}
+from  MyData as a,
+      MyData as b
+where test."spatial-intersect"(a.nested.point,b.nested.point)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/disjunction-to-join.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/disjunction-to-join.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/disjunction-to-join.sqlpp
new file mode 100644
index 0000000..12514d2
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/disjunction-to-join.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    : Disjunctive predicate should be transformed into collection scan.
+ *                  Secondary index should be used to probe the values retrieved from collection.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.NestedTestType as
+{
+
+}
+
+create type test.TestType as
+{
+  id : string,
+  "no-idx" : string,
+  nested : NestedTestType
+}
+
+create  table TestSet(TestType) primary key id;
+
+create  index TestSetIndex  on TestSet (nested.idx:string) type btree enforced;
+
+select element x
+from  TestSet as x
+where ((x.nested.idx = 'one') or (x.nested.idx = 'two'))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1.sqlpp
new file mode 100644
index 0000000..66397ac
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_1.sqlpp
@@ -0,0 +1,72 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : 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  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}},
+  "message-text" : string,
+  countA : int32
+}
+
+create type test.TweetMessageType as
+{
+  nested : TweetMessageNestedType
+}
+
+create  table TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create  index msgCountBIx  on TweetMessages (nested.countB:int32) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_01.adm"
+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.int64('10'))
+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/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2.sqlpp
new file mode 100644
index 0000000..b704758
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_01_2.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 btree 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}},
+  "message-text" : string
+}
+
+create type test.TweetMessageType as
+{
+  nested : TweetMessageNestedType
+}
+
+create  table TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create  index msgCountAIx  on TweetMessages (nested.countA:int32) type btree enforced;
+
+create  index msgCountBIx  on TweetMessages (nested.countB:int32) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_01.adm"
+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.int64('10'))
+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/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1.sqlpp
new file mode 100644
index 0000000..32fb70c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_1.sqlpp
@@ -0,0 +1,72 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : 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  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}},
+  "message-text" : string,
+  countA : int32
+}
+
+create type test.TweetMessageType as
+{
+  nested : TweetMessageNestedType
+}
+
+create  table TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create  index msgCountBIx  on TweetMessages (nested.countB:int32) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_02.adm"
+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.int64('10'))
+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/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2.sqlpp
new file mode 100644
index 0000000..53bcc3b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/leftouterjoin-probe-pidx-with-join-btree-sidx_02_2.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 btree 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}},
+  "message-text" : string
+}
+
+create type test.TweetMessageType as
+{
+  nested : TweetMessageNestedType
+}
+
+create  table TweetMessages(TweetMessageType) primary key nested.tweetid;
+
+create  index msgCountAIx  on TweetMessages (nested.countA:int32) type btree enforced;
+
+create  index msgCountBIx  on TweetMessages (nested.countB:int32) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_leftouterjoin-probe-pidx-with-join-btree-sidx_02.adm"
+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.int64('10'))
+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/btree-index-join/secondary-composite-key-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-join_01.sqlpp
new file mode 100644
index 0000000..d006faf
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-join_01.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  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+  id : int32
+}
+
+create type test.NameType as
+{
+  nested : Nametmp
+}
+
+create  table Names(NameType) primary key nested.id;
+
+create  index Name_idx  on Names (nested.fname:string,lnested.name:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-composite-key-prefix-join_01.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from  Names as emp1,
+      Names as emp2
+where ((emp1.nested.fname /*+ indexnl */  > emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */  > emp2.nested.lname))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-join_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-join_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-join_02.sqlpp
new file mode 100644
index 0000000..aa673af
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-join_02.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  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+  id : int32
+}
+
+create type test.NameType as
+{
+  nested : Nametmp
+}
+
+create  table Names(NameType) primary key nested.id;
+
+create  index Name_idx  on Names (nested.fname:string,lnested.name:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-composite-key-prefix-join_02.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from  Names as emp1,
+      Names as emp2
+where ((emp1.nested.fname /*+ indexnl */  < emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */  < emp2.nested.lname))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-join_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-join_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-join_03.sqlpp
new file mode 100644
index 0000000..ac00e9c
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-join_03.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  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+  id : int32
+}
+
+create type test.NameType as
+{
+  nested : Nametmp
+}
+
+create  table Names(NameType) primary key nested.id;
+
+create  index Name_idx  on Names (nested.fname:string,lnested.name:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-composite-key-prefix-join_03.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from  Names as emp1,
+      Names as emp2
+where ((emp1.nested.fname /*+ indexnl */  = emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */  = emp2.nested.lname))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_01.sqlpp
new file mode 100644
index 0000000..41e6642
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_01.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  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+  id : int32
+}
+
+create type test.NameType as
+{
+  nested : Nametmp
+}
+
+create  table Names(NameType) primary key nested.id;
+
+create  index Name_idx  on Names (nested.fname:string,lnested.name:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-composite-key-prefix-prefix-join_01.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from  Names as emp1,
+      Names as emp2
+where ((emp1.nested.fname /*+ indexnl */  < emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */  > emp2.nested.lname))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_02.sqlpp
new file mode 100644
index 0000000..9dd20ba
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_02.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  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+  id : int32
+}
+
+create type test.NameType as
+{
+  nested : Nametmp
+}
+
+create  table Names(NameType) primary key nested.id;
+
+create  index Name_idx  on Names (nested.fname:string,lnested.name:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-composite-key-prefix-prefix-join_02.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from  Names as emp1,
+      Names as emp2
+where ((emp1.nested.fname /*+ indexnl */  > emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */  < emp2.nested.lname))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_03.sqlpp
new file mode 100644
index 0000000..8d384b4
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_03.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  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+  id : int32
+}
+
+create type test.NameType as
+{
+  nested : Nametmp
+}
+
+create  table Names(NameType) primary key nested.id;
+
+create  index Name_idx  on Names (nested.fname:string,lnested.name:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-composite-key-prefix-prefix-join_03.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from  Names as emp1,
+      Names as emp2
+where ((emp1.nested.fname /*+ indexnl */  > emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */  = emp2.nested.lname))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_04.sqlpp
new file mode 100644
index 0000000..68e4f37
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_04.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  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+  id : int32
+}
+
+create type test.NameType as
+{
+  nested : Nametmp
+}
+
+create  table Names(NameType) primary key nested.id;
+
+create  index Name_idx  on Names (nested.fname:string,lnested.name:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-composite-key-prefix-prefix-join_04.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from  Names as emp1,
+      Names as emp2
+where ((emp1.nested.fname /*+ indexnl */  < emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */  = emp2.nested.lname))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_05.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_05.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_05.sqlpp
new file mode 100644
index 0000000..f551416
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_05.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  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+  id : int32
+}
+
+create type test.NameType as
+{
+  nested : Nametmp
+}
+
+create  table Names(NameType) primary key nested.id;
+
+create  index Name_idx  on Names (nested.fname:string,lnested.name:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-composite-key-prefix-prefix-join_05.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from  Names as emp1,
+      Names as emp2
+where ((emp1.nested.fname /*+ indexnl */  = emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */  > emp2.nested.lname))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_06.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_06.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_06.sqlpp
new file mode 100644
index 0000000..03fc651
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-composite-key-prefix-join_06.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  : Notice the query hint to use an indexed nested-loops join plan in both predicates.
+ *              : We expect a plan to have a self-join, which probes dataset Names’s with a prefix of its secondary index.
+ * Expected Res : Success
+ * Date         : 11th November 2014
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.Nametmp as
+{
+  id : int32
+}
+
+create type test.NameType as
+{
+  nested : Nametmp
+}
+
+create  table Names(NameType) primary key nested.id;
+
+create  index Name_idx  on Names (nested.fname:string,lnested.name:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_secondary-composite-key-prefix-prefix-join_06.adm"
+select element {'emp1':emp1,'emp2':emp2}
+from  Names as emp1,
+      Names as emp2
+where ((emp1.nested.fname /*+ indexnl */  = emp2.nested.fname) and (emp1.nested.lname /*+ indexnl */  < emp2.nested.lname))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join-multiindex.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join-multiindex.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join-multiindex.sqlpp
new file mode 100644
index 0000000..4a2f0b5
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join-multiindex.sqlpp
@@ -0,0 +1,84 @@
+/*
+ * 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    : Equi joins two datasets, FacebookUsers and FacebookMessages, based on their user's id.
+ *                  We first expect FacebookUsers' primary index to be used
+ *                  to satisfy the range condition on it's primary key.
+ *                  FacebookMessages has a secondary btree index on author-id-copy, and given the 'indexnl' hint
+ *                  we expect the join to be transformed into an indexed nested-loop join.
+ * Success        : Yes
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+create type test.EmploymentType as
+ closed {
+  "organization-name" : string,
+  "start-date" : date,
+  "end-date" : date?
+}
+
+create type test.FacebookUserTypetmp as
+ closed {
+  id : int32,
+  "id-copy" : int32,
+  alias : string,
+  name : string,
+  "user-since" : datetime,
+  "user-since-copy" : datetime,
+  "friend-ids" : {{int32}},
+  employment : [EmploymentType]
+}
+
+create type test.FacebookMessageTypetmp as
+{
+  "message-id" : int32,
+  "message-id-copy" : int32,
+  "author-id" : int32,
+  "in-response-to" : int32?,
+  "sender-location" : point?,
+  message : string
+}
+
+create type test.FacebookUserType as
+ closed {
+  nested : FacebookUserTypetmp
+}
+
+create type test.FacebookMessageType as
+ closed {
+  nested : FacebookMessageTypetmp
+}
+
+create  table FacebookUsers(FacebookUserType) primary key nested.id;
+
+create  table FacebookMessages(FacebookMessageType) primary key nested."message-id";
+
+create  index fbmIdxAutId  if not exists  on FacebookMessages (nested."author-id-copy":int32) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join-multiindex.adm"
+select element {'fbu-ID':user.nested.id,'fbm-auth-ID':message.nested."author-id",'uname':user.nested.name,'message':message.nested.message}
+from  FacebookUsers as user,
+      FacebookMessages as message
+where ((user.nested.id /*+ indexnl */  = message.nested."author-id-copy") and (user.nested.id >= 11000) and (user.nested.id <= 12000))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join-multipred.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join-multipred.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join-multipred.sqlpp
new file mode 100644
index 0000000..b2f3fa3
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join-multipred.sqlpp
@@ -0,0 +1,72 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Equi joins two datasets, DBLP and CSX, based on their title.
+ *                  DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ *                  we expect the join to be transformed into an indexed nested-loop join.
+ *                  We expect the additional predicates to be put into a select above the
+ *                  primary index search.
+ * Success        : Yes
+ */
+
+drop  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.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 title_index  on DBLP (nested.title:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join-multipred.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where ((a.nested.title /*+ indexnl */  = b.nested.title) and (a.nested.authors < b.nested.authors) and (a.nested.misc > b.nested.misc))
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_01.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_01.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_01.sqlpp
new file mode 100644
index 0000000..5ca59ca
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_01.sqlpp
@@ -0,0 +1,70 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Equi joins two datasets, DBLP and CSX, based on their title.
+ *                  DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ *                  we expect the join to be transformed into an indexed nested-loop join.
+ * 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.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 title_index  on DBLP (nested.title:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join_01.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where (a.nested.title /*+ indexnl */  = b.nested.title)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_02.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_02.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_02.sqlpp
new file mode 100644
index 0000000..e6b59f1
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_02.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    : Equi joins two datasets, closed DBLP and open CSX, based on their title.
+ *                  DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ *                  we expect the join to be transformed into an indexed nested-loop join.
+ * 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,
+  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 title_index  on CSX (nested.title:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join_02.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where (a.nested.title /*+ indexnl */  = b.nested.title)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_03.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_03.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_03.sqlpp
new file mode 100644
index 0000000..4ebdfc7
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_03.sqlpp
@@ -0,0 +1,71 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description    : Equi joins two open datasets, DBLP and CSX, based on their title.
+ *                  DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ *                  we expect the join to be transformed into an indexed nested-loop join.
+ * 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.CSXTypetmp as
+{
+  id : int32,
+  csxid : 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 title_index_DBLP  on DBLP (nested.title:string) type btree enforced;
+
+create  index title_index_CSX  on CSX (nested.title:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join_03.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where (a.nested.title /*+ indexnl */  = b.nested.title)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_04.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_04.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_04.sqlpp
new file mode 100644
index 0000000..6877a7f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_04.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    : Self-joins dataset DBLP, based on it's title.
+ *                  DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ *                  we expect the join to be transformed into an indexed nested-loop join.
+ * 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
+{
+  nested : DBLPTypetmp
+}
+
+create  table DBLP(DBLPType) primary key nested.id;
+
+create  index title_index  on DBLP (nested.title:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join_04.adm"
+select element {'arec':a,'arec2':a2}
+from  DBLP as a,
+      DBLP as a2
+where (a.nested.title /*+ indexnl */  = a2.nested.title)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_05.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_05.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_05.sqlpp
new file mode 100644
index 0000000..1dc52af
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index-join/secondary-equi-join_05.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    : Equi joins two open datasets, open DBLP and closed CSX, based on their title.
+ *                  DBLP has a secondary btree index on title, and given the 'indexnl' hint
+ *                  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,
+  authors : string,
+  misc : string
+}
+
+create type test.CSXTypetmp as
+{
+  id : int32,
+  csxid : 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 title_index  on DBLP (nested.title:string) type btree enforced;
+
+write output to nc1:"rttest/btree-index-join_title-secondary-equi-join_05.adm"
+select element {'arec':a,'brec':b}
+from  DBLP as a,
+      CSX as b
+where (a.nested.title /*+ indexnl */  = b.nested.title)
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-33.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-33.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-33.sqlpp
new file mode 100644
index 0000000..66a680f
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-33.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     : BTree Index verification test
+ *                  : This test is intended to verify that the secondary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-primary-39.adm"
+create type test.TestTypetmp as
+{
+  id : int32
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.id;
+
+create  index sec_Idx  on testdst (nested.fname:string,nested.lname:string) type btree enforced;
+
+select element emp
+from  testdst as emp
+where (emp.nested.fname > 'Roger')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-34.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-34.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-34.sqlpp
new file mode 100644
index 0000000..2e8b979
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-34.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     : BTree Index verification test
+ *                  : This test is intended to verify that the secondary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-secondary-32.adm"
+create type test.TestTypetmp as
+{
+  id : int32
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.id;
+
+create  index sec_Idx  on testdst (nested.fname:string,nested.lname:string) type btree enforced;
+
+select element emp
+from  testdst as emp
+where (emp.nested.fname >= 'Susan')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-35.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-35.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-35.sqlpp
new file mode 100644
index 0000000..6a637cf
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-35.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     : BTree Index verification (usage) test
+ *                  : This test is intended to verify that the secondary BTree index is used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-secondary-33.adm"
+create type test.TestTypetmp as
+{
+  id : int32
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.id;
+
+create  index sec_Idx  on testdst (nested.fname:string,nested.lname:string) type btree enforced;
+
+select element emp
+from  testdst as emp
+where (emp.nested.fname < 'Isa')
+;

http://git-wip-us.apache.org/repos/asf/incubator-asterixdb/blob/391f09e5/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-36.sqlpp
----------------------------------------------------------------------
diff --git a/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-36.sqlpp b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-36.sqlpp
new file mode 100644
index 0000000..6843fc8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries_sqlpp/nested-open-index/btree-index/btree-secondary-36.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     : BTree Index verification test
+ *                  : This test is intended to verify that the secondary BTree index is NOT used
+ *                  : in the optimized query plan.
+ *  Expected Result : Success
+ *  Date            : 13th Aug 2012
+ */
+
+drop  database test if exists;
+create  database test;
+
+use test;
+
+
+write output to nc1:"rttest/btree-index_btree-secondary-34.adm"
+create type test.TestTypetmp as
+{
+  id : int32
+}
+
+create type test.TestType as
+{
+  nested : TestTypetmp
+}
+
+create  table testdst(TestType) primary key nested.id;
+
+create  index sec_Idx  on testdst (nested.fname:string,nested.lname:string) type btree enforced;
+
+select element emp
+from  testdst as emp
+where (emp.nested.fname <= 'Vanpatten')
+;


Mime
View raw message