This is an automated email from the ASF dual-hosted git repository.
morrysnow 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 4e48e19ca74 [Improvement](Nereids) Support to query rewrite by
materialized view when join input has aggregate (#30230)
4e48e19ca74 is described below
commit 4e48e19ca74bb5483a9623578addcadd660712fb
Author: seawinde <[email protected]>
AuthorDate: Wed Jan 24 10:46:50 2024 +0800
[Improvement](Nereids) Support to query rewrite by materialized view when
join input has aggregate (#30230)
Support to query rewrite by materialized view when join input has
aggregate, the aggregate should be simple
For example as following:
The materialized view def is
> select
> l_linenumber,
> count(distinct l_orderkey),
> sum(case when l_orderkey in (1,2,3) then l_suppkey *
l_linenumber else 0 end),
> max(case when l_orderkey in (4, 5) then (l_quantity *2 +
part_supp_a.qty_max) * 0.88 else 100 end),
> avg(case when l_partkey in (2, 3, 4) then l_discount +
o_totalprice + part_supp_a.qty_sum else 50 end)
> from lineitem
> left join orders on l_orderkey = o_orderkey
> left join
> (select ps_partkey, ps_suppkey, sum(ps_availqty) qty_sum,
max(ps_availqty) qty_max,
> min(ps_availqty) qty_min,
> avg(ps_supplycost) cost_avg
> from partsupp
> group by ps_partkey,ps_suppkey) part_supp_a
> on l_partkey = part_supp_a.ps_partkey
> and l_suppkey = part_supp_a.ps_suppkey
> group by l_linenumber;
when query is like following, it can be rewritten by mv above
> select
> l_linenumber,
> sum(case when l_orderkey in (1,2,3) then l_suppkey *
l_linenumber else 0 end),
> avg(case when l_partkey in (2, 3, 4) then l_discount +
o_totalprice + part_supp_a.qty_sum else 50 end)
> from lineitem
> left join orders on l_orderkey = o_orderkey
> left join
> (select ps_partkey, ps_suppkey, sum(ps_availqty) qty_sum,
max(ps_availqty) qty_max,
> min(ps_availqty) qty_min,
> avg(ps_supplycost) cost_avg
> from partsupp
> group by ps_partkey,ps_suppkey) part_supp_a
> on l_partkey = part_supp_a.ps_partkey
> and l_suppkey = part_supp_a.ps_suppkey
> group by l_linenumber;
---
.../joinorder/hypergraph/node/StructInfoNode.java | 68 ++++++++---
.../mv/AbstractMaterializedViewAggregateRule.java | 2 +-
.../mv/AbstractMaterializedViewRule.java | 21 ++--
.../rules/exploration/mv/ComparisonResult.java | 36 ++++--
.../rules/exploration/mv/HyperGraphComparator.java | 4 +
.../nereids/rules/exploration/mv/StructInfo.java | 24 +++-
.../mv/agg_with_roll_up/aggregate_with_roll_up.out | 8 ++
.../aggregate_without_roll_up.out | 24 ++++
.../mv/join/left_outer/outer_join.out | 8 ++
.../agg_with_roll_up/aggregate_with_roll_up.groovy | 46 +++++++
.../aggregate_without_roll_up.groovy | 135 ++++++++++++++++++++-
.../mv/join/dphyp_outer/outer_join_dphyp.groovy | 2 +-
.../mv/join/left_outer/outer_join.groovy | 118 ++++++++++++++++--
13 files changed, 441 insertions(+), 55 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/joinorder/hypergraph/node/StructInfoNode.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/joinorder/hypergraph/node/StructInfoNode.java
index e32baba6a58..93e13e59da5 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/joinorder/hypergraph/node/StructInfoNode.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/joinorder/hypergraph/node/StructInfoNode.java
@@ -17,19 +17,21 @@
package org.apache.doris.nereids.jobs.joinorder.hypergraph.node;
+import org.apache.doris.common.Pair;
import org.apache.doris.nereids.jobs.joinorder.hypergraph.edge.Edge;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.plans.GroupPlan;
-import org.apache.doris.nereids.trees.plans.LeafPlan;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.algebra.CatalogRelation;
import org.apache.doris.nereids.trees.plans.logical.LogicalAggregate;
import org.apache.doris.nereids.trees.plans.logical.LogicalCatalogRelation;
import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.visitor.DefaultPlanVisitor;
import org.apache.doris.nereids.util.Utils;
import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableList.Builder;
import com.google.common.collect.ImmutableSet;
import java.util.ArrayList;
@@ -57,24 +59,50 @@ public class StructInfoNode extends AbstractNode {
}
private @Nullable List<Set<Expression>> collectExpressions(Plan plan) {
- if (plan instanceof LeafPlan) {
- return ImmutableList.of();
- }
- List<Set<Expression>> childExpressions =
collectExpressions(plan.child(0));
- if (!isValidNodePlan(plan) || childExpressions == null) {
- return null;
- }
- if (plan instanceof LogicalAggregate) {
- return ImmutableList.<Set<Expression>>builder()
- .add(ImmutableSet.copyOf(plan.getExpressions()))
- .add(ImmutableSet.copyOf(((LogicalAggregate<?>)
plan).getGroupByExpressions()))
- .addAll(childExpressions)
- .build();
- }
- return ImmutableList.<Set<Expression>>builder()
- .add(ImmutableSet.copyOf(plan.getExpressions()))
- .addAll(childExpressions)
- .build();
+
+ Pair<Boolean, Builder<Set<Expression>>> collector = Pair.of(true,
ImmutableList.builder());
+ plan.accept(new DefaultPlanVisitor<Void, Pair<Boolean,
ImmutableList.Builder<Set<Expression>>>>() {
+ @Override
+ public Void visitLogicalAggregate(LogicalAggregate<? extends Plan>
aggregate,
+ Pair<Boolean, ImmutableList.Builder<Set<Expression>>>
collector) {
+ if (!collector.key()) {
+ return null;
+ }
+
collector.value().add(ImmutableSet.copyOf(aggregate.getExpressions()));
+
collector.value().add(ImmutableSet.copyOf(((LogicalAggregate<?>)
plan).getGroupByExpressions()));
+ return super.visit(aggregate, collector);
+ }
+
+ @Override
+ public Void visitLogicalFilter(LogicalFilter<? extends Plan>
filter,
+ Pair<Boolean, ImmutableList.Builder<Set<Expression>>>
collector) {
+ if (!collector.key()) {
+ return null;
+ }
+
collector.value().add(ImmutableSet.copyOf(filter.getExpressions()));
+ return super.visit(filter, collector);
+ }
+
+ @Override
+ public Void visitGroupPlan(GroupPlan groupPlan,
+ Pair<Boolean, ImmutableList.Builder<Set<Expression>>>
collector) {
+ if (!collector.key()) {
+ return null;
+ }
+ Plan groupActualPlan =
groupPlan.getGroup().getLogicalExpressions().get(0).getPlan();
+ return groupActualPlan.accept(this, collector);
+ }
+
+ @Override
+ public Void visit(Plan plan, Pair<Boolean,
ImmutableList.Builder<Set<Expression>>> context) {
+ if (!isValidNodePlan(plan)) {
+ context.first = false;
+ return null;
+ }
+ return super.visit(plan, context);
+ }
+ }, collector);
+ return collector.key() ? collector.value().build() : null;
}
private boolean isValidNodePlan(Plan plan) {
@@ -104,7 +132,7 @@ public class StructInfoNode extends AbstractNode {
private static Plan extractPlan(Plan plan) {
if (plan instanceof GroupPlan) {
- //TODO: Note mv can be in logicalExpression, how can we choose it
+ // TODO: Note mv can be in logicalExpression, how can we choose it
plan = ((GroupPlan) plan).getGroup().getLogicalExpressions().get(0)
.getPlan();
}
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 48b900ca745..34f64ecfbc3 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
@@ -101,7 +101,7 @@ public abstract class AbstractMaterializedViewAggregateRule
extends AbstractMate
Pair<Plan, LogicalAggregate<Plan>> viewTopPlanAndAggPair =
splitToTopPlanAndAggregate(viewStructInfo);
if (viewTopPlanAndAggPair == null) {
materializationContext.recordFailReason(queryStructInfo.getOriginalPlanId(),
- Pair.of("Split view to top plan and agg fail",
+ Pair.of("Split view to top plan and agg fail, view doesn't
not contain aggregate",
String.format("view plan = %s\n",
viewStructInfo.getOriginalPlan().treeString())));
return null;
}
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 ea035769b60..57e06d4e16e 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
@@ -186,11 +186,13 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
materializationContext.recordFailReason(queryStructInfo.getOriginalPlanId(),
Pair.of("Predicate compensate fail",
String.format("query predicates = %s,\n query
equivalenceClass = %s, \n"
- + "view predicates = %s,\n
query equivalenceClass = %s\n",
+ + "view predicates = %s,\n
query equivalenceClass = %s\n"
+ + "comparisonResult = %s ",
queryStructInfo.getPredicates(),
queryStructInfo.getEquivalenceClass(),
viewStructInfo.getPredicates(),
-
viewStructInfo.getEquivalenceClass())));
+ viewStructInfo.getEquivalenceClass(),
+ comparisonResult)));
continue;
}
Plan rewrittenPlan;
@@ -467,21 +469,22 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
Set<Set<Slot>> requireNoNullableViewSlot =
comparisonResult.getViewNoNullableSlot();
// check query is use the null reject slot which view comparison need
if (!requireNoNullableViewSlot.isEmpty()) {
- Set<Expression> queryPulledUpPredicates =
queryStructInfo.getPredicates().getPulledUpPredicates();
+ Set<Expression> queryPulledUpPredicates =
comparisonResult.getQueryAllPulledUpExpressions().stream()
+ .flatMap(expr ->
ExpressionUtils.extractConjunction(expr).stream())
+ .collect(Collectors.toSet());
Set<Expression> nullRejectPredicates =
ExpressionUtils.inferNotNull(queryPulledUpPredicates,
cascadesContext);
- if (nullRejectPredicates.isEmpty() ||
queryPulledUpPredicates.containsAll(nullRejectPredicates)) {
- // query has not null reject predicates, so return
- return SplitPredicate.INVALID_INSTANCE;
- }
SlotMapping queryToViewMapping = viewToQuerySlotMapping.inverse();
Set<Expression> queryUsedNeedRejectNullSlotsViewBased =
nullRejectPredicates.stream()
.map(expression ->
TypeUtils.isNotNull(expression).orElse(null))
.filter(Objects::nonNull)
.map(expr -> ExpressionUtils.replace((Expression) expr,
queryToViewMapping.toSlotReferenceMap()))
.collect(Collectors.toSet());
- if (requireNoNullableViewSlot.stream().anyMatch(
- set -> Sets.intersection(set,
queryUsedNeedRejectNullSlotsViewBased).isEmpty())) {
+ // query pulledUp predicates should have null reject predicates
and contains any require noNullable slot
+ boolean valid =
!queryPulledUpPredicates.containsAll(nullRejectPredicates)
+ && requireNoNullableViewSlot.stream().noneMatch(
+ set -> Sets.intersection(set,
queryUsedNeedRejectNullSlotsViewBased).isEmpty());
+ if (!valid) {
return SplitPredicate.INVALID_INSTANCE;
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/ComparisonResult.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/ComparisonResult.java
index 48856f7f491..c60eb9f94d3 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/ComparisonResult.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/ComparisonResult.java
@@ -35,20 +35,23 @@ public class ComparisonResult {
private final boolean valid;
private final List<Expression> viewExpressions;
private final List<Expression> queryExpressions;
+ private final List<Expression> queryAllPulledUpExpressions;
private final Set<Set<Slot>> viewNoNullableSlot;
private final String errorMessage;
- ComparisonResult(List<Expression> queryExpressions, List<Expression>
viewExpressions,
- Set<Set<Slot>> viewNoNullableSlot, boolean valid, String message) {
+ ComparisonResult(List<Expression> queryExpressions, List<Expression>
queryAllPulledUpExpressions,
+ List<Expression> viewExpressions, Set<Set<Slot>>
viewNoNullableSlot, boolean valid, String message) {
this.viewExpressions = ImmutableList.copyOf(viewExpressions);
this.queryExpressions = ImmutableList.copyOf(queryExpressions);
+ this.queryAllPulledUpExpressions =
ImmutableList.copyOf(queryAllPulledUpExpressions);
this.viewNoNullableSlot = ImmutableSet.copyOf(viewNoNullableSlot);
this.valid = valid;
this.errorMessage = message;
}
public static ComparisonResult newInvalidResWithErrorMessage(String
errorMessage) {
- return new ComparisonResult(ImmutableList.of(), ImmutableList.of(),
ImmutableSet.of(), false, errorMessage);
+ return new ComparisonResult(ImmutableList.of(), ImmutableList.of(),
ImmutableList.of(),
+ ImmutableSet.of(), false, errorMessage);
}
public List<Expression> getViewExpressions() {
@@ -59,6 +62,10 @@ public class ComparisonResult {
return queryExpressions;
}
+ public List<Expression> getQueryAllPulledUpExpressions() {
+ return queryAllPulledUpExpressions;
+ }
+
public Set<Set<Slot>> getViewNoNullableSlot() {
return viewNoNullableSlot;
}
@@ -78,6 +85,7 @@ public class ComparisonResult {
ImmutableList.Builder<Expression> queryBuilder = new
ImmutableList.Builder<>();
ImmutableList.Builder<Expression> viewBuilder = new
ImmutableList.Builder<>();
ImmutableSet.Builder<Set<Slot>> viewNoNullableSlotBuilder = new
ImmutableSet.Builder<>();
+ ImmutableList.Builder<Expression> queryAllPulledUpExpressionsBuilder =
new ImmutableList.Builder<>();
boolean valid = true;
/**
@@ -108,25 +116,29 @@ public class ComparisonResult {
return this;
}
+ public Builder addQueryAllPulledUpExpressions(Collection<? extends
Expression> expressions) {
+ queryAllPulledUpExpressionsBuilder.addAll(expressions);
+ return this;
+ }
+
public boolean isInvalid() {
return !valid;
}
public ComparisonResult build() {
Preconditions.checkArgument(valid, "Comparison result must be
valid");
- return new ComparisonResult(queryBuilder.build(),
viewBuilder.build(),
- viewNoNullableSlotBuilder.build(), valid, "");
+ return new ComparisonResult(queryBuilder.build(),
queryAllPulledUpExpressionsBuilder.build(),
+ viewBuilder.build(), viewNoNullableSlotBuilder.build(),
valid, "");
}
}
@Override
public String toString() {
- if (isInvalid()) {
- return "INVALID";
- }
- return String.format("viewExpressions: %s \n "
- + "queryExpressions :%s \n "
- + "viewNoNullableSlot :%s \n",
- viewExpressions, queryExpressions, viewNoNullableSlot);
+ return String.format("valid: %s \n "
+ + "viewExpressions: %s \n "
+ + "queryExpressions :%s \n "
+ + "viewNoNullableSlot :%s \n"
+ + "queryAllPulledUpExpressions :%s \n", valid,
viewExpressions, queryExpressions,
+ viewNoNullableSlot, queryAllPulledUpExpressions);
}
}
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 bf48926348f..b652cd3fc37 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
@@ -168,6 +168,10 @@ public class HyperGraphComparator {
for (Pair<JoinType, Set<Slot>> inferredCond :
inferredViewEdgeWithCond.values()) {
builder.addViewNoNullableSlot(inferredCond.second);
}
+ builder.addQueryAllPulledUpExpressions(
+ getQueryFilterEdges().stream()
+ .filter(this::canPullUp)
+ .flatMap(filter ->
filter.getExpressions().stream()).collect(Collectors.toList()));
return builder.build();
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/StructInfo.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/StructInfo.java
index 62f325a05d4..c9c086df431 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/StructInfo.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/StructInfo.java
@@ -170,6 +170,8 @@ public class StructInfo {
}
// Collect relations from hyper graph which in the bottom plan
hyperGraph.getNodes().forEach(node -> {
+ // plan relation collector and set to map
+ StructInfoNode structInfoNode = (StructInfoNode) node;
// plan relation collector and set to map
Plan nodePlan = node.getPlan();
List<CatalogRelation> nodeRelations = new ArrayList<>();
@@ -177,6 +179,24 @@ public class StructInfo {
relationBuilder.addAll(nodeRelations);
// every node should only have one relation, this is for
LogicalCompatibilityContext
relationIdStructInfoNodeMap.put(nodeRelations.get(0).getRelationId(),
(StructInfoNode) node);
+
+ // record expressions in node
+ if (structInfoNode.getExpressions() != null) {
+ structInfoNode.getExpressions().forEach(expression -> {
+ ExpressionLineageReplacer.ExpressionReplaceContext
replaceContext =
+ new
ExpressionLineageReplacer.ExpressionReplaceContext(
+ Lists.newArrayList(expression),
+ ImmutableSet.of(),
+ ImmutableSet.of());
+ topPlan.accept(ExpressionLineageReplacer.INSTANCE,
replaceContext);
+ // Replace expressions by expression map
+ List<Expression> replacedExpressions =
replaceContext.getReplacedExpressions();
+
shuttledHashConjunctsToConjunctsMap.put(replacedExpressions.get(0), expression);
+ // Record this, will be used in top level expression
shuttle later, see the method
+ // ExpressionLineageReplacer#visitGroupPlan
+
namedExprIdAndExprMapping.putAll(replaceContext.getExprIdExpressionMap());
+ });
+ }
});
// Collect expression from where in hyper graph
hyperGraph.getFilterEdges().forEach(filterEdge -> {
@@ -436,7 +456,9 @@ public class StructInfo {
if (!(plan instanceof Filter)
&& !(plan instanceof Project)
&& !(plan instanceof CatalogRelation)
- && !(plan instanceof Join)) {
+ && !(plan instanceof Join)
+ && !(plan instanceof LogicalAggregate &&
!((LogicalAggregate) plan).getSourceRepeat()
+ .isPresent())) {
return false;
}
if (plan instanceof Join) {
diff --git
a/regression-test/data/nereids_rules_p0/mv/agg_with_roll_up/aggregate_with_roll_up.out
b/regression-test/data/nereids_rules_p0/mv/agg_with_roll_up/aggregate_with_roll_up.out
index 17104fbd732..e5cc57b5c3d 100644
---
a/regression-test/data/nereids_rules_p0/mv/agg_with_roll_up/aggregate_with_roll_up.out
+++
b/regression-test/data/nereids_rules_p0/mv/agg_with_roll_up/aggregate_with_roll_up.out
@@ -261,3 +261,11 @@
-- !query29_1_after --
0 178.10 1.20 8
+-- !query30_0_before --
+4 4 68 100.0000 36.5000
+6 1 0 22.0000 57.2000
+
+-- !query30_0_after --
+4 4 68 100.0000 36.5000
+6 1 0 22.0000 57.2000
+
diff --git
a/regression-test/data/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up.out
b/regression-test/data/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up.out
index cc6e4730fdf..f2babef7b78 100644
---
a/regression-test/data/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up.out
+++
b/regression-test/data/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up.out
@@ -197,3 +197,27 @@ d c 17.00 2
b a 39.00 6
d c 17.00 2
+-- !query21_0_before --
+4 4 68 100.0000 36.5000
+6 1 0 22.0000 57.2000
+
+-- !query21_0_after --
+4 4 68 100.0000 36.5000
+6 1 0 22.0000 57.2000
+
+-- !query21_1_before --
+4 4 92 100.0000 27.0000
+6 1 0 22.0000 47.7000
+
+-- !query21_1_after --
+4 4 92 100.0000 27.0000
+6 1 0 22.0000 47.7000
+
+-- !query21_2_before --
+4 4 68 100.0000 36.5000
+6 1 0 22.0000 57.2000
+
+-- !query21_2_after --
+4 4 68 100.0000 36.5000
+6 1 0 22.0000 57.2000
+
diff --git
a/regression-test/data/nereids_rules_p0/mv/join/left_outer/outer_join.out
b/regression-test/data/nereids_rules_p0/mv/join/left_outer/outer_join.out
index 845ef3933dc..73c5193f136 100644
--- a/regression-test/data/nereids_rules_p0/mv/join/left_outer/outer_join.out
+++ b/regression-test/data/nereids_rules_p0/mv/join/left_outer/outer_join.out
@@ -271,6 +271,14 @@
3 3 2023-12-11
4 3 2023-12-09
+-- !query6_1_before --
+2023-12-10 2023-12-10 2 4 3
+2023-12-10 2023-12-10 2 4 3
+
+-- !query6_1_after --
+2023-12-10 2023-12-10 2 4 3
+2023-12-10 2023-12-10 2 4 3
+
-- !query7_0_before --
3 3 2023-12-11
diff --git
a/regression-test/suites/nereids_rules_p0/mv/agg_with_roll_up/aggregate_with_roll_up.groovy
b/regression-test/suites/nereids_rules_p0/mv/agg_with_roll_up/aggregate_with_roll_up.groovy
index 4d001af4128..3c60301feb6 100644
---
a/regression-test/suites/nereids_rules_p0/mv/agg_with_roll_up/aggregate_with_roll_up.groovy
+++
b/regression-test/suites/nereids_rules_p0/mv/agg_with_roll_up/aggregate_with_roll_up.groovy
@@ -1121,4 +1121,50 @@ suite("aggregate_with_roll_up") {
check_rewrite(mv29_1, query29_1, "mv29_1")
order_qt_query29_1_after "${query29_1}"
sql """ DROP MATERIALIZED VIEW IF EXISTS mv29_1"""
+
+ // join input has simple agg, simple agg which can not contains rollup,
cube
+ // can not rewrite, because avg doesn't support roll up now
+ def mv30_0 = """
+ select
+ l_linenumber,
+ l_quantity,
+ count(distinct l_orderkey),
+ sum(case when l_orderkey in (1,2,3) then l_suppkey * l_linenumber
else 0 end),
+ max(case when l_orderkey in (4, 5) then (l_quantity *2 +
part_supp_a.qty_max) * 0.88 else 100 end),
+ avg(case when l_partkey in (2, 3, 4) then l_discount +
o_totalprice + part_supp_a.qty_sum else 50 end)
+ from lineitem
+ left join orders on l_orderkey = o_orderkey
+ left join
+ (select ps_partkey, ps_suppkey, sum(ps_availqty) qty_sum,
max(ps_availqty) qty_max,
+ min(ps_availqty) qty_min,
+ avg(ps_supplycost) cost_avg
+ from partsupp
+ group by ps_partkey,ps_suppkey) part_supp_a
+ on l_partkey = part_supp_a.ps_partkey
+ and l_suppkey = part_supp_a.ps_suppkey
+ group by l_linenumber, l_quantity;
+ """
+ def query30_0 = """
+ select
+ l_linenumber,
+ count(distinct l_orderkey),
+ sum(case when l_orderkey in (1,2,3) then l_suppkey * l_linenumber
else 0 end),
+ max(case when l_orderkey in (4, 5) then (l_quantity *2 +
part_supp_a.qty_max) * 0.88 else 100 end),
+ avg(case when l_partkey in (2, 3, 4) then l_discount +
o_totalprice + part_supp_a.qty_sum else 50 end)
+ from lineitem
+ left join orders on l_orderkey = o_orderkey
+ left join
+ (select ps_partkey, ps_suppkey, sum(ps_availqty) qty_sum,
max(ps_availqty) qty_max,
+ min(ps_availqty) qty_min,
+ avg(ps_supplycost) cost_avg
+ from partsupp
+ group by ps_partkey,ps_suppkey) part_supp_a
+ on l_partkey = part_supp_a.ps_partkey
+ and l_suppkey = part_supp_a.ps_suppkey
+ group by l_linenumber;
+ """
+ order_qt_query30_0_before "${query30_0}"
+ check_not_match(mv30_0, query30_0, "mv30_0")
+ order_qt_query30_0_after "${query30_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv30_0"""
}
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 b254fe6ac30..8674d575fd9 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
@@ -830,7 +830,7 @@ suite("aggregate_without_roll_up") {
from lineitem
left join
orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY
- group by
+ group by
l_shipmode,
l_shipinstruct;
"""
@@ -876,4 +876,137 @@ suite("aggregate_without_roll_up") {
check_not_match(mv20_2, query20_2, "mv20_2")
order_qt_query20_2_after "${query20_2}"
sql """ DROP MATERIALIZED VIEW IF EXISTS mv20_2"""
+
+ // join input has simple agg, simple agg which can not contains rollup,
cube
+ def mv21_0 = """
+ select
+ l_linenumber,
+ count(distinct l_orderkey),
+ sum(case when l_orderkey in (1,2,3) then l_suppkey * l_linenumber
else 0 end),
+ max(case when l_orderkey in (4, 5) then (l_quantity *2 +
part_supp_a.qty_max) * 0.88 else 100 end),
+ avg(case when l_partkey in (2, 3, 4) then l_discount +
o_totalprice + part_supp_a.qty_sum else 50 end)
+ from lineitem
+ left join orders on l_orderkey = o_orderkey
+ left join
+ (select ps_partkey, ps_suppkey, sum(ps_availqty) qty_sum,
max(ps_availqty) qty_max,
+ min(ps_availqty) qty_min,
+ avg(ps_supplycost) cost_avg
+ from partsupp
+ group by ps_partkey,ps_suppkey) part_supp_a
+ on l_partkey = part_supp_a.ps_partkey
+ and l_suppkey = part_supp_a.ps_suppkey
+ group by l_linenumber;
+ """
+ def query21_0 = """
+ select
+ l_linenumber,
+ count(distinct l_orderkey),
+ sum(case when l_orderkey in (1,2,3) then l_suppkey * l_linenumber
else 0 end),
+ max(case when l_orderkey in (4, 5) then (l_quantity *2 +
part_supp_a.qty_max) * 0.88 else 100 end),
+ avg(case when l_partkey in (2, 3, 4) then l_discount +
o_totalprice + part_supp_a.qty_sum else 50 end)
+ from lineitem
+ left join orders on l_orderkey = o_orderkey
+ left join
+ (select ps_partkey, ps_suppkey, sum(ps_availqty) qty_sum,
max(ps_availqty) qty_max,
+ min(ps_availqty) qty_min,
+ avg(ps_supplycost) cost_avg
+ from partsupp
+ group by ps_partkey,ps_suppkey) part_supp_a
+ on l_partkey = part_supp_a.ps_partkey
+ and l_suppkey = part_supp_a.ps_suppkey
+ group by l_linenumber;
+ """
+ order_qt_query21_0_before "${query21_0}"
+ check_rewrite(mv21_0, query21_0, "mv21_0")
+ order_qt_query21_0_after "${query21_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv21_0"""
+
+ // should rewrite success because query agg input the join has ps_availqty
dimension which is not in mv
+ def mv21_1 = """
+ select
+ l_linenumber,
+ count(distinct l_orderkey),
+ sum(case when l_orderkey in (1,2,3) then l_suppkey * l_linenumber
else 0 end),
+ max(case when l_orderkey in (4, 5) then (l_quantity *2 +
part_supp_a.qty_max) * 0.88 else 100 end),
+ avg(case when l_partkey in (2, 3, 4) then l_discount +
o_totalprice + part_supp_a.qty_sum else 50 end)
+ from lineitem
+ left join orders on l_orderkey = o_orderkey
+ left join
+ (select ps_partkey, ps_suppkey, sum(ps_availqty) qty_sum,
max(ps_availqty) qty_max,
+ min(ps_availqty) qty_min,
+ avg(ps_supplycost) cost_avg
+ from partsupp
+ group by ps_partkey,ps_suppkey) part_supp_a
+ on l_partkey = part_supp_a.ps_partkey
+ and l_suppkey = part_supp_a.ps_suppkey
+ group by l_linenumber;
+ """
+ def query21_1 = """
+ select
+ l_linenumber,
+ count(distinct l_orderkey),
+ sum(case when l_orderkey in (1,2,3) then l_suppkey * l_linenumber
else 0 end),
+ max(case when l_orderkey in (4, 5) then (l_quantity *2 +
part_supp_a.qty_max) * 0.88 else 100 end),
+ avg(case when l_partkey in (2, 3, 4) then l_discount +
o_totalprice + part_supp_a.qty_sum else 50 end)
+ from lineitem
+ left join orders on l_orderkey = o_orderkey
+ left join
+ (select ps_suppkey, ps_partkey, ps_availqty, sum(ps_availqty)
qty_sum, max(ps_availqty) qty_max,
+ min(ps_availqty) qty_min,
+ avg(ps_supplycost) cost_avg
+ from partsupp
+ group by ps_suppkey, ps_partkey, ps_availqty) part_supp_a
+ on l_partkey = part_supp_a.ps_partkey
+ and l_suppkey = part_supp_a.ps_suppkey
+ group by l_linenumber;
+ """
+ order_qt_query21_1_before "${query21_1}"
+ check_not_match(mv21_1, query21_1, "mv21_1")
+ order_qt_query21_1_after "${query21_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv21_1"""
+
+ // should not rewritten successfully, because query has more filter
+ def mv21_2 = """
+ select
+ l_linenumber,
+ count(distinct l_orderkey),
+ sum(case when l_orderkey in (1,2,3) then l_suppkey * l_linenumber
else 0 end),
+ max(case when l_orderkey in (4, 5) then (l_quantity *2 +
part_supp_a.qty_max) * 0.88 else 100 end),
+ avg(case when l_partkey in (2, 3, 4) then l_discount +
o_totalprice + part_supp_a.qty_sum else 50 end)
+ from lineitem
+ left join orders on l_orderkey = o_orderkey
+ left join
+ (select ps_partkey, ps_suppkey, sum(ps_availqty) qty_sum,
max(ps_availqty) qty_max,
+ min(ps_availqty) qty_min,
+ avg(ps_supplycost) cost_avg
+ from partsupp
+ where ps_partkey in (1, 2)
+ group by ps_partkey,ps_suppkey) part_supp_a
+ on l_partkey = part_supp_a.ps_partkey
+ and l_suppkey = part_supp_a.ps_suppkey
+ group by l_linenumber;
+ """
+ def query21_2 = """
+ select
+ l_linenumber,
+ count(distinct l_orderkey),
+ sum(case when l_orderkey in (1,2,3) then l_suppkey * l_linenumber
else 0 end),
+ max(case when l_orderkey in (4, 5) then (l_quantity *2 +
part_supp_a.qty_max) * 0.88 else 100 end),
+ avg(case when l_partkey in (2, 3, 4) then l_discount +
o_totalprice + part_supp_a.qty_sum else 50 end)
+ from lineitem
+ left join orders on l_orderkey = o_orderkey
+ left join
+ (select ps_partkey, ps_suppkey, sum(ps_availqty) qty_sum,
max(ps_availqty) qty_max,
+ min(ps_availqty) qty_min,
+ avg(ps_supplycost) cost_avg
+ from partsupp
+ group by ps_partkey,ps_suppkey) part_supp_a
+ on l_partkey = part_supp_a.ps_partkey
+ and l_suppkey = part_supp_a.ps_suppkey
+ group by l_linenumber;
+ """
+ order_qt_query21_2_before "${query21_2}"
+ check_not_match(mv21_2, query21_2, "mv21_2")
+ order_qt_query21_2_after "${query21_2}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv21_2"""
}
diff --git
a/regression-test/suites/nereids_rules_p0/mv/join/dphyp_outer/outer_join_dphyp.groovy
b/regression-test/suites/nereids_rules_p0/mv/join/dphyp_outer/outer_join_dphyp.groovy
index de253de7823..3d858c57ddc 100644
---
a/regression-test/suites/nereids_rules_p0/mv/join/dphyp_outer/outer_join_dphyp.groovy
+++
b/regression-test/suites/nereids_rules_p0/mv/join/dphyp_outer/outer_join_dphyp.groovy
@@ -321,7 +321,7 @@ suite("outer_join_dphyp") {
"where orders.O_ORDERSTATUS = 'o'"
order_qt_query3_2_before "${query3_2}"
// should not success, as mv filter is under left outer input
- check_not_match(mv3_2, query3_2, "mv3_2")
+ check_rewrite(mv3_2, query3_2, "mv3_2")
order_qt_query3_2_after "${query3_2}"
sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_2"""
diff --git
a/regression-test/suites/nereids_rules_p0/mv/join/left_outer/outer_join.groovy
b/regression-test/suites/nereids_rules_p0/mv/join/left_outer/outer_join.groovy
index 83b2ca726b7..ffeef5e149f 100644
---
a/regression-test/suites/nereids_rules_p0/mv/join/left_outer/outer_join.groovy
+++
b/regression-test/suites/nereids_rules_p0/mv/join/left_outer/outer_join.groovy
@@ -49,6 +49,30 @@ suite("outer_join") {
);
"""
+ sql """
+ drop table if exists orders_null
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS orders_null (
+ o_orderkey INTEGER NULL,
+ o_custkey INTEGER NULL,
+ o_orderstatus CHAR(1) NULL,
+ o_totalprice DECIMALV3(15,2) NULL,
+ o_orderdate DATE NULL,
+ o_orderpriority CHAR(15) NULL,
+ o_clerk CHAR(15) NULL,
+ o_shippriority INTEGER NULL,
+ O_COMMENT VARCHAR(79) NULL
+ )
+ DUPLICATE KEY(o_orderkey, o_custkey)
+ PARTITION BY RANGE(o_orderdate) (PARTITION `day_2` VALUES LESS THAN
('2023-12-30'))
+ DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
sql """
drop table if exists lineitem
"""
@@ -80,6 +104,36 @@ suite("outer_join") {
);
"""
+ sql """
+ drop table if exists lineitem_null
+ """
+
+ sql"""
+ CREATE TABLE IF NOT EXISTS lineitem_null (
+ l_orderkey INTEGER NULL,
+ l_partkey INTEGER NULL,
+ l_suppkey INTEGER NULL,
+ l_linenumber INTEGER NULL,
+ l_quantity DECIMALV3(15,2) NULL,
+ l_extendedprice DECIMALV3(15,2) NULL,
+ l_discount DECIMALV3(15,2) NULL,
+ l_tax DECIMALV3(15,2) NULL,
+ l_returnflag CHAR(1) NULL,
+ l_linestatus CHAR(1) NULL,
+ l_shipdate DATE NULL,
+ l_commitdate DATE NULL,
+ l_receiptdate DATE NULL,
+ l_shipinstruct CHAR(25) NULL,
+ l_shipmode CHAR(10) NULL,
+ l_comment VARCHAR(44) NULL
+ )
+ DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber)
+ DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
sql """
drop table if exists partsupp
"""
@@ -119,6 +173,27 @@ suite("outer_join") {
(5, 2, 'o', 1.2, '2023-12-12', 'c','d',2, 'mi');
"""
+ sql """
+ insert into lineitem_null 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 """
+ insert into orders_null 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 """
insert into partsupp values
(2, 3, 9, 10.01, 'supply1'),
@@ -310,18 +385,22 @@ suite("outer_join") {
sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_1"""
- def mv3_2 = "select lineitem.L_LINENUMBER, t2.O_CUSTKEY, t2.O_ORDERSTATUS
" +
- "from lineitem " +
- "left join " +
- "(select * from orders where O_ORDERSTATUS = 'o') t2 " +
- "on lineitem.L_ORDERKEY = t2.O_ORDERKEY "
- def query3_2 = "select lineitem.L_LINENUMBER " +
- "from lineitem " +
- "left join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY " +
- "where orders.O_ORDERSTATUS = 'o'"
+ def mv3_2 = """
+ select lineitem.L_LINENUMBER, t2.O_CUSTKEY, t2.O_ORDERSTATUS
+ from lineitem
+ left join
+ (select * from orders where O_ORDERSTATUS = 'o') t2
+ on lineitem.L_ORDERKEY = t2.O_ORDERKEY;
+ """
+ def query3_2 = """
+ select lineitem.L_LINENUMBER
+ from lineitem
+ left join orders on lineitem.L_ORDERKEY = orders.O_ORDERKEY
+ where orders.O_ORDERSTATUS = 'o';
+ """
order_qt_query3_2_before "${query3_2}"
// should not success, as mv filter is under left outer input
- check_not_match(mv3_2, query3_2, "mv3_2")
+ check_rewrite(mv3_2, query3_2, "mv3_2")
order_qt_query3_2_after "${query3_2}"
sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_2"""
@@ -391,6 +470,25 @@ suite("outer_join") {
order_qt_query6_0_after "${query6_0}"
sql """ DROP MATERIALIZED VIEW IF EXISTS mv6_0"""
+ // should has one reject null filter in orders_null, which should be
o_orderdate
+ def mv6_1 = """
+ select l_shipdate, t.o_orderdate, l_partkey, l_suppkey, t.o_orderkey
+ from lineitem_null
+ left join (select o_orderdate,o_orderkey from orders_null where
o_orderdate = '2023-12-10' ) t
+ on l_orderkey = t.o_orderkey;
+ """
+ def query6_1 = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey, o_orderkey
+ from lineitem_null
+ left join orders_null
+ on l_orderkey = o_orderkey
+ where l_shipdate = '2023-12-10' and o_orderdate = '2023-12-10';
+ """
+ order_qt_query6_1_before "${query6_1}"
+ check_rewrite(mv6_1, query6_1, "mv6_1")
+ order_qt_query6_1_after "${query6_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv6_1"""
+
// filter inside + left + right
def mv7_0 = "select l_shipdate, o_orderdate, l_partkey, l_suppkey " +
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
