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]


Reply via email to