This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new 7ec36a24c8a [opt](mtmv) Support materialized view query rewriting by
join eliminate when mv use more table than query #56266 (#58108)
7ec36a24c8a is described below
commit 7ec36a24c8ac95eef2455141e2ea54496901ccc0
Author: seawinde <[email protected]>
AuthorDate: Wed Nov 26 09:41:16 2025 +0800
[opt](mtmv) Support materialized view query rewriting by join eliminate
when mv use more table than query #56266 (#58108)
### What problem does this PR solve?
pr: #56266
commitId: 12d2aaa9
Issue Number: close #xxx
Related PR: #xxx
Problem Summary:
### Release note
None
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [ ] Regression test
- [ ] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
- Behavior changed:
- [ ] No.
- [ ] Yes. <!-- Explain the behavior change -->
- Does this need documentation?
- [ ] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR
should merge into -->
Co-authored-by: zfr9527 <[email protected]>
---
.../doris/nereids/jobs/executor/Rewriter.java | 5 +
.../joinorder/hypergraph/bitmap/LongBitmap.java | 5 +
.../org/apache/doris/nereids/rules/RuleType.java | 1 -
.../mv/AbstractMaterializedViewRule.java | 29 +-
.../mv/AbstractMaterializedViewScanRule.java | 7 +
.../rules/exploration/mv/HyperGraphComparator.java | 95 +++-
.../exploration/mv/MaterializedViewUtils.java | 19 +-
.../exploration/mv/mapping/ExpressionMapping.java | 10 +-
.../rules/exploration/mv/mapping/SlotMapping.java | 2 +-
.../plans/visitor/ExpressionLineageReplacer.java | 7 +
.../apache/doris/nereids/util/ExpressionUtils.java | 27 +
.../rules/exploration/mv/BuildStructInfoTest.java | 107 ++++
.../exploration/mv/HyperGraphComparatorTest.java | 47 +-
.../doris/nereids/util/ExpressionUtilsTest.java | 46 ++
.../join_elim_line_pattern.groovy | 558 +++++++++++++++++++
.../join_elim_star_pattern.groovy | 592 +++++++++++++++++++++
16 files changed, 1507 insertions(+), 50 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
index 9aff8ff582d..a9e28a2af0c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java
@@ -385,6 +385,11 @@ public class Rewriter extends AbstractBatchJobExecutor {
new CollectFilterAboveConsumer(),
new CollectCteConsumerOutput())
),
+ topic("eliminate join according unique or foreign
key",
+ cascadesContext ->
cascadesContext.rewritePlanContainsTypes(LogicalJoin.class),
+ bottomUp(new EliminateJoinByFK()),
+ topDown(new EliminateJoinByUnique())
+ ),
topic("Table/Physical optimization",
topDown(
new PruneOlapScanPartition(),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/joinorder/hypergraph/bitmap/LongBitmap.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/joinorder/hypergraph/bitmap/LongBitmap.java
index 4365ec9ae20..32aa78393b7 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/joinorder/hypergraph/bitmap/LongBitmap.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/joinorder/hypergraph/bitmap/LongBitmap.java
@@ -155,6 +155,11 @@ public class LongBitmap {
return SIZE - Long.numberOfLeadingZeros(newBitmap) - 1;
}
+ // return true if bitmap1 contains all bits in bitmap2
+ public static boolean containAll(long bitmap1, long bitmap2) {
+ return (bitmap1 & bitmap2) == bitmap2;
+ }
+
public static int lowestOneIndex(long bitmap) {
return Long.numberOfTrailingZeros(bitmap);
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
index 00a9174f93e..61790063917 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleType.java
@@ -278,7 +278,6 @@ public enum RuleType {
ELIMINATE_LIMIT_ON_EMPTY_RELATION(RuleTypeClass.REWRITE),
ELIMINATE_FILTER(RuleTypeClass.REWRITE),
ELIMINATE_JOIN(RuleTypeClass.REWRITE),
- ELIMINATE_JOIN_BY_FOREIGN_KEY(RuleTypeClass.REWRITE),
ELIMINATE_CONST_JOIN_CONDITION(RuleTypeClass.REWRITE),
ELIMINATE_JOIN_CONDITION(RuleTypeClass.REWRITE),
ELIMINATE_FILTER_ON_ONE_RELATION(RuleTypeClass.REWRITE),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewRule.java
index dfedd782f85..6668b363897 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewRule.java
@@ -18,7 +18,6 @@
package org.apache.doris.nereids.rules.exploration.mv;
import org.apache.doris.catalog.MTMV;
-import org.apache.doris.catalog.constraint.TableIdentifier;
import org.apache.doris.common.AnalysisException;
import org.apache.doris.common.Id;
import org.apache.doris.common.Pair;
@@ -55,6 +54,7 @@ import
org.apache.doris.nereids.trees.expressions.literal.Literal;
import org.apache.doris.nereids.trees.expressions.literal.VarcharLiteral;
import org.apache.doris.nereids.trees.plans.JoinType;
import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.TableId;
import org.apache.doris.nereids.trees.plans.algebra.CatalogRelation;
import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
@@ -213,8 +213,9 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
MaterializationContext materializationContext) throws
AnalysisException {
List<Plan> rewriteResults = new ArrayList<>();
StructInfo viewStructInfo = materializationContext.getStructInfo();
- MatchMode matchMode = decideMatchMode(queryStructInfo.getRelations(),
viewStructInfo.getRelations());
- if (MatchMode.COMPLETE != matchMode) {
+ MatchMode matchMode = decideMatchMode(queryStructInfo.getRelations(),
viewStructInfo.getRelations(),
+ cascadesContext);
+ if (MatchMode.COMPLETE != matchMode && MatchMode.QUERY_PARTIAL !=
matchMode) {
materializationContext.recordFailReason(queryStructInfo, "Match
mode is invalid",
() -> String.format("matchMode is %s", matchMode));
return rewriteResults;
@@ -566,12 +567,12 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
Map<Expression, ExpressionInfo> queryExprToInfoMap,
CascadesContext cascadesContext) {
// Firstly, rewrite the target expression using source with inverse
mapping
// then try to use the target expression to represent the query. if
any of source expressions
- // can not be represented by target expressions, return null.
+ // could not be represented by target expressions, return null.
// generate target to target replacement expression mapping, and
change target expression to source based
List<? extends Expression> sourceShuttledExpressions =
ExpressionUtils.shuttleExpressionWithLineage(
sourceExpressionsToWrite, sourcePlan, sourcePlanBitSet);
ExpressionMapping expressionMappingKeySourceBased =
targetExpressionMapping.keyPermute(targetToSourceMapping);
- // target to target replacement expression mapping, because mv is 1:1
so get first element
+ // target to target replacement expression mapping, because mv is 1:1
so get the first element
List<Map<Expression, Expression>> flattenExpressionMap =
expressionMappingKeySourceBased.flattenMap();
Map<Expression, Expression> targetToTargetReplacementMappingQueryBased
=
flattenExpressionMap.get(0);
@@ -602,7 +603,7 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
targetToTargetReplacementMappingQueryBased);
Set<Expression> replacedExpressionSlotQueryUsed =
replacedExpression.collect(slotsToRewrite::contains);
if (!replacedExpressionSlotQueryUsed.isEmpty()) {
- // if contains any slot to rewrite, which means can not be
rewritten by target,
+ // if contains any slot to rewrite, which means could not be
rewritten by target,
// expressionShuttledToRewrite is slot#0 > '2024-01-01' but mv
plan output is date_trunc(slot#0, 'day')
// which would try to rewrite
if (viewExprParamToDateTruncMap.isEmpty()
@@ -617,7 +618,7 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
if (!queryExprToInfoMap.containsKey(queryOriginalExpr)
||
!viewExprParamToDateTruncMap.containsKey(queryShuttledExprParam)) {
// query expr contains expression info or mv out contains
date_trunc expression,
- // if not, can not try to rewritten by view date_trunc,
bail out
+ // if not, could not try to be rewritten by view
date_trunc, bail out
return ImmutableList.of();
}
Map<Expression, Expression> datetruncMap = new HashMap<>();
@@ -650,7 +651,7 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
}
/**
- * if query contains variant slot reference, extend the expression mapping
for rewrte
+ * if query contains variant slot reference, extend the expression mapping
for rewrite
* such as targetToTargetReplacementMappingQueryBased is
* id#0 -> id#8
* type#1 -> type#9
@@ -912,15 +913,15 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
*
* @see MatchMode
*/
- private MatchMode decideMatchMode(List<CatalogRelation> queryRelations,
List<CatalogRelation> viewRelations) {
-
- Set<TableIdentifier> queryTables = new HashSet<>();
+ private MatchMode decideMatchMode(List<CatalogRelation> queryRelations,
List<CatalogRelation> viewRelations,
+ CascadesContext cascadesContext) {
+ Set<TableId> queryTables = new HashSet<>();
for (CatalogRelation catalogRelation : queryRelations) {
- queryTables.add(new TableIdentifier(catalogRelation.getTable()));
+
queryTables.add(cascadesContext.getStatementContext().getTableId(catalogRelation.getTable()));
}
- Set<TableIdentifier> viewTables = new HashSet<>();
+ Set<TableId> viewTables = new HashSet<>();
for (CatalogRelation catalogRelation : viewRelations) {
- viewTables.add(new TableIdentifier(catalogRelation.getTable()));
+
viewTables.add(cascadesContext.getStatementContext().getTableId(catalogRelation.getTable()));
}
if (queryTables.equals(viewTables)) {
return MatchMode.COMPLETE;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewScanRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewScanRule.java
index 1fc899f5993..a91e2314d32 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewScanRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewScanRule.java
@@ -85,4 +85,11 @@ public abstract class AbstractMaterializedViewScanRule
extends AbstractMateriali
&& !checkContext.isContainsTopLimit() &&
!checkContext.isContainsTopTopN()
&& !checkContext.isContainsTopWindow();
}
+
+ @Override
+ protected boolean checkMaterializationPattern(StructInfo structInfo,
CascadesContext cascadesContext) {
+ PlanCheckContext checkContext =
PlanCheckContext.of(SUPPORTED_JOIN_TYPE_SET);
+ return structInfo.getTopPlan().accept(StructInfo.PLAN_PATTERN_CHECKER,
checkContext)
+ && !checkContext.isContainsTopAggregate();
+ }
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/HyperGraphComparator.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/HyperGraphComparator.java
index f6d23e6a08a..8a6ed611cd1 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/HyperGraphComparator.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/HyperGraphComparator.java
@@ -88,7 +88,8 @@ public class HyperGraphComparator {
private final Map<JoinEdge, Pair<JoinType, Pair<Set<Slot>, Set<Slot>>>>
inferredViewEdgeWithCond = new HashMap<>();
private List<JoinEdge> viewJoinEdgesAfterInferring;
private List<FilterEdge> viewFilterEdgesAfterInferring;
- private final long eliminateViewNodesMap;
+ private final long shouldEliminateViewNodesMap;
+ private long reservedShouldEliminatedViewNodes;
/**
* constructor
@@ -98,9 +99,10 @@ public class HyperGraphComparator {
this.queryHyperGraph = queryHyperGraph;
this.viewHyperGraph = viewHyperGraph;
this.logicalCompatibilityContext = logicalCompatibilityContext;
- this.eliminateViewNodesMap = LongBitmap.newBitmapDiff(
+ this.shouldEliminateViewNodesMap = LongBitmap.newBitmapDiff(
viewHyperGraph.getNodesMap(),
LongBitmap.newBitmap(logicalCompatibilityContext.getQueryToViewNodeIDMapping().values()));
+ this.reservedShouldEliminatedViewNodes =
this.shouldEliminateViewNodesMap;
}
/**
@@ -156,11 +158,11 @@ public class HyperGraphComparator {
.forEach(e -> pullUpQueryExprWithEdge.put(e,
e.getExpressions()));
Sets.difference(getViewJoinEdgeSet(),
Sets.newHashSet(queryToViewJoinEdge.values()))
.stream()
- .filter(e -> !LongBitmap.isOverlap(e.getReferenceNodes(),
eliminateViewNodesMap))
+ .filter(e -> !LongBitmap.isOverlap(e.getReferenceNodes(),
shouldEliminateViewNodesMap))
.forEach(e -> pullUpViewExprWithEdge.put(e,
e.getExpressions()));
Sets.difference(getViewFilterEdgeSet(),
Sets.newHashSet(queryToViewFilterEdge.values()))
.stream()
- .filter(e -> !LongBitmap.isOverlap(e.getReferenceNodes(),
eliminateViewNodesMap))
+ .filter(e -> !LongBitmap.isOverlap(e.getReferenceNodes(),
shouldEliminateViewNodesMap))
.forEach(e -> pullUpViewExprWithEdge.put(e,
e.getExpressions()));
return buildComparisonRes();
@@ -196,7 +198,7 @@ public class HyperGraphComparator {
// eliminate by unique
if (joinEdge.getJoinType().isLeftOuterJoin() &&
joinEdge.isRightSimple()) {
long eliminatedRight =
-
LongBitmap.newBitmapIntersect(joinEdge.getRightExtendedNodes(),
eliminateViewNodesMap);
+
LongBitmap.newBitmapIntersect(joinEdge.getRightExtendedNodes(),
reservedShouldEliminatedViewNodes);
if (LongBitmap.getCardinality(eliminatedRight) != 1) {
return false;
}
@@ -204,8 +206,14 @@ public class HyperGraphComparator {
if (rigthPlan == null) {
return false;
}
- return JoinUtils.canEliminateByLeft(joinEdge.getJoin(),
+ boolean couldEliminateByLeft =
JoinUtils.canEliminateByLeft(joinEdge.getJoin(),
rigthPlan.getLogicalProperties().getTrait());
+ // if eliminated successfully, should refresh the
eliminateViewNodesMap
+ if (couldEliminateByLeft) {
+ this.reservedShouldEliminatedViewNodes =
+
LongBitmap.newBitmapDiff(reservedShouldEliminatedViewNodes, eliminatedRight);
+ }
+ return couldEliminateByLeft;
}
// eliminate by pk fk
if (joinEdge.getJoinType().isInnerJoin()) {
@@ -213,17 +221,30 @@ public class HyperGraphComparator {
return false;
}
long eliminatedLeft =
-
LongBitmap.newBitmapIntersect(joinEdge.getLeftExtendedNodes(),
eliminateViewNodesMap);
+
LongBitmap.newBitmapIntersect(joinEdge.getLeftExtendedNodes(),
reservedShouldEliminatedViewNodes);
long eliminatedRight =
-
LongBitmap.newBitmapIntersect(joinEdge.getRightExtendedNodes(),
eliminateViewNodesMap);
+
LongBitmap.newBitmapIntersect(joinEdge.getRightExtendedNodes(),
reservedShouldEliminatedViewNodes);
+ // Only eliminate the node which is in eliminateViewNodesMap
if (LongBitmap.getCardinality(eliminatedLeft) == 0
&& LongBitmap.getCardinality(eliminatedRight) == 1) {
- return
canEliminatePrimaryByForeign(joinEdge.getRightExtendedNodes(),
joinEdge.getLeftExtendedNodes(),
+ boolean canEliminated =
canEliminatePrimaryByForeign(joinEdge.getRightExtendedNodes(),
+ joinEdge.getLeftExtendedNodes(),
joinEdge.getRightInputSlots(),
joinEdge.getLeftInputSlots(), joinEdge);
+ if (canEliminated) {
+ this.reservedShouldEliminatedViewNodes =
LongBitmap.newBitmapDiff(
+ reservedShouldEliminatedViewNodes,
joinEdge.getRightExtendedNodes());
+ }
+ return canEliminated;
} else if (LongBitmap.getCardinality(eliminatedLeft) == 1
&& LongBitmap.getCardinality(eliminatedRight) == 0) {
- return
canEliminatePrimaryByForeign(joinEdge.getLeftExtendedNodes(),
joinEdge.getRightExtendedNodes(),
+ boolean canEliminate =
canEliminatePrimaryByForeign(joinEdge.getLeftExtendedNodes(),
+ joinEdge.getRightExtendedNodes(),
joinEdge.getLeftInputSlots(),
joinEdge.getRightInputSlots(), joinEdge);
+ if (canEliminate) {
+ this.reservedShouldEliminatedViewNodes =
LongBitmap.newBitmapDiff(
+ reservedShouldEliminatedViewNodes,
joinEdge.getLeftExtendedNodes());
+ }
+ return canEliminate;
}
}
return false;
@@ -232,15 +253,46 @@ public class HyperGraphComparator {
private boolean tryEliminateNodesAndEdge() {
boolean hasFilterEdgeAbove = viewHyperGraph.getFilterEdges().stream()
.filter(e -> LongBitmap.getCardinality(e.getReferenceNodes())
== 1)
- .anyMatch(e -> LongBitmap.isSubset(e.getReferenceNodes(),
eliminateViewNodesMap));
+ .anyMatch(e -> LongBitmap.isSubset(e.getReferenceNodes(),
shouldEliminateViewNodesMap));
if (hasFilterEdgeAbove) {
// If there is some filter edge above the eliminated node, we
should rebuild a plan
// Right now, just reject it.
return false;
}
- return viewHyperGraph.getJoinEdges().stream()
- .filter(joinEdge ->
LongBitmap.isOverlap(joinEdge.getReferenceNodes(), eliminateViewNodesMap))
- .allMatch(this::canEliminateViewEdge);
+ long allCanEliminateNodes = 0;
+ for (JoinEdge joinEdge : viewHyperGraph.getJoinEdges()) {
+ long canEliminateSideNodes = getCanEliminateSideNodes(joinEdge);
+ allCanEliminateNodes = LongBitmap.or(allCanEliminateNodes,
canEliminateSideNodes);
+ if (LongBitmap.isOverlap(canEliminateSideNodes,
reservedShouldEliminatedViewNodes)
+ && !canEliminateViewEdge(joinEdge)) {
+ return false;
+ }
+ }
+ // check all can eliminateNodes contains all should eliminate nodes,
to avoid some nodes can not be eliminated
+ // but in shouldEliminateViewNodesMap
+ // check all needed to eliminate nodes already be eliminated
+ return LongBitmap.containAll(allCanEliminateNodes,
shouldEliminateViewNodesMap)
+ &&
LongBitmap.getCardinality(reservedShouldEliminatedViewNodes) == 0;
+ }
+
+ private static long getCanEliminateSideNodes(JoinEdge joinEdge) {
+ long leftExtendedNodes = joinEdge.getLeftExtendedNodes();
+ long rightExtendedNodes = joinEdge.getRightExtendedNodes();
+ long nodesToCheck = LongBitmap.newBitmap();
+ if (joinEdge.getJoinType().isLeftOuterJoin() &&
joinEdge.isRightSimple()) {
+ if (LongBitmap.getCardinality(rightExtendedNodes) == 1) {
+ nodesToCheck = LongBitmap.or(rightExtendedNodes, nodesToCheck);
+ }
+ }
+ if (joinEdge.getJoinType().isInnerJoin()) {
+ if (LongBitmap.getCardinality(leftExtendedNodes) == 1) {
+ nodesToCheck = LongBitmap.or(leftExtendedNodes, nodesToCheck);
+ }
+ if (LongBitmap.getCardinality(rightExtendedNodes) == 1) {
+ nodesToCheck = LongBitmap.or(rightExtendedNodes, nodesToCheck);
+ }
+ }
+ return nodesToCheck;
}
private boolean compareNodeWithExpr(StructInfoNode query, StructInfoNode
view) {
@@ -558,12 +610,17 @@ public class HyperGraphComparator {
private boolean compareJoinEdgeWithNode(JoinEdge query, JoinEdge view) {
boolean res = false;
+ // if eliminateViewNodesMap is not empty, we should compare the join
nodes after eliminating
if (query.getJoinType().swap() == view.getJoinType()) {
- res |= getViewNodesByQuery(query.getLeftExtendedNodes()) ==
view.getRightExtendedNodes()
- && getViewNodesByQuery(query.getRightExtendedNodes()) ==
view.getLeftExtendedNodes();
- }
- res |= getViewNodesByQuery(query.getLeftExtendedNodes()) ==
view.getLeftExtendedNodes()
- && getViewNodesByQuery(query.getRightExtendedNodes()) ==
view.getRightExtendedNodes();
+ res |= getViewNodesByQuery(query.getLeftExtendedNodes()) ==
LongBitmap.newBitmapDiff(
+ view.getRightExtendedNodes(),
this.shouldEliminateViewNodesMap)
+ && getViewNodesByQuery(query.getRightExtendedNodes()) ==
LongBitmap.newBitmapDiff(
+ view.getLeftExtendedNodes(),
this.shouldEliminateViewNodesMap);
+ }
+ res |= getViewNodesByQuery(query.getLeftExtendedNodes()) ==
LongBitmap.newBitmapDiff(
+ view.getLeftExtendedNodes(), this.shouldEliminateViewNodesMap)
+ && getViewNodesByQuery(query.getRightExtendedNodes()) ==
LongBitmap.newBitmapDiff(
+ view.getRightExtendedNodes(),
this.shouldEliminateViewNodesMap);
return res;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewUtils.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewUtils.java
index 3341c499904..347b800c096 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewUtils.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewUtils.java
@@ -286,12 +286,11 @@ public class MaterializedViewUtils {
ImmutableList.Builder<StructInfo> structInfosBuilder =
ImmutableList.builder();
if (!queryTableSets.isEmpty()) {
for (BitSet queryTableSet : queryTableSets) {
- // TODO As only support MatchMode.COMPLETE, so only get
equaled query table struct info
BitSet queryCommonTableSet =
MaterializedViewUtils.transformToCommonTableId(queryTableSet,
cascadesContext.getStatementContext().getRelationIdToCommonTableIdMap());
// compare relation id corresponding table id
if (!materializedViewTableSet.isEmpty()
- &&
!materializedViewTableSet.equals(queryCommonTableSet)) {
+ && !containsAll(materializedViewTableSet,
queryCommonTableSet)) {
continue;
}
StructInfo structInfo =
structInfoMap.getStructInfo(cascadesContext, queryTableSet, ownerGroup,
@@ -586,6 +585,22 @@ public class MaterializedViewUtils {
return Pair.of(chosenMaterializationMap, usedRelation);
}
+ /**
+ * Checks if the superset contains all of the set bits from the subset.
+ *
+ * @param superset The BitSet expected to contain the bits.
+ * @param subset The BitSet whose set bits are to be checked.
+ * @return true if all bits set in the subset are also set in the
superset, false otherwise.
+ */
+ public static boolean containsAll(BitSet superset, BitSet subset) {
+ // Clone the subset to avoid modifying the original instance.
+ BitSet temp = (BitSet) subset.clone();
+ // Remove all bits from temp that are also present in the superset.
+ // temp.andNot(superset) is equivalent to the operation: temp = temp
AND (NOT superset)
+ temp.andNot(superset);
+ return temp.isEmpty();
+ }
+
/**
* Check the query if Contains query operator
* Such sql as following should return true
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/mapping/ExpressionMapping.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/mapping/ExpressionMapping.java
index 8c77eacfaf0..98cf50a578f 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/mapping/ExpressionMapping.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/mapping/ExpressionMapping.java
@@ -19,6 +19,7 @@ package org.apache.doris.nereids.rules.exploration.mv.mapping;
import org.apache.doris.common.Pair;
import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
import org.apache.doris.nereids.util.ExpressionUtils;
import org.apache.doris.nereids.util.Utils;
@@ -82,7 +83,14 @@ public class ExpressionMapping extends Mapping {
this.getExpressionMapping().asMap();
for (Map.Entry<? extends Expression, ? extends Collection<? extends
Expression>> entry :
expressionMap.entrySet()) {
- Expression replacedExpr = ExpressionUtils.replace(entry.getKey(),
slotMapping.toSlotReferenceMap());
+ // permute by slot mapping, if entry expression is not in slot
mapping should discard,
+ // because in view partial rewrite, mv plan output is more than
query output, so should discard
+ // the expression not in slot mapping
+ Map<SlotReference, SlotReference> slotReferenceMap =
slotMapping.toSlotReferenceMap();
+ Expression replacedExpr =
ExpressionUtils.replaceNullAware(entry.getKey(), slotReferenceMap);
+ if (replacedExpr == null) {
+ continue;
+ }
permutedExpressionMapping.putAll(replacedExpr, entry.getValue());
}
return new ExpressionMapping(permutedExpressionMapping);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/mapping/SlotMapping.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/mapping/SlotMapping.java
index 6e481a10142..68aa6aba4a1 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/mapping/SlotMapping.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/mapping/SlotMapping.java
@@ -87,7 +87,7 @@ public class SlotMapping extends Mapping {
// payload['issue']['number']#20 ->
element_at(element_at(payload#10, 'issue'), 'number') mapping
// in expression rewrite
// 2
- // Maybe table add column after last refresh
+ // Maybe table added column after last refresh
LOG.warn(String.format("SlotMapping generate is null,
source relation is %s, "
+ "target relation is %s", sourceRelation,
targetRelation));
continue;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/ExpressionLineageReplacer.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/ExpressionLineageReplacer.java
index 51e2308cbbf..346e748f390 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/ExpressionLineageReplacer.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/ExpressionLineageReplacer.java
@@ -84,6 +84,13 @@ public class ExpressionLineageReplacer extends
DefaultPlanVisitor<Expression, Ex
public Expression visitNamedExpression(NamedExpression namedExpression,
Map<ExprId, Expression> exprIdExpressionMap) {
Expression childExpr =
exprIdExpressionMap.get(namedExpression.getExprId());
+ // avoid loop when non_nullable(o_orderkey#0) AS `o_orderkey`#0
after join eliminate when
+ // inner join
+ if (childExpr != null && !childExpr.children().isEmpty()
+ && childExpr.child(0) instanceof NamedExpression
+ && ((NamedExpression)
childExpr.child(0)).getExprId().equals(namedExpression.getExprId())) {
+ return namedExpression;
+ }
if (childExpr != null) {
// remove alias
return visit(childExpr, exprIdExpressionMap);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/ExpressionUtils.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/ExpressionUtils.java
index cd04627ee30..34afc3652dc 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/ExpressionUtils.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/ExpressionUtils.java
@@ -70,6 +70,7 @@ import
org.apache.doris.nereids.trees.plans.logical.LogicalEmptyRelation;
import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
import org.apache.doris.nereids.trees.plans.visitor.ExpressionLineageReplacer;
import org.apache.doris.nereids.types.BooleanType;
+import org.apache.doris.nereids.types.VariantType;
import org.apache.doris.nereids.types.coercion.NumericType;
import org.apache.doris.qe.ConnectContext;
@@ -540,6 +541,32 @@ public class ExpressionUtils {
return result.build();
}
+ /**
+ * replaceNullAware, if could not be replaced by map, the return null
+ */
+ public static Expression replaceNullAware(Expression expr,
+ Map<? extends Expression, ? extends Expression> replaceMap) {
+ Set<Boolean> containNull = new HashSet<>();
+ Expression finalReplacedExpr = expr.rewriteDownShortCircuit(e -> {
+ if (!containNull.isEmpty()) {
+ return e;
+ }
+ Expression replacedExpr = replaceMap.get(e);
+ if (replacedExpr == null && e instanceof SlotReference
+ && e.getDataType() instanceof VariantType) {
+ // this is valid, because the variant expression would be
extended in expression rewrite
+ return e;
+ }
+ if (replacedExpr == null && e instanceof NamedExpression) {
+ // if replace named expression failed, return null directly
+ containNull.add(true);
+ return e;
+ }
+ return replacedExpr == null ? e : replacedExpr;
+ });
+ return containNull.isEmpty() ? finalReplacedExpr : null;
+ }
+
/**
* Replace expression node in the expression tree by `replaceMap` in
top-down manner.
*/
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/exploration/mv/BuildStructInfoTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/exploration/mv/BuildStructInfoTest.java
index c1168224829..bfecdbd4181 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/exploration/mv/BuildStructInfoTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/exploration/mv/BuildStructInfoTest.java
@@ -18,14 +18,17 @@
package org.apache.doris.nereids.rules.exploration.mv;
import org.apache.doris.nereids.jobs.joinorder.hypergraph.HyperGraph;
+import
org.apache.doris.nereids.rules.exploration.mv.StructInfo.PlanCheckContext;
import org.apache.doris.nereids.sqltest.SqlTestBase;
import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.util.PlanChecker;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
class BuildStructInfoTest extends SqlTestBase {
+
@Test
void testSimpleSQL() {
connectContext.getSessionVariable().setDisableNereidsRules("PRUNE_EMPTY_PARTITION");
@@ -97,4 +100,108 @@ class BuildStructInfoTest extends SqlTestBase {
return true;
}));
}
+
+ @Test
+ void testPlanCheckerWithJoin() {
+ // Should not make scan to empty relation when the table used by
materialized view has no data
+ connectContext.getSessionVariable().setDisableNereidsRules(
+ "OLAP_SCAN_PARTITION_PRUNE"
+ + ",PRUNE_EMPTY_PARTITION"
+ + ",ELIMINATE_GROUP_BY_KEY_BY_UNIFORM"
+ + ",ELIMINATE_CONST_JOIN_CONDITION"
+ + ",CONSTANT_PROPAGATION"
+ );
+ PlanChecker.from(connectContext)
+ .checkExplain("select * from "
+ + "(select * from lineitem "
+ + "where l_shipdate >= \"2023-12-01\" and
l_shipdate <= \"2023-12-03\") t1 "
+ + "left join "
+ + "(select * from orders where o_orderdate >=
\"2023-12-01\" and o_orderdate <= \"2023-12-03\" ) t2 "
+ + "on t1.l_orderkey = o_orderkey;",
+ nereidsPlanner -> {
+ Plan rewrittenPlan =
nereidsPlanner.getRewrittenPlan();
+ PlanCheckContext checkContext =
PlanCheckContext.of(
+
AbstractMaterializedViewRule.SUPPORTED_JOIN_TYPE_SET);
+ Boolean result =
rewrittenPlan.child(0).accept(StructInfo.PLAN_PATTERN_CHECKER, checkContext);
+ Assertions.assertTrue(result);
+
Assertions.assertFalse(checkContext.isContainsTopAggregate());
+ });
+ }
+
+ @Test
+ void testPlanCheckerWithAggregate() {
+ // Should not make scan to empty relation when the table used by
materialized view has no data
+ connectContext.getSessionVariable().setDisableNereidsRules(
+ "OLAP_SCAN_PARTITION_PRUNE"
+ + ",PRUNE_EMPTY_PARTITION"
+ + ",ELIMINATE_GROUP_BY_KEY_BY_UNIFORM"
+ + ",ELIMINATE_CONST_JOIN_CONDITION"
+ + ",CONSTANT_PROPAGATION"
+ );
+ PlanChecker.from(connectContext)
+ .checkExplain("SELECT l.L_SHIPDATE AS ship_data_alias,
o.O_ORDERDATE, count(*) "
+ + "FROM "
+ + "lineitem as l "
+ + "LEFT JOIN "
+ + "(SELECT abs(O_TOTALPRICE + 10) as c1_abs,
O_CUSTKEY, O_ORDERDATE, O_ORDERKEY "
+ + "FROM orders) as o "
+ + "ON l.L_ORDERKEY = o.O_ORDERKEY "
+ + "JOIN "
+ + "(SELECT abs(sqrt(PS_SUPPLYCOST)) as c2_abs,
PS_AVAILQTY, PS_PARTKEY, PS_SUPPKEY "
+ + "FROM partsupp) as ps "
+ + "ON l.L_PARTKEY = ps.PS_PARTKEY and
l.L_SUPPKEY = ps.PS_SUPPKEY "
+ + "GROUP BY l.L_SHIPDATE, o.O_ORDERDATE ",
+ nereidsPlanner -> {
+ Plan rewrittenPlan =
nereidsPlanner.getRewrittenPlan();
+ PlanCheckContext checkContext =
PlanCheckContext.of(
+
AbstractMaterializedViewRule.SUPPORTED_JOIN_TYPE_SET);
+ Boolean result =
rewrittenPlan.child(0).accept(StructInfo.PLAN_PATTERN_CHECKER, checkContext);
+ Assertions.assertTrue(result);
+
Assertions.assertTrue(checkContext.isContainsTopAggregate());
+ });
+ }
+
+ @Test
+ void testPlanCheckerScanAggregate() {
+ // Should not make scan to empty relation when the table used by
materialized view has no data
+ connectContext.getSessionVariable().setDisableNereidsRules(
+ "OLAP_SCAN_PARTITION_PRUNE"
+ + ",PRUNE_EMPTY_PARTITION"
+ + ",ELIMINATE_GROUP_BY_KEY_BY_UNIFORM"
+ + ",ELIMINATE_CONST_JOIN_CONDITION"
+ + ",CONSTANT_PROPAGATION"
+ );
+ PlanChecker.from(connectContext)
+ .checkExplain("select l.L_SHIPDATE, count(*) from lineitem l "
+ + "GROUP BY l.L_SHIPDATE",
+ nereidsPlanner -> {
+ Plan rewrittenPlan =
nereidsPlanner.getRewrittenPlan();
+ PlanCheckContext checkContext =
PlanCheckContext.of(
+
AbstractMaterializedViewRule.SUPPORTED_JOIN_TYPE_SET);
+ Boolean result =
rewrittenPlan.child(0).accept(StructInfo.SCAN_PLAN_PATTERN_CHECKER,
checkContext);
+ Assertions.assertFalse(result);
+ });
+ }
+
+ @Test
+ void testPlanCheckerOnlyScan() {
+ // Should not make scan to empty relation when the table used by
materialized view has no data
+ connectContext.getSessionVariable().setDisableNereidsRules(
+ "OLAP_SCAN_PARTITION_PRUNE"
+ + ",PRUNE_EMPTY_PARTITION"
+ + ",ELIMINATE_GROUP_BY_KEY_BY_UNIFORM"
+ + ",ELIMINATE_CONST_JOIN_CONDITION"
+ + ",CONSTANT_PROPAGATION"
+ );
+ PlanChecker.from(connectContext)
+ .checkExplain("select l.L_SHIPDATE from lineitem l ",
+ nereidsPlanner -> {
+ Plan rewrittenPlan =
nereidsPlanner.getRewrittenPlan();
+ PlanCheckContext checkContext =
PlanCheckContext.of(
+
AbstractMaterializedViewRule.SUPPORTED_JOIN_TYPE_SET);
+ Boolean result =
rewrittenPlan.child(0).accept(StructInfo.SCAN_PLAN_PATTERN_CHECKER,
checkContext);
+ Assertions.assertTrue(result);
+
Assertions.assertFalse(checkContext.isContainsTopAggregate());
+ });
+ }
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/exploration/mv/HyperGraphComparatorTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/exploration/mv/HyperGraphComparatorTest.java
index 884d9a15d81..db02e45dd48 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/exploration/mv/HyperGraphComparatorTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/exploration/mv/HyperGraphComparatorTest.java
@@ -25,7 +25,6 @@ import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.util.PlanChecker;
import org.junit.jupiter.api.Assertions;
-import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Test;
class HyperGraphComparatorTest extends SqlTestBase {
@@ -61,7 +60,7 @@ class HyperGraphComparatorTest extends SqlTestBase {
HyperGraph h1 = HyperGraph.builderForMv(p1).build();
HyperGraph h2 = HyperGraph.builderForMv(p2).build();
ComparisonResult res = HyperGraphComparator.isLogicCompatible(h1, h2,
constructContext(p1, p2, c1));
- Assertions.assertTrue(!res.isInvalid());
+ Assertions.assertFalse(res.isInvalid());
Assertions.assertEquals(2, res.getViewNoNullableSlot().size());
}
@@ -91,7 +90,7 @@ class HyperGraphComparatorTest extends SqlTestBase {
HyperGraph h1 = HyperGraph.builderForMv(p1).build();
HyperGraph h2 = HyperGraph.builderForMv(p2).build();
ComparisonResult res = HyperGraphComparator.isLogicCompatible(h1, h2,
constructContext(p1, p2, c1));
- Assertions.assertTrue(!res.isInvalid());
+ Assertions.assertFalse(res.isInvalid());
Assertions.assertEquals(2, res.getViewNoNullableSlot().size());
}
@@ -122,11 +121,10 @@ class HyperGraphComparatorTest extends SqlTestBase {
HyperGraph h1 = HyperGraph.builderForMv(p1).build();
HyperGraph h2 = HyperGraph.builderForMv(p2).build();
ComparisonResult res = HyperGraphComparator.isLogicCompatible(h1, h2,
constructContext(p1, p2, c1));
- Assertions.assertTrue(!res.isInvalid());
+ Assertions.assertFalse(res.isInvalid());
Assertions.assertEquals(2, res.getViewNoNullableSlot().size());
}
- @Disabled
@Test
void testIJAndLojAssocWithJoinCond() {
CascadesContext c1 = createCascadesContext(
@@ -140,12 +138,9 @@ class HyperGraphComparatorTest extends SqlTestBase {
.rewrite()
.getPlan().child(0);
CascadesContext c2 = createCascadesContext(
- "select * from T1 left outer join "
- + "("
- + "select T1.* from T1 left outer join T3 "
- + "on T1.id = T3.id and T1.score = T3.score "
- + ") T2 "
- + "on T1.id = T2.id ",
+ "select * from T1 left join T2 "
+ + "on T1.id = T2.id "
+ + "left join T3 on T1.id = T3.id",
connectContext
);
Plan p2 = PlanChecker.from(c2)
@@ -156,7 +151,35 @@ class HyperGraphComparatorTest extends SqlTestBase {
HyperGraph h1 = HyperGraph.builderForMv(p1).build();
HyperGraph h2 = HyperGraph.builderForMv(p2).build();
ComparisonResult res = HyperGraphComparator.isLogicCompatible(h1, h2,
constructContext(p1, p2, c1));
- Assertions.assertTrue(!res.isInvalid());
+ Assertions.assertFalse(res.isInvalid());
Assertions.assertEquals(2, res.getViewNoNullableSlot().size());
}
+
+ @Test
+ void testJoinEliminateShouldFail() {
+ CascadesContext c1 = createCascadesContext(
+ "select * from T1 inner join T2 "
+ + "on T1.id = T2.id",
+ connectContext
+ );
+ Plan p1 = PlanChecker.from(c1)
+ .analyze()
+ .rewrite()
+ .getPlan().child(0);
+ CascadesContext c2 = createCascadesContext(
+ "select * from T1 inner join T2 "
+ + "on T1.id = T2.id "
+ + "inner join T3 on T1.id = T3.id",
+ connectContext
+ );
+ Plan p2 = PlanChecker.from(c2)
+ .analyze()
+ .rewrite()
+ .applyExploration(RuleSet.BUSHY_TREE_JOIN_REORDER)
+ .getAllPlan().get(0).child(0);
+ HyperGraph h1 = HyperGraph.builderForMv(p1).build();
+ HyperGraph h2 = HyperGraph.builderForMv(p2).build();
+ ComparisonResult res = HyperGraphComparator.isLogicCompatible(h1, h2,
constructContext(p1, p2, c1));
+ Assertions.assertTrue(res.isInvalid());
+ }
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/util/ExpressionUtilsTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/util/ExpressionUtilsTest.java
index 81611252ff9..1e952255891 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/util/ExpressionUtilsTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/util/ExpressionUtilsTest.java
@@ -17,22 +17,32 @@
package org.apache.doris.nereids.util;
+import org.apache.doris.catalog.OlapTable;
+import org.apache.doris.catalog.TableTest;
import org.apache.doris.nereids.parser.NereidsParser;
+import org.apache.doris.nereids.trees.expressions.Alias;
import org.apache.doris.nereids.trees.expressions.And;
import org.apache.doris.nereids.trees.expressions.EqualTo;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.NonNullable;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.RelationId;
+import org.apache.doris.nereids.trees.plans.logical.LogicalOdbcScan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
import org.apache.doris.nereids.types.IntegerType;
import org.apache.doris.utframe.TestWithFeService;
+import com.google.common.collect.ImmutableList;
import com.google.common.collect.Maps;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import java.util.Arrays;
+import java.util.BitSet;
+import java.util.HashMap;
import java.util.List;
import java.util.Map;
@@ -207,6 +217,42 @@ public class ExpressionUtilsTest extends TestWithFeService
{
Assertions.assertEquals(expectUniformSlots,
ExpressionUtils.extractUniformSlot(expression));
}
+ @Test
+ public void testSlotInputEqualsOutput() {
+ OlapTable olapTable = TableTest.newOlapTable(10000, "test", 0);
+ Slot a = new SlotReference("id", IntegerType.INSTANCE);
+ Slot b = new SlotReference("id", IntegerType.INSTANCE);
+ Alias bAlias = new Alias(b.getExprId(), new NonNullable(b));
+ LogicalProject<LogicalOdbcScan> project = new
LogicalProject<>(ImmutableList.of(a, bAlias),
+ new LogicalOdbcScan(new RelationId(0), olapTable,
ImmutableList.of("test")));
+ List<? extends Expression> expressions =
ExpressionUtils.shuttleExpressionWithLineage(project.getOutput(),
+ project, new BitSet());
+ // should not loop, should break out loop
+ Assertions.assertEquals(expressions, ImmutableList.of(a,
bAlias.toSlot()));
+ }
+
+ @Test
+ public void testReplaceNullAware() {
+ Slot a = new SlotReference("id1", IntegerType.INSTANCE);
+ Slot b = new SlotReference("id2", IntegerType.INSTANCE);
+
+ Map<Expression, Expression> replaceMap = new HashMap<>();
+ replaceMap.put(a, b);
+ Expression replacedExpression = ExpressionUtils.replaceNullAware(a,
replaceMap);
+ Assertions.assertEquals(replacedExpression, b);
+
+ replaceMap = new HashMap<>();
+ Slot a2 = new SlotReference("id3", IntegerType.INSTANCE);
+ replaceMap.put(a2, b);
+ Expression replacedExpression1 = ExpressionUtils.replaceNullAware(a,
replaceMap);
+ // should return null
+ Assertions.assertNull(replacedExpression1);
+
+ Expression replacedExpression2 = ExpressionUtils.replace(a,
replaceMap);
+ // should return a
+ Assertions.assertEquals(a, replacedExpression2);
+ }
+
private void assertExpect(List<? extends Expression> originalExpressions,
List<? extends Expression> shuttledExpressions,
String... expectExpressions) {
diff --git
a/regression-test/suites/nereids_rules_p0/mv/join_elim_p_f_key/join_elim_line_pattern.groovy
b/regression-test/suites/nereids_rules_p0/mv/join_elim_p_f_key/join_elim_line_pattern.groovy
new file mode 100644
index 00000000000..9e1618d18dd
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/join_elim_p_f_key/join_elim_line_pattern.groovy
@@ -0,0 +1,558 @@
+package mv.join_elim_p_f_key
+// 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.
+
+suite("join_elim_line_pattern") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+ sql "SET enable_materialized_view_rewrite=true"
+ sql "SET enable_nereids_timeout = false"
+
+ sql """
+ drop table if exists orders
+ """
+
+ sql """CREATE TABLE `orders` (
+ `o_orderkey` BIGINT NULL,
+ `o_partkey` INT NULL,
+ `o_suppkey` INT NULL,
+ `o_custkey` INT NULL,
+ `o_orderstatus` VARCHAR(1) NULL,
+ `o_totalprice` DECIMAL(15, 2) NULL,
+ `o_orderpriority` VARCHAR(15) NULL,
+ `o_clerk` VARCHAR(15) NULL,
+ `o_shippriority` INT NULL,
+ `o_comment` VARCHAR(79) NULL,
+ `o_orderdate` DATE not NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`o_orderkey`)
+ COMMENT 'OLAP'
+ auto partition by range (date_trunc(`o_orderdate`, 'day')) ()
+ DISTRIBUTED BY HASH(`o_orderkey`) BUCKETS 96
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql """
+ drop table if exists lineitem
+ """
+
+ sql """CREATE TABLE `lineitem` (
+ `l_orderkey` BIGINT NOT NULL,
+ `l_linenumber` INT NULL,
+ `l_partkey` INT NOT NULL,
+ `l_suppkey` INT NOT NULL,
+ `l_quantity` DECIMAL(15, 2) NULL,
+ `l_extendedprice` DECIMAL(15, 2) NULL,
+ `l_discount` DECIMAL(15, 2) NULL,
+ `l_tax` DECIMAL(15, 2) NULL,
+ `l_returnflag` VARCHAR(1) NULL,
+ `l_linestatus` VARCHAR(1) NULL,
+ `l_commitdate` DATE NULL,
+ `l_receiptdate` DATE NULL,
+ `l_shipinstruct` VARCHAR(25) NULL,
+ `l_shipmode` VARCHAR(10) NULL,
+ `l_comment` VARCHAR(44) NULL,
+ `l_shipdate` DATE not NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(l_orderkey)
+ COMMENT 'OLAP'
+ auto partition by range (date_trunc(`l_shipdate`, 'day')) ()
+ DISTRIBUTED BY HASH(`l_orderkey`) BUCKETS 96
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql """
+ drop table if exists partsupp
+ """
+
+ sql """CREATE TABLE `partsupp` (
+ `ps_partkey` INT NULL,
+ `ps_suppkey` INT NULL,
+ `ps_availqty` INT NULL,
+ `ps_supplycost` DECIMAL(15, 2) NULL,
+ `ps_comment` VARCHAR(199) NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`ps_partkey`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`ps_partkey`) BUCKETS 24
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+
+ sql """
+ INSERT INTO orders (
+ o_orderkey, o_partkey, o_suppkey, o_custkey, o_orderstatus,
+ o_totalprice, o_orderpriority, o_clerk, o_shippriority, o_comment,
o_orderdate
+ ) VALUES
+ (1001, 501, 201, 3001, 'O', 1500.00, '1-URGENT', 'Clerk#001', 0, '紧急订单',
'2024-01-15'),
+ (1001, 501, 201, 3001, 'O', 1500.00, '1-URGENT', 'Clerk#001', 0, '紧急订单',
'2024-01-15'),
+ (1002, 502, 202, 3002, 'F', 2500.00, '2-HIGH', 'Clerk#002', 1, '普通订单',
'2024-02-20'),
+ (1002, 502, 202, 3002, 'F', 2500.00, '2-HIGH', 'Clerk#002', 1, '普通订单',
'2024-02-20'),
+ (1003, 503, 203, 3003, 'O', 1800.00, '3-MEDIUM', 'Clerk#003', 0, '中等优先级',
'2024-03-05'),
+ (1003, 503, 203, 3003, 'O', 1800.00, '3-MEDIUM', 'Clerk#003', 0, '中等优先级',
'2024-03-05'),
+ (1004, 504, 204, 3004, 'F', 3200.00, '4-NOT SPEC', 'Clerk#004', 1, '大额订单',
'2024-03-15'),
+ (1004, 504, 204, 3004, 'F', 3200.00, '4-NOT SPEC', 'Clerk#004', 1, '大额订单',
'2024-03-15'),
+ (1005, 505, 205, 3005, 'O', 950.00, '5-LOW', 'Clerk#005', 0, '小额订单',
'2024-04-01'),
+ (1005, 505, 205, 3005, 'O', 950.00, '5-LOW', 'Clerk#005', 0, '小额订单',
'2024-04-01');
+
+ INSERT INTO lineitem (
+ l_orderkey, l_linenumber, l_partkey, l_suppkey, l_quantity,
+ l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus,
+ l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment,
l_shipdate
+ ) VALUES
+ -- 订单1001的明细
+ (1001, 1, 501, 201, 5.00, 500.00, 0.05, 0.10, 'N', 'O', '2024-01-16',
'2024-01-20', 'DELIVER IN PERSON', 'TRUCK', '订单1001第一行', '2024-01-18'),
+ (1002, 1, 502, 202, 8.00, 800.00, 0.08, 0.12, 'N', 'O', '2024-02-21',
'2024-02-25', 'TAKE BACK RETURN', 'MAIL', '订单1002第一行', '2024-02-22'),
+ (1003, 1, 503, 203, 6.00, 600.00, 0.06, 0.12, 'N', 'O', '2024-03-06',
'2024-03-10', 'DELIVER IN PERSON', 'TRUCK', '订单1003第一行', '2024-03-08'),
+ (1004, 1, 504, 204, 10.00, 1000.00, 0.10, 0.18, 'N', 'O', '2024-03-16',
'2024-03-20', 'TAKE BACK RETURN', 'MAIL', '订单1004第一行', '2024-03-18'),
+ (1005, 1, 505, 205, 4.00, 400.00, 0.04, 0.08, 'N', 'O', '2024-04-02',
'2024-04-06', 'DELIVER IN PERSON', 'TRUCK', '订单1005第一行', '2024-04-04');
+
+
+ INSERT INTO partsupp (
+ ps_partkey, ps_suppkey, ps_availqty, ps_supplycost, ps_comment
+ ) VALUES
+ (501, 201, 100, 50.00, '零件501供应商201'),
+ (502, 202, 200, 75.00, '零件502供应商202'),
+ (503, 203, 150, 60.00, '零件503供应商203'),
+ (504, 204, 180, 65.00, '零件504供应商204'),
+ (505, 205, 120, 55.00, '零件505供应商205');
+ """
+
+ sql """analyze table lineitem with sync;"""
+ sql """analyze table orders with sync;"""
+ sql """analyze table partsupp with sync;"""
+ sql """alter table lineitem modify column l_comment set stats
('row_count'='10');"""
+ sql """alter table orders modify column o_comment set stats
('row_count'='5');"""
+ sql """alter table partsupp modify column ps_comment set stats
('row_count'='5');"""
+
+ def compare_res = { def stmt, int orderByColumns = 1 ->
+ sql "SET enable_materialized_view_rewrite=false"
+ def orderStmt = " order by " + (1..orderByColumns).join(", ")
+ def origin_res = sql stmt + orderStmt
+ logger.info("origin_res: " + origin_res)
+ sql "SET enable_materialized_view_rewrite=true"
+ def mv_origin_res = sql stmt + orderStmt
+ logger.info("mv_origin_res: " + mv_origin_res)
+ assertTrue((mv_origin_res == [] && origin_res == []) ||
(mv_origin_res.size() == origin_res.size()))
+ for (int row = 0; row < mv_origin_res.size(); row++) {
+ assertTrue(mv_origin_res[row].size() == origin_res[row].size())
+ for (int col = 0; col < mv_origin_res[row].size(); col++) {
+ assertTrue(mv_origin_res[row][col] == origin_res[row][col])
+ }
+ }
+ }
+
+ def dropAllConstraints = { def tableName ->
+ def getConstraintsQuery = "SHOW CONSTRAINTS FROM ${tableName}"
+ def constraints = sql getConstraintsQuery
+ logger.info("needed deleted constraints : ${constraints}")
+ constraints.each { constraint ->
+ def constraintName = constraint[0]
+ def dropConstraintSQL = "ALTER TABLE ${tableName} DROP CONSTRAINT
${constraintName}"
+ sql dropConstraintSQL
+ logger.info("delete ${tableName} constraits : ${constraintName}")
+ }
+ }
+
+ // inner join
+ def mv_stmt_1 = """
+ select o_orderkey, o_partkey, o_suppkey, o_custkey, o_comment,
+ l_partkey, l_suppkey, l_orderkey, l_commitdate, l_comment,
+ ps_suppkey, ps_partkey, ps_availqty, ps_supplycost, ps_comment
+ from orders o
+ inner join lineitem l on o_orderkey = l_orderkey
+ inner join partsupp p on l_partkey = ps_partkey and l_suppkey =
ps_suppkey
+ """
+ // outer join
+ def mv_stmt_2 = """
+ select o_orderkey, o_partkey, o_suppkey, o_custkey, o_comment,
+ l_partkey, l_suppkey, l_orderkey, l_commitdate, l_comment,
+ ps_suppkey, ps_partkey, ps_availqty, ps_supplycost, ps_comment
+ from orders o
+ left join lineitem l on o_orderkey = l_orderkey
+ left join partsupp p on l_partkey = ps_partkey and l_suppkey =
ps_suppkey
+ """
+
+ // single table
+ def query_1 = """select o_orderkey, o_partkey, o_suppkey, o_custkey,
o_comment from orders"""
+
+ // orders join lineitem, use both
+ def query_2 = """select o_orderkey, l_partkey, l_suppkey, l_orderkey,
l_commitdate from orders o join lineitem l on o_orderkey = l_orderkey"""
+ def query_3 = """select o_orderkey, l_partkey, l_suppkey, l_orderkey,
l_commitdate from orders o left join lineitem l on o_orderkey = l_orderkey"""
+
+ // orders join lineitem, use left
+ def query_4 = """select o_orderkey, o_partkey, o_suppkey, o_custkey,
o_comment from orders o join lineitem l on o_orderkey = l_orderkey"""
+ def query_5 = """select o_orderkey, o_partkey, o_suppkey, o_custkey,
o_comment from orders o left join lineitem l on o_orderkey = l_orderkey"""
+
+ // orders join lineitem, use right
+ def query_6 = """select l_partkey, l_suppkey, l_orderkey, l_commitdate,
l_comment from orders o join lineitem l on o_orderkey = l_orderkey"""
+ def query_7 = """select l_partkey, l_suppkey, l_orderkey, l_commitdate,
l_comment from orders o left join lineitem l on o_orderkey = l_orderkey"""
+
+ // lineitem join partsupp, use both
+ def query_8 = """select l_orderkey, l_linenumber, l_partkey, ps_suppkey,
ps_partkey from lineitem l join partsupp p on l_partkey = ps_partkey and
l_suppkey = ps_suppkey"""
+ def query_9 = """select l_orderkey, l_linenumber, l_partkey, ps_suppkey,
ps_partkey from lineitem l left join partsupp p on l_partkey = ps_partkey and
l_suppkey = ps_suppkey"""
+
+ // lineitem join partsupp, use left
+ def query_10 = """select l_partkey, l_suppkey, l_orderkey, l_commitdate,
l_comment from lineitem l join partsupp p on l_partkey = ps_partkey and
l_suppkey = ps_suppkey"""
+ def query_11 = """select l_partkey, l_suppkey, l_orderkey, l_commitdate,
l_comment from lineitem l left join partsupp p on l_partkey = ps_partkey and
l_suppkey = ps_suppkey"""
+
+ // lineitem join partsupp, use right
+ def query_12 = """select ps_suppkey, ps_partkey, ps_availqty,
ps_supplycost, ps_comment from lineitem l join partsupp p on l_partkey =
ps_partkey and l_suppkey = ps_suppkey"""
+ def query_13 = """select ps_suppkey, ps_partkey, ps_availqty,
ps_supplycost, ps_comment from lineitem l left join partsupp p on l_partkey =
ps_partkey and l_suppkey = ps_suppkey"""
+
+
+ def query_list = [query_1, query_2, query_3, query_4, query_5, query_6,
query_7, query_8, query_9,
+ query_10, query_11, query_12, query_13]
+
+ dropAllConstraints("orders")
+ dropAllConstraints("lineitem")
+ dropAllConstraints("partsupp")
+ // orders -> lineitem (fk-pk)
+ // lineitem -> partsupp (fk-pk)
+ sql """alter table partsupp add constraint pk primary key(ps_partkey,
ps_suppkey)"""
+ sql """alter table lineitem add constraint fk1 foreign key (l_partkey,
l_suppkey) references partsupp(ps_partkey, ps_suppkey)"""
+ sql """alter table lineitem add constraint pk primary key(l_orderkey)"""
+ sql """alter table orders add constraint fk foreign key (o_orderkey)
references lineitem(l_orderkey)"""
+
+ def mv_name = "join_elim_line_pattern"
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in [1, 2, 3, 4, 5, 6, 7]) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ }
+ else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in [1, 2, 3, 4, 5, 6, 7]) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders")
+ dropAllConstraints("lineitem")
+ dropAllConstraints("partsupp")
+ // orders -> lineitem (fk-pk)
+ sql """alter table lineitem add constraint pk primary key(l_orderkey)"""
+ sql """alter table orders add constraint fk foreign key (o_orderkey)
references lineitem(l_orderkey)"""
+
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in []) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ }
+ else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in []) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders")
+ dropAllConstraints("lineitem")
+ dropAllConstraints("partsupp")
+ // lineitem -> partsupp (fk-pk)
+ sql """alter table partsupp add constraint pk primary key(ps_partkey,
ps_suppkey)"""
+ sql """alter table lineitem add constraint fk1 foreign key (l_partkey,
l_suppkey) references partsupp(ps_partkey, ps_suppkey)"""
+
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in [2, 4, 6]) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ }
+ else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in [3, 5, 7]) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders")
+ dropAllConstraints("lineitem")
+ dropAllConstraints("partsupp")
+ // orders -> lineitem (u)
+ // lineitem -> partsupp (fk-pk)
+ sql """alter table partsupp add constraint pk primary key (ps_partkey,
ps_suppkey)"""
+ sql """alter table lineitem add constraint fk1 foreign key (l_partkey,
l_suppkey) references partsupp(ps_partkey, ps_suppkey)"""
+ sql """alter table lineitem add constraint uk unique (l_orderkey)"""
+
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in [2, 4, 6]) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ }
+ else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in [1, 2, 3, 4, 5, 6, 7]) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders")
+ dropAllConstraints("lineitem")
+ dropAllConstraints("partsupp")
+ // orders -> lineitem (fk-pk)
+ // lineitem -> partsupp (u)
+ sql """alter table partsupp add constraint pk unique (ps_partkey,
ps_suppkey)"""
+ sql """alter table lineitem add constraint pk primary key(l_orderkey)"""
+ sql """alter table orders add constraint fk foreign key (o_orderkey)
references lineitem(l_orderkey)"""
+
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in []) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ }
+ else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in [1, 2, 3, 4, 5, 6, 7]) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders")
+ dropAllConstraints("lineitem")
+ dropAllConstraints("partsupp")
+ // orders -> lineitem (u)
+ // lineitem -> partsupp (u)
+ sql """alter table partsupp add constraint pk unique (ps_partkey,
ps_suppkey)"""
+ sql """alter table lineitem add constraint pk unique (l_orderkey)"""
+
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in []) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ }
+ else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in [1, 2, 3, 4, 5, 6, 7]) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders")
+ dropAllConstraints("lineitem")
+ dropAllConstraints("partsupp")
+ // orders -> lineitem (u)
+ sql """alter table lineitem add constraint pk unique (l_orderkey)"""
+
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in []) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ }
+ else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in []) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders")
+ dropAllConstraints("lineitem")
+ dropAllConstraints("partsupp")
+ // lineitem -> partsupp (u)
+ sql """alter table partsupp add constraint pk unique (ps_partkey,
ps_suppkey)"""
+
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in []) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ }
+ else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in [3, 5, 7]) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders")
+ dropAllConstraints("lineitem")
+ dropAllConstraints("partsupp")
+ // without constraints
+
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in []) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ }
+ else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in []) {
+ mv_rewrite_success_without_check_chosen(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 5)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders")
+ dropAllConstraints("lineitem")
+ dropAllConstraints("partsupp")
+ sql """alter table partsupp add constraint pk unique (ps_partkey,
ps_suppkey)"""
+ // negative examples orders
+ async_mv_rewrite_fail(db,
+ """
+ select l_shipdate, l_linenumber, o_orderkey, ps_suppkey from
orders
+ left join lineitem
+ on o_orderkey = l_orderkey
+ left join partsupp
+ on ps_partkey = l_partkey or ps_suppkey = l_suppkey
+ """,
+ """
+ select l_shipdate, l_linenumber, o_orderkey
+ from lineitem
+ left join orders on o_orderkey = l_orderkey
+ """,
+ "${mv_name}_neg_2")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}_neg_1;"""
+
+ dropAllConstraints("orders")
+ dropAllConstraints("lineitem")
+ dropAllConstraints("partsupp")
+
+ sql """
+ drop table if exists orders;
+ drop table if exists lineitem;
+ drop table if exists partsupp;
+ """
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/join_elim_p_f_key/join_elim_star_pattern.groovy
b/regression-test/suites/nereids_rules_p0/mv/join_elim_p_f_key/join_elim_star_pattern.groovy
new file mode 100644
index 00000000000..d8850796a3e
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/join_elim_p_f_key/join_elim_star_pattern.groovy
@@ -0,0 +1,592 @@
+package mv.join_elim_p_f_key
+// 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.
+
+suite("join_elim_star_pattern") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+ sql "SET enable_materialized_view_rewrite=true"
+ sql "SET enable_nereids_timeout = false"
+
+ sql """
+ drop table if exists orders_1
+ """
+
+ sql """CREATE TABLE `orders_1` (
+ `o_orderkey` BIGINT NOT NULL,
+ `o_partkey` INT NOT NULL,
+ `o_suppkey` INT NOT NULL,
+ `o_custkey` INT NULL,
+ `o_orderstatus` VARCHAR(1) NULL,
+ `o_totalprice` DECIMAL(15, 2) NULL,
+ `o_orderpriority` VARCHAR(15) NULL,
+ `o_clerk` VARCHAR(15) NULL,
+ `o_shippriority` INT NULL,
+ `o_comment` VARCHAR(79) NULL,
+ `o_orderdate` DATE not NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`o_orderkey`)
+ COMMENT 'OLAP'
+ auto partition by range (date_trunc(`o_orderdate`, 'day')) ()
+ DISTRIBUTED BY HASH(`o_orderkey`) BUCKETS 96
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql """
+ drop table if exists lineitem_1
+ """
+
+ sql """CREATE TABLE `lineitem_1` (
+ `l_orderkey` BIGINT NOT NULL,
+ `l_linenumber` INT NULL,
+ `l_partkey` INT NOT NULL,
+ `l_suppkey` INT NOT NULL,
+ `l_quantity` DECIMAL(15, 2) NULL,
+ `l_extendedprice` DECIMAL(15, 2) NULL,
+ `l_discount` DECIMAL(15, 2) NULL,
+ `l_tax` DECIMAL(15, 2) NULL,
+ `l_returnflag` VARCHAR(1) NULL,
+ `l_linestatus` VARCHAR(1) NULL,
+ `l_commitdate` DATE NULL,
+ `l_receiptdate` DATE NULL,
+ `l_shipinstruct` VARCHAR(25) NULL,
+ `l_shipmode` VARCHAR(10) NULL,
+ `l_comment` VARCHAR(44) NULL,
+ `l_shipdate` DATE not NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(l_orderkey)
+ COMMENT 'OLAP'
+ auto partition by range (date_trunc(`l_shipdate`, 'day')) ()
+ DISTRIBUTED BY HASH(`l_orderkey`) BUCKETS 96
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql """
+ drop table if exists partsupp_1
+ """
+
+ sql """CREATE TABLE `partsupp_1` (
+ `ps_partkey` INT NOT NULL,
+ `ps_suppkey` INT NOT NULL,
+ `ps_availqty` INT NULL,
+ `ps_supplycost` DECIMAL(15, 2) NULL,
+ `ps_comment` VARCHAR(199) NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`ps_partkey`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`ps_partkey`) BUCKETS 24
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+
+ sql """
+ INSERT INTO orders_1 (
+ o_orderkey, o_partkey, o_suppkey, o_custkey, o_orderstatus,
+ o_totalprice, o_orderpriority, o_clerk, o_shippriority, o_comment,
o_orderdate
+ ) VALUES
+ (1001, 501, 201, 3001, 'O', 1500.00, '1-URGENT', 'Clerk#001', 0, '紧急订单',
'2024-01-15'),
+ (1002, 502, 202, 3002, 'F', 2500.00, '2-HIGH', 'Clerk#002', 1, '普通订单',
'2024-02-20'),
+ (1003, 503, 203, 3003, 'O', 1800.00, '3-MEDIUM', 'Clerk#003', 0, '中等优先级',
'2024-03-05'),
+ (1004, 504, 204, 3004, 'F', 3200.00, '4-NOT SPEC', 'Clerk#004', 1, '大额订单',
'2024-03-15'),
+ (1005, 505, 205, 3005, 'O', 950.00, '5-LOW', 'Clerk#005', 0, '小额订单',
'2024-04-01'),
+ (1006, 506, 206, 3005, 'O', 950.00, '5-LOW', 'Clerk#005', 0, '小额订单',
'2024-04-01'),
+ (1007, 507, 207, 3005, 'O', 950.00, '5-LOW', 'Clerk#005', 0, '小额订单',
'2024-04-01'),
+ (1008, 508, 208, 3005, 'O', 950.00, '5-LOW', 'Clerk#005', 0, '小额订单',
'2024-04-01');
+
+ INSERT INTO lineitem_1 (
+ l_orderkey, l_linenumber, l_partkey, l_suppkey, l_quantity,
+ l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus,
+ l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment,
l_shipdate
+ ) VALUES
+ (1001, 1, 501, 201, 5.00, 500.00, 0.05, 0.10, 'N', 'O', '2024-01-16',
'2024-01-20', 'DELIVER IN PERSON', 'TRUCK', '订单1001第一行', '2024-01-18'),
+ (1001, 2, 502, 202, 10.00, 1000.00, 0.10, 0.15, 'N', 'O', '2024-01-16',
'2024-01-20', 'NONE', 'AIR', '订单1001第二行', '2024-01-18'),
+ (1002, 1, 503, 203, 8.00, 800.00, 0.08, 0.12, 'N', 'O', '2024-02-21',
'2024-02-25', 'TAKE BACK RETURN', 'MAIL', '订单1002第一行', '2024-02-22'),
+ (1002, 2, 504, 204, 12.00, 1200.00, 0.12, 0.18, 'N', 'O', '2024-02-21',
'2024-02-26', 'COLLECT COD', 'SHIP', '订单1002第二行', '2024-02-23'),
+ (1003, 1, 505, 205, 6.00, 600.00, 0.06, 0.12, 'N', 'O', '2024-03-06',
'2024-03-10', 'DELIVER IN PERSON', 'TRUCK', '订单1003第一行', '2024-03-08'),
+ (1003, 2, 506, 206, 9.00, 900.00, 0.09, 0.15, 'N', 'O', '2024-03-06',
'2024-03-11', 'NONE', 'AIR', '订单1003第二行', '2024-03-09'),
+ (1004, 1, 507, 207, 10.00, 1000.00, 0.10, 0.18, 'N', 'O', '2024-03-16',
'2024-03-20', 'TAKE BACK RETURN', 'MAIL', '订单1004第一行', '2024-03-18'),
+ (1004, 2, 508, 208, 15.00, 1500.00, 0.15, 0.22, 'N', 'O', '2024-03-16',
'2024-03-21', 'COLLECT COD', 'SHIP', '订单1004第二行', '2024-03-19'),
+ (1005, 1, 509, 209, 4.00, 400.00, 0.04, 0.08, 'N', 'O', '2024-04-02',
'2024-04-06', 'DELIVER IN PERSON', 'TRUCK', '订单1005第一行', '2024-04-04'),
+ (1005, 2, 510, 210, 5.50, 550.00, 0.05, 0.11, 'N', 'O', '2024-04-02',
'2024-04-07', 'NONE', 'AIR', '订单1005第二行', '2024-04-05');
+
+ INSERT INTO partsupp_1 (
+ ps_partkey, ps_suppkey, ps_availqty, ps_supplycost, ps_comment
+ ) VALUES
+ (501, 201, 100, 50.00, '零件501供应商201'),
+ (502, 202, 200, 75.00, '零件502供应商202'),
+ (503, 203, 150, 60.00, '零件503供应商203'),
+ (504, 204, 180, 65.00, '零件504供应商204'),
+ (505, 205, 120, 55.00, '零件505供应商205'),
+ (506, 206, 90, 70.00, '零件506供应商206'),
+ (507, 207, 160, 80.00, '零件507供应商207'),
+ (508, 208, 140, 75.00, '零件508供应商208'),
+ (509, 209, 110, 85.00, '零件509供应商209'),
+ (510, 210, 130, 90.00, '零件510供应商210');
+ """
+
+ sql """analyze table lineitem_1 with sync;"""
+ sql """analyze table orders_1 with sync;"""
+ sql """analyze table partsupp_1 with sync;"""
+ sql """alter table lineitem_1 modify column l_comment set stats
('row_count'='10');"""
+ sql """alter table orders_1 modify column o_comment set stats
('row_count'='8');"""
+ sql """alter table partsupp_1 modify column ps_comment set stats
('row_count'='10');"""
+
+
+ def compare_res = { def stmt, int orderByColumns = 1 ->
+ sql "SET enable_materialized_view_rewrite=false"
+ def orderStmt = " order by " + (1..orderByColumns).join(", ")
+ def origin_res = sql stmt + orderStmt
+ logger.info("origin_res: " + origin_res)
+ sql "SET enable_materialized_view_rewrite=true"
+ def mv_origin_res = sql stmt + orderStmt
+ logger.info("mv_origin_res: " + mv_origin_res)
+ assertTrue((mv_origin_res == [] && origin_res == []) ||
(mv_origin_res.size() == origin_res.size()))
+ for (int row = 0; row < mv_origin_res.size(); row++) {
+ assertTrue(mv_origin_res[row].size() == origin_res[row].size())
+ for (int col = 0; col < mv_origin_res[row].size(); col++) {
+ assertTrue(mv_origin_res[row][col] == origin_res[row][col])
+ }
+ }
+ }
+
+ def dropAllConstraints = { def tableName ->
+ def getConstraintsQuery = "SHOW CONSTRAINTS FROM ${tableName}"
+ def constraints = sql getConstraintsQuery
+ logger.info("needed deleted constraints : ${constraints}")
+ constraints.each { constraint ->
+ def constraintName = constraint[0]
+ def dropConstraintSQL = "ALTER TABLE ${tableName} DROP CONSTRAINT
${constraintName}"
+ sql dropConstraintSQL
+ logger.info("delete ${tableName} constraits : ${constraintName}")
+ }
+ }
+
+
+ // base mtmv + inner/left join without unique
+ def mv_stmt_1 = """
+ select l_shipdate, l_linenumber, l_partkey, l_orderkey, o_orderkey,
o_partkey, o_suppkey, o_custkey, ps_partkey, ps_suppkey, ps_availqty,
ps_supplycost from lineitem_1
+ inner join orders_1
+ on o_orderkey = l_orderkey
+ inner join partsupp_1
+ on ps_partkey = l_partkey and ps_suppkey = l_suppkey
+ """
+ def mv_stmt_2 = """
+ select l_shipdate, l_linenumber, l_partkey, l_orderkey, o_orderkey,
o_partkey, o_suppkey, o_custkey, ps_partkey, ps_suppkey, ps_availqty,
ps_supplycost from lineitem_1
+ left join orders_1
+ on o_orderkey = l_orderkey
+ left join partsupp_1
+ on ps_partkey = l_partkey and ps_suppkey = l_suppkey
+ """
+
+ // single table, use left
+ def query_1 = """select l_shipdate, l_linenumber, l_partkey, l_orderkey
from lineitem_1"""
+
+ // lineitem_1 join orders_1 use left
+ def query_2 = """select l_shipdate, l_linenumber, l_partkey, l_orderkey
+ from lineitem_1
+ inner join
+ orders_1
+ on o_orderkey = l_orderkey"""
+ def query_3 = """select l_shipdate, l_linenumber, l_partkey, l_orderkey
+ from lineitem_1
+ left join
+ orders_1
+ on o_orderkey = l_orderkey"""
+ // lineitem_1 join orders_1 use both
+ def query_4 = """select l_shipdate, l_linenumber, l_partkey, o_orderkey
+ from lineitem_1
+ inner join
+ orders_1
+ on o_orderkey = l_orderkey"""
+ def query_5 = """select l_shipdate, l_linenumber, l_partkey, o_orderkey
+ from lineitem_1
+ left join
+ orders_1
+ on o_orderkey = l_orderkey"""
+ // lineitem_1 join orders_1 use right
+ def query_6 = """select o_orderkey, o_partkey, o_suppkey, o_custkey
+ from lineitem_1
+ inner join
+ orders_1
+ on o_orderkey = l_orderkey"""
+ def query_7 = """select o_orderkey, o_partkey, o_suppkey, o_custkey
+ from lineitem_1
+ left join
+ orders_1
+ on o_orderkey = l_orderkey"""
+
+ // lineitem_1 join partsupp_1 use left
+ def query_8 = """select l_shipdate, l_linenumber, l_partkey, l_orderkey
+ from lineitem_1
+ inner join
+ partsupp_1
+ on ps_partkey = l_partkey and ps_suppkey =
l_suppkey"""
+ def query_9 = """select l_shipdate, l_linenumber, l_partkey, l_orderkey
+ from lineitem_1
+ left join partsupp_1
+ on ps_partkey = l_partkey and ps_suppkey =
l_suppkey"""
+ // lineitem_1 join partsupp_1 use both
+ def query_10 = """select l_shipdate, l_linenumber, l_partkey, ps_partkey
+ from lineitem_1
+ inner join
+ partsupp_1 on ps_partkey = l_partkey and
ps_suppkey = l_suppkey"""
+ def query_11 = """select l_shipdate, l_linenumber, l_partkey, ps_partkey
+ from lineitem_1
+ left join partsupp_1
+ on ps_partkey = l_partkey and ps_suppkey =
l_suppkey"""
+
+ // lineitem_1 join partsupp_1 use right
+ def query_12 = """select ps_partkey, ps_suppkey, ps_availqty, ps_supplycost
+ from lineitem_1
+ inner join
+ partsupp_1 on ps_partkey = l_partkey and
ps_suppkey = l_suppkey"""
+ def query_13 = """select ps_partkey, ps_suppkey, ps_availqty, ps_supplycost
+ from lineitem_1
+ left join partsupp_1
+ on ps_partkey = l_partkey and ps_suppkey =
l_suppkey"""
+
+
+ def query_list = [query_1, query_2, query_3, query_4, query_5, query_6,
query_7, query_8, query_9,
+ query_10, query_11, query_12, query_13]
+
+ dropAllConstraints("orders_1")
+ dropAllConstraints("lineitem_1")
+ dropAllConstraints("partsupp_1")
+
+ // lineitem -> orders (fk-pk)
+ // lineitem -> partsupp (fk-pk)
+ sql """alter table partsupp_1 add constraint pk primary key(ps_partkey,
ps_suppkey)"""
+ sql """alter table lineitem_1 add constraint fk1 foreign key (l_partkey,
l_suppkey) references partsupp_1(ps_partkey, ps_suppkey)"""
+ sql """alter table orders_1 add constraint pk primary key(o_orderkey)"""
+ sql """alter table lineitem_1 add constraint fk foreign key (l_orderkey)
references orders_1(o_orderkey)"""
+
+ def mv_name = "join_elim_star_pattern"
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12]) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13]) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ dropAllConstraints("orders_1")
+ dropAllConstraints("lineitem_1")
+ dropAllConstraints("partsupp_1")
+ // lineitem -> orders (fk-pk)
+ sql """alter table orders_1 add constraint pk primary key(o_orderkey)"""
+ sql """alter table lineitem_1 add constraint fk foreign key (l_orderkey)
references orders_1(o_orderkey)"""
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in [8, 10, 12]) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in [9, 11, 13]) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders_1")
+ dropAllConstraints("lineitem_1")
+ dropAllConstraints("partsupp_1")
+ // lineitem -> partsupp (fk-pk)
+ sql """alter table partsupp_1 add constraint pk primary key(ps_partkey,
ps_suppkey)"""
+ sql """alter table lineitem_1 add constraint fk1 foreign key (l_partkey,
l_suppkey) references partsupp_1(ps_partkey, ps_suppkey)"""
+
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in [2, 4, 6]) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in [3, 5, 7]) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders_1")
+ dropAllConstraints("lineitem_1")
+ dropAllConstraints("partsupp_1")
+ // lineitem -> orders (u)
+ // lineitem -> partsupp (fk-pk)
+ sql """alter table partsupp_1 add constraint pk primary key (ps_partkey,
ps_suppkey)"""
+ sql """alter table lineitem_1 add constraint fk1 foreign key (l_partkey,
l_suppkey) references partsupp_1(ps_partkey, ps_suppkey)"""
+ sql """alter table orders_1 add constraint uk unique (o_orderkey)"""
+
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in [2, 4, 6]) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13]) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders_1")
+ dropAllConstraints("lineitem_1")
+ dropAllConstraints("partsupp_1")
+ // lineitem -> orders (fk-pk)
+ // lineitem -> partsupp (u)
+ sql """alter table partsupp_1 add constraint pk unique (ps_partkey,
ps_suppkey)"""
+ sql """alter table orders_1 add constraint pk primary key (o_orderkey)"""
+ sql """alter table lineitem_1 add constraint fk foreign key (l_orderkey)
references orders_1(o_orderkey)"""
+
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in [8, 10, 12]) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in [1, 2, 3, 4, 5, 6, 7, 9, 11, 13]) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders_1")
+ dropAllConstraints("lineitem_1")
+ dropAllConstraints("partsupp_1")
+ // lineitem -> orders (u)
+ // lineitem -> partsupp (u)
+ sql """alter table partsupp_1 add constraint pk unique (ps_partkey,
ps_suppkey)"""
+ sql """alter table orders_1 add constraint pk unique (o_orderkey)"""
+
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in []) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in [1, 2, 3, 4, 5, 6, 7, 9, 11, 13]) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders_1")
+ dropAllConstraints("lineitem_1")
+ dropAllConstraints("partsupp_1")
+ // lineitem -> orders (u)
+ sql """alter table orders_1 add constraint pk unique (o_orderkey)"""
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in []) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in [9, 11, 13]) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders_1")
+ dropAllConstraints("lineitem_1")
+ dropAllConstraints("partsupp_1")
+ // lineitem -> partsupp (u)
+ sql """alter table partsupp_1 add constraint pk unique (ps_partkey,
ps_suppkey)"""
+
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in []) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in [3, 5, 7]) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders_1")
+ dropAllConstraints("lineitem_1")
+ dropAllConstraints("partsupp_1")
+ // without constraints
+ create_async_mv(db, mv_name, mv_stmt_1)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("inner mv current query index: " + j)
+ if (j in []) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+ create_async_mv(db, mv_name, mv_stmt_2)
+ for (int j = 1; j < query_list.size() + 1; j++) {
+ logger.info("left mv current query index: " + j)
+ if (j in []) {
+ mv_rewrite_success(query_list[j - 1], mv_name)
+ compare_res(query_list[j - 1], 4)
+ } else {
+ mv_rewrite_fail(query_list[j - 1], mv_name)
+ }
+ }
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name};"""
+
+
+ dropAllConstraints("orders_1")
+ dropAllConstraints("lineitem_1")
+ dropAllConstraints("partsupp_1")
+
+ // negative examplesorders_1, lineitem_1, partsupp_1
+ sql """alter table partsupp_1 add constraint pk unique (ps_partkey,
ps_suppkey)"""
+ async_mv_rewrite_fail(db,
+ """
+ select l_shipdate, l_linenumber, o_orderkey, ps_suppkey from
lineitem_1
+ inner join orders_1
+ on o_orderkey = l_orderkey
+ inner join partsupp_1
+ on ps_partkey = l_partkey or ps_suppkey = l_suppkey
+ """,
+ """
+ select l_shipdate, l_linenumber, o_orderkey
+ from lineitem_1
+ inner join orders_1 on o_orderkey = l_orderkey
+ """,
+ "${mv_name}_neg_2")
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}_neg_1;"""
+
+ sql """
+ drop table if exists orders_1;
+ drop table if exists lineitem_1;
+ drop table if exists partsupp_1;
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
