asterixdb-notifications mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ali Alsuliman (Code Review)" <do-not-re...@asterixdb.incubator.apache.org>
Subject Change in asterixdb[master]: [ASTERIXDB-2015][IDX] Introduce Secondary Primary Index
Date Fri, 04 Aug 2017 09:17:23 GMT
Ali Alsuliman has uploaded a new change for review.

  https://asterix-gerrit.ics.uci.edu/1916

Change subject: [ASTERIXDB-2015][IDX] Introduce Secondary Primary Index
......................................................................

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

- user model changes: no
- storage format changes: no
- interface changes: no

details:
WIP

Change-Id: I59725425ba7c5fe438507dc900f83eaab239d296
---
M asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java
A asterixdb/asterix-app/data/names2.adm
M asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java
M asterixdb/asterix-app/src/test/resources/runtimets/only_sqlpp.xml
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
M 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
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
M 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
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp
M 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
M 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
M 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
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
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
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
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/load-with-index/load-with-index.1.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp
M 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
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-correlated-secondary-btree/insert-with-correlated-secondary-btree.3.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-secondary-btree/insert-with-secondary-btree.3.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/load-with-secondary-btree/load-with-secondary-btree.3.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.2.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-but-different-order.1.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-in-same-order.2.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/repetitive-keys.3.ddl.sqlpp
M 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
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
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
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
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
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
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/drop-index/drop-index.3.ddl.sqlpp
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
M 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
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-index/drop-index.3.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
M 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
M 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
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/filtered-dataset/filtered-dataset.1.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-correlated-secondaries/multiple-correlated-secondaries.3.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-secondaries/multiple-secondaries.1.ddl.sqlpp
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/primary-secondary-btree/primary-secondary-btree.1.ddl.sqlpp
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
A asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.adm
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
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
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
A asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.3.adm
A asterixdb/asterix-app/src/test/resources/runtimets/results/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.adm
M asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
M asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java
M asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
M asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/BTreeResourceFactoryProvider.java
M asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/MetadataProvider.java
M asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/IndexUtil.java
M asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryBTreeOperationsHelper.java
M asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryCorrelatedTreeIndexOperationsHelper.java
M asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryIndexOperationsHelper.java
A asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryPrimaryBTreeOperationsHelper.java
A asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryPrimaryCorrelatedBTreeOperationsHelper.java
M hyracks-fullstack/hyracks/hyracks-storage-am-lsm-btree/src/main/java/org/apache/hyracks/storage/am/lsm/btree/impls/LSMBTreePointSearchCursor.java
86 files changed, 1,911 insertions(+), 44 deletions(-)


  git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/16/1916/1

diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java
index ddc93f8..2898925 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/BTreeAccessMethod.java
@@ -36,6 +36,7 @@
 import org.apache.asterix.lang.common.util.FunctionUtil;
 import org.apache.asterix.metadata.entities.Dataset;
 import org.apache.asterix.metadata.entities.Index;
+import org.apache.asterix.metadata.utils.IndexUtil;
 import org.apache.asterix.om.functions.BuiltinFunctions;
 import org.apache.asterix.om.types.ARecordType;
 import org.apache.asterix.om.types.AUnionType;
@@ -502,8 +503,10 @@
             inputOp = probeSubTree.getRoot();
         }
 
+        // TODO: "isSecondaryPrimary" is passed to "outputPrimaryKeysOnly" parameter since secondary primary index has only PK
+        // This is one solution among other alternatives
         ILogicalOperator secondaryIndexUnnestOp = AccessMethodUtils.createSecondaryIndexUnnestMap(dataset, recordType,
-                metaRecordType, chosenIndex, inputOp, jobGenParams, context, false, retainInput, retainNull);
+                metaRecordType, chosenIndex, inputOp, jobGenParams, context, IndexUtil.isSecondaryPrimary(dataset, chosenIndex), retainInput, retainNull);
 
         // Generate the rest of the upstream plan which feeds the search results into the primary index.
         AbstractUnnestMapOperator primaryIndexUnnestOp = null;
diff --git a/asterixdb/asterix-app/data/names2.adm b/asterixdb/asterix-app/data/names2.adm
new file mode 100644
index 0000000..15afd26
--- /dev/null
+++ b/asterixdb/asterix-app/data/names2.adm
@@ -0,0 +1,120 @@
+{"nested":{"id":711,"fname":"Hugh","lname":"Lema","age":25,"dept":"HR"}}
+{"nested":{"id":721,"fname":"Schwan","lname":"Phil","age":34,"dept":"Payroll"}}
+{"nested":{"id":732,"fname":"Noemi","lname":"Eacret","age":56,"dept":"HR"}}
+{"nested":{"id":741,"fname":"Julio","lname":"Mattocks","age":38,"dept":"Sales"}}
+{"nested":{"id":751,"fname":"Lance","lname":"Kottke","age":34,"dept":"IT"}}
+{"nested":{"id":761,"fname":"Kurt","lname":"Liz","age":32,"dept":"HR"}}
+{"nested":{"id":771,"fname":"Neva","lname":"Barbeau","age":45,"dept":"Sales"}}
+{"nested":{"id":781,"fname":"Karina","lname":"Tuthill","age":46,"dept":"Payroll"}}
+{"nested":{"id":791,"fname":"Maricela","lname":"Cambron","age":36,"dept":"IT"}}
+{"nested":{"id":110,"fname":"Allan","lname":"Piland","age":29,"dept":"HR"}}
+{"nested":{"id":101,"fname":"Javier","lname":"Makuch","age":28,"dept":"IT"}}
+{"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":210,"fname":"Clayton","lname":"Durgin","age":52,"dept":"HR"}}
+{"nested":{"id":299,"fname":"Julio","lname":"Iorio","age":37,"dept":"IT"}}
+{"nested":{"id":212,"fname":"Emilia","lname":"Chenail","age":26,"dept":"Sales"}}
+{"nested":{"id":213,"fname":"Kenya","lname":"Almquist","age":43,"dept":"Payroll"}}
+{"nested":{"id":214,"fname":"Alejandra","lname":"Lacefield","age":41,"dept":"HR"}}
+{"nested":{"id":215,"fname":"Karina","lname":"Michelsen","age":46,"dept":"IT"}}
+{"nested":{"id":216,"fname":"Katy","lname":"Delillo","age":36,"dept":"IT"}}
+{"nested":{"id":217,"fname":"Benita","lname":"Kleist","age":37,"dept":"HR"}}
+{"nested":{"id":218,"fname":"Earlene","lname":"Paluch","age":31,"dept":"IT"}}
+{"nested":{"id":219,"fname":"Kurt","lname":"Petermann","age":27,"dept":"Payroll"}}
+{"nested":{"id":915,"fname":"Starner","lname":"Stuart","age":25,"dept":"Sales"}}
+{"nested":{"id":925,"fname":"Sofia","lname":"Cuff","age":30,"dept":"HR"}}
+{"nested":{"id":935,"fname":"Milagros","lname":"Murguia","age":31,"dept":"IT"}}
+{"nested":{"id":945,"fname":"Margery","lname":"Haldeman","age":32,"dept":"IT"}}
+{"nested":{"id":955,"fname":"Max","lname":"Mell","age":33,"dept":"HR"}}
+{"nested":{"id":965,"fname":"Micco","lname":"Mercy","age":31,"dept":"Payroll"}}
+{"nested":{"id":975,"fname":"Clare","lname":"Vangieson","age":34,"dept":"IT"}}
+{"nested":{"id":985,"fname":"Elnora","lname":"Dimauro","age":35,"dept":"Sales"}}
+{"nested":{"id":995,"fname":"Pearlie","lname":"Kocian","age":38,"dept":"HR"}}
+{"nested":{"id":809,"fname":"Clayton","lname":"Delany","age":23,"dept":"IT"}}
+{"nested":{"id":811,"fname":"Kubik","lname":"Kuhn","age":27,"dept":"HR"}}
+{"nested":{"id":821,"fname":"Allan","lname":"Tomes","age":29,"dept":"Payroll"}}
+{"nested":{"id":831,"fname":"Lonnie","lname":"Aller","age":33,"dept":"Sales"}}
+{"nested":{"id":841,"fname":"Neil","lname":"Hurrell","age":26,"dept":"IT"}}
+{"nested":{"id":851,"fname":"Clayton","lname":"Engles","age":41,"dept":"HR"}}
+{"nested":{"id":861,"fname":"Javier","lname":"Gabrielson","age":39,"dept":"Payroll"}}
+{"nested":{"id":871,"fname":"Allan","lname":"Alejandre","age":48,"dept":"IT"}}
+{"nested":{"id":881,"fname":"Julio","lname":"Isa","age":38,"dept":"Sales"}}
+{"nested":{"id":891,"fname":"Roslyn","lname":"Simmerman","age":31,"dept":"IT"}}
+{"nested":{"id":601,"fname":"Neil","lname":"Deforge","age":26,"dept":"HR"}}
+{"nested":{"id":611,"fname":"Earlene","lname":"Marcy","age":32,"dept":"IT"}}
+{"nested":{"id":621,"fname":"Erik","lname":"Lechuga","age":42,"dept":"Payroll"}}
+{"nested":{"id":631,"fname":"Tyrone","lname":"Holtzclaw","age":34,"dept":"Sales"}}
+{"nested":{"id":641,"fname":"Lance","lname":"Hankey","age":35,"dept":"Sales"}}
+{"nested":{"id":651,"fname":"Mallory","lname":"Gladding","age":31,"dept":"HR"}}
+{"nested":{"id":661,"fname":"Tia","lname":"Braaten","age":40,"dept":"IT"}}
+{"nested":{"id":671,"fname":"Julio","lname":"Vanpatten","age":30,"dept":"Payroll"}}
+{"nested":{"id":681,"fname":"Max","lname":"Teachout","age":34,"dept":"IT"}}
+{"nested":{"id":691,"fname":"Karina","lname":"Wingerter","age":31,"dept":"IT"}}
+{"nested":{"id":8301,"fname":"Earlene","lname":"Wallick","age":26,"dept":"HR"}}
+{"nested":{"id":8338,"fname":"Julio","lname":"Bosket","age":28,"dept":"Payroll"}}
+{"nested":{"id":5438,"fname":"Lakisha","lname":"Quashie","age":29,"dept":"HR"}}
+{"nested":{"id":538,"fname":"Milagros","lname":"Forkey","age":34,"dept":"Sales"}}
+{"nested":{"id":504,"fname":"Erik","lname":"Dobek","age":29,"dept":"IT"}}
+{"nested":{"id":584,"fname":"Dollie","lname":"Dattilo","age":32,"dept":"Payroll"}}
+{"nested":{"id":524,"fname":"Benita","lname":"Maltos","age":33,"dept":"IT"}}
+{"nested":{"id":534,"fname":"Kurt","lname":"Biscoe","age":36,"dept":"HR"}}
+{"nested":{"id":544,"fname":"Loraine","lname":"Housel","age":30,"dept":"Sales"}}
+{"nested":{"id":554,"fname":"Jamie","lname":"Rachal","age":30,"dept":"IT"}}
+{"nested":{"id":564,"fname":"Liza","lname":"Fredenburg","age":37,"dept":"IT"}}
+{"nested":{"id":574,"fname":"Ericka","lname":"Feldmann","age":29,"dept":"Sales"}}
+{"nested":{"id":589,"fname":"Lorrie","lname":"Sharon","age":27,"dept":"IT"}}
+{"nested":{"id":594,"fname":"Roxie","lname":"Houghtaling","age":40,"dept":"Payroll"}}
+{"nested":{"id":514,"fname":"Julio","lname":"Ruben","age":41,"dept":"IT"}}
+{"nested":{"id":414,"fname":"Mathew","lname":"Fuschetto","age":34,"dept":"HR"}}
+{"nested":{"id":424,"fname":"Allyson","lname":"Remus","age":32,"dept":"IT"}}
+{"nested":{"id":434,"fname":"Earlene","lname":"Linebarger","age":26,"dept":"Payroll"}}
+{"nested":{"id":444,"fname":"Clinton","lname":"Sick","age":29,"dept":"IT"}}
+{"nested":{"id":454,"fname":"Ted","lname":"Caba","age":28,"dept":"HR"}}
+{"nested":{"id":464,"fname":"Fernando","lname":"Engelke","age":39,"dept":"IT"}}
+{"nested":{"id":474,"fname":"Mathew","lname":"Courchesne","age":31,"dept":"IT"}}
+{"nested":{"id":484,"fname":"Cody","lname":"Vinyard","age":36,"dept":"Payroll"}}
+{"nested":{"id":494,"fname":"Benita","lname":"Fravel","age":33,"dept":"Sales"}}
+{"nested":{"id":404,"fname":"Emilia","lname":"Square","age":32,"dept":"IT"}}
+{"nested":{"id":1263,"fname":"Tania","lname":"Loffredo","age":25,"dept":"IT"}}
+{"nested":{"id":363,"fname":"Cody","lname":"Rodreguez","age":26,"dept":"IT"}}
+{"nested":{"id":463,"fname":"Marcie","lname":"States","age":28,"dept":"IT"}}
+{"nested":{"id":3563,"fname":"Hazeltine","lname":"Susan","age":29,"dept":"Sales"}}
+{"nested":{"id":7663,"fname":"Annabelle","lname":"Nimmo","age":30,"dept":"Payroll"}}
+{"nested":{"id":9763,"fname":"Ted","lname":"Saini","age":31,"dept":"IT"}}
+{"nested":{"id":1863,"fname":"Darren","lname":"Thorington","age":32,"dept":"Sales"}}
+{"nested":{"id":2963,"fname":"Neil","lname":"Gunnerson","age":34,"dept":"IT"}}
+{"nested":{"id":1410,"fname":"Clinton","lname":"Fredricks","age":34,"dept":"IT"}}
+{"nested":{"id":1411,"fname":"Lance","lname":"Farquhar","age":32,"dept":"HR"}}
+{"nested":{"id":1412,"fname":"Tabatha","lname":"Crisler","age":33,"dept":"IT"}}
+{"nested":{"id":1413,"fname":"Max","lname":"Durney","age":29,"dept":"IT"}}
+{"nested":{"id":1414,"fname":"Carmella","lname":"Strauser","age":30,"dept":"Payroll"}}
+{"nested":{"id":1415,"fname":"Kelly","lname":"Carrales","age":40,"dept":"IT"}}
+{"nested":{"id":1416,"fname":"Guy","lname":"Merten","age":29,"dept":"Sales"}}
+{"nested":{"id":1417,"fname":"Noreen","lname":"Ruhland","age":29,"dept":"IT"}}
+{"nested":{"id":1418,"fname":"Julio","lname":"Damore","age":27,"dept":"Sales"}}
+{"nested":{"id":1419,"fname":"Selena","lname":"Truby","age":25,"dept":"HR"}}
+{"nested":{"id":1420,"fname":"Alejandra","lname":"Commons","age":30,"dept":"Sales"}}
+{"nested":{"id":1421,"fname":"Allyson","lname":"Balk","age":30,"dept":"IT"}}
+{"nested":{"id":1422,"fname":"Nelson","lname":"Byun","age":40,"dept":"Sales"}}
+{"nested":{"id":1423,"fname":"Christian","lname":"Reidhead","age":40,"dept":"IT"}}
+{"nested":{"id":1424,"fname":"Pearlie","lname":"Hopkin","age":48,"dept":"Payroll"}}
+{"nested":{"id":1425,"fname":"Nelson","lname":"Wohlers","age":41,"dept":"HR"}}
+{"nested":{"id":1426,"fname":"Marcie","lname":"Rasnake","age":42,"dept":"Sales"}}
+{"nested":{"id":1427,"fname":"Hugh","lname":"Marshburn","age":43,"dept":"Payroll"}}
+{"nested":{"id":1428,"fname":"Mathew","lname":"Marasco","age":45,"dept":"Sales"}}
+{"nested":{"id":1429,"fname":"Kurt","lname":"Veres","age":32,"dept":"IT"}}
+{"nested":{"id":1430,"fname":"Julio","lname":"Barkett","age":39,"dept":"Sales"}}
+{"nested":{"id":4727,"fname":"Michael","lname":"Carey","age":50,"dept":"Payroll"}}
+{"nested":{"id":2333,"fname":"Chen","lname":"Li","age":42,"dept":"HR"}}
+{"nested":{"id":7444,"fname":"Sharad","lname":"Mehrotra","age":42,"dept":"Sales"}}
+{"nested":{"id":9555,"fname":"Tony","lname":"Givargis","age":40,"dept":"Sales"}}
+{"nested":{"id":3666,"fname":"Young Seok","lname":"Kim","age":35,"dept":"Payroll"}}
+{"nested":{"id":9941,"fname":"Khurram Faraaz","lname":"Mohammed","age":30,"dept":"HR"}}
+{"nested":{"id":1007,"fname":"Yingyi","lname":"Bu","age":27,"dept":"IT"}}
+{"nested":{"id":1999,"fname":"Susan","lname":"Malaika","age":42,"dept":"HR"}}
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java
index 28cb998..857f852 100644
--- a/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java
+++ b/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java
@@ -25,18 +25,8 @@
 import java.io.InputStream;
 import java.io.InputStreamReader;
 import java.rmi.RemoteException;
-import java.util.ArrayList;
-import java.util.Collections;
-import java.util.Date;
-import java.util.EnumSet;
-import java.util.HashMap;
-import java.util.Iterator;
-import java.util.LinkedHashSet;
-import java.util.List;
-import java.util.Map;
+import java.util.*;
 import java.util.Map.Entry;
-import java.util.Properties;
-import java.util.Set;
 import java.util.concurrent.ExecutorService;
 import java.util.logging.Level;
 import java.util.logging.Logger;
@@ -775,6 +765,11 @@
             List<IAType> indexFieldTypes = new ArrayList<>();
             int keyIndex = 0;
             boolean overridesFieldTypes = false;
+
+            // this set is used to detect duplicates in the specified keys in the create index statement
+            // e.g. CREATE INDEX someIdx on dataset(id,id);
+            Set<List<String>> indexKeysSet = new HashSet<>();
+
             for (Pair<List<String>, IndexedTypeExpression> fieldExpr : stmtCreateIndex.getFieldExprs()) {
                 IAType fieldType = null;
                 ARecordType subType =
@@ -799,6 +794,13 @@
                         throw new AsterixException(ErrorCode.INDEX_ILLEGAL_ENFORCED_NON_OPTIONAL,
                                 String.valueOf(fieldExpr.first));
                     }
+                    // don't allow creating an enforced index on a closed-type field, fields that are part of schema.
+                    // get the field type, if it's not null, then the field is closed-type
+                    if(stmtCreateIndex.isEnforced() &&
+                            subType.getSubFieldType(fieldExpr.first.subList(i, fieldExpr.first.size())) != null) {
+                        throw new AsterixException(ErrorCode.INDEX_ILLEGAL_ENFORCED_ON_CLOSED_FIELD,
+                                String.valueOf(fieldExpr.first));
+                    }
                     if (!isOpen) {
                         throw new AlgebricksException("Typed index on \"" + fieldExpr.first
                                 + "\" field could be created only for open datatype");
@@ -817,11 +819,28 @@
                             "Unknown type " + (fieldExpr.second == null ? fieldExpr.first : fieldExpr.second));
                 }
 
+                // try to add the key to the set of keys, if key couldn't be added, there is a duplicate
+                if(!indexKeysSet.add(fieldExpr.first)) {
+                    throw new AsterixException(ErrorCode.INDEX_ILLEGAL_REPETITIVE_FIELD,
+                            String.valueOf(fieldExpr.first));
+                }
+
                 indexFields.add(fieldExpr.first);
                 indexFieldTypes.add(fieldType);
                 ++keyIndex;
             }
 
+            // check if the index keys are the same as the PKs but in different order.
+            // e.g. PK is <id,name> while SK is <name,id>
+            // doing it the easy way, check if their sets are equal but the list are not equal
+            List<List<String>> primaryKeys = ds.getPrimaryKeys();
+            Set<List<String>> primaryKeysSet = new HashSet<>();
+            primaryKeysSet.addAll(primaryKeys);
+
+            if(!primaryKeys.equals(indexFields) && primaryKeysSet.equals(indexKeysSet)) {
+                throw new AsterixException(ErrorCode.INDEX_ILLEGAL_KEYS_SAME_AS_PK_BUT_IN_DIFFERENT_ORDER);
+            }
+
             validateIndexKeyFields(stmtCreateIndex, keySourceIndicators, aRecordType, metaRecordType, indexFields,
                     indexFieldTypes);
             // Checks whether a user is trying to create an inverted secondary index on a dataset
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>
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..a65fdfb 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  index sec_primary_idx  on LineItem (l_orderkey,l_linenumber) type btree;
\ No newline at end of file
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..a65fdfb 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  index sec_primary_idx  on LineItem (l_orderkey,l_linenumber) type btree;
\ No newline at end of file
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 ba3498a..15ccff1 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 index sec_primary_idx on t1 (id);
+
 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
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..cdd8a1e 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  index sec_primary_idx on t1(unique2) type btree;
+
 drop index t1.idx_t1_str1;
 drop index t1.idx_t1_unique1;
+drop index t1.sec_primary_idx;
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 8bf5899..b050049 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  index sec_primary_idx  on LineItem (l_orderkey,l_linenumber) type btree;
\ No newline at end of file
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 4d7f484..3b0d9cf 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  index sec_primary_idx  on test.employee (id) type btree;
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 8ec2d05..d55b13a 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  index sec_primary_idx  on SimpleGeoPlace (id) type btree;
\ No newline at end of file
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 1edf6c7..63ea09a 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  index sec_primary_idx  on LineID (l_orderkey,l_linenumber) type btree;
\ No newline at end of file
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..0d3e9d5 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  index sec_primary_idx  on LineID (l_orderkey,l_linenumber) type btree;
\ No newline at end of file
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..0d3e9d5 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  index sec_primary_idx  on LineID (l_orderkey,l_linenumber) type btree;
\ No newline at end of file
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..824903f
--- /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 index sec_primary_idx on DBLP(id);
\ No newline at end of file
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'};
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')
+;
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 2f106ac..85f4218 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  index sec_primary_idx  on LineItem (l_orderkey,l_linenumber) type btree;
\ No newline at end of file
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 80ef192..14344d5 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  index sec_primary_idx  on test.employee (id) type btree;
\ No newline at end of file
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..4eb12e8 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  index sec_primary_idx  on test.employee (id) type btree;
+
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..a65fdfb 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  index sec_primary_idx  on LineItem (l_orderkey,l_linenumber) type btree;
\ No newline at end of file
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..0085b9d 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  index sec_primary_idx  on LineItem (l_orderkey,l_linenumber) type btree;
\ No newline at end of file
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..a65fdfb 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  index sec_primary_idx  on LineItem (l_orderkey,l_linenumber) type btree;
\ No newline at end of file
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..a65fdfb 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  index sec_primary_idx  on LineItem (l_orderkey,l_linenumber) type btree;
\ No newline at end of file
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..a65fdfb 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  index sec_primary_idx  on LineItem (l_orderkey,l_linenumber) type btree;
\ No newline at end of file
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..047e5fb 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  index sec_primary_idx  on FacebookMessages2 (`message-id`) type btree;
\ No newline at end of file
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..1e99f1d 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  index sec_primary_idx  on FacebookMessages2 (`message-id`) type btree;
\ No newline at end of file
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..f0eab1c 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  index sec_primary_idx  on FacebookMessages (`message-id`) type btree;
\ No newline at end of file
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..1c532be
--- /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
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`=`|`));
+
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..61f53e3
--- /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 index sec_primary_idx on employee(id);
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
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..1eda05c
--- /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 : 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, `value`;
+
+create  index testIdx  on testDS (`value`, id);
+
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`);
+
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`);
+
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..7134e99 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  index sec_primary_idx  on LineItem (nested.l_orderkey,nested.l_linenumber) type btree;
\ No newline at end of file
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..433fe5d
--- /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;
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
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..817b1ba
--- /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  index sec_primary_idx  on employee (nested.id, nested.lname) type btree;
\ No newline at end of file
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
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;
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..b941492 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  index sec_primary_idx  on t1 (nested.unique2) type btree;
+
 drop index t1.idx_t1_str1;
 drop index t1.idx_t1_unique1;
+drop index t1.sec_primary_idx;
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..68ff682
--- /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  index sec_primary_idx  on employee (nested.id) type btree;
\ No newline at end of file
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;
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
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..3c2898b
--- /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;
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`));
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..d08884f
--- /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  index sec_primary_idx  on employee (nested.id, nested.lname) type btree;
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'}};
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;
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..9e77eb8
--- /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  index sec_primary_idx  on employee (nested.id) type btree;
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
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
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..35fb44d
--- /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;
+
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
+;
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..2681614
--- /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  index sec_primary_idx  on employee (nested.id, nested.lname) type btree;
+
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'));
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..a3f9c95 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  index sec_primary_idx  on LineItem (l_orderkey,l_linenumber) type btree;
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 eb52258..d3886f2 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  index sec_primary_idx on t1 (id);
+
 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;
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..be7177c 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  index sec_primary_idx  on t1 (unique2) type btree;
+
 drop index t1.idx_t1_str1;
 drop index t1.idx_t1_unique1;
+drop index t1.sec_primary_idx;
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 ef45232..3b46d35 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  index sec_primary_idx on LineItem (l_orderkey,l_linenumber) type btree;
\ No newline at end of file
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..8800961 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  index sec_primary_idx  on test.employee (id) type btree;
+
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 4532f3c..cf0b0f8 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  index sec_primary_idx  on test.employee (id) type btree;
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 751ba27..3d18b40 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
@@ -43,4 +43,5 @@
 primary key `message-id` with filter on `send-time`;
 
 create index AutherIdx on FilteredFacebookMessages(`author-id`);
-create index MessageIdx on FilteredFacebookMessages(message);
\ No newline at end of file
+create index MessageIdx on FilteredFacebookMessages(message);
+create index sec_primary_idx on FilteredFacebookMessages(`message-id`);
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..4c5e95f 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 index sec_primary_idx on UpsertTo(id);
\ No newline at end of file
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 f9649c7..ec6a5a0 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 index sec_primary_idx on UpsertTo(id);
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 b232d8f..5db7e97 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 index sec_primary_idx on UpsertTo(id);
 create dataset UpsertFrom(TestType) primary key id;
\ No newline at end of file
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
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
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
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
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
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
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" }
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 47c0529..f776521 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -1570,6 +1570,11 @@
   </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>
@@ -1742,6 +1747,11 @@
           </compilation-unit>
         </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>
@@ -2921,6 +2931,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>
@@ -3455,18 +3470,39 @@
       </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>
+          <expected-error>Cannot create index with the same keys as the primary keys but in different order.</expected-error>
+        </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 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">
@@ -3987,6 +4023,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>
@@ -4152,6 +4193,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>
@@ -4159,6 +4205,11 @@
     <test-case FilePath="nested-index-dml">
       <compilation-unit name="insert-into-empty-dataset-with-index">
         <output-dir compare="Text">insert-into-empty-dataset-with-index</output-dir>
+      </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">
@@ -4172,11 +4223,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>
diff --git a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java
index 94fe951..00e697f 100644
--- a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java
+++ b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/exceptions/ErrorCode.java
@@ -121,6 +121,9 @@
     public static final int UPGRADE_FAILED_LOCK_WAS_NOT_ACQUIRED = 1047;
     public static final int DOWNGRADE_FAILED_LOCK_WAS_NOT_ACQUIRED = 1048;
     public static final int LOCK_WAS_ACQUIRED_DIFFERENT_OPERATION = 1049;
+    public static final int INDEX_ILLEGAL_ENFORCED_ON_CLOSED_FIELD = 1050;
+    public static final int INDEX_ILLEGAL_REPETITIVE_FIELD = 1051;
+    public static final int INDEX_ILLEGAL_KEYS_SAME_AS_PK_BUT_IN_DIFFERENT_ORDER = 1052;
 
     // Feed errors
     public static final int DATAFLOW_ILLEGAL_STATE = 3001;
diff --git a/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties b/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
index 4cc06a6..0d974fd 100644
--- a/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
+++ b/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
@@ -107,6 +107,9 @@
 1047 = Metadata lock cannot be upgraded! because it was not acquired before
 1048 = Metadata lock cannot be downgraded! because it was not acquired before
 1049 = Metadata lock cannot be acquired for %1$s since it is already acquired for %2$s
+1050 = Cannot create enforced index on \"%1$s\" field. The field is closed type.
+1051 = Cannot create index with same field \"%1$s\" specified more than once.
+1052 = Cannot create index with the same keys as the primary keys but in different order.
 
 # Feed Errors
 3001 = Illegal state.
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/BTreeResourceFactoryProvider.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/BTreeResourceFactoryProvider.java
index bfc6a8e..f380d7f 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/BTreeResourceFactoryProvider.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/BTreeResourceFactoryProvider.java
@@ -115,9 +115,11 @@
             return FilesIndexDescription.EXTERNAL_FILE_INDEX_TYPE_TRAITS;
         }
         int numPrimaryKeys = dataset.getPrimaryKeys().size();
-        int numSecondaryKeys = index.getKeyFieldNames().size();
+        int numSecondaryKeys = IndexUtil.getNumOfPhysicalSecondaryKeys(dataset, index);
         ITypeTraitProvider typeTraitProvider = metadataProvider.getStorageComponentProvider().getTypeTraitProvider();
         ITypeTraits[] secondaryTypeTraits = new ITypeTraits[numSecondaryKeys + numPrimaryKeys];
+
+        // this loop will be skipped if the index is secondary primary index
         for (int i = 0; i < numSecondaryKeys; i++) {
             ARecordType sourceType;
             List<Integer> keySourceIndicators = index.getKeyFieldSourceIndicators();
@@ -149,11 +151,13 @@
             return FilesIndexDescription.FILES_INDEX_COMP_FACTORIES;
         }
         int numPrimaryKeys = dataset.getPrimaryKeys().size();
-        int numSecondaryKeys = index.getKeyFieldNames().size();
+        int numSecondaryKeys = IndexUtil.getNumOfPhysicalSecondaryKeys(dataset, index);
         IBinaryComparatorFactoryProvider cmpFactoryProvider =
                 metadataProvider.getStorageComponentProvider().getComparatorFactoryProvider();
         IBinaryComparatorFactory[] secondaryCmpFactories =
                 new IBinaryComparatorFactory[numSecondaryKeys + numPrimaryKeys];
+
+        // this loop will be skipped if the index is secondary primary index
         for (int i = 0; i < numSecondaryKeys; i++) {
             ARecordType sourceType;
             List<Integer> keySourceIndicators = index.getKeyFieldSourceIndicators();
@@ -184,6 +188,7 @@
                 return new int[] { index.getKeyFieldNames().size() };
             }
         }
+        //TODO: should the bloom filter fields be set for secondary indexes?
         int numKeys = index.getKeyFieldNames().size();
         int[] bloomFilterKeyFields = new int[numKeys];
         for (int i = 0; i < numKeys; i++) {
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/MetadataProvider.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/MetadataProvider.java
index 7d28a06..d30f638 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/MetadataProvider.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/declared/MetadataProvider.java
@@ -72,6 +72,7 @@
 import org.apache.asterix.metadata.feeds.FeedMetadataUtil;
 import org.apache.asterix.metadata.lock.ExternalDatasetsRegistry;
 import org.apache.asterix.metadata.utils.DatasetUtil;
+import org.apache.asterix.metadata.utils.IndexUtil;
 import org.apache.asterix.metadata.utils.MetadataConstants;
 import org.apache.asterix.metadata.utils.SplitsAndConstraintsUtil;
 import org.apache.asterix.om.functions.BuiltinFunctions;
@@ -548,6 +549,7 @@
     }
 
     @Override
+    // TODO:ali check this
     public Pair<IOperatorDescriptor, AlgebricksPartitionConstraint> getWriteResultRuntime(
             IDataSource<DataSourceId> dataSource, IOperatorSchema propagatedSchema, List<LogicalVariable> keys,
             LogicalVariable payload, List<LogicalVariable> additionalNonKeyFields, JobGenContext context,
@@ -885,7 +887,7 @@
 
         return new Pair<>(dataScanner, constraint);
     }
-
+    // TODO: this method is never used.
     private Pair<IBinaryComparatorFactory[], ITypeTraits[]> getComparatorFactoriesAndTypeTraitsOfSecondaryBTreeIndex(
             List<List<String>> sidxKeyFieldNames, List<IAType> sidxKeyFieldTypes, List<List<String>> pidxKeyFieldNames,
             ARecordType recType, DatasetType dsType, boolean hasMeta, List<Integer> primaryIndexKeyIndicators,
@@ -1059,8 +1061,9 @@
         Dataset dataset = MetadataManagerUtil.findExistingDataset(mdTxnCtx, dataverseName, datasetName);
         boolean temp = dataset.getDatasetDetails().isTemp();
         isTemporaryDatasetWriteJob = isTemporaryDatasetWriteJob && temp;
+        Index secondaryIndex = MetadataManager.INSTANCE.getIndex(mdTxnCtx, dataverseName, datasetName, indexName);
 
-        int numKeys = primaryKeys.size() + secondaryKeys.size();
+        int numKeys = primaryKeys.size() + IndexUtil.getNumOfPhysicalSecondaryKeys(dataset, secondaryIndex);
         int numFilterFields = DatasetUtil.getFilterField(dataset) == null ? 0 : 1;
 
         // generate field permutations
@@ -1068,15 +1071,21 @@
         int[] modificationCallbackPrimaryKeyFields = new int[primaryKeys.size()];
         int i = 0;
         int j = 0;
-        for (LogicalVariable varKey : secondaryKeys) {
-            int idx = propagatedSchema.findVariable(varKey);
-            fieldPermutation[i] = idx;
-            i++;
+
+        // skip the secondary keys if this index is secondary primary since the index has only PK
+        if(!IndexUtil.isSecondaryPrimary(dataset, secondaryIndex)) {
+            for (LogicalVariable varKey : secondaryKeys) {
+                int idx = propagatedSchema.findVariable(varKey);
+                fieldPermutation[i] = idx;
+                i++;
+            }
         }
+
         for (LogicalVariable varKey : primaryKeys) {
             int idx = propagatedSchema.findVariable(varKey);
             fieldPermutation[i] = idx;
             modificationCallbackPrimaryKeyFields[j] = i;
+
             i++;
             j++;
         }
@@ -1091,16 +1100,22 @@
             // generate field permutations for prev record
             prevFieldPermutation = new int[numKeys + numFilterFields];
             int k = 0;
-            for (LogicalVariable varKey : prevSecondaryKeys) {
-                int idx = propagatedSchema.findVariable(varKey);
-                prevFieldPermutation[k] = idx;
-                k++;
+
+            // skip the secondary keys if this index is secondary primary since the index has only PK
+            if(!IndexUtil.isSecondaryPrimary(dataset, secondaryIndex)) {
+                for (LogicalVariable varKey : prevSecondaryKeys) {
+                    int idx = propagatedSchema.findVariable(varKey);
+                    prevFieldPermutation[k] = idx;
+                    k++;
+                }
             }
+
             for (LogicalVariable varKey : primaryKeys) {
                 int idx = propagatedSchema.findVariable(varKey);
                 prevFieldPermutation[k] = idx;
                 k++;
             }
+
             // Filter can only be one field!
             if (numFilterFields > 0) {
                 int idx = propagatedSchema.findVariable(prevAdditionalFilteringKeys.get(0));
@@ -1109,8 +1124,6 @@
         }
         try {
             // Index parameters.
-            Index secondaryIndex = MetadataManager.INSTANCE.getIndex(mdTxnCtx, dataset.getDataverseName(),
-                    dataset.getDatasetName(), indexName);
             Pair<IFileSplitProvider, AlgebricksPartitionConstraint> splitsAndConstraint =
                     getSplitProviderAndConstraints(dataset, secondaryIndex.getIndexName());
             // prepare callback
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/IndexUtil.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/IndexUtil.java
index 411f866..8f4c648 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/IndexUtil.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/IndexUtil.java
@@ -63,12 +63,54 @@
                 id.getPrimaryKeyType(), false, false, true, dataset.getPendingOp());
     }
 
+    /**
+     *  This method should be used when dealing with some cases for secondary B-Trees.
+     *  Normally, a secondary BTree index is composed of {SK,PK}. However, for secondary primary indexes
+     *  (indexes built on the primary keys of the dataset) consists of only {PK} and this method returns 0 for this
+     *  special index. Inherently, though, SK and PK are always there.
+     *  Index object will always contain the SK since it is part of the metadata.
+     *  Dataset object will also always contain PK.
+     *  Assumption is neither of the dataset or index is null
+     * @param dataset   the dataset that contains the index
+     * @param index     the BTree index
+     * @return          number of SK that are actually stored
+     */
+    public static int getNumOfPhysicalSecondaryKeys(Dataset dataset, Index index) {
+        if(IndexUtil.isSecondaryPrimary(dataset, index))
+            return 0;
+        else
+            return index.getKeyFieldNames().size();
+    }
+
+    /**
+     * an index is considered secondary primary if all of these conditions are met:
+     * 1. it is not a primary index
+     * 2. the dataset must be internal
+     * 3. the index is a BTree index
+     * 4. the primary keys of the dataset and the keys of the index are equal
+     * Assumption is neither of the dataset or index is null
+     * @param dataset   the dataset that contains the index
+     * @param index     the BTree index
+     * @return          whether this index is secondary primary
+     */
+    public static boolean isSecondaryPrimary(Dataset dataset, Index index) {
+        List<List<String>> secondaryIndexKeys = index.getKeyFieldNames();
+        List<List<String>> primaryIndexKeys = dataset.getPrimaryKeys();
+
+        if(index.isPrimaryIndex() || secondaryIndexKeys.size() != primaryIndexKeys.size()
+                || dataset.getDatasetType() == DatasetConfig.DatasetType.EXTERNAL || index.getIndexType() != DatasetConfig.IndexType.BTREE)
+            return false;
+
+        // TODO: should the types be checked as well?
+        return primaryIndexKeys.equals(secondaryIndexKeys);
+    }
+
     public static int[] getBtreeFieldsIfFiltered(Dataset dataset, Index index) throws AlgebricksException {
         if (index.isPrimaryIndex()) {
             return DatasetUtil.createBTreeFieldsWhenThereisAFilter(dataset);
         }
         int numPrimaryKeys = dataset.getPrimaryKeys().size();
-        int numSecondaryKeys = index.getKeyFieldNames().size();
+        int numSecondaryKeys = getNumOfPhysicalSecondaryKeys(dataset, index);
         int[] btreeFields = new int[numSecondaryKeys + numPrimaryKeys];
         for (int k = 0; k < btreeFields.length; k++) {
             btreeFields[k] = k;
@@ -82,7 +124,7 @@
             return empty;
         }
         int numPrimaryKeys = dataset.getPrimaryKeys().size();
-        int numSecondaryKeys = index.getKeyFieldNames().size();
+        int numSecondaryKeys = getNumOfPhysicalSecondaryKeys(dataset, index);
         switch (index.getIndexType()) {
             case BTREE:
                 return new int[] { numPrimaryKeys + numSecondaryKeys };
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryBTreeOperationsHelper.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryBTreeOperationsHelper.java
index 75e714b..445fbd8 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryBTreeOperationsHelper.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryBTreeOperationsHelper.java
@@ -181,6 +181,23 @@
         return index.getKeyFieldNames().size();
     }
 
+    /**
+     *      ======
+     *     |  SK  |             Bloom filter
+     *      ======
+     *      ====== ======
+     *     |  SK  |  PK  |      comparators, type traits
+     *      ====== ======
+     *      ====== ........
+     *     |  SK  | Filter |    field access evaluators
+     *      ====== ........
+     *      ====== ====== ........
+     *     |  SK  |  PK  | Filter |   record fields
+     *      ====== ====== ........
+     *      ====== ========= ........ ........
+     *     |  PK  | Payload |  Meta  | Filter | enforced record
+     *      ====== ========= ........ ........
+     */
     @Override
     @SuppressWarnings("rawtypes")
     protected void setSecondaryRecDescAndComparators() throws AlgebricksException {
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryCorrelatedTreeIndexOperationsHelper.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryCorrelatedTreeIndexOperationsHelper.java
index 171d72e..27faa7b 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryCorrelatedTreeIndexOperationsHelper.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryCorrelatedTreeIndexOperationsHelper.java
@@ -303,8 +303,15 @@
         SecondaryIndexOperationsHelper indexOperationsHelper = null;
         switch (index.getIndexType()) {
             case BTREE:
-                indexOperationsHelper =
-                        new SecondaryCorrelatedBTreeOperationsHelper(dataset, index, physOptConf, metadataProvider);
+                if (IndexUtil.isSecondaryPrimary(dataset, index) && dataset.getDatasetType() == DatasetType.INTERNAL) {
+                    indexOperationsHelper = new SecondaryPrimaryCorrelatedBTreeOperationsHelper(dataset, index, physOptConf,
+                            metadataProvider);
+                }
+                else {
+                    indexOperationsHelper =
+                            new SecondaryCorrelatedBTreeOperationsHelper(dataset, index, physOptConf, metadataProvider);
+                }
+
                 break;
             case RTREE:
                 indexOperationsHelper =
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryIndexOperationsHelper.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryIndexOperationsHelper.java
index 8fc9ed7..91c1c4e 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryIndexOperationsHelper.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryIndexOperationsHelper.java
@@ -143,8 +143,15 @@
         SecondaryIndexOperationsHelper indexOperationsHelper;
         switch (index.getIndexType()) {
             case BTREE:
-                indexOperationsHelper = new SecondaryBTreeOperationsHelper(dataset, index, physOptConf,
-                        metadataProvider);
+                if (IndexUtil.isSecondaryPrimary(dataset, index) && dataset.getDatasetType() == DatasetType.INTERNAL) {
+                    indexOperationsHelper = new SecondaryPrimaryBTreeOperationsHelper(dataset, index, physOptConf,
+                            metadataProvider);
+                }
+                else {
+                    indexOperationsHelper = new SecondaryBTreeOperationsHelper(dataset, index, physOptConf,
+                            metadataProvider);
+                }
+
                 break;
             case RTREE:
                 indexOperationsHelper = new SecondaryRTreeOperationsHelper(dataset, index, physOptConf,
@@ -176,11 +183,9 @@
         payloadSerde = SerializerDeserializerProvider.INSTANCE.getSerializerDeserializer(itemType);
         metaSerde =
                 metaType == null ? null : SerializerDeserializerProvider.INSTANCE.getSerializerDeserializer(metaType);
-        Pair<IFileSplitProvider, AlgebricksPartitionConstraint> secondarySplitsAndConstraint =
-                metadataProvider.getSplitProviderAndConstraints(dataset, index.getIndexName());
-        secondaryFileSplitProvider = secondarySplitsAndConstraint.first;
-        secondaryPartitionConstraint = secondarySplitsAndConstraint.second;
         numPrimaryKeys = dataset.getPrimaryKeys().size();
+
+        // primary record information is only valid in the context of internal datasets. Skip for external datasets.
         if (dataset.getDatasetType() == DatasetType.INTERNAL) {
             filterFieldName = DatasetUtil.getFilterField(dataset);
             if (filterFieldName != null) {
@@ -194,7 +199,14 @@
             primaryPartitionConstraint = primarySplitsAndConstraint.second;
             setPrimaryRecDescAndComparators();
         }
+
+        // secondary record information
+        Pair<IFileSplitProvider, AlgebricksPartitionConstraint> secondarySplitsAndConstraint =
+                metadataProvider.getSplitProviderAndConstraints(dataset, index.getIndexName());
+        secondaryFileSplitProvider = secondarySplitsAndConstraint.first;
+        secondaryPartitionConstraint = secondarySplitsAndConstraint.second;
         setSecondaryRecDescAndComparators();
+
         numElementsHint = metadataProvider.getCardinalityPerPartitionHint(dataset);
         Pair<ILSMMergePolicyFactory, Map<String, String>> compactionInfo =
                 DatasetUtil.getMergePolicyFactory(dataset, metadataProvider.getMetadataTxnContext());
@@ -206,12 +218,13 @@
     }
 
     protected void setFilterTypeTraitsAndComparators() throws AlgebricksException {
+        // all of these instance variables are not used. Any reasons?
         filterTypeTraits = new ITypeTraits[numFilterFields];
         filterCmpFactories = new IBinaryComparatorFactory[numFilterFields];
         secondaryFilterFields = new int[numFilterFields];
         primaryFilterFields = new int[numFilterFields];
         primaryBTreeFields = new int[numPrimaryKeys + 1];
-        secondaryBTreeFields = new int[index.getKeyFieldNames().size() + numPrimaryKeys];
+        secondaryBTreeFields = new int[getNumSecondaryKeys() + numPrimaryKeys];
         for (int i = 0; i < primaryBTreeFields.length; i++) {
             primaryBTreeFields[i] = i;
         }
@@ -260,7 +273,6 @@
     }
 
     protected abstract void setSecondaryRecDescAndComparators() throws AlgebricksException;
-
 
     protected AlgebricksMetaOperatorDescriptor createAssignOp(JobSpecification spec, int numSecondaryKeyFields,
             RecordDescriptor secondaryRecDesc) throws AlgebricksException {
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryPrimaryBTreeOperationsHelper.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryPrimaryBTreeOperationsHelper.java
new file mode 100644
index 0000000..98a3ec3
--- /dev/null
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryPrimaryBTreeOperationsHelper.java
@@ -0,0 +1,205 @@
+/*
+ * 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.
+ */
+package org.apache.asterix.metadata.utils;
+
+import org.apache.asterix.common.config.GlobalConfig;
+import org.apache.asterix.common.transactions.JobId;
+import org.apache.asterix.metadata.declared.MetadataProvider;
+import org.apache.asterix.metadata.entities.Dataset;
+import org.apache.asterix.metadata.entities.Index;
+import org.apache.asterix.om.types.ARecordType;
+import org.apache.asterix.om.types.IAType;
+import org.apache.asterix.runtime.utils.RuntimeUtils;
+import org.apache.hyracks.algebricks.common.constraints.AlgebricksPartitionConstraintHelper;
+import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
+import org.apache.hyracks.algebricks.common.utils.Pair;
+import org.apache.hyracks.algebricks.core.jobgen.impl.ConnectorPolicyAssignmentPolicy;
+import org.apache.hyracks.algebricks.core.rewriter.base.PhysicalOptimizationConfig;
+import org.apache.hyracks.algebricks.data.ISerializerDeserializerProvider;
+import org.apache.hyracks.algebricks.runtime.base.IPushRuntimeFactory;
+import org.apache.hyracks.algebricks.runtime.base.IScalarEvaluatorFactory;
+import org.apache.hyracks.algebricks.runtime.operators.base.SinkRuntimeFactory;
+import org.apache.hyracks.algebricks.runtime.operators.meta.AlgebricksMetaOperatorDescriptor;
+import org.apache.hyracks.algebricks.runtime.operators.std.AssignRuntimeFactory;
+import org.apache.hyracks.api.dataflow.IOperatorDescriptor;
+import org.apache.hyracks.api.dataflow.value.IBinaryComparatorFactory;
+import org.apache.hyracks.api.dataflow.value.ISerializerDeserializer;
+import org.apache.hyracks.api.dataflow.value.ITypeTraits;
+import org.apache.hyracks.api.dataflow.value.RecordDescriptor;
+import org.apache.hyracks.api.job.JobSpecification;
+import org.apache.hyracks.dataflow.std.connectors.OneToOneConnectorDescriptor;
+import org.apache.hyracks.dataflow.std.sort.ExternalSortOperatorDescriptor;
+import org.apache.hyracks.storage.am.common.dataflow.IIndexDataflowHelperFactory;
+import org.apache.hyracks.storage.am.common.dataflow.IndexDataflowHelperFactory;
+import org.apache.hyracks.storage.am.common.dataflow.TreeIndexBulkLoadOperatorDescriptor;
+
+import java.util.List;
+
+/**
+ * This class is used to handle creating/loading/dropping/compacting secondary primary indexes.
+ * Secondary primary indexes are secondary B-Tree indexes that have only {PK} rather than {SK,PK}. They are used to optimize
+ * queries like count(*). Even though SK is missing in the B-Tree itself, the index object it is supporting still has
+ * "something" in the instance variable keyFieldNames. It holds the PK.
+ */
+public class SecondaryPrimaryBTreeOperationsHelper extends SecondaryTreeIndexOperationsHelper {
+
+    protected SecondaryPrimaryBTreeOperationsHelper(Dataset dataset, Index index, PhysicalOptimizationConfig physOptConf,
+            MetadataProvider metadataProvider) throws AlgebricksException {
+        super(dataset, index, physOptConf, metadataProvider);
+    }
+
+    @Override
+    protected int getNumSecondaryKeys() {
+        return 0;
+    }
+
+    /**
+     *      ======
+     *     |  PK  |     comparators, type traits, Bloom filter
+     *      ======
+     *      ====== ........
+     *     |  PK  | Filter |    field access evaluators, record fields
+     *      ====== ........
+     */
+    @Override
+    @SuppressWarnings("rawtypes")
+    protected void setSecondaryRecDescAndComparators() throws AlgebricksException {
+        // record information
+        secondaryComparatorFactories = new IBinaryComparatorFactory[numPrimaryKeys];
+        secondaryTypeTraits = new ITypeTraits[numPrimaryKeys];
+        secondaryBloomFilterKeyFields = new int[numPrimaryKeys];
+        secondaryFieldAccessEvalFactories = new IScalarEvaluatorFactory[numPrimaryKeys + numFilterFields];
+        ISerializerDeserializer[] secondaryRecFields = new ISerializerDeserializer[numPrimaryKeys + numFilterFields];
+
+        // providers
+        ISerializerDeserializerProvider serdeProvider = metadataProvider.getFormat().getSerdeProvider();
+
+        // enforced record field initializing not needed.
+
+        // set the comparators, type traits, Bloom filter, fields serde
+        for (int i = 0; i < numPrimaryKeys; i++) {
+            ARecordType sourceType;
+            int sourceColumn;
+            List<Integer> keySourceIndicators = index.getKeyFieldSourceIndicators();
+
+            if (keySourceIndicators == null || keySourceIndicators.get(i) == 0) {
+                sourceType = itemType;
+                sourceColumn = numPrimaryKeys;
+            } else {
+                sourceType = metaType;
+                sourceColumn = numPrimaryKeys + 1;
+            }
+
+            secondaryFieldAccessEvalFactories[i] = metadataProvider.getFormat().getFieldAccessEvaluatorFactory(
+                    sourceType, index.getKeyFieldNames().get(i), sourceColumn);
+            secondaryRecFields[i] = primaryRecDesc.getFields()[i];
+            secondaryComparatorFactories[i] = primaryComparatorFactories[i];
+            secondaryTypeTraits[i] = primaryRecDesc.getTypeTraits()[i];
+            secondaryBloomFilterKeyFields[i] = i;
+        }
+
+        // if there is a filter, set the last cell.
+        if (numFilterFields > 0) {
+            secondaryFieldAccessEvalFactories[numPrimaryKeys] = metadataProvider.getFormat()
+                    .getFieldAccessEvaluatorFactory(itemType, filterFieldName, numPrimaryKeys);
+            Pair<IAType, Boolean> keyTypePair = Index.getNonNullableKeyFieldType(filterFieldName, itemType);
+            IAType type = keyTypePair.first;
+            ISerializerDeserializer serde = serdeProvider.getSerializerDeserializer(type);
+            secondaryRecFields[numPrimaryKeys] = serde;
+        }
+
+        secondaryRecDesc = new RecordDescriptor(secondaryRecFields, secondaryTypeTraits);
+    }
+
+    @Override
+    public JobSpecification buildLoadingJobSpec() throws AlgebricksException {
+        JobSpecification spec = RuntimeUtils.createJobSpecification(metadataProvider.getApplicationContext());
+
+        int[] fieldPermutation = new int[numPrimaryKeys + numFilterFields];
+        for (int i = 0; i < fieldPermutation.length; i++) {
+            fieldPermutation[i] = i;
+        }
+
+        IIndexDataflowHelperFactory dataflowHelperFactory = new IndexDataflowHelperFactory(
+                metadataProvider.getStorageComponentProvider().getStorageManager(), secondaryFileSplitProvider);
+
+        // create dummy key provider for feeding the primary index scan.
+        IOperatorDescriptor keyProviderOp = DatasetUtil.createDummyKeyProviderOp(spec, dataset,
+                metadataProvider);
+        JobId jobId = IndexUtil.bindJobEventListener(spec, metadataProvider);
+
+        // create primary index scan op.
+        IOperatorDescriptor primaryScanOp = DatasetUtil.createPrimaryIndexScanOp(spec, metadataProvider, dataset,
+                jobId);
+
+        // assign op.
+        AlgebricksMetaOperatorDescriptor asterixAssignOp =
+                createAssignOp(spec, numPrimaryKeys, secondaryRecDesc);
+
+        // sort by secondary keys (that is primary keys, in fact). TODO: is sort operator needed?
+        ExternalSortOperatorDescriptor sortOp = createSortOp(spec, secondaryComparatorFactories, secondaryRecDesc);
+
+        // create secondary BTree bulk load op.
+        TreeIndexBulkLoadOperatorDescriptor secondaryBulkLoadOp = createTreeIndexBulkLoadOp(spec, fieldPermutation,
+                dataflowHelperFactory, GlobalConfig.DEFAULT_TREE_FILL_FACTOR);
+
+        AlgebricksMetaOperatorDescriptor metaOp = new AlgebricksMetaOperatorDescriptor(spec, 1, 0,
+                new IPushRuntimeFactory[] { new SinkRuntimeFactory() },
+                new RecordDescriptor[] { secondaryRecDesc });
+
+        // Connect the operators.
+        spec.connect(new OneToOneConnectorDescriptor(spec), keyProviderOp, 0, primaryScanOp, 0);
+        spec.connect(new OneToOneConnectorDescriptor(spec), primaryScanOp, 0, asterixAssignOp, 0);
+        spec.connect(new OneToOneConnectorDescriptor(spec), asterixAssignOp, 0, sortOp, 0);
+        spec.connect(new OneToOneConnectorDescriptor(spec), sortOp, 0, secondaryBulkLoadOp, 0);
+        spec.connect(new OneToOneConnectorDescriptor(spec), secondaryBulkLoadOp, 0, metaOp, 0);
+        spec.addRoot(metaOp);
+        spec.setConnectorPolicyAssignmentPolicy(new ConnectorPolicyAssignmentPolicy());
+        return spec;
+    }
+
+    @Override
+    protected AlgebricksMetaOperatorDescriptor createAssignOp(JobSpecification spec, int numOfPrimaryKeys,
+            RecordDescriptor secondaryRecDesc) throws AlgebricksException {
+        int[] outColumns = new int[numOfPrimaryKeys + numFilterFields];
+        int[] projectionList = new int[numOfPrimaryKeys + numFilterFields];
+        int i;
+
+        for (i = 0; i < numOfPrimaryKeys; i++) {
+            outColumns[i] = i; // TODO:ali check this
+            projectionList[i] = i;
+        }
+
+        // if there is a filter, set the last cell
+        if (numFilterFields > 0) {
+            projectionList[numOfPrimaryKeys] = i;
+        }
+
+        IScalarEvaluatorFactory[] sefs = new IScalarEvaluatorFactory[secondaryFieldAccessEvalFactories.length];
+        for (i = 0; i < secondaryFieldAccessEvalFactories.length; ++i) {
+            sefs[i] = secondaryFieldAccessEvalFactories[i];
+        }
+        AssignRuntimeFactory assign = new AssignRuntimeFactory(outColumns, sefs, projectionList);
+        AlgebricksMetaOperatorDescriptor asterixAssignOp = new AlgebricksMetaOperatorDescriptor(spec, 1, 1,
+                new IPushRuntimeFactory[] { assign }, new RecordDescriptor[] { secondaryRecDesc });
+        AlgebricksPartitionConstraintHelper.setPartitionConstraintInJobSpec(spec, asterixAssignOp,
+                primaryPartitionConstraint);
+        return asterixAssignOp;
+    }
+}
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryPrimaryCorrelatedBTreeOperationsHelper.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryPrimaryCorrelatedBTreeOperationsHelper.java
new file mode 100644
index 0000000..d4e780a
--- /dev/null
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryPrimaryCorrelatedBTreeOperationsHelper.java
@@ -0,0 +1,218 @@
+/*
+ * 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.
+ */
+package org.apache.asterix.metadata.utils;
+
+import org.apache.asterix.common.transactions.JobId;
+import org.apache.asterix.metadata.declared.MetadataProvider;
+import org.apache.asterix.metadata.entities.Dataset;
+import org.apache.asterix.metadata.entities.Index;
+import org.apache.asterix.om.types.ARecordType;
+import org.apache.asterix.om.types.IAType;
+import org.apache.asterix.runtime.operators.LSMSecondaryIndexBulkLoadOperatorDescriptor;
+import org.apache.asterix.runtime.utils.RuntimeUtils;
+import org.apache.hyracks.algebricks.common.constraints.AlgebricksPartitionConstraintHelper;
+import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
+import org.apache.hyracks.algebricks.common.utils.Pair;
+import org.apache.hyracks.algebricks.core.jobgen.impl.ConnectorPolicyAssignmentPolicy;
+import org.apache.hyracks.algebricks.core.rewriter.base.PhysicalOptimizationConfig;
+import org.apache.hyracks.algebricks.data.ISerializerDeserializerProvider;
+import org.apache.hyracks.algebricks.runtime.base.IPushRuntimeFactory;
+import org.apache.hyracks.algebricks.runtime.base.IScalarEvaluatorFactory;
+import org.apache.hyracks.algebricks.runtime.operators.base.SinkRuntimeFactory;
+import org.apache.hyracks.algebricks.runtime.operators.meta.AlgebricksMetaOperatorDescriptor;
+import org.apache.hyracks.algebricks.runtime.operators.std.AssignRuntimeFactory;
+import org.apache.hyracks.api.dataflow.IOperatorDescriptor;
+import org.apache.hyracks.api.dataflow.value.IBinaryComparatorFactory;
+import org.apache.hyracks.api.dataflow.value.ISerializerDeserializer;
+import org.apache.hyracks.api.dataflow.value.ITypeTraits;
+import org.apache.hyracks.api.dataflow.value.RecordDescriptor;
+import org.apache.hyracks.api.job.JobSpecification;
+import org.apache.hyracks.dataflow.std.connectors.OneToOneConnectorDescriptor;
+import org.apache.hyracks.dataflow.std.sort.ExternalSortOperatorDescriptor;
+
+import java.util.List;
+
+/**
+ * This is the correlated version of secondary primary B-Tree index. It is used to handle creating/loading/dropping/compacting
+ * secondary primary indexes.
+ * Secondary primary indexes are secondary B-Tree indexes that have only {PK} rather than {SK,PK}. They are used to optimize
+ * queries like count(*). Even though SK is missing in the B-Tree itself, the index object it is supporting still has
+ * "something" in the instance variable keyFieldNames. It holds the PK.
+ */
+public class SecondaryPrimaryCorrelatedBTreeOperationsHelper extends SecondaryCorrelatedTreeIndexOperationsHelper {
+
+    protected SecondaryPrimaryCorrelatedBTreeOperationsHelper(Dataset dataset, Index index,
+            PhysicalOptimizationConfig physOptConf, MetadataProvider metadataProvider) throws AlgebricksException {
+        super(dataset, index, physOptConf, metadataProvider);
+    }
+
+    @Override
+    protected int getNumSecondaryKeys() {
+        return 0;
+    }
+
+    /**
+     *      ======
+     *     |  PK  |     comparators, type traits, Bloom filter
+     *      ======
+     *      ====== ........
+     *     |  PK  | Filter |    field access evaluators, record fields
+     *      ====== ........
+     */
+    @Override
+    @SuppressWarnings("rawtypes")
+    protected void setSecondaryRecDescAndComparators() throws AlgebricksException {
+        // record information
+        secondaryComparatorFactories = new IBinaryComparatorFactory[numPrimaryKeys];
+        secondaryBloomFilterKeyFields = new int[numPrimaryKeys];
+        secondaryTypeTraits = new ITypeTraits[numPrimaryKeys];
+        secondaryFieldAccessEvalFactories = new IScalarEvaluatorFactory[numPrimaryKeys + numFilterFields];
+        ISerializerDeserializer[] secondaryRecFields = new ISerializerDeserializer[numPrimaryKeys + numFilterFields];
+
+        // providers
+        ISerializerDeserializerProvider serdeProvider = metadataProvider.getFormat().getSerdeProvider();
+
+        int recordColumn = NUM_TAG_FIELDS + numPrimaryKeys;
+
+        for (int i = 0; i < numPrimaryKeys; i++) {
+            ARecordType sourceType;
+            int sourceColumn;
+            List<Integer> keySourceIndicators = index.getKeyFieldSourceIndicators();
+            if (keySourceIndicators == null || keySourceIndicators.get(i) == 0) {
+                sourceType = itemType;
+                sourceColumn = recordColumn;
+            } else {
+                sourceType = metaType;
+                sourceColumn = recordColumn + 1;
+            }
+            secondaryFieldAccessEvalFactories[i] = metadataProvider.getFormat().getFieldAccessEvaluatorFactory(
+                    sourceType, index.getKeyFieldNames().get(i), sourceColumn);
+            secondaryRecFields[i] = primaryRecDesc.getFields()[i];;
+            secondaryComparatorFactories[i] = primaryComparatorFactories[i];
+            secondaryTypeTraits[i] = primaryRecDesc.getTypeTraits()[i];
+            secondaryBloomFilterKeyFields[i] = i;
+        }
+
+        // if there is a filter, set the last cell.
+        if (numFilterFields > 0) {
+            Pair<IAType, Boolean> keyTypePair = Index.getNonNullableKeyFieldType(filterFieldName, itemType);
+            IAType type = keyTypePair.first;
+            ISerializerDeserializer serde = serdeProvider.getSerializerDeserializer(type);
+            secondaryRecFields[numPrimaryKeys] = serde;
+            secondaryFieldAccessEvalFactories[numPrimaryKeys] = metadataProvider.getFormat()
+                    .getFieldAccessEvaluatorFactory(itemType, filterFieldName, recordColumn);
+
+        }
+        secondaryRecDesc = new RecordDescriptor(secondaryRecFields, secondaryTypeTraits);
+    }
+
+    @Override
+    public JobSpecification buildLoadingJobSpec() throws AlgebricksException {
+        JobSpecification spec = RuntimeUtils.createJobSpecification(metadataProvider.getApplicationContext());
+
+        // create dummy key provider for feeding the primary index scan.
+        JobId jobId = IndexUtil.bindJobEventListener(spec, metadataProvider);
+
+        // create dummy key provider for feeding the primary index scan.
+        IOperatorDescriptor keyProviderOp = DatasetUtil.createDummyKeyProviderOp(spec, dataset, metadataProvider);
+
+        // Create primary index scan op.
+        IOperatorDescriptor primaryScanOp = createPrimaryIndexScanDiskComponentsOp(spec, metadataProvider,
+                getTaggedRecordDescriptor(dataset.getPrimaryRecordDescriptor(metadataProvider)), jobId);
+
+        // Assign op.
+        RecordDescriptor taggedSecondaryRecDesc = getTaggedRecordDescriptor(secondaryRecDesc);
+        AlgebricksMetaOperatorDescriptor asterixAssignOp =
+                createAssignOp(spec, numPrimaryKeys, taggedSecondaryRecDesc);
+
+        // Generate compensate tuples for upsert
+        IOperatorDescriptor processorOp =
+                createTupleProcessorOp(spec, taggedSecondaryRecDesc, getNumSecondaryKeys(), numPrimaryKeys, false);
+
+        // TODO: is sort needed?
+        ExternalSortOperatorDescriptor sortOp = createSortOp(spec,
+                getTaggedSecondaryComparatorFactories(secondaryComparatorFactories), taggedSecondaryRecDesc);
+
+        // Create secondary BTree bulk load op.
+        LSMSecondaryIndexBulkLoadOperatorDescriptor secondaryBulkLoadOp =
+                createTreeIndexBulkLoadOp(spec, metadataProvider, taggedSecondaryRecDesc,
+                        createFieldPermutationForBulkLoadOp(), getNumSecondaryKeys(), numPrimaryKeys, false);
+
+        AlgebricksMetaOperatorDescriptor metaOp =
+                new AlgebricksMetaOperatorDescriptor(spec, 1, 0, new IPushRuntimeFactory[] { new SinkRuntimeFactory() },
+                        new RecordDescriptor[] { taggedSecondaryRecDesc });
+        // Connect the operators.
+        spec.connect(new OneToOneConnectorDescriptor(spec), keyProviderOp, 0, primaryScanOp, 0);
+        spec.connect(new OneToOneConnectorDescriptor(spec), primaryScanOp, 0, asterixAssignOp, 0);
+        spec.connect(new OneToOneConnectorDescriptor(spec), asterixAssignOp, 0, processorOp, 0);
+        spec.connect(new OneToOneConnectorDescriptor(spec), processorOp, 0, sortOp, 0);
+        spec.connect(new OneToOneConnectorDescriptor(spec), sortOp, 0, secondaryBulkLoadOp, 0);
+        spec.connect(new OneToOneConnectorDescriptor(spec), secondaryBulkLoadOp, 0, metaOp, 0);
+        spec.addRoot(metaOp);
+        spec.setConnectorPolicyAssignmentPolicy(new ConnectorPolicyAssignmentPolicy());
+        return spec;
+    }
+
+    @Override
+    protected AlgebricksMetaOperatorDescriptor createAssignOp(JobSpecification spec, int numOfPrimaryKeys,
+            RecordDescriptor secondaryRecDesc) throws AlgebricksException {
+        int[] outColumns = new int[numOfPrimaryKeys + numFilterFields];
+        int[] projectionList = new int[NUM_TAG_FIELDS + numPrimaryKeys + numFilterFields];
+        for (int i = 0; i < numOfPrimaryKeys + numFilterFields; i++) {
+            outColumns[i] = NUM_TAG_FIELDS + i;
+        }
+
+        int projectionCount = 0;
+
+        //set tag fields
+        for (int i = 0; i < NUM_TAG_FIELDS; i++) {
+            projectionList[projectionCount++] = i;
+        }
+
+        //set primary keys
+        for (int i = 0; i < numOfPrimaryKeys; i++) {
+            projectionList[projectionCount++] = NUM_TAG_FIELDS + i;
+        }
+
+        //set filter fields
+        if (numFilterFields > 0) {
+            projectionList[projectionCount] = NUM_TAG_FIELDS + numOfPrimaryKeys;
+        }
+
+        IScalarEvaluatorFactory[] sefs = new IScalarEvaluatorFactory[secondaryFieldAccessEvalFactories.length];
+        for (int i = 0; i < secondaryFieldAccessEvalFactories.length; ++i) {
+            sefs[i] = secondaryFieldAccessEvalFactories[i];
+        }
+        AssignRuntimeFactory assign = new AssignRuntimeFactory(outColumns, sefs, projectionList);
+        AlgebricksMetaOperatorDescriptor asterixAssignOp = new AlgebricksMetaOperatorDescriptor(spec, 1, 1,
+                new IPushRuntimeFactory[] { assign }, new RecordDescriptor[] { secondaryRecDesc });
+        AlgebricksPartitionConstraintHelper.setPartitionConstraintInJobSpec(spec, asterixAssignOp,
+                primaryPartitionConstraint);
+        return asterixAssignOp;
+    }
+
+    @Override
+    protected int[] createFieldPermutationForBulkLoadOp() {
+        int[] fieldPermutation = new int[NUM_TAG_FIELDS + numPrimaryKeys + numFilterFields];
+        for (int i = 0; i < fieldPermutation.length; i++) {
+            fieldPermutation[i] = i;
+        }
+        return fieldPermutation;
+    }
+}
diff --git a/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-btree/src/main/java/org/apache/hyracks/storage/am/lsm/btree/impls/LSMBTreePointSearchCursor.java b/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-btree/src/main/java/org/apache/hyracks/storage/am/lsm/btree/impls/LSMBTreePointSearchCursor.java
index 6126c53..9082dcc 100644
--- a/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-btree/src/main/java/org/apache/hyracks/storage/am/lsm/btree/impls/LSMBTreePointSearchCursor.java
+++ b/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-btree/src/main/java/org/apache/hyracks/storage/am/lsm/btree/impls/LSMBTreePointSearchCursor.java
@@ -23,6 +23,7 @@
 
 import org.apache.hyracks.api.exceptions.HyracksDataException;
 import org.apache.hyracks.dataflow.common.data.accessors.ITupleReference;
+import org.apache.hyracks.storage.am.bloomfilter.impls.BloomFilter;
 import org.apache.hyracks.storage.am.btree.api.IBTreeLeafFrame;
 import org.apache.hyracks.storage.am.btree.impls.BTree;
 import org.apache.hyracks.storage.am.btree.impls.BTree.BTreeAccessor;
@@ -188,8 +189,16 @@
                 } else {
                     // create new cursor <should be relatively rare>
                     IBTreeLeafFrame leafFrame = (IBTreeLeafFrame) lsmInitialState.getLeafFrameFactory().createFrame();
-                    rangeCursors[i] = new BloomFilterAwareBTreePointSearchCursor(leafFrame, false,
-                            ((LSMBTreeDiskComponent) component).getBloomFilter());
+                    BloomFilter bloomFilter = ((LSMBTreeDiskComponent) component).getBloomFilter();
+
+                    if(bloomFilter == null) {
+                        rangeCursors[i] = new BTreeRangeSearchCursor(leafFrame, false);
+                    }
+                    else {
+                        rangeCursors[i] = new BloomFilterAwareBTreePointSearchCursor(leafFrame, false,
+                                bloomFilter);
+                    }
+
                 }
                 btree = ((LSMBTreeDiskComponent) component).getBTree();
             }

-- 
To view, visit https://asterix-gerrit.ics.uci.edu/1916
To unsubscribe, visit https://asterix-gerrit.ics.uci.edu/settings

Gerrit-MessageType: newchange
Gerrit-Change-Id: I59725425ba7c5fe438507dc900f83eaab239d296
Gerrit-PatchSet: 1
Gerrit-Project: asterixdb
Gerrit-Branch: master
Gerrit-Owner: Ali Alsuliman <ali.al.solaiman@gmail.com>

Mime
View raw message