diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java
index d94a045..a8d9ec0 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java
@@ -38,6 +38,7 @@
 import org.apache.asterix.optimizer.rules.ExtractDistinctByExpressionsRule;
 import org.apache.asterix.optimizer.rules.ExtractOrderExpressionsRule;
 import org.apache.asterix.optimizer.rules.FeedScanCollectionToUnnest;
+import org.apache.asterix.optimizer.rules.FixReplicateOperatorOutputsRule;
 import org.apache.asterix.optimizer.rules.FullTextContainsParameterCheckRule;
 import org.apache.asterix.optimizer.rules.FuzzyEqRule;
 import org.apache.asterix.optimizer.rules.InjectTypeCastForSwitchCaseRule;
@@ -369,6 +370,7 @@
         prepareForJobGenRewrites.add(new PushGroupByIntoSortRule());
         prepareForJobGenRewrites.add(new SetExecutionModeRule());
         prepareForJobGenRewrites.add(new SweepIllegalNonfunctionalFunctions());
+        prepareForJobGenRewrites.add(new FixReplicateOperatorOutputsRule());
         return prepareForJobGenRewrites;
     }
 }
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/FixReplicateOperatorOutputsRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/FixReplicateOperatorOutputsRule.java
new file mode 100644
index 0000000..d9e58f3
--- /dev/null
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/FixReplicateOperatorOutputsRule.java
@@ -0,0 +1,167 @@
+/*
+ * 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.optimizer.rules;
+
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+import org.apache.asterix.algebra.operators.CommitOperator;
+import org.apache.asterix.common.exceptions.ErrorCode;
+import org.apache.commons.lang3.mutable.Mutable;
+import org.apache.commons.lang3.mutable.MutableInt;
+import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
+import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator;
+import org.apache.hyracks.algebricks.core.algebra.base.IOptimizationContext;
+import org.apache.hyracks.algebricks.core.algebra.base.LogicalOperatorTag;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractLogicalOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractReplicateOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.DelegateOperator;
+import org.apache.hyracks.algebricks.core.rewriter.base.IAlgebraicRewriteRule;
+
+/**
+ * This rule does not really rewrite the plan. The whole purpose of this rule is to fix the outputs of replicate/split
+ * operator. AbstractReplicateOperator has a list of outputs. The outputs represent the logical operators to which the
+ * replicate operator is connected to. Ideally, the outputs should match the parents of replicate operator in the real
+ * execution plan. However, the issue is that when the replicate operator is created, it starts out with specific
+ * outputs as its parents. Afterwards, while optimizing the plan, new operators could be inserted above replicate
+ * operators. In the plan now, those operators have their inputs coming from replicate operator. But the replicate
+ * operator still holds the "old" parents as outputs. One example is when bulk loading into a dataset with some indexes.
+ * At first, the plan looks something like this:
+ * ...              ...
+ * |                |
+ * idx1 insert Op   idx2 insert Op
+ * \________________/
+ *          |
+ *       replicate (where replicate.outputs = idx1 insert operator & idx2 insert operator
+ *
+ * After several optimizations, the plan would look something like this:
+ * ...              ...
+ * |                |
+ * idx1 insert Op   idx2 insert Op
+ * |                |
+ * ...              ...
+ * |                |
+ * exchange1        exchange2
+ * \________________/
+ *          |
+ *       replicate (where replicate.outputs is still = idx1 insert operator & idx2 insert operator)
+ *
+ * The reason for this divergence is that the usual routine when inserting a new operator, like exchange1 in the plan,
+ * exchange1 operator sets its input to the operator below it which is a one way change for operators that have outputs
+ * as instance variables such as replicate/split.
+ */
+public class FixReplicateOperatorOutputsRule implements IAlgebraicRewriteRule {
+
+    // Integer = how many parents have been fixed
+    private final Map<AbstractReplicateOperator, MutableInt> replicateOperators;
+    private final List<Mutable<ILogicalOperator>> parentsPathToReplicate;
+
+    public FixReplicateOperatorOutputsRule() {
+        parentsPathToReplicate = new ArrayList<>();
+        replicateOperators = new HashMap<>();
+    }
+
+    @Override
+    public boolean rewritePre(Mutable<ILogicalOperator> opRef, IOptimizationContext context) {
+        // as you traverse down the tree, append the operators on your path
+        parentsPathToReplicate.add(opRef);
+        return false;
+    }
+
+    @Override
+    public boolean rewritePost(Mutable<ILogicalOperator> opRef, IOptimizationContext context)
+            throws AlgebricksException {
+        AbstractLogicalOperator op = (AbstractLogicalOperator) opRef.getValue();
+
+        // remove yourself from the path since you should be the last one by now
+        parentsPathToReplicate.remove(parentsPathToReplicate.size() - 1);
+
+        // when done with the whole plan, check that all replicate operators have been fixed
+        // if there is one that has not been completely fixed, it indicates that one "old" parent couldn't be found
+        if (op.getOperatorTag() == LogicalOperatorTag.DISTRIBUTE_RESULT ||
+                op.getOperatorTag() == LogicalOperatorTag.SINK ||
+                (op.getOperatorTag() == LogicalOperatorTag.DELEGATE_OPERATOR &&
+                        ((DelegateOperator) op).getDelegate() instanceof CommitOperator)) {
+            for (Map.Entry<AbstractReplicateOperator, MutableInt> entry : replicateOperators.entrySet()) {
+                if (entry.getKey().getOutputs().size() != entry.getValue().getValue()) {
+                    throw new AlgebricksException(ErrorCode.ASTERIX, ErrorCode.COMPILATION_FAILED_DUE_TO_REPLICATE_OP);
+                }
+            }
+            return false;
+        }
+
+        // rewrite/fix only replicate operators
+        if ((op.getOperatorTag() != LogicalOperatorTag.REPLICATE && op.getOperatorTag() != LogicalOperatorTag.SPLIT) ||
+                context.checkIfInDontApplySet(this, opRef.getValue())) {
+            return false;
+        }
+        AbstractReplicateOperator replicateOperator = (AbstractReplicateOperator) op;
+
+        // initialize number of parents fixed for this replicate operator
+        replicateOperators.putIfAbsent(replicateOperator, new MutableInt(0));
+
+        // fix the old parent of replicate operator, the real parent is the last element in parentsPathToReplicate
+        Mutable<ILogicalOperator> replicateActualParent = parentsPathToReplicate.get(parentsPathToReplicate.size() - 1);
+
+        // if the real parent is already in the output list of the replicate, do nothing
+        if (replicateOperator.getOutputs().contains(replicateActualParent)) {
+            // update number of parents done for this replicate operator
+            updateNumberOfParentsDone(replicateOperator);
+
+            // if all parents are fixed, add this replicate to the do not apply set
+            if (replicateOperators.get(replicateOperator).getValue() == replicateOperator.getOutputs().size()) {
+                context.addToDontApplySet(this, opRef.getValue());
+            }
+            return false;
+        } else {
+            // if the parent (the one currently in the plan) is not in the output list of the replicate operator,
+            // the "old" output (one that was once the parent) should be replaced with the actual parent in the plan
+            // find this old parent in the parentsPathToReplicate
+            boolean parentFixed = false;
+            for (int oldParentIndex = 0; oldParentIndex < replicateOperator.getOutputs().size(); oldParentIndex++) {
+                if (parentsPathToReplicate.contains(replicateOperator.getOutputs().get(oldParentIndex))) {
+                    replicateOperator.getOutputs().get(oldParentIndex).setValue(replicateActualParent.getValue());
+                    parentFixed = true;
+                    updateNumberOfParentsDone(replicateOperator);
+                    break;
+                }
+            }
+
+            // if all parents are fixed, add this replicate to the do not apply set
+            if (replicateOperators.get(replicateOperator).getValue() == replicateOperator.getOutputs().size()) {
+                context.addToDontApplySet(this, opRef.getValue());
+            }
+
+            return parentFixed;
+        }
+    }
+
+    /**
+     * replicate operator could have more than one parent (output). This method keeps count of how many outputs
+     * have been fixed
+     * @param replicateOperator the replicate operator in question
+     */
+    private void updateNumberOfParentsDone(AbstractReplicateOperator replicateOperator) {
+        MutableInt numParentsDone = replicateOperators.get(replicateOperator);
+        Integer newNumParentsDone = numParentsDone.getValue() + 1;
+        numParentsDone.setValue(newNumParentsDone);
+    }
+}
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceSecondaryIndexInsertDeleteRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceSecondaryIndexInsertDeleteRule.java
index ec3ec26..95dfceb 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceSecondaryIndexInsertDeleteRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceSecondaryIndexInsertDeleteRule.java
@@ -475,6 +475,16 @@
                 currentTop = indexUpdate;
             } else {
                 replicateOp.getOutputs().add(new MutableObject<>(replicateOutput));
+
+                /* special treatment for bulk load with the existence of secondary primary index.
+                 * the branch coming out of the replicate operator and feeding the index will not have the usual
+                 * "blocking" sort operator since tuples are already sorted. We mark the materialization flag for that
+                 * branch to make it blocking. Without "blocking", the activity cluster graph would be messed up
+                 */
+                if (index.getKeyFieldNames().isEmpty() && index.getIndexType() == IndexType.BTREE) {
+                    int positionOfSecondaryPrimaryIndex = replicateOp.getOutputs().size() - 1;
+                    replicateOp.getOutputMaterializationFlags()[positionOfSecondaryPrimaryIndex] = true;
+                }
             }
             if (primaryIndexModificationOp.isBulkload()) {
                 // For bulk load, we connect all fanned out insert operator to a single SINK operator
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 29135d3..2abc18f 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
@@ -30,6 +30,7 @@
 import java.util.Date;
 import java.util.EnumSet;
 import java.util.HashMap;
+import java.util.HashSet;
 import java.util.Iterator;
 import java.util.LinkedHashSet;
 import java.util.List;
@@ -762,6 +763,10 @@
                     throw new AlgebricksException("An index with this name " + indexName + " already exists.");
                 }
             }
+            // can't create secondary primary index on an external dataset
+            if (ds.getDatasetType() == DatasetType.EXTERNAL && stmtCreateIndex.getFieldExprs().isEmpty()) {
+                throw new AsterixException(ErrorCode.CANNOT_CREATE_SEC_PRIMARY_IDX_ON_EXT_DATASET);
+            }
             Datatype dt = MetadataManager.INSTANCE.getDatatype(metadataProvider.getMetadataTxnContext(),
                     ds.getItemTypeDataverseName(), ds.getItemTypeName());
             ARecordType aRecordType = (ARecordType) dt.getDatatype();
@@ -776,6 +781,13 @@
             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).
+            // checking only the names is not enough. Need also to check the source indicators for cases like:
+            // CREATE INDEX someIdx on dataset(meta().id, id)
+            Set<Pair<List<String>, Integer>> indexKeysSet = new HashSet<>();
+
             for (Pair<List<String>, IndexedTypeExpression> fieldExpr : stmtCreateIndex.getFieldExprs()) {
                 IAType fieldType = null;
                 ARecordType subType =
@@ -800,6 +812,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");
@@ -818,6 +837,13 @@
                             "Unknown type " + (fieldExpr.second == null ? fieldExpr.first : fieldExpr.second));
                 }
 
+                // try to add the key & its source to the set of keys, if key couldn't be added, there is a duplicate
+                if (!indexKeysSet.add(new Pair<>(fieldExpr.first,
+                        stmtCreateIndex.getFieldSourceIndicators().get(keyIndex)))) {
+                    throw new AsterixException(ErrorCode.INDEX_ILLEGAL_REPETITIVE_FIELD,
+                            String.valueOf(fieldExpr.first));
+                }
+
                 indexFields.add(fieldExpr.first);
                 indexFieldTypes.add(fieldType);
                 ++keyIndex;
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/ddl/create-index/create-index-1/create-index-1.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-1/create-index-1.1.ddl.sqlpp
new file mode 100644
index 0000000..94cfa7b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-1/create-index-1.1.ddl.sqlpp
@@ -0,0 +1,57 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : Create a secondary primary index using the dedicated syntax
+ * Date         : Aug 21 2017
+ * Expected Res : Success
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.LineItemType as
+ closed {
+  l_orderkey : bigint,
+  l_partkey : bigint,
+  l_suppkey : bigint,
+  l_linenumber : bigint,
+  l_quantity : double,
+  l_extendedprice : double,
+  l_discount : double,
+  l_tax : double,
+  l_returnflag : string,
+  l_linestatus : string,
+  l_shipdate : string,
+  l_commitdate : string,
+  l_receiptdate : string,
+  l_shipinstruct : string,
+  l_shipmode : string,
+  l_comment : string
+};
+
+create  dataset LineItem(LineItemType) primary key l_orderkey;
+
+create  primary index sec_primary_idx1  on LineItem type btree;
+create  primary index sec_primary_idx2  on LineItem;
+create  primary index sec_primary_idx2 if not exists  on LineItem;
+create  primary index on LineItem;
+create  primary index if not exists on LineItem;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-2/create-index-2.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-2/create-index-2.1.ddl.sqlpp
new file mode 100644
index 0000000..aa57850
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-2/create-index-2.1.ddl.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : Create a secondary primary index using the dedicated syntax
+ * Date         : Aug 21 2017
+ * Expected Res : Failure
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.LineItemType as
+ closed {
+  l_orderkey : bigint,
+  l_partkey : bigint,
+  l_suppkey : bigint,
+  l_linenumber : bigint,
+  l_quantity : double,
+  l_extendedprice : double,
+  l_discount : double,
+  l_tax : double,
+  l_returnflag : string,
+  l_linestatus : string,
+  l_shipdate : string,
+  l_commitdate : string,
+  l_receiptdate : string,
+  l_shipinstruct : string,
+  l_shipmode : string,
+  l_comment : string
+};
+
+create  dataset LineItem(LineItemType) primary key l_orderkey;
+
+create  primary index sec_primary_idx1  on LineItem type rtree;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-3/create-index-3.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-3/create-index-3.1.ddl.sqlpp
new file mode 100644
index 0000000..cf0c952
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-3/create-index-3.1.ddl.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : Create a secondary primary index using the dedicated syntax
+ * Date         : Aug 21 2017
+ * Expected Res : Failure
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.LineItemType as
+ closed {
+  l_orderkey : bigint,
+  l_partkey : bigint,
+  l_suppkey : bigint,
+  l_linenumber : bigint,
+  l_quantity : double,
+  l_extendedprice : double,
+  l_discount : double,
+  l_tax : double,
+  l_returnflag : string,
+  l_linestatus : string,
+  l_shipdate : string,
+  l_commitdate : string,
+  l_receiptdate : string,
+  l_shipinstruct : string,
+  l_shipmode : string,
+  l_comment : string
+};
+
+create  dataset LineItem(LineItemType) primary key l_orderkey;
+
+create  primary  sec_primary_idx1  on LineItem;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-4/create-index-4.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-4/create-index-4.1.ddl.sqlpp
new file mode 100644
index 0000000..a591cdc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-4/create-index-4.1.ddl.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : Create a secondary primary index using the dedicated syntax
+ * Date         : Aug 21 2017
+ * Expected Res : Failure
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.LineItemType as
+ closed {
+  l_orderkey : bigint,
+  l_partkey : bigint,
+  l_suppkey : bigint,
+  l_linenumber : bigint,
+  l_quantity : double,
+  l_extendedprice : double,
+  l_discount : double,
+  l_tax : double,
+  l_returnflag : string,
+  l_linestatus : string,
+  l_shipdate : string,
+  l_commitdate : string,
+  l_receiptdate : string,
+  l_shipinstruct : string,
+  l_shipmode : string,
+  l_comment : string
+};
+
+create  dataset LineItem(LineItemType) primary key l_orderkey;
+
+create  primary index if not exists sec_primary_idx1  if not exists on LineItem;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-5/create-index-5.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-5/create-index-5.1.ddl.sqlpp
new file mode 100644
index 0000000..e895382
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/ddl/create-index/create-index-5/create-index-5.1.ddl.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description  : Create a secondary primary index using the dedicated syntax
+ * Date         : Aug 21 2017
+ * Expected Res : Failure
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.LineItemType as
+ closed {
+  l_orderkey : bigint,
+  l_partkey : bigint,
+  l_suppkey : bigint,
+  l_linenumber : bigint,
+  l_quantity : double,
+  l_extendedprice : double,
+  l_discount : double,
+  l_tax : double,
+  l_returnflag : string,
+  l_linestatus : string,
+  l_shipdate : string,
+  l_commitdate : string,
+  l_receiptdate : string,
+  l_shipinstruct : string,
+  l_shipmode : string,
+  l_comment : string
+};
+
+create  dataset LineItem(LineItemType) primary key l_orderkey;
+
+create  primary index if exists sec_primary_idx1  on LineItem;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
index d93f069..6a64fbd 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
@@ -65,6 +65,8 @@
 
 create  index secndIndx_open  on t1 (address:string?) type btree enforced;
 
+create  primary index sec_primary_idx on t1;
+
 drop index t1.rtree_index_point;
 drop index t1.rtree_index_point_open;
 drop index t1.keyWD_indx;
@@ -72,3 +74,4 @@
 drop index t1.secndIndx;
 drop index t1.nested;
 drop index t1.secndIndx_open;
+drop index t1.sec_primary_idx;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp
index 013c209..4badf84 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp
@@ -24,5 +24,8 @@
 
 create  index idx_t1_unique1  on t1 (unique1) type btree;
 
+create  primary index sec_primary_idx on t1 type btree;
+
 drop index t1.idx_t1_str1;
 drop index t1.idx_t1_unique1;
+drop index t1.sec_primary_idx;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
index 751bdeb..f62364e 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
@@ -53,3 +53,4 @@
 
 create  index part_index  on LineItem (l_partkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
index 1bff150..dfbfb63 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
@@ -43,3 +43,4 @@
 
 create  index idx_employee_first_name  on test.employee (fname) type btree;
 
+create  primary index sec_primary_idx  on test.employee;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp
index 2cb2f83..eef86a3 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp
@@ -37,3 +37,4 @@
 
 create  index btreeName  on SimpleGeoPlace (name) type btree;
 
+create  primary index sec_primary_idx  on SimpleGeoPlace;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-empty-dataset-with-index/insert-into-empty-dataset-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-empty-dataset-with-index/insert-into-empty-dataset-with-index.1.ddl.sqlpp
index 3a7ffe8..1ba5ecee 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-empty-dataset-with-index/insert-into-empty-dataset-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-empty-dataset-with-index/insert-into-empty-dataset-with-index.1.ddl.sqlpp
@@ -42,3 +42,4 @@
 
 create  index idx_LineID_suppkey  on LineID (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineID;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_01/insert-into-loaded-dataset-with-index_01.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_01/insert-into-loaded-dataset-with-index_01.3.ddl.sqlpp
index ec12e33..98e5016 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_01/insert-into-loaded-dataset-with-index_01.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_01/insert-into-loaded-dataset-with-index_01.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineID_suppkey  on LineID (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineID;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_02/insert-into-loaded-dataset-with-index_02.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_02/insert-into-loaded-dataset-with-index_02.3.ddl.sqlpp
index ec12e33..98e5016 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_02/insert-into-loaded-dataset-with-index_02.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_02/insert-into-loaded-dataset-with-index_02.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineID_suppkey  on LineID (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineID;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.1.ddl.sqlpp
new file mode 100644
index 0000000..5c943d8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.1.ddl.sqlpp
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Testing secondary primary index with autogenerated primary key
+ * Expected Result : Success
+ * Date            : Jul 30 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+  id : uuid,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+};
+
+create  dataset DBLP(DBLPType) primary key id autogenerated ;
+
+create primary index sec_primary_idx on DBLP;
\ No newline at end of file
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 b2d43a6..893fdac 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/load-with-index/load-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/load-with-index/load-with-index.1.ddl.sqlpp
@@ -47,3 +47,4 @@
 
 create  index idx_partkey  on LineItem (l_partkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp
index 79f376a..fb79495 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp
@@ -44,3 +44,4 @@
 
 create  index idx_employee_first_name  on test.employee (fname) type btree;
 
+create  primary index sec_primary_idx  on test.employee;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp
index 0f86a33..e2ec595 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp
@@ -29,3 +29,6 @@
 drop index employee.idx_employee_first_name;
 create  index idx_employee_first_name  on test.employee (fname) type btree;
 
+drop index employee.sec_primary_idx;
+create  primary index sec_primary_idx  on test.employee;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy-with-feed/using-correlated-prefix-merge-policy-with-feed.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy-with-feed/using-correlated-prefix-merge-policy-with-feed.3.ddl.sqlpp
index fff1e39..c551c29 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy-with-feed/using-correlated-prefix-merge-policy-with-feed.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy-with-feed/using-correlated-prefix-merge-policy-with-feed.3.ddl.sqlpp
@@ -23,3 +23,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp
index 873c727..f64c5a3 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem ;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-correlated-secondary-btree/insert-with-correlated-secondary-btree.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-correlated-secondary-btree/insert-with-correlated-secondary-btree.3.ddl.sqlpp
index cbc3aa8..50407b5 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-correlated-secondary-btree/insert-with-correlated-secondary-btree.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-correlated-secondary-btree/insert-with-correlated-secondary-btree.3.ddl.sqlpp
@@ -26,3 +26,4 @@
 
 create  index fbAuthorIdx  on FacebookMessages2 (`author-id`) type btree;
 
+create  primary index sec_primary_idx  on FacebookMessages2;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-secondary-btree/insert-with-secondary-btree.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-secondary-btree/insert-with-secondary-btree.3.ddl.sqlpp
index 99da4f0..1422128 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-secondary-btree/insert-with-secondary-btree.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/insert-with-secondary-btree/insert-with-secondary-btree.3.ddl.sqlpp
@@ -22,3 +22,4 @@
 
 create  index fbAuthorIdx  on FacebookMessages2 (`author-id`) type btree;
 
+create  primary index sec_primary_idx  on FacebookMessages2;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/load-with-secondary-btree/load-with-secondary-btree.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/load-with-secondary-btree/load-with-secondary-btree.3.ddl.sqlpp
index b2fcb2d..d528d4c 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/load-with-secondary-btree/load-with-secondary-btree.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/filters/load-with-secondary-btree/load-with-secondary-btree.3.ddl.sqlpp
@@ -22,3 +22,4 @@
 
 create  index fbAuthorIdx  on FacebookMessages (`author-id`) type btree;
 
+create  primary index sec_primary_idx  on FacebookMessages;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp
new file mode 100644
index 0000000..242bf5d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp
@@ -0,0 +1,41 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.Emp as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create  dataset employee(Emp) primary key id;
\ No newline at end of file
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..452938b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+
+create primary index sec_primary_idx on employee ;
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..1194907
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-but-different-order.1.ddl.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Test creating an index with the same keys as primary keys but in different order
+ * Expected Result : Success
+ * Date            : Aug 3 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.testType as
+{
+  id : integer,
+  `value` : string
+}
+
+create  dataset testDS(testType) primary key id, `value`;
+
+create  index testIdx  on testDS (`value`, id);
+
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..5e82d6f 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
@@ -24,3 +24,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (nested.l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.1.ddl.sqlpp
new file mode 100644
index 0000000..5795925
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.1.ddl.sqlpp
@@ -0,0 +1,45 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Testing deleting from a dataset that has a nested composite secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.Nested as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create type test.Emp as
+ closed {
+  nested : Nested
+};
+
+create  dataset employee(Emp) primary key nested.id, nested.lname;
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..3772116
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/delete-from-loaded-dataset-with-sec-primary-index/delete-from-loaded-dataset-with-sec-primary-index.3.ddl.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Testing deleting from a dataset that has a nested composite secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+
+use test;
+
+create  primary index sec_primary_idx  on employee;
\ No newline at end of file
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..4212037 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/drop-index/drop-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/drop-index/drop-index.3.ddl.sqlpp
@@ -24,5 +24,8 @@
 
 create  index idx_t1_unique1  on t1 (nested.unique1) type btree;
 
+create  primary index sec_primary_idx  on t1;
+
 drop index t1.idx_t1_str1;
 drop index t1.idx_t1_unique1;
+drop index t1.sec_primary_idx;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.1.ddl.sqlpp
new file mode 100644
index 0000000..4fa95b3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-empty-dataset-with-sec-primary-index/insert-into-empty-dataset-with-sec-primary-index.1.ddl.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Test inserting into an empty dataset and its empty secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.EmpTmp as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create type test.Nested as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create type test.Emp as
+ closed {
+  nested : Nested
+};
+
+create  dataset employeeTmp(EmpTmp) primary key id;
+
+create  dataset employee(Emp) primary key nested.id;
+
+create  primary index sec_primary_idx  on employee;
\ No newline at end of file
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..1f7ad33
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.1.ddl.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Test inserting into an already loaded dataset and its secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type test.Nested as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create type test.Emp as
+ closed {
+  nested : Nested
+};
+
+create  dataset employee(Emp) primary key nested.id, nested.lname;
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..38c7c86
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/insert-into-loaded-dataset-with-sec-primary-index/insert-into-loaded-dataset-with-sec-primary-index.3.ddl.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Test inserting into an already loaded dataset and its secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+create  primary index sec_primary_idx  on employee;
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..952e543
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index-dml/load-with-sec-primary-index/load-with-sec-primary-index.1.ddl.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Test loading into an empty dataset and its empty secondary primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type test.Nested as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create type test.Emp as
+ closed {
+  nested : Nested
+};
+
+create  dataset employee(Emp) primary key nested.id;
+
+create  primary index sec_primary_idx  on employee;
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..23d5f16
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp
@@ -0,0 +1,58 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index.
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.EmpTmp as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create type test.Nested as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create type test.Emp as
+ closed {
+  nested : Nested
+};
+
+create  dataset employeeTmp(EmpTmp) primary key id;
+
+create  dataset employee(Emp) primary key nested.id, nested.lname;
+
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..1814242
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/nested-index/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+
+create primary index sec_primary_idx  on employee;
+
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..55f14eb 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
@@ -30,3 +30,4 @@
 
 create  index idx_LineItem_suppkey  on LineItem (l_suppkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
index 1072a85..4ca67fe 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
@@ -65,6 +65,8 @@
 
 create  index secndIndx_open  on t1 (address:string?) type btree enforced;
 
+create  primary index sec_primary_idx on t1;
+
 drop index t1.rtree_index_point;
 drop index t1.rtree_index_point_open;
 drop index t1.keyWD_indx;
@@ -72,3 +74,4 @@
 drop index t1.secndIndx;
 drop index t1.nested;
 drop index t1.secndIndx_open;
+drop index t1.sec_primary_idx;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-index/drop-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-index/drop-index.3.ddl.sqlpp
index 90e6b7f..f01f959 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-index/drop-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/drop-index/drop-index.3.ddl.sqlpp
@@ -30,5 +30,8 @@
 
 create  index idx_t1_unique1  on t1 (unique1) type btree;
 
+create  primary index sec_primary_idx  on t1;
+
 drop index t1.idx_t1_str1;
 drop index t1.idx_t1_unique1;
+drop index t1.sec_primary_idx;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
index d51ed99..2b169dd 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
@@ -52,3 +52,4 @@
 
 create  index part_index  on LineItem (l_partkey) type btree;
 
+create  primary index sec_primary_idx on LineItem ;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-correlated-index/insert-and-scan-dataset-with-correlated-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-correlated-index/insert-and-scan-dataset-with-correlated-index.3.ddl.sqlpp
index 0b0405c..c3ee05f 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-correlated-index/insert-and-scan-dataset-with-correlated-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-correlated-index/insert-and-scan-dataset-with-correlated-index.3.ddl.sqlpp
@@ -28,3 +28,5 @@
 
 create  index idx_employee_first_name  on test.employee (fname) type btree;
 
+create  primary index sec_primary_idx  on test.employee;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
index ac3d7da..e9aae4a 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/temp-dataset/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
@@ -39,3 +39,4 @@
 
 create  index idx_employee_first_name  on test.employee (fname) type btree;
 
+create  primary index sec_primary_idx  on test.employee;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/filtered-dataset/filtered-dataset.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/filtered-dataset/filtered-dataset.1.ddl.sqlpp
index f9349a7..f56f997 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/filtered-dataset/filtered-dataset.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/filtered-dataset/filtered-dataset.1.ddl.sqlpp
@@ -44,3 +44,4 @@
 
 create index AutherIdx on FilteredFacebookMessages(`author-id`);
 create index MessageIdx on FilteredFacebookMessages(message);
+create primary index sec_primary_idx on FilteredFacebookMessages;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-correlated-secondaries/multiple-correlated-secondaries.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-correlated-secondaries/multiple-correlated-secondaries.3.ddl.sqlpp
index acf2ebd..06e21d9 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-correlated-secondaries/multiple-correlated-secondaries.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-correlated-secondaries/multiple-correlated-secondaries.3.ddl.sqlpp
@@ -22,3 +22,4 @@
 create index btree_index on UpsertTo(kwds);
 create index rtree_index on UpsertTo(point) type rtree;
 create index inverted_index on UpsertTo(kwds) type keyword;
+create primary index sec_primary_idx on UpsertTo;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-secondaries/multiple-secondaries.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-secondaries/multiple-secondaries.1.ddl.sqlpp
index e509614..cac8822 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-secondaries/multiple-secondaries.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/multiple-secondaries/multiple-secondaries.1.ddl.sqlpp
@@ -42,3 +42,4 @@
 create index btree_index on UpsertTo(kwds);
 create index rtree_index on UpsertTo(point) type rtree;
 create index inverted_index on UpsertTo(kwds) type keyword;
+create primary index sec_primary_idx on UpsertTo;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/primary-secondary-btree/primary-secondary-btree.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/primary-secondary-btree/primary-secondary-btree.1.ddl.sqlpp
index 0e5d5b2..d9111c7 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/primary-secondary-btree/primary-secondary-btree.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/upsert/primary-secondary-btree/primary-secondary-btree.1.ddl.sqlpp
@@ -35,4 +35,5 @@
 
 create dataset UpsertTo(TestType) primary key id;
 create index ageindex on UpsertTo(age);
+create primary index sec_primary_idx on UpsertTo;
 create dataset UpsertFrom(TestType) primary key id;
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 920f0f0..68b4227 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -1618,8 +1618,44 @@
         </test-case>
         -->
   </test-group>
+  <test-group name="ddl/create-index">
+    <test-case FilePath="ddl/create-index">
+      <compilation-unit name="create-index-1">
+        <output-dir compare="Text">create-index-1</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="ddl/create-index">
+      <compilation-unit name="create-index-2">
+        <output-dir compare="Text">create-index-2</output-dir>
+        <expected-error>Syntax error: In line 53 >>create  primary index sec_primary_idx1  on LineItem type rtree;&lt;&lt; Encountered "rtree" at column 58.</expected-error>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="ddl/create-index">
+      <compilation-unit name="create-index-3">
+        <output-dir compare="Text">create-index-3</output-dir>
+        <expected-error>Syntax error: In line 53 >>create  primary  sec_primary_idx1  on LineItem;&lt;&lt; Encountered &lt;IDENTIFIER&gt; "sec_primary_idx1" at column 18.</expected-error>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="ddl/create-index">
+      <compilation-unit name="create-index-4">
+        <output-dir compare="Text">create-index-4</output-dir>
+        <expected-error>Syntax error: In line 53 >>create  primary index if not exists sec_primary_idx1  if not exists on LineItem;&lt;&lt; Encountered &lt;IDENTIFIER&gt; "sec_primary_idx1" at column 37.</expected-error>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="ddl/create-index">
+      <compilation-unit name="create-index-5">
+        <output-dir compare="Text">create-index-5</output-dir>
+        <expected-error>Syntax error: In line 53 >>create  primary index if exists sec_primary_idx1  on LineItem;&lt;&lt; Encountered "exists" at column 26.</expected-error>
+      </compilation-unit>
+    </test-case>
+  </test-group>
   <test-group name="dml">
     <test-case FilePath="dml">
+      <compilation-unit name="insert-with-autogenerated-pk_adm-with-sec-primary-index">
+        <output-dir compare="Text">insert-with-autogenerated-pk_adm-with-sec-primary-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="dml">
       <compilation-unit name="compact-dataset-and-its-indexes">
         <output-dir compare="Text">compact-dataset-and-its-indexes</output-dir>
       </compilation-unit>
@@ -1793,6 +1829,11 @@
         </test-case>
         -->
     <test-case FilePath="dml">
+      <compilation-unit name="insert-with-autogenerated-pk_adm-with-sec-primary-index">
+        <output-dir compare="Text">insert-with-autogenerated-pk_adm-with-sec-primary-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="dml">
       <compilation-unit name="insert-with-autogenerated-pk_adm_01">
         <output-dir compare="Text">insert-with-autogenerated-pk_adm_01</output-dir>
       </compilation-unit>
@@ -2994,6 +3035,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="index-selection">
+      <compilation-unit name="btree-sec-primary-index">
+        <output-dir compare="Text">btree-sec-primary-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="index-selection">
       <compilation-unit name="btree-index-composite-key-mixed-intervals">
         <output-dir compare="Text">btree-index-composite-key-mixed-intervals</output-dir>
       </compilation-unit>
@@ -3528,18 +3574,38 @@
       </compilation-unit>
     </test-case>
   </test-group>
+  <test-group name="index">
+    <test-group name="index/validations">
+      <test-case FilePath="index/validations">
+        <compilation-unit name="keys-same-as-pk-but-different-order">
+          <output-dir compare="Text">keys-same-as-pk-but-different-order</output-dir>
+        </compilation-unit>
+      </test-case>
+      <test-case FilePath="index/validations">
+        <compilation-unit name="keys-same-as-pk-in-same-order">
+          <output-dir compare="Text">keys-same-as-pk-in-same-order</output-dir>
+        </compilation-unit>
+      </test-case>
+      <test-case FilePath="index/validations">
+        <compilation-unit name="repetitive-keys">
+          <output-dir compare="Text">repetitive-keys</output-dir>
+          <expected-error>Cannot create index with the same field "[value]" specified more than once.</expected-error>
+        </compilation-unit>
+      </test-case>
+    </test-group>
+  </test-group>
   <test-group name="open-index-enforced">
     <test-group name="open-index-enforced/error-checking">
       <test-case FilePath="open-index-enforced/error-checking">
         <compilation-unit name="enforced-field-name-collision">
           <output-dir compare="Text">enforced-field-name-collision</output-dir>
-          <!-- <expected-error>org.apache.hyracks.algebricks.common.exceptions.AlgebricksException</expected-error> -->
+           <expected-error>Cannot create enforced index on "[value]" field. The field is closed type.</expected-error>
         </compilation-unit>
       </test-case>
       <test-case FilePath="open-index-enforced/error-checking">
         <compilation-unit name="enforced-field-type-collision">
           <output-dir compare="Text">enforced-field-type-collision</output-dir>
-          <expected-error>A field &quot;[value]&quot; is already defined with the type &quot;string&quot;</expected-error>
+          <expected-error>Cannot create enforced index on "[value]" field. The field is closed type.</expected-error>
         </compilation-unit>
       </test-case>
       <test-case FilePath="open-index-enforced/error-checking">
@@ -4060,6 +4126,11 @@
         </compilation-unit>
       </test-case>
       <test-case FilePath="nested-index/index-selection">
+        <compilation-unit name="btree-sec-primary-index">
+          <output-dir compare="Text">btree-sec-primary-index</output-dir>
+        </compilation-unit>
+      </test-case>
+      <test-case FilePath="nested-index/index-selection">
         <compilation-unit name="btree-index-composite-key-mixed-intervals">
           <output-dir compare="Text">btree-index-composite-key-mixed-intervals</output-dir>
         </compilation-unit>
@@ -4225,6 +4296,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="nested-index-dml">
+      <compilation-unit name="delete-from-loaded-dataset-with-sec-primary-index">
+        <output-dir compare="Text">delete-from-loaded-dataset-with-sec-primary-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="nested-index-dml">
       <compilation-unit name="drop-index">
         <output-dir compare="Text">drop-index</output-dir>
       </compilation-unit>
@@ -4235,6 +4311,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="nested-index-dml">
+      <compilation-unit name="insert-into-empty-dataset-with-sec-primary-index">
+        <output-dir compare="Text">insert-into-empty-dataset-with-sec-primary-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="nested-index-dml">
       <compilation-unit name="insert-into-loaded-dataset-with-index_01">
         <output-dir compare="Text">insert-into-loaded-dataset-with-index_01</output-dir>
       </compilation-unit>
@@ -4245,11 +4326,21 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="nested-index-dml">
+      <compilation-unit name="insert-into-loaded-dataset-with-sec-primary-index">
+        <output-dir compare="Text">insert-into-loaded-dataset-with-sec-primary-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="nested-index-dml">
       <compilation-unit name="load-with-index">
         <output-dir compare="Text">load-with-index</output-dir>
       </compilation-unit>
     </test-case>
     <test-case FilePath="nested-index-dml">
+      <compilation-unit name="load-with-sec-primary-index">
+        <output-dir compare="Text">load-with-sec-primary-index</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="nested-index-dml">
       <compilation-unit name="load-with-ngram-index">
         <output-dir compare="Text">load-with-ngram-index</output-dir>
       </compilation-unit>
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 1e32678..f960ce5 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
@@ -125,6 +125,10 @@
     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 NO_DATASET_WITH_NAME = 1050;
+    public static final int INDEX_ILLEGAL_ENFORCED_ON_CLOSED_FIELD = 1051;
+    public static final int INDEX_ILLEGAL_REPETITIVE_FIELD = 1052;
+    public static final int CANNOT_CREATE_SEC_PRIMARY_IDX_ON_EXT_DATASET = 1053;
+    public static final int COMPILATION_FAILED_DUE_TO_REPLICATE_OP = 1054;
 
     // 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 b1a5ff2..7362181 100644
--- a/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
+++ b/asterixdb/asterix-common/src/main/resources/asx_errormsg/en.properties
@@ -111,6 +111,10 @@
 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 = There is no dataset with name %2$s in dataverse %1$s
+1051 = Cannot create enforced index on \"%1$s\" field. The field is closed type.
+1052 = Cannot create index with the same field \"%1$s\" specified more than once.
+1053 = Cannot create primary index on external dataset.
+1054 = Compilation failed due to some problem in the query plan.
 
 # Feed Errors
 3001 = Illegal state.
diff --git a/asterixdb/asterix-lang-aql/src/main/javacc/AQL.jj b/asterixdb/asterix-lang-aql/src/main/javacc/AQL.jj
index db63abb..0cc6789 100644
--- a/asterixdb/asterix-lang-aql/src/main/javacc/AQL.jj
+++ b/asterixdb/asterix-lang-aql/src/main/javacc/AQL.jj
@@ -580,34 +580,47 @@
   Pair<Integer, Pair<List<String>, IndexedTypeExpression>> fieldPair = null;
   IndexParams indexType = null;
   boolean enforced = false;
+  boolean isPrimaryIdx = false;
 }
 {
-  <INDEX> indexName = Identifier()
-  ifNotExists = IfNotExists()
-  <ON> nameComponents = QualifiedName()
-  <LEFTPAREN> ( fieldPair = OpenField()
-    {
-       cis.addFieldExprPair(fieldPair.second);
-       cis.addFieldIndexIndicator(fieldPair.first);
-    }
-  ) (<COMMA> fieldPair = OpenField()
-    {
-       cis.addFieldExprPair(fieldPair.second);
-       cis.addFieldIndexIndicator(fieldPair.first);
-    }
-  )* <RIGHTPAREN> ( <TYPE> indexType = IndexType() )? ( <ENFORCED> { enforced = true; } )?
-    {
-      cis.setIndexName(new Identifier(indexName));
-      cis.setIfNotExists(ifNotExists);
-      cis.setDataverseName(nameComponents.first);
-      cis.setDatasetName(nameComponents.second);
-      if (indexType != null) {
-        cis.setIndexType(indexType.type);
-        cis.setGramLength(indexType.gramLength);
+  (
+    (<INDEX> indexName = Identifier()
+    ifNotExists = IfNotExists()
+    <ON> nameComponents = QualifiedName()
+    <LEFTPAREN> ( fieldPair = OpenField()
+      {
+        cis.addFieldExprPair(fieldPair.second);
+        cis.addFieldIndexIndicator(fieldPair.first);
       }
-      cis.setEnforced(enforced);
-      return cis;
+    ) (<COMMA> fieldPair = OpenField()
+      {
+        cis.addFieldExprPair(fieldPair.second);
+        cis.addFieldIndexIndicator(fieldPair.first);
+      }
+    )* <RIGHTPAREN> ( <TYPE> indexType = IndexType() )? ( <ENFORCED> { enforced = true; } )?)
+    |
+    (<PRIMARY> <INDEX> {isPrimaryIdx = true;}
+      (
+        (indexName = Identifier())? ifNotExists = IfNotExists()
+      )
+      <ON> nameComponents = QualifiedName() (<TYPE> <BTREE>)?
+    )
+  )
+  {
+    if (isPrimaryIdx && indexName == null) {
+      indexName = "primary_idx_" + nameComponents.second;
     }
+    cis.setIndexName(new Identifier(indexName));
+    cis.setIfNotExists(ifNotExists);
+    cis.setDataverseName(nameComponents.first);
+    cis.setDatasetName(nameComponents.second);
+    if (indexType != null) {
+      cis.setIndexType(indexType.type);
+      cis.setGramLength(indexType.gramLength);
+    }
+    cis.setEnforced(enforced);
+    return cis;
+  }
 }
 
 String CompactionPolicy() throws ParseException :
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
index 68c1cba..c451124 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
+++ b/asterixdb/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
@@ -626,34 +626,47 @@
   Pair<Integer, Pair<List<String>, IndexedTypeExpression>> fieldPair = null;
   IndexParams indexType = null;
   boolean enforced = false;
+  boolean isPrimaryIdx = false;
 }
 {
-  <INDEX> indexName = Identifier()
-  ifNotExists = IfNotExists()
-  <ON> nameComponents = QualifiedName()
-  <LEFTPAREN> ( fieldPair = OpenField()
-    {
-      cis.addFieldExprPair(fieldPair.second);
-      cis.addFieldIndexIndicator(fieldPair.first);
-    }
-  ) (<COMMA> fieldPair = OpenField()
-    {
-      cis.addFieldExprPair(fieldPair.second);
-      cis.addFieldIndexIndicator(fieldPair.first);
-    }
-  )* <RIGHTPAREN> ( <TYPE> indexType = IndexType() )? ( <ENFORCED> { enforced = true; } )?
-    {
-      cis.setIndexName(new Identifier(indexName));
-      cis.setIfNotExists(ifNotExists);
-      cis.setDataverseName(nameComponents.first);
-      cis.setDatasetName(nameComponents.second);
-      if (indexType != null) {
-        cis.setIndexType(indexType.type);
-        cis.setGramLength(indexType.gramLength);
+  (
+    (<INDEX> indexName = Identifier()
+    ifNotExists = IfNotExists()
+    <ON> nameComponents = QualifiedName()
+    <LEFTPAREN> ( fieldPair = OpenField()
+      {
+        cis.addFieldExprPair(fieldPair.second);
+        cis.addFieldIndexIndicator(fieldPair.first);
       }
-      cis.setEnforced(enforced);
-      return cis;
+    ) (<COMMA> fieldPair = OpenField()
+      {
+        cis.addFieldExprPair(fieldPair.second);
+        cis.addFieldIndexIndicator(fieldPair.first);
+      }
+    )* <RIGHTPAREN> ( <TYPE> indexType = IndexType() )? ( <ENFORCED> { enforced = true; } )?)
+    |
+    (<PRIMARY> <INDEX> {isPrimaryIdx = true;}
+      (
+        (indexName = Identifier())? ifNotExists = IfNotExists()
+      )
+      <ON> nameComponents = QualifiedName() (<TYPE> <BTREE>)?
+    )
+  )
+  {
+    if (isPrimaryIdx && indexName == null) {
+      indexName = "primary_idx_" + nameComponents.second;
     }
+    cis.setIndexName(new Identifier(indexName));
+    cis.setIfNotExists(ifNotExists);
+    cis.setDataverseName(nameComponents.first);
+    cis.setDatasetName(nameComponents.second);
+    if (indexType != null) {
+      cis.setIndexType(indexType.type);
+      cis.setGramLength(indexType.gramLength);
+    }
+    cis.setEnforced(enforced);
+    return cis;
+  }
 }
 
 String CompactionPolicy() throws ParseException :
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/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/utils/DotFormatBuilder.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/utils/DotFormatBuilder.java
new file mode 100644
index 0000000..1bbda27
--- /dev/null
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/utils/DotFormatBuilder.java
@@ -0,0 +1,253 @@
+/*
+ *  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.hyracks.algebricks.core.utils;
+
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+public class DotFormatBuilder {
+    private final StringBuilder stringBuilder;
+    private final Set<Node> nodes;
+    private final List<Edge> edges;
+
+    public DotFormatBuilder(StringValue graphName) {
+        this.edges = new ArrayList<>();
+        this.nodes = new HashSet<>();
+        this.stringBuilder = new StringBuilder();
+        this.stringBuilder.append("digraph ").append(graphName).append(" {\n").append("rankdir=BT;\n");
+        this.stringBuilder.append("node [style=\"rounded,filled\",shape=box];\n");
+    }
+
+    public String getDotDocument() {
+        // print edges first
+        for (Edge edge : edges) {
+            stringBuilder.append(edge);
+        }
+        // print nodes
+        for (Node node : nodes) {
+            stringBuilder.append(node);
+        }
+        stringBuilder.append("\n}");
+        return stringBuilder.toString();
+    }
+
+    // point of entry method
+    public Node createNode(StringValue nodeId, StringValue nodeLabel) {
+        Node node = new Node(nodeId, nodeLabel);
+        for (Node existingNode : nodes) {
+            if (node.equals(existingNode)) {
+                existingNode.setNodeLabel(nodeLabel);
+                return existingNode;
+            }
+        }
+        nodes.add(node);
+        return node;
+    }
+
+    // point of entry method
+    public Edge createEdge(final Node source, final Node destination) {
+        // sanity checks if any variable is null?
+        if (source == null || destination == null || !nodes.contains(source) || !nodes.contains(destination)) {
+            return null;
+        }
+
+        // append to edges list
+        Edge newEdge = new Edge(source, destination);
+        edges.add(newEdge);
+
+        return newEdge;
+    }
+
+    public class Node {
+        private final StringValue nodeId;
+        private HashMap<String,AttributeValue> attributes = new HashMap<>();
+
+        // no instantiation
+        private Node(StringValue nodeId, StringValue nodeLabel) {
+            this.nodeId = nodeId;
+            setNodeLabel(nodeLabel);
+        }
+
+        public StringValue getNodeId() {
+            return nodeId;
+        }
+
+        public AttributeValue getNodeLabel() {
+            return attributes.get(Attribute.LABEL);
+        }
+
+        public Node setNodeLabel(StringValue nodeLabel) {
+            if (nodeLabel != null) {
+                attributes.put(Attribute.LABEL, nodeLabel);
+            }
+            return this;
+        }
+
+        public Node setFillColor(Color color) {
+            if (color != null) {
+                attributes.put(Attribute.COLOR, color);
+            }
+            return this;
+        }
+
+        @Override
+        public boolean equals(Object other) {
+            if (!(other instanceof Node)) {
+                return false;
+            }
+            Node otherNode = (Node) other;
+
+            return nodeId.getValue().equals(otherNode.nodeId.getValue());
+        }
+
+        @Override
+        public int hashCode() {
+            return nodeId.getValue().hashCode();
+        }
+
+        @Override
+        public String toString() {
+            StringBuilder nodeString = new StringBuilder();
+            nodeString.append(nodeId).append(" [");
+            for (Map.Entry attribute : attributes.entrySet()) {
+                nodeString.append(attribute.getKey()).append("=").append(attribute.getValue()).append(",");
+            }
+            // remove last ","
+            if (nodeString.charAt(nodeString.length() - 1) == ',') {
+                nodeString.deleteCharAt(nodeString.length() - 1);
+            }
+            nodeString.append("];\n");
+
+            return nodeString.toString();
+        }
+    }
+
+    public class Edge {
+        private final Node source;
+        private final Node destination;
+        private final HashMap<String,AttributeValue> attributes = new HashMap<>();
+
+        // no instantiation
+        private Edge(Node source, Node destination) {
+            this.source = source;
+            this.destination = destination;
+        }
+
+        public Edge setLabel(StringValue edgeLabel) {
+            if (edgeLabel != null) {
+                attributes.put(Attribute.LABEL, edgeLabel);
+            }
+            return this;
+        }
+
+        public Edge setColor(Color color) {
+            if (color != null) {
+                attributes.put(Attribute.COLOR, color);
+            }
+            return this;
+        }
+
+        @Override
+        public boolean equals(Object other) {
+            if (!(other instanceof Edge)) {
+                return false;
+            }
+            Edge otherEdge = (Edge) other;
+
+            return source.equals(otherEdge.source) && destination.equals(otherEdge.destination);
+        }
+
+        @Override
+        public int hashCode() {
+            return source.hashCode() ^ destination.hashCode();
+        }
+
+        @Override
+        public String toString() {
+            StringBuilder edgeString = new StringBuilder();
+            edgeString.append(source.getNodeId()).append("->").append(destination.getNodeId()).append(" [");
+            for (Map.Entry attribute : attributes.entrySet()) {
+                edgeString.append(attribute.getKey()).append("=").append(attribute.getValue()).append(",");
+            }
+            // remove last ","
+            if (edgeString.charAt(edgeString.length() - 1) == ',') {
+                edgeString.deleteCharAt(edgeString.length() - 1);
+            }
+            edgeString.append("];\n");
+
+            return edgeString.toString();
+        }
+    }
+
+    public abstract static class AttributeValue {
+        private final String value;
+
+        // no instantiation
+        private AttributeValue(String value) {
+            this.value = value;
+        }
+
+        public String getValue() {
+            return value;
+        }
+
+        @Override
+        public String toString() {
+            return value;
+        }
+    }
+
+    public static final class StringValue extends AttributeValue {
+        // no instantiation
+        private StringValue (String value) {
+            super(value);
+        }
+
+        public static StringValue of(String value) {
+            String newValue = value;
+            if (value == null) {
+                newValue = "";
+            }
+            return new StringValue("\"" + newValue.replace("\"","\'").trim() + "\"");
+        }
+    }
+
+    public static final class Color extends AttributeValue {
+        public static final Color RED = new Color("red");
+        public static final Color SKYBLUE = new Color("skyblue");
+
+        // no instantiation
+        private Color (String color) {
+            super(color);
+        }
+    }
+
+    private class Attribute {
+        private static final String COLOR = "color";
+        private static final String LABEL = "label";
+
+        // no instantiation
+        private Attribute() {
+        }
+    }
+}
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/utils/DotFormatGenerator.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/utils/DotFormatGenerator.java
new file mode 100644
index 0000000..392bf44
--- /dev/null
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/utils/DotFormatGenerator.java
@@ -0,0 +1,277 @@
+/*
+ *  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.hyracks.algebricks.core.utils;
+
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+import org.apache.commons.lang3.mutable.Mutable;
+import org.apache.commons.lang3.tuple.ImmutablePair;
+import org.apache.commons.lang3.tuple.Pair;
+import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
+import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator;
+import org.apache.hyracks.algebricks.core.algebra.base.ILogicalPlan;
+import org.apache.hyracks.algebricks.core.algebra.base.IPhysicalOperator;
+import org.apache.hyracks.algebricks.core.algebra.base.LogicalOperatorTag;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractLogicalOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractOperatorWithNestedPlans;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractReplicateOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.ExchangeOperator;
+import org.apache.hyracks.api.dataflow.ActivityId;
+import org.apache.hyracks.api.dataflow.ConnectorDescriptorId;
+import org.apache.hyracks.api.dataflow.IActivity;
+import org.apache.hyracks.api.dataflow.IConnectorDescriptor;
+import org.apache.hyracks.api.dataflow.IOperatorDescriptor;
+import org.apache.hyracks.api.job.JobActivityGraph;
+import org.apache.hyracks.api.job.JobSpecification;
+
+public class DotFormatGenerator {
+
+    private DotFormatGenerator() {
+    }
+
+    /**
+     * Generates DOT format for {@link JobActivityGraph} that can be visualized
+     * using any DOT format visualizer.
+     *
+     * @param jobActivityGraph The job activity graph
+     * @return DOT format
+     */
+    public static String generate(final JobActivityGraph jobActivityGraph) {
+        final DotFormatBuilder graphBuilder =
+                new DotFormatBuilder(DotFormatBuilder.StringValue.of("JobActivityGraph"));
+        List<IConnectorDescriptor> connectors;
+        IActivity activity;
+        ActivityId fromActivityId;
+        ActivityId toActivityId;
+        String fromFullClassName;
+        String toFullClassName;
+        String fromClassName;
+        String toClassName;
+        DotFormatBuilder.Node fromNode;
+        DotFormatBuilder.Node toNode;
+        final Set<Pair<ActivityId, ActivityId>> activitiesPairedSet = new HashSet<>();
+        final Map<ActivityId, IActivity> activityMap = jobActivityGraph.getActivityMap();
+        final Map<ActivityId, List<IConnectorDescriptor>> activityInputMap = jobActivityGraph.getActivityInputMap();
+        final Map<ActivityId, List<IConnectorDescriptor>> activityOutputMap = jobActivityGraph.getActivityOutputMap();
+
+        // go through each activity. First, map its input -> activity, then activity -> its output
+        for (Map.Entry<ActivityId, IActivity> entry : activityMap.entrySet()) {
+            toFullClassName = entry.getValue().getClass().getName();
+            toClassName = toFullClassName.substring(toFullClassName.lastIndexOf('.') + 1);
+            toActivityId = entry.getValue().getActivityId();
+            toNode = graphBuilder.createNode(DotFormatBuilder.StringValue.of(toActivityId.toString()),
+                    DotFormatBuilder.StringValue.of(toActivityId.toString() + "-" + toClassName));
+            // process input -> to activity
+            connectors = activityInputMap.get(entry.getKey());
+            if (connectors != null) {
+                for (IConnectorDescriptor connector : connectors) {
+                    fromActivityId = jobActivityGraph.getProducerActivity(connector.getConnectorId());
+                    activity = activityMap.get(fromActivityId);
+                    fromFullClassName = activity.getClass().getName();
+                    fromClassName = fromFullClassName.substring(fromFullClassName.lastIndexOf('.') + 1);
+                    fromNode = graphBuilder.createNode(DotFormatBuilder.StringValue.of(fromActivityId.toString()),
+                            DotFormatBuilder.StringValue.of(fromActivityId.toString() + "-" + fromClassName));
+                    Pair<ActivityId, ActivityId> newPair = new ImmutablePair<>(fromActivityId, toActivityId);
+                    if (!activitiesPairedSet.contains(newPair)) {
+                        activitiesPairedSet.add(newPair);
+                        graphBuilder.createEdge(fromNode, toNode);
+                    }
+                }
+            }
+
+            // process from activity -> output
+            fromActivityId = toActivityId;
+            fromNode = toNode;
+            connectors = activityOutputMap.get(entry.getKey());
+            if (connectors != null) {
+                for (IConnectorDescriptor connector : connectors) {
+                    toActivityId = jobActivityGraph.getConsumerActivity(connector.getConnectorId());
+                    activity = activityMap.get(toActivityId);
+                    toFullClassName = activity.getClass().getName();
+                    toClassName = toFullClassName.substring(toFullClassName.lastIndexOf('.') + 1);
+                    toNode = graphBuilder.createNode(DotFormatBuilder.StringValue.of(toActivityId.toString()),
+                            DotFormatBuilder.StringValue.of(toActivityId.toString() + "-" + toClassName));
+                    Pair<ActivityId, ActivityId> newPair = new ImmutablePair<>(fromActivityId, toActivityId);
+                    if (!activitiesPairedSet.contains(newPair)) {
+                        activitiesPairedSet.add(newPair);
+                        graphBuilder.createEdge(fromNode, toNode);
+                    }
+                }
+            }
+        }
+
+        final Map<ActivityId, Set<ActivityId>> blocked2BlockerMap = jobActivityGraph.getBlocked2BlockerMap();
+        IActivity blockedActivity;
+        for (Map.Entry<ActivityId, Set<ActivityId>> entry : blocked2BlockerMap.entrySet()) {
+            blockedActivity = activityMap.get(entry.getKey());
+            toFullClassName = blockedActivity.getClass().getName();
+            toClassName = toFullClassName.substring(toFullClassName.lastIndexOf('.') + 1);
+            toActivityId = entry.getKey();
+            toNode = graphBuilder.createNode(DotFormatBuilder.StringValue.of(toActivityId.toString()),
+                    DotFormatBuilder.StringValue.of(toActivityId.toString() + "-" + toClassName));
+            for (ActivityId blockingActivityId : entry.getValue()) {
+                fromActivityId = blockingActivityId;
+                activity = activityMap.get(fromActivityId);
+                fromFullClassName = activity.getClass().getName();
+                fromClassName = fromFullClassName.substring(fromFullClassName.lastIndexOf('.') + 1);
+                fromNode = graphBuilder.createNode(DotFormatBuilder.StringValue.of(fromActivityId.toString()),
+                        DotFormatBuilder.StringValue.of(fromActivityId.toString() + "-" + fromClassName));
+                Pair<ActivityId, ActivityId> newPair = new ImmutablePair<>(fromActivityId, toActivityId);
+                if (!activitiesPairedSet.contains(newPair)) {
+                    activitiesPairedSet.add(newPair);
+                    graphBuilder.createEdge(fromNode, toNode).setColor(DotFormatBuilder.Color.RED);
+                }
+            }
+        }
+
+        return graphBuilder.getDotDocument();
+    }
+
+    /**
+     * Generates DOT format for {@link JobSpecification} that can be visualized
+     * using any DOT format visualizer.
+     *
+     * @param jobSpecification The job specification
+     * @return DOT format
+     */
+    public static String generate(final JobSpecification jobSpecification) {
+        final DotFormatBuilder graphBuilder =
+                new DotFormatBuilder(DotFormatBuilder.StringValue.of("JobSpecification"));
+        final Map<ConnectorDescriptorId, IConnectorDescriptor> connectorMap = jobSpecification.getConnectorMap();
+        final Map<ConnectorDescriptorId, Pair<Pair<IOperatorDescriptor, Integer>, Pair<IOperatorDescriptor, Integer>>>
+                cOp = jobSpecification.getConnectorOperatorMap();
+        ConnectorDescriptorId connectorId;
+        IConnectorDescriptor connector;
+        IOperatorDescriptor leftOperator;
+        IOperatorDescriptor rightOperator;
+        DotFormatBuilder.Node sourceNode;
+        DotFormatBuilder.Node destinationNode;
+        String source;
+        String destination;
+        String edgeLabel;
+        for (Map.Entry<ConnectorDescriptorId,
+                Pair<Pair<IOperatorDescriptor, Integer>, Pair<IOperatorDescriptor, Integer>>> entry : cOp.entrySet()) {
+            connectorId = entry.getKey();
+            connector = connectorMap.get(connectorId);
+            edgeLabel = connector.getClass().getName().substring(connector.getClass().getName().lastIndexOf(".") + 1);
+            edgeLabel += "-" + connectorId;
+            leftOperator = entry.getValue().getLeft().getLeft();
+            rightOperator = entry.getValue().getRight().getLeft();
+            source = leftOperator.getClass().getName().substring(
+                    leftOperator.getClass().getName().lastIndexOf(".") + 1);
+            sourceNode = graphBuilder.createNode(DotFormatBuilder.StringValue.of(leftOperator.toString()),
+                    DotFormatBuilder.StringValue.of(leftOperator.toString() + "-" + source));
+            destination = rightOperator.getClass().getName().substring(
+                    rightOperator.getClass().getName().lastIndexOf(".") + 1);
+            destinationNode = graphBuilder.createNode(DotFormatBuilder.StringValue.of(rightOperator.toString()),
+                    DotFormatBuilder.StringValue.of(rightOperator.toString() + "-" + destination));
+            graphBuilder.createEdge(sourceNode, destinationNode).setLabel(DotFormatBuilder.StringValue.of(edgeLabel));
+        }
+
+        return graphBuilder.getDotDocument();
+    }
+
+    /**
+     * Generates DOT format for {@link ILogicalPlan} that can be visualized
+     * using any DOT format visualizer.
+     *
+     * @param plan  The logical plan
+     * @param dotVisitor    The DOT visitor
+     * @return DOT format
+     * @throws AlgebricksException
+     */
+    public static String generate(ILogicalPlan plan, LogicalOperatorDotVisitor dotVisitor) throws AlgebricksException {
+        final DotFormatBuilder graphBuilder = new DotFormatBuilder(DotFormatBuilder.StringValue.of("Plan"));
+        ILogicalOperator root = plan.getRoots().get(0).getValue();
+        generateNode(graphBuilder, root, dotVisitor, new HashSet<>());
+        return graphBuilder.getDotDocument();
+    }
+
+    public static void generateNode(DotFormatBuilder dotBuilder, ILogicalOperator op,
+            LogicalOperatorDotVisitor dotVisitor, Set<ILogicalOperator> operatorsVisited)
+            throws AlgebricksException {
+        DotFormatBuilder.StringValue destinationNodeLabel = formatStringOf(op, dotVisitor);
+        DotFormatBuilder.Node destinationNode = dotBuilder.createNode(DotFormatBuilder.StringValue.of(
+                Integer.toString(op.hashCode())), destinationNodeLabel);
+        DotFormatBuilder.StringValue sourceNodeLabel;
+        DotFormatBuilder.Node sourceNode;
+        for (Mutable<ILogicalOperator> child : op.getInputs()) {
+            sourceNodeLabel = formatStringOf(child.getValue(), dotVisitor);
+            sourceNode = dotBuilder.createNode(DotFormatBuilder.StringValue.of(
+                    Integer.toString(child.getValue().hashCode())), sourceNodeLabel);
+            dotBuilder.createEdge(sourceNode, destinationNode);
+            if (!operatorsVisited.contains(child.getValue())) {
+                generateNode(dotBuilder, child.getValue(), dotVisitor, operatorsVisited);
+            }
+        }
+        if (((AbstractLogicalOperator) op).hasNestedPlans()) {
+            ILogicalOperator nestedOperator;
+            for (ILogicalPlan nestedPlan : ((AbstractOperatorWithNestedPlans) op).getNestedPlans()) {
+                nestedOperator = nestedPlan.getRoots().get(0).getValue();
+                sourceNodeLabel = formatStringOf(nestedOperator, dotVisitor);
+                sourceNode = dotBuilder.createNode(DotFormatBuilder.StringValue.of(
+                        Integer.toString(nestedOperator.hashCode())), sourceNodeLabel);
+                dotBuilder.createEdge(sourceNode, destinationNode).
+                        setLabel(DotFormatBuilder.StringValue.of("subplan"));
+                if (!operatorsVisited.contains(nestedOperator)) {
+                    generateNode(dotBuilder, nestedOperator, dotVisitor, operatorsVisited);
+                }
+            }
+        }
+        if (!(op instanceof ExchangeOperator)) {
+            destinationNode.setFillColor(DotFormatBuilder.Color.SKYBLUE);
+        }
+
+        // replicate/split operator
+        if (op.getOperatorTag() == LogicalOperatorTag.REPLICATE || op.getOperatorTag() == LogicalOperatorTag.SPLIT) {
+            AbstractReplicateOperator replicateOperator = (AbstractReplicateOperator) op;
+            ILogicalOperator replicateOutput;
+            sourceNode = destinationNode;
+            for (int i = 0; i < replicateOperator.getOutputs().size(); i++) {
+                replicateOutput = replicateOperator.getOutputs().get(i).getValue();
+                destinationNodeLabel = formatStringOf(replicateOutput, dotVisitor);
+                destinationNode = dotBuilder.createNode(DotFormatBuilder.StringValue.of(
+                        Integer.toString(replicateOutput.hashCode())), destinationNodeLabel);
+                if (replicateOperator.getOutputMaterializationFlags()[i]) {
+                    dotBuilder.createEdge(sourceNode, destinationNode).setColor(DotFormatBuilder.Color.RED);
+                } else {
+                    dotBuilder.createEdge(sourceNode, destinationNode);
+                }
+            }
+        }
+
+        operatorsVisited.add(op);
+    }
+
+    private static DotFormatBuilder.StringValue formatStringOf(ILogicalOperator operator,
+            LogicalOperatorDotVisitor dotVisitor) throws AlgebricksException {
+        String formattedString = operator.accept(dotVisitor, null).trim();
+        IPhysicalOperator physicalOperator = ((AbstractLogicalOperator)operator).getPhysicalOperator();
+        if (physicalOperator != null) {
+            formattedString += "\\n" + physicalOperator.toString().trim() + " |" + operator.getExecutionMode() + "|";
+        } else {
+            formattedString += "\\n|" + operator.getExecutionMode() + "|";
+        }
+
+        return DotFormatBuilder.StringValue.of(formattedString);
+    }
+}
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/utils/LogicalOperatorDotVisitor.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/utils/LogicalOperatorDotVisitor.java
new file mode 100644
index 0000000..a54ff63
--- /dev/null
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/utils/LogicalOperatorDotVisitor.java
@@ -0,0 +1,492 @@
+/*
+ * 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.hyracks.algebricks.core.utils;
+
+import java.util.List;
+
+import org.apache.commons.lang3.mutable.Mutable;
+import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
+import org.apache.hyracks.algebricks.common.utils.Pair;
+import org.apache.hyracks.algebricks.common.utils.Triple;
+import org.apache.hyracks.algebricks.core.algebra.base.ILogicalExpression;
+import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractUnnestMapOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.AggregateOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.AssignOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.DataSourceScanOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.DelegateOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.DistinctOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.DistributeResultOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.EmptyTupleSourceOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.ExchangeOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.GroupByOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.IndexInsertDeleteUpsertOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.InnerJoinOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.InsertDeleteUpsertOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.InsertDeleteUpsertOperator.Kind;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.IntersectOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.LeftOuterJoinOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.LeftOuterUnnestMapOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.LeftOuterUnnestOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.LimitOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.MaterializeOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.NestedTupleSourceOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.OrderOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.ProjectOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.ReplicateOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.RunningAggregateOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.ScriptOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.SelectOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.SinkOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.SplitOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.SubplanOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.TokenizeOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnionAllOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestMapOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.WriteOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.WriteResultOperator;
+import org.apache.hyracks.algebricks.core.algebra.visitors.ILogicalOperatorVisitor;
+
+public class LogicalOperatorDotVisitor implements ILogicalOperatorVisitor<String, Void> {
+
+    private final StringBuilder stringBuilder;
+
+    public LogicalOperatorDotVisitor() {
+        stringBuilder = new StringBuilder();
+    }
+
+    @Override
+    public String toString() {
+        return "";
+    }
+
+    private CharSequence str(Object o) {
+        return String.valueOf(o);
+    }
+
+    @Override
+    public String visitAggregateOperator(AggregateOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("aggregate ").append(str(op.getVariables())).append(" <- ");
+        pprintExprList(op.getExpressions());
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitRunningAggregateOperator(RunningAggregateOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("running-aggregate ").append(str(op.getVariables())).append(" <- ");
+        pprintExprList(op.getExpressions());
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitEmptyTupleSourceOperator(EmptyTupleSourceOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("empty-tuple-source");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitGroupByOperator(GroupByOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("group by").append(op.isGroupAll() ? " (all)" : "").append(" (");
+        pprintVeList(op.getGroupByList());
+        stringBuilder.append(") decor (");
+        pprintVeList(op.getDecorList());
+        stringBuilder.append(")");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitDistinctOperator(DistinctOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("distinct (");
+        pprintExprList(op.getExpressions());
+        stringBuilder.append(")");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitInnerJoinOperator(InnerJoinOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("join (").append(op.getCondition().getValue().toString()).append(")");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitLeftOuterJoinOperator(LeftOuterJoinOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("left outer join (").append(op.getCondition().getValue().toString()).append(")");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitNestedTupleSourceOperator(NestedTupleSourceOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("nested tuple source");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitOrderOperator(OrderOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("order ");
+        for (Pair<OrderOperator.IOrder, Mutable<ILogicalExpression>> p : op.getOrderExpressions()) {
+            if (op.getTopK() != -1) {
+                stringBuilder.append("(topK: ").append(op.getTopK()).append(") ");
+            }
+            String fst = getOrderString(p.first);
+            stringBuilder.append("(").append(fst).append(", ").append(p.second.getValue().toString()).append(") ");
+        }
+        return stringBuilder.toString();
+    }
+
+    private String getOrderString(OrderOperator.IOrder first) {
+        switch (first.getKind()) {
+            case ASC:
+                return "ASC";
+            case DESC:
+                return "DESC";
+            default:
+                return first.getExpressionRef().toString();
+        }
+    }
+
+    @Override
+    public String visitAssignOperator(AssignOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("assign ").append(str(op.getVariables())).append(" <- ");
+        pprintExprList(op.getExpressions());
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitWriteOperator(WriteOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("write ");
+        pprintExprList(op.getExpressions());
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitDistributeResultOperator(DistributeResultOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("distribute result ");
+        pprintExprList(op.getExpressions());
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitWriteResultOperator(WriteResultOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("load ").append(str(op.getDataSource())).append(" from ")
+                .append(op.getPayloadExpression().getValue().toString()).append(" partitioned by ");
+        pprintExprList(op.getKeyExpressions());
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitSelectOperator(SelectOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("select (").append(op.getCondition().getValue().toString()).append(")");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitProjectOperator(ProjectOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("project ").append("(").append(op.getVariables()).append(")");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitSubplanOperator(SubplanOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("subplan {}");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitUnionOperator(UnionAllOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("union");
+        for (Triple<LogicalVariable, LogicalVariable, LogicalVariable> v : op.getVariableMappings()) {
+            stringBuilder.append(" (").append(v.first).append(", ").append(v.second).append(", ").append(v.third)
+                    .append(")");
+        }
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitIntersectOperator(IntersectOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("intersect (");
+        stringBuilder.append('[');
+        for (int i = 0; i < op.getOutputVars().size(); i++) {
+            if (i > 0) {
+                stringBuilder.append(", ");
+            }
+            stringBuilder.append(str(op.getOutputVars().get(i)));
+        }
+        stringBuilder.append("] <- [");
+        for (int i = 0; i < op.getNumInput(); i++) {
+            if (i > 0) {
+                stringBuilder.append(", ");
+            }
+            stringBuilder.append('[');
+            for (int j = 0; j < op.getInputVariables(i).size(); j++) {
+                if (j > 0) {
+                    stringBuilder.append(", ");
+                }
+                stringBuilder.append(str(op.getInputVariables(i).get(j)));
+            }
+            stringBuilder.append(']');
+        }
+        stringBuilder.append("])");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitUnnestOperator(UnnestOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("unnest ").append(op.getVariable());
+        if (op.getPositionalVariable() != null) {
+            stringBuilder.append(" at ").append(op.getPositionalVariable());
+        }
+        stringBuilder.append(" <- ").append(op.getExpressionRef().getValue().toString());
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitLeftOuterUnnestOperator(LeftOuterUnnestOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("outer-unnest ").append(op.getVariable());
+        if (op.getPositionalVariable() != null) {
+            stringBuilder.append(" at ").append(op.getPositionalVariable());
+        }
+        stringBuilder.append(" <- ").append(op.getExpressionRef().getValue().toString());
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitUnnestMapOperator(UnnestMapOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        return printAbstractUnnestMapOperator(op, "unnest-map");
+    }
+
+    @Override
+    public String visitLeftOuterUnnestMapOperator(LeftOuterUnnestMapOperator op, Void noArgs)
+            throws AlgebricksException {
+        stringBuilder.setLength(0);
+        return printAbstractUnnestMapOperator(op, "left-outer-unnest-map");
+    }
+
+    private String printAbstractUnnestMapOperator(AbstractUnnestMapOperator op, String opSignature) {
+        stringBuilder.append(opSignature).append(" ").append(op.getVariables()).append(" <- ")
+                .append(op.getExpressionRef().getValue().toString());
+        appendFilterInformation(stringBuilder, op.getMinFilterVars(), op.getMaxFilterVars());
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitDataScanOperator(DataSourceScanOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("data-scan ").append(op.getProjectVariables()).append("<-").append(op.getVariables())
+                .append(" <- ").append(op.getDataSource());
+        appendFilterInformation(stringBuilder, op.getMinFilterVars(), op.getMaxFilterVars());
+        return stringBuilder.toString();
+    }
+
+    private String appendFilterInformation(StringBuilder plan, List<LogicalVariable> minFilterVars,
+            List<LogicalVariable> maxFilterVars) {
+        if (minFilterVars != null || maxFilterVars != null) {
+            plan.append(" with filter on");
+        }
+        if (minFilterVars != null) {
+            plan.append(" min:").append(minFilterVars);
+        }
+        if (maxFilterVars != null) {
+            plan.append(" max:").append(maxFilterVars);
+        }
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitLimitOperator(LimitOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("limit ").append(op.getMaxObjects().getValue().toString());
+        ILogicalExpression offset = op.getOffset().getValue();
+        if (offset != null) {
+            stringBuilder.append(", ").append(offset.toString());
+        }
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitExchangeOperator(ExchangeOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("exchange");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitScriptOperator(ScriptOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("script (in: ").append(op.getInputVariables()).append(") (out: " )
+                .append(op.getOutputVariables()).append(")");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitReplicateOperator(ReplicateOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("replicate");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitSplitOperator(SplitOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        Mutable<ILogicalExpression> branchingExpression = op.getBranchingExpression();
+        stringBuilder.append("split ").append(branchingExpression.getValue().toString());
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitMaterializeOperator(MaterializeOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("materialize");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitInsertDeleteUpsertOperator(InsertDeleteUpsertOperator op, Void noArgs)
+            throws AlgebricksException {
+        stringBuilder.setLength(0);
+        String header = getIndexOpString(op.getOperation());
+        stringBuilder.append(header).append(str(op.getDataSource())).append(" from record: ")
+                .append(op.getPayloadExpression().getValue().toString());
+        if (op.getAdditionalNonFilteringExpressions() != null) {
+            stringBuilder.append(", meta: ");
+            pprintExprList(op.getAdditionalNonFilteringExpressions());
+        }
+        stringBuilder.append(" partitioned by ");
+        pprintExprList(op.getPrimaryKeyExpressions());
+        if (op.getOperation() == Kind.UPSERT) {
+            stringBuilder.append(" out: ([record-before-upsert:").append(op.getBeforeOpRecordVar());
+            if (op.getBeforeOpAdditionalNonFilteringVars() != null) {
+                stringBuilder.append(", additional-before-upsert: ").append(op.getBeforeOpAdditionalNonFilteringVars());
+            }
+            stringBuilder.append("]) ");
+        }
+        if (op.isBulkload()) {
+            stringBuilder.append(" [bulkload]");
+        }
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitIndexInsertDeleteUpsertOperator(IndexInsertDeleteUpsertOperator op, Void noArgs)
+            throws AlgebricksException {
+        stringBuilder.setLength(0);
+        String header = getIndexOpString(op.getOperation());
+        stringBuilder.append(header).append(op.getIndexName()).append(" on ")
+                .append(str(op.getDataSourceIndex().getDataSource())).append(" from ");
+        if (op.getOperation() == Kind.UPSERT) {
+            stringBuilder.append(" replace:");
+            pprintExprList(op.getPrevSecondaryKeyExprs());
+            stringBuilder.append(" with:");
+            pprintExprList(op.getSecondaryKeyExpressions());
+        } else {
+            pprintExprList(op.getSecondaryKeyExpressions());
+        }
+        if (op.isBulkload()) {
+            stringBuilder.append(" [bulkload]");
+        }
+        return stringBuilder.toString();
+    }
+
+    private String getIndexOpString(Kind opKind) {
+        switch (opKind) {
+            case DELETE:
+                return "delete from ";
+            case INSERT:
+                return "insert into ";
+            case UPSERT:
+                return "upsert into ";
+        }
+        return null;
+    }
+
+    @Override
+    public String visitTokenizeOperator(TokenizeOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("tokenize ").append(str(op.getTokenizeVars())).append(" <- ");
+        pprintExprList(op.getSecondaryKeyExpressions());
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitSinkOperator(SinkOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append("sink");
+        return stringBuilder.toString();
+    }
+
+    @Override
+    public String visitDelegateOperator(DelegateOperator op, Void noArgs) throws AlgebricksException {
+        stringBuilder.setLength(0);
+        stringBuilder.append(op.toString());
+        return stringBuilder.toString();
+    }
+
+    private void pprintExprList(List<Mutable<ILogicalExpression>> expressions) {
+        stringBuilder.append("[");
+        boolean first = true;
+        for (Mutable<ILogicalExpression> exprRef : expressions) {
+            if (first) {
+                first = false;
+            } else {
+                stringBuilder.append(", ");
+            }
+            stringBuilder.append(exprRef.getValue().toString());
+        }
+        stringBuilder.append("]");
+    }
+
+    private void pprintVeList(List<Pair<LogicalVariable, Mutable<ILogicalExpression>>> vePairList) {
+        stringBuilder.append("[");
+        boolean fst = true;
+        for (Pair<LogicalVariable, Mutable<ILogicalExpression>> ve : vePairList) {
+            if (fst) {
+                fst = false;
+            } else {
+                stringBuilder.append("; ");
+            }
+            if (ve.first != null) {
+                stringBuilder.append(ve.first).append(" := ").append(ve.second);
+            } else {
+                stringBuilder.append(ve.second.getValue().toString());
+            }
+        }
+        stringBuilder.append("]");
+    }
+}