asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From wangs...@apache.org
Subject [05/16] asterixdb git commit: [ASTERIXDB-1972][COMP][RT][TX] index-only plan
Date Fri, 16 Feb 2018 19:04:11 GMT
http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.2.update.sqlpp
new file mode 100644
index 0000000..9116663
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.2.update.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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.
+ */
+
+use test;
+
+load dataset MyData1
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) pre-sorted;
+
+load dataset MyData2
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData.json"),("format"="adm")) pre-sorted;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.3.ddl.sqlpp
new file mode 100644
index 0000000..d283b1c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.3.ddl.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+use test;
+
+create index rtree_index1 on MyData1(poly1) type rtree;
+create index rtree_index2 on MyData2(poly2) type rtree;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.4.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.4.query.sqlpp
new file mode 100644
index 0000000..3f8f9d6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/rtree-spatial-intersect-point_05/rtree-spatial-intersect-point_05.4.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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.
+ */
+
+use test;
+
+select element {'aid':a.id,'bid':b.id,'apoly1':a.poly1,'bpoly2':b.poly2}
+from  MyData1 as a,
+      MyData2 as b
+where test.`spatial-intersect`(a.poly1,b.poly2)
+order by a.id,b.id
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.1.ddl.sqlpp
new file mode 100644
index 0000000..4920706
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.1.ddl.sqlpp
@@ -0,0 +1,56 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Description  : Test that left-outer-join may use two available indexes, one for primary index in prob subtree and
+ *                another for secondary index in index subtree. For inner branch, this is an index-only plan.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type TwitterUserType as closed {
+    `screen-name`: string,
+    lang: string,
+    `friends-count`: int64,
+    `statuses-count`: int64,
+    name: string,
+    `followers-count`: int64
+};
+
+create type TweetMessageType as closed {
+    tweetid: int64,
+    user: TwitterUserType,
+    `sender-location`: point,
+    `send-time`: datetime,
+    `referred-topics`: {{ string }},
+    `message-text`: string,
+    countA: int64,
+    countB: int64
+};
+
+create dataset TweetMessages(TweetMessageType)
+primary key tweetid;
+
+create index twmSndLocIx on TweetMessages(`sender-location`) type rtree;
+create index msgCountAIx on TweetMessages(countA) type btree;
+create index msgCountBIx on TweetMessages(countB) type btree;
+create index msgTextIx on TweetMessages(`message-text`) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.2.update.sqlpp
new file mode 100644
index 0000000..cb2a156
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.2.update.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+use test;
+
+load dataset TweetMessages
+using localfs
+(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.3.query.sqlpp
new file mode 100644
index 0000000..65d953c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-pidx-with-join-btree-sidx3-indexonly-plan/probe-pidx-with-join-btree-sidx3-indexonly-plan.3.query.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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.
+ */
+
+use test;
+
+select value count(*) from (
+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
+    )}
+from  TweetMessages as t1
+where t1.tweetid < test.bigint('10')) a;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.1.ddl.sqlpp
new file mode 100644
index 0000000..adffdfd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.1.ddl.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  : Test that left-outer-join may use an available primary index in the index subtree.
+ *                In the probe side, this is an index-only plan since a secondary index-search
+ *                will fetch all fields that are required for the plan.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type TwitterUserType as closed {
+    `screen-name`: string,
+    lang: string,
+    `friends-count`: int64,
+    `statuses-count`: int64,
+    name: string,
+    `followers-count`: int64
+};
+
+create type TweetMessageType as closed {
+    tweetid: int64,
+    user: TwitterUserType,
+    `sender-location`: point,
+    `send-time`: datetime,
+    `referred-topics`: {{ string }},
+    `message-text`: string,
+    countA: int64,
+    countB: int64
+};
+
+create dataset TweetMessages(TweetMessageType)
+primary key tweetid;
+
+create index twmSndLocIx on TweetMessages(`sender-location`) type rtree;
+create index msgCountAIx on TweetMessages(countA) type btree;
+create index msgCountBIx on TweetMessages(countB) type btree;
+create index msgTextIx on TweetMessages(`message-text`) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.2.update.sqlpp
new file mode 100644
index 0000000..cb2a156
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.2.update.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+use test;
+
+load dataset TweetMessages
+using localfs
+(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.3.query.sqlpp
new file mode 100644
index 0000000..7d0f895
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-indexonly-plan-with-join-btree-pidx1.3.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * 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.
+ */
+
+use test;
+
+
+select value count(*) from (
+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.tweetid
+    )}
+from  TweetMessages as t1
+where t1.countA > 0) a;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.sqlpp
new file mode 100644
index 0000000..c7685a8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.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  : Test that left-outer-join may use an available secondary index in the index subtree.
+ *                In the probe side, this is an index-only plan since a secondary index-search will
+ *                fetch all fields that are required for the plan.
+ *                In the inner branch, this is also an index-only plan since a secondary index-search will
+ *                fetch all fields that are required for the branch.
+ *                All other variables will be propagated from the outer branch.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type TwitterUserType as closed {
+    `screen-name`: string,
+    lang: string,
+    `friends-count`: int64,
+    `statuses-count`: int64,
+    name: string,
+    `followers-count`: int64
+};
+
+create type TweetMessageType as closed {
+    tweetid: int64,
+    user: TwitterUserType,
+    `sender-location`: point,
+    `send-time`: datetime,
+    `referred-topics`: {{ string }},
+    `message-text`: string,
+    countA: int64,
+    countB: int64
+};
+
+create dataset TweetMessages(TweetMessageType)
+primary key tweetid;
+
+create index twmSndLocIx on TweetMessages(`sender-location`) type rtree;
+create index msgCountAIx on TweetMessages(countA) type btree;
+create index msgCountBIx on TweetMessages(countB) type btree;
+create index msgTextIx on TweetMessages(`message-text`) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.sqlpp
new file mode 100644
index 0000000..cb2a156
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+use test;
+
+load dataset TweetMessages
+using localfs
+(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.sqlpp
new file mode 100644
index 0000000..6e2e769
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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.
+ */
+
+use test;
+
+select value count(*) from (
+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
+    )}
+from  TweetMessages as t1
+where t1.countA > 0) a;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.1.ddl.sqlpp
new file mode 100644
index 0000000..3e85829
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.1.ddl.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  : Test that left-outer-join may use an available primary index in the index subtree.
+ *                In the probe side, this is a non index-only plan since a secondary index-search can't cover
+ *                all fields that are required for the plan.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type TwitterUserType as closed {
+    `screen-name`: string,
+    lang: string,
+    `friends-count`: int64,
+    `statuses-count`: int64,
+    name: string,
+    `followers-count`: int64
+};
+
+create type TweetMessageType as closed {
+    tweetid: int64,
+    user: TwitterUserType,
+    `sender-location`: point,
+    `send-time`: datetime,
+    `referred-topics`: {{ string }},
+    `message-text`: string,
+    countA: int64,
+    countB: int64
+};
+
+create dataset TweetMessages(TweetMessageType)
+primary key tweetid;
+
+create index twmSndLocIx on TweetMessages(`sender-location`) type rtree;
+create index msgCountAIx on TweetMessages(countA) type btree;
+create index msgCountBIx on TweetMessages(countB) type btree;
+create index msgTextIx on TweetMessages(`message-text`) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.2.update.sqlpp
new file mode 100644
index 0000000..cb2a156
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.2.update.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+use test;
+
+load dataset TweetMessages
+using localfs
+(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.3.query.sqlpp
new file mode 100644
index 0000000..312b329
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1/probe-sidx-btree-non-indexonly-plan-with-join-btree-pidx1.3.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * 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.
+ */
+
+use test;
+
+
+select value count(*) from (
+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.tweetid
+    )}
+from  TweetMessages as t1
+where t1.countA > 0 and t1.countB < 10000) a;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.sqlpp
new file mode 100644
index 0000000..3d8f3e3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.1.ddl.sqlpp
@@ -0,0 +1,59 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Description  : Test that left-outer-join may use an available secondary index in the index subtree.
+ *                In the probe side, this is a non index-only plan since a secondary index-search can't cover
+ *                all fields that are required for the plan.
+ *                In the inner branch, this is a index-only plan since a secondary index-search can cover
+ *                all fields that are required for that branch.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type TwitterUserType as closed {
+    `screen-name`: string,
+    lang: string,
+    `friends-count`: int64,
+    `statuses-count`: int64,
+    name: string,
+    `followers-count`: int64
+};
+
+create type TweetMessageType as closed {
+    tweetid: int64,
+    user: TwitterUserType,
+    `sender-location`: point,
+    `send-time`: datetime,
+    `referred-topics`: {{ string }},
+    `message-text`: string,
+    countA: int64,
+    countB: int64
+};
+
+create dataset TweetMessages(TweetMessageType)
+primary key tweetid;
+
+create index twmSndLocIx on TweetMessages(`sender-location`) type rtree;
+create index msgCountAIx on TweetMessages(countA) type btree;
+create index msgCountBIx on TweetMessages(countB) type btree;
+create index msgTextIx on TweetMessages(`message-text`) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.sqlpp
new file mode 100644
index 0000000..cb2a156
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.2.update.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+
+use test;
+
+load dataset TweetMessages
+using localfs
+(("path"="asterix_nc1://data/twitter/tw_for_indexleftouterjoin.adm"),("format"="adm"));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.sqlpp
new file mode 100644
index 0000000..2cae1d6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan/probe-sidx-btree-non-indexonly-plan-with-join-btree-sidx1-indexonly-plan.3.query.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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.
+ */
+
+use test;
+
+select value count(*) from (
+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
+    )}
+from  TweetMessages as t1
+where t1.countA > 0 and t1.countB < 10000) a;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.1.ddl.sqlpp
new file mode 100644
index 0000000..57e8d36
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.1.ddl.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     : Secondary BTree Index index-only selection plan verification test
+ *                  : This test is intended to verify that the secondary BTree index is
+ *                  : used in the optimized query plan.
+ *                  : In this plan, we fetch PK and SK based on a select condition that utilizes a secondary index.
+ *                  : The plan should have two paths after the secondary index-lookup.
+ *                  : The left path:
+ *                      ... -> unnest-map (sidx) -> split -> unnest-map (pidx) -> select -> union -> ...
+ *                  : The right path:
+ *                      ... -> unnest-map (sidx) -> split ->                                union -> ...
+ *  Expected Result : Success
+ *
+*/
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type MyRecord as closed {
+  id: int64,
+  docid: int64,
+  val1: int64,
+  title: string,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle,
+  circle: circle
+};
+
+create dataset MyData(MyRecord)
+  primary key id;
+
+create index btree_index_docid_val1 on MyData(docid,val1) type btree;
+create index rtree_index_point on MyData(point) type rtree;
+create index rtree_index_rec on MyData(rec) type rtree;
+create index ngram_index_title on MyData(title) type ngram(3);
+create index keyword_index_title on MyData(title) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.2.update.sqlpp
new file mode 100644
index 0000000..bc705b2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.2.update.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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.
+ */
+
+use test;
+
+load dataset MyData
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData2.json"),("format"="adm"));
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.3.query.sqlpp
new file mode 100644
index 0000000..ee9242c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-01/btree-secondary-composite-index-indexonly-plan-01.3.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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.
+ */
+
+use test;
+
+select element {"pk":o.id, "sk":o.val1}
+from MyData o
+where o.docid < 3
+order by o.id;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-02/btree-secondary-composite-index-indexonly-plan-02.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-02/btree-secondary-composite-index-indexonly-plan-02.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-02/btree-secondary-composite-index-indexonly-plan-02.1.ddl.sqlpp
new file mode 100644
index 0000000..57e8d36
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-02/btree-secondary-composite-index-indexonly-plan-02.1.ddl.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     : Secondary BTree Index index-only selection plan verification test
+ *                  : This test is intended to verify that the secondary BTree index is
+ *                  : used in the optimized query plan.
+ *                  : In this plan, we fetch PK and SK based on a select condition that utilizes a secondary index.
+ *                  : The plan should have two paths after the secondary index-lookup.
+ *                  : The left path:
+ *                      ... -> unnest-map (sidx) -> split -> unnest-map (pidx) -> select -> union -> ...
+ *                  : The right path:
+ *                      ... -> unnest-map (sidx) -> split ->                                union -> ...
+ *  Expected Result : Success
+ *
+*/
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type MyRecord as closed {
+  id: int64,
+  docid: int64,
+  val1: int64,
+  title: string,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle,
+  circle: circle
+};
+
+create dataset MyData(MyRecord)
+  primary key id;
+
+create index btree_index_docid_val1 on MyData(docid,val1) type btree;
+create index rtree_index_point on MyData(point) type rtree;
+create index rtree_index_rec on MyData(rec) type rtree;
+create index ngram_index_title on MyData(title) type ngram(3);
+create index keyword_index_title on MyData(title) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-02/btree-secondary-composite-index-indexonly-plan-02.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-02/btree-secondary-composite-index-indexonly-plan-02.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-02/btree-secondary-composite-index-indexonly-plan-02.2.update.sqlpp
new file mode 100644
index 0000000..867c708
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-02/btree-secondary-composite-index-indexonly-plan-02.2.update.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+use test;
+
+load dataset MyData
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData2.json"),("format"="adm"));
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-02/btree-secondary-composite-index-indexonly-plan-02.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-02/btree-secondary-composite-index-indexonly-plan-02.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-02/btree-secondary-composite-index-indexonly-plan-02.3.query.sqlpp
new file mode 100644
index 0000000..37c54e3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-02/btree-secondary-composite-index-indexonly-plan-02.3.query.sqlpp
@@ -0,0 +1,24 @@
+/*
+ * 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.
+ */
+use test;
+
+select element {"pk":o.id, "sk":o.docid}
+from MyData o
+where o.docid < 3
+order by o.id;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-03/btree-secondary-composite-index-indexonly-plan-03.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-03/btree-secondary-composite-index-indexonly-plan-03.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-03/btree-secondary-composite-index-indexonly-plan-03.1.ddl.sqlpp
new file mode 100644
index 0000000..4530d8d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-03/btree-secondary-composite-index-indexonly-plan-03.1.ddl.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     : Secondary BTree Index index-only selection plan verification test
+ *                  : This test is intended to verify that the secondary BTree index is
+ *                  : used in the optimized query plan.
+ *                  : In this plan, we fetch PK and SK based on a select condition that utilizes a secondary index.
+ *                  : The plan should have two paths after the secondary index-lookup.
+ *                  : The left path:
+ *                      ... -> unnest-map (sidx) -> split -> unnest-map (pidx) -> select   -> union -> ...
+ *                  : The right path:
+ *                      ... -> unnest-map (sidx) -> split -> select (the second condition) -> union -> ...
+ *  Expected Result : Success
+ *
+*/
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type MyRecord as closed {
+  id: int64,
+  docid: int64,
+  val1: int64,
+  title: string,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle,
+  circle: circle
+};
+
+create dataset MyData(MyRecord)
+  primary key id;
+
+create index btree_index_docid_val1 on MyData(docid,val1) type btree;
+create index rtree_index_point on MyData(point) type rtree;
+create index rtree_index_rec on MyData(rec) type rtree;
+create index ngram_index_title on MyData(title) type ngram(3);
+create index keyword_index_title on MyData(title) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-03/btree-secondary-composite-index-indexonly-plan-03.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-03/btree-secondary-composite-index-indexonly-plan-03.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-03/btree-secondary-composite-index-indexonly-plan-03.2.update.sqlpp
new file mode 100644
index 0000000..bc705b2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-03/btree-secondary-composite-index-indexonly-plan-03.2.update.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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.
+ */
+
+use test;
+
+load dataset MyData
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData2.json"),("format"="adm"));
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-03/btree-secondary-composite-index-indexonly-plan-03.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-03/btree-secondary-composite-index-indexonly-plan-03.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-03/btree-secondary-composite-index-indexonly-plan-03.3.query.sqlpp
new file mode 100644
index 0000000..73c7632
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-composite-index-indexonly-plan-03/btree-secondary-composite-index-indexonly-plan-03.3.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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.
+ */
+
+use test;
+
+select element {"pk":o.id, "sk":o.docid, "sk2":o.val1}
+from MyData o
+where o.docid < 3 and o.val1 >= 3
+order by o.id;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.1.ddl.sqlpp
new file mode 100644
index 0000000..c0e76e7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.1.ddl.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     : Secondary BTree Index index-only selection plan verification test
+ *                  : The test is intended to verify that the secondary BTree index is used in the optimized query plan.
+ *                  : In this plan, we fetch PK and SK based on a select condition that utilizes a secondary index.
+ *                  : The plan should have two paths after the secondary index-lookup.
+ *                  : The left path:
+ *                      ... -> unnest-map (sidx) -> split -> unnest-map (pidx) -> select -> union -> ...
+ *                  : The right path:
+ *                      ... -> unnest-map (sidx) -> split ->                             -> union -> ...
+ *  Expected Result : Success
+ *
+*/
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type MyRecord as closed {
+  id: int64,
+  docid: int64,
+  val1: int64,
+  title: string,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle,
+  circle: circle
+};
+
+create dataset MyData(MyRecord)
+  primary key id;
+
+create index btree_index_docid on MyData(docid) type btree;
+create index btree_index_val1 on MyData(val1) type btree;
+create index rtree_index_point on MyData(point) type rtree;
+create index rtree_index_rec on MyData(rec) type rtree;
+create index ngram_index_title on MyData(title) type ngram(3);
+create index keyword_index_title on MyData(title) type keyword;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.10.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.10.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.10.query.sqlpp
new file mode 100644
index 0000000..1094ca1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.10.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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.
+ */
+
+use test;
+
+select value count(*) from (
+select o
+from MyData o
+where o.docid < 3
+order by o.id) a;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.2.update.sqlpp
new file mode 100644
index 0000000..bc705b2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.2.update.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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.
+ */
+
+use test;
+
+load dataset MyData
+using localfs
+(("path"="asterix_nc1://data/spatial/spatialData2.json"),("format"="adm"));
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.3.query.sqlpp
new file mode 100644
index 0000000..a4311fe
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.3.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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.
+ */
+
+use test;
+
+select element {"pk":o.id, "sk":o.docid}
+from MyData o
+where o.docid < 3
+order by o.id;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.4.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.4.query.sqlpp
new file mode 100644
index 0000000..37c1d49
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.4.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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.
+ */
+
+use test;
+
+select element {"pk":o.id}
+from MyData o
+where o.docid < 3
+order by o.id;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.5.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.5.query.sqlpp
new file mode 100644
index 0000000..46aca8b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.5.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * 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.
+ */
+
+use test;
+
+select element {"sk":o.docid}
+from MyData o
+where o.docid < 3
+order by o.id;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.6.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.6.query.sqlpp
new file mode 100644
index 0000000..226d9d9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.6.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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.
+ */
+
+use test;
+
+select value count(*) from (
+select element {"pk":o.id, "sk":o.docid}
+from MyData o
+where o.docid < 3
+order by o.id) a;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.7.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.7.query.sqlpp
new file mode 100644
index 0000000..c97dc08
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.7.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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.
+ */
+
+use test;
+
+select value count(*) from (
+select element {"pk":o.id}
+from MyData o
+where o.docid < 3
+order by o.id) a;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.8.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.8.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.8.query.sqlpp
new file mode 100644
index 0000000..b7676d8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.8.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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.
+ */
+
+use test;
+
+select value count(*) from (
+select element {"sk":o.docid}
+from MyData o
+where o.docid < 3
+order by o.id) a;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.9.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.9.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.9.query.sqlpp
new file mode 100644
index 0000000..2db329a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-indexonly-plan-01/btree-secondary-index-indexonly-plan-01.9.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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.
+ */
+
+use test;
+
+select value count(*) from (
+select element {"rec":o, "pk":o.id}
+from MyData o
+where o.docid < 3
+order by o.id) a;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/c3c23574/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-non-indexonly-plan-01/btree-secondary-index-non-indexonly-plan-01.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-non-indexonly-plan-01/btree-secondary-index-non-indexonly-plan-01.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-non-indexonly-plan-01/btree-secondary-index-non-indexonly-plan-01.1.ddl.sqlpp
new file mode 100644
index 0000000..c2251fd
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-secondary-index-non-indexonly-plan-01/btree-secondary-index-non-indexonly-plan-01.1.ddl.sqlpp
@@ -0,0 +1,56 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ *  Description     : Secondary BTree Index index-only selection plan verification test
+ *                  : The test is intended to verify that the secondary BTree index is used in the optimized query plan.
+ *                  : In this plan, we have multiple conditions that one index can cover.
+ *                  : Thus, index-only plan is not possible.
+ *  Expected Result : Success
+ *
+*/
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create type MyRecord as closed {
+  id: int64,
+  docid: int64,
+  val1: int64,
+  title: string,
+  point: point,
+  kwds: string,
+  line1: line,
+  line2: line,
+  poly1: polygon,
+  poly2: polygon,
+  rec: rectangle,
+  circle: circle
+};
+
+create dataset MyData(MyRecord)
+  primary key id;
+
+create index btree_index_docid on MyData(docid) type btree;
+create index btree_index_val1 on MyData(val1) type btree;
+create index rtree_index_point on MyData(point) type rtree;
+create index rtree_index_rec on MyData(rec) type rtree;
+create index ngram_index_title on MyData(title) type ngram(3);
+create index keyword_index_title on MyData(title) type keyword;


Mime
View raw message