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 dd8f630..df8d4cd 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
@@ -860,7 +860,7 @@
 
                 AbstractFunctionCallExpression theFieldAccessFunc;
                 LogicalVariable fieldVar = context.newVar();
-                if (fieldType == null) {
+                if (fieldType == null && !ATypeTag.ANY.equals(skType.getTypeTag())) {
                     // Open field. must prevent inlining to maintain the cast before the primaryOp and
                     // make handling of records with incorrect value type for this field easier and cleaner
                     context.addNotToBeInlinedVar(fieldVar);
@@ -906,6 +906,9 @@
         FunctionIdentifier skFun = null;
         IAObject fmtArg = null;
         Pair<FunctionIdentifier, IAObject> castExpr;
+        if (ATypeTag.ANY.equals(skType.getTypeTag())) {
+            return new IndexFieldId(skSrc, skName, skType.getTypeTag(), skFun, fmtArg);
+        }
         if (fieldType == null) {
             // open field
             castExpr = getCastExpression(index, skType, srcLoc);
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
index b5041f0..24ef2d7 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AbstractIntroduceAccessMethodRule.java
@@ -485,7 +485,9 @@
 
                     for (int j = 0; j < matchedTypes.size(); j++) {
                         for (int k = j + 1; k < matchedTypes.size(); k++) {
-                            typeMatch &= isMatched(elementTypes.get(j), elementTypes.get(k), jaccardSimilarity);
+                            if (!ATypeTag.ANY.equals(elementTypes.get(k).getTypeTag())) {
+                                typeMatch &= isMatched(elementTypes.get(j), elementTypes.get(k), jaccardSimilarity);
+                            }
                         }
                     }
 
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java
index 1ff90c6..9b30dfb 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/AccessMethodUtils.java
@@ -1133,20 +1133,40 @@
         }
     }
 
-    private static AbstractUnnestMapOperator createFinalNonIndexOnlySearchPlan(Dataset dataset,
-            ILogicalOperator inputOp, IOptimizationContext context, boolean sortPrimaryKeys, boolean retainInput,
-            boolean retainMissing, boolean requiresBroadcast, boolean requiresDistinct,
-            List<LogicalVariable> primaryKeyVars, List<LogicalVariable> primaryIndexUnnestVars,
-            List<LogicalVariable> auxDistinctVars, List<Object> primaryIndexOutputTypes,
-            IAlgebricksConstantValue leftOuterMissingValue) throws AlgebricksException {
-        SourceLocation sourceLoc = inputOp.getSourceLocation();
+    private static AbstractUnnestMapOperator createFinalNonIndexOnlySearchPlan(
+            List<Mutable<ILogicalOperator>> afterTopOpRefs, Dataset dataset, ILogicalOperator inputOp,
+            IOptimizationContext context, boolean sortPrimaryKeys, boolean retainInput, boolean retainMissing,
+            boolean requiresBroadcast, boolean requiresDistinct, List<LogicalVariable> primaryKeyVars,
+            List<LogicalVariable> primaryIndexUnnestVars, List<LogicalVariable> auxDistinctVars,
+            List<Object> primaryIndexOutputTypes, IAlgebricksConstantValue leftOuterMissingValue,
+            ARecordType recordType, ARecordType metaRecordType, OptimizableOperatorSubTree subTree,
+            Index secondaryIndex, Mutable<ILogicalOperator> topOpRef,
+            List<Mutable<ILogicalOperator>> assignsBeforeTopOpRef, Mutable<ILogicalExpression> conditionRef,
+            LogicalVariable newMissingPlaceHolderForLOJ) throws AlgebricksException {
 
+        SourceLocation sourceLoc = inputOp.getSourceLocation();
         // Sanity check: requiresDistinct and sortPrimaryKeys are mutually exclusive.
         if (requiresDistinct && sortPrimaryKeys) {
             throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, sourceLoc,
                     "Non-index search plan " + "cannot include a DISTINCT and an ORDER.");
         }
+        ILogicalOperator op = null;
+        IndexType idxType = secondaryIndex.getIndexType();
 
+        // for now the additional selection is only added just for the value index.
+        if (Index.IndexCategory.of(idxType) == Index.IndexCategory.VALUE) {
+            Index.ValueIndexDetails secondaryIndexDetails = (Index.ValueIndexDetails) secondaryIndex.getIndexDetails();
+            List<List<String>> chosenIndexFieldNames = secondaryIndexDetails.getKeyFieldNames();
+            List<IAType> secodaryKeysType = secondaryIndexDetails.getKeyFieldTypes();
+            if (idxType == IndexType.BTREE && secodaryKeysType.contains(BuiltinType.ANY)) {
+                op = additionalSelectForHeterogeneousIndex(chosenIndexFieldNames, afterTopOpRefs, dataset, inputOp,
+                        context, retainMissing, leftOuterMissingValue, recordType, metaRecordType, subTree,
+                        secondaryIndex, topOpRef, assignsBeforeTopOpRef, conditionRef, newMissingPlaceHolderForLOJ);
+            }
+        }
+        if (op == null) {
+            op = inputOp;
+        }
         // If we have an array index, then we must only give unique keys to our primary-index scan.
         DistinctOperator distinct = null;
         if (requiresDistinct) {
@@ -1165,7 +1185,7 @@
             }
             distinct = new DistinctOperator(distinctExprs);
             distinct.setSourceLocation(sourceLoc);
-            distinct.getInputs().add(new MutableObject<>(inputOp));
+            distinct.getInputs().add(new MutableObject<>(op));
             distinct.setExecutionMode(ExecutionMode.LOCAL);
             context.computeAndSetTypeEnvironmentForOperator(distinct);
         }
@@ -1182,7 +1202,7 @@
                 order.getOrderExpressions().add(new Pair<>(OrderOperator.ASC_ORDER, vRef));
             }
             // The secondary-index search feeds into the sort.
-            order.getInputs().add(new MutableObject<>(inputOp));
+            order.getInputs().add(new MutableObject<>(op));
             order.setExecutionMode(ExecutionMode.LOCAL);
             context.computeAndSetTypeEnvironmentForOperator(order);
         }
@@ -1196,7 +1216,7 @@
         } else if (sortPrimaryKeys) {
             primaryIndexUnnestMapOp.getInputs().add(new MutableObject<>(order));
         } else {
-            primaryIndexUnnestMapOp.getInputs().add(new MutableObject<>(inputOp));
+            primaryIndexUnnestMapOp.getInputs().add(new MutableObject<>(op));
         }
         context.computeAndSetTypeEnvironmentForOperator(primaryIndexUnnestMapOp);
         primaryIndexUnnestMapOp.setExecutionMode(ExecutionMode.PARTITIONED);
@@ -1831,9 +1851,12 @@
                 joinPKVars = Collections.emptyList();
             }
 
-            return createFinalNonIndexOnlySearchPlan(dataset, inputOp, context, !isArrayIndex && sortPrimaryKeys,
-                    retainInput, retainMissing, requiresBroadcast, isArrayIndex, pkVarsFromSIdxUnnestMapOp,
-                    primaryIndexUnnestVars, joinPKVars, primaryIndexOutputTypes, leftOuterMissingValue);
+            return createFinalNonIndexOnlySearchPlan(afterTopOpRefs, dataset, inputOp, context,
+                    !isArrayIndex && sortPrimaryKeys, retainInput, retainMissing, requiresBroadcast, isArrayIndex,
+                    pkVarsFromSIdxUnnestMapOp, primaryIndexUnnestVars, joinPKVars, primaryIndexOutputTypes,
+                    leftOuterMissingValue, recordType, metaRecordType, indexSubTree, secondaryIndex, topOpRef,
+                    assignsBeforeTopOpRef, conditionRef, newMissingPlaceHolderForLOJ);
+
         } else if (!isArrayIndex) {
             // Index-only plan case: creates a UNIONALL operator that has two paths after the secondary unnest-map op,
             // and returns it.
@@ -3086,7 +3109,6 @@
                     }
                 }
 
-                // Otherwise... recurse.
                 Pair<List<String>, Integer> parentFieldNames =
                         !isIndexOnFunction
                                 ? getFieldNameAndStepsFromSubTree(optFuncExpr, subTree, assignAndExpressionIndexes[0],
@@ -3337,4 +3359,155 @@
         }
         return isDataSourceVariableFound;
     }
+
+    private static void splitIntoConjuncts(ILogicalExpression condition, List<Mutable<ILogicalExpression>> conjuncts) {
+        if (condition.getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) {
+            return;
+        }
+        AbstractFunctionCallExpression funcExpr = (AbstractFunctionCallExpression) condition;
+        if (funcExpr.getFunctionIdentifier() == BuiltinFunctions.AND) {
+            for (Mutable<ILogicalExpression> arg : funcExpr.getArguments()) {
+                splitIntoConjuncts(arg.getValue(), conjuncts);
+            }
+        } else {
+            conjuncts.add(new MutableObject<>(condition));
+        }
+    }
+
+    protected static ILogicalExpression filterCondition(ILogicalExpression condition,
+            Set<LogicalVariable> listOfAcceptableVAR) {
+        List<Mutable<ILogicalExpression>> conjuncts = new ArrayList<>();
+        List<Mutable<ILogicalExpression>> filteredConjuncts = new ArrayList<>();
+        splitIntoConjuncts(condition, conjuncts);
+        for (Mutable<ILogicalExpression> conjunct : conjuncts) {
+            if (containsOnlyAllowedVariables(conjunct.getValue(), listOfAcceptableVAR)) {
+                filteredConjuncts.add(conjunct);
+            }
+        }
+
+        if (filteredConjuncts.size() == 1) {
+            return filteredConjuncts.get(0).getValue();
+        } else if (!filteredConjuncts.isEmpty()) {
+            ScalarFunctionCallExpression combinedCondition =
+                    new ScalarFunctionCallExpression(BuiltinFunctions.getBuiltinFunctionInfo(BuiltinFunctions.AND));
+            combinedCondition.getArguments().addAll(filteredConjuncts);
+            combinedCondition.setSourceLocation(condition.getSourceLocation());
+            return combinedCondition;
+        } else {
+            return null;
+        }
+    }
+
+    private static boolean containsOnlyAllowedVariables(ILogicalExpression expression,
+            Set<LogicalVariable> allowedVars) {
+
+        Set<LogicalVariable> usedVars = new HashSet<>();
+        expression.getUsedVariables(usedVars);
+        for (LogicalVariable var : usedVars) {
+            if (!allowedVars.contains(var)) {
+                return false;
+            }
+        }
+        return true;
+    }
+
+    private static ILogicalOperator additionalSelectForHeterogeneousIndex(List<List<String>> chosenIndexFieldNames,
+            List<Mutable<ILogicalOperator>> afterTopOpRefs, Dataset dataset, ILogicalOperator inputOp,
+            IOptimizationContext context, boolean retainMissing, IAlgebricksConstantValue leftOuterMissingValue,
+            ARecordType recordType, ARecordType metaRecordType, OptimizableOperatorSubTree subTree,
+            Index secondaryIndex, Mutable<ILogicalOperator> topOpRef,
+            List<Mutable<ILogicalOperator>> assignsBeforeTopOpRef, Mutable<ILogicalExpression> conditionRef,
+            LogicalVariable newMissingPlaceHolderForLOJ) throws AlgebricksException {
+        List<LogicalVariable> skVarsFromSIdxUnnestMap = AccessMethodUtils.getKeyVarsFromSecondaryUnnestMap(dataset,
+                recordType, metaRecordType, inputOp, secondaryIndex, SecondaryUnnestMapOutputVarType.SECONDARY_KEY);
+
+        List<LogicalVariable> usedVarsInConditionOp = new ArrayList<>();
+        // Constructs the variable mapping between newly constructed secondary
+        // key search (SK, PK) and those in the original plan (datasource scan).
+        LinkedHashMap<LogicalVariable, LogicalVariable> origVarToSIdxUnnestMapOpVarMap = new LinkedHashMap<>();
+        VariableUtilities.getUsedVariables(topOpRef.getValue(), usedVarsInConditionOp);
+        // Gets all variables from the right (inner) branch.
+        List<LogicalVariable> liveVarsInInnerBranch = new ArrayList<>();
+        VariableUtilities.getLiveVariables(subTree.getRootRef().getValue(), liveVarsInInnerBranch);
+        for (Iterator<LogicalVariable> iterator = usedVarsInConditionOp.iterator(); iterator.hasNext();) {
+            LogicalVariable v = iterator.next();
+            if (!liveVarsInInnerBranch.contains(v)) {
+                iterator.remove();
+            }
+        }
+        List<LogicalVariable> uniqueUsedVarsInConditionOp = new ArrayList<>();
+        copyVarsToAnotherList(usedVarsInConditionOp, uniqueUsedVarsInConditionOp);
+
+        List<LogicalVariable> producedVarsInAssignsBeforeCondtionOp = new ArrayList<>();
+        List<LogicalVariable> varsTmpList = new ArrayList<>();
+        if (assignsBeforeTopOpRef != null && !assignsBeforeTopOpRef.isEmpty()) {
+            for (int i = 0; i < assignsBeforeTopOpRef.size(); i++) {
+                ILogicalOperator assignBeforeTopOp = assignsBeforeTopOpRef.get(i).getValue();
+                varsTmpList.clear();
+                VariableUtilities.getProducedVariables(assignBeforeTopOp, varsTmpList);
+                copyVarsToAnotherList(varsTmpList, producedVarsInAssignsBeforeCondtionOp);
+            }
+        }
+        List<LogicalVariable> usedVarsAfterSelect = new ArrayList<>();
+        HashSet<LogicalVariable> varsTmpSet = new HashSet<>();
+        if (afterTopOpRefs != null) {
+            for (Mutable<ILogicalOperator> afterTopOpRef : afterTopOpRefs) {
+                varsTmpSet.clear();
+                OperatorPropertiesUtil.getFreeVariablesInOp(afterTopOpRef.getValue(), varsTmpSet);
+                copyVarsToAnotherList(varsTmpSet, usedVarsAfterSelect);
+            }
+        }
+
+        for (LogicalVariable tVar : usedVarsAfterSelect) {
+
+            int sIndexIdx = chosenIndexFieldNames.indexOf(subTree.getVarsToFieldNameMap().get(tVar));
+            if (sIndexIdx == -1) {
+                continue;
+            }
+            // Constructs the mapping between the PK from the original data-scan to the PK
+            // from the secondary index search since they are different logical variables.
+            origVarToSIdxUnnestMapOpVarMap.put(tVar, skVarsFromSIdxUnnestMap.get(sIndexIdx));
+        }
+
+        List<LogicalVariable> varsUsedInTopOpButNotAfterwards = new ArrayList<>();
+        copyVarsToAnotherList(uniqueUsedVarsInConditionOp, varsUsedInTopOpButNotAfterwards);
+        varsUsedInTopOpButNotAfterwards.removeAll(usedVarsAfterSelect);
+        // For B-Tree case: if the given secondary key field variable is used only in the select or
+        // join condition, we were not able to catch the mapping between the SK from the original
+        // data-scan and the SK from the secondary index search since they are different logical variables.
+        // (E.g., we are sending a query on a composite index but returns only one field.)
+        for (LogicalVariable v : varsUsedInTopOpButNotAfterwards) {
+            int sIndexIdx = chosenIndexFieldNames.indexOf(subTree.getVarsToFieldNameMap().get(v));
+            if (sIndexIdx == -1) {
+                continue;
+            }
+            origVarToSIdxUnnestMapOpVarMap.put(v, skVarsFromSIdxUnnestMap.get(sIndexIdx));
+        }
+
+        // The additional select which will be added after the secondary index search
+        // only the conditions which include the variables used before or in secondary index search
+        // any other predicates which includes other variables should not be added here.
+        List<LogicalVariable> usedVarInInput = new ArrayList<>();
+        VariableUtilities.getUsedVariables(inputOp, usedVarInInput);
+        Set<LogicalVariable> skAcceptableVars = new HashSet<>();
+        skAcceptableVars.addAll(origVarToSIdxUnnestMapOpVarMap.keySet());
+        skAcceptableVars.addAll(usedVarInInput);
+        ILogicalExpression conditionRefExpr =
+                filterCondition(conditionRef.getValue().cloneExpression(), skAcceptableVars);
+        if (conditionRefExpr != null) {
+            LogicalVariable newMissingPlaceHolderVar = null;
+            SelectOperator newSelectOp =
+                    retainMissing
+                            ? new SelectOperator(new MutableObject<>(conditionRefExpr), leftOuterMissingValue,
+                                    newMissingPlaceHolderVar)
+                            : new SelectOperator(new MutableObject<>(conditionRefExpr));
+            newSelectOp.setSourceLocation(conditionRefExpr.getSourceLocation());
+            newSelectOp.getInputs().add(new MutableObject(inputOp));
+            VariableUtilities.substituteVariables(newSelectOp, origVarToSIdxUnnestMapOpVarMap, context);
+            newSelectOp.setExecutionMode(ExecutionMode.PARTITIONED);
+            context.computeAndSetTypeEnvironmentForOperator(newSelectOp);
+            return newSelectOp;
+        }
+        return null;
+    }
 }
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/util/ValidateUtil.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/util/ValidateUtil.java
index 34976b6..579ad51 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/util/ValidateUtil.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/util/ValidateUtil.java
@@ -276,6 +276,7 @@
                     case UUID:
                     case YEARMONTHDURATION:
                     case DAYTIMEDURATION:
+                    case ANY:
                         break;
                     default:
                         throw new CompilationException(ErrorCode.COMPILATION_ERROR, sourceLoc,
diff --git a/asterixdb/asterix-app/data/heterogeneousData.adm b/asterixdb/asterix-app/data/heterogeneousData.adm
new file mode 100644
index 0000000..0d96209
--- /dev/null
+++ b/asterixdb/asterix-app/data/heterogeneousData.adm
@@ -0,0 +1,8 @@
+{ "userId": 1, "email": "john.doe@example.com", "username": "johndoe", "isActive": true, "occupation": "teacher","age": 23}
+{ "userId": 2, "email": "jane.smith@example.com", "username": "janesmith", "isActive": false, "occupation": "firefighter" }
+{ "userId": 3, "email": "michael.brown@example.com", "username": "michaelb", "isActive": true, "age": "five", "occupation":4 }
+{ "userId": 4, "email": "emily.davis@example.com", "username": "emilyd", "isActive": true, "age": 23.1 }
+{ "userId": 5, "email": "chris.jones@example.com", "username": "chrisj", "isActive": false, "age": "old" }
+{ "userId": 6, "email": "patricia.garcia@example.com", "username": "patriciag", "isActive": true, "age": "45" }
+{ "userId": 7, "email": "linda.martinez@example.com", "username": "lindam", "isActive": false }
+{ "userId": 8, "email": "robert.lee@example.com", "username": "robertl", "isActive": true, "age": 31 }
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 1d21ce2..ed93838 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
@@ -1374,6 +1374,7 @@
             List<List<IAType>> indexFieldTypes = new ArrayList<>(indexedElementsCount);
             boolean hadUnnest = false;
             boolean overridesFieldTypes = false;
+            boolean isHeterogeneousIndex = false;
 
             // this set is used to detect duplicates in the specified keys in the create
             // index statement
@@ -1447,7 +1448,7 @@
                         projectTypeNullable = inputTypeNullable;
                         projectTypeMissable = inputTypeMissable;
                     } else if (inputTypePrime == null) {
-                        projectTypePrime = null; // ANY
+                        projectTypePrime = null;
                         projectTypeNullable = projectTypeMissable = true;
                     } else {
                         if (inputTypePrime.getTypeTag() != ATypeTag.OBJECT) {
@@ -1528,38 +1529,50 @@
                     }
 
                     if (fieldTypePrime == null) {
-                        if (projectPath != null) {
-                            String fieldName = LogRedactionUtil.userData(RecordUtil.toFullyQualifiedName(projectPath));
-                            throw new CompilationException(ErrorCode.COMPILATION_ERROR,
-                                    indexedElement.getSourceLocation(),
-                                    "cannot find type of field '" + fieldName + "'");
+                        if (indexType != IndexType.BTREE) {
+                            if (projectPath != null) {
+                                String fieldName =
+                                        LogRedactionUtil.userData(RecordUtil.toFullyQualifiedName(projectPath));
+                                throw new CompilationException(ErrorCode.COMPILATION_ERROR,
+                                        indexedElement.getSourceLocation(),
+                                        "cannot find type of field '" + fieldName + "'");
+                            }
+                            // projectPath == null should only be the case with array index having UNNESTs only
+                            if (indexedElement.hasUnnest()) {
+                                List<List<String>> unnestList = indexedElement.getUnnestList();
+                                List<String> arrayField = unnestList.get(unnestList.size() - 1);
+                                String fieldName =
+                                        LogRedactionUtil.userData(RecordUtil.toFullyQualifiedName(arrayField));
+                                throw new CompilationException(ErrorCode.COMPILATION_ERROR,
+                                        indexedElement.getSourceLocation(),
+                                        "cannot find type of elements of field '" + fieldName + "'");
+                            }
+                        } else {
+                            fieldTypePrime = BuiltinType.ANY;
+                            isHeterogeneousIndex = true;
+                            fieldTypeNullable = fieldTypeMissable = false;
                         }
-                        // projectPath == null should only be the case with array index having UNNESTs only
-                        if (indexedElement.hasUnnest()) {
-                            List<List<String>> unnestList = indexedElement.getUnnestList();
-                            List<String> arrayField = unnestList.get(unnestList.size() - 1);
-                            String fieldName = LogRedactionUtil.userData(RecordUtil.toFullyQualifiedName(arrayField));
-                            throw new CompilationException(ErrorCode.COMPILATION_ERROR,
-                                    indexedElement.getSourceLocation(),
-                                    "cannot find type of elements of field '" + fieldName + "'");
-                        }
-                        throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE,
-                                indexedElement.getSourceLocation(), "cannot find type of field");
+                    }
+                    if (ATypeTag.ANY.equals(fieldTypePrime.getTypeTag()) && stmtCreateIndex.hasExcludeUnknownKey()) {
+                        throw new CompilationException(ErrorCode.COMPILATION_ERROR, indexedElement.getSourceLocation(),
+                                "Cannot specify exclude/include unknown for untyped keys in the index definition.");
                     }
                     validateIndexFieldType(indexType, fieldTypePrime, projectPath, indexedElement.getSourceLocation());
 
                     IAType fieldType =
                             KeyFieldTypeUtil.makeUnknownableType(fieldTypePrime, fieldTypeNullable, fieldTypeMissable);
+                    if (isHeterogeneousIndex && !ATypeTag.ANY.equals(fieldType.getTypeTag())) {
+                        throw new CompilationException(ErrorCode.COMPILATION_ERROR, sourceLoc,
+                                "Typed keys cannot be combined with untyped keys in the index definition.");
+                    }
                     fieldTypes.add(fieldType);
                 }
-
                 // Try to add the key & its source to the set of keys for duplicate detection.
                 if (!indexKeysSet.add(indexedElement.toIdentifier())) {
                     throw new AsterixException(ErrorCode.INDEX_ILLEGAL_REPETITIVE_FIELD,
                             indexedElement.getSourceLocation(),
                             LogRedactionUtil.userData(indexedElement.getProjectListDisplayForm()));
                 }
-
                 indexFieldTypes.add(fieldTypes);
             }
 
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.1.ddl.sqlpp
new file mode 100644
index 0000000..040968e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.1.ddl.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * 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.
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create type tenkType as {
+  unique1         : integer,
+  unique2         : integer,
+  two             : integer,
+  four            : integer,
+  ten             : integer,
+  twenty          : integer,
+  hundred         : integer,
+  fivethous       : integer,
+  tenthous        : integer,
+  odd100          : integer,
+  even100         : integer,
+  stringu1        : string,
+  stringu2        : string,
+  string4         : string
+};
+
+create dataset tenk1(tenkType) primary key unique2;
+
+create index idx_tenk1_1k on tenk1(thousand);
+
+create dataset tenk2(tenkType) primary key unique2;
+
+create index idx_tenk2_1k on tenk2(thousand);
+
+create index idx_tenk2_1k_2k on tenk2(thousand, twothousand);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.2.update.sqlpp
new file mode 100644
index 0000000..c6189ce
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.2.update.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+
+Upsert INTO tenk1 (
+    [{ "unique1": 5057, "unique2": 6, "two": 1, "four": 1, "ten": 7, "twenty": 17, "hundred": 57, "thousand": 57, "twothousand": 1057, "fivethous": 57, "tenthous": 5057, "odd100": 114, "even100": 115, "stringu1": "NMAAAA", "stringu2": "GAAAAA", "string4": "OOOOxx" }
+, { "unique1": 1891, "unique2": 1, "two": 1, "four": 3, "ten": 1, "twenty": 11, "hundred": 91, "thousand": 891, "twothousand": 1891, "fivethous": 1891, "tenthous": 1891, "odd100": 182, "even100": 183, "stringu1": "TUAAAA", "stringu2": "BAAAAA", "string4": "HHHHxx" }
+, { "unique1": 3420, "unique2": 2, "two": 0, "four": 0, "ten": 0, "twenty": 0, "hundred": 20, "thousand": 420, "twothousand": 1420, "fivethous": 3420, "tenthous": 3420, "odd100": 40, "even100": 41, "stringu1": "OBAAAA", "stringu2": "CAAAAA", "string4": "OOOOxx" }
+, { "unique1": 7164, "unique2": 4, "two": 0, "four": 0, "ten": 4, "twenty": 4, "hundred": 64, "thousand": 164, "twothousand": 1164, "fivethous": 2164, "tenthous": 7164, "odd100": 128, "even100": 129, "stringu1": "OPAAAA", "stringu2": "EAAAAA", "string4": "AAAAxx" }
+, { "unique1": 8800, "unique2": 0, "two": 0, "four": 0, "ten": 0, "twenty": 0, "hundred": 0, "thousand": 800, "twothousand": 800, "fivethous": 3800, "tenthous": 8800, "odd100": 0, "even100": 1, "stringu1": "MAAAAA", "stringu2": "AAAAAA", "string4": "AAAAxx" }
+, { "unique1": 4321, "unique2": 8, "two": 1, "four": 1, "ten": 1, "twenty": 1, "hundred": 21, "thousand": 321, "twothousand": 321, "fivethous": 4321, "tenthous": 4321, "odd100": 42, "even100": 43, "stringu1": "FKAAAA", "stringu2": "IAAAAA", "string4": "AAAAxx" }
+, { "unique1": 3043, "unique2": 9, "two": 1, "four": 3, "ten": 3, "twenty": 3, "hundred": 43, "thousand": 43, "twothousand": 1043, "fivethous": 3043, "tenthous": 3043, "odd100": 86, "even100": 87, "stringu1": "BNAAAA", "stringu2": "JAAAAA", "string4": "HHHHxx" }
+, { "unique1": 9850, "unique2": 3, "two": 0, "four": 2, "ten": 0, "twenty": 10, "hundred": 50, "thousand": 850, "twothousand": 1850, "fivethous": 4850, "tenthous": 9850, "odd100": 100, "even100": 101, "stringu1": "WOAAAA", "stringu2": "DAAAAA", "string4": "VVVVxx" }
+, { "unique1": 8009, "unique2": 5, "two": 1, "four": 1, "ten": 9, "twenty": 9, "hundred": 9, "thousand": 9, "twothousand": 9, "fivethous": 3009, "tenthous": 8009, "odd100": 18, "even100": 19, "stringu1": "BWAAAA", "stringu2": "FAAAAA", "string4": "HHHHxx" }
+, { "unique1": 6701, "unique2": 7, "two": 1, "four": 1, "ten": 1, "twenty": 1, "hundred": 1, "thousand": 701, "twothousand": 701, "fivethous": 1701, "tenthous": 6701, "odd100": 2, "even100": 3, "stringu1": "TXAAAA", "stringu2": "HAAAAA", "string4": "VVVVxx" }]);
+
+
+
+Upsert INTO tenk2 (
+    [{ "unique1": 5057, "unique2": 6, "two": 1, "four": 1, "ten": 7, "twenty": 17, "hundred": 57, "thousand": 57, "twothousand": 1057, "fivethous": 57, "tenthous": 5057, "odd100": 114, "even100": 115, "stringu1": "NMAAAA", "stringu2": "GAAAAA", "string4": "OOOOxx" }
+, { "unique1": 1891, "unique2": 1, "two": 1, "four": 3, "ten": 1, "twenty": 11, "hundred": 91, "thousand": 891, "twothousand": 1891, "fivethous": 1891, "tenthous": 1891, "odd100": 182, "even100": 183, "stringu1": "TUAAAA", "stringu2": "BAAAAA", "string4": "HHHHxx" }
+, { "unique1": 3420, "unique2": 2, "two": 0, "four": 0, "ten": 0, "twenty": 0, "hundred": 20, "thousand": 420, "twothousand": 1420, "fivethous": 3420, "tenthous": 3420, "odd100": 40, "even100": 41, "stringu1": "OBAAAA", "stringu2": "CAAAAA", "string4": "OOOOxx" }
+, { "unique1": 7164, "unique2": 4, "two": 0, "four": 0, "ten": 4, "twenty": 4, "hundred": 64, "thousand": 164, "twothousand": 1164, "fivethous": 2164, "tenthous": 7164, "odd100": 128, "even100": 129, "stringu1": "OPAAAA", "stringu2": "EAAAAA", "string4": "AAAAxx" }
+, { "unique1": 8800, "unique2": 0, "two": 0, "four": 0, "ten": 0, "twenty": 0, "hundred": 0, "thousand": 800, "twothousand": 800, "fivethous": 3800, "tenthous": 8800, "odd100": 0, "even100": 1, "stringu1": "MAAAAA", "stringu2": "AAAAAA", "string4": "AAAAxx" }
+, { "unique1": 4321, "unique2": 8, "two": 1, "four": 1, "ten": 1, "twenty": 1, "hundred": 21, "thousand": 321, "twothousand": 321, "fivethous": 4321, "tenthous": 4321, "odd100": 42, "even100": 43, "stringu1": "FKAAAA", "stringu2": "IAAAAA", "string4": "AAAAxx" }
+, { "unique1": 3043, "unique2": 9, "two": 1, "four": 3, "ten": 3, "twenty": 3, "hundred": 43, "thousand": 43, "twothousand": 1043, "fivethous": 3043, "tenthous": 3043, "odd100": 86, "even100": 87, "stringu1": "BNAAAA", "stringu2": "JAAAAA", "string4": "HHHHxx" }
+, { "unique1": 9850, "unique2": 3, "two": 0, "four": 2, "ten": 0, "twenty": 10, "hundred": 50, "thousand": 850, "twothousand": 1850, "fivethous": 4850, "tenthous": 9850, "odd100": 100, "even100": 101, "stringu1": "WOAAAA", "stringu2": "DAAAAA", "string4": "VVVVxx" }
+, { "unique1": 8009, "unique2": 5, "two": 1, "four": 1, "ten": 9, "twenty": 9, "hundred": 9, "thousand": 9, "twothousand": 9, "fivethous": 3009, "tenthous": 8009, "odd100": 18, "even100": 19, "stringu1": "BWAAAA", "stringu2": "FAAAAA", "string4": "HHHHxx" }
+, { "unique1": 6701, "unique2": 7, "two": 1, "four": 1, "ten": 1, "twenty": 1, "hundred": 1, "thousand": 701, "twothousand": 701, "fivethous": 1701, "tenthous": 6701, "odd100": 2, "even100": 3, "stringu1": "TXAAAA", "stringu2": "HAAAAA", "string4": "VVVVxx" }]);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.3.query.sqlpp
new file mode 100644
index 0000000..c07004d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.3.query.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.
+ */
+
+use test;
+
+/*
+ * indexnl hint. use specified index (idx_tenk2_1k_2k)
+ */
+
+select value tenk2.unique1
+from tenk1, tenk2
+where
+  tenk1.unique2 < 1
+and
+  tenk1.thousand /* +indexnl(idx_tenk2_1k_2k) */ = tenk2.thousand
+order by tenk2.unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.4.query.sqlpp
new file mode 100644
index 0000000..b5ac9ad
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.4.query.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.
+ */
+
+use test;
+
+select value tenk2.unique1
+from tenk1, tenk2
+where
+  tenk1.unique2 < 2
+and
+  tenk1.thousand /* +indexnl() */ = tenk2.thousand
+order by tenk2.unique1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.1.ddl.sqlpp
new file mode 100644
index 0000000..200dba3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.1.ddl.sqlpp
@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+USE test;
+
+CREATE TYPE untyped AS {id: string};
+CREATE TYPE typed AS {id: string};
+
+CREATE DATASET ds_outer_untyped(untyped) primary key id;
+CREATE DATASET ds_outer_typed(typed) primary key id;
+
+CREATE DATASET ds_inner_untyped(untyped) primary key id;
+CREATE DATASET ds_inner_typed(typed) primary key id;
+
+CREATE INDEX idx_c_int32 ON ds_inner_untyped(c_int32);
+CREATE INDEX idx_c_int32 ON ds_inner_typed(c_int32);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.2.update.sqlpp
new file mode 100644
index 0000000..5b41d9b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.2.update.sqlpp
@@ -0,0 +1,39 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+USE test;
+
+UPSERT INTO ds_outer_untyped [
+{'id': "o_untyped:01", 'c_any':null, 'c_int8':null, 'c_int16':null, 'c_int32':null, 'c_int64':null, 'c_float':null, 'c_double':null}];
+
+UPSERT INTO ds_outer_typed [
+{'id': "o_untyped:01", 'c_any':null, 'c_int8':null, 'c_int16':null, 'c_int32':null, 'c_int64':null, 'c_float':null, 'c_double':null}];
+
+UPSERT INTO ds_inner_untyped [
+{ 'id': "i_untyped:01", 'c_any':null, 'c_int8':null, 'c_int16':null, 'c_int32':null, 'c_int64':null, 'c_float':null, 'c_double':null},
+{ 'id': "i_untyped:02", 'c_any':null, 'c_int8':null, 'c_int16':null, 'c_int32':null, 'c_int64':null, 'c_float':null, 'c_double':null},
+{ 'id': "i_untyped:03", 'c_any':null, 'c_int8':null, 'c_int16':null, 'c_int32':null, 'c_int64':null, 'c_float':null, 'c_double':null}
+];
+
+UPSERT INTO ds_inner_typed [
+{ 'id': "i_typed:01", 'c_any':null, 'c_int8':null, 'c_int16':null, 'c_int32':null, 'c_int64':null, 'c_float':null, 'c_double':null},
+{ 'id': "i_typed:02", 'c_any':null, 'c_int8':null, 'c_int16':null, 'c_int32':null, 'c_int64':null, 'c_float':null, 'c_double':null},
+{ 'id': "i_typed:03", 'c_any':null, 'c_int8':null, 'c_int16':null, 'c_int32':null, 'c_int64':null, 'c_float':null, 'c_double':null}
+];
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.3.query.sqlpp
new file mode 100644
index 0000000..25d7c9d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.3.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `compiler.sort.parallel` 'false';
+SET `compiler.indexonly` 'true';
+SELECT t1.id AS t1_id, t2.id AS t2_id
+FROM ds_outer_untyped t1 LEFT JOIN ds_inner_typed t2 ON int32(t1.c_int32) /* +indexnl */ = (t2.c_int32) ;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.4.query.sqlpp
new file mode 100644
index 0000000..065273c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.4.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+SET `compiler.sort.parallel` 'false';
+SET `compiler.indexonly` 'false';
+SELECT t1.id AS t1_id, t2.id AS t2_id
+FROM ds_outer_untyped t1 LEFT JOIN ds_inner_typed t2 ON int32(t1.c_int32) /* +indexnl */ = (t2.c_int32);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-bulkLoad.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-bulkLoad.1.ddl.sqlpp
new file mode 100644
index 0000000..d425059
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-bulkLoad.1.ddl.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.
+ */
+
+
+
+drop  dataverse test if exists;
+create  dataverse test;
+use test;
+
+CREATE TYPE UserType AS {
+  userId : int,
+  email : string,
+  username:string,
+  isActive : boolean };
+
+create dataset UserTypes(UserType) primary key userId;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-bulkLoad.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-bulkLoad.2.update.sqlpp
new file mode 100644
index 0000000..05a21fa
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-bulkLoad.2.update.sqlpp
@@ -0,0 +1,21 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+use test;
+
+load  dataset UserTypes using localfs ((`path`=`asterix_nc1://data/heterogeneousData.adm`),(`format`=`adm`));
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-bulkLoad.3.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-bulkLoad.3.ddl.sqlpp
new file mode 100644
index 0000000..027d408
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-bulkLoad.3.ddl.sqlpp
@@ -0,0 +1,23 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use test;
+
+create index idx_user_age  on UserTypes(age);
+create index idx_user_age_occupation1  on UserTypes(age, occupation);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-bulkLoad.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-bulkLoad.4.query.sqlpp
new file mode 100644
index 0000000..944054a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-bulkLoad.4.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+select *
+from UserTypes u
+where u.age > 20
+order by userId;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.1.ddl.sqlpp
new file mode 100644
index 0000000..e0e6961
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.1.ddl.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.
+ */
+
+
+drop  dataverse test if exists;
+create  dataverse test;
+use test;
+
+CREATE TYPE UserType AS {
+  userId : int,
+  email : string,
+  username:string,
+  isActive : boolean };
+
+create dataset UserTypes(UserType) primary key userId;
+create dataset UserTypes2(UserType) primary key userId;
+
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.2.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.2.ddl.sqlpp
new file mode 100644
index 0000000..2d84c34
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.2.ddl.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+
+use test;
+
+create index idx_user_age  on UserTypes(age);
+create index idx_user_age_occupation1  on UserTypes(age, occupation);
+create index idx_user_age_occupation2  on UserTypes2(age, occupation);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.3.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.3.update.sqlpp
new file mode 100644
index 0000000..17fb100
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.3.update.sqlpp
@@ -0,0 +1,221 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+Use test;
+
+
+INSERT INTO UserTypes (
+    [
+        {
+            "userId": 1,
+            "email": "john.doe@example.com",
+            "username": "johndoe",
+            "isActive": true,
+            "occupation": "teacher",
+            "age": 23
+        },
+        {
+            "userId": 2,
+            "email": "jane.smith@example.com",
+            "username": "janesmith",
+            "isActive": false,
+            "occupation": "firefighter"
+
+
+        },
+        {
+            "userId": 3,
+            "email": "michael.brown@example.com",
+            "username": "michaelb",
+            "isActive": true,
+            "age": "five",
+            "occupation":4
+
+        },
+        {
+            "userId": 4,
+            "email": "emily.davis@example.com",
+            "username": "emilyd",
+            "isActive": true,
+            "age": 23.1
+
+        },
+        {
+            "userId": 5,
+            "email": "chris.jones@example.com",
+            "username": "chrisj",
+            "isActive": false,
+            "age": "old"
+
+        },
+        {
+            "userId": 6,
+            "email": "patricia.garcia@example.com",
+            "username": "patriciag",
+            "isActive": true,
+            "age": "45"
+
+        },
+        {
+            "userId": 7,
+            "email": "linda.martinez@example.com",
+            "username": "lindam",
+            "isActive": false
+        },
+        {
+            "userId": 8,
+            "email": "robert.lee@example.com",
+            "username": "robertl",
+            "isActive": true,
+            "age": 31
+
+        },
+        {
+            "userId": 9,
+            "email": "james.wilson@example.com",
+            "username": "jamesw",
+            "isActive": true,
+            "age": "65"
+
+        },
+        {
+            "userId": 10,
+            "email": "mary.moore@example.com",
+            "username": "marym",
+            "isActive": false,
+            "age": ""
+
+        },
+        {
+            "userId": 11,
+            "email": "jack.moore@example.com",
+            "username": "jk",
+            "isActive": true,
+            "age": "20",
+            "occupation": "firefighter"
+
+
+        }
+    ]
+);
+
+
+
+
+INSERT INTO UserTypes2 (
+    [
+        {
+            "userId": 1,
+            "email": "john.doe@example.com",
+            "username": "johndoe",
+            "isActive": true,
+            "occupation": "teacher",
+            "age": 23
+        },
+        {
+            "userId": 2,
+            "email": "jane.smith@example.com",
+            "username": "janesmith",
+            "isActive": false,
+            "occupation": "firefighter"
+
+
+        },
+        {
+            "userId": 3,
+            "email": "michael.brown@example.com",
+            "username": "michaelb",
+            "isActive": true,
+            "age": "five",
+            "occupation":4
+
+        },
+        {
+            "userId": 4,
+            "email": "emily.davis@example.com",
+            "username": "emilyd",
+            "isActive": true,
+            "age": 23.1
+
+        },
+        {
+            "userId": 5,
+            "email": "chris.jones@example.com",
+            "username": "chrisj",
+            "isActive": false,
+            "age": "old"
+
+        },
+        {
+            "userId": 6,
+            "email": "patricia.garcia@example.com",
+            "username": "patriciag",
+            "isActive": true,
+            "age": "45"
+
+        },
+        {
+            "userId": 7,
+            "email": "linda.martinez@example.com",
+            "username": "lindam",
+            "isActive": false
+        },
+        {
+            "userId": 8,
+            "email": "robert.lee@example.com",
+            "username": "robertl",
+            "isActive": true,
+            "age": 31
+
+        },
+        {
+            "userId": 9,
+            "email": "james.wilson@example.com",
+            "username": "jamesw",
+            "isActive": true,
+            "age": "65"
+
+        },
+        {
+            "userId": 10,
+            "email": "mary.moore@example.com",
+            "username": "marym",
+            "isActive": false,
+            "age": ""
+
+        },
+        {
+            "userId": 11,
+            "email": "jack.moore@example.com",
+            "username": "jk",
+            "isActive": true,
+            "age": "20",
+            "occupation": "firefighter"
+
+
+        }
+    ]
+);
+
+
+
+
+
+
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.4.query.sqlpp
new file mode 100644
index 0000000..40e8c99
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.4.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+Use test;
+
+select *
+from UserTypes u
+where u.age > 20
+order by userId;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.5.query.sqlpp
new file mode 100644
index 0000000..cf04d96
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.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.
+ */
+
+Use test;
+
+
+
+select *
+from UserTypes u
+where u.age > 20 and u.isActive = true
+order by userId;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.6.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.6.query.sqlpp
new file mode 100644
index 0000000..4d6d0a2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.6.query.sqlpp
@@ -0,0 +1,26 @@
+
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+Use test;
+
+select *
+from UserTypes u
+where u.age = "20" and u.occupation = "firefighter"
+order by userId;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.7.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.7.query.sqlpp
new file mode 100644
index 0000000..45fa384
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-select/heterogeneous-index-select.7.query.sqlpp
@@ -0,0 +1,26 @@
+
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+Use test;
+
+select *
+from UserTypes2 u
+where u.age = "20" and u.occupation = "firefighter"
+order by userId;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-type-and-heterogeneous-key/heterogeneous-index-type-and-heterogeneous-key.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-type-and-heterogeneous-key/heterogeneous-index-type-and-heterogeneous-key.1.ddl.sqlpp
new file mode 100644
index 0000000..9c4994a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-type-and-heterogeneous-key/heterogeneous-index-type-and-heterogeneous-key.1.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.
+ */
+drop  dataverse test if exists;
+create  dataverse test;
+use test;
+
+
+CREATE TYPE t1 AS {
+  id: int,
+  f2: int};
+
+CREATE DATASET ds(t1) primary key id;
+CREATE INDEX i2 on ds(f1,f2);
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-type-and-heterogeneous-key/heterogeneous-index-type-and-heterogeneous-key.2.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-type-and-heterogeneous-key/heterogeneous-index-type-and-heterogeneous-key.2.ddl.sqlpp
new file mode 100644
index 0000000..5f364b1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-selection/heterogeneous-index-type-and-heterogeneous-key/heterogeneous-index-type-and-heterogeneous-key.2.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.
+ */
+drop  dataverse test if exists;
+create  dataverse test;
+use test;
+
+
+CREATE TYPE t1 AS {
+  id: int,
+  f2: int};
+
+CREATE DATASET ds(t1) primary key id;
+CREATE INDEX i2 on ds(f1,f3: int);
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.3.adm
new file mode 100644
index 0000000..0290fae
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.3.adm
@@ -0,0 +1 @@
+8800
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.4.adm
new file mode 100644
index 0000000..adffc32
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-join/secondary-heterogeneous-indexnl-params/secondary-heterogeneous-indexnl-params.4.adm
@@ -0,0 +1,2 @@
+1891
+8800
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.3.adm
new file mode 100644
index 0000000..5237c63
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.3.adm
@@ -0,0 +1 @@
+{ "t1_id": "o_untyped:01" }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.4.adm
new file mode 100644
index 0000000..5237c63
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-Leftouterjoin/heterogeneous-index-Leftouterjoin.4.adm
@@ -0,0 +1 @@
+{ "t1_id": "o_untyped:01" }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-select.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-select.4.adm
new file mode 100644
index 0000000..cf8c3b7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-bulkLoad/heterogeneous-index-select.4.adm
@@ -0,0 +1,3 @@
+{ "u": { "userId": 1, "email": "john.doe@example.com", "username": "johndoe", "isActive": true, "occupation": "teacher", "age": 23 } }
+{ "u": { "userId": 4, "email": "emily.davis@example.com", "username": "emilyd", "isActive": true, "age": 23.1 } }
+{ "u": { "userId": 8, "email": "robert.lee@example.com", "username": "robertl", "isActive": true, "age": 31 } }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-select/heterogeneous-index-select.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-select/heterogeneous-index-select.4.adm
new file mode 100644
index 0000000..cf8c3b7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-select/heterogeneous-index-select.4.adm
@@ -0,0 +1,3 @@
+{ "u": { "userId": 1, "email": "john.doe@example.com", "username": "johndoe", "isActive": true, "occupation": "teacher", "age": 23 } }
+{ "u": { "userId": 4, "email": "emily.davis@example.com", "username": "emilyd", "isActive": true, "age": 23.1 } }
+{ "u": { "userId": 8, "email": "robert.lee@example.com", "username": "robertl", "isActive": true, "age": 31 } }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-select/heterogeneous-index-select.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-select/heterogeneous-index-select.5.adm
new file mode 100644
index 0000000..049b69e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-select/heterogeneous-index-select.5.adm
@@ -0,0 +1,3 @@
+{ "u": { "userId": 1, "email": "john.doe@example.com", "username": "johndoe", "isActive": true, "occupation": "teacher", "age": 23 } }
+{ "u": { "userId": 4, "email": "emily.davis@example.com", "username": "emilyd", "isActive": true, "age": 23.1 } }
+{ "u": { "userId": 8, "email": "robert.lee@example.com", "username": "robertl", "isActive": true, "age": 31 } }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-select/heterogeneous-index-select.6.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-select/heterogeneous-index-select.6.adm
new file mode 100644
index 0000000..5f0696b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-select/heterogeneous-index-select.6.adm
@@ -0,0 +1 @@
+{ "u": { "userId": 11, "email": "jack.moore@example.com", "username": "jk", "isActive": true, "age": "20", "occupation": "firefighter" } }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-select/heterogeneous-index-select.7.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-select/heterogeneous-index-select.7.adm
new file mode 100644
index 0000000..5f0696b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-selection/heterogeneous-index-select/heterogeneous-index-select.7.adm
@@ -0,0 +1 @@
+{ "u": { "userId": 11, "email": "jack.moore@example.com", "username": "jk", "isActive": true, "age": "20", "occupation": "firefighter" } }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
index 71ba960..bbeb517 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
@@ -6366,6 +6366,11 @@
         <expected-warn><![CDATA[ASX1132: Invalid specification for hint indexnl. ASX1001: Syntax error: In line 1 >>(8, idx_tenk2_1k_2k)<< Encountered <INTEGER_LITERAL> "8" at column 2.  (in line 35, at column 21)]]></expected-warn>
       </compilation-unit>
     </test-case>
+    <test-case FilePath="index-join" check-warnings="true">
+      <compilation-unit name="secondary-heterogeneous-indexnl-params">
+        <output-dir compare="Text">secondary-heterogeneous-indexnl-params</output-dir>
+      </compilation-unit>
+    </test-case>
     <test-case FilePath="index-join">
       <compilation-unit name="rtree-spatial-intersect-point_01">
         <output-dir compare="Text">rtree-spatial-intersect-point_01</output-dir>
@@ -6691,6 +6696,28 @@
         <output-dir compare="Text">verify</output-dir>
       </compilation-unit>
     </test-case>
+    <test-case FilePath="index-selection">
+      <compilation-unit name="heterogeneous-index-select">
+        <output-dir compare="Text">heterogeneous-index-select</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="index-selection">
+      <compilation-unit name="heterogeneous-index-type-and-heterogeneous-key">
+        <output-dir compare="Text">none</output-dir>
+        <expected-error>ASX1079: Compilation error: Typed keys cannot be combined with untyped keys in the index definition. (in line 29, at column 1)</expected-error>
+        <expected-error>ASX1079: Compilation error: Typed keys cannot be combined with untyped keys in the index definition. (in line 29, at column 1)</expected-error>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="index-selection">
+      <compilation-unit name="heterogeneous-index-bulkLoad">
+        <output-dir compare="Text">heterogeneous-index-bulkLoad</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="index-selection">
+      <compilation-unit name="heterogeneous-index-Leftouterjoin">
+        <output-dir compare="Text">heterogeneous-index-Leftouterjoin</output-dir>
+      </compilation-unit>
+    </test-case>
   </test-group>
   <test-group name="inverted-index-join">
     <test-case FilePath="inverted-index-join">
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java
index deca260..e726db7 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/entitytupletranslators/IndexTupleTranslator.java
@@ -385,11 +385,18 @@
                         Triple<IAType, Boolean, Boolean> projectTypeResult =
                                 KeyFieldTypeUtil.getKeyProjectType((ARecordType) inputTypePrime, projectPath, null);
                         if (projectTypeResult == null) {
-                            throw new AsterixException(ErrorCode.METADATA_ERROR, projectPath.toString());
+                            if (indexType != IndexType.BTREE) {
+                                throw new AsterixException(ErrorCode.METADATA_ERROR, projectPath.toString());
+                            }
+                            projectTypePrime = BuiltinType.ANY;
+                            // We do not want the type to be union of Any, null and missing. Any will cover it all.
+                            projectTypeNullable = false;
+                            projectTypeMissable = false;
+                        } else {
+                            projectTypePrime = projectTypeResult.first;
+                            projectTypeNullable = inputTypeNullable || projectTypeResult.second;
+                            projectTypeMissable = inputTypeMissable || projectTypeResult.third;
                         }
-                        projectTypePrime = projectTypeResult.first;
-                        projectTypeNullable = inputTypeNullable || projectTypeResult.second;
-                        projectTypeMissable = inputTypeMissable || projectTypeResult.third;
                     }
                     IAType projectType = projectTypePrime == null ? null
                             : KeyFieldTypeUtil.makeUnknownableType(projectTypePrime, projectTypeNullable,
diff --git a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryIndexOperationsHelper.java b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryIndexOperationsHelper.java
index 276f22b..cf4f8e0 100644
--- a/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryIndexOperationsHelper.java
+++ b/asterixdb/asterix-metadata/src/main/java/org/apache/asterix/metadata/utils/SecondaryIndexOperationsHelper.java
@@ -49,6 +49,7 @@
 import org.apache.asterix.om.functions.IFunctionManager;
 import org.apache.asterix.om.typecomputer.impl.TypeComputeUtils;
 import org.apache.asterix.om.types.ARecordType;
+import org.apache.asterix.om.types.ATypeTag;
 import org.apache.asterix.om.types.BuiltinType;
 import org.apache.asterix.om.types.IAType;
 import org.apache.asterix.runtime.evaluators.functions.AndDescriptor;
@@ -328,6 +329,10 @@
             throws AlgebricksException {
         IFunctionManager funManger = metadataProvider.getFunctionManager();
         IDataFormat dataFormat = metadataProvider.getDataFormat();
+        //if the target type is "BuiltinType.ANY" there is no need to cast. If not we have to cast.
+        if (ATypeTag.ANY.equals(targetType.getTypeTag())) {
+            return fieldEvalFactory;
+        }
 
         // check IndexUtil.castDefaultNull(index), too, because we always want to cast even if the overriding type is
         // the same as the overridden type (this is for the case where overriding the type of closed field is allowed)
diff --git a/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/types/hierachy/ATypeHierarchy.java b/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/types/hierachy/ATypeHierarchy.java
index 82de905..c94a5ae 100644
--- a/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/types/hierachy/ATypeHierarchy.java
+++ b/asterixdb/asterix-om/src/main/java/org/apache/asterix/om/types/hierachy/ATypeHierarchy.java
@@ -177,6 +177,13 @@
         return null;
     }
 
+    /*
+    * Checks whether a type can be promoted (or converted) from one type (type1)
+    * to another (type2). This promotion typically follows certain rules defined
+    * within the class to allow safe type conversions,
+    * such as promoting a smaller numeric type (e.g., TINYINT) to a larger numeric type
+    * (e.g., INTEGER or DOUBLE), where no precision or range is lost.
+    */
     public static boolean canPromote(ATypeTag type1, ATypeTag type2) {
         return typePromotionHierachyMap.get(type1.ordinal() * ATypeTag.TYPE_COUNT + type2.ordinal());
     }