commit 8351d25314175b759b8f02b5246e5b472aa8336a
author Ali Alsuliman <ali.al.solaiman@gmail.com> Sun Sep 24 00:43:15 2017 -0700
committer Dmitry Lychagin <dmitry.lychagin@couchbase.com> Mon Sep 25 17:22:49 2017 -0700
tree 759ef11abc19e38d142ae616c96ec2482f127cb8
parent fd9c6020da2c02328c1db1aee5359b02c6311b1f

[ASTERIXDB-2015][IDX] Introduce Secondary Primary Index

- user model changes: yes. CREATE PRIMARY INDEX
- storage format changes: no
- interface changes: no

details:
- enable the creation of secondary primary indexes storing only PKs
- change the grammar to allow the creation of secondary primary index
- introduce a new rule to fix the outputs of the replicate operator
to match its parents in the final plan
- disallow creating an enforced index on a closed-type field
- disallow creating an index with repeating keys

Change-Id: I59725425ba7c5fe438507dc900f83eaab239d296
Reviewed-on: https://asterix-gerrit.ics.uci.edu/1916
Sonar-Qube: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Contrib: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Integration-Tests: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Reviewed-by: Dmitry Lychagin <dmitry.lychagin@couchbase.com>

asterixdb/asterix-app/src/test/resources/runtimets/only_sqlpp.xml

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/only_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/only_sqlpp.xml
index 876a10b..334dd52 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/only_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/only_sqlpp.xml
@@ -17,7 +17,7 @@
  ! specific language governing permissions and limitations
  ! under the License.
  !-->
-<test-suite xmlns="urn:xml.testframework.asterix.apache.org" ResultOffsetPath="results" QueryOffsetPath="queries_sqlpp">
+<test-suite xmlns="urn:xml.testframework.asterix.apache.org" ResultOffsetPath="results" QueryOffsetPath="queries_sqlpp" QueryFileExtension=".sqlpp">
   <test-group name="failed">
   </test-group>
 </test-suite>

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-1/create-index-1.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-1/create-index-1.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-1/create-index-1.1.ddl.sqlpp
new file mode 100644
index 0000000..94cfa7b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-1/create-index-1.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  : Create a secondary primary index using the dedicated syntax
+ * Date         : Aug 21 2017
+ * Expected Res : Success
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.LineItemType as
+ closed {
+  l_orderkey : bigint,
+  l_partkey : bigint,
+  l_suppkey : bigint,
+  l_linenumber : bigint,
+  l_quantity : double,
+  l_extendedprice : double,
+  l_discount : double,
+  l_tax : double,
+  l_returnflag : string,
+  l_linestatus : string,
+  l_shipdate : string,
+  l_commitdate : string,
+  l_receiptdate : string,
+  l_shipinstruct : string,
+  l_shipmode : string,
+  l_comment : string
+};
+
+create  dataset LineItem(LineItemType) primary key l_orderkey;
+
+create  primary index sec_primary_idx1  on LineItem type btree;
+create  primary index sec_primary_idx2  on LineItem;
+create  primary index sec_primary_idx2 if not exists  on LineItem;
+create  primary index on LineItem;
+create  primary index if not exists on LineItem;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-2/create-index-2.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-2/create-index-2.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-2/create-index-2.1.ddl.sqlpp
new file mode 100644
index 0000000..aa57850
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-2/create-index-2.1.ddl.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : Create a secondary primary index using the dedicated syntax
+ * Date         : Aug 21 2017
+ * Expected Res : Failure
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.LineItemType as
+ closed {
+  l_orderkey : bigint,
+  l_partkey : bigint,
+  l_suppkey : bigint,
+  l_linenumber : bigint,
+  l_quantity : double,
+  l_extendedprice : double,
+  l_discount : double,
+  l_tax : double,
+  l_returnflag : string,
+  l_linestatus : string,
+  l_shipdate : string,
+  l_commitdate : string,
+  l_receiptdate : string,
+  l_shipinstruct : string,
+  l_shipmode : string,
+  l_comment : string
+};
+
+create  dataset LineItem(LineItemType) primary key l_orderkey;
+
+create  primary index sec_primary_idx1  on LineItem type rtree;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-3/create-index-3.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-3/create-index-3.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-3/create-index-3.1.ddl.sqlpp
new file mode 100644
index 0000000..cf0c952
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-3/create-index-3.1.ddl.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : Create a secondary primary index using the dedicated syntax
+ * Date         : Aug 21 2017
+ * Expected Res : Failure
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.LineItemType as
+ closed {
+  l_orderkey : bigint,
+  l_partkey : bigint,
+  l_suppkey : bigint,
+  l_linenumber : bigint,
+  l_quantity : double,
+  l_extendedprice : double,
+  l_discount : double,
+  l_tax : double,
+  l_returnflag : string,
+  l_linestatus : string,
+  l_shipdate : string,
+  l_commitdate : string,
+  l_receiptdate : string,
+  l_shipinstruct : string,
+  l_shipmode : string,
+  l_comment : string
+};
+
+create  dataset LineItem(LineItemType) primary key l_orderkey;
+
+create  primary  sec_primary_idx1  on LineItem;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-4/create-index-4.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-4/create-index-4.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-4/create-index-4.1.ddl.sqlpp
new file mode 100644
index 0000000..a591cdc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-4/create-index-4.1.ddl.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : Create a secondary primary index using the dedicated syntax
+ * Date         : Aug 21 2017
+ * Expected Res : Failure
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.LineItemType as
+ closed {
+  l_orderkey : bigint,
+  l_partkey : bigint,
+  l_suppkey : bigint,
+  l_linenumber : bigint,
+  l_quantity : double,
+  l_extendedprice : double,
+  l_discount : double,
+  l_tax : double,
+  l_returnflag : string,
+  l_linestatus : string,
+  l_shipdate : string,
+  l_commitdate : string,
+  l_receiptdate : string,
+  l_shipinstruct : string,
+  l_shipmode : string,
+  l_comment : string
+};
+
+create  dataset LineItem(LineItemType) primary key l_orderkey;
+
+create  primary index if not exists sec_primary_idx1  if not exists on LineItem;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-5/create-index-5.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-5/create-index-5.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-5/create-index-5.1.ddl.sqlpp
new file mode 100644
index 0000000..e895382
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-5/create-index-5.1.ddl.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : Create a secondary primary index using the dedicated syntax
+ * Date         : Aug 21 2017
+ * Expected Res : Failure
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.LineItemType as
+ closed {
+  l_orderkey : bigint,
+  l_partkey : bigint,
+  l_suppkey : bigint,
+  l_linenumber : bigint,
+  l_quantity : double,
+  l_extendedprice : double,
+  l_discount : double,
+  l_tax : double,
+  l_returnflag : string,
+  l_linestatus : string,
+  l_shipdate : string,
+  l_commitdate : string,
+  l_receiptdate : string,
+  l_shipinstruct : string,
+  l_shipmode : string,
+  l_comment : string
+};
+
+create  dataset LineItem(LineItemType) primary key l_orderkey;
+
+create  primary index if exists sec_primary_idx1  on LineItem;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
index d93f069..6a64fbd 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
@@ -65,6 +65,8 @@
 
 create  index secndIndx_open  on t1 (address:string?) type btree enforced;
 
+create  primary index sec_primary_idx on t1;
+
 drop index t1.rtree_index_point;
 drop index t1.rtree_index_point_open;
 drop index t1.keyWD_indx;
@@ -72,3 +74,4 @@
 drop index t1.secndIndx;
 drop index t1.nested;
 drop index t1.secndIndx_open;
+drop index t1.sec_primary_idx;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp
index 013c209..4badf84 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp
@@ -24,5 +24,8 @@
 
 create  index idx_t1_unique1  on t1 (unique1) type btree;
 
+create  primary index sec_primary_idx on t1 type btree;
+
 drop index t1.idx_t1_str1;
 drop index t1.idx_t1_unique1;
+drop index t1.sec_primary_idx;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
index 751bdeb..f62364e 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
@@ -53,3 +53,4 @@
 
 create  index part_index  on LineItem (l_partkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
index 1bff150..dfbfb63 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
@@ -43,3 +43,4 @@
 
 create  index idx_employee_first_name  on test.employee (fname) type btree;
 
+create  primary index sec_primary_idx  on test.employee;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp
index 2cb2f83..eef86a3 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp
@@ -37,3 +37,4 @@
 
 create  index btreeName  on SimpleGeoPlace (name) type btree;
 
+create  primary index sec_primary_idx  on SimpleGeoPlace;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-empty-dataset-with-index/insert-into-empty-dataset-with-index.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-empty-dataset-with-index/insert-into-empty-dataset-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-empty-dataset-with-index/insert-into-empty-dataset-with-index.1.ddl.sqlpp
index 3a7ffe8..1ba5ecee 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-empty-dataset-with-index/insert-into-empty-dataset-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-empty-dataset-with-index/insert-into-empty-dataset-with-index.1.ddl.sqlpp
@@ -42,3 +42,4 @@
 
 create  index idx_LineID_suppkey  on LineID (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineID;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_01/insert-into-loaded-dataset-with-index_01.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_01/insert-into-loaded-dataset-with-index_01.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_01/insert-into-loaded-dataset-with-index_01.3.ddl.sqlpp
index ec12e33..98e5016 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_01/insert-into-loaded-dataset-with-index_01.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_01/insert-into-loaded-dataset-with-index_01.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineID_suppkey  on LineID (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineID;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_02/insert-into-loaded-dataset-with-index_02.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_02/insert-into-loaded-dataset-with-index_02.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_02/insert-into-loaded-dataset-with-index_02.3.ddl.sqlpp
index ec12e33..98e5016 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_02/insert-into-loaded-dataset-with-index_02.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_02/insert-into-loaded-dataset-with-index_02.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineID_suppkey  on LineID (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineID;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.1.ddl.sqlpp
new file mode 100644
index 0000000..5c943d8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.1.ddl.sqlpp
@@ -0,0 +1,42 @@
+/*
+ * 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     : Testing secondary primary index with autogenerated primary key
+ * Expected Result : Success
+ * Date            : Jul 30 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+  id : uuid,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+};
+
+create  dataset DBLP(DBLPType) primary key id autogenerated ;
+
+create primary index sec_primary_idx on DBLP;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.2.update.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.2.update.sqlpp
new file mode 100644
index 0000000..7fed49f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.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.
+ */
+/*
+ * Description     : Testing secondary primary index with autogenerated primary key
+ * Expected Result : Success
+ * Date            : Jul 30 2017
+ */
+
+use test;
+
+
+insert into DBLP
+select element {'dblpid':'books/acm/kim95/Blakeley95','title':'OQL[C++]  Extending C++ with an Object Query Capability.','authors':'José A. Blakeley','misc':'2002-01-03 69-88 Modern Database Systems db/books/collections/kim95.html#Blakeley95 1995'};

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.3.query.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.3.query.sqlpp
new file mode 100644
index 0000000..bd12757
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.3.query.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * 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     : Testing secondary primary index with autogenerated primary key
+ * Expected Result : Success
+ * Date            : Jul 30 2017
+ */
+ 
+use test;
+
+
+select element o.title
+from  DBLP as o
+where test.contains(o.title,'Extending')
+;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/load-with-index/load-with-index.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/load-with-index/load-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/load-with-index/load-with-index.1.ddl.sqlpp
index b2d43a6..893fdac 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/load-with-index/load-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/load-with-index/load-with-index.1.ddl.sqlpp
@@ -47,3 +47,4 @@
 
 create  index idx_partkey  on LineItem (l_partkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp
index 79f376a..fb79495 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp
@@ -44,3 +44,4 @@
 
 create  index idx_employee_first_name  on test.employee (fname) type btree;
 
+create  primary index sec_primary_idx  on test.employee;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp
index 0f86a33..e2ec595 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp
@@ -29,3 +29,6 @@
 drop index employee.idx_employee_first_name;
 create  index idx_employee_first_name  on test.employee (fname) type btree;
 
+drop index employee.sec_primary_idx;
+create  primary index sec_primary_idx  on test.employee;
+

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy-with-feed/using-correlated-prefix-merge-policy-with-feed.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy-with-feed/using-correlated-prefix-merge-policy-with-feed.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy-with-feed/using-correlated-prefix-merge-policy-with-feed.3.ddl.sqlpp
index fff1e39..c551c29 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy-with-feed/using-correlated-prefix-merge-policy-with-feed.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy-with-feed/using-correlated-prefix-merge-policy-with-feed.3.ddl.sqlpp
@@ -23,3 +23,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp
index 873c727..f64c5a3 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem ;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-correlated-secondary-btree/insert-with-correlated-secondary-btree.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-correlated-secondary-btree/insert-with-correlated-secondary-btree.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-correlated-secondary-btree/insert-with-correlated-secondary-btree.3.ddl.sqlpp
index cbc3aa8..50407b5 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-correlated-secondary-btree/insert-with-correlated-secondary-btree.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-correlated-secondary-btree/insert-with-correlated-secondary-btree.3.ddl.sqlpp
@@ -26,3 +26,4 @@
 
 create  index fbAuthorIdx  on FacebookMessages2 (`author-id`) type btree;
 
+create  primary index sec_primary_idx  on FacebookMessages2;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-secondary-btree/insert-with-secondary-btree.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-secondary-btree/insert-with-secondary-btree.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-secondary-btree/insert-with-secondary-btree.3.ddl.sqlpp
index 99da4f0..1422128 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-secondary-btree/insert-with-secondary-btree.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-secondary-btree/insert-with-secondary-btree.3.ddl.sqlpp
@@ -22,3 +22,4 @@
 
 create  index fbAuthorIdx  on FacebookMessages2 (`author-id`) type btree;
 
+create  primary index sec_primary_idx  on FacebookMessages2;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/load-with-secondary-btree/load-with-secondary-btree.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/load-with-secondary-btree/load-with-secondary-btree.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/load-with-secondary-btree/load-with-secondary-btree.3.ddl.sqlpp
index b2fcb2d..d528d4c 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/load-with-secondary-btree/load-with-secondary-btree.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/load-with-secondary-btree/load-with-secondary-btree.3.ddl.sqlpp
@@ -22,3 +22,4 @@
 
 create  index fbAuthorIdx  on FacebookMessages (`author-id`) type btree;
 
+create  primary index sec_primary_idx  on FacebookMessages;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp
new file mode 100644
index 0000000..242bf5d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp
@@ -0,0 +1,41 @@
+/*
+ * 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     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.Emp as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create  dataset employee(Emp) primary key id;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.2.update.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.2.update.sqlpp
new file mode 100644
index 0000000..86d776a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.2.update.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.
+ */
+/*
+ * Description     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+
+load  dataset employee using localfs ((`path`=`asterix_nc1://data/names.adm`),(`format`=`delimited-text`),(`delimiter`=`|`));
+

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.sqlpp
new file mode 100644
index 0000000..452938b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.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.
+ */
+/*
+ * Description     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+
+create primary index sec_primary_idx on employee ;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.query.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.query.sqlpp
new file mode 100644
index 0000000..6dad4b0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.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.
+ */
+/*
+ * Description     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+select element l
+from  employee as l where l.id < 200 order by l.id;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-but-different-order.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-but-different-order.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-but-different-order.1.ddl.sqlpp
new file mode 100644
index 0000000..1194907
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-but-different-order.1.ddl.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * 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 creating an index with the same keys as primary keys but in different order
+ * Expected Result : Success
+ * Date            : Aug 3 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.testType as
+{
+  id : integer,
+  `value` : string
+}
+
+create  dataset testDS(testType) primary key id, `value`;
+
+create  index testIdx  on testDS (`value`, id);
+

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-in-same-order.2.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-in-same-order.2.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-in-same-order.2.ddl.sqlpp
new file mode 100644
index 0000000..f0e387c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-in-same-order.2.ddl.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * 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 creating an index with the same keys as primary keys in same order
+ * Expected Result : Success
+ * Date            : Aug 3 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.testType as
+{
+  id : integer,
+  `value` : string
+}
+
+create  dataset testDS(testType) primary key id, `value`;
+
+create  index testIdx  on testDS (id, `value`);
+

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/repetitive-keys.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/repetitive-keys.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/repetitive-keys.3.ddl.sqlpp
new file mode 100644
index 0000000..fb7310e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/repetitive-keys.3.ddl.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * 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 creating an index with the same key repeated
+ * Expected Result : Failure
+ * Date            : Aug 3 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.testType as
+{
+  id : integer,
+  `value` : string
+}
+
+create  dataset testDS(testType) primary key id;
+
+create  index testIdx  on testDS (`value`,`value`);
+

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
index 6114030..5e82d6f 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (nested.l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.1.ddl.sqlpp
new file mode 100644
index 0000000..5795925
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.1.ddl.sqlpp
@@ -0,0 +1,45 @@
+/*
+ * 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     : Testing deleting from a dataset that has a nested composite secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.Nested as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create type test.Emp as
+ closed {
+  nested : Nested
+};
+
+create  dataset employee(Emp) primary key nested.id, nested.lname;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.2.update.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.2.update.sqlpp
new file mode 100644
index 0000000..065e559
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.2.update.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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     : Testing deleting from a dataset that has a nested composite secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+
+load  dataset employee using localfs ((`path`=`asterix_nc1://data/names2.adm`),(`format`=`adm`));
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.3.ddl.sqlpp
new file mode 100644
index 0000000..3772116
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.3.ddl.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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     : Testing deleting from a dataset that has a nested composite secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+
+use test;
+
+create  primary index sec_primary_idx  on employee;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.4.update.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.4.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.4.update.sqlpp
new file mode 100644
index 0000000..c42fd77
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.4.update.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.
+ */
+/*
+ * Description     : Testing deleting from a dataset that has a nested composite secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+delete from employee e where e.nested.id > 200 and e.nested.lname != "Isa";
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.5.query.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.5.query.sqlpp
new file mode 100644
index 0000000..2f4c415
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.5.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.
+ */
+/*
+ * Description     : Testing deleting from a dataset that has a nested composite secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+select value e from employee e order by e.nested.id;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/drop-index/drop-index.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/drop-index/drop-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/drop-index/drop-index.3.ddl.sqlpp
index cbb4ef5..4212037 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/drop-index/drop-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/drop-index/drop-index.3.ddl.sqlpp
@@ -24,5 +24,8 @@
 
 create  index idx_t1_unique1  on t1 (nested.unique1) type btree;
 
+create  primary index sec_primary_idx  on t1;
+
 drop index t1.idx_t1_str1;
 drop index t1.idx_t1_unique1;
+drop index t1.sec_primary_idx;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.1.ddl.sqlpp
new file mode 100644
index 0000000..4fa95b3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.1.ddl.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     : Test inserting into an empty dataset and its empty secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.EmpTmp as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create type test.Nested as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create type test.Emp as
+ closed {
+  nested : Nested
+};
+
+create  dataset employeeTmp(EmpTmp) primary key id;
+
+create  dataset employee(Emp) primary key nested.id;
+
+create  primary index sec_primary_idx  on employee;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.2.update.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.2.update.sqlpp
new file mode 100644
index 0000000..6be0699
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.2.update.sqlpp
@@ -0,0 +1,32 @@
+/*
+ * 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 inserting into an empty dataset and its empty secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+
+load  dataset employeeTmp using localfs ((`path`=`asterix_nc1://data/names.adm`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+insert into employee
+select element {'nested':{'id':c.id,'fname':c.fname,'lname':c.lname,'age':c.age,'dept':c.dept}}
+from  employeeTmp as c;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.3.query.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.3.query.sqlpp
new file mode 100644
index 0000000..9ef9a81
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.3.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.
+ */
+/*
+ * Description     : Test inserting into an empty dataset and its empty secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+select value e from employee e where e.nested.id < 200 order by e.nested.id;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.1.ddl.sqlpp
new file mode 100644
index 0000000..1f7ad33
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.1.ddl.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * 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 inserting into an already loaded dataset and its secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type test.Nested as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create type test.Emp as
+ closed {
+  nested : Nested
+};
+
+create  dataset employee(Emp) primary key nested.id, nested.lname;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.2.update.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.2.update.sqlpp
new file mode 100644
index 0000000..01af829
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.2.update.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.
+ */
+/*
+ * Description     : Test inserting into an already loaded dataset and its secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+load  dataset employee using localfs ((`path`=`asterix_nc1://data/names2.adm`),(`format`=`adm`));

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.3.ddl.sqlpp
new file mode 100644
index 0000000..38c7c86
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.3.ddl.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.
+ */
+/*
+ * Description     : Test inserting into an already loaded dataset and its secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+create  primary index sec_primary_idx  on employee;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.4.update.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.4.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.4.update.sqlpp
new file mode 100644
index 0000000..0da083e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.4.update.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.
+ */
+/*
+ * Description     : Test inserting into an already loaded dataset and its secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+insert into employee
+select element {'nested':{'id':4432,'fname':'John','lname':'James','age':23,'dept':'IT'}};
+insert into employee
+select element {'nested':{'id':2256,'fname':'David','lname':'Blow','age':33,'dept':'HR'}};

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.5.query.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.5.query.sqlpp
new file mode 100644
index 0000000..6547cd2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.5.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.
+ */
+/*
+ * Description     : Test inserting into an already loaded dataset and its secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+select value e from employee e where e.nested.id > 4431 order by e.nested.id;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.1.ddl.sqlpp
new file mode 100644
index 0000000..952e543
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.1.ddl.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Test loading into an empty dataset and its empty secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type test.Nested as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create type test.Emp as
+ closed {
+  nested : Nested
+};
+
+create  dataset employee(Emp) primary key nested.id;
+
+create  primary index sec_primary_idx  on employee;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.2.update.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.2.update.sqlpp
new file mode 100644
index 0000000..0a50dca
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.2.update.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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 loading into an empty dataset and its empty secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+
+load  dataset employee using localfs ((`path`=`asterix_nc1://data/names2.adm`),(`format`=`adm`));
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.3.query.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.3.query.sqlpp
new file mode 100644
index 0000000..0667e37
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.3.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.
+ */
+/*
+ * Description     : Test loading into an empty dataset and its empty secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+select value e from employee e where e.nested.id < 200 order by e.nested.id;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp
new file mode 100644
index 0000000..23d5f16
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.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     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index.
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.EmpTmp as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create type test.Nested as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create type test.Emp as
+ closed {
+  nested : Nested
+};
+
+create  dataset employeeTmp(EmpTmp) primary key id;
+
+create  dataset employee(Emp) primary key nested.id, nested.lname;
+

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.2.update.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.2.update.sqlpp
new file mode 100644
index 0000000..7e25c0d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.2.update.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * 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     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+
+load  dataset employeeTmp using localfs ((`path`=`asterix_nc1://data/names.adm`),(`format`=`delimited-text`),(`delimiter`=`|`));
+
+insert into employee
+select element {'nested':{'id':c.id,'fname':c.fname,'lname':c.lname,'age':c.age,'dept':c.dept}}
+from  employeeTmp as c
+;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.sqlpp
new file mode 100644
index 0000000..1814242
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.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.
+ */
+/*
+ * Description     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+
+create primary index sec_primary_idx  on employee;
+

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.query.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.query.sqlpp
new file mode 100644
index 0000000..e099d11
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.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.
+ */
+/*
+ * Description     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+select element l.nested
+from  employee as l
+where ((l.nested.id = 881) and (l.nested.lname = 'Isa'));

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
index 925ae4a..55f14eb 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
@@ -30,3 +30,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
index 1072a85..4ca67fe 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
@@ -65,6 +65,8 @@
 
 create  index secndIndx_open  on t1 (address:string?) type btree enforced;
 
+create  primary index sec_primary_idx on t1;
+
 drop index t1.rtree_index_point;
 drop index t1.rtree_index_point_open;
 drop index t1.keyWD_indx;
@@ -72,3 +74,4 @@
 drop index t1.secndIndx;
 drop index t1.nested;
 drop index t1.secndIndx_open;
+drop index t1.sec_primary_idx;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-index/drop-index.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-index/drop-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-index/drop-index.3.ddl.sqlpp
index 90e6b7f..f01f959 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-index/drop-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-index/drop-index.3.ddl.sqlpp
@@ -30,5 +30,8 @@
 
 create  index idx_t1_unique1  on t1 (unique1) type btree;
 
+create  primary index sec_primary_idx  on t1;
+
 drop index t1.idx_t1_str1;
 drop index t1.idx_t1_unique1;
+drop index t1.sec_primary_idx;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
index d51ed99..2b169dd 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
@@ -52,3 +52,4 @@
 
 create  index part_index  on LineItem (l_partkey) type btree;
 
+create  primary index sec_primary_idx on LineItem ;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-correlated-index/insert-and-scan-dataset-with-correlated-index.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-correlated-index/insert-and-scan-dataset-with-correlated-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-correlated-index/insert-and-scan-dataset-with-correlated-index.3.ddl.sqlpp
index 0b0405c..c3ee05f 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-correlated-index/insert-and-scan-dataset-with-correlated-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-correlated-index/insert-and-scan-dataset-with-correlated-index.3.ddl.sqlpp
@@ -28,3 +28,5 @@
 
 create  index idx_employee_first_name  on test.employee (fname) type btree;
 
+create  primary index sec_primary_idx  on test.employee;
+

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
index ac3d7da..e9aae4a 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
@@ -39,3 +39,4 @@
 
 create  index idx_employee_first_name  on test.employee (fname) type btree;
 
+create  primary index sec_primary_idx  on test.employee;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/filtered-dataset/filtered-dataset.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/filtered-dataset/filtered-dataset.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/filtered-dataset/filtered-dataset.1.ddl.sqlpp
index f9349a7..f56f997 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/filtered-dataset/filtered-dataset.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/filtered-dataset/filtered-dataset.1.ddl.sqlpp
@@ -44,3 +44,4 @@
 
 create index AutherIdx on FilteredFacebookMessages(`author-id`);
 create index MessageIdx on FilteredFacebookMessages(message);
+create primary index sec_primary_idx on FilteredFacebookMessages;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-correlated-secondaries/multiple-correlated-secondaries.3.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-correlated-secondaries/multiple-correlated-secondaries.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-correlated-secondaries/multiple-correlated-secondaries.3.ddl.sqlpp
index acf2ebd..06e21d9 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-correlated-secondaries/multiple-correlated-secondaries.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-correlated-secondaries/multiple-correlated-secondaries.3.ddl.sqlpp
@@ -22,3 +22,4 @@
 create index btree_index on UpsertTo(kwds);
 create index rtree_index on UpsertTo(point) type rtree;
 create index inverted_index on UpsertTo(kwds) type keyword;
+create primary index sec_primary_idx on UpsertTo;
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-secondaries/multiple-secondaries.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-secondaries/multiple-secondaries.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-secondaries/multiple-secondaries.1.ddl.sqlpp
index e509614..cac8822 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-secondaries/multiple-secondaries.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-secondaries/multiple-secondaries.1.ddl.sqlpp
@@ -42,3 +42,4 @@
 create index btree_index on UpsertTo(kwds);
 create index rtree_index on UpsertTo(point) type rtree;
 create index inverted_index on UpsertTo(kwds) type keyword;
+create primary index sec_primary_idx on UpsertTo;

asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/primary-secondary-btree/primary-secondary-btree.1.ddl.sqlpp

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/primary-secondary-btree/primary-secondary-btree.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/primary-secondary-btree/primary-secondary-btree.1.ddl.sqlpp
index 0e5d5b2..d9111c7 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/primary-secondary-btree/primary-secondary-btree.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/primary-secondary-btree/primary-secondary-btree.1.ddl.sqlpp
@@ -35,4 +35,5 @@
 
 create dataset UpsertTo(TestType) primary key id;
 create index ageindex on UpsertTo(age);
+create primary index sec_primary_idx on UpsertTo;
 create dataset UpsertFrom(TestType) primary key id;

asterixdb/asterix-app/src/test/resources/runtimets/results/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.3.adm

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.3.adm
new file mode 100644
index 0000000..c5b58bb
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.3.adm
@@ -0,0 +1 @@
+"OQL[C++]  Extending C++ with an Object Query Capability."
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.adm

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.adm
new file mode 100644
index 0000000..7891dc9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.adm
@@ -0,0 +1,10 @@
+{ "id": 101, "fname": "Javier", "lname": "Makuch", "age": 28, "dept": "IT" }
+{ "id": 110, "fname": "Allan", "lname": "Piland", "age": 29, "dept": "HR" }
+{ "id": 112, "fname": "Pearlie", "lname": "Aumann", "age": 31, "dept": "Payroll" }
+{ "id": 113, "fname": "Chandra", "lname": "Hase", "age": 34, "dept": "Sales" }
+{ "id": 114, "fname": "Christian", "lname": "Convery", "age": 28, "dept": "HR" }
+{ "id": 115, "fname": "Panther", "lname": "Ritch", "age": 26, "dept": "IT" }
+{ "id": 116, "fname": "Ted", "lname": "Elsea", "age": 26, "dept": "IT" }
+{ "id": 117, "fname": "Tabatha", "lname": "Bladen", "age": 25, "dept": "HR" }
+{ "id": 118, "fname": "Clayton", "lname": "Oltman", "age": 42, "dept": "Sales" }
+{ "id": 119, "fname": "Sharron", "lname": "Darwin", "age": 32, "dept": "Payroll" }
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.5.adm

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.5.adm
new file mode 100644
index 0000000..cebb3dc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.5.adm
@@ -0,0 +1,11 @@
+{ "nested": { "id": 101, "fname": "Javier", "lname": "Makuch", "age": 28, "dept": "IT" } }
+{ "nested": { "id": 110, "fname": "Allan", "lname": "Piland", "age": 29, "dept": "HR" } }
+{ "nested": { "id": 112, "fname": "Pearlie", "lname": "Aumann", "age": 31, "dept": "Payroll" } }
+{ "nested": { "id": 113, "fname": "Chandra", "lname": "Hase", "age": 34, "dept": "Sales" } }
+{ "nested": { "id": 114, "fname": "Christian", "lname": "Convery", "age": 28, "dept": "HR" } }
+{ "nested": { "id": 115, "fname": "Panther", "lname": "Ritch", "age": 26, "dept": "IT" } }
+{ "nested": { "id": 116, "fname": "Ted", "lname": "Elsea", "age": 26, "dept": "IT" } }
+{ "nested": { "id": 117, "fname": "Tabatha", "lname": "Bladen", "age": 25, "dept": "HR" } }
+{ "nested": { "id": 118, "fname": "Clayton", "lname": "Oltman", "age": 42, "dept": "Sales" } }
+{ "nested": { "id": 119, "fname": "Sharron", "lname": "Darwin", "age": 32, "dept": "Payroll" } }
+{ "nested": { "id": 881, "fname": "Julio", "lname": "Isa", "age": 38, "dept": "Sales" } }
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.3.adm

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.3.adm
new file mode 100644
index 0000000..6edf6da
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.3.adm
@@ -0,0 +1,10 @@
+{ "nested": { "id": 101, "fname": "Javier", "lname": "Makuch", "age": 28, "dept": "IT" } }
+{ "nested": { "id": 110, "fname": "Allan", "lname": "Piland", "age": 29, "dept": "HR" } }
+{ "nested": { "id": 112, "fname": "Pearlie", "lname": "Aumann", "age": 31, "dept": "Payroll" } }
+{ "nested": { "id": 113, "fname": "Chandra", "lname": "Hase", "age": 34, "dept": "Sales" } }
+{ "nested": { "id": 114, "fname": "Christian", "lname": "Convery", "age": 28, "dept": "HR" } }
+{ "nested": { "id": 115, "fname": "Panther", "lname": "Ritch", "age": 26, "dept": "IT" } }
+{ "nested": { "id": 116, "fname": "Ted", "lname": "Elsea", "age": 26, "dept": "IT" } }
+{ "nested": { "id": 117, "fname": "Tabatha", "lname": "Bladen", "age": 25, "dept": "HR" } }
+{ "nested": { "id": 118, "fname": "Clayton", "lname": "Oltman", "age": 42, "dept": "Sales" } }
+{ "nested": { "id": 119, "fname": "Sharron", "lname": "Darwin", "age": 32, "dept": "Payroll" } }
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.5.adm

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.5.adm
new file mode 100644
index 0000000..ff7446a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.5.adm
@@ -0,0 +1,10 @@
+{ "nested": { "id": 4432, "fname": "John", "lname": "James", "age": 23, "dept": "IT" } }
+{ "nested": { "id": 4727, "fname": "Michael", "lname": "Carey", "age": 50, "dept": "Payroll" } }
+{ "nested": { "id": 5438, "fname": "Lakisha", "lname": "Quashie", "age": 29, "dept": "HR" } }
+{ "nested": { "id": 7444, "fname": "Sharad", "lname": "Mehrotra", "age": 42, "dept": "Sales" } }
+{ "nested": { "id": 7663, "fname": "Annabelle", "lname": "Nimmo", "age": 30, "dept": "Payroll" } }
+{ "nested": { "id": 8301, "fname": "Earlene", "lname": "Wallick", "age": 26, "dept": "HR" } }
+{ "nested": { "id": 8338, "fname": "Julio", "lname": "Bosket", "age": 28, "dept": "Payroll" } }
+{ "nested": { "id": 9555, "fname": "Tony", "lname": "Givargis", "age": 40, "dept": "Sales" } }
+{ "nested": { "id": 9763, "fname": "Ted", "lname": "Saini", "age": 31, "dept": "IT" } }
+{ "nested": { "id": 9941, "fname": "Khurram Faraaz", "lname": "Mohammed", "age": 30, "dept": "HR" } }
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.3.adm

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.3.adm
new file mode 100644
index 0000000..6edf6da
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.3.adm
@@ -0,0 +1,10 @@
+{ "nested": { "id": 101, "fname": "Javier", "lname": "Makuch", "age": 28, "dept": "IT" } }
+{ "nested": { "id": 110, "fname": "Allan", "lname": "Piland", "age": 29, "dept": "HR" } }
+{ "nested": { "id": 112, "fname": "Pearlie", "lname": "Aumann", "age": 31, "dept": "Payroll" } }
+{ "nested": { "id": 113, "fname": "Chandra", "lname": "Hase", "age": 34, "dept": "Sales" } }
+{ "nested": { "id": 114, "fname": "Christian", "lname": "Convery", "age": 28, "dept": "HR" } }
+{ "nested": { "id": 115, "fname": "Panther", "lname": "Ritch", "age": 26, "dept": "IT" } }
+{ "nested": { "id": 116, "fname": "Ted", "lname": "Elsea", "age": 26, "dept": "IT" } }
+{ "nested": { "id": 117, "fname": "Tabatha", "lname": "Bladen", "age": 25, "dept": "HR" } }
+{ "nested": { "id": 118, "fname": "Clayton", "lname": "Oltman", "age": 42, "dept": "Sales" } }
+{ "nested": { "id": 119, "fname": "Sharron", "lname": "Darwin", "age": 32, "dept": "Payroll" } }
\ No newline at end of file

asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.adm

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.adm
new file mode 100644
index 0000000..cebf05b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.adm
@@ -0,0 +1 @@
+{ "id": 881, "fname": "Julio", "lname": "Isa", "age": 38, "dept": "Sales" }

asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml

diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index 920f0f0..68b4227 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -1618,8 +1618,44 @@
         </test-case>
         -->
   </test-group>
+  <test-group name="ddl/create-index">
+    <test-case FilePath="ddl/create-index">
+      <compilation-unit name="create-index-1">
+        <output-dir compare="Text">create-index-1</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="ddl/create-index">
+      <compilation-unit name="create-index-2">
+        <output-dir compare="Text">create-index-2</output-dir>
+        <expected-error>Syntax error: In line 53 >>create  primary index sec_primary_idx1  on LineItem type rtree;&lt;&lt; Encountered "rtree" at column 58.</expected-error>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="ddl/create-index">
+      <compilation-unit name="create-index-3">
+        <output-dir compare="Text">create-index-3</output-dir>
+        <expected-error>Syntax error: In line 53 >>create  primary  sec_primary_idx1  on LineItem;&lt;&lt; Encountered &lt;IDENTIFIER&gt; "sec_primary_idx1" at column 18.</expected-error>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="ddl/create-index">
+      <compilation-unit name="create-index-4">
+        <output-dir compare="Text">create-index-4</output-dir>
+        <expected-error>Syntax error: In line 53 >>create  primary index if not exists sec_primary_idx1  if not exists on LineItem;&lt;&lt; Encountered &lt;IDENTIFIER&gt; "sec_primary_idx1" at column 37.</expected-error>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="ddl/create-index">
+      <compilation-unit name="create-index-5">
+        <output-dir compare="Text">create-index-5</output-dir>
+        <expected-error>Syntax error: In line 53 >>create  primary index if exists sec_primary_idx1  on LineItem;&lt;&lt; Encountered "exists" at column 26.</expected-error>
+      </compilation-unit>
+    </test-case>
+  </test-group>
   <test-group name="dml">
     <test-case FilePath="dml">
+      <compilation-unit name="insert-with-autogenerated-pk_adm-with-sec-primary-index">
+        <output-dir compare="Text">insert-with-autogenerated-pk_adm-with-sec-primary-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="dml">
       <compilation-unit name="compact-dataset-and-its-indexes">
         <output-dir compare="Text">compact-dataset-and-its-indexes</output-dir>
       </compilation-unit>
@@ -1793,6 +1829,11 @@
         </test-case>
         -->
     <test-case FilePath="dml">
+      <compilation-unit name="insert-with-autogenerated-pk_adm-with-sec-primary-index">
+        <output-dir compare="Text">insert-with-autogenerated-pk_adm-with-sec-primary-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="dml">
       <compilation-unit name="insert-with-autogenerated-pk_adm_01">
         <output-dir compare="Text">insert-with-autogenerated-pk_adm_01</output-dir>
       </compilation-unit>
@@ -2994,6 +3035,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="index-selection">
+      <compilation-unit name="btree-sec-primary-index">
+        <output-dir compare="Text">btree-sec-primary-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="index-selection">
       <compilation-unit name="btree-index-composite-key-mixed-intervals">
         <output-dir compare="Text">btree-index-composite-key-mixed-intervals</output-dir>
       </compilation-unit>
@@ -3528,18 +3574,38 @@
       </compilation-unit>
     </test-case>
   </test-group>
+  <test-group name="index">
+    <test-group name="index/validations">
+      <test-case FilePath="index/validations">
+        <compilation-unit name="keys-same-as-pk-but-different-order">
+          <output-dir compare="Text">keys-same-as-pk-but-different-order</output-dir>
+        </compilation-unit>
+      </test-case>
+      <test-case FilePath="index/validations">
+        <compilation-unit name="keys-same-as-pk-in-same-order">
+          <output-dir compare="Text">keys-same-as-pk-in-same-order</output-dir>
+        </compilation-unit>
+      </test-case>
+      <test-case FilePath="index/validations">
+        <compilation-unit name="repetitive-keys">
+          <output-dir compare="Text">repetitive-keys</output-dir>
+          <expected-error>Cannot create index with the same field "[value]" specified more than once.</expected-error>
+        </compilation-unit>
+      </test-case>
+    </test-group>
+  </test-group>
   <test-group name="open-index-enforced">
     <test-group name="open-index-enforced/error-checking">
       <test-case FilePath="open-index-enforced/error-checking">
         <compilation-unit name="enforced-field-name-collision">
           <output-dir compare="Text">enforced-field-name-collision</output-dir>
-          <!-- <expected-error>org.apache.hyracks.algebricks.common.exceptions.AlgebricksException</expected-error> -->
+           <expected-error>Cannot create enforced index on "[value]" field. The field is closed type.</expected-error>
         </compilation-unit>
       </test-case>
       <test-case FilePath="open-index-enforced/error-checking">
         <compilation-unit name="enforced-field-type-collision">
           <output-dir compare="Text">enforced-field-type-collision</output-dir>
-          <expected-error>A field &quot;[value]&quot; is already defined with the type &quot;string&quot;</expected-error>
+          <expected-error>Cannot create enforced index on "[value]" field. The field is closed type.</expected-error>
         </compilation-unit>
       </test-case>
       <test-case FilePath="open-index-enforced/error-checking">
@@ -4060,6 +4126,11 @@
         </compilation-unit>
       </test-case>
       <test-case FilePath="nested-index/index-selection">
+        <compilation-unit name="btree-sec-primary-index">
+          <output-dir compare="Text">btree-sec-primary-index</output-dir>
+        </compilation-unit>
+      </test-case>
+      <test-case FilePath="nested-index/index-selection">
         <compilation-unit name="btree-index-composite-key-mixed-intervals">
           <output-dir compare="Text">btree-index-composite-key-mixed-intervals</output-dir>
         </compilation-unit>
@@ -4225,6 +4296,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="nested-index-dml">
+      <compilation-unit name="delete-from-loaded-dataset-with-sec-primary-index">
+        <output-dir compare="Text">delete-from-loaded-dataset-with-sec-primary-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="nested-index-dml">
       <compilation-unit name="drop-index">
         <output-dir compare="Text">drop-index</output-dir>
       </compilation-unit>
@@ -4235,6 +4311,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="nested-index-dml">
+      <compilation-unit name="insert-into-empty-dataset-with-sec-primary-index">
+        <output-dir compare="Text">insert-into-empty-dataset-with-sec-primary-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="nested-index-dml">
       <compilation-unit name="insert-into-loaded-dataset-with-index_01">
         <output-dir compare="Text">insert-into-loaded-dataset-with-index_01</output-dir>
       </compilation-unit>
@@ -4245,11 +4326,21 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="nested-index-dml">
+      <compilation-unit name="insert-into-loaded-dataset-with-sec-primary-index">
+        <output-dir compare="Text">insert-into-loaded-dataset-with-sec-primary-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="nested-index-dml">
       <compilation-unit name="load-with-index">
         <output-dir compare="Text">load-with-index</output-dir>
       </compilation-unit>
     </test-case>
     <test-case FilePath="nested-index-dml">
+      <compilation-unit name="load-with-sec-primary-index">
+        <output-dir compare="Text">load-with-sec-primary-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="nested-index-dml">
       <compilation-unit name="load-with-ngram-index">
         <output-dir compare="Text">load-with-ngram-index</output-dir>
       </compilation-unit>