This is an automated email from the ASF dual-hosted git repository.
starocean999 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 649f9bc6837 [feature](mtmv) Support query rewrite by materialized
view when query is aggregate and materialized view has no aggregate (#36278)
649f9bc6837 is described below
commit 649f9bc6837b57618ea17341ca716ceaffa3a1e0
Author: seawinde <149132972+seawi...@users.noreply.github.com>
AuthorDate: Fri Jun 14 11:15:53 2024 +0800
[feature](mtmv) Support query rewrite by materialized view when query is
aggregate and materialized view has no aggregate (#36278)
## Proposed changes
Support query rewrite by materialized view when query is aggregate and
materialized view has no aggregate
this maybe improve query spped, because it can save expression
evaluation by use the expression result in materialized view.
this also support single table rewrite.
For example as follwoing:
mv def is:
> CREATE MATERIALIZED VIEW mv1
> BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
> DISTRIBUTED BY RANDOM BUCKETS 2
> PROPERTIES ('replication_num' = '1')
> AS
> select case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end,
> o_orderstatus,
> bin(o_orderkey),
> l_suppkey,
> l_linenumber
> from orders
> left join lineitem on o_orderkey = l_orderkey;
the query as following can be rewritten by mv successfully
> select
> count(case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end) as count_case,
> o_orderstatus,
> bin(o_orderkey)
> from orders
> left join lineitem on o_orderkey = l_orderkey
> where l_linenumber = 4
> group by
> o_orderstatus,
> bin(o_orderkey);
---
.../org/apache/doris/nereids/rules/RuleSet.java | 2 +
.../org/apache/doris/nereids/rules/RuleType.java | 6 +
.../mv/AbstractMaterializedViewAggregateRule.java | 116 ++++-
.../mv/AbstractMaterializedViewJoinRule.java | 2 +-
.../mv/AbstractMaterializedViewRule.java | 18 +-
...terializedViewAggregateOnNoneAggregateRule.java | 134 ++++++
...MaterializedViewFilterProjectAggregateRule.java | 2 +-
...MaterializedViewProjectFilterAggregateRule.java | 2 +-
.../exploration/mv/MaterializedViewScanRule.java | 2 +-
.../mv/agg_on_none_agg/agg_on_none_agg.out | 155 +++++++
.../mv/agg_on_none_agg/agg_on_none_agg.groovy | 488 +++++++++++++++++++++
.../aggregate_without_roll_up.groovy | 2 +-
12 files changed, 890 insertions(+), 39 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleSet.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleSet.java
index ada72f04680..a5fb0b8736a 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleSet.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/RuleSet.java
@@ -31,6 +31,7 @@ import
org.apache.doris.nereids.rules.exploration.join.OuterJoinLAsscomProject;
import
org.apache.doris.nereids.rules.exploration.join.PushDownProjectThroughInnerOuterJoin;
import
org.apache.doris.nereids.rules.exploration.join.PushDownProjectThroughSemiJoin;
import
org.apache.doris.nereids.rules.exploration.join.SemiJoinSemiJoinTransposeProject;
+import
org.apache.doris.nereids.rules.exploration.mv.MaterializedViewAggregateOnNoneAggregateRule;
import
org.apache.doris.nereids.rules.exploration.mv.MaterializedViewAggregateRule;
import
org.apache.doris.nereids.rules.exploration.mv.MaterializedViewFilterAggregateRule;
import
org.apache.doris.nereids.rules.exploration.mv.MaterializedViewFilterJoinRule;
@@ -236,6 +237,7 @@ public class RuleSet {
.add(MaterializedViewFilterProjectScanRule.INSTANCE)
.add(MaterializedViewProjectScanRule.INSTANCE)
.add(MaterializedViewProjectFilterScanRule.INSTANCE)
+ .add(MaterializedViewAggregateOnNoneAggregateRule.INSTANCE)
.build();
public static final List<Rule> DPHYP_REORDER_RULES =
ImmutableList.<Rule>builder()
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 62dc7840da9..bde86b61c29 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
@@ -396,6 +396,12 @@ public enum RuleType {
MATERIALIZED_VIEW_FILTER_PROJECT_AGGREGATE(RuleTypeClass.EXPLORATION),
MATERIALIZED_VIEW_ONLY_AGGREGATE(RuleTypeClass.EXPLORATION),
+
MATERIALIZED_VIEW_PROJECT_AGGREGATE_ON_NONE_AGGREGATE(RuleTypeClass.EXPLORATION),
+
MATERIALIZED_VIEW_FILTER_AGGREGATE_ON_NONE_AGGREGATE(RuleTypeClass.EXPLORATION),
+
MATERIALIZED_VIEW_PROJECT_FILTER_AGGREGATE_ON_NONE_AGGREGATE(RuleTypeClass.EXPLORATION),
+
MATERIALIZED_VIEW_FILTER_PROJECT_AGGREGATE_ON_NONE_AGGREGATE(RuleTypeClass.EXPLORATION),
+
MATERIALIZED_VIEW_ONLY_AGGREGATE_ON_NONE_AGGREGATE(RuleTypeClass.EXPLORATION),
+
MATERIALIZED_VIEW_FILTER_SCAN(RuleTypeClass.EXPLORATION),
MATERIALIZED_VIEW_PROJECT_SCAN(RuleTypeClass.EXPLORATION),
MATERIALIZED_VIEW_FILTER_PROJECT_SCAN(RuleTypeClass.EXPLORATION),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewAggregateRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewAggregateRule.java
index 997792f0f4c..53b0c29bde1 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewAggregateRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewAggregateRule.java
@@ -20,6 +20,7 @@ package org.apache.doris.nereids.rules.exploration.mv;
import org.apache.doris.common.Pair;
import org.apache.doris.nereids.CascadesContext;
import org.apache.doris.nereids.rules.analysis.NormalizeRepeat;
+import
org.apache.doris.nereids.rules.exploration.mv.AbstractMaterializedViewAggregateRule.AggregateExpressionRewriteContext.ExpressionRewriteMode;
import
org.apache.doris.nereids.rules.exploration.mv.StructInfo.PlanCheckContext;
import
org.apache.doris.nereids.rules.exploration.mv.StructInfo.PlanSplitContext;
import org.apache.doris.nereids.rules.exploration.mv.mapping.SlotMapping;
@@ -136,6 +137,27 @@ public abstract class
AbstractMaterializedViewAggregateRule extends AbstractMate
materializationContext.getShuttledExprToScanExprMapping(),
viewToQuerySlotMapping));
}
+ return doRewriteQueryByView(queryStructInfo,
+ viewToQuerySlotMapping,
+ queryTopPlanAndAggPair,
+ tempRewritedPlan,
+ materializationContext,
+ ExpressionRewriteMode.EXPRESSION_DIRECT,
+ ExpressionRewriteMode.EXPRESSION_ROLL_UP);
+ }
+
+ /**
+ * Aggregate function and group by expression rewrite impl
+ */
+ protected LogicalAggregate<Plan> doRewriteQueryByView(
+ StructInfo queryStructInfo,
+ SlotMapping viewToQuerySlotMapping,
+ Pair<Plan, LogicalAggregate<Plan>> queryTopPlanAndAggPair,
+ Plan tempRewritedPlan,
+ MaterializationContext materializationContext,
+ ExpressionRewriteMode groupByMode,
+ ExpressionRewriteMode aggregateFunctionMode) {
+
// try to roll up.
// split the query top plan expressions to group expressions and
functions, if can not, bail out.
Pair<Set<? extends Expression>, Set<? extends Expression>>
queryGroupAndFunctionPair
@@ -149,11 +171,12 @@ public abstract class
AbstractMaterializedViewAggregateRule extends AbstractMate
Map<Expression, Expression> mvExprToMvScanExprQueryBased =
materializationContext.getShuttledExprToScanExprMapping().keyPermute(viewToQuerySlotMapping)
.flattenMap().get(0);
+ Plan queryTopPlan = queryStructInfo.getTopPlan();
for (Expression topExpression : queryTopPlan.getOutput()) {
if (queryTopPlanFunctionSet.contains(topExpression)) {
// if agg function, try to roll up and rewrite
Expression rollupedExpression =
tryRewriteExpression(queryStructInfo, topExpression,
- mvExprToMvScanExprQueryBased, false,
materializationContext,
+ mvExprToMvScanExprQueryBased, aggregateFunctionMode,
materializationContext,
"Query function roll up fail",
() -> String.format("queryExpression = %s,\n
mvExprToMvScanExprQueryBased = %s",
topExpression, mvExprToMvScanExprQueryBased));
@@ -164,7 +187,7 @@ public abstract class AbstractMaterializedViewAggregateRule
extends AbstractMate
} else {
// if group by dimension, try to rewrite
Expression rewrittenGroupByExpression =
tryRewriteExpression(queryStructInfo, topExpression,
- mvExprToMvScanExprQueryBased, true,
materializationContext,
+ mvExprToMvScanExprQueryBased, groupByMode,
materializationContext,
"View dimensions doesn't not cover the query
dimensions",
() -> String.format("mvExprToMvScanExprQueryBased is
%s,\n queryExpression is %s",
mvExprToMvScanExprQueryBased, topExpression));
@@ -178,6 +201,7 @@ public abstract class AbstractMaterializedViewAggregateRule
extends AbstractMate
finalGroupExpressions.add(groupByExpression);
}
}
+ LogicalAggregate<Plan> queryAggregate = queryTopPlanAndAggPair.value();
List<Expression> queryGroupByExpressions =
queryAggregate.getGroupByExpressions();
// handle the scene that query top plan not use the group by in query
bottom aggregate
if (queryGroupByExpressions.size() != queryTopPlanGroupBySet.size()) {
@@ -186,7 +210,7 @@ public abstract class AbstractMaterializedViewAggregateRule
extends AbstractMate
continue;
}
Expression rewrittenGroupByExpression =
tryRewriteExpression(queryStructInfo, expression,
- mvExprToMvScanExprQueryBased, true,
materializationContext,
+ mvExprToMvScanExprQueryBased, groupByMode,
materializationContext,
"View dimensions doesn't not cover the query
dimensions in bottom agg ",
() -> String.format("mvExprToMvScanExprQueryBased is
%s,\n expression is %s",
mvExprToMvScanExprQueryBased, expression));
@@ -198,7 +222,7 @@ public abstract class AbstractMaterializedViewAggregateRule
extends AbstractMate
finalGroupExpressions.add(groupByExpression);
}
}
- if (queryContainsGroupSets) {
+ if (queryAggregate.getSourceRepeat().isPresent()) {
// construct group sets for repeat
List<List<Expression>> rewrittenGroupSetsExpressions = new
ArrayList<>();
List<List<Expression>> groupingSets =
queryAggregate.getSourceRepeat().get().getGroupingSets();
@@ -209,7 +233,8 @@ public abstract class AbstractMaterializedViewAggregateRule
extends AbstractMate
List<Expression> rewrittenGroupSetExpressions = new
ArrayList<>();
for (Expression expression : groupingSet) {
Expression rewrittenGroupByExpression =
tryRewriteExpression(queryStructInfo, expression,
- mvExprToMvScanExprQueryBased, true,
materializationContext,
+ mvExprToMvScanExprQueryBased,
ExpressionRewriteMode.EXPRESSION_DIRECT,
+ materializationContext,
"View dimensions doesn't not cover the query
group set dimensions",
() ->
String.format("mvExprToMvScanExprQueryBased is %s,\n queryExpression is %s",
mvExprToMvScanExprQueryBased,
expression));
@@ -232,14 +257,14 @@ public abstract class
AbstractMaterializedViewAggregateRule extends AbstractMate
* Try to rewrite query expression by view, contains both group by
dimension and aggregate function
*/
protected Expression tryRewriteExpression(StructInfo queryStructInfo,
Expression queryExpression,
- Map<Expression, Expression> mvShuttledExprToMvScanExprQueryBased,
boolean isGroupBy,
+ Map<Expression, Expression> mvShuttledExprToMvScanExprQueryBased,
ExpressionRewriteMode rewriteMode,
MaterializationContext materializationContext, String
summaryIfFail, Supplier<String> detailIfFail) {
Expression queryFunctionShuttled =
ExpressionUtils.shuttleExpressionWithLineage(
queryExpression,
queryStructInfo.getTopPlan(),
queryStructInfo.getTableBitSet());
AggregateExpressionRewriteContext expressionRewriteContext = new
AggregateExpressionRewriteContext(
- isGroupBy, mvShuttledExprToMvScanExprQueryBased,
queryStructInfo.getTopPlan(),
+ rewriteMode, mvShuttledExprToMvScanExprQueryBased,
queryStructInfo.getTopPlan(),
queryStructInfo.getTableBitSet());
Expression rewrittenExpression =
queryFunctionShuttled.accept(AGGREGATE_EXPRESSION_REWRITER,
expressionRewriteContext);
@@ -344,7 +369,7 @@ public abstract class AbstractMaterializedViewAggregateRule
extends AbstractMate
return null;
}
- private Pair<Set<? extends Expression>, Set<? extends Expression>>
topPlanSplitToGroupAndFunction(
+ protected Pair<Set<? extends Expression>, Set<? extends Expression>>
topPlanSplitToGroupAndFunction(
Pair<Plan, LogicalAggregate<Plan>> topPlanAndAggPair, StructInfo
queryStructInfo) {
LogicalAggregate<Plan> bottomQueryAggregate =
topPlanAndAggPair.value();
Set<Expression> groupByExpressionSet = new
HashSet<>(bottomQueryAggregate.getGroupByExpressions());
@@ -377,7 +402,7 @@ public abstract class AbstractMaterializedViewAggregateRule
extends AbstractMate
return Pair.of(topGroupByExpressions, topFunctionExpressions);
}
- private Pair<Plan, LogicalAggregate<Plan>>
splitToTopPlanAndAggregate(StructInfo structInfo) {
+ protected Pair<Plan, LogicalAggregate<Plan>>
splitToTopPlanAndAggregate(StructInfo structInfo) {
Plan topPlan = structInfo.getTopPlan();
PlanSplitContext splitContext = new
PlanSplitContext(Sets.newHashSet(LogicalAggregate.class));
topPlan.accept(StructInfo.PLAN_SPLITTER, splitContext);
@@ -394,7 +419,7 @@ public abstract class AbstractMaterializedViewAggregateRule
extends AbstractMate
* slot reference equals currently.
*/
@Override
- protected boolean checkPattern(StructInfo structInfo, CascadesContext
cascadesContext) {
+ protected boolean checkQueryPattern(StructInfo structInfo, CascadesContext
cascadesContext) {
PlanCheckContext checkContext =
PlanCheckContext.of(SUPPORTED_JOIN_TYPE_SET);
// if query or mv contains more then one top aggregate, should fail
return structInfo.getTopPlan().accept(StructInfo.PLAN_PATTERN_CHECKER,
checkContext)
@@ -414,17 +439,38 @@ public abstract class
AbstractMaterializedViewAggregateRule extends AbstractMate
if (!rewriteContext.isValid()) {
return aggregateFunction;
}
- Expression queryFunctionShuttled =
ExpressionUtils.shuttleExpressionWithLineage(
- aggregateFunction,
- rewriteContext.getQueryTopPlan(),
- rewriteContext.getQueryTableBitSet());
- Function rollupAggregateFunction = rollup(aggregateFunction,
queryFunctionShuttled,
- rewriteContext.getMvExprToMvScanExprQueryBasedMapping());
- if (rollupAggregateFunction == null) {
+ if
(ExpressionRewriteMode.EXPRESSION_DIRECT.equals(rewriteContext.getExpressionRewriteMode()))
{
rewriteContext.setValid(false);
return aggregateFunction;
}
- return rollupAggregateFunction;
+ Function rewrittenFunction;
+ if
(ExpressionRewriteMode.EXPRESSION_ROLL_UP.equals(rewriteContext.getExpressionRewriteMode()))
{
+ Expression queryFunctionShuttled =
ExpressionUtils.shuttleExpressionWithLineage(
+ aggregateFunction,
+ rewriteContext.getQueryTopPlan(),
+ rewriteContext.getQueryTableBitSet());
+ rewrittenFunction = rollup(aggregateFunction,
queryFunctionShuttled,
+
rewriteContext.getMvExprToMvScanExprQueryBasedMapping());
+ if (rewrittenFunction == null) {
+ rewriteContext.setValid(false);
+ return aggregateFunction;
+ }
+ return rewrittenFunction;
+ }
+ if
(ExpressionRewriteMode.EXPRESSION_DIRECT_ALL.equals(rewriteContext.getExpressionRewriteMode()))
{
+ List<Expression> children = aggregateFunction.children();
+ List<Expression> rewrittenChildren = new ArrayList<>();
+ for (Expression child : children) {
+ Expression rewrittenExpression = child.accept(this,
rewriteContext);
+ if (!rewriteContext.isValid()) {
+ return aggregateFunction;
+ }
+ rewrittenChildren.add(rewrittenExpression);
+ }
+ return aggregateFunction.withChildren(rewrittenChildren);
+ }
+ rewriteContext.setValid(false);
+ return aggregateFunction;
}
@Override
@@ -474,7 +520,8 @@ public abstract class AbstractMaterializedViewAggregateRule
extends AbstractMate
return expr;
}
// for group by expression try to get corresponding expression
directly
- if (rewriteContext.isOnlyContainGroupByExpression()
+ if
((ExpressionRewriteMode.EXPRESSION_DIRECT.equals(rewriteContext.getExpressionRewriteMode())
+ ||
ExpressionRewriteMode.EXPRESSION_DIRECT_ALL.equals(rewriteContext.getExpressionRewriteMode()))
&&
rewriteContext.getMvExprToMvScanExprQueryBasedMapping().containsKey(expr)) {
return
rewriteContext.getMvExprToMvScanExprQueryBasedMapping().get(expr);
}
@@ -499,15 +546,15 @@ public abstract class
AbstractMaterializedViewAggregateRule extends AbstractMate
*/
protected static class AggregateExpressionRewriteContext {
private boolean valid = true;
- private final boolean onlyContainGroupByExpression;
+ private final ExpressionRewriteMode expressionRewriteMode;
private final Map<Expression, Expression>
mvExprToMvScanExprQueryBasedMapping;
private final Plan queryTopPlan;
private final BitSet queryTableBitSet;
- public AggregateExpressionRewriteContext(boolean
onlyContainGroupByExpression,
+ public AggregateExpressionRewriteContext(ExpressionRewriteMode
expressionRewriteMode,
Map<Expression, Expression>
mvExprToMvScanExprQueryBasedMapping, Plan queryTopPlan,
BitSet queryTableBitSet) {
- this.onlyContainGroupByExpression = onlyContainGroupByExpression;
+ this.expressionRewriteMode = expressionRewriteMode;
this.mvExprToMvScanExprQueryBasedMapping =
mvExprToMvScanExprQueryBasedMapping;
this.queryTopPlan = queryTopPlan;
this.queryTableBitSet = queryTableBitSet;
@@ -521,8 +568,8 @@ public abstract class AbstractMaterializedViewAggregateRule
extends AbstractMate
this.valid = valid;
}
- public boolean isOnlyContainGroupByExpression() {
- return onlyContainGroupByExpression;
+ public ExpressionRewriteMode getExpressionRewriteMode() {
+ return expressionRewriteMode;
}
public Map<Expression, Expression>
getMvExprToMvScanExprQueryBasedMapping() {
@@ -536,5 +583,26 @@ public abstract class
AbstractMaterializedViewAggregateRule extends AbstractMate
public BitSet getQueryTableBitSet() {
return queryTableBitSet;
}
+
+ /**
+ * The expression rewrite mode, which decide how the expression in
query is rewritten by mv
+ */
+ protected enum ExpressionRewriteMode {
+ /**
+ * Try to use the expression in mv directly, and doesn't handle
aggregate function
+ */
+ EXPRESSION_DIRECT,
+
+ /**
+ * Try to use the expression in mv directly, and try to rewrite
the arguments in aggregate function except
+ * the aggregate function
+ */
+ EXPRESSION_DIRECT_ALL,
+
+ /**
+ * Try to roll up aggregate function
+ */
+ EXPRESSION_ROLL_UP
+ }
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewJoinRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewJoinRule.java
index 3bf037e0189..cc90a05d06d 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewJoinRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewJoinRule.java
@@ -75,7 +75,7 @@ public abstract class AbstractMaterializedViewJoinRule
extends AbstractMateriali
* Join condition should be slot reference equals currently.
*/
@Override
- protected boolean checkPattern(StructInfo structInfo, CascadesContext
cascadesContext) {
+ protected boolean checkQueryPattern(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/AbstractMaterializedViewRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewRule.java
index b5377181126..74db83d7863 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
@@ -43,7 +43,6 @@ import
org.apache.doris.nereids.trees.expressions.NamedExpression;
import org.apache.doris.nereids.trees.expressions.Not;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.SlotReference;
-import org.apache.doris.nereids.trees.expressions.VirtualSlotReference;
import
org.apache.doris.nereids.trees.expressions.functions.agg.AggregateFunction;
import org.apache.doris.nereids.trees.expressions.functions.scalar.NonNullable;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Nullable;
@@ -149,7 +148,7 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
List<StructInfo> uncheckedStructInfos =
MaterializedViewUtils.extractStructInfo(queryPlan, cascadesContext,
materializedViewTableSet);
uncheckedStructInfos.forEach(queryStructInfo -> {
- boolean valid = checkPattern(queryStructInfo, cascadesContext) &&
queryStructInfo.isValid();
+ boolean valid = checkQueryPattern(queryStructInfo,
cascadesContext) && queryStructInfo.isValid();
if (!valid) {
cascadesContext.getMaterializationContexts().forEach(ctx ->
ctx.recordFailReason(queryStructInfo, "Query struct
info is invalid",
@@ -346,7 +345,7 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
}
}
- private boolean needUnionRewrite(
+ protected boolean needUnionRewrite(
Pair<Map<BaseTableInfo, Set<String>>, Map<BaseTableInfo,
Set<String>>> invalidPartitions,
CascadesContext cascadesContext) {
return invalidPartitions != null
@@ -689,13 +688,17 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
/**
* Check the pattern of query or materializedView is supported or not.
*/
- protected boolean checkPattern(StructInfo structInfo, CascadesContext
cascadesContext) {
+ protected boolean checkQueryPattern(StructInfo structInfo, CascadesContext
cascadesContext) {
if (structInfo.getRelations().isEmpty()) {
return false;
}
return true;
}
+ protected boolean checkMaterializationPattern(StructInfo structInfo,
CascadesContext cascadesContext) {
+ return checkQueryPattern(structInfo, cascadesContext);
+ }
+
protected void recordIfRewritten(Plan plan, MaterializationContext
context) {
context.setSuccess(true);
if (plan.getGroupExpression().isPresent()) {
@@ -708,11 +711,6 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
&&
context.alreadyRewrite(plan.getGroupExpression().get().getOwnerGroup().getGroupId());
}
- protected boolean isEmptyVirtualSlot(Expression expression) {
- return expression instanceof VirtualSlotReference
- && ((VirtualSlotReference)
expression).getRealExpressions().isEmpty();
- }
-
// check mv plan is valid or not, this can use cache for performance
private boolean isMaterializationValid(CascadesContext cascadesContext,
MaterializationContext context) {
long materializationId =
context.getMaterializationQualifier().hashCode();
@@ -720,7 +718,7 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
materializationId);
if (cachedCheckResult == null) {
// need check in real time
- boolean checkResult = checkPattern(context.getStructInfo(),
cascadesContext);
+ boolean checkResult =
checkMaterializationPattern(context.getStructInfo(), cascadesContext);
if (!checkResult) {
context.recordFailReason(context.getStructInfo(),
"View struct info is invalid", () ->
String.format("view plan is %s",
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewAggregateOnNoneAggregateRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewAggregateOnNoneAggregateRule.java
new file mode 100644
index 00000000000..21a8ea55857
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewAggregateOnNoneAggregateRule.java
@@ -0,0 +1,134 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.rules.exploration.mv;
+
+import org.apache.doris.common.AnalysisException;
+import org.apache.doris.common.Pair;
+import org.apache.doris.mtmv.BaseTableInfo;
+import org.apache.doris.nereids.CascadesContext;
+import org.apache.doris.nereids.rules.Rule;
+import org.apache.doris.nereids.rules.RuleType;
+import
org.apache.doris.nereids.rules.exploration.mv.AbstractMaterializedViewAggregateRule.AggregateExpressionRewriteContext.ExpressionRewriteMode;
+import
org.apache.doris.nereids.rules.exploration.mv.StructInfo.PlanCheckContext;
+import org.apache.doris.nereids.rules.exploration.mv.mapping.SlotMapping;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+/**
+ * MaterializedViewAggregateOnNoAggregateRule
+ */
+public class MaterializedViewAggregateOnNoneAggregateRule extends
AbstractMaterializedViewAggregateRule {
+
+ public static final MaterializedViewAggregateOnNoneAggregateRule INSTANCE =
+ new MaterializedViewAggregateOnNoneAggregateRule();
+
+ @Override
+ public List<Rule> buildRules() {
+ return ImmutableList.of(
+
logicalFilter(logicalProject(logicalAggregate(any().when(LogicalPlan.class::isInstance))))
+ .thenApplyMultiNoThrow(ctx -> {
+
LogicalFilter<LogicalProject<LogicalAggregate<Plan>>> root = ctx.root;
+ return rewrite(root, ctx.cascadesContext);
+
}).toRule(RuleType.MATERIALIZED_VIEW_FILTER_PROJECT_AGGREGATE_ON_NONE_AGGREGATE),
+
logicalAggregate(any().when(LogicalPlan.class::isInstance)).thenApplyMultiNoThrow(ctx
-> {
+ LogicalAggregate<Plan> root = ctx.root;
+ return rewrite(root, ctx.cascadesContext);
+
}).toRule(RuleType.MATERIALIZED_VIEW_ONLY_AGGREGATE_ON_NONE_AGGREGATE),
+
logicalProject(logicalFilter(logicalAggregate(any().when(LogicalPlan.class::isInstance))))
+ .thenApplyMultiNoThrow(ctx -> {
+
LogicalProject<LogicalFilter<LogicalAggregate<Plan>>> root = ctx.root;
+ return rewrite(root, ctx.cascadesContext);
+
}).toRule(RuleType.MATERIALIZED_VIEW_PROJECT_FILTER_AGGREGATE_ON_NONE_AGGREGATE),
+
logicalProject(logicalAggregate(any().when(LogicalPlan.class::isInstance))).thenApplyMultiNoThrow(
+ ctx -> {
+ LogicalProject<LogicalAggregate<Plan>> root =
ctx.root;
+ return rewrite(root, ctx.cascadesContext);
+
}).toRule(RuleType.MATERIALIZED_VIEW_PROJECT_AGGREGATE_ON_NONE_AGGREGATE),
+
logicalFilter(logicalAggregate(any().when(LogicalPlan.class::isInstance))).thenApplyMultiNoThrow(
+ ctx -> {
+ LogicalFilter<LogicalAggregate<Plan>> root =
ctx.root;
+ return rewrite(root, ctx.cascadesContext);
+
}).toRule(RuleType.MATERIALIZED_VIEW_FILTER_AGGREGATE_ON_NONE_AGGREGATE));
+ }
+
+ @Override
+ protected boolean checkMaterializationPattern(StructInfo structInfo,
CascadesContext cascadesContext) {
+ // any check result of join or scan is true, then return true
+ PlanCheckContext joinCheckContext =
PlanCheckContext.of(SUPPORTED_JOIN_TYPE_SET);
+ boolean joinCheckResult =
structInfo.getTopPlan().accept(StructInfo.PLAN_PATTERN_CHECKER,
joinCheckContext)
+ && !joinCheckContext.isContainsTopAggregate();
+ if (joinCheckResult) {
+ return true;
+ }
+ PlanCheckContext scanCheckContext =
PlanCheckContext.of(ImmutableSet.of());
+ return
structInfo.getTopPlan().accept(StructInfo.SCAN_PLAN_PATTERN_CHECKER,
scanCheckContext)
+ && !scanCheckContext.isContainsTopAggregate();
+ }
+
+ @Override
+ protected Pair<Map<BaseTableInfo, Set<String>>, Map<BaseTableInfo,
Set<String>>> calcInvalidPartitions(
+ Plan queryPlan, Plan rewrittenPlan, AsyncMaterializationContext
materializationContext,
+ CascadesContext cascadesContext) throws AnalysisException {
+ Pair<Map<BaseTableInfo, Set<String>>, Map<BaseTableInfo, Set<String>>>
invalidPartitions
+ = super.calcInvalidPartitions(queryPlan, rewrittenPlan,
materializationContext, cascadesContext);
+ if (needUnionRewrite(invalidPartitions, cascadesContext)) {
+ // if query use some invalid partition in mv, bail out
+ return null;
+ }
+ return invalidPartitions;
+ }
+
+ @Override
+ protected Plan rewriteQueryByView(MatchMode matchMode, StructInfo
queryStructInfo, StructInfo viewStructInfo,
+ SlotMapping viewToQuerySlotMapping, Plan tempRewritedPlan,
MaterializationContext materializationContext) {
+ // check the expression used in group by and group out expression in
query
+ Pair<Plan, LogicalAggregate<Plan>> queryTopPlanAndAggPair =
splitToTopPlanAndAggregate(queryStructInfo);
+ if (queryTopPlanAndAggPair == null) {
+ materializationContext.recordFailReason(queryStructInfo,
+ "Split query to top plan and agg fail",
+ () -> String.format("query plan = %s\n",
queryStructInfo.getOriginalPlan().treeString()));
+ return null;
+ }
+ LogicalAggregate<Plan> queryAggregate = queryTopPlanAndAggPair.value();
+ boolean queryContainsGroupSets =
queryAggregate.getSourceRepeat().isPresent();
+ if (queryContainsGroupSets) {
+ // doesn't support group sets momentarily
+ materializationContext.recordFailReason(queryStructInfo,
+ "Query function roll up fail",
+ () -> String.format("query aggregate = %s",
queryAggregate.treeString()));
+ return null;
+ }
+ return doRewriteQueryByView(queryStructInfo,
+ viewToQuerySlotMapping,
+ queryTopPlanAndAggPair,
+ tempRewritedPlan,
+ materializationContext,
+ ExpressionRewriteMode.EXPRESSION_DIRECT_ALL,
+ ExpressionRewriteMode.EXPRESSION_DIRECT_ALL);
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewFilterProjectAggregateRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewFilterProjectAggregateRule.java
index bd7437a7d90..eb7c9bf5f82 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewFilterProjectAggregateRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewFilterProjectAggregateRule.java
@@ -44,6 +44,6 @@ public class MaterializedViewFilterProjectAggregateRule
extends AbstractMaterial
.thenApplyMultiNoThrow(ctx -> {
LogicalFilter<LogicalProject<LogicalAggregate<Plan>>> root = ctx.root;
return rewrite(root, ctx.cascadesContext);
-
}).toRule(RuleType.MATERIALIZED_VIEW_FILTER_AGGREGATE));
+
}).toRule(RuleType.MATERIALIZED_VIEW_FILTER_PROJECT_AGGREGATE));
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewProjectFilterAggregateRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewProjectFilterAggregateRule.java
index 906ca31b4c2..c83eedea2a1 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewProjectFilterAggregateRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewProjectFilterAggregateRule.java
@@ -43,6 +43,6 @@ public class MaterializedViewProjectFilterAggregateRule
extends AbstractMaterial
any().when(LogicalPlan.class::isInstance)))).thenApplyMultiNoThrow(ctx -> {
LogicalProject<LogicalFilter<LogicalAggregate<Plan>>> root
= ctx.root;
return rewrite(root, ctx.cascadesContext);
- }).toRule(RuleType.MATERIALIZED_VIEW_FILTER_AGGREGATE));
+
}).toRule(RuleType.MATERIALIZED_VIEW_PROJECT_FILTER_AGGREGATE));
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewScanRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewScanRule.java
index e2c3d89cb92..904c121ce9e 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewScanRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewScanRule.java
@@ -76,7 +76,7 @@ public abstract class MaterializedViewScanRule extends
AbstractMaterializedViewR
* Join condition should be slot reference equals currently.
*/
@Override
- protected boolean checkPattern(StructInfo structInfo, CascadesContext
cascadesContext) {
+ protected boolean checkQueryPattern(StructInfo structInfo, CascadesContext
cascadesContext) {
PlanCheckContext checkContext = PlanCheckContext.of(ImmutableSet.of());
return
structInfo.getTopPlan().accept(StructInfo.SCAN_PLAN_PATTERN_CHECKER,
checkContext)
&& !checkContext.isContainsTopAggregate();
diff --git
a/regression-test/data/nereids_rules_p0/mv/agg_on_none_agg/agg_on_none_agg.out
b/regression-test/data/nereids_rules_p0/mv/agg_on_none_agg/agg_on_none_agg.out
new file mode 100644
index 00000000000..55d8a524c6e
--- /dev/null
+++
b/regression-test/data/nereids_rules_p0/mv/agg_on_none_agg/agg_on_none_agg.out
@@ -0,0 +1,155 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !query1_0_before --
+1 o 10
+1 o 100
+2 o 1
+2 o 101
+2 o 11
+
+-- !query1_0_after --
+1 o 10
+1 o 100
+2 o 1
+2 o 101
+2 o 11
+
+-- !query1_1_before --
+1 o 59874.14171519782
+1 o 7.307059979368067E43
+2 o 2.6881171418161356E43
+2 o 22026.465794806718
+2 o 5.92097202766467E47
+
+-- !query1_1_after --
+1 o 59874.14171519782
+1 o 7.307059979368067E43
+2 o 2.6881171418161356E43
+2 o 22026.465794806718
+2 o 5.92097202766467E47
+
+-- !query1_3_before --
+1 o 59874.14171519782 4
+1 o 7.307059979368067E43 4
+2 o 2.6881171418161356E43 4
+2 o 22026.465794806718 4
+2 o 5.92097202766467E47 6
+
+-- !query1_3_after --
+1 o 59874.14171519782 4
+1 o 7.307059979368067E43 4
+2 o 2.6881171418161356E43 4
+2 o 22026.465794806718 4
+2 o 5.92097202766467E47 6
+
+-- !query2_0_before --
+1 o 1 10
+1 o 2 100
+2 o 1 1
+2 o 1 11
+2 o 2 101
+
+-- !query2_0_after --
+1 o 1 10
+1 o 2 100
+2 o 1 1
+2 o 1 11
+2 o 2 101
+
+-- !query2_1_before --
+1 o 1 10 3
+1 o 2 100 3
+2 o 1 1 3
+2 o 1 11 4
+2 o 2 101 3
+
+-- !query2_1_after --
+1 o 1 10 3
+1 o 2 100 3
+2 o 1 1 3
+2 o 1 11 4
+2 o 2 101 3
+
+-- !query3_0_before --
+1 o 10
+1 o 100
+2 o 1
+2 o 101
+2 o 11
+
+-- !query3_0_after --
+1 o 10
+1 o 100
+2 o 1
+2 o 101
+2 o 11
+
+-- !query3_1_before --
+1 o 100
+2 o 101
+
+-- !query3_1_after --
+1 o 100
+2 o 101
+
+-- !query3_2_before --
+1 o 100
+2 o 101
+
+-- !query3_2_after --
+1 o 100
+2 o 101
+
+-- !query3_3_before --
+1 o 10
+1 o 100
+2 o 1
+2 o 11
+
+-- !query3_3_after --
+1 o 10
+1 o 100
+2 o 1
+2 o 11
+
+-- !query4_0_before --
+1 o 100
+2 o 101
+
+-- !query4_0_after --
+1 o 100
+2 o 101
+
+-- !query4_1_before --
+1 o 100 3
+
+-- !query4_1_after --
+1 o 100 3
+
+-- !query5_0_before --
+1 11.50 10
+1 43.20 100
+2 20.00 1
+2 46.00 11
+2 57.40 101
+
+-- !query5_0_after --
+1 11.50 10
+1 43.20 100
+2 20.00 1
+2 46.00 11
+2 57.40 101
+
+-- !query5_1_before --
+1 11.50 10
+1 43.20 100
+2 20.00 1
+2 46.00 11
+2 57.40 101
+
+-- !query5_1_after --
+1 11.50 10
+1 43.20 100
+2 20.00 1
+2 46.00 11
+2 57.40 101
+
diff --git
a/regression-test/suites/nereids_rules_p0/mv/agg_on_none_agg/agg_on_none_agg.groovy
b/regression-test/suites/nereids_rules_p0/mv/agg_on_none_agg/agg_on_none_agg.groovy
new file mode 100644
index 00000000000..2219354e417
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/agg_on_none_agg/agg_on_none_agg.groovy
@@ -0,0 +1,488 @@
+package mv.agg_on_none_agg
+// 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("agg_on_none_agg") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ sql "set runtime_filter_mode=OFF";
+ sql "SET ignore_shape_nodes='PhysicalDistribute,PhysicalProject'"
+
+ sql """
+ drop table if exists orders
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS orders (
+ o_orderkey INTEGER NOT NULL,
+ o_custkey INTEGER NOT NULL,
+ o_orderstatus CHAR(1) NOT NULL,
+ o_totalprice DECIMALV3(15,2) NOT NULL,
+ o_orderdate DATE NOT NULL,
+ o_orderpriority CHAR(15) NOT NULL,
+ o_clerk CHAR(15) NOT NULL,
+ o_shippriority INTEGER NOT NULL,
+ O_COMMENT VARCHAR(79) NOT NULL
+ )
+ DUPLICATE KEY(o_orderkey, o_custkey)
+ PARTITION BY RANGE(o_orderdate) (
+ PARTITION `day_2` VALUES LESS THAN ('2023-12-9'),
+ PARTITION `day_3` VALUES LESS THAN ("2023-12-11"),
+ PARTITION `day_4` VALUES LESS THAN ("2023-12-30")
+ )
+ DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql """
+ insert into orders values
+ (1, 1, 'o', 9.5, '2023-12-08', 'a', 'b', 1, 'yy'),
+ (1, 1, 'o', 10.5, '2023-12-08', 'a', 'b', 1, 'yy'),
+ (2, 1, 'o', 11.5, '2023-12-09', 'a', 'b', 1, 'yy'),
+ (3, 1, 'o', 12.5, '2023-12-10', 'a', 'b', 1, 'yy'),
+ (3, 1, 'o', 33.5, '2023-12-10', 'a', 'b', 1, 'yy'),
+ (4, 2, 'o', 43.2, '2023-12-11', 'c','d',2, 'mm'),
+ (5, 2, 'o', 56.2, '2023-12-12', 'c','d',2, 'mi'),
+ (5, 2, 'o', 1.2, '2023-12-12', 'c','d',2, 'mi');
+ """
+
+ sql """
+ drop table if exists lineitem
+ """
+
+ sql"""
+ CREATE TABLE IF NOT EXISTS lineitem (
+ l_orderkey INTEGER NOT NULL,
+ l_partkey INTEGER NOT NULL,
+ l_suppkey INTEGER NOT NULL,
+ l_linenumber INTEGER NOT NULL,
+ l_quantity DECIMALV3(15,2) NOT NULL,
+ l_extendedprice DECIMALV3(15,2) NOT NULL,
+ l_discount DECIMALV3(15,2) NOT NULL,
+ l_tax DECIMALV3(15,2) NOT NULL,
+ l_returnflag CHAR(1) NOT NULL,
+ l_linestatus CHAR(1) NOT NULL,
+ l_shipdate DATE NOT NULL,
+ l_commitdate DATE NOT NULL,
+ l_receiptdate DATE NOT NULL,
+ l_shipinstruct CHAR(25) NOT NULL,
+ l_shipmode CHAR(10) NOT NULL,
+ l_comment VARCHAR(44) NOT NULL
+ )
+ DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber)
+ PARTITION BY RANGE(l_shipdate) (
+ PARTITION `day_1` VALUES LESS THAN ('2023-12-9'),
+ PARTITION `day_2` VALUES LESS THAN ("2023-12-11"),
+ PARTITION `day_3` VALUES LESS THAN ("2023-12-30"))
+ DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ """
+
+ sql """ insert into lineitem values
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-12-08', '2023-12-09',
'2023-12-10', 'a', 'b', 'yyyyyyyyy'),
+ (2, 4, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-12-09', '2023-12-09',
'2023-12-10', 'a', 'b', 'yyyyyyyyy'),
+ (3, 2, 4, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-12-10', '2023-12-09',
'2023-12-10', 'a', 'b', 'yyyyyyyyy'),
+ (4, 3, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-12-11', '2023-12-09',
'2023-12-10', 'a', 'b', 'yyyyyyyyy'),
+ (5, 2, 3, 6, 7.5, 8.5, 9.5, 10.5, 'k', 'o', '2023-12-12', '2023-12-12',
'2023-12-13', 'c', 'd', 'xxxxxxxxx');
+ """
+
+ sql """
+ drop table if exists partsupp
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS partsupp (
+ ps_partkey INTEGER NOT NULL,
+ ps_suppkey INTEGER NOT NULL,
+ ps_availqty INTEGER NOT NULL,
+ ps_supplycost DECIMALV3(15,2) NOT NULL,
+ ps_comment VARCHAR(199) NOT NULL
+ )
+ DUPLICATE KEY(ps_partkey, ps_suppkey)
+ DISTRIBUTED BY HASH(ps_partkey) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ """
+
+ sql """
+ insert into partsupp values
+ (2, 3, 9, 10.01, 'supply1'),
+ (2, 3, 10, 11.01, 'supply2');
+ """
+
+ sql """analyze table orders with sync;"""
+ sql """analyze table lineitem with sync;"""
+ sql """analyze table partsupp with sync;"""
+
+ def check_rewrite_but_not_chose = { mv_sql, query_sql, mv_name ->
+
+ sql """DROP MATERIALIZED VIEW IF EXISTS ${mv_name}"""
+ sql"""
+ CREATE MATERIALIZED VIEW ${mv_name}
+ BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS ${mv_sql}
+ """
+
+ def job_name = getJobName(db, mv_name);
+ waitingMTMVTaskFinished(job_name)
+ explain {
+ sql("${query_sql}")
+ check {result ->
+ def splitResult = result.split("MaterializedViewRewriteFail")
+ splitResult.length == 2 ? splitResult[0].contains(mv_name) :
false
+ }
+ }
+ }
+
+ // query used expression is in mv
+ def mv1_0 = """
+ select case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end,
+ o_orderstatus,
+ bin(o_orderkey)
+ from orders;
+ """
+ def query1_0 = """
+ select
+ count(case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end),
+ o_orderstatus,
+ bin(o_orderkey)
+ from orders
+ group by
+ o_orderstatus,
+ bin(o_orderkey);
+ """
+ order_qt_query1_0_before "${query1_0}"
+ check_mv_rewrite_success(db, mv1_0, query1_0, "mv1_0")
+ order_qt_query1_0_after "${query1_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_0"""
+
+
+ def mv1_1 = """
+ select case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end,
+ o_orderstatus,
+ o_orderkey + 1
+ from orders;
+ """
+ def query1_1 = """
+ select
+ count(case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end),
+ o_orderstatus,
+ exp(bin(o_orderkey + 1))
+ from orders
+ group by
+ o_orderstatus,
+ exp(bin(o_orderkey + 1));
+ """
+ order_qt_query1_1_before "${query1_1}"
+ check_mv_rewrite_success(db, mv1_1, query1_1, "mv1_1")
+ order_qt_query1_1_after "${query1_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_1"""
+
+ def mv1_3 = """
+ select case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end,
+ o_orderstatus,
+ o_orderkey + 1,
+ l_linenumber
+ from orders
+ left join lineitem on o_orderkey = l_orderkey;
+ """
+ def query1_3 = """
+ select
+ count(case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end),
+ o_orderstatus,
+ exp(bin(o_orderkey + 1)),
+ l_linenumber
+ from orders
+ left join lineitem on o_orderkey = l_orderkey
+ group by
+ l_linenumber,
+ o_orderstatus,
+ exp(bin(o_orderkey + 1));
+ """
+ order_qt_query1_3_before "${query1_3}"
+ check_mv_rewrite_success(db, mv1_3, query1_3, "mv1_3")
+ order_qt_query1_3_after "${query1_3}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_3"""
+
+ // query use expression is not in mv
+ def mv2_0 = """
+ select case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end,
+ o_orderstatus,
+ bin(o_orderkey),
+ l_linenumber
+ from orders
+ left join lineitem on o_orderkey = l_orderkey;
+ """
+ def query2_0 = """
+ select
+ count(case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end),
+ o_orderstatus,
+ o_shippriority,
+ bin(o_orderkey)
+ from orders
+ group by
+ o_orderstatus,
+ bin(o_orderkey),
+ o_shippriority;
+ """
+ order_qt_query2_0_before "${query2_0}"
+ check_mv_rewrite_fail(db, mv2_0, query2_0, "mv2_0")
+ order_qt_query2_0_after "${query2_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_0"""
+
+
+ def mv2_1 = """
+ select case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end,
+ o_orderstatus,
+ bin(o_orderkey)
+ from orders;
+ """
+ def query2_1 = """
+ select
+ count(case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end),
+ o_orderstatus,
+ o_shippriority,
+ bin(o_orderkey),
+ l_suppkey
+ from orders
+ left join lineitem on o_orderkey = l_orderkey
+ group by
+ o_orderstatus,
+ bin(o_orderkey),
+ o_shippriority,
+ l_suppkey;
+ """
+ order_qt_query2_1_before "${query2_1}"
+ check_mv_rewrite_fail(db, mv2_1, query2_1, "mv2_1")
+ order_qt_query2_1_after "${query2_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_1"""
+
+
+ // query use filter and view doesn't use filter
+ def mv3_0 = """
+ select case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end,
+ o_orderstatus,
+ bin(o_orderkey)
+ from orders;
+ """
+ def query3_0 = """
+ select
+ count(case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end),
+ o_orderstatus,
+ bin(o_orderkey)
+ from orders
+ where o_orderstatus = 'o'
+ group by
+ o_orderstatus,
+ bin(o_orderkey);
+ """
+ order_qt_query3_0_before "${query3_0}"
+ check_mv_rewrite_success(db, mv3_0, query3_0, "mv3_0")
+ order_qt_query3_0_after "${query3_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_0"""
+
+
+ def mv3_1 = """
+ select case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end,
+ o_orderstatus,
+ bin(o_orderkey)
+ from orders;
+ """
+ def query3_1 = """
+ select
+ count(case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end),
+ o_orderstatus,
+ bin(o_orderkey)
+ from orders
+ where o_shippriority = 2
+ group by
+ o_orderstatus,
+ bin(o_orderkey);
+ """
+ order_qt_query3_1_before "${query3_1}"
+ // the filter slot used in query can not be found from mv
+ check_mv_rewrite_fail(db, mv3_1, query3_1, "mv3_1")
+ order_qt_query3_1_after "${query3_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_1"""
+
+
+ def mv3_2 = """
+ select case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end,
+ o_orderstatus,
+ bin(o_orderkey)
+ from orders;
+ """
+ def query3_2 = """
+ select
+ count(case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end) as count_case,
+ o_orderstatus,
+ bin(o_orderkey)
+ from orders
+ where o_shippriority = 2
+ group by
+ o_orderstatus,
+ bin(o_orderkey)
+ having count_case > 0;
+ """
+ order_qt_query3_2_before "${query3_2}"
+ // the filter slot used in query can not be found from mv
+ check_mv_rewrite_fail(db, mv3_2, query3_2, "mv3_2")
+ order_qt_query3_2_after "${query3_2}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_2"""
+
+
+ def mv3_3 = """
+ select case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end,
+ o_orderstatus,
+ bin(o_orderkey),
+ l_suppkey,
+ l_linenumber
+ from orders
+ left join lineitem on o_orderkey = l_orderkey;
+ """
+ def query3_3 = """
+ select
+ count(case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end) as count_case,
+ o_orderstatus,
+ bin(o_orderkey)
+ from orders
+ left join lineitem on o_orderkey = l_orderkey
+ where l_linenumber = 4
+ group by
+ o_orderstatus,
+ bin(o_orderkey);
+ """
+ order_qt_query3_3_before "${query3_3}"
+ // the filter slot used in query can not be found from mv
+ check_mv_rewrite_success(db, mv3_3, query3_3, "mv3_3")
+ order_qt_query3_3_after "${query3_3}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_3"""
+
+
+
+ // both query and view use filter
+ def mv4_0 = """
+ select case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end,
+ o_orderstatus,
+ bin(o_orderkey)
+ from orders
+ where o_shippriority = 2;
+ """
+ def query4_0 = """
+ select
+ count(case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end),
+ o_orderstatus,
+ bin(o_orderkey)
+ from orders
+ where o_shippriority = 2 and o_orderstatus = 'o'
+ group by
+ o_orderstatus,
+ bin(o_orderkey);
+ """
+ order_qt_query4_0_before "${query4_0}"
+ check_mv_rewrite_success(db, mv4_0, query4_0, "mv4_0")
+ order_qt_query4_0_after "${query4_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv4_0"""
+
+
+ def mv4_1 = """
+ select case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end,
+ o_orderstatus,
+ bin(o_orderkey),
+ l_partkey
+ from orders
+ left join lineitem on o_orderkey = l_orderkey
+ where o_shippriority = 2 and l_linenumber = 4;
+ """
+ def query4_1 = """
+ select
+ count(case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end),
+ o_orderstatus,
+ bin(o_orderkey),
+ l_partkey
+ from orders
+ left join lineitem on o_orderkey = l_orderkey
+ where o_shippriority = 2 and o_orderstatus = 'o' and l_linenumber
= 4
+ group by
+ o_orderstatus,
+ bin(o_orderkey),
+ l_partkey;
+ """
+ order_qt_query4_1_before "${query4_1}"
+ check_mv_rewrite_success(db, mv4_1, query4_1, "mv4_1")
+ order_qt_query4_1_after "${query4_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv4_1"""
+
+ // query use less dimension in select then group by
+
+ def mv5_0 = """
+ select case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end,
+ o_orderstatus,
+ o_totalprice,
+ bin(o_orderkey)
+ from orders;
+ """
+ def query5_0 = """
+ select
+ count(case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end),
+ sum(o_totalprice),
+ bin(o_orderkey)
+ from orders
+ group by
+ o_orderstatus,
+ bin(o_orderkey);
+ """
+ order_qt_query5_0_before "${query5_0}"
+ check_mv_rewrite_success(db, mv5_0, query5_0, "mv5_0")
+ order_qt_query5_0_after "${query5_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv5_0"""
+
+
+
+ def mv5_1 = """
+ select case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end,
+ o_orderstatus,
+ o_totalprice,
+ bin(o_orderkey),
+ l_linenumber
+ from orders
+ left join lineitem on o_orderkey = l_orderkey;
+ """
+ def query5_1 = """
+ select
+ count(case when o_shippriority > 1 and o_orderkey IN (4, 5) then
o_custkey else o_shippriority end),
+ sum(o_totalprice),
+ bin(o_orderkey)
+ from orders
+ left join lineitem on o_orderkey = l_orderkey
+ group by
+ o_orderstatus,
+ bin(o_orderkey),
+ l_linenumber;
+ """
+ order_qt_query5_1_before "${query5_1}"
+ check_mv_rewrite_success(db, mv5_1, query5_1, "mv5_1")
+ order_qt_query5_1_after "${query5_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv5_1"""
+}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up.groovy
b/regression-test/suites/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up.groovy
index 72a6144bcda..83842996c29 100644
---
a/regression-test/suites/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up.groovy
+++
b/regression-test/suites/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up.groovy
@@ -835,7 +835,7 @@ suite("aggregate_without_roll_up") {
order_qt_query19_3_before "${query19_3}"
check_mv_rewrite_success(db, mv19_3, query19_3, "mv19_3")
order_qt_query19_3_after "${query19_3}"
- sql """ DROP MATERIALIZED VIEW IF EXISTS mv19_0"""
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv19_3"""
// without group, scalar aggregate
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
