You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by mb...@apache.org on 2023/01/30 03:26:59 UTC
[asterixdb] 10/30: [ASTERIXDB-3043][COMP] Fix handling of multiple datasources in join branch
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
commit 1f8236ca93bde4d80559359b14887feae90a1db5
Author: Ali Alsuliman <al...@gmail.com>
AuthorDate: Fri Jun 24 22:26:10 2022 +0300
[ASTERIXDB-3043][COMP] Fix handling of multiple datasources in join branch
- user model changes: no
- storage format changes: no
- interface changes: no
Details:
When analyzing and collecting information from a join branch,
make sure the correct datasource is used instead of using
the first datasource encountered in the branch path.
- Collect and map the datasources variables to their types/source indicators.
- Remove unused isUnnestOverVarAllowed since it's always passed false.
- In case the field name is nested (i.e. there are nested assigns that eventually lead to the datasource), use the optimization context to compute the intermediate nested assign variable.
Change-Id: I1ce48e0bb1198855b6d6d5efad49a19861a3ef98
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/16686
Integration-Tests: Jenkins <je...@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <je...@fulliautomatix.ics.uci.edu>
Reviewed-by: Ali Alsuliman <al...@gmail.com>
Reviewed-by: Glenn Galvizo <gg...@uci.edu>
Reviewed-by: Wail Alkowaileet <wa...@gmail.com>
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/17349
Reviewed-by: Michael Blow <mb...@apache.org>
Tested-by: Michael Blow <mb...@apache.org>
---
.../am/AbstractIntroduceAccessMethodRule.java | 59 +++----
.../optimizer/rules/am/AccessMethodUtils.java | 109 +++++-------
.../rules/am/IntroduceJoinAccessMethodRule.java | 4 +-
.../rules/am/IntroduceSelectAccessMethodRule.java | 2 +-
.../rules/am/OptimizableOperatorSubTree.java | 70 ++++----
.../queries/btree-ternary-inlj/query4.sqlpp | 194 +++++++++++++++++++++
.../results/btree-ternary-inlj/query4.plan | 32 ++++
.../queries_sqlpp/tpcds/q18/q18.4.query.sqlpp | 30 ++++
.../queries_sqlpp/tpcds/q18/q18.5.query.sqlpp | 30 ++++
.../runtimets/results/tpcds/q18/q18.2.adm | 1 +
.../runtimets/results/tpcds/q18/q18.3.adm | 1 +
11 files changed, 398 insertions(+), 134 deletions(-)
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 7f1ff4adfc..5be9d66928 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
@@ -821,7 +821,7 @@ public abstract class AbstractIntroduceAccessMethodRule implements IAlgebraicRew
// Remember matching subtree.
optFuncExpr.setOptimizableSubTree(funcVarIndex, subTree);
List<String> fieldName = null;
- MutableInt fieldSource = new MutableInt(0);
+ int fieldSource = 0;
if (subTree.getDataSourceType() == DataSourceType.COLLECTION_SCAN) {
ILogicalExpression expr = optFuncExpr.getArgument(funcVarIndex).getValue();
if (expr.getExpressionTag() == LogicalExpressionTag.FUNCTION_CALL) {
@@ -832,9 +832,11 @@ public abstract class AbstractIntroduceAccessMethodRule implements IAlgebraicRew
if (subTree.getDataSourceType() == DataSourceType.DATASOURCE_SCAN) {
subTree.setLastMatchedDataSourceVars(0, funcVarIndex);
}
- fieldName = AccessMethodUtils.getFieldNameSetStepsFromSubTree(optFuncExpr, subTree, assignOrUnnestIndex, 0,
- subTree.getRecordType(), funcVarIndex, optFuncExpr.getArgument(funcVarIndex).getValue(),
- subTree.getMetaRecordType(), datasetMetaVar, fieldSource, false);
+ Pair<List<String>, Integer> fieldNameAndSource =
+ AccessMethodUtils.getFieldNameSetStepsFromSubTree(optFuncExpr, subTree, assignOrUnnestIndex, 0,
+ funcVarIndex, optFuncExpr.getArgument(funcVarIndex).getValue(), context);
+ fieldName = fieldNameAndSource.first;
+ fieldSource = fieldNameAndSource.second;
if (fieldName.isEmpty()) {
return;
}
@@ -843,13 +845,13 @@ public abstract class AbstractIntroduceAccessMethodRule implements IAlgebraicRew
(IAType) context.getOutputTypeEnvironment(unnestOp).getType(optFuncExpr.getLogicalExpr(funcVarIndex));
// Set the fieldName in the corresponding matched function
// expression.
- optFuncExpr.setFieldName(funcVarIndex, fieldName, fieldSource.intValue());
+ optFuncExpr.setFieldName(funcVarIndex, fieldName, fieldSource);
optFuncExpr.setFieldType(funcVarIndex, fieldType);
setTypeTag(context, subTree, optFuncExpr, funcVarIndex);
if (subTree.hasDataSource()) {
fillIndexExprs(datasetIndexes, fieldName, fieldType, optFuncExpr, optFuncExprIndex, funcVarIndex, subTree,
- analysisCtx, fieldSource.intValue(), accessMethod);
+ analysisCtx, fieldSource, accessMethod);
}
}
@@ -860,7 +862,6 @@ public abstract class AbstractIntroduceAccessMethodRule implements IAlgebraicRew
boolean doesArrayIndexQualify = context.getPhysicalOptimizationConfig().isArrayIndexEnabled()
&& datasetIndexes.stream().anyMatch(i -> i.getIndexType() == IndexType.ARRAY);
List<LogicalVariable> varList = assignOp.getVariables();
- MutableInt fieldSource = new MutableInt(0);
for (int varIndex = 0; varIndex < varList.size(); varIndex++) {
LogicalVariable var = varList.get(varIndex);
int optVarIndex = optFuncExpr.findLogicalVar(var);
@@ -871,8 +872,7 @@ public abstract class AbstractIntroduceAccessMethodRule implements IAlgebraicRew
.analyzeVarForArrayIndexes(datasetIndexes, optFuncExpr, subTree, context, var, analysisCtx);
if (fieldTriplet != null && subTree.hasDataSource()) {
fillIndexExprs(datasetIndexes, fieldTriplet.second, fieldTriplet.third, optFuncExpr,
- optFuncExprIndex, fieldTriplet.first, subTree, analysisCtx, fieldSource.intValue(),
- accessMethod);
+ optFuncExprIndex, fieldTriplet.first, subTree, analysisCtx, 0, accessMethod);
}
}
continue;
@@ -885,22 +885,22 @@ public abstract class AbstractIntroduceAccessMethodRule implements IAlgebraicRew
subTree.setLastMatchedDataSourceVars(varIndex, optVarIndex);
}
- fieldSource.setValue(0);
- List<String> fieldName = AccessMethodUtils.getFieldNameSetStepsFromSubTree(optFuncExpr, subTree,
- assignOrUnnestIndex, varIndex, subTree.getRecordType(), optVarIndex,
- optFuncExpr.getArgument(optVarIndex).getValue(), subTree.getMetaRecordType(), datasetMetaVar,
- fieldSource, false);
+ Pair<List<String>, Integer> fieldNameAndSource =
+ AccessMethodUtils.getFieldNameSetStepsFromSubTree(optFuncExpr, subTree, assignOrUnnestIndex,
+ varIndex, optVarIndex, optFuncExpr.getArgument(optVarIndex).getValue(), context);
+ List<String> fieldName = fieldNameAndSource.first;
+ int fieldSource = fieldNameAndSource.second;
IAType fieldType = (IAType) context.getOutputTypeEnvironment(assignOp).getVarType(var);
// Set the fieldName in the corresponding matched
// function expression.
- optFuncExpr.setFieldName(optVarIndex, fieldName, fieldSource.intValue());
+ optFuncExpr.setFieldName(optVarIndex, fieldName, fieldSource);
optFuncExpr.setFieldType(optVarIndex, fieldType);
setTypeTag(context, subTree, optFuncExpr, optVarIndex);
if (subTree.hasDataSource()) {
fillIndexExprs(datasetIndexes, fieldName, fieldType, optFuncExpr, optFuncExprIndex, optVarIndex,
- subTree, analysisCtx, fieldSource.intValue(), accessMethod);
+ subTree, analysisCtx, fieldSource, accessMethod);
}
}
}
@@ -989,16 +989,8 @@ public abstract class AbstractIntroduceAccessMethodRule implements IAlgebraicRew
/**
* Finds the field name of each variable in the ASSIGN or UNNEST operators of the sub-tree.
*/
- protected void fillFieldNamesInTheSubTree(OptimizableOperatorSubTree subTree) throws AlgebricksException {
- LogicalVariable datasetMetaVar = null;
- if (subTree.getDataSourceType() != DataSourceType.COLLECTION_SCAN
- && subTree.getDataSourceType() != DataSourceType.INDEXONLY_PLAN_SECONDARY_INDEX_LOOKUP) {
- List<LogicalVariable> datasetVars = subTree.getDataSourceVariables();
- if (subTree.getDataset().hasMetaPart()) {
- datasetMetaVar = datasetVars.get(datasetVars.size() - 1);
- }
- }
- MutableInt fieldSource = new MutableInt(0);
+ protected void fillFieldNamesInTheSubTree(OptimizableOperatorSubTree subTree, IOptimizationContext context)
+ throws AlgebricksException {
for (int assignOrUnnestIndex = 0; assignOrUnnestIndex < subTree.getAssignsAndUnnests()
.size(); assignOrUnnestIndex++) {
AbstractLogicalOperator op = subTree.getAssignsAndUnnests().get(assignOrUnnestIndex);
@@ -1009,10 +1001,8 @@ public abstract class AbstractIntroduceAccessMethodRule implements IAlgebraicRew
LogicalVariable var = varList.get(varIndex);
// funcVarIndex is not required. Thus, we set it to -1.
// optFuncExpr and parentFuncExpr are not required, too. Thus, we set them to null.
- fieldSource.setValue(0);
List<String> fieldName = AccessMethodUtils.getFieldNameSetStepsFromSubTree(null, subTree,
- assignOrUnnestIndex, varIndex, subTree.getRecordType(), -1, null,
- subTree.getMetaRecordType(), datasetMetaVar, fieldSource, false);
+ assignOrUnnestIndex, varIndex, -1, null, context).first;
if (fieldName != null && !fieldName.isEmpty()) {
subTree.getVarsToFieldNameMap().put(var, fieldName);
}
@@ -1020,14 +1010,11 @@ public abstract class AbstractIntroduceAccessMethodRule implements IAlgebraicRew
} else if (op.getOperatorTag() == LogicalOperatorTag.UNNEST) {
UnnestOperator unnestOp = (UnnestOperator) op;
LogicalVariable var = unnestOp.getVariable();
- List<String> fieldName = null;
if (subTree.getDataSourceType() != DataSourceType.COLLECTION_SCAN) {
// funcVarIndex is not required. Thus, we set it to -1.
// optFuncExpr and parentFuncExpr are not required, too. Thus, we set them to null.
- fieldSource.setValue(0);
- fieldName = AccessMethodUtils.getFieldNameSetStepsFromSubTree(null, subTree, assignOrUnnestIndex, 0,
- subTree.getRecordType(), -1, null, subTree.getMetaRecordType(), datasetMetaVar, fieldSource,
- false);
+ List<String> fieldName = AccessMethodUtils.getFieldNameSetStepsFromSubTree(null, subTree,
+ assignOrUnnestIndex, 0, -1, null, context).first;
if (fieldName != null && !fieldName.isEmpty()) {
subTree.getVarsToFieldNameMap().put(var, fieldName);
}
@@ -1052,10 +1039,8 @@ public abstract class AbstractIntroduceAccessMethodRule implements IAlgebraicRew
LogicalVariable var = varList.get(varIndex);
// funcVarIndex is not required. Thus, we set it to -1.
// optFuncExpr and parentFuncExpr are not required, too. Thus, we set them to null.
- fieldSource.setValue(0);
List<String> fieldName = AccessMethodUtils.getFieldNameSetStepsFromSubTree(null, subTree,
- assignOrUnnestIndex, varIndex, subTree.getRecordType(), -1, null,
- subTree.getMetaRecordType(), datasetMetaVar, fieldSource, false);
+ assignOrUnnestIndex, varIndex, -1, null, context).first;
if (fieldName != null && !fieldName.isEmpty()) {
subTree.getVarsToFieldNameMap().put(var, fieldName);
}
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 3b39d9d043..ddc4964c03 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
@@ -24,7 +24,6 @@ import static org.apache.asterix.om.functions.BuiltinFunctions.FIELD_ACCESS_BY_N
import static org.apache.asterix.om.functions.BuiltinFunctions.FIELD_ACCESS_NESTED;
import java.util.ArrayList;
-import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.HashSet;
@@ -147,6 +146,8 @@ public class AccessMethodUtils {
BuiltinFunctions.YEAR_MONTH_DURATION_DEFAULT_NULL_CONSTRUCTOR,
BuiltinFunctions.UUID_DEFAULT_NULL_CONSTRUCTOR, BuiltinFunctions.BINARY_BASE64_DEFAULT_NULL_CONSTRUCTOR);
+ private final static Pair<List<String>, Integer> NO_FIELD_NAME = new Pair<>(Collections.emptyList(), 0);
+
public static void appendPrimaryIndexTypes(Dataset dataset, IAType itemType, IAType metaItemType,
List<Object> target) throws AlgebricksException {
ARecordType recordType = (ARecordType) itemType;
@@ -2898,17 +2899,16 @@ public class AccessMethodUtils {
return ann == null ? null : ann.getIndexNames();
}
- public static List<String> getFieldNameSetStepsFromSubTree(IOptimizableFuncExpr optFuncExpr,
- OptimizableOperatorSubTree subTree, int opIndex, int assignVarIndex, ARecordType recordType,
- int funcVarIndex, ILogicalExpression parentFuncExpr, ARecordType metaType, LogicalVariable metaVar,
- MutableInt fieldSource, boolean isUnnestOverVarAllowed) throws AlgebricksException {
+ public static Pair<List<String>, Integer> getFieldNameSetStepsFromSubTree(IOptimizableFuncExpr optFuncExpr,
+ OptimizableOperatorSubTree subTree, int opIndex, int assignVarIndex, int funcVarIndex,
+ ILogicalExpression parentFuncExpr, IOptimizationContext context) throws AlgebricksException {
if (optFuncExpr != null) {
if (parentFuncExpr.getExpressionTag() == LogicalExpressionTag.FUNCTION_CALL) {
optFuncExpr.addStepExpr(funcVarIndex, ((AbstractFunctionCallExpression) parentFuncExpr));
}
}
- return getFieldNameAndStepsFromSubTree(optFuncExpr, subTree, opIndex, assignVarIndex, recordType, funcVarIndex,
- parentFuncExpr, metaType, metaVar, fieldSource, isUnnestOverVarAllowed);
+ return getFieldNameAndStepsFromSubTree(optFuncExpr, subTree, opIndex, assignVarIndex, funcVarIndex,
+ parentFuncExpr, context);
}
/**
@@ -2918,10 +2918,9 @@ public class AccessMethodUtils {
*
* @throws AlgebricksException
*/
- private static List<String> getFieldNameAndStepsFromSubTree(IOptimizableFuncExpr optFuncExpr,
- OptimizableOperatorSubTree subTree, int opIndex, int assignVarIndex, ARecordType recordType,
- int funcVarIndex, ILogicalExpression parentFuncExpr, ARecordType metaType, LogicalVariable metaVar,
- MutableInt fieldSource, boolean isUnnestOverVarAllowed) throws AlgebricksException {
+ private static Pair<List<String>, Integer> getFieldNameAndStepsFromSubTree(IOptimizableFuncExpr optFuncExpr,
+ OptimizableOperatorSubTree subTree, int opIndex, int assignVarIndex, int funcVarIndex,
+ ILogicalExpression parentFuncExpr, IOptimizationContext context) throws AlgebricksException {
// Get expression corresponding to opVar at varIndex.
AbstractLogicalExpression expr = null;
AbstractFunctionCallExpression childFuncExpr = null;
@@ -2931,23 +2930,23 @@ public class AccessMethodUtils {
expr = (AbstractLogicalExpression) assignOp.getExpressions().get(assignVarIndex).getValue();
// Can't get a field name from a constant expression. So, return null.
if (expr.getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) {
- return Collections.emptyList();
+ return NO_FIELD_NAME;
}
childFuncExpr = (AbstractFunctionCallExpression) expr;
} else {
UnnestOperator unnestOp = (UnnestOperator) op;
expr = (AbstractLogicalExpression) unnestOp.getExpressionRef().getValue();
if (expr.getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) {
- return Collections.emptyList();
+ return NO_FIELD_NAME;
}
childFuncExpr = (AbstractFunctionCallExpression) expr;
if (childFuncExpr.getFunctionIdentifier() != BuiltinFunctions.SCAN_COLLECTION) {
- return Collections.emptyList();
+ return NO_FIELD_NAME;
}
expr = (AbstractLogicalExpression) childFuncExpr.getArguments().get(0).getValue();
}
if (expr.getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) {
- return Collections.emptyList();
+ return NO_FIELD_NAME;
}
AbstractFunctionCallExpression funcExpr = (AbstractFunctionCallExpression) expr;
FunctionIdentifier funcIdent = funcExpr.getFunctionIdentifier();
@@ -2960,21 +2959,21 @@ public class AccessMethodUtils {
if (funcIdent == BuiltinFunctions.FIELD_ACCESS_BY_NAME) {
fieldName = ConstantExpressionUtil.getStringArgument(funcExpr, 1);
if (fieldName == null) {
- return Collections.emptyList();
+ return NO_FIELD_NAME;
}
isFieldAccess = true;
isByName = true;
} else if (funcIdent == BuiltinFunctions.FIELD_ACCESS_BY_INDEX) {
Integer idx = ConstantExpressionUtil.getIntArgument(funcExpr, 1);
if (idx == null) {
- return Collections.emptyList();
+ return NO_FIELD_NAME;
}
fieldIndex = idx;
isFieldAccess = true;
} else if (funcIdent == BuiltinFunctions.FIELD_ACCESS_NESTED) {
ILogicalExpression nameArg = funcExpr.getArguments().get(1).getValue();
if (nameArg.getExpressionTag() != LogicalExpressionTag.CONSTANT) {
- return Collections.emptyList();
+ return NO_FIELD_NAME;
}
ConstantExpression constExpr = (ConstantExpression) nameArg;
AOrderedList orderedNestedFieldName =
@@ -2989,11 +2988,6 @@ public class AccessMethodUtils {
if (isFieldAccess) {
LogicalVariable sourceVar =
((VariableReferenceExpression) funcExpr.getArguments().get(0).getValue()).getVariableReference();
- if (sourceVar.equals(metaVar)) {
- fieldSource.setValue(1);
- } else {
- fieldSource.setValue(0);
- }
if (optFuncExpr != null) {
optFuncExpr.setLogicalExpr(funcVarIndex, parentFuncExpr);
optFuncExpr.addStepExpr(funcVarIndex, funcExpr);
@@ -3024,8 +3018,7 @@ public class AccessMethodUtils {
if (parentVars.contains(var)) {
//Found the variable we are looking for.
//return assign and index of expression
- int[] returnValues = { i, varIndex };
- assignAndExpressionIndexes = returnValues;
+ assignAndExpressionIndexes = new int[] { i, varIndex };
}
}
}
@@ -3033,34 +3026,25 @@ public class AccessMethodUtils {
//We found the nested assign
//Recursive call on nested assign
- List<String> parentFieldNames = getFieldNameAndStepsFromSubTree(optFuncExpr, subTree,
- assignAndExpressionIndexes[0], assignAndExpressionIndexes[1], recordType, funcVarIndex,
- parentFuncExpr, metaType, metaVar, fieldSource, isUnnestOverVarAllowed);
+ Pair<List<String>, Integer> parentFieldNames =
+ getFieldNameAndStepsFromSubTree(optFuncExpr, subTree, assignAndExpressionIndexes[0],
+ assignAndExpressionIndexes[1], funcVarIndex, parentFuncExpr, context);
- boolean isPreviousOperatorLegalUnnest = isUnnestOverVarAllowed && subTree.getAssignsAndUnnests()
- .get(assignAndExpressionIndexes[0]).getOperatorTag().equals(LogicalOperatorTag.UNNEST);
- if (parentFieldNames.isEmpty() && !isPreviousOperatorLegalUnnest) {
+ if (parentFieldNames.first.isEmpty()) {
//Nested assign was not a field access.
//We will not use index
- return Collections.emptyList();
- } else if (isPreviousOperatorLegalUnnest) {
- parentFieldNames = new ArrayList<>();
+ return NO_FIELD_NAME;
}
if (!isByName) {
- IAType subFieldType;
- if (isUnnestOverVarAllowed && isPreviousOperatorLegalUnnest) {
- // In the case of UNNESTing over a variable, we use the record type given by our caller instead.
- subFieldType = sourceVar.equals(metaVar) ? metaType : recordType;
- } else {
- subFieldType = sourceVar.equals(metaVar) ? metaType.getSubFieldType(parentFieldNames)
- : recordType.getSubFieldType(parentFieldNames);
- // Sub-field type can be AUnionType in case if optional. Thus, needs to get the actual type.
- subFieldType = TypeComputeUtils.getActualType(subFieldType);
- if (subFieldType.getTypeTag() != ATypeTag.OBJECT) {
- throw CompilationException.create(ErrorCode.TYPE_CONVERT, subFieldType,
- ARecordType.class.getName());
- }
+ IVariableTypeEnvironment outputTypeEnvironment = context.getOutputTypeEnvironment(
+ subTree.getAssignsAndUnnests().get(assignAndExpressionIndexes[0]));
+ IAType subFieldType = (IAType) outputTypeEnvironment.getVarType(sourceVar);
+ // Sub-field type can be AUnionType in case if optional. Thus, needs to get the actual type.
+ subFieldType = TypeComputeUtils.getActualType(subFieldType);
+ if (subFieldType.getTypeTag() != ATypeTag.OBJECT) {
+ throw CompilationException.create(ErrorCode.TYPE_CONVERT, subFieldType,
+ ARecordType.class.getName());
}
fieldName = ((ARecordType) subFieldType).getFieldNames()[fieldIndex];
@@ -3070,11 +3054,9 @@ public class AccessMethodUtils {
}
//add fieldName to the nested fieldName, return
if (nestedAccessFieldName != null) {
- for (int i = 0; i < nestedAccessFieldName.size(); i++) {
- parentFieldNames.add(nestedAccessFieldName.get(i));
- }
+ parentFieldNames.first.addAll(nestedAccessFieldName);
} else {
- parentFieldNames.add(fieldName);
+ parentFieldNames.first.add(fieldName);
}
return (parentFieldNames);
}
@@ -3083,15 +3065,15 @@ public class AccessMethodUtils {
optFuncExpr.setSourceVar(funcVarIndex, ((AssignOperator) op).getVariables().get(assignVarIndex));
}
//no nested assign, we are at the lowest level.
+ OptimizableOperatorSubTree.RecordTypeSource recType = subTree.getRecordTypeFor(sourceVar);
if (isByName) {
if (nestedAccessFieldName != null) {
- return nestedAccessFieldName;
+ return new Pair<>(nestedAccessFieldName, recType.sourceIndicator);
}
- return new ArrayList<>(Arrays.asList(fieldName));
+ return new Pair<>(new ArrayList<>(List.of(fieldName)), recType.sourceIndicator);
}
- return new ArrayList<>(Arrays.asList(sourceVar.equals(metaVar) ? metaType.getFieldNames()[fieldIndex]
- : recordType.getFieldNames()[fieldIndex]));
-
+ return new Pair<>(new ArrayList<>(List.of(recType.recordType.getFieldNames()[fieldIndex])),
+ recType.sourceIndicator);
}
// We use a part of the field in edit distance computation
@@ -3101,17 +3083,17 @@ public class AccessMethodUtils {
}
List<Mutable<ILogicalExpression>> funcArgs = funcExpr.getArguments();
if (funcArgs.isEmpty()) {
- return Collections.emptyList();
+ return NO_FIELD_NAME;
}
// We expect the function's argument to be a variable, otherwise we
// cannot apply an index.
ILogicalExpression argExpr = funcArgs.get(0).getValue();
if (argExpr.getExpressionTag() != LogicalExpressionTag.VARIABLE) {
- return Collections.emptyList();
+ return NO_FIELD_NAME;
}
for (int i = 1; i < funcArgs.size(); i++) {
if (funcArgs.get(i).getValue().getExpressionTag() != LogicalExpressionTag.CONSTANT) {
- return Collections.emptyList();
+ return NO_FIELD_NAME;
}
}
if (optFuncExpr != null) {
@@ -3131,20 +3113,19 @@ public class AccessMethodUtils {
if (var.equals(curVar) && optFuncExpr != null) {
optFuncExpr.setSourceVar(funcVarIndex, var);
return getFieldNameAndStepsFromSubTree(optFuncExpr, subTree, assignOrUnnestIndex, varIndex,
- recordType, funcVarIndex, childFuncExpr, metaType, metaVar, fieldSource,
- isUnnestOverVarAllowed);
+ funcVarIndex, childFuncExpr, context);
}
}
} else {
UnnestOperator unnestOp = (UnnestOperator) curOp;
LogicalVariable var = unnestOp.getVariable();
if (var.equals(curVar)) {
- getFieldNameAndStepsFromSubTree(optFuncExpr, subTree, assignOrUnnestIndex, 0, recordType,
- funcVarIndex, childFuncExpr, metaType, metaVar, fieldSource, isUnnestOverVarAllowed);
+ getFieldNameAndStepsFromSubTree(optFuncExpr, subTree, assignOrUnnestIndex, 0, funcVarIndex,
+ childFuncExpr, context);
}
}
}
- return Collections.emptyList();
+ return NO_FIELD_NAME;
}
public static Triple<Integer, List<String>, IAType> analyzeVarForArrayIndexes(List<Index> datasetIndexes,
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java
index 6f532196fc..751e216081 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceJoinAccessMethodRule.java
@@ -382,10 +382,10 @@ public class IntroduceJoinAccessMethodRule extends AbstractIntroduceAccessMethod
// Finds the field name of each variable in the sub-tree such as variables for order by.
// This step is required when checking index-only plan.
if (checkLeftSubTreeMetadata) {
- fillFieldNamesInTheSubTree(leftSubTree);
+ fillFieldNamesInTheSubTree(leftSubTree, context);
}
if (checkRightSubTreeMetadata) {
- fillFieldNamesInTheSubTree(rightSubTree);
+ fillFieldNamesInTheSubTree(rightSubTree, context);
}
// Applies the plan transformation using chosen index.
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java
index d9b5da988d..1cf4b0cbf5 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/IntroduceSelectAccessMethodRule.java
@@ -453,7 +453,7 @@ public class IntroduceSelectAccessMethodRule extends AbstractIntroduceAccessMeth
AccessMethodAnalysisContext analysisCtx = analyzedAMs.get(chosenIndexes.get(0).first);
// Finds the field name of each variable in the sub-tree.
- fillFieldNamesInTheSubTree(subTree);
+ fillFieldNamesInTheSubTree(subTree, context);
// Finally, try to apply plan transformation using chosen index.
res = chosenIndexes.get(0).first.applySelectPlanTransformation(afterSelectRefs, selectRef, subTree,
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/OptimizableOperatorSubTree.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/OptimizableOperatorSubTree.java
index c55edf982e..bbfb365edb 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/OptimizableOperatorSubTree.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/am/OptimizableOperatorSubTree.java
@@ -59,7 +59,7 @@ import org.apache.hyracks.algebricks.core.algebra.util.OperatorPropertiesUtil;
*/
public class OptimizableOperatorSubTree {
- public static enum DataSourceType {
+ public enum DataSourceType {
DATASOURCE_SCAN,
EXTERNAL_SCAN,
PRIMARY_INDEX_LOOKUP,
@@ -68,6 +68,16 @@ public class OptimizableOperatorSubTree {
NO_DATASOURCE
}
+ public static class RecordTypeSource {
+ final ARecordType recordType;
+ final int sourceIndicator;
+
+ RecordTypeSource(ARecordType recordType, int sourceIndicator) {
+ this.recordType = recordType;
+ this.sourceIndicator = sourceIndicator;
+ }
+ }
+
private ILogicalOperator root = null;
private Mutable<ILogicalOperator> rootRef = null;
private final List<Mutable<ILogicalOperator>> assignsAndUnnestsRefs = new ArrayList<>();
@@ -91,6 +101,7 @@ public class OptimizableOperatorSubTree {
private List<DataSourceType> ixJoinOuterAdditionalDataSourceTypes = null;
private List<Dataset> ixJoinOuterAdditionalDatasets = null;
private List<ARecordType> ixJoinOuterAdditionalRecordTypes = null;
+ private final Map<LogicalVariable, RecordTypeSource> varsToRecordType = new HashMap<>();
/**
* Identifies the root of the subtree and initializes the data-source, assign, and unnest information.
@@ -172,10 +183,10 @@ public class OptimizableOperatorSubTree {
AccessMethodJobGenParams jobGenParams = new AccessMethodJobGenParams();
jobGenParams.readFromFuncArgs(f.getArguments());
if (jobGenParams.isPrimaryIndex()) {
- intializeDataSourceRefAndType(DataSourceType.PRIMARY_INDEX_LOOKUP, subTreeOpRef);
+ initializeDataSourceRefAndType(DataSourceType.PRIMARY_INDEX_LOOKUP, subTreeOpRef);
dataSourceFound = true;
} else if (unnestMapOp.getGenerateCallBackProceedResultVar()) {
- intializeDataSourceRefAndType(DataSourceType.INDEXONLY_PLAN_SECONDARY_INDEX_LOOKUP,
+ initializeDataSourceRefAndType(DataSourceType.INDEXONLY_PLAN_SECONDARY_INDEX_LOOKUP,
subTreeOpRef);
dataSourceFound = true;
}
@@ -221,7 +232,7 @@ public class OptimizableOperatorSubTree {
return false;
}
- private void intializeDataSourceRefAndType(DataSourceType dsType, Mutable<ILogicalOperator> opRef) {
+ private void initializeDataSourceRefAndType(DataSourceType dsType, Mutable<ILogicalOperator> opRef) {
if (getDataSourceRef() == null) {
setDataSourceRef(opRef);
setDataSourceType(dsType);
@@ -238,12 +249,6 @@ public class OptimizableOperatorSubTree {
* Also sets recordType to be the type of that dataset.
*/
public boolean setDatasetAndTypeMetadata(MetadataProvider metadataProvider) throws AlgebricksException {
- DataverseName dataverseName = null;
- String datasetName = null;
-
- Dataset ds = null;
- ARecordType rType = null;
-
List<Mutable<ILogicalOperator>> sourceOpRefs = new ArrayList<>();
List<DataSourceType> dsTypes = new ArrayList<>();
@@ -259,6 +264,9 @@ public class OptimizableOperatorSubTree {
}
for (int i = 0; i < sourceOpRefs.size(); i++) {
+ List<LogicalVariable> vars;
+ DataverseName dataverseName;
+ String datasetName;
switch (dsTypes.get(i)) {
case DATASOURCE_SCAN:
DataSourceScanOperator dataSourceScan = (DataSourceScanOperator) sourceOpRefs.get(i).getValue();
@@ -272,6 +280,7 @@ public class OptimizableOperatorSubTree {
Pair<DataverseName, String> datasetInfo = AnalysisUtil.getDatasetInfo(dataSourceScan);
dataverseName = datasetInfo.first;
datasetName = datasetInfo.second;
+ vars = dataSourceScan.getScanVariables();
break;
case PRIMARY_INDEX_LOOKUP:
case INDEXONLY_PLAN_SECONDARY_INDEX_LOOKUP:
@@ -282,12 +291,14 @@ public class OptimizableOperatorSubTree {
jobGenParams.readFromFuncArgs(f.getArguments());
datasetName = jobGenParams.getDatasetName();
dataverseName = jobGenParams.getDataverseName();
+ vars = unnestMapOp.getScanVariables();
break;
case EXTERNAL_SCAN:
UnnestMapOperator externalScan = (UnnestMapOperator) sourceOpRefs.get(i).getValue();
datasetInfo = AnalysisUtil.getExternalDatasetInfo(externalScan);
dataverseName = datasetInfo.first;
datasetName = datasetInfo.second;
+ vars = externalScan.getScanVariables();
break;
case COLLECTION_SCAN:
if (i != 0) {
@@ -303,7 +314,7 @@ public class OptimizableOperatorSubTree {
return false;
}
// Find the dataset corresponding to the datasource in the metadata.
- ds = metadataProvider.findDataset(dataverseName, datasetName);
+ Dataset ds = metadataProvider.findDataset(dataverseName, datasetName);
if (ds == null) {
throw new CompilationException(ErrorCode.NO_METADATA_FOR_DATASET, root.getSourceLocation(),
datasetName);
@@ -318,26 +329,31 @@ public class OptimizableOperatorSubTree {
getIxJoinOuterAdditionalRecordTypes().add(null);
}
}
- rType = (ARecordType) itemType;
+ ARecordType rType = (ARecordType) itemType;
// Get the meta record type for that dataset.
- IAType metaItemType =
- metadataProvider.findType(ds.getMetaItemTypeDataverseName(), ds.getMetaItemTypeName());
+ ARecordType metaItemType = (ARecordType) metadataProvider.findType(ds.getMetaItemTypeDataverseName(),
+ ds.getMetaItemTypeName());
// First index is always the primary datasource in this subtree.
if (i == 0) {
setDataset(ds);
setRecordType(rType);
- setMetaRecordType((ARecordType) metaItemType);
+ setMetaRecordType(metaItemType);
} else {
getIxJoinOuterAdditionalDatasets().add(ds);
getIxJoinOuterAdditionalRecordTypes().add(rType);
}
- dataverseName = null;
- datasetName = null;
- ds = null;
- rType = null;
+ if (!vars.isEmpty()) {
+ int numVars = vars.size();
+ if (ds.hasMetaPart()) {
+ varsToRecordType.put(vars.get(numVars - 2), new RecordTypeSource(rType, 0));
+ varsToRecordType.put(vars.get(numVars - 1), new RecordTypeSource(metaItemType, 1));
+ } else {
+ varsToRecordType.put(vars.get(numVars - 1), new RecordTypeSource(rType, 0));
+ }
+ }
}
return true;
@@ -364,17 +380,6 @@ public class OptimizableOperatorSubTree {
return getDataSourceType() == DataSourceType.DATASOURCE_SCAN;
}
- public boolean hasIxJoinOuterAdditionalDataSourceScan() {
- if (getIxJoinOuterAdditionalDataSourceTypes() != null) {
- for (int i = 0; i < getIxJoinOuterAdditionalDataSourceTypes().size(); i++) {
- if (getIxJoinOuterAdditionalDataSourceTypes().get(i) == DataSourceType.DATASOURCE_SCAN) {
- return true;
- }
- }
- }
- return false;
- }
-
public void reset() {
setRoot(null);
setRootRef(null);
@@ -392,6 +397,7 @@ public class OptimizableOperatorSubTree {
setIxJoinOuterAdditionalRecordTypes(null);
lastMatchedDataSourceVars.first = -1;
lastMatchedDataSourceVars.second = -1;
+ varsToRecordType.clear();
}
/**
@@ -545,6 +551,10 @@ public class OptimizableOperatorSubTree {
return recordType;
}
+ public RecordTypeSource getRecordTypeFor(LogicalVariable var) {
+ return varsToRecordType.get(var);
+ }
+
public void setRecordType(ARecordType recordType) {
this.recordType = recordType;
}
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-ternary-inlj/query4.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-ternary-inlj/query4.sqlpp
new file mode 100644
index 0000000000..fae9ea4700
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/btree-ternary-inlj/query4.sqlpp
@@ -0,0 +1,194 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Description: ASTERIXDB-3043. Part of q18 of tpcds.
+ */
+
+DROP DATAVERSE tpcds IF EXISTS;
+CREATE DATAVERSE tpcds;
+
+USE tpcds;
+
+CREATE TYPE tpcds.catalog_sales_type AS
+ CLOSED {
+ cs_sold_date_sk: bigint?,
+ cs_sold_time_sk: bigint?,
+ cs_ship_date_sk: bigint?,
+ cs_bill_customer_sk: bigint?,
+ cs_bill_cdemo_sk: bigint?,
+ cs_bill_hdemo_sk: bigint?,
+ cs_bill_addr_sk: bigint?,
+ cs_ship_customer_sk: bigint?,
+ cs_ship_cdemo_sk: bigint?,
+ cs_ship_hdemo_sk: bigint?,
+ cs_ship_addr_sk: bigint?,
+ cs_call_center_sk: bigint?,
+ cs_catalog_page_sk: bigint?,
+ cs_ship_mode_sk: bigint?,
+ cs_warehouse_sk: bigint?,
+ cs_item_sk: bigint,
+ cs_promo_sk: bigint?,
+ cs_order_number: bigint,
+ cs_quantity: bigint?,
+ cs_wholesale_cost: double?,
+ cs_list_price: double?,
+ cs_sales_price: double?,
+ cs_ext_discount_amt: double?,
+ cs_ext_sales_price: double?,
+ cs_ext_wholesale_cost: double?,
+ cs_ext_list_price: double?,
+ cs_ext_tax: double?,
+ cs_coupon_amt: double?,
+ cs_ext_ship_cost: double?,
+ cs_net_paid: double?,
+ cs_net_paid_inc_tax: double?,
+ cs_net_paid_inc_ship: double?,
+ cs_net_paid_inc_ship_tax: double?,
+ cs_net_profit: double?
+};
+
+CREATE TYPE tpcds.customer_demographics_type AS
+ CLOSED {
+ cd_demo_sk : int64,
+ cd_gender : string?,
+ cd_marital_status : string?,
+ cd_education_status : string?,
+ cd_purchase_estimate : int64?,
+ cd_credit_rating : string?,
+ cd_dep_count : int64?,
+ cd_dep_employed_count : int64?,
+ cd_dep_college_count : int64?
+};
+
+CREATE TYPE tpcds.customer_type AS
+ CLOSED {
+ c_customer_sk : int64,
+ c_customer_id : string,
+ c_current_cdemo_sk : int64?,
+ c_current_hdemo_sk : int64?,
+ c_current_addr_sk : int64?,
+ c_first_shipto_date_sk : int64?,
+ c_first_sales_date_sk : int64?,
+ c_salutation : string?,
+ c_first_name : string?,
+ c_last_name : string?,
+ c_preferred_cust_flag : string?,
+ c_birth_day : int64?,
+ c_birth_month : int64?,
+ c_birth_year : int64?,
+ c_birth_country : string?,
+ c_login : string?,
+ c_email_address : string?,
+ c_last_review_date : string?
+};
+
+CREATE TYPE tpcds.customer_address_type AS
+ CLOSED {
+ ca_address_sk : bigint,
+ ca_address_id : string,
+ ca_street_number : string?,
+ ca_street_name : string?,
+ ca_street_type : string?,
+ ca_suite_number : string?,
+ ca_city : string?,
+ ca_county : string?,
+ ca_state : string?,
+ ca_zip : string?,
+ ca_country : string?,
+ ca_gmt_offset : double?,
+ ca_location_type : string?
+ };
+
+CREATE TYPE tpcds.item_type AS
+ CLOSED {
+ i_item_sk : bigint,
+ i_item_id : string,
+ i_rec_start_date : string?,
+ i_rec_end_date : string?,
+ i_item_desc : string?,
+ i_current_price : double?,
+ i_wholesale_cost : double?,
+ i_brand_id : bigint?,
+ i_brand : string?,
+ i_class_id : bigint?,
+ i_class : string?,
+ i_category_id : bigint?,
+ i_category : string?,
+ i_manufact_id : bigint?,
+ i_manufact : string?,
+ i_size : string?,
+ i_formulation : string?,
+ i_color : string?,
+ i_units : string?,
+ i_container : string?,
+ i_manager_id : bigint?,
+ i_product_name : string?
+};
+
+CREATE TYPE tpcds.date_dim_type AS
+ CLOSED {
+ d_date_sk : bigint,
+ d_date_id : string,
+ d_date : string?,
+ d_month_seq : bigint?,
+ d_week_seq : bigint?,
+ d_quarter_seq : bigint?,
+ d_year : bigint? ,
+ d_dow : bigint? ,
+ d_moy : bigint?,
+ d_dom : bigint?,
+ d_qoy : bigint?,
+ d_fy_year : bigint?,
+ d_fy_quarter_seq : bigint?,
+ d_fy_week_seq : bigint?,
+ d_day_name : string?,
+ d_quarter_name : string?,
+ d_holiday : string?,
+ d_weekend : string?,
+ d_following_holiday : string?,
+ d_first_dom : bigint?,
+ d_last_dom : bigint?,
+ d_same_day_ly : bigint?,
+ d_same_day_lq : bigint?,
+ d_current_day : string?,
+ d_current_week : string?,
+ d_current_month : string?,
+ d_current_quarter : string?,
+ d_current_year : string?
+};
+
+CREATE DATASET catalog_sales(catalog_sales_type) PRIMARY KEY cs_item_sk, cs_order_number;
+
+CREATE DATASET customer_demographics(customer_demographics_type) PRIMARY KEY cd_demo_sk;
+
+CREATE DATASET customer(customer_type) PRIMARY KEY c_customer_sk;
+
+CREATE DATASET customer_address(customer_address_type) PRIMARY KEY ca_address_sk;
+
+CREATE DATASET item(item_type) PRIMARY KEY i_item_sk;
+
+CREATE DATASET date_dim(date_dim_type) PRIMARY KEY d_date_sk;
+
+SELECT count (*)
+FROM customer c, customer_demographics cd2, customer_address ca
+WHERE
+ c.c_current_cdemo_sk /*+ indexnl */ = cd2.cd_demo_sk
+ AND c.c_current_addr_sk /*+ indexnl */= ca.ca_address_sk
+ AND c.c_birth_month in [4,5];
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-ternary-inlj/query4.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-ternary-inlj/query4.plan
new file mode 100644
index 0000000000..4e1a931f9f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-ternary-inlj/query4.plan
@@ -0,0 +1,32 @@
+-- DISTRIBUTE_RESULT |UNPARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
+ -- STREAM_PROJECT |UNPARTITIONED|
+ -- ASSIGN |UNPARTITIONED|
+ -- AGGREGATE |UNPARTITIONED|
+ -- RANDOM_MERGE_EXCHANGE |PARTITIONED|
+ -- AGGREGATE |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- HYBRID_HASH_JOIN [$$57][$$70] |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (tpcds.customer_address.customer_address) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$74(ASC)] |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$74] |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- BTREE_SEARCH (tpcds.customer_demographics.customer_demographics) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- STABLE_SORT [$$72(ASC)] |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$72] |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ASSIGN |PARTITIONED|
+ -- STREAM_PROJECT |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- DATASOURCE_SCAN (tpcds.customer) |PARTITIONED|
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- UNNEST |UNPARTITIONED|
+ -- EMPTY_TUPLE_SOURCE |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.4.query.sqlpp
new file mode 100644
index 0000000000..1f7c7d5bd4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.4.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+// see ASTERIXDB-3043
+SET `compiler.joinmemory` "160KB";
+
+USE tpcds;
+
+SELECT count (*) AS cnt
+FROM customer c, customer_demographics cd2, customer_address ca
+WHERE
+ c.c_current_cdemo_sk /*+ indexnl */ = cd2.cd_demo_sk
+ AND c.c_current_addr_sk /*+ indexnl */ = ca.ca_address_sk
+ AND c.c_birth_month in [4,5];
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.5.query.sqlpp
new file mode 100644
index 0000000000..3a7ae0b410
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/q18/q18.5.query.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+// see ASTERIXDB-3043
+SET `compiler.joinmemory` "160KB";
+
+USE tpcds;
+
+SELECT count (*) AS cnt
+FROM customer c, customer_demographics cd2, customer_address ca
+WHERE
+ c.c_current_cdemo_sk = cd2.cd_demo_sk
+ AND c.c_current_addr_sk = ca.ca_address_sk
+ AND c.c_birth_month in [4,5];
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q18/q18.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q18/q18.2.adm
new file mode 100644
index 0000000000..6280446914
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q18/q18.2.adm
@@ -0,0 +1 @@
+{ "cnt": 3 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q18/q18.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q18/q18.3.adm
new file mode 100644
index 0000000000..6280446914
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/q18/q18.3.adm
@@ -0,0 +1 @@
+{ "cnt": 3 }
\ No newline at end of file