asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dl...@apache.org
Subject [3/3] asterixdb git commit: [ASTERIXDB-2015][IDX] Introduce Secondary Primary Index
Date Tue, 26 Sep 2017 00:23:39 GMT
[ASTERIXDB-2015][IDX] Introduce Secondary Primary Index

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

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

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


Project: http://git-wip-us.apache.org/repos/asf/asterixdb/repo
Commit: http://git-wip-us.apache.org/repos/asf/asterixdb/commit/8351d253
Tree: http://git-wip-us.apache.org/repos/asf/asterixdb/tree/8351d253
Diff: http://git-wip-us.apache.org/repos/asf/asterixdb/diff/8351d253

Branch: refs/heads/master
Commit: 8351d25314175b759b8f02b5246e5b472aa8336a
Parents: fd9c602
Author: Ali Alsuliman <ali.al.solaiman@gmail.com>
Authored: Sun Sep 24 00:43:15 2017 -0700
Committer: Dmitry Lychagin <dmitry.lychagin@couchbase.com>
Committed: Mon Sep 25 17:22:49 2017 -0700

----------------------------------------------------------------------
 .../asterix/optimizer/base/RuleCollections.java |   2 +
 .../rules/FixReplicateOperatorOutputsRule.java  | 167 +++++++
 ...IntroduceSecondaryIndexInsertDeleteRule.java |  10 +
 asterixdb/asterix-app/data/names2.adm           | 120 +++++
 .../asterix/app/translator/QueryTranslator.java |  26 +
 .../src/test/resources/runtimets/only_sqlpp.xml |   2 +-
 .../create-index-1/create-index-1.1.ddl.sqlpp   |  57 +++
 .../create-index-2/create-index-2.1.ddl.sqlpp   |  53 ++
 .../create-index-3/create-index-3.1.ddl.sqlpp   |  53 ++
 .../create-index-4/create-index-4.1.ddl.sqlpp   |  53 ++
 .../create-index-5/create-index-5.1.ddl.sqlpp   |  53 ++
 .../compact-dataset-and-its-indexes.3.ddl.sqlpp |   1 +
 ...e-from-loaded-dataset-with-index.3.ddl.sqlpp |   1 +
 .../drop-empty-secondary-indexes.1.ddl.sqlpp    |   3 +
 .../dml/drop-index/drop-index.3.ddl.sqlpp       |   3 +
 .../empty-load-with-index.1.ddl.sqlpp           |   1 +
 ...sert-and-scan-dataset-with-index.1.ddl.sqlpp |   1 +
 .../insert-duplicated-keys.1.ddl.sqlpp          |   1 +
 ...rt-into-empty-dataset-with-index.1.ddl.sqlpp |   1 +
 ...nto-loaded-dataset-with-index_01.3.ddl.sqlpp |   1 +
 ...nto-loaded-dataset-with-index_02.3.ddl.sqlpp |   1 +
 ...ed-pk_adm-with-sec-primary-index.1.ddl.sqlpp |  42 ++
 ...pk_adm-with-sec-primary-index.2.update.sqlpp |  29 ++
 ...-pk_adm-with-sec-primary-index.3.query.sqlpp |  31 ++
 .../load-with-index/load-with-index.1.ddl.sqlpp |   1 +
 .../recreate-index/recreate-index.1.ddl.sqlpp   |   1 +
 .../recreate-index/recreate-index.3.ddl.sqlpp   |   3 +
 .../using-constant-merge-policy.3.ddl.sqlpp     |   1 +
 ...ed-prefix-merge-policy-with-feed.3.ddl.sqlpp |   1 +
 ...g-correlated-prefix-merge-policy.3.ddl.sqlpp |   1 +
 .../using-no-merge-policy.3.ddl.sqlpp           |   1 +
 .../using-prefix-merge-policy.3.ddl.sqlpp       |   1 +
 ...-with-correlated-secondary-btree.3.ddl.sqlpp |   1 +
 .../insert-with-secondary-btree.3.ddl.sqlpp     |   1 +
 .../load-with-secondary-btree.3.ddl.sqlpp       |   1 +
 .../btree-sec-primary-index.1.ddl.sqlpp         |  41 ++
 .../btree-sec-primary-index.2.update.sqlpp      |  30 ++
 .../btree-sec-primary-index.3.ddl.sqlpp         |  29 ++
 .../btree-sec-primary-index.4.query.sqlpp       |  29 ++
 ...s-same-as-pk-but-different-order.1.ddl.sqlpp |  40 ++
 .../keys-same-as-pk-in-same-order.2.ddl.sqlpp   |  40 ++
 .../validations/repetitive-keys.3.ddl.sqlpp     |  40 ++
 .../compact-dataset-and-its-indexes.3.ddl.sqlpp |   1 +
 ...d-dataset-with-sec-primary-index.1.ddl.sqlpp |  45 ++
 ...ataset-with-sec-primary-index.2.update.sqlpp |  28 ++
 ...d-dataset-with-sec-primary-index.3.ddl.sqlpp |  28 ++
 ...ataset-with-sec-primary-index.4.update.sqlpp |  27 +
 ...dataset-with-sec-primary-index.5.query.sqlpp |  27 +
 .../drop-index/drop-index.3.ddl.sqlpp           |   3 +
 ...y-dataset-with-sec-primary-index.1.ddl.sqlpp |  58 +++
 ...ataset-with-sec-primary-index.2.update.sqlpp |  32 ++
 ...dataset-with-sec-primary-index.3.query.sqlpp |  27 +
 ...d-dataset-with-sec-primary-index.1.ddl.sqlpp |  44 ++
 ...ataset-with-sec-primary-index.2.update.sqlpp |  27 +
 ...d-dataset-with-sec-primary-index.3.ddl.sqlpp |  27 +
 ...ataset-with-sec-primary-index.4.update.sqlpp |  30 ++
 ...dataset-with-sec-primary-index.5.query.sqlpp |  27 +
 .../load-with-sec-primary-index.1.ddl.sqlpp     |  46 ++
 .../load-with-sec-primary-index.2.update.sqlpp  |  28 ++
 .../load-with-sec-primary-index.3.query.sqlpp   |  27 +
 .../btree-sec-primary-index.1.ddl.sqlpp         |  58 +++
 .../btree-sec-primary-index.2.update.sqlpp      |  34 ++
 .../btree-sec-primary-index.3.ddl.sqlpp         |  30 ++
 .../btree-sec-primary-index.4.query.sqlpp       |  30 ++
 ...e-from-loaded-dataset-with-index.3.ddl.sqlpp |   1 +
 .../drop-empty-secondary-indexes.1.ddl.sqlpp    |   3 +
 .../drop-index/drop-index.3.ddl.sqlpp           |   3 +
 .../empty-load-with-index.1.ddl.sqlpp           |   1 +
 ...an-dataset-with-correlated-index.3.ddl.sqlpp |   2 +
 ...sert-and-scan-dataset-with-index.1.ddl.sqlpp |   1 +
 .../filtered-dataset.1.ddl.sqlpp                |   1 +
 .../multiple-correlated-secondaries.3.ddl.sqlpp |   1 +
 .../multiple-secondaries.1.ddl.sqlpp            |   1 +
 .../primary-secondary-btree.1.ddl.sqlpp         |   1 +
 ...enerated-pk_adm-with-sec-primary-index.3.adm |   1 +
 .../btree-sec-primary-index.4.adm               |  10 +
 ...-loaded-dataset-with-sec-primary-index.5.adm |  11 +
 ...o-empty-dataset-with-sec-primary-index.3.adm |  10 +
 ...-loaded-dataset-with-sec-primary-index.5.adm |  10 +
 .../load-with-sec-primary-index.3.adm           |  10 +
 .../btree-sec-primary-index.4.adm               |   1 +
 .../resources/runtimets/testsuite_sqlpp.xml     |  95 +++-
 .../asterix/common/exceptions/ErrorCode.java    |   4 +
 .../main/resources/asx_errormsg/en.properties   |   4 +
 .../asterix-lang-aql/src/main/javacc/AQL.jj     |  63 ++-
 .../asterix-lang-sqlpp/src/main/javacc/SQLPP.jj |  63 ++-
 .../utils/SecondaryBTreeOperationsHelper.java   |  17 +
 .../algebricks/core/utils/DotFormatBuilder.java | 253 ++++++++++
 .../core/utils/DotFormatGenerator.java          | 277 +++++++++++
 .../core/utils/LogicalOperatorDotVisitor.java   | 492 +++++++++++++++++++
 90 files changed, 2941 insertions(+), 53 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java
----------------------------------------------------------------------
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.DisjunctivePredicateToJoinRule;
 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 @@ public final class RuleCollections {
         prepareForJobGenRewrites.add(new PushGroupByIntoSortRule());
         prepareForJobGenRewrites.add(new SetExecutionModeRule());
         prepareForJobGenRewrites.add(new SweepIllegalNonfunctionalFunctions());
+        prepareForJobGenRewrites.add(new FixReplicateOperatorOutputsRule());
         return prepareForJobGenRewrites;
     }
 }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/FixReplicateOperatorOutputsRule.java
----------------------------------------------------------------------
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);
+    }
+}

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/IntroduceSecondaryIndexInsertDeleteRule.java
----------------------------------------------------------------------
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 @@ public class IntroduceSecondaryIndexInsertDeleteRule implements IAlgebraicRewrit
                 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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/data/names2.adm
----------------------------------------------------------------------
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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/main/java/org/apache/asterix/app/translator/QueryTranslator.java
----------------------------------------------------------------------
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.Collections;
 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 @@ public class QueryTranslator extends AbstractLangTranslator implements IStatemen
                     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 @@ public class QueryTranslator extends AbstractLangTranslator implements IStatemen
             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 @@ public class QueryTranslator extends AbstractLangTranslator implements IStatemen
                         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 @@ public class QueryTranslator extends AbstractLangTranslator implements IStatemen
                             "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;

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

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

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

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

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

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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/compact-dataset-and-its-indexes/compact-dataset-and-its-indexes.3.ddl.sqlpp
@@ -24,3 +24,4 @@ create  index idx_LineItem_partkey  on LineItem (l_linenumber) type btree;
 
 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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/delete-from-loaded-dataset-with-index/delete-from-loaded-dataset-with-index.3.ddl.sqlpp
@@ -24,3 +24,4 @@ create  index idx_LineItem_partkey  on LineItem (l_linenumber) type btree;
 
 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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
index d93f069..6a64fbd 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-empty-secondary-indexes/drop-empty-secondary-indexes.1.ddl.sqlpp
@@ -65,6 +65,8 @@ create  index nested  on t1 (person.name.first) type btree;
 
 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.keyWD_indx_open;
 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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp
index 013c209..4badf84 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/drop-index/drop-index.3.ddl.sqlpp
@@ -24,5 +24,8 @@ create  index idx_t1_str1  on t1 (stringu1) type btree;
 
 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;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
index 751bdeb..f62364e 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/empty-load-with-index/empty-load-with-index.1.ddl.sqlpp
@@ -53,3 +53,4 @@ create  dataset LineItem(LineItemType) primary key l_orderkey,l_linenumber;
 
 create  index part_index  on LineItem (l_partkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
index 1bff150..dfbfb63 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-and-scan-dataset-with-index/insert-and-scan-dataset-with-index.1.ddl.sqlpp
@@ -43,3 +43,4 @@ create  dataset test.employee(Emp) primary key id;
 
 create  index idx_employee_first_name  on test.employee (fname) type btree;
 
+create  primary index sec_primary_idx  on test.employee;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp
index 2cb2f83..eef86a3 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-duplicated-keys/insert-duplicated-keys.1.ddl.sqlpp
@@ -37,3 +37,4 @@ create  dataset SimpleGeoPlace(SimpleGeoPlaceType) primary key id;
 
 create  index btreeName  on SimpleGeoPlace (name) type btree;
 
+create  primary index sec_primary_idx  on SimpleGeoPlace;
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-empty-dataset-with-index/insert-into-empty-dataset-with-index.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-empty-dataset-with-index/insert-into-empty-dataset-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-empty-dataset-with-index/insert-into-empty-dataset-with-index.1.ddl.sqlpp
index 3a7ffe8..1ba5ece 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_partkey  on LineID (l_linenumber) type btree;
 
 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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_01/insert-into-loaded-dataset-with-index_01.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_01/insert-into-loaded-dataset-with-index_01.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_01/insert-into-loaded-dataset-with-index_01.3.ddl.sqlpp
index ec12e33..98e5016 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_01/insert-into-loaded-dataset-with-index_01.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_01/insert-into-loaded-dataset-with-index_01.3.ddl.sqlpp
@@ -24,3 +24,4 @@ create  index idx_LineID_partkey  on LineID (l_linenumber) type btree;
 
 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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_02/insert-into-loaded-dataset-with-index_02.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_02/insert-into-loaded-dataset-with-index_02.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_02/insert-into-loaded-dataset-with-index_02.3.ddl.sqlpp
index ec12e33..98e5016 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_02/insert-into-loaded-dataset-with-index_02.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-into-loaded-dataset-with-index_02/insert-into-loaded-dataset-with-index_02.3.ddl.sqlpp
@@ -24,3 +24,4 @@ create  index idx_LineID_partkey  on LineID (l_linenumber) type btree;
 
 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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.1.ddl.sqlpp
new file mode 100644
index 0000000..5c943d8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.1.ddl.sqlpp
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Testing secondary primary index with autogenerated primary key
+ * Expected Result : Success
+ * Date            : Jul 30 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.DBLPType as
+ closed {
+  id : uuid,
+  dblpid : string,
+  title : string,
+  authors : string,
+  misc : string
+};
+
+create  dataset DBLP(DBLPType) primary key id autogenerated ;
+
+create primary index sec_primary_idx on DBLP;
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.2.update.sqlpp
new file mode 100644
index 0000000..7fed49f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.2.update.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Testing secondary primary index with autogenerated primary key
+ * Expected Result : Success
+ * Date            : Jul 30 2017
+ */
+
+use test;
+
+
+insert into DBLP
+select element {'dblpid':'books/acm/kim95/Blakeley95','title':'OQL[C++]  Extending C++ with an Object Query Capability.','authors':'José A. Blakeley','misc':'2002-01-03 69-88 Modern Database Systems db/books/collections/kim95.html#Blakeley95 1995'};

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.3.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.3.query.sqlpp
new file mode 100644
index 0000000..bd12757
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/insert-with-autogenerated-pk_adm-with-sec-primary-index/insert-with-autogenerated-pk_adm-with-sec-primary-index.3.query.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Testing secondary primary index with autogenerated primary key
+ * Expected Result : Success
+ * Date            : Jul 30 2017
+ */
+ 
+use test;
+
+
+select element o.title
+from  DBLP as o
+where test.contains(o.title,'Extending')
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/load-with-index/load-with-index.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/load-with-index/load-with-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/load-with-index/load-with-index.1.ddl.sqlpp
index b2d43a6..893fdac 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/load-with-index/load-with-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/load-with-index/load-with-index.1.ddl.sqlpp
@@ -47,3 +47,4 @@ create  dataset LineItem(LineItemType) primary key l_orderkey,l_linenumber;
 
 create  index idx_partkey  on LineItem (l_partkey) type btree;
 
+create  primary index sec_primary_idx  on LineItem;
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp
index 79f376a..fb79495 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.1.ddl.sqlpp
@@ -44,3 +44,4 @@ create  dataset test.employee(Emp) primary key id;
 
 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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp
index 0f86a33..e2ec595 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/recreate-index/recreate-index.3.ddl.sqlpp
@@ -29,3 +29,6 @@ use test;
 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;
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-constant-merge-policy/using-constant-merge-policy.3.ddl.sqlpp
@@ -24,3 +24,4 @@ create  index idx_LineItem_partkey  on LineItem (l_linenumber) type btree;
 
 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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy-with-feed/using-correlated-prefix-merge-policy-with-feed.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy-with-feed/using-correlated-prefix-merge-policy-with-feed.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy-with-feed/using-correlated-prefix-merge-policy-with-feed.3.ddl.sqlpp
index fff1e39..c551c29 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy-with-feed/using-correlated-prefix-merge-policy-with-feed.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy-with-feed/using-correlated-prefix-merge-policy-with-feed.3.ddl.sqlpp
@@ -23,3 +23,4 @@ create  index idx_LineItem_partkey  on LineItem (l_linenumber) type btree;
 
 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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp
index 873c727..f64c5a3 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-correlated-prefix-merge-policy/using-correlated-prefix-merge-policy.3.ddl.sqlpp
@@ -24,3 +24,4 @@ create  index idx_LineItem_partkey  on LineItem (l_linenumber) type btree;
 
 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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-no-merge-policy/using-no-merge-policy.3.ddl.sqlpp
@@ -24,3 +24,4 @@ create  index idx_LineItem_partkey  on LineItem (l_linenumber) type btree;
 
 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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp
index 873c727..cb40111 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/dml/using-prefix-merge-policy/using-prefix-merge-policy.3.ddl.sqlpp
@@ -24,3 +24,4 @@ create  index idx_LineItem_partkey  on LineItem (l_linenumber) type btree;
 
 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

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

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

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

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp
new file mode 100644
index 0000000..242bf5d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.1.ddl.sqlpp
@@ -0,0 +1,41 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.Emp as
+ closed {
+  id : bigint,
+  fname : string,
+  lname : string,
+  age : bigint,
+  dept : string
+};
+
+create  dataset employee(Emp) primary key id;
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.2.update.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.2.update.sqlpp
new file mode 100644
index 0000000..86d776a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.2.update.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+
+load  dataset employee using localfs ((`path`=`asterix_nc1://data/names.adm`),(`format`=`delimited-text`),(`delimiter`=`|`));
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.sqlpp
new file mode 100644
index 0000000..452938b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.3.ddl.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+
+create primary index sec_primary_idx on employee ;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.query.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.query.sqlpp
new file mode 100644
index 0000000..6dad4b0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/btree-sec-primary-index/btree-sec-primary-index.4.query.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Testing selecting primary index with the existence of secondary primary index for predicates that
+ *                 : could be answered by primary index
+ * Expected Result : Success
+ * Date            : Jul 31 2017
+ */
+
+use test;
+
+select element l
+from  employee as l where l.id < 200 order by l.id;
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8351d253/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-but-different-order.1.ddl.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-but-different-order.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-but-different-order.1.ddl.sqlpp
new file mode 100644
index 0000000..1194907
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index/validations/keys-same-as-pk-but-different-order.1.ddl.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+ * Description     : Test creating an index with the same keys as primary keys but in different order
+ * Expected Result : Success
+ * Date            : Aug 3 2017
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+
+create type test.testType as
+{
+  id : integer,
+  `value` : string
+}
+
+create  dataset testDS(testType) primary key id, `value`;
+
+create  index testIdx  on testDS (`value`, id);
+


Mime
View raw message