This is an automated email from the ASF dual-hosted git repository.
mblow pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/asterixdb.git
The following commit(s) were added to refs/heads/master by this push:
new 993f91c750 [ASTERIXDB-3536][IDX] Add support for heterogeneous index
993f91c750 is described below
commit 993f91c7504451c844b9d6d16777bbb5add71952
Author: Shahrzad shirazi <[email protected]>
AuthorDate: Mon Aug 12 16:31:36 2024 -0700
[ASTERIXDB-3536][IDX] Add support for heterogeneous index
- user model changes: no
- storage format changes: no
- interface changes: no
details:
Introducing the heterogeneous index which allows creating an
index on a field without specifying its type. This means the
field’s type no longer needs to be defined in the data type
or the index creation DDL.
Ext-ref: MB-27845
Change-Id: I03b5edfbc304cdb9f36e2b382050781811a14759
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/18522
Reviewed-by: Ali Alsuliman <[email protected]>
Tested-by: Ali Alsuliman <[email protected]>
---
.../IntroduceSecondaryIndexInsertDeleteRule.java | 5 +-
.../am/AbstractIntroduceAccessMethodRule.java | 4 +-
.../optimizer/rules/am/AccessMethodUtils.java | 201 +++++++++++++++++--
.../asterix/translator/util/ValidateUtil.java | 1 +
asterixdb/asterix-app/data/heterogeneousData.adm | 8 +
.../asterix/app/translator/QueryTranslator.java | 51 +++--
...ondary-heterogeneous-indexnl-params.1.ddl.sqlpp | 50 +++++
...ary-heterogeneous-indexnl-params.2.update.sqlpp | 47 +++++
...dary-heterogeneous-indexnl-params.3.query.sqlpp | 32 +++
...dary-heterogeneous-indexnl-params.4.query.sqlpp | 28 +++
.../heterogeneous-index-Leftouterjoin.1.ddl.sqlpp | 35 ++++
...eterogeneous-index-Leftouterjoin.2.update.sqlpp | 39 ++++
...heterogeneous-index-Leftouterjoin.3.query.sqlpp | 25 +++
...heterogeneous-index-Leftouterjoin.4.query.sqlpp | 25 +++
.../heterogeneous-index-bulkLoad.1.ddl.sqlpp | 32 +++
.../heterogeneous-index-bulkLoad.2.update.sqlpp | 21 ++
.../heterogeneous-index-bulkLoad.3.ddl.sqlpp | 23 +++
.../heterogeneous-index-bulkLoad.4.query.sqlpp | 25 +++
.../heterogeneous-index-select.1.ddl.sqlpp | 34 ++++
.../heterogeneous-index-select.2.ddl.sqlpp | 25 +++
.../heterogeneous-index-select.3.update.sqlpp | 221 +++++++++++++++++++++
.../heterogeneous-index-select.4.query.sqlpp | 25 +++
.../heterogeneous-index-select.5.query.sqlpp | 27 +++
.../heterogeneous-index-select.6.query.sqlpp | 26 +++
.../heterogeneous-index-select.7.query.sqlpp | 26 +++
...us-index-type-and-heterogeneous-key.1.ddl.sqlpp | 30 +++
...us-index-type-and-heterogeneous-key.2.ddl.sqlpp | 30 +++
.../secondary-heterogeneous-indexnl-params.3.adm | 1 +
.../secondary-heterogeneous-indexnl-params.4.adm | 2 +
.../heterogeneous-index-Leftouterjoin.3.adm | 1 +
.../heterogeneous-index-Leftouterjoin.4.adm | 1 +
.../heterogeneous-index-select.4.adm | 3 +
.../heterogeneous-index-select.4.adm | 3 +
.../heterogeneous-index-select.5.adm | 3 +
.../heterogeneous-index-select.6.adm | 1 +
.../heterogeneous-index-select.7.adm | 1 +
.../src/test/resources/runtimets/sqlpp_queries.xml | 27 +++
.../IndexTupleTranslator.java | 15 +-
.../utils/SecondaryIndexOperationsHelper.java | 5 +
.../asterix/om/types/hierachy/ATypeHierarchy.java | 7 +
40 files changed, 1127 insertions(+), 39 deletions(-)
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 dd8f6302d2..df8d4cd7f4 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 @@ public class IntroduceSecondaryIndexInsertDeleteRule
implements IAlgebraicRewrit
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 @@ public class IntroduceSecondaryIndexInsertDeleteRule
implements IAlgebraicRewrit
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 b5041f0cdc..24ef2d7a55 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 @@ public abstract class AbstractIntroduceAccessMethodRule
implements IAlgebraicRew
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 1ff90c61a3..9b30dfb89f 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 @@ public class AccessMethodUtils {
}
}
- 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 @@ public class AccessMethodUtils {
}
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 @@ public class AccessMethodUtils {
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 @@ public class AccessMethodUtils {
} 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 @@ public class AccessMethodUtils {
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 @@ public class AccessMethodUtils {
}
}
- // Otherwise... recurse.
Pair<List<String>, Integer> parentFieldNames =
!isIndexOnFunction
? getFieldNameAndStepsFromSubTree(optFuncExpr,
subTree, assignAndExpressionIndexes[0],
@@ -3337,4 +3359,155 @@ public class AccessMethodUtils {
}
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 34976b6b47..579ad51bb7 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 @@ public class ValidateUtil {
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 0000000000..0d962094b4
--- /dev/null
+++ b/asterixdb/asterix-app/data/heterogeneousData.adm
@@ -0,0 +1,8 @@
+{ "userId": 1, "email": "[email protected]", "username": "johndoe",
"isActive": true, "occupation": "teacher","age": 23}
+{ "userId": 2, "email": "[email protected]", "username": "janesmith",
"isActive": false, "occupation": "firefighter" }
+{ "userId": 3, "email": "[email protected]", "username": "michaelb",
"isActive": true, "age": "five", "occupation":4 }
+{ "userId": 4, "email": "[email protected]", "username": "emilyd",
"isActive": true, "age": 23.1 }
+{ "userId": 5, "email": "[email protected]", "username": "chrisj",
"isActive": false, "age": "old" }
+{ "userId": 6, "email": "[email protected]", "username":
"patriciag", "isActive": true, "age": "45" }
+{ "userId": 7, "email": "[email protected]", "username": "lindam",
"isActive": false }
+{ "userId": 8, "email": "[email protected]", "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 1d21ce24ed..ed93838e37 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 @@ public class QueryTranslator extends
AbstractLangTranslator implements IStatemen
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 @@ public class QueryTranslator extends
AbstractLangTranslator implements IStatemen
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 @@ public class QueryTranslator extends
AbstractLangTranslator implements IStatemen
}
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
+ "'");
- }
- // 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 + "'");
+ 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;
}
- 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 0000000000..040968e87d
--- /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 0000000000..c6189ceca8
--- /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 0000000000..c07004d68d
--- /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 0000000000..b5ac9ad6c2
--- /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 0000000000..200dba3484
--- /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 0000000000..5b41d9b1af
--- /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 0000000000..25d7c9d10e
--- /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 0000000000..065273cadd
--- /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 0000000000..d425059463
--- /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 0000000000..05a21fa54f
--- /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 0000000000..027d4087fb
--- /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 0000000000..944054adaa
--- /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 0000000000..e0e6961a3b
--- /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 0000000000..2d84c3494d
--- /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 0000000000..17fb100a41
--- /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": "[email protected]",
+ "username": "johndoe",
+ "isActive": true,
+ "occupation": "teacher",
+ "age": 23
+ },
+ {
+ "userId": 2,
+ "email": "[email protected]",
+ "username": "janesmith",
+ "isActive": false,
+ "occupation": "firefighter"
+
+
+ },
+ {
+ "userId": 3,
+ "email": "[email protected]",
+ "username": "michaelb",
+ "isActive": true,
+ "age": "five",
+ "occupation":4
+
+ },
+ {
+ "userId": 4,
+ "email": "[email protected]",
+ "username": "emilyd",
+ "isActive": true,
+ "age": 23.1
+
+ },
+ {
+ "userId": 5,
+ "email": "[email protected]",
+ "username": "chrisj",
+ "isActive": false,
+ "age": "old"
+
+ },
+ {
+ "userId": 6,
+ "email": "[email protected]",
+ "username": "patriciag",
+ "isActive": true,
+ "age": "45"
+
+ },
+ {
+ "userId": 7,
+ "email": "[email protected]",
+ "username": "lindam",
+ "isActive": false
+ },
+ {
+ "userId": 8,
+ "email": "[email protected]",
+ "username": "robertl",
+ "isActive": true,
+ "age": 31
+
+ },
+ {
+ "userId": 9,
+ "email": "[email protected]",
+ "username": "jamesw",
+ "isActive": true,
+ "age": "65"
+
+ },
+ {
+ "userId": 10,
+ "email": "[email protected]",
+ "username": "marym",
+ "isActive": false,
+ "age": ""
+
+ },
+ {
+ "userId": 11,
+ "email": "[email protected]",
+ "username": "jk",
+ "isActive": true,
+ "age": "20",
+ "occupation": "firefighter"
+
+
+ }
+ ]
+);
+
+
+
+
+INSERT INTO UserTypes2 (
+ [
+ {
+ "userId": 1,
+ "email": "[email protected]",
+ "username": "johndoe",
+ "isActive": true,
+ "occupation": "teacher",
+ "age": 23
+ },
+ {
+ "userId": 2,
+ "email": "[email protected]",
+ "username": "janesmith",
+ "isActive": false,
+ "occupation": "firefighter"
+
+
+ },
+ {
+ "userId": 3,
+ "email": "[email protected]",
+ "username": "michaelb",
+ "isActive": true,
+ "age": "five",
+ "occupation":4
+
+ },
+ {
+ "userId": 4,
+ "email": "[email protected]",
+ "username": "emilyd",
+ "isActive": true,
+ "age": 23.1
+
+ },
+ {
+ "userId": 5,
+ "email": "[email protected]",
+ "username": "chrisj",
+ "isActive": false,
+ "age": "old"
+
+ },
+ {
+ "userId": 6,
+ "email": "[email protected]",
+ "username": "patriciag",
+ "isActive": true,
+ "age": "45"
+
+ },
+ {
+ "userId": 7,
+ "email": "[email protected]",
+ "username": "lindam",
+ "isActive": false
+ },
+ {
+ "userId": 8,
+ "email": "[email protected]",
+ "username": "robertl",
+ "isActive": true,
+ "age": 31
+
+ },
+ {
+ "userId": 9,
+ "email": "[email protected]",
+ "username": "jamesw",
+ "isActive": true,
+ "age": "65"
+
+ },
+ {
+ "userId": 10,
+ "email": "[email protected]",
+ "username": "marym",
+ "isActive": false,
+ "age": ""
+
+ },
+ {
+ "userId": 11,
+ "email": "[email protected]",
+ "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 0000000000..40e8c99baf
--- /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 0000000000..cf04d96453
--- /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 0000000000..4d6d0a2b8a
--- /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 0000000000..45fa3843e6
--- /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 0000000000..9c4994a0f7
--- /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 0000000000..5f364b1af9
--- /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 0000000000..0290fae218
--- /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 0000000000..adffc32cfa
--- /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 0000000000..5237c63607
--- /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 0000000000..5237c63607
--- /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 0000000000..cf8c3b7918
--- /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": "[email protected]", "username": "johndoe",
"isActive": true, "occupation": "teacher", "age": 23 } }
+{ "u": { "userId": 4, "email": "[email protected]", "username":
"emilyd", "isActive": true, "age": 23.1 } }
+{ "u": { "userId": 8, "email": "[email protected]", "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 0000000000..cf8c3b7918
--- /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": "[email protected]", "username": "johndoe",
"isActive": true, "occupation": "teacher", "age": 23 } }
+{ "u": { "userId": 4, "email": "[email protected]", "username":
"emilyd", "isActive": true, "age": 23.1 } }
+{ "u": { "userId": 8, "email": "[email protected]", "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 0000000000..049b69e269
--- /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": "[email protected]", "username": "johndoe",
"isActive": true, "occupation": "teacher", "age": 23 } }
+{ "u": { "userId": 4, "email": "[email protected]", "username":
"emilyd", "isActive": true, "age": 23.1 } }
+{ "u": { "userId": 8, "email": "[email protected]", "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 0000000000..5f0696bb7b
--- /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": "[email protected]", "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 0000000000..5f0696bb7b
--- /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": "[email protected]", "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 71ba960f45..bbeb517d0d 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 deca260d5d..e726db7b92 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 @@ public class IndexTupleTranslator extends
AbstractTupleTranslator<Index> {
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 276f22beb8..cf4f8e0067 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.IFunctionDescriptor;
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 @@ public abstract class SecondaryIndexOperationsHelper
implements ISecondaryIndexO
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 82de9055ef..c94a5ae95c 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 @@ public class ATypeHierarchy {
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());
}
