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 df088085ca1 [fix](mv) Fix NPE when materialized view aggregate rewrite
treats a derived group by projection as a group by key (#67362)
df088085ca1 is described below
commit df088085ca1956b63d739f0cf9600f90229852d6
Author: starocean999 <[email protected]>
AuthorDate: Wed Sep 16 19:02:08 2026 +0800
[fix](mv) Fix NPE when materialized view aggregate rewrite treats a derived
group by projection as a group by key (#67362)
### What problem does this PR solve?
Problem Summary:
Given a sync materialized view on `sync_tz_base`:
```sql
CREATE MATERIALIZED VIEW sync_tz_day AS
SELECT date_trunc(ts, 'day') AS day_ts, sum(v) AS day_sum
FROM sync_tz_base WHERE ts IS NOT NULL
GROUP BY date_trunc(ts, 'day');
```
the following query fails during planning with an NPE:
```sql
SELECT CAST(date_trunc(ts, 'day') AS STRING) AS day_ts, SUM(v)
FROM sync_tz_base WHERE ts IS NOT NULL
GROUP BY date_trunc(ts, 'day');
```
```
java.lang.NullPointerException: Cannot invoke
"org.apache.doris.analysis.Expr.getChildren()" because "root" is null
at org.apache.doris.analysis.Expr.extractSlots(Expr.java:173)
at
org.apache.doris.nereids.glue.translator.PhysicalPlanTranslator.visitPhysicalProject(PhysicalPlanTranslator.java:2173)
```
**Root cause**
In `AbstractMaterializedViewAggregateRule.aggregateRewriteByView`, the
group by keys of the rewritten aggregate were collected from the query
**top plan** output expressions. For a `project -> aggregate` structure,
`topPlanSplitToGroupAndFunction` classifies the derived projection
`cast(date_trunc(ts, 'day') AS STRING)` as a group-by expression,
because it is derived from the real group key. This derived projection
was then wrongly added as a group by key of the rewritten aggregate:
```
finalGroupExpressions = [cast(day_ts#7 as TEXT) AS #9, day_ts#7]
finalOutputExpressions = [cast(day_ts#7 as TEXT) AS #9, sum(day_sum#8) AS
#10]
```
This led to two problems:
1. A redundant group by key `cast(day_ts#7 as TEXT)` which is only a
projection of the real group key `day_ts#7`.
2. The real group key `day_ts#7` was added by the group-by compensation
logic but was **not** present in the aggregate output expressions, so
the top project referenced a slot that the physical aggregate never
produced, causing the `"root" is null` NPE during physical plan
translation.
**Fix**
The rewritten aggregate is now built directly from the query bottom
aggregate:
- The group by keys are the query bottom aggregate's group by
expressions rewritten against the MV scan (always correct, so the
previous group-by compensation is no longer needed).
- The aggregate output contains the rewritten group keys and the
rolled-up aggregate functions.
- The query top plan output expressions — including derived projections
of group keys such as `cast(date_trunc(ts, 'day') AS STRING)` — are
recomputed by a `LogicalProject` above the rewritten aggregate when they
cannot be produced by the aggregate directly.
After the fix the rewritten plan is valid and the query returns correct
results:
```
Project [cast(day_ts#7 as TEXT) AS day_ts#3, sum(day_sum#8) AS SUM(v)#4]
Aggregate [group by [day_ts#7], output [day_ts#7, sum(day_sum#8)]]
MVScan(sync_tz_day)
```
---
.../mv/AbstractMaterializedViewAggregateRule.java | 269 ++++++++++++++++-----
.../aggregate_without_roll_up_projection.out | 40 +++
.../aggregate_without_roll_up_projection.groovy | 161 ++++++++++++
3 files changed, 416 insertions(+), 54 deletions(-)
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 7ee28753abc..046793de494 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
@@ -161,7 +161,7 @@ public abstract class AbstractMaterializedViewAggregateRule
extends AbstractMate
/**
* Aggregate function and group by expression rewrite impl
*/
- protected LogicalAggregate<Plan> aggregateRewriteByView(
+ protected Plan aggregateRewriteByView(
StructInfo queryStructInfo,
SlotMapping viewToQuerySlotMapping,
Pair<Plan, LogicalAggregate<Plan>> queryTopPlanAndAggPair,
@@ -170,11 +170,6 @@ public abstract class
AbstractMaterializedViewAggregateRule extends AbstractMate
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
- = topPlanSplitToGroupAndFunction(queryTopPlanAndAggPair,
queryStructInfo);
- Set<? extends Expression> queryTopPlanGroupBySet =
queryGroupAndFunctionPair.key();
- Set<? extends Expression> queryTopPlanFunctionSet =
queryGroupAndFunctionPair.value();
// try to rewrite, contains both roll up aggregate functions and
aggregate group expression
List<NamedExpression> finalOutputExpressions = new ArrayList<>();
List<Expression> finalGroupExpressions = new ArrayList<>();
@@ -183,57 +178,79 @@ public abstract class
AbstractMaterializedViewAggregateRule extends AbstractMate
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, aggregateFunctionMode,
materializationContext,
- "Query function roll up fail",
- () -> String.format("queryExpression = %s,\n
mvExprToMvScanExprQueryBased = %s",
- topExpression, mvExprToMvScanExprQueryBased));
- if (rollupedExpression == null) {
- return null;
- }
- finalOutputExpressions.add(new Alias(rollupedExpression));
- } else {
- // if group by dimension, try to rewrite
- Expression rewrittenGroupByExpression =
tryRewriteExpression(queryStructInfo, topExpression,
- mvExprToMvScanExprQueryBased, groupByMode,
materializationContext,
- "View dimensions doesn't not cover the query
dimensions",
- () -> String.format("mvExprToMvScanExprQueryBased is
%s,\n queryExpression is %s",
- mvExprToMvScanExprQueryBased, topExpression));
- if (rewrittenGroupByExpression == null) {
- // group expr can not rewrite by view
- return null;
- }
- NamedExpression groupByExpression = rewrittenGroupByExpression
instanceof NamedExpression
- ? (NamedExpression) rewrittenGroupByExpression : new
Alias(rewrittenGroupByExpression);
- finalOutputExpressions.add(groupByExpression);
- 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 (needCompensateGroupBy(queryTopPlanGroupBySet,
queryGroupByExpressions)) {
- for (Expression expression : queryGroupByExpressions) {
- if (queryTopPlanGroupBySet.contains(expression)) {
- continue;
+ if (queryAggregate.getSourceRepeat().isPresent()) {
+ // The group by/function classification of the query top plan
output expressions is only
+ // used by the repeat rewrite, so it is computed lazily inside
this branch to avoid paying
+ // the full plan lineage walk for every ordinary aggregate rewrite.
+ // split the query top plan expressions to group expressions and
functions, if can not, bail out.
+ Pair<Set<? extends Expression>, Set<? extends Expression>>
queryGroupAndFunctionPair
+ = topPlanSplitToGroupAndFunction(queryTopPlanAndAggPair,
queryStructInfo);
+ Set<? extends Expression> queryTopPlanGroupBySet =
queryGroupAndFunctionPair.key();
+ Set<? extends Expression> queryTopPlanFunctionSet =
queryGroupAndFunctionPair.value();
+ // try to rewrite the query top plan expressions, the query top
plan output expressions
+ // are used as the repeat output expressions directly. The repeat
output keeps the original top
+ // output expr id equivalence classes, see
rewriteOutputInOriginalExprIdEquivalenceClass, so the
+ // rewritten output set size equals the query output set size and
the repeat rewrite is not
+ // rejected by the output set size guard of
MaterializedViewUtils.rewriteByRules.
+ Map<ExprId, NamedExpression> originalExprIdToRewrittenOutput = new
HashMap<>();
+ Set<ExprId> usedRewrittenOutputExprIds = new HashSet<>();
+ for (Slot topPlanOutput : queryTopPlan.getOutput()) {
+ NamedExpression rewrittenOutput =
originalExprIdToRewrittenOutput.get(topPlanOutput.getExprId());
+ if (rewrittenOutput == null) {
+ if (queryTopPlanFunctionSet.contains(topPlanOutput)) {
+ // if agg function, try to roll up and rewrite
+ Expression rollupedExpression =
tryRewriteExpression(queryStructInfo, topPlanOutput,
+ mvExprToMvScanExprQueryBased,
aggregateFunctionMode, materializationContext,
+ "Query function roll up fail",
+ () -> String.format("queryExpression = %s,\n
mvExprToMvScanExprQueryBased = %s",
+ topPlanOutput,
mvExprToMvScanExprQueryBased));
+ if (rollupedExpression == null) {
+ return null;
+ }
+ rewrittenOutput =
rewriteOutputInOriginalExprIdEquivalenceClass(
+ originalExprIdToRewrittenOutput,
usedRewrittenOutputExprIds,
+ topPlanOutput.getExprId(), rollupedExpression);
+ } else {
+ // if group by dimension, try to rewrite
+ Expression rewrittenGroupByExpression =
tryRewriteExpression(
+ queryStructInfo, topPlanOutput,
mvExprToMvScanExprQueryBased, groupByMode,
+ materializationContext,
+ "View dimensions doesn't not cover the query
dimensions",
+ () ->
String.format("mvExprToMvScanExprQueryBased is %s,\n queryExpression is %s",
+ mvExprToMvScanExprQueryBased,
topPlanOutput));
+ if (rewrittenGroupByExpression == null) {
+ // group expr can not rewrite by view
+ return null;
+ }
+ rewrittenOutput =
rewriteOutputInOriginalExprIdEquivalenceClass(
+ originalExprIdToRewrittenOutput,
usedRewrittenOutputExprIds,
+ topPlanOutput.getExprId(),
rewrittenGroupByExpression);
+ finalGroupExpressions.add(rewrittenOutput);
+ }
}
- Expression rewrittenGroupByExpression =
tryRewriteExpression(queryStructInfo, expression,
- 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));
- if (rewrittenGroupByExpression == null) {
- return null;
+ finalOutputExpressions.add(rewrittenOutput);
+ }
+ // handle the scene that query top plan not use the group by in
query bottom aggregate
+ if (needCompensateGroupBy(queryTopPlanGroupBySet,
queryGroupByExpressions)) {
+ for (Expression expression : queryGroupByExpressions) {
+ if (queryTopPlanGroupBySet.contains(expression)) {
+ continue;
+ }
+ Expression rewrittenGroupByExpression =
tryRewriteExpression(queryStructInfo, expression,
+ 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));
+ if (rewrittenGroupByExpression == null) {
+ return null;
+ }
+ NamedExpression groupByExpression =
rewrittenGroupByExpression instanceof NamedExpression
+ ? (NamedExpression) rewrittenGroupByExpression :
new Alias(rewrittenGroupByExpression);
+ finalGroupExpressions.add(groupByExpression);
}
- NamedExpression groupByExpression = rewrittenGroupByExpression
instanceof NamedExpression
- ? (NamedExpression) rewrittenGroupByExpression : new
Alias(rewrittenGroupByExpression);
- finalGroupExpressions.add(groupByExpression);
}
- }
- if (queryAggregate.getSourceRepeat().isPresent()) {
// construct group sets for repeat
List<List<Expression>> rewrittenGroupSetsExpressions = new
ArrayList<>();
List<List<Expression>> groupingSets =
queryAggregate.collectFirst(LogicalRepeat.class::isInstance)
@@ -265,7 +282,102 @@ public abstract class
AbstractMaterializedViewAggregateRule extends AbstractMate
queryAggregate.getSourceRepeat().get().getRepeatType(),
tempRewritedPlan);
return NormalizeRepeat.doNormalize(repeat);
}
- return new LogicalAggregate<>(finalGroupExpressions,
finalOutputExpressions, tempRewritedPlan);
+
+ // The rewritten aggregate should group by the query bottom
aggregate's group by expressions,
+ // and its output expressions should be the rewritten group by
expressions and the rolled up
+ // aggregate functions. The query top plan output expressions are
recomputed by a project above
+ // the rewritten aggregate, so the projection of a group by key in the
query top plan (such as
+ // `select cast(date_trunc(ts, 'day') as string) from t group by
date_trunc(ts, 'day')`) will not
+ // be wrongly treated as a group by key of the rewritten aggregate.
+ // The mapping from the query bottom aggregate output slot to the new
aggregate output expression
+ // is used to rewrite the query top plan output expressions to
reference the new aggregate output.
+ // The shuttled query bottom aggregate outputs are used as the map
keys and are passed to the
+ // rewriter directly, and the shuttled query top plan outputs restore
the projection expressions,
+ // so the whole top plan is traversed only twice instead of once per
output expression.
+ List<? extends Expression> shuttledBottomAggOutputs =
ExpressionUtils.shuttleExpressionWithLineage(
+ queryAggregate.getOutputExpressions(), queryTopPlan);
+ List<? extends Expression> shuttledTopPlanOutputs =
ExpressionUtils.shuttleExpressionWithLineage(
+ queryTopPlan.getOutput(), queryTopPlan);
+ Map<Expression, Expression> bottomAggOutputToNewExprMap = new
HashMap<>();
+ Set<Expression> queryGroupByExpressionSet = new
HashSet<>(queryGroupByExpressions);
+ List<NamedExpression> queryAggregateOutputs =
queryAggregate.getOutputExpressions();
+ for (int i = 0; i < queryAggregateOutputs.size(); i++) {
+ NamedExpression queryAggregateOutput =
queryAggregateOutputs.get(i);
+ Expression shuttledQueryAggregateOutput =
shuttledBottomAggOutputs.get(i);
+ if (queryGroupByExpressionSet.contains(queryAggregateOutput)) {
+ // if it is a group by expression, rewrite it to the new
aggregate group by key
+ Expression rewrittenGroupByExpression =
rewriteShuttledExpression(queryStructInfo,
+ shuttledQueryAggregateOutput,
mvExprToMvScanExprQueryBased, groupByMode,
+ materializationContext,
+ "View dimensions doesn't not cover the query
dimensions",
+ () -> String.format("mvExprToMvScanExprQueryBased is
%s,\n queryExpression is %s",
+ mvExprToMvScanExprQueryBased,
queryAggregateOutput));
+ if (rewrittenGroupByExpression == null) {
+ return null;
+ }
+ NamedExpression groupByOutput = rewrittenGroupByExpression
instanceof NamedExpression
+ ? (NamedExpression) rewrittenGroupByExpression : new
Alias(rewrittenGroupByExpression);
+ finalGroupExpressions.add(groupByOutput);
+ finalOutputExpressions.add(groupByOutput);
+ bottomAggOutputToNewExprMap.put(shuttledQueryAggregateOutput,
groupByOutput.toSlot());
+ } else {
+ // if it is an aggregate function, try to roll up and rewrite
+ Expression rewrittenFunction =
rewriteShuttledExpression(queryStructInfo,
+ shuttledQueryAggregateOutput,
mvExprToMvScanExprQueryBased, aggregateFunctionMode,
+ materializationContext,
+ "Query function roll up fail",
+ () -> String.format("queryExpression = %s,\n
mvExprToMvScanExprQueryBased = %s",
+ queryAggregateOutput,
mvExprToMvScanExprQueryBased));
+ if (rewrittenFunction == null) {
+ return null;
+ }
+ NamedExpression functionOutput = new Alias(rewrittenFunction);
+ finalOutputExpressions.add(functionOutput);
+ bottomAggOutputToNewExprMap.put(shuttledQueryAggregateOutput,
functionOutput.toSlot());
+ }
+ }
+
+ LogicalAggregate<Plan> rewrittenAggregate =
+ new LogicalAggregate<>(finalGroupExpressions,
finalOutputExpressions, tempRewritedPlan);
+
+ // rewrite the query top plan output expressions to reference the
rewritten aggregate output,
+ // the query top plan output slot is shuttled by lineage firstly to
restore the projection
+ // expression, so a projection of the group by key in the query top
plan can be recomputed
+ // by a project above the rewritten aggregate.
+ List<NamedExpression> topProjectExpressions = new ArrayList<>();
+ // Preserve the original top output expr id equivalence classes, see
+ // rewriteOutputInOriginalExprIdEquivalenceClass for the details.
+ List<Slot> queryTopPlanOutputs = queryTopPlan.getOutput();
+ Map<ExprId, NamedExpression> originalExprIdToRewritten = new
HashMap<>();
+ Set<ExprId> usedRewrittenExprIds = new HashSet<>();
+ for (int i = 0; i < shuttledTopPlanOutputs.size(); i++) {
+ ExprId originalExprId = queryTopPlanOutputs.get(i).getExprId();
+ NamedExpression groupRewrittenExpression =
originalExprIdToRewritten.get(originalExprId);
+ if (groupRewrittenExpression == null) {
+ groupRewrittenExpression =
rewriteOutputInOriginalExprIdEquivalenceClass(
+ originalExprIdToRewritten, usedRewrittenExprIds,
originalExprId,
+ ExpressionUtils.replace(shuttledTopPlanOutputs.get(i),
bottomAggOutputToNewExprMap));
+ }
+ topProjectExpressions.add(groupRewrittenExpression);
+ }
+ // If the query top plan output expressions can be produced by the
rewritten aggregate directly,
+ // return the aggregate, otherwise compute them by a project above the
rewritten aggregate.
+ // Note the query top plan output may be a strict matching prefix of
the rewritten aggregate output
+ // (e.g. `select k1 from t group by k1, k2`), in which case the
redundant aggregate outputs must be
+ // projected away. Otherwise the rewritten plan output count differs
from the query and the candidate
+ // is rejected by MaterializedViewUtils.normalizeExpressions, so a
valid sync MV is silently not used.
+ boolean needTopProject = topProjectExpressions.size() !=
finalOutputExpressions.size();
+ for (int i = 0; i < topProjectExpressions.size(); i++) {
+ if (i >= finalOutputExpressions.size()
+ ||
!topProjectExpressions.get(i).toSlot().equals(finalOutputExpressions.get(i).toSlot()))
{
+ needTopProject = true;
+ break;
+ }
+ }
+ if (!needTopProject) {
+ return rewrittenAggregate;
+ }
+ return new LogicalProject<>(topProjectExpressions, rewrittenAggregate);
}
/**
@@ -304,6 +416,43 @@ public abstract class
AbstractMaterializedViewAggregateRule extends AbstractMate
return
!queryTopPlanGroupByUseNamedExpressions.containsAll(queryGroupByUseNamedExpressions);
}
+ /**
+ * Build one rewritten output of a query top plan output position, keeping
the rewritten output
+ * consistent with the original top output expr id equivalence classes.
+ * <p>
+ * Positions which share one original expr id (such as the unaliased
`select k, k from t group by k`,
+ * where both positions reference the same top output slot) must share one
rewritten output expr id,
+ * otherwise the rewritten output set is inflated. Conversely, positions
with distinct original expr ids
+ * which are rewritten to the same view output (such as `select k as k1, k
as k2 from t group by k`,
+ * where both positions rewrite to the same view slot) must keep distinct
rewritten output expr ids,
+ * otherwise the rewritten output set is collapsed.
+ * <p>
+ * Both directions matter because the rewritten output set size must equal
the query output set size,
+ * which is the guard of MaterializedViewUtils.rewriteByRules. When the
guard is hit, the whole-tree
+ * normalization and partition pruning are skipped, and the repeat rewrite
returns an aggregate with
+ * normalized=false whose derived projections are still inside the
unnormalized aggregate, so that the
+ * physical translation can not resolve them when the top plan output is
projected.
+ *
+ * @param originalExprIdToRewritten rewritten output of the original expr
id, reused when present
+ * @param usedRewrittenExprIds rewritten output expr ids which are used by
other expr id classes already
+ * @param originalExprId original expr id of the query top plan output
position
+ * @param rewrittenExpression expression which the query top plan output
position is rewritten to
+ * @return rewritten output expression of the query top plan output
position
+ */
+ private static NamedExpression
rewriteOutputInOriginalExprIdEquivalenceClass(
+ Map<ExprId, NamedExpression> originalExprIdToRewritten,
Set<ExprId> usedRewrittenExprIds,
+ ExprId originalExprId, Expression rewrittenExpression) {
+ NamedExpression rewrittenOutput = rewrittenExpression instanceof
NamedExpression
+ ? (NamedExpression) rewrittenExpression : new
Alias(rewrittenExpression);
+ if (!usedRewrittenExprIds.add(rewrittenOutput.getExprId())) {
+ // The rewritten expr id is used by another original expr id
equivalence class already, keep a
+ // distinct output expr id for this class.
+ rewrittenOutput = new Alias(rewrittenExpression);
+ }
+ originalExprIdToRewritten.put(originalExprId, rewrittenOutput);
+ return rewrittenOutput;
+ }
+
/**
* Try to rewrite query expression by view, contains both group by
dimension and aggregate function
*/
@@ -313,10 +462,22 @@ public abstract class
AbstractMaterializedViewAggregateRule extends AbstractMate
Expression queryFunctionShuttled =
ExpressionUtils.shuttleExpressionWithLineage(
queryExpression,
queryStructInfo.getTopPlan());
+ return rewriteShuttledExpression(queryStructInfo,
queryFunctionShuttled, mvShuttledExprToMvScanExprQueryBased,
+ rewriteMode, materializationContext, summaryIfFail,
detailIfFail);
+ }
+
+ /**
+ * Rewrite the shuttled query expression by view, contains both group by
dimension and aggregate
+ * function. The query expression is expected to be shuttled by lineage
already, so the top plan is
+ * not traversed again here and the batched lineage result can be reused.
+ */
+ private Expression rewriteShuttledExpression(StructInfo queryStructInfo,
Expression queryShuttledExpression,
+ Map<Expression, Expression> mvShuttledExprToMvScanExprQueryBased,
ExpressionRewriteMode rewriteMode,
+ MaterializationContext materializationContext, String
summaryIfFail, Supplier<String> detailIfFail) {
AggregateExpressionRewriteContext expressionRewriteContext = new
AggregateExpressionRewriteContext(
rewriteMode, mvShuttledExprToMvScanExprQueryBased,
queryStructInfo.getTopPlan(),
queryStructInfo.getGroupingId());
- Expression rewrittenExpression =
queryFunctionShuttled.accept(AGGREGATE_EXPRESSION_REWRITER,
+ Expression rewrittenExpression =
queryShuttledExpression.accept(AGGREGATE_EXPRESSION_REWRITER,
expressionRewriteContext);
if (!expressionRewriteContext.isValid()) {
materializationContext.recordFailReason(queryStructInfo,
summaryIfFail, detailIfFail);
diff --git
a/regression-test/data/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up_projection.out
b/regression-test/data/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up_projection.out
new file mode 100644
index 00000000000..76d7df7aef9
--- /dev/null
+++
b/regression-test/data/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up_projection.out
@@ -0,0 +1,40 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select_mv --
+2024-01-01 00:00:00.000000+08:00 7
+2024-01-02 00:00:00.000000+08:00 3
+
+-- !select_mv_leading_subset --
+2024-01-01 00:00:00.000000+08:00
+2024-01-01 00:00:00.000000+08:00
+2024-01-02 00:00:00.000000+08:00
+
+-- !select_mv_dup_sum --
+2024-01-01 00:00:00.000000+08:00 7 7
+2024-01-02 00:00:00.000000+08:00 3 3
+
+-- !select_mv_dup_group --
+2024-01-01 00:00:00.000000+08:00 2024-01-01 00:00:00.000000+08:00
7
+2024-01-02 00:00:00.000000+08:00 2024-01-02 00:00:00.000000+08:00
3
+
+-- !select_mv_dup_bare_group --
+2024-01-01 00:00:00.000000+08:00 2024-01-01 00:00:00.000000+08:00
7
+2024-01-02 00:00:00.000000+08:00 2024-01-02 00:00:00.000000+08:00
3
+
+-- !select_mv_grouping_sets --
+\N \N \N 7
+\N \N 4 7
+2024-01-01 00:00:00.000000+08:00 2024-01-01 00:00:00.000000+08:00
\N 7
+2024-01-01 00:00:00.000000+08:00 2024-01-01 00:00:00.000000+08:00
1 3
+2024-01-01 00:00:00.000000+08:00 2024-01-01 00:00:00.000000+08:00
2 4
+2024-01-02 00:00:00.000000+08:00 2024-01-02 00:00:00.000000+08:00
\N 3
+2024-01-02 00:00:00.000000+08:00 2024-01-02 00:00:00.000000+08:00
3 3
+
+-- !select_mv_grouping_sets_bare_group --
+\N \N 7
+\N \N 7
+2024-01-01 00:00:00.000000+08:00 2024-01-01 00:00:00.000000+08:00
3
+2024-01-01 00:00:00.000000+08:00 2024-01-01 00:00:00.000000+08:00
4
+2024-01-01 00:00:00.000000+08:00 2024-01-01 00:00:00.000000+08:00
7
+2024-01-02 00:00:00.000000+08:00 2024-01-02 00:00:00.000000+08:00
3
+2024-01-02 00:00:00.000000+08:00 2024-01-02 00:00:00.000000+08:00
3
+
diff --git
a/regression-test/suites/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up_projection.groovy
b/regression-test/suites/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up_projection.groovy
new file mode 100644
index 00000000000..bf8d55e2e2f
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/agg_without_roll_up/aggregate_without_roll_up_projection.groovy
@@ -0,0 +1,161 @@
+// 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("aggregate_without_roll_up_projection") {
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+ // Pin the session time zone: the test inserts offset-less TIMESTAMPTZ
values and the golden output
+ // hard-codes +08:00, so on a non +08:00 runner the rendered values would
differ for environmental reasons.
+ sql "set time_zone = '+08:00'"
+ sql "set pre_materialized_view_rewrite_strategy = TRY_IN_RBO"
+ // Disable the plain aggregate rule so that the query is rewritten by the
+ // MATERIALIZED_VIEW_PROJECT_FILTER_AGGREGATE rule only, which reproduces
the bug that a
+ // projection of the group by key in the query top plan is wrongly treated
as a group by key.
+ sql "set disable_nereids_rules='MATERIALIZED_VIEW_ONLY_AGGREGATE'"
+
+ sql """ DROP TABLE IF EXISTS sync_tz_base; """
+
+ sql """
+ create table sync_tz_base(
+ id int null,
+ ts timestamptz(6) null,
+ v int null
+ )
+ duplicate key (id)
+ distributed BY hash(id) buckets 3
+ properties("replication_num" = "1");
+ """
+
+ sql "insert into sync_tz_base values (1, '2024-01-01 10:00:00', 3), (2,
'2024-01-01 12:00:00', 4), (3, '2024-01-02 08:00:00', 3), (4, null, 7);"
+
+ // the sync mv contains the same `ts is not null` predicate as the query,
so it can be covered
+ // by the mv and no predicate compensation is needed
+ create_sync_mv(db, "sync_tz_base", "sync_tz_day",
+ "select date_trunc(ts, 'day') as day_ts, sum(v) as day_sum from
sync_tz_base "
+ + "where ts is not null group by date_trunc(ts, 'day');")
+ // The mv groups by (day, id), while a query selecting only `day` is
grouped by (day, id) and its top
+ // project is a strict matching prefix of the normalized aggregate
outputs. The redundant aggregate
+ // output must be projected away, otherwise the rewritten plan output
count differs from the query
+ // and the rewrite is rejected. The select aliases avoid column name
conflicts with the base table
+ // and the existing mv on the same table.
+ create_sync_mv(db, "sync_tz_base", "sync_tz_day_id",
+ "select date_trunc(ts, 'day') as day_ts2, id as id2, sum(v) as
day_sum2 from sync_tz_base "
+ + "where ts is not null group by date_trunc(ts, 'day'),
id;")
+
+ sql "analyze table sync_tz_base with sync;"
+ sql """alter table sync_tz_base modify column id set stats
('row_count'='4');"""
+
+ // The query has a derived projection `cast(date_trunc(ts, 'day') as
string)` of the group by key
+ // `date_trunc(ts, 'day')` in the top project. The derived projection
should be recomputed by a
+ // project above the rewritten aggregate, and must not be treated as a
group by key of the
+ // rewritten aggregate.
+ mv_rewrite_success("select cast(date_trunc(ts, 'day') as string) as
day_ts, sum(v) "
+ + "from sync_tz_base where ts is not null group by date_trunc(ts,
'day');", "sync_tz_day")
+ order_qt_select_mv """select cast(date_trunc(ts, 'day') as string) as
day_ts, sum(v)
+ from sync_tz_base where ts is not null group by date_trunc(ts,
'day') order by 1;"""
+
+ // The query top project is a strict matching prefix of the normalized
aggregate outputs
+ // (`select day from ... group by day, id`), and the leading subset
exactly matches the mv
+ // outputs, which reproduces the boundary where the redundant aggregate
output must be
+ // projected away by a top project above the rewritten aggregate.
+ mv_rewrite_success("select date_trunc(ts, 'day') from sync_tz_base where
ts is not null "
+ + "group by date_trunc(ts, 'day'), id;", "sync_tz_day_id")
+ order_qt_select_mv_leading_subset """select date_trunc(ts, 'day') from
sync_tz_base
+ where ts is not null group by date_trunc(ts, 'day'), id order by
1;"""
+
+ // Multiple query top plan expressions can be rewritten to the same
aggregate output slot
+ // (`sum(v) as s1` and `sum(v) as s2` both reference the same bottom sum
slot). Each top
+ // project position must keep a distinct output expr id, otherwise the
rewritten output set
+ // collapses and the rewritten plan is skipped before normalization.
+ mv_rewrite_success("select cast(date_trunc(ts, 'day') as string) as
day_ts, sum(v) as s1, "
+ + "sum(v) as s2 from sync_tz_base where ts is not null "
+ + "group by date_trunc(ts, 'day');", "sync_tz_day")
+ order_qt_select_mv_dup_sum """select cast(date_trunc(ts, 'day') as string)
as day_ts, sum(v) as s1,
+ sum(v) as s2 from sync_tz_base where ts is not null group by
date_trunc(ts, 'day') order by 1;"""
+
+ // The same duplicate-collapse can also happen for separately aliased
duplicates of the group by
+ // expression itself.
+ mv_rewrite_success("select date_trunc(ts, 'day') as d1, date_trunc(ts,
'day') as d2, sum(v) as s "
+ + "from sync_tz_base where ts is not null group by date_trunc(ts,
'day');", "sync_tz_day")
+ order_qt_select_mv_dup_group """select date_trunc(ts, 'day') as d1,
date_trunc(ts, 'day') as d2, sum(v) as s
+ from sync_tz_base where ts is not null group by date_trunc(ts,
'day') order by 1;"""
+
+ // A repeated unaliased bare output references the same original output
slot twice
+ // (`select day, day, sum(v) ... group by day`). The two positions rewrite
to the same mv slot and
+ // must keep the single original expr id multiplicity: forcing a fresh
alias on the repeated
+ // position would inflate the rewritten output set and skip the whole-tree
normalization and
+ // partition pruning in MaterializedViewUtils.rewriteByRules.
+ mv_rewrite_success("select date_trunc(ts, 'day'), date_trunc(ts, 'day'),
sum(v) "
+ + "from sync_tz_base where ts is not null group by date_trunc(ts,
'day');", "sync_tz_day")
+ order_qt_select_mv_dup_bare_group """select date_trunc(ts, 'day'),
date_trunc(ts, 'day'), sum(v)
+ from sync_tz_base where ts is not null group by date_trunc(ts,
'day') order by 1;"""
+
+ // The grouping sets rewrite is a separate path of the aggregate rule: the
rewritten repeat outputs
+ // are built from the query top plan outputs directly, so the same
duplicate aliasing has to keep the
+ // original top output expr id equivalence classes there too. Re-enable
the aggregate rule, as the
+ // cases above only exercise the project filter aggregate rule.
+ sql "set disable_nereids_rules=''"
+
+ // A dedicated table and mv without any predicate, because the repeated
group sets are rewritten by
+ // the mv group sets directly and the mv predicate is not compensated on
this path.
+ sql """ DROP TABLE IF EXISTS sync_tz_gs; """
+
+ sql """
+ create table sync_tz_gs(
+ id int null,
+ ts timestamptz(6) null,
+ v int null
+ )
+ duplicate key (id)
+ distributed BY hash(id) buckets 3
+ properties("replication_num" = "1");
+ """
+
+ sql "insert into sync_tz_gs values (1, '2024-01-01 10:00:00', 3), (2,
'2024-01-01 12:00:00', 4), (3, '2024-01-02 08:00:00', 3), (4, null, 7);"
+
+ create_sync_mv(db, "sync_tz_gs", "sync_tz_gs_mv",
+ "select date_trunc(ts, 'day') as day_ts, id as id2, sum(v) as
day_sum from sync_tz_gs "
+ + "group by date_trunc(ts, 'day'), id;")
+
+ sql "analyze table sync_tz_gs with sync;"
+ sql """alter table sync_tz_gs modify column id set stats
('row_count'='4');"""
+
+ // Two separately aliased duplicates of the group by key in a grouping
sets query (`d1` and `d2`)
+ // both rewrite to the same mv slot. If every position keeps that slot
expr id, the rewritten repeat
+ // output set collapses from 4 to 3 members, so
MaterializedViewUtils.rewriteByRules returns at its
+ // output set size guard before the whole-tree normalization. The derived
`cast(id as string)`
+ // projection then stays inside the repeat aggregate, which
NormalizeRepeat returns as
+ // normalized=false, so the physical translation can not resolve that
output and the query fails
+ // with a null pointer exception while translating the final projection.
+ mv_rewrite_success("select date_trunc(ts, 'day') as d1, date_trunc(ts,
'day') as d2, "
+ + "cast(id as string) as id_s, sum(v) as s from sync_tz_gs "
+ + "group by grouping sets ((date_trunc(ts, 'day'), id),
(date_trunc(ts, 'day')));",
+ "sync_tz_gs_mv")
+ order_qt_select_mv_grouping_sets """select date_trunc(ts, 'day') as d1,
date_trunc(ts, 'day') as d2,
+ cast(id as string) as id_s, sum(v) as s from sync_tz_gs
+ group by grouping sets ((date_trunc(ts, 'day'), id),
(date_trunc(ts, 'day'))) order by 1, 3, 4;"""
+
+ // The opposite direction of the same equivalence class: a repeated
unaliased bare output references
+ // the same original output slot twice, and both positions must reuse one
rewritten repeat output.
+ // Forcing a fresh alias on the repeated position would inflate the
rewritten output set instead.
+ mv_rewrite_success("select date_trunc(ts, 'day'), date_trunc(ts, 'day'),
sum(v) from sync_tz_gs "
+ + "group by grouping sets ((date_trunc(ts, 'day'), id),
(date_trunc(ts, 'day')));",
+ "sync_tz_gs_mv")
+ order_qt_select_mv_grouping_sets_bare_group """select date_trunc(ts,
'day'), date_trunc(ts, 'day'), sum(v)
+ from sync_tz_gs
+ group by grouping sets ((date_trunc(ts, 'day'), id),
(date_trunc(ts, 'day'))) order by 1, 3;"""
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
