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 ebee839bcde [opt](mtmv) support rewrite when mv has date_trunc but
query doesn't have (#44948)
ebee839bcde is described below
commit ebee839bcdecfb56013b17a3e6b52eb8763f1089
Author: seawinde <[email protected]>
AuthorDate: Thu Jan 2 17:37:51 2025 +0800
[opt](mtmv) support rewrite when mv has date_trunc but query doesn't have
(#44948)
### What problem does this PR solve?
Such as mv def is as following:
select
l_shipmode,
date_trunc(l_commitdate, 'day') as day_trunc,
count(*)
from
lineitem
group by
l_shipmode,
date_trunc(l_commitdate, 'day');
query is as following, if query filter is like `l_commitdate >=
'2023-10-17' and l_commitdate < '2023-10-18'`
can be rewritten successfully by mv
select
l_shipmode,
count(*)
from
lineitem
where
l_commitdate >= '2023-10-17' and l_commitdate < '2023-10-18'
group by
l_shipmode;
### Release note
support rewrite when mv has date_trunc but query doesn't have
---
.../mv/AbstractMaterializedViewAggregateRule.java | 4 +-
.../mv/AbstractMaterializedViewJoinRule.java | 5 +-
.../mv/AbstractMaterializedViewRule.java | 113 +-
.../mv/AbstractMaterializedViewScanRule.java | 4 +-
.../nereids/rules/exploration/mv/Predicates.java | 218 ++--
.../rules/exploration/mv/PredicatesSplitter.java | 32 +-
.../nereids/rules/exploration/mv/StructInfo.java | 2 +-
.../apache/doris/nereids/util/ExpressionUtils.java | 25 +
.../rules/expression/PredicatesSplitterTest.java | 18 +-
.../mv/date_trunc/mv_with_date_trunc.out | 750 +++++++++++
.../mv/date_trunc/mv_with_date_trunc.groovy | 1378 ++++++++++++++++++++
11 files changed, 2402 insertions(+), 147 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 5a854f085b2..fd2b5b4b8aa 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
@@ -57,6 +57,7 @@ import
org.apache.doris.nereids.trees.plans.visitor.ExpressionLineageReplacer;
import org.apache.doris.nereids.util.ExpressionUtils;
import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Sets;
@@ -126,7 +127,8 @@ public abstract class AbstractMaterializedViewAggregateRule
extends AbstractMate
queryTopPlan,
materializationContext.getShuttledExprToScanExprMapping(),
viewToQuerySlotMapping,
- queryStructInfo.getTableBitSet());
+ queryStructInfo.getTableBitSet(),
+ ImmutableMap.of(), cascadesContext);
boolean isRewrittenQueryExpressionValid = true;
if (!rewrittenQueryExpressions.isEmpty()) {
List<NamedExpression> projects = new ArrayList<>();
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 28ea4dc2c7e..122688b766e 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
@@ -26,6 +26,8 @@ import
org.apache.doris.nereids.trees.expressions.NamedExpression;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import com.google.common.collect.ImmutableMap;
+
import java.util.List;
import java.util.stream.Collectors;
@@ -49,7 +51,8 @@ public abstract class AbstractMaterializedViewJoinRule
extends AbstractMateriali
queryStructInfo.getTopPlan(),
materializationContext.getShuttledExprToScanExprMapping(),
targetToSourceMapping,
- queryStructInfo.getTableBitSet()
+ queryStructInfo.getTableBitSet(),
+ ImmutableMap.of(), cascadesContext
);
// Can not rewrite, bail out
if (expressionsRewritten.isEmpty()) {
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 e6f384502d6..9339f0066a9 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
@@ -32,23 +32,27 @@ import org.apache.doris.nereids.CascadesContext;
import org.apache.doris.nereids.jobs.executor.Rewriter;
import org.apache.doris.nereids.properties.LogicalProperties;
import org.apache.doris.nereids.rules.exploration.ExplorationRuleFactory;
+import org.apache.doris.nereids.rules.exploration.mv.Predicates.ExpressionInfo;
import org.apache.doris.nereids.rules.exploration.mv.Predicates.SplitPredicate;
import
org.apache.doris.nereids.rules.exploration.mv.StructInfo.PartitionRemover;
import org.apache.doris.nereids.rules.exploration.mv.mapping.ExpressionMapping;
import org.apache.doris.nereids.rules.exploration.mv.mapping.RelationMapping;
import org.apache.doris.nereids.rules.exploration.mv.mapping.SlotMapping;
+import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext;
+import org.apache.doris.nereids.rules.expression.rules.FoldConstantRuleOnFE;
import org.apache.doris.nereids.rules.rewrite.MergeProjects;
import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.ComparisonPredicate;
import org.apache.doris.nereids.trees.expressions.Expression;
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.functions.agg.AggregateFunction;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.DateTrunc;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ElementAt;
import org.apache.doris.nereids.trees.expressions.functions.scalar.NonNullable;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Nullable;
-import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.DateLiteral;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
import org.apache.doris.nereids.trees.expressions.literal.VarcharLiteral;
import org.apache.doris.nereids.trees.plans.JoinType;
@@ -134,6 +138,7 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
rewrittenPlans.addAll(doRewrite(queryStructInfo,
cascadesContext, context));
}
} catch (Exception exception) {
+ LOG.warn("Materialized view rule exec fail", exception);
context.recordFailReason(queryStructInfo,
"Materialized view rule exec fail",
exception::toString);
}
@@ -242,7 +247,9 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
// Try to rewrite compensate predicates by using mv scan
List<Expression> rewriteCompensatePredicates =
rewriteExpression(compensatePredicates.toList(),
queryPlan,
materializationContext.getShuttledExprToScanExprMapping(),
- viewToQuerySlotMapping,
queryStructInfo.getTableBitSet());
+ viewToQuerySlotMapping,
queryStructInfo.getTableBitSet(),
+ compensatePredicates.getRangePredicateMap(),
+ cascadesContext);
if (rewriteCompensatePredicates.isEmpty()) {
materializationContext.recordFailReason(queryStructInfo,
"Rewrite compensate predicate by view fail",
@@ -560,7 +567,8 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
* then use the corresponding value of mapping to replace it
*/
protected List<Expression> rewriteExpression(List<? extends Expression>
sourceExpressionsToWrite, Plan sourcePlan,
- ExpressionMapping targetExpressionMapping, SlotMapping
targetToSourceMapping, BitSet sourcePlanBitSet) {
+ ExpressionMapping targetExpressionMapping, SlotMapping
targetToSourceMapping, BitSet sourcePlanBitSet,
+ Map<Expression, ExpressionInfo> queryExprToInfoMap,
CascadesContext cascadesContext) {
// Firstly, rewrite the target expression using source with inverse
mapping
// then try to use the target expression to represent the query. if
any of source expressions
// can not be represented by target expressions, return null.
@@ -573,24 +581,73 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
Map<Expression, Expression> targetToTargetReplacementMappingQueryBased
=
flattenExpressionMap.get(0);
+ // viewExprParamToDateTruncMap is {slot#0 : date_trunc(slot#0, 'day')}
+ Map<Expression, DateTrunc> viewExprParamToDateTruncMap = new
HashMap<>();
+ targetToTargetReplacementMappingQueryBased.keySet().forEach(expr -> {
+ if (expr instanceof DateTrunc) {
+ viewExprParamToDateTruncMap.put(expr.child(0), (DateTrunc)
expr);
+ }
+ });
+
List<Expression> rewrittenExpressions = new ArrayList<>();
- for (Expression expressionShuttledToRewrite :
sourceShuttledExpressions) {
+ for (int exprIndex = 0; exprIndex < sourceShuttledExpressions.size();
exprIndex++) {
+ Expression expressionShuttledToRewrite =
sourceShuttledExpressions.get(exprIndex);
if (expressionShuttledToRewrite instanceof Literal) {
rewrittenExpressions.add(expressionShuttledToRewrite);
continue;
}
- final Set<Object> slotsToRewrite =
+ final Set<Expression> slotsToRewrite =
expressionShuttledToRewrite.collectToSet(expression ->
expression instanceof Slot);
final Set<SlotReference> variants =
expressionShuttledToRewrite.collectToSet(expression ->
expression instanceof SlotReference
- && ((SlotReference) expression).getDataType() instanceof
VariantType);
+ && ((SlotReference) expression).getDataType()
instanceof VariantType);
extendMappingByVariant(variants,
targetToTargetReplacementMappingQueryBased);
Expression replacedExpression =
ExpressionUtils.replace(expressionShuttledToRewrite,
targetToTargetReplacementMappingQueryBased);
- if (replacedExpression.anyMatch(slotsToRewrite::contains)) {
- // if contains any slot to rewrite, which means can not be
rewritten by target, bail out
- return ImmutableList.of();
+ Set<Expression> replacedExpressionSlotQueryUsed =
replacedExpression.collect(slotsToRewrite::contains);
+ if (!replacedExpressionSlotQueryUsed.isEmpty()) {
+ // if contains any slot to rewrite, which means can not be
rewritten by target,
+ // expressionShuttledToRewrite is slot#0 > '2024-01-01' but mv
plan output is date_trunc(slot#0, 'day')
+ // which would try to rewrite
+ if (viewExprParamToDateTruncMap.isEmpty()
+ || expressionShuttledToRewrite.children().isEmpty()
+ || !(expressionShuttledToRewrite instanceof
ComparisonPredicate)) {
+ // view doesn't have date_trunc, or
+ // expressionShuttledToRewrite is not ComparisonPredicate,
bail out
+ return ImmutableList.of();
+ }
+ Expression queryShuttledExprParam =
expressionShuttledToRewrite.child(0);
+ Expression queryOriginalExpr =
sourceExpressionsToWrite.get(exprIndex);
+ if (!queryExprToInfoMap.containsKey(queryOriginalExpr)
+ ||
!viewExprParamToDateTruncMap.containsKey(queryShuttledExprParam)) {
+ // query expr contains expression info or mv out contains
date_trunc expression,
+ // if not, can not try to rewritten by view date_trunc,
bail out
+ return ImmutableList.of();
+ }
+ Map<Expression, Expression> datetruncMap = new HashMap<>();
+ Literal queryUsedLiteral =
queryExprToInfoMap.get(queryOriginalExpr).literal;
+ if (!(queryUsedLiteral instanceof DateLiteral)) {
+ return ImmutableList.of();
+ }
+ datetruncMap.put(queryShuttledExprParam, queryUsedLiteral);
+ Expression dateTruncWithLiteral = ExpressionUtils.replace(
+
viewExprParamToDateTruncMap.get(queryShuttledExprParam), datetruncMap);
+ Expression foldedExpressionWithLiteral =
FoldConstantRuleOnFE.evaluate(dateTruncWithLiteral,
+ new ExpressionRewriteContext(cascadesContext));
+ if (!(foldedExpressionWithLiteral instanceof DateLiteral)) {
+ return ImmutableList.of();
+ }
+ if (((DateLiteral) foldedExpressionWithLiteral).getDouble() ==
queryUsedLiteral.getDouble()) {
+ // after date_trunc simplify if equals to original
expression, expr could be rewritten by mv
+ replacedExpression =
ExpressionUtils.replace(expressionShuttledToRewrite,
+ targetToTargetReplacementMappingQueryBased,
+ viewExprParamToDateTruncMap);
+ }
+ if (replacedExpression.anyMatch(slotsToRewrite::contains)) {
+ // has expression not rewritten successfully, bail out
+ return ImmutableList.of();
+ }
}
rewrittenExpressions.add(replacedExpression);
}
@@ -752,40 +809,20 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
}
// viewEquivalenceClass to query based
// equal predicate compensate
- final Set<Expression> equalCompensateConjunctions =
Predicates.compensateEquivalence(
- queryStructInfo,
- viewStructInfo,
- viewToQuerySlotMapping,
- comparisonResult);
+ final Map<Expression, ExpressionInfo> equalCompensateConjunctions =
Predicates.compensateEquivalence(
+ queryStructInfo, viewStructInfo, viewToQuerySlotMapping,
comparisonResult);
// range compensate
- final Set<Expression> rangeCompensatePredicates =
Predicates.compensateRangePredicate(
- queryStructInfo,
- viewStructInfo,
- viewToQuerySlotMapping,
- comparisonResult,
- cascadesContext);
+ final Map<Expression, ExpressionInfo> rangeCompensatePredicates =
+ Predicates.compensateRangePredicate(queryStructInfo,
viewStructInfo, viewToQuerySlotMapping,
+ comparisonResult, cascadesContext);
// residual compensate
- final Set<Expression> residualCompensatePredicates =
Predicates.compensateResidualPredicate(
- queryStructInfo,
- viewStructInfo,
- viewToQuerySlotMapping,
- comparisonResult);
+ final Map<Expression, ExpressionInfo> residualCompensatePredicates =
Predicates.compensateResidualPredicate(
+ queryStructInfo, viewStructInfo, viewToQuerySlotMapping,
comparisonResult);
if (equalCompensateConjunctions == null || rangeCompensatePredicates
== null
|| residualCompensatePredicates == null) {
return SplitPredicate.INVALID_INSTANCE;
}
- if (equalCompensateConjunctions.stream().anyMatch(expr ->
expr.containsType(AggregateFunction.class))
- || rangeCompensatePredicates.stream().anyMatch(expr ->
expr.containsType(AggregateFunction.class))
- || residualCompensatePredicates.stream().anyMatch(expr ->
- expr.containsType(AggregateFunction.class))) {
- return SplitPredicate.INVALID_INSTANCE;
- }
- return SplitPredicate.of(equalCompensateConjunctions.isEmpty() ?
BooleanLiteral.TRUE
- : ExpressionUtils.and(equalCompensateConjunctions),
- rangeCompensatePredicates.isEmpty() ? BooleanLiteral.TRUE
- : ExpressionUtils.and(rangeCompensatePredicates),
- residualCompensatePredicates.isEmpty() ? BooleanLiteral.TRUE
- : ExpressionUtils.and(residualCompensatePredicates));
+ return SplitPredicate.of(equalCompensateConjunctions,
rangeCompensatePredicates, residualCompensatePredicates);
}
/**
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewScanRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewScanRule.java
index 7cd49c94b09..7b2f685cf30 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewScanRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/AbstractMaterializedViewScanRule.java
@@ -26,6 +26,7 @@ import
org.apache.doris.nereids.trees.expressions.NamedExpression;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import com.google.common.collect.ImmutableMap;
import com.google.common.collect.ImmutableSet;
import java.util.List;
@@ -50,7 +51,8 @@ public abstract class AbstractMaterializedViewScanRule
extends AbstractMateriali
queryStructInfo.getTopPlan(),
materializationContext.getShuttledExprToScanExprMapping(),
targetToSourceMapping,
- queryStructInfo.getTableBitSet()
+ queryStructInfo.getTableBitSet(),
+ ImmutableMap.of(), cascadesContext
);
// Can not rewrite, bail out
if (expressionsRewritten.isEmpty()) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/Predicates.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/Predicates.java
index 20ded0415ad..18095d5f60c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/Predicates.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/Predicates.java
@@ -23,24 +23,30 @@ import
org.apache.doris.nereids.rules.exploration.mv.mapping.SlotMapping;
import org.apache.doris.nereids.rules.expression.ExpressionNormalization;
import org.apache.doris.nereids.rules.expression.ExpressionOptimization;
import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext;
+import org.apache.doris.nereids.trees.expressions.ComparisonPredicate;
import org.apache.doris.nereids.trees.expressions.EqualTo;
import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.GreaterThan;
+import org.apache.doris.nereids.trees.expressions.LessThanEqual;
import org.apache.doris.nereids.trees.expressions.SlotReference;
+import
org.apache.doris.nereids.trees.expressions.functions.agg.AggregateFunction;
import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.Literal;
import org.apache.doris.nereids.util.ExpressionUtils;
import org.apache.doris.nereids.util.Utils;
import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Sets;
import java.util.ArrayList;
import java.util.Collection;
+import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Objects;
-import java.util.Optional;
import java.util.Set;
/**
@@ -81,7 +87,7 @@ public class Predicates {
/**
* compensate equivalence predicates
*/
- public static Set<Expression> compensateEquivalence(StructInfo
queryStructInfo,
+ public static Map<Expression, ExpressionInfo>
compensateEquivalence(StructInfo queryStructInfo,
StructInfo viewStructInfo,
SlotMapping viewToQuerySlotMapping,
ComparisonResult comparisonResult) {
@@ -92,9 +98,9 @@ public class Predicates {
if (viewEquivalenceClassQueryBased == null) {
return null;
}
- final Set<Expression> equalCompensateConjunctions = new HashSet<>();
+ final Map<Expression, ExpressionInfo> equalCompensateConjunctions =
new HashMap<>();
if (queryEquivalenceClass.isEmpty() && viewEquivalenceClass.isEmpty())
{
- equalCompensateConjunctions.add(BooleanLiteral.TRUE);
+ return ImmutableMap.of();
}
if (queryEquivalenceClass.isEmpty() &&
!viewEquivalenceClass.isEmpty()) {
return null;
@@ -109,37 +115,42 @@ public class Predicates {
// do equal compensate
Set<List<SlotReference>> mappedQueryEquivalenceSet =
queryToViewEquivalenceMapping.getEquivalenceClassSetMap().keySet();
- queryEquivalenceClass.getEquivalenceSetList().forEach(
- queryEquivalenceSet -> {
- // compensate the equivalence in query but not in view
- if
(!mappedQueryEquivalenceSet.contains(queryEquivalenceSet)) {
- Iterator<SlotReference> iterator =
queryEquivalenceSet.iterator();
- SlotReference first = iterator.next();
- while (iterator.hasNext()) {
- Expression equals = new EqualTo(first,
iterator.next());
- equalCompensateConjunctions.add(equals);
- }
- } else {
- // compensate the equivalence both in query and view,
but query has more equivalence
- List<SlotReference> viewEquivalenceSet =
-
queryToViewEquivalenceMapping.getEquivalenceClassSetMap().get(queryEquivalenceSet);
- List<SlotReference> copiedQueryEquivalenceSet = new
ArrayList<>(queryEquivalenceSet);
-
copiedQueryEquivalenceSet.removeAll(viewEquivalenceSet);
- SlotReference first =
viewEquivalenceSet.iterator().next();
- for (SlotReference slotReference :
copiedQueryEquivalenceSet) {
- Expression equals = new EqualTo(first,
slotReference);
- equalCompensateConjunctions.add(equals);
- }
+
+ for (List<SlotReference> queryEquivalenceSet :
queryEquivalenceClass.getEquivalenceSetList()) {
+ // compensate the equivalence in query but not in view
+ if (!mappedQueryEquivalenceSet.contains(queryEquivalenceSet)) {
+ Iterator<SlotReference> iterator =
queryEquivalenceSet.iterator();
+ SlotReference first = iterator.next();
+ while (iterator.hasNext()) {
+ Expression equals = new EqualTo(first, iterator.next());
+ if (equals.anyMatch(AggregateFunction.class::isInstance)) {
+ return null;
+ }
+ equalCompensateConjunctions.put(equals,
ExpressionInfo.EMPTY);
+ }
+ } else {
+ // compensate the equivalence both in query and view, but
query has more equivalence
+ List<SlotReference> viewEquivalenceSet =
+
queryToViewEquivalenceMapping.getEquivalenceClassSetMap().get(queryEquivalenceSet);
+ List<SlotReference> copiedQueryEquivalenceSet = new
ArrayList<>(queryEquivalenceSet);
+ copiedQueryEquivalenceSet.removeAll(viewEquivalenceSet);
+ SlotReference first = viewEquivalenceSet.iterator().next();
+ for (SlotReference slotReference : copiedQueryEquivalenceSet) {
+ Expression equals = new EqualTo(first, slotReference);
+ if (equals.anyMatch(AggregateFunction.class::isInstance)) {
+ return null;
}
+ equalCompensateConjunctions.put(equals,
ExpressionInfo.EMPTY);
}
- );
+ }
+ }
return equalCompensateConjunctions;
}
/**
* compensate range predicates
*/
- public static Set<Expression> compensateRangePredicate(StructInfo
queryStructInfo,
+ public static Map<Expression, ExpressionInfo>
compensateRangePredicate(StructInfo queryStructInfo,
StructInfo viewStructInfo,
SlotMapping viewToQuerySlotMapping,
ComparisonResult comparisonResult,
@@ -147,19 +158,22 @@ public class Predicates {
SplitPredicate querySplitPredicate =
queryStructInfo.getSplitPredicate();
SplitPredicate viewSplitPredicate = viewStructInfo.getSplitPredicate();
- Expression queryRangePredicate =
querySplitPredicate.getRangePredicate();
- Expression viewRangePredicate = viewSplitPredicate.getRangePredicate();
- Expression viewRangePredicateQueryBased =
- ExpressionUtils.replace(viewRangePredicate,
viewToQuerySlotMapping.toSlotReferenceMap());
+ Set<Expression> viewRangeQueryBasedSet = new HashSet<>();
+ for (Expression viewExpression :
viewSplitPredicate.getRangePredicateMap().keySet()) {
+ viewRangeQueryBasedSet.add(
+ ExpressionUtils.replace(viewExpression,
viewToQuerySlotMapping.toSlotReferenceMap()));
+ }
+ viewRangeQueryBasedSet.remove(BooleanLiteral.TRUE);
+
+ Set<Expression> queryRangeSet =
querySplitPredicate.getRangePredicateMap().keySet();
+ queryRangeSet.remove(BooleanLiteral.TRUE);
- Set<Expression> queryRangeSet =
ExpressionUtils.extractConjunctionToSet(queryRangePredicate);
- Set<Expression> viewRangeQueryBasedSet =
ExpressionUtils.extractConjunctionToSet(viewRangePredicateQueryBased);
Set<Expression> differentExpressions = new HashSet<>();
Sets.difference(queryRangeSet,
viewRangeQueryBasedSet).copyInto(differentExpressions);
Sets.difference(viewRangeQueryBasedSet,
queryRangeSet).copyInto(differentExpressions);
// the range predicate in query and view is same, don't need to
compensate
if (differentExpressions.isEmpty()) {
- return differentExpressions;
+ return ImmutableMap.of();
}
// try to normalize the different expressions
Set<Expression> normalizedExpressions =
@@ -168,7 +182,24 @@ public class Predicates {
// normalized expressions is not in query, can not compensate
return null;
}
- return normalizedExpressions;
+ Map<Expression, ExpressionInfo> normalizedExpressionsWithLiteral = new
HashMap<>();
+ for (Expression expression : normalizedExpressions) {
+ Set<Literal> literalSet = expression.collect(expressionTreeNode ->
expressionTreeNode instanceof Literal);
+ if (!(expression instanceof ComparisonPredicate)
+ || (expression instanceof GreaterThan || expression
instanceof LessThanEqual)
+ || literalSet.size() != 1) {
+ if (expression.anyMatch(AggregateFunction.class::isInstance)) {
+ return null;
+ }
+ normalizedExpressionsWithLiteral.put(expression,
ExpressionInfo.EMPTY);
+ continue;
+ }
+ if (expression.anyMatch(AggregateFunction.class::isInstance)) {
+ return null;
+ }
+ normalizedExpressionsWithLiteral.put(expression, new
ExpressionInfo(literalSet.iterator().next()));
+ }
+ return normalizedExpressionsWithLiteral;
}
private static Set<Expression> normalizeExpression(Expression expression,
CascadesContext cascadesContext) {
@@ -183,31 +214,38 @@ public class Predicates {
/**
* compensate residual predicates
*/
- public static Set<Expression> compensateResidualPredicate(StructInfo
queryStructInfo,
+ public static Map<Expression, ExpressionInfo>
compensateResidualPredicate(StructInfo queryStructInfo,
StructInfo viewStructInfo,
SlotMapping viewToQuerySlotMapping,
ComparisonResult comparisonResult) {
// TODO Residual predicates compensate, simplify implementation
currently.
SplitPredicate querySplitPredicate =
queryStructInfo.getSplitPredicate();
SplitPredicate viewSplitPredicate = viewStructInfo.getSplitPredicate();
- Expression queryResidualPredicate =
querySplitPredicate.getResidualPredicate();
- Expression viewResidualPredicate =
viewSplitPredicate.getResidualPredicate();
- Expression viewResidualPredicateQueryBased =
- ExpressionUtils.replace(viewResidualPredicate,
viewToQuerySlotMapping.toSlotReferenceMap());
- Set<Expression> queryResidualSet =
-
Sets.newHashSet(ExpressionUtils.extractConjunction(queryResidualPredicate));
- Set<Expression> viewResidualQueryBasedSet =
-
Sets.newHashSet(ExpressionUtils.extractConjunction(viewResidualPredicateQueryBased));
+
+ Set<Expression> viewResidualQueryBasedSet = new HashSet<>();
+ for (Expression viewExpression :
viewSplitPredicate.getResidualPredicateMap().keySet()) {
+ viewResidualQueryBasedSet.add(
+ ExpressionUtils.replace(viewExpression,
viewToQuerySlotMapping.toSlotReferenceMap()));
+ }
+ viewResidualQueryBasedSet.remove(BooleanLiteral.TRUE);
+
+ Set<Expression> queryResidualSet =
querySplitPredicate.getResidualPredicateMap().keySet();
// remove unnecessary literal BooleanLiteral.TRUE
queryResidualSet.remove(BooleanLiteral.TRUE);
- viewResidualQueryBasedSet.remove(BooleanLiteral.TRUE);
// query residual predicate can not contain all view residual
predicate when view have residual predicate,
// bail out
if (!queryResidualSet.containsAll(viewResidualQueryBasedSet)) {
return null;
}
queryResidualSet.removeAll(viewResidualQueryBasedSet);
- return queryResidualSet;
+ Map<Expression, ExpressionInfo> expressionExpressionInfoMap = new
HashMap<>();
+ for (Expression needCompensate : queryResidualSet) {
+ if (needCompensate.anyMatch(AggregateFunction.class::isInstance)) {
+ return null;
+ }
+ expressionExpressionInfoMap.put(needCompensate,
ExpressionInfo.EMPTY);
+ }
+ return expressionExpressionInfoMap;
}
@Override
@@ -215,41 +253,57 @@ public class Predicates {
return Utils.toSqlString("Predicates", "pulledUpPredicates",
pulledUpPredicates);
}
+ /**
+ * The struct info for expression, such as the constant that it used
+ */
+ public static final class ExpressionInfo {
+
+ public static final ExpressionInfo EMPTY = new ExpressionInfo(null);
+
+ public final Literal literal;
+
+ public ExpressionInfo(Literal literal) {
+ this.literal = literal;
+ }
+ }
+
/**
* The split different representation for predicate expression, such as
equal, range and residual predicate.
*/
public static final class SplitPredicate {
public static final SplitPredicate INVALID_INSTANCE =
SplitPredicate.of(null, null, null);
- private final Optional<Expression> equalPredicate;
- private final Optional<Expression> rangePredicate;
- private final Optional<Expression> residualPredicate;
-
- public SplitPredicate(Expression equalPredicate, Expression
rangePredicate, Expression residualPredicate) {
- this.equalPredicate = Optional.ofNullable(equalPredicate);
- this.rangePredicate = Optional.ofNullable(rangePredicate);
- this.residualPredicate = Optional.ofNullable(residualPredicate);
+ private final Map<Expression, ExpressionInfo> equalPredicateMap;
+ private final Map<Expression, ExpressionInfo> rangePredicateMap;
+ private final Map<Expression, ExpressionInfo> residualPredicateMap;
+
+ public SplitPredicate(Map<Expression, ExpressionInfo>
equalPredicateMap,
+ Map<Expression, ExpressionInfo> rangePredicateMap,
+ Map<Expression, ExpressionInfo> residualPredicateMap) {
+ this.equalPredicateMap = equalPredicateMap;
+ this.rangePredicateMap = rangePredicateMap;
+ this.residualPredicateMap = residualPredicateMap;
}
- public Expression getEqualPredicate() {
- return equalPredicate.orElse(BooleanLiteral.TRUE);
+ public Map<Expression, ExpressionInfo> getEqualPredicateMap() {
+ return equalPredicateMap;
}
- public Expression getRangePredicate() {
- return rangePredicate.orElse(BooleanLiteral.TRUE);
+ public Map<Expression, ExpressionInfo> getRangePredicateMap() {
+ return rangePredicateMap;
}
- public Expression getResidualPredicate() {
- return residualPredicate.orElse(BooleanLiteral.TRUE);
+ public Map<Expression, ExpressionInfo> getResidualPredicateMap() {
+ return residualPredicateMap;
}
/**
* SplitPredicate construct
*/
- public static SplitPredicate of(Expression equalPredicates,
- Expression rangePredicates,
- Expression residualPredicates) {
- return new SplitPredicate(equalPredicates, rangePredicates,
residualPredicates);
+ public static SplitPredicate of(Map<Expression, ExpressionInfo>
equalPredicateMap,
+ Map<Expression, ExpressionInfo> rangePredicateMap,
+ Map<Expression, ExpressionInfo> residualPredicateMap) {
+ return new SplitPredicate(equalPredicateMap, rangePredicateMap,
residualPredicateMap);
}
/**
@@ -259,23 +313,25 @@ public class Predicates {
return Objects.equals(this, INVALID_INSTANCE);
}
+ /**
+ * Get expression list in predicates
+ */
public List<Expression> toList() {
- return ImmutableList.of(getEqualPredicate(), getRangePredicate(),
getResidualPredicate());
+ if (isInvalid()) {
+ return ImmutableList.of();
+ }
+ List<Expression> flattenExpressions = new
ArrayList<>(getEqualPredicateMap().keySet());
+ flattenExpressions.addAll(getRangePredicateMap().keySet());
+ flattenExpressions.addAll(getResidualPredicateMap().keySet());
+ return flattenExpressions;
}
/**
* Check the predicates in SplitPredicate is whether all true or not
*/
public boolean isAlwaysTrue() {
- Expression equalExpr = getEqualPredicate();
- Expression rangeExpr = getRangePredicate();
- Expression residualExpr = getResidualPredicate();
- return equalExpr instanceof BooleanLiteral
- && rangeExpr instanceof BooleanLiteral
- && residualExpr instanceof BooleanLiteral
- && ((BooleanLiteral) equalExpr).getValue()
- && ((BooleanLiteral) rangeExpr).getValue()
- && ((BooleanLiteral) residualExpr).getValue();
+ return getEqualPredicateMap().isEmpty() &&
getRangePredicateMap().isEmpty()
+ && getResidualPredicateMap().isEmpty();
}
@Override
@@ -287,22 +343,22 @@ public class Predicates {
return false;
}
SplitPredicate that = (SplitPredicate) o;
- return Objects.equals(equalPredicate, that.equalPredicate)
- && Objects.equals(rangePredicate, that.rangePredicate)
- && Objects.equals(residualPredicate,
that.residualPredicate);
+ return Objects.equals(equalPredicateMap, that.equalPredicateMap)
+ && Objects.equals(rangePredicateMap,
that.residualPredicateMap)
+ && Objects.equals(residualPredicateMap,
that.residualPredicateMap);
}
@Override
public int hashCode() {
- return Objects.hash(equalPredicate, rangePredicate,
residualPredicate);
+ return Objects.hash(equalPredicateMap, rangePredicateMap,
residualPredicateMap);
}
@Override
public String toString() {
return Utils.toSqlString("SplitPredicate",
- "equalPredicate", equalPredicate,
- "rangePredicate", rangePredicate,
- "residualPredicate", residualPredicate);
+ "equalPredicate", equalPredicateMap,
+ "rangePredicate", rangePredicateMap,
+ "residualPredicate", residualPredicateMap);
}
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/PredicatesSplitter.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/PredicatesSplitter.java
index f7182eeab73..a7a5af74d00 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/PredicatesSplitter.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/PredicatesSplitter.java
@@ -17,6 +17,7 @@
package org.apache.doris.nereids.rules.exploration.mv;
+import org.apache.doris.nereids.rules.exploration.mv.Predicates.ExpressionInfo;
import org.apache.doris.nereids.trees.expressions.Alias;
import org.apache.doris.nereids.trees.expressions.Cast;
import org.apache.doris.nereids.trees.expressions.ComparisonPredicate;
@@ -28,9 +29,9 @@ import
org.apache.doris.nereids.trees.expressions.literal.Literal;
import
org.apache.doris.nereids.trees.expressions.visitor.DefaultExpressionVisitor;
import org.apache.doris.nereids.util.ExpressionUtils;
-import java.util.LinkedHashSet;
+import java.util.HashMap;
import java.util.List;
-import java.util.Set;
+import java.util.Map;
/**
* Split the expression to equal, range and residual predicate.
@@ -39,9 +40,9 @@ import java.util.Set;
*/
public class PredicatesSplitter {
- private final Set<Expression> equalPredicates = new LinkedHashSet<>();
- private final Set<Expression> rangePredicates = new LinkedHashSet<>();
- private final Set<Expression> residualPredicates = new LinkedHashSet<>();
+ private final Map<Expression, ExpressionInfo> equalPredicates = new
HashMap<>();
+ private final Map<Expression, ExpressionInfo> rangePredicates = new
HashMap<>();
+ private final Map<Expression, ExpressionInfo> residualPredicates = new
HashMap<>();
private final List<Expression> conjunctExpressions;
public PredicatesSplitter(Expression target) {
@@ -65,19 +66,19 @@ public class PredicatesSplitter {
boolean rightArgOnlyContainsColumnRef =
containOnlyColumnRef(rightArg, true);
if (comparisonPredicate instanceof EqualPredicate) {
if (leftArgOnlyContainsColumnRef &&
rightArgOnlyContainsColumnRef) {
- equalPredicates.add(comparisonPredicate);
+ equalPredicates.put(comparisonPredicate,
ExpressionInfo.EMPTY);
return null;
} else if ((leftArgOnlyContainsColumnRef && rightArg
instanceof Literal)
|| (rightArgOnlyContainsColumnRef && leftArg
instanceof Literal)) {
- rangePredicates.add(comparisonPredicate);
+ rangePredicates.put(comparisonPredicate,
ExpressionInfo.EMPTY);
} else {
- residualPredicates.add(comparisonPredicate);
+ residualPredicates.put(comparisonPredicate,
ExpressionInfo.EMPTY);
}
} else if ((leftArgOnlyContainsColumnRef && rightArg instanceof
Literal)
|| (rightArgOnlyContainsColumnRef && leftArg instanceof
Literal)) {
- rangePredicates.add(comparisonPredicate);
+ rangePredicates.put(comparisonPredicate, ExpressionInfo.EMPTY);
} else {
- residualPredicates.add(comparisonPredicate);
+ residualPredicates.put(comparisonPredicate,
ExpressionInfo.EMPTY);
}
return null;
}
@@ -86,25 +87,22 @@ public class PredicatesSplitter {
public Void visitInPredicate(InPredicate inPredicate, Void context) {
if (containOnlyColumnRef(inPredicate.getCompareExpr(), true)
&&
(ExpressionUtils.isAllLiteral(inPredicate.getOptions()))) {
- rangePredicates.add(inPredicate);
+ rangePredicates.put(inPredicate, ExpressionInfo.EMPTY);
} else {
- residualPredicates.add(inPredicate);
+ residualPredicates.put(inPredicate, ExpressionInfo.EMPTY);
}
return null;
}
@Override
public Void visit(Expression expr, Void context) {
- residualPredicates.add(expr);
+ residualPredicates.put(expr, ExpressionInfo.EMPTY);
return null;
}
}
public Predicates.SplitPredicate getSplitPredicate() {
- return Predicates.SplitPredicate.of(
- equalPredicates.isEmpty() ? null :
ExpressionUtils.and(equalPredicates),
- rangePredicates.isEmpty() ? null :
ExpressionUtils.and(rangePredicates),
- residualPredicates.isEmpty() ? null :
ExpressionUtils.and(residualPredicates));
+ return Predicates.SplitPredicate.of(equalPredicates, rangePredicates,
residualPredicates);
}
private static boolean containOnlyColumnRef(Expression expression, boolean
allowCast) {
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 365360e06b0..3f0397dc411 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
@@ -286,7 +286,7 @@ public class StructInfo {
.collect(Collectors.toList());
SplitPredicate splitPredicate =
Predicates.splitPredicates(ExpressionUtils.and(shuttledExpression));
EquivalenceClass equivalenceClass = new EquivalenceClass();
- for (Expression expression :
ExpressionUtils.extractConjunction(splitPredicate.getEqualPredicate())) {
+ for (Expression expression :
splitPredicate.getEqualPredicateMap().keySet()) {
if (expression instanceof Literal) {
continue;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/ExpressionUtils.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/ExpressionUtils.java
index 25637d1b816..a84af3c8336 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/ExpressionUtils.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/ExpressionUtils.java
@@ -463,6 +463,31 @@ public class ExpressionUtils {
});
}
+ /**
+ * Replace expression node in the expression tree by `replaceMap` in
top-down manner.
+ * For example.
+ * <pre>
+ * input expression: a > 1
+ * replaceMap: d -> b + c, transferMap: a -> d
+ * firstly try to get mapping expression from replaceMap by a, if can not
then
+ * get mapping d from transferMap by a
+ * and get mapping b + c from replaceMap by d
+ * output:
+ * b + c > 1
+ * </pre>
+ */
+ public static Expression replace(Expression expr, Map<? extends
Expression, ? extends Expression> replaceMap,
+ Map<? extends Expression, ? extends Expression> transferMap) {
+ return expr.rewriteDownShortCircuit(e -> {
+ Expression replacedExpr = replaceMap.get(e);
+ if (replacedExpr != null) {
+ return replacedExpr;
+ }
+ replacedExpr = replaceMap.get(transferMap.get(e));
+ return replacedExpr == null ? e : replacedExpr;
+ });
+ }
+
public static List<Expression> replace(List<Expression> exprs,
Map<? extends Expression, ? extends Expression> replaceMap) {
ImmutableList.Builder<Expression> result =
ImmutableList.builderWithExpectedSize(exprs.size());
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/PredicatesSplitterTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/PredicatesSplitterTest.java
index 0374244ce56..9ba77c7ae40 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/PredicatesSplitterTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/PredicatesSplitterTest.java
@@ -24,7 +24,7 @@ import
org.apache.doris.nereids.rules.exploration.mv.Predicates.SplitPredicate;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.SlotReference;
-import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
+import org.apache.doris.nereids.util.ExpressionUtils;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
@@ -67,23 +67,27 @@ public class PredicatesSplitterTest extends
ExpressionRewriteTestHelper {
if (!StringUtils.isEmpty(expectedEqualExpr)) {
Expression equalExpression =
replaceUnboundSlot(PARSER.parseExpression(expectedEqualExpr), mem);
- Assertions.assertEquals(equalExpression,
splitPredicate.getEqualPredicate());
+
Assertions.assertEquals(ExpressionUtils.extractConjunctionToSet(equalExpression),
+ splitPredicate.getEqualPredicateMap().keySet());
} else {
- Assertions.assertEquals(splitPredicate.getEqualPredicate(),
BooleanLiteral.TRUE);
+
Assertions.assertTrue(splitPredicate.getEqualPredicateMap().isEmpty());
}
if (!StringUtils.isEmpty(expectedRangeExpr)) {
Expression rangeExpression =
replaceUnboundSlot(PARSER.parseExpression(expectedRangeExpr), mem);
- Assertions.assertEquals(rangeExpression,
splitPredicate.getRangePredicate());
+
Assertions.assertEquals(ExpressionUtils.extractConjunctionToSet(rangeExpression),
+ splitPredicate.getRangePredicateMap().keySet());
} else {
- Assertions.assertEquals(splitPredicate.getRangePredicate(),
BooleanLiteral.TRUE);
+
Assertions.assertTrue(splitPredicate.getRangePredicateMap().isEmpty());
}
if (!StringUtils.isEmpty(expectedResidualExpr)) {
Expression residualExpression =
replaceUnboundSlot(PARSER.parseExpression(expectedResidualExpr), mem);
- Assertions.assertEquals(residualExpression,
splitPredicate.getResidualPredicate());
+ Assertions.assertEquals(
+
ExpressionUtils.extractConjunctionToSet(residualExpression),
+ splitPredicate.getResidualPredicateMap().keySet());
} else {
- Assertions.assertEquals(splitPredicate.getResidualPredicate(),
BooleanLiteral.TRUE);
+
Assertions.assertTrue(splitPredicate.getResidualPredicateMap().isEmpty());
}
}
diff --git
a/regression-test/data/nereids_rules_p0/mv/date_trunc/mv_with_date_trunc.out
b/regression-test/data/nereids_rules_p0/mv/date_trunc/mv_with_date_trunc.out
new file mode 100644
index 00000000000..d02e2938633
--- /dev/null
+++ b/regression-test/data/nereids_rules_p0/mv/date_trunc/mv_with_date_trunc.out
@@ -0,0 +1,750 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !query1_0_before --
+b 4
+
+-- !query1_0_after --
+b 4
+
+-- !query1_1_before --
+b 4
+
+-- !query1_1_after --
+b 4
+
+-- !query1_1_0_before --
+b
+b
+b
+b
+
+-- !query1_1_0_after --
+b
+b
+b
+b
+
+-- !query1_1_1_before --
+b
+b
+b
+b
+
+-- !query1_1_1_after --
+b
+b
+b
+b
+
+-- !query1_2_before --
+b 1
+
+-- !query1_2_after --
+b 1
+
+-- !query1_3_before --
+b 21
+
+-- !query1_3_after --
+b 21
+
+-- !query1_3_0_before --
+b
+b
+b
+b
+
+-- !query1_3_0_after --
+b
+b
+b
+b
+
+-- !query1_3_1_before --
+b
+b
+b
+b
+
+-- !query1_3_1_after --
+b
+b
+b
+b
+
+-- !query1_3_2_before --
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+
+-- !query1_3_2_after --
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+
+-- !query1_3_3_before --
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+
+-- !query1_3_3_after --
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+
+-- !query1_4_before --
+b 21
+
+-- !query1_4_after --
+b 21
+
+-- !query1_5_before --
+b 21
+
+-- !query1_5_after --
+b 21
+
+-- !query1_5_0_before --
+b
+b
+b
+b
+b
+
+-- !query1_5_0_after --
+b
+b
+b
+b
+b
+
+-- !query1_5_1_before --
+b
+b
+b
+b
+b
+
+-- !query1_5_1_after --
+b
+b
+b
+b
+b
+
+-- !query1_6_before --
+b 18
+
+-- !query1_6_after --
+b 18
+
+-- !query1_7_before --
+b 18
+
+-- !query1_7_after --
+b 18
+
+-- !query1_7_0_before --
+b
+b
+b
+b
+b
+
+-- !query1_7_0_after --
+b
+b
+b
+b
+b
+
+-- !query1_7_1_before --
+b
+b
+b
+b
+b
+
+-- !query1_7_1_after --
+b
+b
+b
+b
+b
+
+-- !query1_8_before --
+b 6
+
+-- !query1_8_after --
+b 6
+
+-- !query1_9_before --
+b 6
+
+-- !query1_9_after --
+b 6
+
+-- !query1_9_0_before --
+b
+b
+b
+b
+b
+b
+
+-- !query1_9_0_after --
+b
+b
+b
+b
+b
+b
+
+-- !query1_9_1_before --
+b
+b
+b
+b
+b
+b
+
+-- !query1_9_1_after --
+b
+b
+b
+b
+b
+b
+
+-- !query1_10_before --
+b 25
+d 4
+
+-- !query1_10_after --
+b 25
+d 4
+
+-- !query1_11_before --
+b 25
+d 4
+
+-- !query1_11_after --
+b 25
+d 4
+
+-- !query1_11_0_before --
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+d
+d
+d
+d
+
+-- !query1_11_0_after --
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+d
+d
+d
+d
+
+-- !query1_11_1_before --
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+d
+d
+d
+d
+
+-- !query1_11_1_after --
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+d
+d
+d
+d
+
+-- !query1_12_before --
+b 6
+
+-- !query1_12_after --
+b 6
+
+-- !query1_13_before --
+b 6
+
+-- !query1_13_after --
+b 6
+
+-- !query1_13_0_before --
+b
+b
+b
+b
+b
+b
+
+-- !query1_13_0_after --
+b
+b
+b
+b
+b
+b
+
+-- !query1_13_1_before --
+b
+b
+b
+b
+b
+b
+
+-- !query1_13_1_after --
+b
+b
+b
+b
+b
+b
+
+-- !query1_14_before --
+b 28
+d 4
+
+-- !query1_14_after --
+b 28
+d 4
+
+-- !query1_15_before --
+b 28
+d 4
+
+-- !query1_15_after --
+b 28
+d 4
+
+-- !query1_15_0_before --
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+d
+d
+d
+d
+
+-- !query1_15_0_after --
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+d
+d
+d
+d
+
+-- !query1_15_1_before --
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+d
+d
+d
+d
+
+-- !query1_15_1_after --
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+b
+d
+d
+d
+d
+
+-- !query2_0_before --
+b 6
+
+-- !query2_0_after --
+b 6
+
+-- !query2_1_before --
+
+-- !query2_1_after --
+
+-- !query2_2_before --
+b 21
+
+-- !query2_2_after --
+b 21
+
+-- !query2_3_before --
+b 21
+
+-- !query2_3_after --
+b 21
+
+-- !query3_0_before --
+b 19
+d 4
+
+-- !query3_0_after --
+b 19
+d 4
+
+-- !query3_1_before --
+b 19
+d 4
+
+-- !query3_1_after --
+b 19
+d 4
+
+-- !query3_2_before --
+b 19
+d 4
+
+-- !query3_2_after --
+b 19
+d 4
+
+-- !query3_3_before --
+b 19
+d 4
+
+-- !query3_3_after --
+b 19
+d 4
+
+-- !query3_4_before --
+b 19
+d 4
+
+-- !query3_4_after --
+b 19
+d 4
+
+-- !query3_5_before --
+b 19
+d 4
+
+-- !query3_5_after --
+b 19
+d 4
+
+-- !query4_0_after --
+b 2023-01-01 6
+b 2023-04-01 3
+
+-- !query4_1_after --
+b 2023-01-01
+b 2023-01-01
+b 2023-01-01
+b 2023-01-01
+b 2023-01-01
+b 2023-01-01
+b 2023-04-01
+b 2023-04-01
+b 2023-04-01
+
+-- !query5_0_before --
+b 2023-10-17T00:00 4
+
+-- !query5_0_after --
+b 2023-10-17T00:00 4
+
+-- !query5_1_before --
+b 2023-10-16T00:00 1
+b 2023-10-17T00:00 3
+
+-- !query5_1_after --
+b 2023-10-16T00:00 1
+b 2023-10-17T00:00 3
+
+-- !query5_3_before --
+b 2023-10-01T17:09 1
+b 2023-10-01T19:10 1
+b 2023-10-01T21:11 1
+b 2023-10-15T00:00 2
+b 2023-10-15T23:59 1
+b 2023-10-16T00:00 2
+b 2023-10-16T23:59 1
+b 2023-10-17T00:00 3
+b 2023-10-17T23:59 1
+b 2023-10-18T00:00 3
+b 2023-10-18T23:59 1
+
+-- !query5_3_after --
+b 2023-10-01T17:09 1
+b 2023-10-01T19:10 1
+b 2023-10-01T21:11 1
+b 2023-10-15T00:00 2
+b 2023-10-15T23:59 1
+b 2023-10-16T00:00 2
+b 2023-10-16T23:59 1
+b 2023-10-17T00:00 3
+b 2023-10-17T23:59 1
+b 2023-10-18T00:00 3
+b 2023-10-18T23:59 1
+
diff --git
a/regression-test/suites/nereids_rules_p0/mv/date_trunc/mv_with_date_trunc.groovy
b/regression-test/suites/nereids_rules_p0/mv/date_trunc/mv_with_date_trunc.groovy
new file mode 100644
index 00000000000..115c39f8b50
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/mv/date_trunc/mv_with_date_trunc.groovy
@@ -0,0 +1,1378 @@
+package mv.date_trunc
+// 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("mv_with_date_trunc") {
+ 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 lineitem
+ """
+
+ 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 DATETIME(6) not null,
+ l_commitdate_var varchar(44) 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) (
+ FROM
+ ('2022-12-31') TO ('2023-12-31') INTERVAL 1 DAY
+ ) 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-01-01', '2023-01-01
00:00:00.999999', '2023-01-01 00:00:00.999999', '2023-10-17', 'a', 'b',
'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-01-01', '2023-01-01
02:01:45', '2023-01-01 02:01:45', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-01-01', '2023-01-01
03:02:43', '2023-01-01 03:02:43', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-04-01', '2023-04-01
05:03:42', '2023-04-01 05:03:42', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-04-01', '2023-04-01
07:04:40', '2023-04-01 07:04:40', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-04-01', '2023-04-01
09:05:36', '2023-04-01 09:05:36', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-07-01', '2023-07-01
11:06:35', '2023-07-01 11:06:35', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-07-01', '2023-07-01
13:07:34', '2023-07-01 13:07:34', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-07-01', '2023-07-01
15:08:30', '2023-07-01 15:08:30', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-01', '2023-10-01
17:09:25', '2023-10-01 17:09:25', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-01', '2023-10-01
19:10:24', '2023-10-01 19:10:24', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-01', '2023-10-01
21:11:23', '2023-10-01 21:11:23', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-15', '2023-10-15
00:00:00.000000', '2023-10-15 00:00:00.000000', '2023-10-17', 'a', 'b',
'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-15', '2023-10-15
00:00:00.000001', '2023-10-15 00:00:00.000001', '2023-10-17', 'a', 'b',
'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-15', '2023-10-15
00:00:00.000001', '2023-10-abcd', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-15', '2023-10-15
23:59:59.999999', '2023-10-15 23:59:59.999999', '2023-10-17', 'a', 'b',
'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-16', '2023-10-16
00:00:00', '2023-10-16 00:00:00', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-16', '2023-10-16
00:00:00.000001', '2023-10-16 00:00:00.000001', '2023-10-17', 'a', 'b',
'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-16', '2023-10-16
00:00:00.000001', '2023-10-abcd', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-16', '2023-10-16
23:59:59.999999', '2023-10-16 23:59:59.999999', '2023-10-17', 'a', 'b',
'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17
00:00:00.000000', '2023-10-17 00:00:00.000000', '2023-10-17', 'a', 'b',
'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17
00:00:00.000001', '2023-10-17 00:00:00.000001', '2023-10-17', 'a', 'b',
'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17
00:00:00.000001', '2023-10-17 00:abcd', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17
23:59:59.999999', '2023-10-17 23:59:59.999999', '2023-10-17', 'a', 'b',
'yyyyyyyyy'),
+ (2, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k','2023-10-18', '2023-10-18
00:00:00.000000', '2023-10-18 00:00:00.000000', '2023-10-18', 'a', 'b',
'yyyyyyyyy'),
+ (2, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k','2023-10-18', '2023-10-18
00:00:00.000001','2023-10-18 00:00:00.000001', '2023-10-18', 'a', 'b',
'yyyyyyyyy'),
+ (2, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k','2023-10-18', '2023-10-18
00:00:00.000001','2023-10-18 00:00:abcd', '2023-10-18', 'a', 'b', 'yyyyyyyyy'),
+ (2, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k','2023-10-18', '2023-10-18
23:59:59.999999', '2023-10-18 23:59:59.999999', '2023-10-18', 'a', 'b',
'yyyyyyyyy'),
+ (3, 2, 3, 6, 7.5, 8.5, 9.5, 10.5, 'k', 'o','2023-10-19', '2023-10-19
00:00:00','2023-10-19 00:00:00', '2023-10-19', 'c', 'd', 'xxxxxxxxx'),
+ (3, 2, 3, 6, 7.5, 8.5, 9.5, 10.5, 'k', 'o','2023-10-19', '2023-10-19
00:00:00.000001','2023-10-19 00:00:00.000001','2023-10-19', 'c', 'd',
'xxxxxxxxx'),
+ (3, 2, 3, 6, 7.5, 8.5, 9.5, 10.5, 'k', 'o','2023-10-19', '2023-10-19
00:00:00.000001','2023-10-19 00:00:00.abcd','2023-10-19', 'c', 'd',
'xxxxxxxxx'),
+ (3, 2, 3, 6, 7.5, 8.5, 9.5, 10.5, 'k', 'o','2023-10-19', '2023-10-19
23:59:59.999999','2023-10-19 23:59:59.999999', '2023-10-19', 'c', 'd',
'xxxxxxxxx');
+ """
+
+ sql """analyze table lineitem with sync;"""
+ sql """alter table lineitem modify column l_comment set stats
('row_count'='27');"""
+
+ // second, minute ,hour, day, week, month, quarter, year
+ // 1. expr in date_trunc is simple col
+ // 1.1 positive case, use field data type is datetime
+ // day
+ def mv1_0 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'day') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'day');
+ """
+ def query1_0 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ l_commitdate >= '2023-10-17' and l_commitdate < '2023-10-18'
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'day');
+ """
+ order_qt_query1_0_before "${query1_0}"
+ async_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
+ l_shipmode,
+ date_trunc(l_commitdate, 'day') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'day');
+ """
+ def query1_1 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ '2023-10-17' <= l_commitdate and '2023-10-18' > l_commitdate
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_1_before "${query1_1}"
+ async_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_1_0 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'day') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_1_0 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-10-17' <= l_commitdate and '2023-10-18' > l_commitdate;
+ """
+ order_qt_query1_1_0_before "${query1_1_0}"
+ // without group, should success
+ async_mv_rewrite_success(db, mv1_1_0, query1_1_0, "mv1_1_0")
+ order_qt_query1_1_0_after "${query1_1_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_1_0"""
+
+
+ def mv1_1_1 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'day') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_1_1 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-10-17' <= date_trunc(l_commitdate, 'day') and '2023-10-18' >
date_trunc(l_commitdate, 'day');
+ """
+ order_qt_query1_1_1_before "${query1_1_1}"
+ async_mv_rewrite_success(db, mv1_1_1, query1_1_1, "mv1_1_1")
+ order_qt_query1_1_1_after "${query1_1_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_1_1"""
+
+
+ // second
+ def mv1_2 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'second') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'second');
+ """
+ def query1_2 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ l_commitdate >= '2023-10-17 00:05:08' and l_commitdate < '2023-10-18'
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_2_before "${query1_2}"
+ async_mv_rewrite_success(db, mv1_2, query1_2, "mv1_2")
+ order_qt_query1_2_after "${query1_2}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_2"""
+
+
+ def mv1_3 = """
+ select
+ l_shipmode,
+ date_trunc(l_shipdate, 'second') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_shipdate, 'second');
+ """
+ def query1_3 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ '2023-01-17 00:05:08' <= l_shipdate and '2023-10-18' > l_shipdate
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_3_before "${query1_3}"
+ async_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"""
+
+
+ def mv1_3_0 = """
+ select
+ l_shipmode,
+ date_trunc(l_shipdate, 'second') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_3_0 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-10-16 00:05:08' <= l_shipdate and '2023-10-18' > l_shipdate;
+ """
+ order_qt_query1_3_0_before "${query1_3_0}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_3_0, query1_3_0,
"mv1_3_0")
+ order_qt_query1_3_0_after "${query1_3_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_3_0"""
+
+
+ def mv1_3_1 = """
+ select
+ l_shipmode,
+ date_trunc(l_shipdate, 'second') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_3_1 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-10-16 00:05:08' <= date_trunc(l_shipdate, 'second') and '2023-10-18'
> date_trunc(l_shipdate, 'second');
+ """
+ order_qt_query1_3_1_before "${query1_3_1}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_3_1, query1_3_1,
"mv1_3_1")
+ order_qt_query1_3_1_after "${query1_3_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_3_1"""
+
+
+ def mv1_3_2 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'second') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_3_2 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-01-17 00:05:08.999999' <= date_trunc(l_commitdate, 'second') and
'2023-10-18' > date_trunc(l_commitdate, 'second');
+ """
+ order_qt_query1_3_2_before "${query1_3_2}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_3_2, query1_3_2,
"mv1_3_2")
+ order_qt_query1_3_2_after "${query1_3_2}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_3_2"""
+
+
+ def mv1_3_3 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'second') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_3_3 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-01-17 00:05:08.999999' <= l_commitdate and '2023-10-18' >
l_commitdate;
+ """
+ order_qt_query1_3_3_before "${query1_3_3}"
+ // use micro second,should fail
+ async_mv_rewrite_fail(db, mv1_3_3, query1_3_3, "mv1_3_3")
+ order_qt_query1_3_3_after "${query1_3_3}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_3_3"""
+
+
+ // minute
+ def mv1_4 = """
+ select
+ l_shipmode,
+ date_trunc(l_shipdate, 'minute') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_shipdate, 'minute');
+ """
+ def query1_4 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ l_shipdate >= '2023-01-17 00:05:00' and l_shipdate < '2023-10-18'
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_4_before "${query1_4}"
+ async_mv_rewrite_success(db, mv1_4, query1_4, "mv1_4")
+ order_qt_query1_4_after "${query1_4}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_4"""
+
+
+ def mv1_5 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'minute') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'minute');
+ """
+ def query1_5 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ '2023-10-18' > l_commitdate and '2023-01-17 00:05:00' <= l_commitdate
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_5_before "${query1_5}"
+ async_mv_rewrite_success(db, mv1_5, query1_5, "mv1_5")
+ order_qt_query1_5_after "${query1_5}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_5"""
+
+
+ def mv1_5_0 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'minute') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_5_0 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-10-18' > l_commitdate and '2023-10-16 00:05:00' <= l_commitdate;
+ """
+ order_qt_query1_5_0_before "${query1_5_0}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_5_0, query1_5_0,
"mv1_5_0")
+ order_qt_query1_5_0_after "${query1_5_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_5_0"""
+
+ def mv1_5_1 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'minute') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_5_1 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-10-18' > date_trunc(l_commitdate, 'minute') and '2023-10-16
00:05:00' <= date_trunc(l_commitdate, 'minute');
+ """
+ order_qt_query1_5_1_before "${query1_5_1}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_5_1, query1_5_1,
"mv1_5_1")
+ order_qt_query1_5_1_after "${query1_5_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_5_1"""
+
+
+ // hour
+ def mv1_6 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'hour') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'hour');
+ """
+ def query1_6 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ l_commitdate < '2023-10-18' and l_commitdate >= '2023-05-17 01:00:00'
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_6_before "${query1_6}"
+ async_mv_rewrite_success(db, mv1_6, query1_6, "mv1_6")
+ order_qt_query1_6_after "${query1_6}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_6"""
+
+
+ def mv1_7 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'hour') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'hour');
+ """
+ def query1_7 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ '2023-05-17 01:00:00' <= l_commitdate and '2023-10-18' > l_commitdate
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_7_before "${query1_7}"
+ async_mv_rewrite_success(db, mv1_7, query1_7, "mv1_7")
+ order_qt_query1_7_after "${query1_7}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_7"""
+
+ def mv1_7_0 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'hour') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_7_0 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-10-16 01:00:00' <= l_commitdate and '2023-10-18' > l_commitdate;
+ """
+ order_qt_query1_7_0_before "${query1_7_0}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_7_0, query1_7_0,
"mv1_7_0")
+ order_qt_query1_7_0_after "${query1_7_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_7_0"""
+
+
+ def mv1_7_1 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'hour') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_7_1 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-10-16 01:00:00' <= date_trunc(l_commitdate, 'hour') and '2023-10-18'
> date_trunc(l_commitdate, 'hour');
+ """
+ order_qt_query1_7_1_before "${query1_7_1}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_7_1, query1_7_1,
"mv1_7_1")
+ order_qt_query1_7_1_after "${query1_7_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_7_1"""
+
+
+ // week
+ def mv1_8 = """
+ select
+ l_shipmode,
+ date_trunc(l_shipdate, 'week') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_shipdate, 'week');
+ """
+ def query1_8 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ l_shipdate < '2023-09-04' and '2023-01-16' <= l_shipdate
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_8_before "${query1_8}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_8, query1_8, "mv1_8")
+ order_qt_query1_8_after "${query1_8}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_8"""
+
+
+ def mv1_9 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'week') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'week');
+ """
+ def query1_9 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ '2023-01-16' <= l_commitdate and '2023-09-04' > l_commitdate
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_9_before "${query1_9}"
+ async_mv_rewrite_success(db, mv1_9, query1_9, "mv1_9")
+ order_qt_query1_9_after "${query1_9}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_9"""
+
+
+ def mv1_9_0 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'week') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_9_0 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-01-16' <= l_commitdate and '2023-09-04' > l_commitdate;
+ """
+ order_qt_query1_9_0_before "${query1_9_0}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_9_0, query1_9_0,
"mv1_9_0")
+ order_qt_query1_9_0_after "${query1_9_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_9_0"""
+
+
+
+ def mv1_9_1 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'week') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_9_1 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-01-16' <= date_trunc(l_commitdate, 'week') and '2023-09-04' >
date_trunc(l_commitdate, 'week');
+ """
+ order_qt_query1_9_1_before "${query1_9_1}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_9_1, query1_9_1,
"mv1_9_1")
+ order_qt_query1_9_1_after "${query1_9_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_9_1"""
+
+ // month
+ def mv1_10 = """
+ select
+ l_shipmode,
+ date_trunc(l_shipdate, 'month') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_shipdate, 'month');
+ """
+ def query1_10 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ l_shipdate >= '2023-02-01' and l_shipdate < '2023-12-01'
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_10_before "${query1_10}"
+ async_mv_rewrite_success(db, mv1_10, query1_10, "mv1_10")
+ order_qt_query1_10_after "${query1_10}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_10"""
+
+
+ def mv1_11 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'month') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'month');
+ """
+ def query1_11 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ '2023-02-01' <= l_commitdate and '2023-12-01' > l_commitdate
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_11_before "${query1_11}"
+ async_mv_rewrite_success(db, mv1_11, query1_11, "mv1_11")
+ order_qt_query1_11_after "${query1_11}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_11"""
+
+
+ def mv1_11_0 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'month') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_11_0 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-02-01' <= l_commitdate and '2023-12-01' > l_commitdate;
+ """
+ order_qt_query1_11_0_before "${query1_11_0}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_11_0, query1_11_0,
"mv1_11_0")
+ order_qt_query1_11_0_after "${query1_11_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_11_0"""
+
+
+ def mv1_11_1 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'month') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_11_1 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-02-01' <= date_trunc(l_commitdate, 'month') and '2023-12-01' >
date_trunc(l_commitdate, 'month');
+ """
+ order_qt_query1_11_1_before "${query1_11_1}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_11_1, query1_11_1,
"mv1_11_1")
+ order_qt_query1_11_1_after "${query1_11_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_11_1"""
+
+
+ // quarter
+ def mv1_12 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'quarter') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'quarter');
+ """
+ def query1_12 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ l_commitdate >= '2023-04-01' and l_commitdate < '2023-10-01'
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_12_before "${query1_12}"
+ async_mv_rewrite_success(db, mv1_12, query1_12, "mv1_12")
+ order_qt_query1_12_after "${query1_12}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_12"""
+
+
+ def mv1_13 = """
+ select
+ l_shipmode,
+ date_trunc(l_shipdate, 'quarter') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_shipdate, 'quarter');
+ """
+ def query1_13 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ '2023-04-01' <= l_shipdate and '2023-10-01' > l_shipdate
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_13_before "${query1_13}"
+ async_mv_rewrite_success(db, mv1_13, query1_13, "mv1_13")
+ order_qt_query1_13_after "${query1_13}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_13"""
+
+
+ def mv1_13_0 = """
+ select
+ l_shipmode,
+ date_trunc(l_shipdate, 'quarter') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_13_0 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-04-01' <= l_shipdate and '2023-10-01' > l_shipdate;
+ """
+ order_qt_query1_13_0_before "${query1_13_0}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_13_0, query1_13_0,
"mv1_13_0")
+ order_qt_query1_13_0_after "${query1_13_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_13_0"""
+
+
+ def mv1_13_1 = """
+ select
+ l_shipmode,
+ date_trunc(l_shipdate, 'quarter') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_13_1 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-04-01' <= date_trunc(l_shipdate, 'quarter') and '2023-10-01' >
date_trunc(l_shipdate, 'quarter');
+ """
+ order_qt_query1_13_1_before "${query1_13_1}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_13_1, query1_13_1,
"mv1_13_1")
+ order_qt_query1_13_1_after "${query1_13_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_13_1"""
+
+
+ // year
+ def mv1_14 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'year') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'year');
+ """
+ def query1_14 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ l_commitdate >= '2023-01-01' and l_commitdate < '2024-01-01'
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_14_before "${query1_14}"
+ async_mv_rewrite_success(db, mv1_14, query1_14, "mv1_14")
+ order_qt_query1_14_after "${query1_14}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_14"""
+
+
+ def mv1_15 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'year') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'year');
+ """
+ def query1_15 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ '2023-01-01' <= l_commitdate and '2024-01-01' > l_commitdate
+ group by
+ l_shipmode;
+ """
+ order_qt_query1_15_before "${query1_15}"
+ async_mv_rewrite_success(db, mv1_15, query1_15, "mv1_15")
+ order_qt_query1_15_after "${query1_15}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_15"""
+
+
+ def mv1_15_0 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'year') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_15_0 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-01-01' <= l_commitdate and '2024-01-01' > l_commitdate;
+ """
+ order_qt_query1_15_0_before "${query1_15_0}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_15_0, query1_15_0,
"mv1_15_0")
+ order_qt_query1_15_0_after "${query1_15_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_15_0"""
+
+
+ def mv1_15_1 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'year') as day_trunc
+ from
+ lineitem;
+ """
+ def query1_15_1 = """
+ select
+ l_shipmode
+ from
+ lineitem
+ where
+ '2023-01-01' <= date_trunc(l_commitdate, 'year') and '2024-01-01' >
date_trunc(l_commitdate, 'year');
+ """
+ order_qt_query1_15_1_before "${query1_15_1}"
+ async_mv_rewrite_success_without_check_chosen(db, mv1_15_1, query1_15_1,
"mv1_15_1")
+ order_qt_query1_15_1_after "${query1_15_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv1_15_1"""
+
+
+ // use ComparisonPredicate except >= and <, should fail
+ def mv2_0 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'day') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'day');
+ """
+ def query2_0 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ l_commitdate <= '2023-09-04' and '2023-01-16' <= l_commitdate
+ group by
+ l_shipmode;
+ """
+ order_qt_query2_0_before "${query2_0}"
+ async_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
+ l_shipmode,
+ date_trunc(l_shipdate, 'week') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_shipdate, 'week');
+ """
+ def query2_1 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ '2023-01-16' = l_shipdate
+ group by
+ l_shipmode;
+ """
+ order_qt_query2_1_before "${query2_1}"
+ async_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"""
+
+ // use un expect constant should fail
+ def mv2_2 = """
+ select
+ l_shipmode,
+ date_trunc(l_shipdate, 'minute') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_shipdate, 'minute');
+ """
+ def query2_2 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ l_shipdate >= '2023-01-17 00:05:01' and l_shipdate <
'2023-10-18'
+ group by
+ l_shipmode;
+ """
+ order_qt_query2_2_before "${query2_2}"
+ // l_shipdate >= '2023-01-17 00:05:01' because l_shipdate data type is
date, so
+ // simply to l_shipdate >= '2023-01-18 00:00:00'
+ async_mv_rewrite_success(db, mv2_2, query2_2, "mv2_2")
+ order_qt_query2_2_after "${query2_2}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_2"""
+
+
+ def mv2_3 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'minute') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'minute');
+ """
+ def query2_3 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ l_commitdate >= '2023-01-17 00:05:01' and l_commitdate <
'2023-10-18'
+ group by
+ l_shipmode;
+ """
+ order_qt_query2_3_before "${query2_3}"
+ async_mv_rewrite_fail(db, mv2_3, query2_3, "mv2_3")
+ order_qt_query2_3_after "${query2_3}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv2_3"""
+
+
+ // 2. expr in date_trunc is cmplex expr
+ def mv3_0 = """
+ select
+ l_shipmode,
+ date_add(l_commitdate, INTERVAL 2 DAY) as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_add(l_commitdate, INTERVAL 2 DAY);
+ """
+ def query3_0 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ '2023-10-01' <= date_add(l_commitdate, INTERVAL 2 DAY) and
'2023-10-30 00:10:00' > date_add(l_commitdate, INTERVAL 2 DAY)
+ group by
+ l_shipmode;
+ """
+ order_qt_query3_0_before "${query3_0}"
+ async_mv_rewrite_fail(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
+ l_shipmode,
+ date_trunc(date_add(l_commitdate, INTERVAL 2 DAY), 'day') as
day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(date_add(l_commitdate, INTERVAL 2 DAY), 'day');
+ """
+ def query3_1 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ '2023-10-01' <= date_add(l_commitdate, INTERVAL 2 DAY) and
'2023-10-30 00:10:00' > date_add(l_commitdate, INTERVAL 2 DAY)
+ group by
+ l_shipmode;
+ """
+ order_qt_query3_1_before "${query3_1}"
+ async_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
+ l_shipmode,
+ date_trunc(l_commitdate, 'day') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'day');
+ """
+ def query3_2 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ date_add(l_commitdate, INTERVAL 2 DAY) >= '2023-10-01' and
date_add(l_commitdate, INTERVAL 2 DAY) < '2023-10-25'
+ group by
+ l_shipmode;
+ """
+ order_qt_query3_2_before "${query3_2}"
+ async_mv_rewrite_success(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
+ l_shipmode,
+ date_trunc(l_commitdate, 'day') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'day');
+ """
+ def query3_3 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ '2023-10-01' <= date_add(l_commitdate, INTERVAL 2 DAY) and
'2023-10-30' > date_add(l_commitdate, INTERVAL 2 DAY)
+ group by
+ l_shipmode;
+ """
+ order_qt_query3_3_before "${query3_3}"
+ async_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"""
+
+
+ // use ComparisonPredicate except >= and <, should fail
+ def mv3_4 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'day') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'day');
+ """
+ def query3_4 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ date_add(l_commitdate, INTERVAL 2 DAY) >= '2023-10-01' and
date_add(l_commitdate, INTERVAL 2 DAY) <= '2023-10-25'
+ group by
+ l_shipmode;
+ """
+ order_qt_query3_4_before "${query3_4}"
+ async_mv_rewrite_fail(db, mv3_4, query3_4, "mv3_4")
+ order_qt_query3_4_after "${query3_4}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_4"""
+
+
+ // use un expect constant should fail
+ def mv3_5 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate, 'day') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate, 'day');
+ """
+ def query3_5 = """
+ select
+ l_shipmode,
+ count(*)
+ from
+ lineitem
+ where
+ '2023-10-01' <= date_add(l_commitdate, INTERVAL 2 DAY) and
'2023-10-30 00:10:00' > date_add(l_commitdate, INTERVAL 2 DAY)
+ group by
+ l_shipmode;
+ """
+ order_qt_query3_5_before "${query3_5}"
+ async_mv_rewrite_fail(db, mv3_5, query3_5, "mv3_5")
+ order_qt_query3_5_after "${query3_5}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv3_5"""
+
+ // some partition is invalid, should compensate partition
+ create_async_partition_mv(db, "mv4_0", """
+ select
+ l_shipmode,
+ date_trunc(l_shipdate, 'day') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_shipdate, 'day');
+ """, "(day_trunc)")
+
+
+ create_async_partition_mv(db, "mv4_1", """
+ select
+ l_shipmode,
+ date_trunc(l_shipdate, 'day') as day_trunc
+ from
+ lineitem;
+ """, "(day_trunc)")
+
+ sql """
+ insert into lineitem
+ values
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-01-01', '2023-01-01
00:00:00.999999', '2023-01-01 00:00:00.999999', '2023-10-17', 'a', 'b',
'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-01-01', '2023-01-01
02:01:45', '2023-01-01 02:01:45', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-01-01', '2023-01-01
03:02:43', '2023-01-01 03:02:43', '2023-10-17', 'a', 'b', 'yyyyyyyyy');
+ """
+
+ def query4_0 = """
+ select
+ l_shipmode,
+ date_trunc(l_shipdate, 'day') as day_trunc,
+ count(*)
+ from
+ lineitem
+ where l_shipdate >= '2023-01-01' and l_shipdate < '2023-05-01'
+ group by
+ l_shipmode,
+ date_trunc(l_shipdate, 'day');
+ """
+ mv_rewrite_success(query4_0, "mv4_0")
+ order_qt_query4_0_after "${query4_0}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv4_0"""
+
+
+ def query4_1 = """
+ select
+ l_shipmode,
+ date_trunc(l_shipdate, 'day') as day_trunc
+ from
+ lineitem
+ where l_shipdate >= '2023-01-01' and l_shipdate < '2023-05-01';
+ """
+ mv_rewrite_success_without_check_chosen(query4_1, "mv4_1")
+ order_qt_query4_1_after "${query4_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv4_1"""
+
+
+
+
+ // mv def
+ def mv5_0 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate_var, 'day') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate_var, 'day');
+ """
+ def query5_0 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate_var, 'day'),
+ count(*)
+ from
+ lineitem
+ where
+ l_commitdate_var >= cast('2023-10-17' as datetime) and l_commitdate_var <
cast('2023-10-18' as datetime)
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate_var, 'day');
+ """
+ order_qt_query5_0_before "${query5_0}"
+ // should success because as datetime would be datetime(6)
+ async_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
+ l_shipmode,
+ date_trunc(l_commitdate_var, 'day') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate_var, 'day');
+ """
+ def query5_1 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate_var, 'day'),
+ count(*)
+ from
+ lineitem
+ where
+ l_commitdate_var >= cast('2023-10-17' as date) and l_commitdate_var <
cast('2023-10-18' as date)
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate_var, 'day');
+ """
+ order_qt_query5_1_before "${query5_1}"
+ // fail because as datetime would be datetime(0), but mv is datetime(6)
+ async_mv_rewrite_fail(db, mv5_1, query5_1, "mv5_1")
+ order_qt_query5_1_after "${query5_1}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv5_1"""
+
+
+ def mv5_3 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate_var, 'minute') as day_trunc,
+ count(*)
+ from
+ lineitem
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate_var, 'minute');
+ """
+ def query5_3 = """
+ select
+ l_shipmode,
+ date_trunc(l_commitdate_var, 'minute'),
+ count(*)
+ from
+ lineitem
+ where
+ l_commitdate_var >= cast('2023-10-01' as datetime) and l_commitdate_var <
cast('2023-10-19' as datetime)
+ group by
+ l_shipmode,
+ date_trunc(l_commitdate_var, 'minute');
+ """
+ order_qt_query5_3_before "${query5_3}"
+ // data is not valid
+ async_mv_rewrite_success(db, mv5_3, query5_3, "mv5_3")
+ order_qt_query5_3_after "${query5_3}"
+ sql """ DROP MATERIALIZED VIEW IF EXISTS mv5_3"""
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]