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 172007f23b7 [opt](nereids) Composite predicate supports range
predicate when rewritting by materialzied view (#31538)
172007f23b7 is described below
commit 172007f23b724717a38bd7ac095374776586e47f
Author: seawinde <[email protected]>
AuthorDate: Fri Mar 1 18:03:10 2024 +0800
[opt](nereids) Composite predicate supports range predicate when rewritting
by materialzied view (#31538)
It supports predicate composite as following:
materialized view define
> select l_shipdate, o_orderdate, l_partkey, l_suppkey
> from lineitem_1
> left join orders_1
> on lineitem_1.l_orderkey = orders_1.o_orderkey
> where l_shipdate > '2023-10-19'
the query as following can be rewritten by the materialized view above
> select l_shipdate, o_orderdate, l_partkey, l_suppkey
> from lineitem_1
> left join orders_1
> on lineitem_1.l_orderkey = orders_1.o_orderkey
> where l_shipdate > '2023-10-25'
---
.../mv/AbstractMaterializedViewRule.java | 3 +-
.../exploration/mv/MaterializedViewUtils.java | 3 +
.../nereids/rules/exploration/mv/Predicates.java | 44 ++++---
.../rules/exploration/mv/PredicatesSplitter.java | 12 ++
.../rules/expression/rules/SimplifyRange.java | 9 +-
.../apache/doris/nereids/util/ExpressionUtils.java | 4 +
.../rules/expression/SimplifyRangeTest.java | 134 +++++++++++++++++++++
.../filter_equal_or_notequal.groovy | 104 ++++++++++------
8 files changed, 255 insertions(+), 58 deletions(-)
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 4840416667c..a11b9101045 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
@@ -473,7 +473,8 @@ public abstract class AbstractMaterializedViewRule
implements ExplorationRuleFac
queryStructInfo,
viewStructInfo,
viewToQuerySlotMapping,
- comparisonResult);
+ comparisonResult,
+ cascadesContext);
// residual compensate
final Set<Expression> residualCompensatePredicates =
Predicates.compensateResidualPredicate(
queryStructInfo,
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewUtils.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewUtils.java
index baf72cc278e..ce12e059b77 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewUtils.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/exploration/mv/MaterializedViewUtils.java
@@ -75,6 +75,9 @@ public class MaterializedViewUtils {
break;
}
}
+ if (columnExpr == null) {
+ return Optional.empty();
+ }
if (!(columnExpr instanceof SlotReference)) {
return Optional.empty();
}
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 472e49d3b43..139230be5d4 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
@@ -17,8 +17,12 @@
package org.apache.doris.nereids.rules.exploration.mv;
+import org.apache.doris.nereids.CascadesContext;
import
org.apache.doris.nereids.rules.exploration.mv.mapping.EquivalenceClassSetMapping;
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.EqualTo;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.SlotReference;
@@ -143,8 +147,8 @@ public class Predicates {
public static Set<Expression> compensateRangePredicate(StructInfo
queryStructInfo,
StructInfo viewStructInfo,
SlotMapping viewToQuerySlotMapping,
- ComparisonResult comparisonResult) {
- // TODO Range predicates compensate, simplify implementation currently.
+ ComparisonResult comparisonResult,
+ CascadesContext cascadesContext) {
SplitPredicate querySplitPredicate =
queryStructInfo.getSplitPredicate();
SplitPredicate viewSplitPredicate = viewStructInfo.getSplitPredicate();
@@ -153,20 +157,32 @@ public class Predicates {
Expression viewRangePredicateQueryBased =
ExpressionUtils.replace(viewRangePredicate,
viewToQuerySlotMapping.toSlotReferenceMap());
- Set<Expression> queryRangeSet =
-
Sets.newHashSet(ExpressionUtils.extractConjunction(queryRangePredicate));
- Set<Expression> viewRangeQueryBasedSet =
-
Sets.newHashSet(ExpressionUtils.extractConjunction(viewRangePredicateQueryBased));
- // remove unnecessary literal BooleanLiteral.TRUE
- queryRangeSet.remove(BooleanLiteral.TRUE);
- viewRangeQueryBasedSet.remove(BooleanLiteral.TRUE);
- // query residual predicate can not contain all view residual
predicate when view have residual predicate,
- // bail out
- if (!queryRangeSet.containsAll(viewRangeQueryBasedSet)) {
+ 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;
+ }
+ // try to normalize the different expressions
+ Set<Expression> normalizedExpressions =
+ normalizeExpression(ExpressionUtils.and(differentExpressions),
cascadesContext);
+ if (!queryRangeSet.containsAll(normalizedExpressions)) {
+ // normalized expressions is not in query, can not compensate
return null;
}
- queryRangeSet.removeAll(viewRangeQueryBasedSet);
- return queryRangeSet;
+ return normalizedExpressions;
+ }
+
+ private static Set<Expression> normalizeExpression(Expression expression,
CascadesContext cascadesContext) {
+ ExpressionNormalization expressionNormalization = new
ExpressionNormalization();
+ ExpressionOptimization expressionOptimization = new
ExpressionOptimization();
+ ExpressionRewriteContext context = new
ExpressionRewriteContext(cascadesContext);
+ expression = expressionNormalization.rewrite(expression, context);
+ expression = expressionOptimization.rewrite(expression, context);
+ return ExpressionUtils.extractConjunctionToSet(expression);
}
/**
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 da8f582f64b..f1c0ae8f96a 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
@@ -22,6 +22,7 @@ import org.apache.doris.nereids.trees.expressions.Cast;
import org.apache.doris.nereids.trees.expressions.ComparisonPredicate;
import org.apache.doris.nereids.trees.expressions.EqualPredicate;
import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.InPredicate;
import org.apache.doris.nereids.trees.expressions.SlotReference;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
import
org.apache.doris.nereids.trees.expressions.visitor.DefaultExpressionVisitor;
@@ -81,6 +82,17 @@ public class PredicatesSplitter {
return null;
}
+ @Override
+ public Void visitInPredicate(InPredicate inPredicate, Void context) {
+ if (containOnlyColumnRef(inPredicate.getCompareExpr(), true)
+ &&
(ExpressionUtils.isAllLiteral(inPredicate.getOptions()))) {
+ rangePredicates.add(inPredicate);
+ } else {
+ residualPredicates.add(inPredicate);
+ }
+ return null;
+ }
+
@Override
public Void visit(Expression expr, Void context) {
residualPredicates.add(expr);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SimplifyRange.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SimplifyRange.java
index c0cb834b2f4..13666405bb9 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SimplifyRange.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SimplifyRange.java
@@ -98,8 +98,8 @@ public class SimplifyRange extends
AbstractExpressionRewriteRule {
private ValueDesc buildRange(ComparisonPredicate predicate) {
Expression rewrite = ExpressionRuleExecutor.normalize(predicate);
Expression right = rewrite.child(1);
- // only handle `NumericType`
- if (right.isLiteral() && right.getDataType().isNumericType()) {
+ // only handle `NumericType` and `DateLikeType`
+ if (right.isLiteral() && (right.getDataType().isNumericType() ||
right.getDataType().isDateLikeType())) {
return ValueDesc.range((ComparisonPredicate) rewrite);
}
return new UnknownValue(predicate);
@@ -132,9 +132,10 @@ public class SimplifyRange extends
AbstractExpressionRewriteRule {
@Override
public ValueDesc visitInPredicate(InPredicate inPredicate, Void
context) {
- // only handle `NumericType`
+ // only handle `NumericType` and `DateLikeType`
if (ExpressionUtils.isAllLiteral(inPredicate.getOptions())
- &&
ExpressionUtils.matchNumericType(inPredicate.getOptions())) {
+ &&
(ExpressionUtils.matchNumericType(inPredicate.getOptions())
+ ||
ExpressionUtils.matchDateLikeType(inPredicate.getOptions()))) {
return ValueDesc.discrete(inPredicate);
}
return new UnknownValue(inPredicate);
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 8aee620b9f9..d59661d21c9 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
@@ -460,6 +460,10 @@ public class ExpressionUtils {
return children.stream().allMatch(c ->
c.getDataType().isNumericType());
}
+ public static boolean matchDateLikeType(List<Expression> children) {
+ return children.stream().allMatch(c ->
c.getDataType().isDateLikeType());
+ }
+
public static boolean hasNullLiteral(List<Expression> children) {
return children.stream().anyMatch(c -> c instanceof NullLiteral);
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/SimplifyRangeTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/SimplifyRangeTest.java
index 1b56e0d06fe..16476e37146 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/SimplifyRangeTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/SimplifyRangeTest.java
@@ -158,6 +158,140 @@ public class SimplifyRangeTest {
}
+ @Test
+ public void testSimplifyDate() {
+ executor = new
ExpressionRuleExecutor(ImmutableList.of(SimplifyRange.INSTANCE));
+ // assertRewrite("TA", "TA");
+ assertRewrite(
+ "(TA >= date '2024-01-01' and TA <= date '2024-01-03') or (TA
> date '2024-01-05' and TA < date '2024-01-07')",
+ "(TA >= date '2024-01-01' and TA <= date '2024-01-03') or (TA
> date '2024-01-05' and TA < date '2024-01-07')");
+ assertRewrite(
+ "(TA > date '2024-01-03' and TA < date '2024-01-01') or (TA >
date '2024-01-07'and TA < date '2024-01-05')",
+ "FALSE");
+ assertRewrite("TA > date '2024-01-03' and TA < date '2024-01-01'",
"FALSE");
+ assertRewrite("TA >= date '2024-01-01' and TA < date '2024-01-01'",
+ "TA >= date '2024-01-01' and TA < date '2024-01-01'");
+ assertRewrite("TA = date '2024-01-01' and TA > date '2024-01-10'",
"FALSE");
+ assertRewrite("TA > date '2024-01-05' or TA < date '2024-01-01'",
+ "TA > date '2024-01-05' or TA < date '2024-01-01'");
+ assertRewrite("TA > date '2024-01-05' or TA > date '2024-01-01' or TA
> date '2024-01-10'",
+ "TA > date '2024-01-01'");
+ assertRewrite("TA > date '2024-01-05' or TA > date '2024-01-01' or TA
< date '2024-01-10'", "TA IS NOT NULL");
+ assertRewriteNotNull("TA > date '2024-01-05' or TA > date '2024-01-01'
or TA < date '2024-01-10'", "TRUE");
+ assertRewrite("TA > date '2024-01-05' and TA > date '2024-01-01' and
TA > date '2024-01-10'",
+ "TA > date '2024-01-10'");
+ assertRewrite("TA > date '2024-01-05' and TA > date '2024-01-01' and
TA < date '2024-01-10'",
+ "TA > date '2024-01-05' and TA < date '2024-01-10'");
+ assertRewrite("TA > date '2024-01-05' or TA < date '2024-01-05'",
+ "TA > date '2024-01-05' or TA < date '2024-01-05'");
+ assertRewrite("TA > date '2024-01-01' or TA < date '2024-01-10'", "TA
IS NOT NULL");
+ assertRewriteNotNull("TA > date '2024-01-01' or TA < date
'2024-01-10'", "TRUE");
+ assertRewrite("TA > date '2024-01-05' and TA < date '2024-01-10'",
+ "TA > date '2024-01-05' and TA < date '2024-01-10'");
+ assertRewrite("TA > date '2024-01-05' and TA > date '2024-01-10'", "TA
> date '2024-01-10'");
+ assertRewrite("(TA > date '2024-01-01' and TA > date '2024-01-10') or
TA > date '2024-01-20'",
+ "TA > date '2024-01-10'");
+ assertRewrite("(TA > date '2024-01-01' or TA > date '2024-01-10') and
TA > date '2024-01-20'",
+ "TA > date '2024-01-20'");
+ assertRewrite("TA > date '2024-01-05' or TA > date '2024-01-05'", "TA
> date '2024-01-05'");
+ assertRewrite(
+ "(TA > date '2024-01-10' or TA > date '2024-01-20') and (TB >
date '2024-01-10' and TB < date '2024-01-20')",
+ "TA > date '2024-01-10' and (TB > date '2024-01-10' and TB <
date '2024-01-20') ");
+ assertRewrite("TA in (date '2024-01-01',date '2024-01-02',date
'2024-01-03') and TA > date '2024-01-10'",
+ "FALSE");
+ assertRewrite("TA in (date '2024-01-01',date '2024-01-02',date
'2024-01-03') and TA >= date '2024-01-01'",
+ "TA in (date '2024-01-01',date '2024-01-02',date
'2024-01-03')");
+ assertRewrite("TA in (date '2024-01-01',date '2024-01-02',date
'2024-01-03') and TA > date '2024-01-01'",
+ "((TA = date '2024-01-02') OR (TA = date '2024-01-03'))");
+ assertRewrite("TA in (date '2024-01-01',date '2024-01-02',date
'2024-01-03') or TA >= date '2024-01-01'",
+ "TA >= date '2024-01-01'");
+ assertRewrite("TA in (date '2024-01-01')", "TA in (date
'2024-01-01')");
+ assertRewrite("TA in (date '2024-01-01',date '2024-01-02',date
'2024-01-03') and TA < date '2024-01-10'",
+ "TA in (date '2024-01-01',date '2024-01-02',date
'2024-01-03')");
+ assertRewrite("TA in (date '2024-01-01',date '2024-01-02',date
'2024-01-03') and TA < date '2024-01-01'",
+ "FALSE");
+ assertRewrite("TA in (date '2024-01-01',date '2024-01-02',date
'2024-01-03') or TA < date '2024-01-01'",
+ "TA in (date '2024-01-01',date '2024-01-02',date '2024-01-03')
or TA < date '2024-01-01'");
+ assertRewrite("TA in (date '2024-01-01',date '2024-01-02') or TA in
(date '2024-01-02', date '2024-01-03')",
+ "TA in (date '2024-01-01',date '2024-01-02',date
'2024-01-03')");
+ assertRewrite("TA in (date '2024-01-01',date '2024-01-02') and TA in
(date '2024-01-03', date '2024-01-04')",
+ "FALSE");
+ assertRewrite("TA = date '2024-01-03' and TA = date '2024-01-01'",
"FALSE");
+ assertRewrite("TA in (date '2024-01-01') and TA in (date
'2024-01-03')", "FALSE");
+ assertRewrite("TA in (date '2024-01-03') and TA in (date
'2024-01-03')", "TA = date '2024-01-03'");
+ assertRewrite("(TA > date '2024-01-03' and TA < date '2024-01-01') and
TB < date '2024-01-05'", "FALSE");
+ assertRewrite("(TA > date '2024-01-03' and TA < date '2024-01-01') or
TB < date '2024-01-05'",
+ "TB < date '2024-01-05'");
+ }
+
+ @Test
+ public void testSimplifyDateTime() {
+ executor = new
ExpressionRuleExecutor(ImmutableList.of(SimplifyRange.INSTANCE));
+ // assertRewrite("TA", "TA");
+ assertRewrite(
+ "(TA >= timestamp '2024-01-01 00:00:00' and TA <= timestamp
'2024-01-03 00:00:00') or (TA > timestamp '2024-01-05 00:00:00' and TA <
timestamp '2024-01-07 00:00:00')",
+ "(TA >= timestamp '2024-01-01 00:00:00' and TA <= timestamp
'2024-01-03 00:00:00') or (TA > timestamp '2024-01-05 00:00:00' and TA <
timestamp '2024-01-07 00:00:00')");
+ assertRewrite(
+ "(TA > timestamp '2024-01-03 00:00:10' and TA < timestamp
'2024-01-01 00:00:10') or (TA > timestamp '2024-01-07 00:00:10'and TA <
timestamp '2024-01-05 00:00:10')",
+ "FALSE");
+ assertRewrite("TA > timestamp '2024-01-03 00:00:10' and TA < timestamp
'2024-01-01 01:00:00'", "FALSE");
+ assertRewrite("TA >= timestamp '2024-01-01 00:00:10' and TA <
timestamp '2024-01-01 00:00:10'",
+ "TA >= timestamp '2024-01-01 00:00:10' and TA < timestamp
'2024-01-01 00:00:10'");
+ assertRewrite("TA = timestamp '2024-01-01 10:00:10' and TA > timestamp
'2024-01-10 00:00:10'", "FALSE");
+ assertRewrite("TA > timestamp '2024-01-05 00:00:10' or TA < timestamp
'2024-01-01 00:00:10'",
+ "TA > timestamp '2024-01-05 00:00:10' or TA < timestamp
'2024-01-01 00:00:10'");
+ assertRewrite("TA > timestamp '2024-01-05 00:00:10' or TA > timestamp
'2024-01-01 00:00:10' or TA > timestamp '2024-01-10 00:00:10'",
+ "TA > timestamp '2024-01-01 00:00:10'");
+ assertRewrite("TA > timestamp '2024-01-05 00:00:10' or TA > timestamp
'2024-01-01 00:00:10' or TA < timestamp '2024-01-10 00:00:10'", "TA IS NOT
NULL");
+ assertRewriteNotNull("TA > timestamp '2024-01-05 00:00:10' or TA >
timestamp '2024-01-01 00:00:10' or TA < timestamp '2024-01-10 00:00:10'",
"TRUE");
+ assertRewrite("TA > timestamp '2024-01-05 00:00:10' and TA > timestamp
'2024-01-01 00:00:10' and TA > timestamp '2024-01-10 00:00:15'",
+ "TA > timestamp '2024-01-10 00:00:15'");
+ assertRewrite("TA > timestamp '2024-01-05 00:00:10' and TA > timestamp
'2024-01-01 00:00:10' and TA < timestamp '2024-01-10 00:00:10'",
+ "TA > timestamp '2024-01-05 00:00:10' and TA < timestamp
'2024-01-10 00:00:10'");
+ assertRewrite("TA > timestamp '2024-01-05 00:00:10' or TA < timestamp
'2024-01-05 00:00:10'",
+ "TA > timestamp '2024-01-05 00:00:10' or TA < timestamp
'2024-01-05 00:00:10'");
+ assertRewrite("TA > timestamp '2024-01-01 00:02:10' or TA < timestamp
'2024-01-10 00:02:10'", "TA IS NOT NULL");
+ assertRewriteNotNull("TA > timestamp '2024-01-01 00:00:00' or TA <
timestamp '2024-01-10 00:00:00'", "TRUE");
+ assertRewrite("TA > timestamp '2024-01-05 01:00:00' and TA < timestamp
'2024-01-10 01:00:00'",
+ "TA > timestamp '2024-01-05 01:00:00' and TA < timestamp
'2024-01-10 01:00:00'");
+ assertRewrite("TA > timestamp '2024-01-05 01:00:00' and TA > timestamp
'2024-01-10 01:00:00'", "TA > timestamp '2024-01-10 01:00:00'");
+ assertRewrite("(TA > timestamp '2024-01-01 01:00:00' and TA >
timestamp '2024-01-10 01:00:00') or TA > timestamp '2024-01-20 01:00:00'",
+ "TA > timestamp '2024-01-10 01:00:00'");
+ assertRewrite("(TA > timestamp '2024-01-01 01:00:00' or TA > timestamp
'2024-01-10 01:00:00') and TA > timestamp '2024-01-20 01:00:00'",
+ "TA > timestamp '2024-01-20 01:00:00'");
+ assertRewrite("TA > timestamp '2024-01-05 01:00:00' or TA > timestamp
'2024-01-05 01:00:00'", "TA > timestamp '2024-01-05 01:00:00'");
+ assertRewrite(
+ "(TA > timestamp '2024-01-10 01:00:00' or TA > timestamp
'2024-01-20 01:00:00') and (TB > timestamp '2024-01-10 01:00:00' and TB <
timestamp '2024-01-20 01:00:00')",
+ "TA > timestamp '2024-01-10 01:00:00' and (TB > timestamp
'2024-01-10 01:00:00' and TB < timestamp '2024-01-20 01:00:00') ");
+ assertRewrite("TA in (timestamp '2024-01-01 01:00:00',timestamp
'2024-01-02 02:00:00',timestamp '2024-01-03 03:00:00') and TA > timestamp
'2024-01-10 01:00:00'",
+ "FALSE");
+ assertRewrite("TA in (timestamp '2024-01-01 01:00:00',timestamp
'2024-01-02 01:50:00',timestamp '2024-01-03 02:00:00') and TA >= timestamp
'2024-01-01'",
+ "TA in (timestamp '2024-01-01 01:00:00',timestamp '2024-01-02
01:50:00',timestamp '2024-01-03 02:00:00')");
+ assertRewrite("TA in (timestamp '2024-01-01 02:00:00',timestamp
'2024-01-02 02:00:00',timestamp '2024-01-03 02:00:00') and TA > timestamp
'2024-01-01 02:10:00'",
+ "((TA = timestamp '2024-01-02 02:00:00') OR (TA = timestamp
'2024-01-03 02:00:00'))");
+ assertRewrite("TA in (timestamp '2024-01-01 02:00:00',timestamp
'2024-01-02 02:00:00',timestamp '2024-01-03 02:00:00') or TA >= timestamp
'2024-01-01 01:00:00'",
+ "TA >= timestamp '2024-01-01 01:00:00'");
+ assertRewrite("TA in (timestamp '2024-01-01 02:00:00')", "TA in
(timestamp '2024-01-01 02:00:00')");
+ assertRewrite("TA in (timestamp '2024-01-01 02:00:00',timestamp
'2024-01-02 02:00:00',timestamp '2024-01-03 02:00:00') and TA < timestamp
'2024-01-10 02:00:00'",
+ "TA in (timestamp '2024-01-01 02:00:00',timestamp '2024-01-02
02:00:00',timestamp '2024-01-03 02:00:00')");
+ assertRewrite("TA in (timestamp '2024-01-01 02:00:00',timestamp
'2024-01-02 02:00:00',timestamp '2024-01-03 02:00:00') and TA < timestamp
'2024-01-01 02:00:00'",
+ "FALSE");
+ assertRewrite("TA in (timestamp '2024-01-01 02:00:00',timestamp
'2024-01-02 02:00:00',timestamp '2024-01-03 02:00:00') and TA < timestamp
'2024-01-01 02:00:01'",
+ "TA = timestamp '2024-01-01 02:00:00'");
+ assertRewrite("TA in (timestamp '2024-01-01 02:00:00',timestamp
'2024-01-02 02:00:00',timestamp '2024-01-03 02:00:00') or TA < timestamp
'2024-01-01 01:00:00'",
+ "TA in (timestamp '2024-01-01 02:00:00',timestamp '2024-01-02
02:00:00',timestamp '2024-01-03 02:00:00') or TA < timestamp '2024-01-01
01:00:00'");
+ assertRewrite("TA in (timestamp '2024-01-01 00:00:00',timestamp
'2024-01-02 00:00:00') or TA in (timestamp '2024-01-02 00:00:00', timestamp
'2024-01-03 00:00:00')",
+ "TA in (timestamp '2024-01-01 00:00:00',timestamp '2024-01-02
00:00:00',timestamp '2024-01-03 00:00:00')");
+ assertRewrite("TA in (timestamp '2024-01-01 00:50:00',timestamp
'2024-01-02 00:50:00') and TA in (timestamp '2024-01-03 00:50:00', timestamp
'2024-01-04 00:50:00')",
+ "FALSE");
+ assertRewrite("TA = timestamp '2024-01-03 00:50:00' and TA = timestamp
'2024-01-01 00:50:00'", "FALSE");
+ assertRewrite("TA in (timestamp '2024-01-01 00:50:00') and TA in
(timestamp '2024-01-03 00:50:00')", "FALSE");
+ assertRewrite("TA in (timestamp '2024-01-03 00:50:00') and TA in
(timestamp '2024-01-03 00:50:00')", "TA = timestamp '2024-01-03 00:50:00'");
+ assertRewrite("(TA > timestamp '2024-01-03 00:50:00' and TA <
timestamp '2024-01-01 00:50:00') and TB < timestamp '2024-01-05 00:50:00'",
"FALSE");
+ assertRewrite("(TA > timestamp '2024-01-03 00:50:00' and TA <
timestamp '2024-01-01 00:50:00') or TB < timestamp '2024-01-05 00:50:00'",
+ "TB < timestamp '2024-01-05 00:50:00'");
+ }
+
private void assertRewrite(String expression, String expected) {
Map<String, Slot> mem = Maps.newHashMap();
Expression needRewriteExpression =
replaceUnboundSlot(PARSER.parseExpression(expression), mem);
diff --git
a/regression-test/suites/nereids_rules_p0/mv/dimension_equal/filter_equal_or_notequal.groovy
b/regression-test/suites/nereids_rules_p0/mv/dimension_equal/filter_equal_or_notequal.groovy
index 1422b5db585..1800a91b84b 100644
---
a/regression-test/suites/nereids_rules_p0/mv/dimension_equal/filter_equal_or_notequal.groovy
+++
b/regression-test/suites/nereids_rules_p0/mv/dimension_equal/filter_equal_or_notequal.groovy
@@ -282,20 +282,19 @@ suite("filter_equal_or_notequal_case") {
notContains "${mv_name}(${mv_name})"
}
- // Todo: It is not currently supported and is expected to be
// mv is range and sql equal and filter in mv range
-// query_sql = """
-// select l_shipdate, o_orderdate, l_partkey, l_suppkey
-// from lineitem_1
-// left join orders_1
-// on lineitem_1.l_orderkey = orders_1.o_orderkey
-// where l_shipdate = '2023-10-19'
-// """
-// explain {
-// sql("${query_sql}")
-// contains "${mv_name}(${mv_name})"
-// }
-// compare_res(query_sql + " order by 1,2,3,4")
+ query_sql = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ where l_shipdate = '2023-10-19'
+ """
+ explain {
+ sql("${query_sql}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(query_sql + " order by 1,2,3,4")
// mv is range and sql is range and sql range is bigger than mv
query_sql = """
@@ -350,20 +349,19 @@ suite("filter_equal_or_notequal_case") {
notContains "${mv_name}(${mv_name})"
}
- // Todo: It is not currently supported and is expected to be
// mv is range and sql is range and sql range is bigger than mv
-// query_sql = """
-// select l_shipdate, o_orderdate, l_partkey, l_suppkey
-// from lineitem_1
-// left join orders_1
-// on lineitem_1.l_orderkey = orders_1.o_orderkey
-// where l_shipdate > '2023-10-19'
-// """
-// explain {
-// sql("${query_sql}")
-// contains "${mv_name}(${mv_name})"
-// }
-// compare_res(query_sql + " order by 1,2,3,4")
+ query_sql = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ where l_shipdate > '2023-10-18'
+ """
+ explain {
+ sql("${query_sql}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(query_sql + " order by 1,2,3,4")
// mv is range and sql is range and sql range is not in mv range
query_sql = """
@@ -418,20 +416,34 @@ suite("filter_equal_or_notequal_case") {
notContains "${mv_name}(${mv_name})"
}
- // Todo: It is not currently supported and is expected to be
// sql range is in mv range
-// query_sql = """
-// select l_shipdate, o_orderdate, l_partkey, l_suppkey
-// from lineitem_1
-// left join orders_1
-// on lineitem_1.l_orderkey = orders_1.o_orderkey
-// where l_shipdate in ('2023-10-18')
-// """
-// explain {
-// sql("${query_sql}")
-// contains "${mv_name}(${mv_name})"
-// }
-// compare_res(query_sql + " order by 1,2,3,4")
+ // single value
+ query_sql = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ where l_shipdate in ('2023-10-18')
+ """
+ explain {
+ sql("${query_sql}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(query_sql + " order by 1,2,3,4")
+
+ // multi value
+ query_sql = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ where l_shipdate in ('2023-10-18', '2023-11-18')
+ """
+ explain {
+ sql("${query_sql}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(query_sql + " order by 1,2,3,4")
// sql range like mv range
query_sql = """
@@ -498,6 +510,20 @@ suite("filter_equal_or_notequal_case") {
}
compare_res(query_sql + " order by 1,2,3,4")
+
+ query_sql = """
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey
+ from lineitem_1
+ left join orders_1
+ on lineitem_1.l_orderkey = orders_1.o_orderkey
+ where l_shipdate > '2023-10-17' and l_shipdate < '2023-10-19'
+ """
+ explain {
+ sql("${query_sql}")
+ contains "${mv_name}(${mv_name})"
+ }
+ compare_res(query_sql + " order by 1,2,3,4")
+
//
mtmv_sql = """
select l_shipdate, o_orderdate, l_partkey, l_suppkey, o_orderkey
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
