This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.1 by this push:
new f4b2d35a4ab branch-4.1: [fix](nereids) Preserve date arithmetic
evaluation boundaries #69113 (#69577)
f4b2d35a4ab is described below
commit f4b2d35a4ab9baa7bf11c2f4f94c75c915cbd72e
Author: Oliveira <[email protected]>
AuthorDate: Mon Oct 26 10:03:37 2026 +0800
branch-4.1: [fix](nereids) Preserve date arithmetic evaluation boundaries
#69113 (#69577)
### What problem does this PR solve?
Issue Number: N/A (no associated GitHub issue)
Related PR: #69113
Problem Summary:
Backport #69113 to `branch-4.1`. An outer predicate on a projected date
expression can be expanded and evaluated before an inner condition
rejects the row. A filtered row containing `9999-12-31 23:59:59` can
therefore cause `WEEKS_ADD(dt, 5)` to overflow instead of returning the
expected empty result.
Preserve potentially overflowing date arithmetic aliases above Filter,
Join, Limit, EXCEPT, INTERSECT, Generate and CTE consumer boundaries in
logical and physical project predicate pushdown. The branch adaptation
uses its existing logical/physical EXCEPT and INTERSECT classes and
retains its Project-level NoneMovableFunction and volatile-expression
guards. Protect both scalar function calls and `TimestampArithmetic`
(`dt +/- INTERVAL ...`). Preserve the compensating `Filter -> Limit ->
Project` rewrite to `Project -> Filter -> Limit`, so OFFSET discards
rows before date evaluation. Safe integer-zero offsets and ordinary
UNION retain the behavior of the master fix.
### Release note
Fix date arithmetic overflow errors caused by predicate pushdown
evaluating expressions on rows excluded by an inner query.
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [x] Regression test
- [x] Unit Test
- [x] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
<details>
<summary>Local validation on branch-4.1</summary>
- FE/BE build and strict Checkstyle passed with JDK 17 and clang21/ASAN.
The service build used the repository's `DISABLE_BUILD_UI=ON` option.
- **17 FE unit tests passed**, with zero failures, errors or skips:
`DateArithmeticFilterPushDownTest` (11),
`PushDowFilterThroughProjectTest` (4), and
`org.apache.doris.nereids.postprocess.PushDownFilterThroughProjectTest`
(2).
- **Three native P0 suites passed**, with zero failed/fatal/skipped
suites:
`nereids_rules_p0/filter_push_down/test_date_arithmetic_rejecting_boundary`,
`nereids_rules_p0/date_function_rewrite/date_function_rewrite`, and
`nereids_rules_p0/cte/test_cte_filter_pushdown`.
- Tests ran on this branch's verified binaries in a fresh isolated 1 FE
+ 3 BE instance. Source, configuration, framework and process identities
remained stable. The epoch-based date-rewrite suite was run with the
private instance's default time zone set to `Asia/Shanghai`.
- The unit tests cover logical/physical interval protection, integer and
bigint zero offsets, and projection restoration above OFFSET with and
without a lower Filter. The native regression adds OFFSET 1/100
empty-result checks, interval addition/subtraction and zero-offset
controls, and participating-row overflow controls. Expected output files
were not edited.
- **10 direct SQL checks passed** against the final branch binaries:
OFFSET 1/100, interval addition, subtraction of a negative interval,
subtraction, both zero-interval spellings, a zero scalar offset, and
scalar/interval participating-row overflow.
- Source whitespace checks passed; regression outputs retain the
framework's standard final separator.
Before this review update, manual validation passed **18 checks covering
ORC and Parquet**, in each of UTC and Asia/Shanghai. That external-file
validation was not rerun for this update; the final update was verified
with the unit/native/direct-SQL checks above:
1. Generate 200 rows: one row has `dt='9999-12-31 23:59:59'` and
`filter_dt='2025-01-01'`; the other 199 have `dt='2020-01-01'` and
`filter_dt='2020-01-01'`. Export the same input to ORC and Parquet on a
fresh private HDFS fixture.
2. For WEEKS_ADD, HOURS_ADD, SECOND_CEIL, YEAR_CEIL and DAYS_ADD, run
the filtered CTE/outer-alias predicate shape from the original issue.
All return zero rows without overflow. A surviving-row control returns a
count of 199; a shared CTE control returns 398 rows.
3. Verify that arithmetic on a participating maximum-date row still
raises the expected out-of-range error, and that a NULL date returns
NULL. Input row hashes and HDFS/runtime identities remained unchanged
during each run.
These are targeted local results, not a full TeamCity or External-group
verdict. The five historical RQG executions have not been replayed
because their actual external-table DDL and immutable ORC inputs were
unavailable; the manual fixtures above were reconstructed.
</details>
- Behavior changed:
- [ ] No.
- [x] Yes. Preserve date arithmetic evaluation boundaries while
retaining safe zero-offset predicate pushdown and partition pruning.
Nonzero date predicates can remain above rejecting boundaries.
- Does this need documentation?
- [x] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR
should merge into -->
---
.../post/PushDownFilterThroughProject.java | 3 +-
.../rewrite/PushDownFilterThroughProject.java | 14 +-
.../apache/doris/nereids/util/ExpressionUtils.java | 51 ++++
.../rewrite/DateArithmeticFilterPushDownTest.java | 339 +++++++++++++++++++++
.../tvf/test_date_expression_filter.out | 47 +++
.../test_date_arithmetic_rejecting_boundary.out | 13 +
.../tvf/test_date_expression_filter.groovy | 107 +++++++
.../test_date_arithmetic_rejecting_boundary.groovy | 102 +++++++
8 files changed, 670 insertions(+), 6 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/post/PushDownFilterThroughProject.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/post/PushDownFilterThroughProject.java
index 9b4e262d6a1..7c6425be1e8 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/post/PushDownFilterThroughProject.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/post/PushDownFilterThroughProject.java
@@ -45,7 +45,8 @@ public class PushDownFilterThroughProject extends
PlanPostProcessor {
PhysicalProject<? extends Plan> project = (PhysicalProject<? extends
Plan>) child;
Map<Slot, Expression> childAlias = project.getAliasToProducer();
if
(filter.getInputSlots().stream().map(childAlias::get).filter(Objects::nonNull)
- .anyMatch(Expression::containsVolatileExpression)) {
+ .anyMatch(expression -> expression.containsVolatileExpression()
+ ||
ExpressionUtils.requiresDateArithmeticProtection(expression, project.child())))
{
return filter;
}
PhysicalFilter<? extends Plan> newFilter =
filter.withConjunctsAndChild(
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownFilterThroughProject.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownFilterThroughProject.java
index 1a46b51a246..5ad6f1900e6 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownFilterThroughProject.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PushDownFilterThroughProject.java
@@ -82,7 +82,7 @@ public class PushDownFilterThroughProject implements
RewriteRuleFactory {
// splitConjuncts.first -> conjuncts having outer query slots which
should NOT be pushed down
// splitConjuncts.second -> conjuncts without any outer query slots
which should be pushed down
Pair<Set<Expression>, Set<Expression>> splitConjuncts =
- splitConjunctsByChildOutput(filter.getConjuncts(),
childOutputs, childAlias);
+ splitConjunctsByChildOutput(filter.getConjuncts(),
childOutputs, childAlias, project.child(), true);
Set<Expression> remainPredicates = splitConjuncts.first;
Set<Expression> pushDownPredicates = splitConjuncts.second;
if (pushDownPredicates.isEmpty()) {
@@ -99,9 +99,10 @@ public class PushDownFilterThroughProject implements
RewriteRuleFactory {
LogicalProject<Plan> project = limit.child();
Set<Slot> childOutputs = project.getOutputSet();
Map<Slot, Expression> childAlias = project.getAliasToProducer();
- // split the conjuncts by child's output
+ // Restore evaluation above the enclosing Limit. The expanded filter
stays above
+ // that Limit and every lower rejecting boundary, so date arithmetic
is safe here.
Pair<Set<Expression>, Set<Expression>> splitConjuncts =
- splitConjunctsByChildOutput(filter.getConjuncts(),
childOutputs, childAlias);
+ splitConjunctsByChildOutput(filter.getConjuncts(),
childOutputs, childAlias, limit, false);
Set<Expression> remainPredicates = splitConjuncts.first;
Set<Expression> pushDownPredicates = splitConjuncts.second;
if (pushDownPredicates.isEmpty()) {
@@ -113,7 +114,8 @@ public class PushDownFilterThroughProject implements
RewriteRuleFactory {
}
private static Pair<Set<Expression>, Set<Expression>>
splitConjunctsByChildOutput(
- Set<Expression> conjuncts, Set<Slot> childOutputs, Map<Slot,
Expression> childAlias) {
+ Set<Expression> conjuncts, Set<Slot> childOutputs, Map<Slot,
Expression> childAlias, Plan input,
+ boolean protectDateArithmetic) {
Set<Expression> pushDownPredicates = Sets.newLinkedHashSet();
Set<Expression> remainPredicates = Sets.newLinkedHashSet();
for (Expression conjunct : conjuncts) {
@@ -125,7 +127,9 @@ public class PushDownFilterThroughProject implements
RewriteRuleFactory {
// `project(b + random(1, 10) as a) -> filter(b + random(1, 10) >
1)`, it contains two distinct RANDOM.
if (childOutputs.containsAll(conjunctSlots)
&&
conjunctSlots.stream().map(childAlias::get).filter(Objects::nonNull)
-
.noneMatch(Expression::containsVolatileExpression)) {
+ .noneMatch(expression ->
expression.containsVolatileExpression()
+ || (protectDateArithmetic
+ &&
ExpressionUtils.requiresDateArithmeticProtection(expression, input)))) {
pushDownPredicates.add(conjunct);
} else {
remainPredicates.add(conjunct);
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 836db942c18..72a6a65b3b6 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
@@ -49,10 +49,13 @@ import org.apache.doris.nereids.trees.expressions.Not;
import org.apache.doris.nereids.trees.expressions.Or;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.TimestampArithmetic;
import org.apache.doris.nereids.trees.expressions.VolatileExpression;
import org.apache.doris.nereids.trees.expressions.WhenClause;
import org.apache.doris.nereids.trees.expressions.WindowExpression;
import org.apache.doris.nereids.trees.expressions.functions.BoundFunction;
+import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubMonotonic;
+import
org.apache.doris.nereids.trees.expressions.functions.DateCeilFloorMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.NoneMovableFunction;
import org.apache.doris.nereids.trees.expressions.functions.agg.Avg;
import org.apache.doris.nereids.trees.expressions.functions.agg.Count;
@@ -76,6 +79,8 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.Nullable;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Nvl;
import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
import org.apache.doris.nereids.trees.expressions.literal.ComparableLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLikeLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
import org.apache.doris.nereids.trees.expressions.literal.NullLiteral;
import org.apache.doris.nereids.trees.expressions.literal.StringLiteral;
@@ -83,8 +88,18 @@ import
org.apache.doris.nereids.trees.expressions.visitor.DefaultExpressionRewri
import
org.apache.doris.nereids.trees.expressions.visitor.DefaultExpressionVisitor;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitors;
import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.Filter;
+import org.apache.doris.nereids.trees.plans.algebra.Generate;
+import org.apache.doris.nereids.trees.plans.algebra.Join;
+import org.apache.doris.nereids.trees.plans.algebra.Limit;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCTEConsumer;
import org.apache.doris.nereids.trees.plans.logical.LogicalEmptyRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalExcept;
+import org.apache.doris.nereids.trees.plans.logical.LogicalIntersect;
import org.apache.doris.nereids.trees.plans.logical.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalCTEConsumer;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalExcept;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalIntersect;
import org.apache.doris.nereids.trees.plans.visitor.ExpressionLineageReplacer;
import org.apache.doris.nereids.types.DataType;
import org.apache.doris.nereids.types.VariantType;
@@ -133,6 +148,42 @@ public class ExpressionUtils {
// folding the conjunct). We restrict MAX_MARK_SLOT_COUNT to 4 to bound
this cost.
private static final int MAX_MARK_SLOT_COUNT = 4;
+ /**
+ * Date arithmetic can throw for values outside its valid range. Keep it
above a lower
+ * condition, join, limit, rejecting set operation, generate, or CTE
consumer so rejected
+ * rows are not evaluated early.
+ * A date add/subtract with literal zero is an identity and remains safe
to push through.
+ */
+ public static boolean requiresDateArithmeticProtection(Expression
expression, Plan input) {
+ return expression.anyMatch(expr -> isUnsafeDateArithmetic((Expression)
expr))
+ && input.anyMatch(plan -> plan instanceof Filter || plan
instanceof Join || plan instanceof Limit
+ || plan instanceof LogicalExcept || plan instanceof
PhysicalExcept
+ || plan instanceof LogicalIntersect || plan instanceof
PhysicalIntersect
+ || plan instanceof Generate || plan instanceof
LogicalCTEConsumer
+ || plan instanceof PhysicalCTEConsumer);
+ }
+
+ private static boolean isUnsafeDateArithmetic(Expression expression) {
+ if (expression instanceof DateCeilFloorMonotonic) {
+ return true;
+ }
+ int dateChildIndex;
+ if (expression instanceof TimestampArithmetic) {
+ dateChildIndex = 0;
+ } else if (expression instanceof DateAddSubMonotonic) {
+ dateChildIndex = ((DateAddSubMonotonic)
expression).getMonotonicFunctionChildIndex();
+ } else {
+ return false;
+ }
+ for (int i = 0; i < expression.children().size(); i++) {
+ if (i != dateChildIndex && !(expression.child(i) instanceof
IntegerLikeLiteral
+ && ((IntegerLikeLiteral)
expression.child(i)).compareTo(new IntegerLiteral(0)) == 0)) {
+ return true;
+ }
+ }
+ return false;
+ }
+
public static List<Expression> extractConjunction(Expression expr) {
return extract(And.class, expr);
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/DateArithmeticFilterPushDownTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/DateArithmeticFilterPushDownTest.java
new file mode 100644
index 00000000000..d376b6b42fd
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/DateArithmeticFilterPushDownTest.java
@@ -0,0 +1,339 @@
+// 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.
+
+package org.apache.doris.nereids.rules.rewrite;
+
+import org.apache.doris.analysis.ArithmeticExpr.Operator;
+import org.apache.doris.nereids.rules.exploration.join.JoinReorderContext;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.Cast;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.NamedExpression;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.StatementScopeIdGenerator;
+import org.apache.doris.nereids.trees.expressions.TimestampArithmetic;
+import org.apache.doris.nereids.trees.expressions.functions.generator.Explode;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.DaysAdd;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.HoursAdd;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondCeil;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.WeeksAdd;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.YearCeil;
+import org.apache.doris.nereids.trees.expressions.literal.ArrayLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.BigIntLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.DateTimeV2Literal;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.Interval.TimeUnit;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.LimitPhase;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.logical.LogicalCTEConsumer;
+import org.apache.doris.nereids.trees.plans.logical.LogicalExcept;
+import org.apache.doris.nereids.trees.plans.logical.LogicalFilter;
+import org.apache.doris.nereids.trees.plans.logical.LogicalGenerate;
+import org.apache.doris.nereids.trees.plans.logical.LogicalIntersect;
+import org.apache.doris.nereids.trees.plans.logical.LogicalJoin;
+import org.apache.doris.nereids.trees.plans.logical.LogicalLimit;
+import org.apache.doris.nereids.trees.plans.logical.LogicalOneRowRelation;
+import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalCTEConsumer;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalExcept;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalFilter;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalGenerate;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalIntersect;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalOneRowRelation;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalProject;
+import org.apache.doris.nereids.types.DateTimeV2Type;
+import org.apache.doris.nereids.types.IntegerType;
+import org.apache.doris.nereids.util.MemoTestUtils;
+import org.apache.doris.nereids.util.PlanChecker;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+import java.util.List;
+
+class DateArithmeticFilterPushDownTest {
+ private final Alias datetime = new Alias(new DateTimeV2Literal("9999-12-31
23:59:59.000001"), "dt");
+ private final Alias id = new Alias(new IntegerLiteral(1), "id");
+ private final LogicalOneRowRelation relation = new LogicalOneRowRelation(
+ StatementScopeIdGenerator.newRelationId(),
ImmutableList.of(datetime, id));
+
+ private List<Expression> addSubExpressions(Expression offset) {
+ return ImmutableList.of(
+ new WeeksAdd(datetime.toSlot(), offset),
+ new HoursAdd(datetime.toSlot(), offset),
+ new DaysAdd(datetime.toSlot(), offset),
+ new TimestampArithmetic(Operator.ADD, datetime.toSlot(),
offset, TimeUnit.WEEK),
+ new TimestampArithmetic(Operator.SUBTRACT, datetime.toSlot(),
offset, TimeUnit.WEEK));
+ }
+
+ private List<Expression> dateExpressions() {
+ return
ImmutableList.<Expression>builder().addAll(addSubExpressions(new
IntegerLiteral(5)))
+ .add(new SecondCeil(datetime.toSlot(), new IntegerLiteral(5)),
+ new YearCeil(datetime.toSlot(), new
IntegerLiteral(5))).build();
+ }
+
+ @Test
+ void protectRejectingSetOperations() {
+ Alias rightDatetime = new Alias(new DateTimeV2Literal("2020-01-01
00:00:00"), "right_dt");
+ Alias rightId = new Alias(new IntegerLiteral(1), "right_id");
+ LogicalOneRowRelation right = new
LogicalOneRowRelation(StatementScopeIdGenerator.newRelationId(),
+ ImmutableList.of(rightDatetime, rightId));
+ List<Plan> children = ImmutableList.of(relation, right);
+ List<NamedExpression> setOutputs = ImmutableList.of(datetime.toSlot(),
id.toSlot());
+ List<List<SlotReference>> outputs = ImmutableList.of(
+ ImmutableList.of((SlotReference) datetime.toSlot(),
(SlotReference) id.toSlot()),
+ ImmutableList.of((SlotReference) rightDatetime.toSlot(),
(SlotReference) rightId.toSlot()));
+ LogicalExcept except = new LogicalExcept(Qualifier.DISTINCT,
setOutputs, outputs, children);
+ LogicalIntersect intersect = new LogicalIntersect(Qualifier.DISTINCT,
+ setOutputs, outputs, children);
+ PhysicalOneRowRelation source = new
PhysicalOneRowRelation(relation.getRelationId(),
+ relation.getProjects(), relation.getLogicalProperties());
+ PhysicalOneRowRelation physicalRight = new
PhysicalOneRowRelation(right.getRelationId(),
+ right.getProjects(), right.getLogicalProperties());
+ List<Plan> physicalChildren = ImmutableList.of(source, physicalRight);
+ assertRejectingBoundary(except, new PhysicalExcept(Qualifier.DISTINCT,
+ setOutputs, outputs, except.getLogicalProperties(),
physicalChildren));
+ assertRejectingBoundary(intersect, new
PhysicalIntersect(Qualifier.DISTINCT,
+ setOutputs, outputs, intersect.getLogicalProperties(),
physicalChildren));
+ }
+
+ @Test
+ void protectGenerate() {
+ Explode explode = new Explode(new ArrayLiteral(ImmutableList.of(new
IntegerLiteral(1))));
+ SlotReference item = new SlotReference("item", IntegerType.INSTANCE);
+ LogicalGenerate<?> generate = new
LogicalGenerate<>(ImmutableList.of(explode),
+ ImmutableList.of(item), relation);
+ PhysicalOneRowRelation source = new
PhysicalOneRowRelation(relation.getRelationId(),
+ relation.getProjects(), relation.getLogicalProperties());
+ assertRejectingBoundary(generate, new
PhysicalGenerate<>(ImmutableList.of(explode),
+ ImmutableList.of(item), ImmutableList.of(),
generate.getLogicalProperties(), source));
+ }
+
+ private void assertRejectingBoundary(Plan logicalInput, Plan
physicalInput) {
+ for (int offset : new int[] {0, 5}) {
+ for (Expression expression : addSubExpressions(new
IntegerLiteral(offset))) {
+ Alias result = new Alias(new Cast(expression,
DateTimeV2Type.of(0)), "result");
+ Expression predicate = new EqualTo(result.toSlot(), new
DateTimeV2Literal("2020-02-05 00:00:00"));
+ LogicalProject<?> project = new
LogicalProject<>(ImmutableList.of(result), logicalInput);
+ LogicalFilter<?> filter = new
LogicalFilter<>(ImmutableSet.of(predicate), project);
+ Plan rewritten =
PlanChecker.from(MemoTestUtils.createConnectContext(), filter)
+ .applyTopDown(new
PushDownFilterThroughProject()).getPlan();
+ Assertions.assertEquals(offset == 0 ? LogicalProject.class :
LogicalFilter.class, rewritten.getClass());
+ PhysicalProject<?> physicalProject = new
PhysicalProject<>(ImmutableList.of(result),
+ project.getLogicalProperties(), physicalInput);
+ PhysicalFilter<?> physicalFilter = new
PhysicalFilter<>(ImmutableSet.of(predicate),
+ physicalProject.getLogicalProperties(),
physicalProject);
+ Plan physicalRewritten = physicalFilter.accept(
+ new
org.apache.doris.nereids.processor.post.PushDownFilterThroughProject(), null);
+ Assertions.assertEquals(offset == 0 ? PhysicalProject.class :
PhysicalFilter.class,
+ physicalRewritten.getClass());
+ }
+ }
+ }
+
+ @Test
+ void keepLogicalDatePredicateAboveProject() {
+ for (Expression expression : dateExpressions()) {
+ Alias result = new Alias(new Cast(expression,
DateTimeV2Type.of(0)), "result");
+ LogicalFilter<LogicalOneRowRelation> input = new LogicalFilter<>(
+ ImmutableSet.of(new EqualTo(id.toSlot(), new
IntegerLiteral(0))), relation);
+ LogicalProject<LogicalFilter<LogicalOneRowRelation>> project = new
LogicalProject<>(
+ ImmutableList.of(result, id.toSlot()), input);
+ Expression predicate = new EqualTo(result.toSlot(), new
DateTimeV2Literal("2020-01-01 00:00:00"));
+ LogicalFilter<?> filter = new
LogicalFilter<>(ImmutableSet.of(predicate), project);
+ Plan rewritten =
PlanChecker.from(MemoTestUtils.createConnectContext(), filter)
+ .applyTopDown(new
PushDownFilterThroughProject()).getPlan();
+ Assertions.assertInstanceOf(LogicalFilter.class, rewritten,
expression.toSql());
+ Assertions.assertInstanceOf(LogicalProject.class,
rewritten.child(0));
+ }
+ }
+
+ @Test
+ void pushZeroOffsetDatePredicateThroughLowerFilter() {
+ for (Expression zero : ImmutableList.of(new IntegerLiteral(0), new
BigIntLiteral(0))) {
+ for (Expression expression : addSubExpressions(zero)) {
+ Alias result = new Alias(expression, "result");
+ LogicalFilter<?> input = new LogicalFilter<>(
+ ImmutableSet.of(new EqualTo(id.toSlot(), new
IntegerLiteral(0))), relation);
+ LogicalProject<?> project = new
LogicalProject<>(ImmutableList.of(result, id.toSlot()), input);
+ Expression predicate = new EqualTo(result.toSlot(), new
DateTimeV2Literal("2020-01-01 00:00:00"));
+ LogicalFilter<?> filter = new
LogicalFilter<>(ImmutableSet.of(predicate), project);
+ Plan rewritten =
PlanChecker.from(MemoTestUtils.createConnectContext(), filter)
+ .applyTopDown(new
PushDownFilterThroughProject()).getPlan();
+ Assertions.assertInstanceOf(LogicalProject.class, rewritten);
+ Assertions.assertInstanceOf(LogicalFilter.class,
rewritten.child(0));
+
+ PhysicalOneRowRelation source = new
PhysicalOneRowRelation(relation.getRelationId(),
+ relation.getProjects(),
relation.getLogicalProperties());
+ PhysicalFilter<?> physicalInput = new
PhysicalFilter<>(input.getConjuncts(),
+ source.getLogicalProperties(), source);
+ PhysicalProject<?> physicalProject = new
PhysicalProject<>(project.getProjects(),
+ project.getLogicalProperties(), physicalInput);
+ PhysicalFilter<?> physicalFilter = new
PhysicalFilter<>(ImmutableSet.of(predicate),
+ physicalProject.getLogicalProperties(),
physicalProject);
+ Plan physicalRewritten = physicalFilter.accept(
+ new
org.apache.doris.nereids.processor.post.PushDownFilterThroughProject(), null);
+ Assertions.assertInstanceOf(PhysicalProject.class,
physicalRewritten);
+ Assertions.assertInstanceOf(PhysicalFilter.class,
physicalRewritten.child(0));
+ }
+ }
+ }
+
+ @Test
+ void pushLogicalDatePredicateWithoutLowerFilter() {
+ for (Expression expression :
ImmutableList.<Expression>builder().addAll(dateExpressions())
+ .add(new DaysAdd(datetime.toSlot(), new
IntegerLiteral(0))).build()) {
+ Alias result = new Alias(new Cast(expression,
DateTimeV2Type.of(0)), "result");
+ LogicalProject<?> project = new
LogicalProject<>(ImmutableList.of(result, id.toSlot()), relation);
+ Expression predicate = new EqualTo(result.toSlot(), new
DateTimeV2Literal("2020-01-01 00:00:00"));
+ LogicalFilter<?> filter = new
LogicalFilter<>(ImmutableSet.of(predicate), project);
+ Plan rewritten =
PlanChecker.from(MemoTestUtils.createConnectContext(), filter)
+ .applyTopDown(new
PushDownFilterThroughProject()).getPlan();
+ Assertions.assertInstanceOf(LogicalProject.class, rewritten,
expression.toSql());
+ Assertions.assertInstanceOf(LogicalFilter.class,
rewritten.child(0));
+ }
+ }
+
+ @Test
+ void pushPhysicalDatePredicateWithoutLowerFilter() {
+ for (Expression expression :
ImmutableList.<Expression>builder().addAll(dateExpressions())
+ .add(new DaysAdd(datetime.toSlot(), new
IntegerLiteral(0))).build()) {
+ Alias result = new Alias(new Cast(expression,
DateTimeV2Type.of(0)), "result");
+ PhysicalOneRowRelation source = new
PhysicalOneRowRelation(relation.getRelationId(),
+ relation.getProjects(), relation.getLogicalProperties());
+ LogicalProject<?> logicalProject = new
LogicalProject<>(ImmutableList.of(result, id.toSlot()), relation);
+ PhysicalProject<?> project = new
PhysicalProject<>(ImmutableList.of(result, id.toSlot()),
+ logicalProject.getLogicalProperties(), source);
+ PhysicalFilter<?> filter = new PhysicalFilter<>(
+ ImmutableSet.of(new EqualTo(result.toSlot(), new
DateTimeV2Literal("2020-01-01 00:00:00"))),
+ project.getLogicalProperties(), project);
+ Plan rewritten = filter.accept(
+ new
org.apache.doris.nereids.processor.post.PushDownFilterThroughProject(), null);
+ Assertions.assertInstanceOf(PhysicalProject.class, rewritten,
expression.toSql());
+ Assertions.assertInstanceOf(PhysicalFilter.class,
rewritten.child(0));
+ }
+ }
+
+ @Test
+ void keepLogicalDatePredicateAboveJoinOrLimit() {
+ LogicalOneRowRelation right = new LogicalOneRowRelation(
+ StatementScopeIdGenerator.newRelationId(),
ImmutableList.of(new Alias(new IntegerLiteral(1), "right")));
+ for (Plan input : ImmutableList.of(
+ new LogicalJoin<>(JoinType.INNER_JOIN, relation, right, new
JoinReorderContext()),
+ new LogicalLimit<>(0, 0, LimitPhase.ORIGIN, relation))) {
+ Alias result = new Alias(new WeeksAdd(datetime.toSlot(), new
IntegerLiteral(5)), "result");
+ LogicalProject<?> project = new
LogicalProject<>(ImmutableList.of(result), input);
+ LogicalFilter<?> filter = new LogicalFilter<>(ImmutableSet.of(
+ new EqualTo(result.toSlot(), new
DateTimeV2Literal("2020-01-01 00:00:00"))), project);
+ Plan rewritten =
PlanChecker.from(MemoTestUtils.createConnectContext(), filter)
+ .applyTopDown(new
PushDownFilterThroughProject()).getPlan();
+ Assertions.assertInstanceOf(LogicalFilter.class, rewritten);
+ Assertions.assertInstanceOf(LogicalProject.class,
rewritten.child(0));
+ }
+ }
+
+ @Test
+ void liftDateEvaluationAboveLimitProject() {
+ LogicalFilter<?> filtered = new LogicalFilter<>(
+ ImmutableSet.of(new EqualTo(id.toSlot(), new
IntegerLiteral(1))), relation);
+ for (Plan input : ImmutableList.of(relation, filtered)) {
+ for (long offset : new long[] {1, 100, 0}) {
+ for (Expression expression : dateExpressions()) {
+ Alias result = new Alias(new Cast(expression,
DateTimeV2Type.of(0)), "result");
+ LogicalProject<?> project = new
LogicalProject<>(ImmutableList.of(result), input);
+ LogicalLimit<?> limit = new LogicalLimit<>(1, offset,
LimitPhase.ORIGIN, project);
+ LogicalFilter<?> filter = new
LogicalFilter<>(ImmutableSet.of(
+ new EqualTo(result.toSlot(), new
DateTimeV2Literal("2020-02-05 00:00:00"))), limit);
+ Plan rewritten =
PlanChecker.from(MemoTestUtils.createConnectContext(), filter)
+ .applyTopDown(new
PushDownFilterThroughProject()).getPlan();
+ Assertions.assertInstanceOf(LogicalProject.class,
rewritten);
+ Assertions.assertInstanceOf(LogicalFilter.class,
rewritten.child(0));
+ Assertions.assertInstanceOf(LogicalLimit.class,
rewritten.child(0).child(0));
+ LogicalLimit<?> liftedLimit = (LogicalLimit<?>)
rewritten.child(0).child(0);
+ Assertions.assertEquals(offset, liftedLimit.getOffset());
+ Assertions.assertEquals(1, liftedLimit.getLimit());
+ Assertions.assertEquals(input.getClass(),
liftedLimit.child().getClass());
+ }
+ }
+ }
+ }
+
+ @Test
+ void keepDatePredicateAboveCteConsumer() {
+ LogicalCTEConsumer consumer = new
LogicalCTEConsumer(StatementScopeIdGenerator.newRelationId(),
+ StatementScopeIdGenerator.newCTEId(), "c", relation);
+ Alias result = new Alias(new WeeksAdd(consumer.getOutput().get(0), new
IntegerLiteral(5)), "result");
+ LogicalProject<?> project = new
LogicalProject<>(ImmutableList.of(result), consumer);
+ Expression predicate = new EqualTo(result.toSlot(), new
DateTimeV2Literal("2020-01-01 00:00:00"));
+ LogicalFilter<?> filter = new
LogicalFilter<>(ImmutableSet.of(predicate), project);
+ Plan rewritten =
PlanChecker.from(MemoTestUtils.createConnectContext(), filter)
+ .applyTopDown(new PushDownFilterThroughProject()).getPlan();
+ Assertions.assertInstanceOf(LogicalFilter.class, rewritten);
+ Assertions.assertInstanceOf(LogicalProject.class, rewritten.child(0));
+
+ PhysicalCTEConsumer physicalConsumer = new
PhysicalCTEConsumer(consumer.getRelationId(), consumer.getCteId(),
+ consumer.getConsumerToProducerOutputMap(),
consumer.getProducerToConsumerOutputMap(),
+ consumer.getLogicalProperties());
+ PhysicalProject<?> physicalProject = new
PhysicalProject<>(ImmutableList.of(result),
+ project.getLogicalProperties(), physicalConsumer);
+ PhysicalFilter<?> physicalFilter = new
PhysicalFilter<>(ImmutableSet.of(predicate),
+ physicalProject.getLogicalProperties(), physicalProject);
+ Plan physicalRewritten = physicalFilter.accept(
+ new
org.apache.doris.nereids.processor.post.PushDownFilterThroughProject(), null);
+ Assertions.assertInstanceOf(PhysicalFilter.class, physicalRewritten);
+ Assertions.assertInstanceOf(PhysicalProject.class,
physicalRewritten.child(0));
+ }
+
+ @Test
+ void stillPushLogicalPassthroughPredicate() {
+ Alias result = new Alias(new WeeksAdd(datetime.toSlot(), new
IntegerLiteral(5)), "result");
+ LogicalProject<?> project = new
LogicalProject<>(ImmutableList.of(result, id.toSlot()), relation);
+ Expression predicate = new EqualTo(id.toSlot(), new IntegerLiteral(0));
+ LogicalFilter<?> filter = new
LogicalFilter<>(ImmutableSet.of(predicate), project);
+ Plan rewritten =
PlanChecker.from(MemoTestUtils.createConnectContext(), filter)
+ .applyTopDown(new PushDownFilterThroughProject()).getPlan();
+ Assertions.assertInstanceOf(LogicalProject.class, rewritten);
+ Assertions.assertInstanceOf(LogicalFilter.class, rewritten.child(0));
+ }
+
+ @Test
+ void keepPhysicalDatePredicateAboveProject() {
+ for (Expression expression : dateExpressions()) {
+ Alias result = new Alias(new Cast(expression,
DateTimeV2Type.of(0)), "result");
+ PhysicalOneRowRelation source = new
PhysicalOneRowRelation(relation.getRelationId(),
+ relation.getProjects(), relation.getLogicalProperties());
+ PhysicalFilter<?> input = new PhysicalFilter<>(
+ ImmutableSet.of(new EqualTo(id.toSlot(), new
IntegerLiteral(0))),
+ source.getLogicalProperties(), source);
+ LogicalProject<?> logicalProject = new
LogicalProject<>(ImmutableList.of(result, id.toSlot()), relation);
+ PhysicalProject<?> project = new
PhysicalProject<>(ImmutableList.of(result, id.toSlot()),
+ logicalProject.getLogicalProperties(), input);
+ PhysicalFilter<?> filter = new PhysicalFilter<>(
+ ImmutableSet.of(new EqualTo(result.toSlot(), new
DateTimeV2Literal("2020-01-01 00:00:00"))),
+ project.getLogicalProperties(), project);
+ Plan rewritten = filter.accept(
+ new
org.apache.doris.nereids.processor.post.PushDownFilterThroughProject(), null);
+ Assertions.assertInstanceOf(PhysicalFilter.class, rewritten,
expression.toSql());
+ Assertions.assertInstanceOf(PhysicalProject.class,
rewritten.child(0));
+ }
+ }
+}
diff --git
a/regression-test/data/external_table_p0/tvf/test_date_expression_filter.out
b/regression-test/data/external_table_p0/tvf/test_date_expression_filter.out
new file mode 100644
index 00000000000..7bc7653a638
--- /dev/null
+++ b/regression-test/data/external_table_p0/tvf/test_date_expression_filter.out
@@ -0,0 +1,47 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !filtered_date --
+
+-- !filtered_date --
+
+-- !filtered_date --
+
+-- !filtered_date --
+
+-- !filtered_date --
+
+-- !valid_rows --
+2020-02-05 00:00:00 1
+
+-- !shared_cte --
+2020-02-05 00:00:00
+2020-02-05 00:00:00
+
+-- !filtered_date --
+
+-- !filtered_date --
+
+-- !filtered_date --
+
+-- !filtered_date --
+
+-- !filtered_date --
+
+-- !valid_rows --
+2020-02-05 00:00:00 1
+
+-- !shared_cte --
+2020-02-05 00:00:00
+2020-02-05 00:00:00
+
+-- !partition_pruning --
+2
+
+-- !partition_pruning --
+1
+
+-- !zero_offset_partition_pruning --
+2
+
+-- !null --
+\N
+
diff --git
a/regression-test/data/nereids_rules_p0/filter_push_down/test_date_arithmetic_rejecting_boundary.out
b/regression-test/data/nereids_rules_p0/filter_push_down/test_date_arithmetic_rejecting_boundary.out
new file mode 100644
index 00000000000..fe7329764f8
--- /dev/null
+++
b/regression-test/data/nereids_rules_p0/filter_push_down/test_date_arithmetic_rejecting_boundary.out
@@ -0,0 +1,13 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !except --
+2020-02-05T00:00
+
+-- !intersect --
+2020-02-05T00:00
+
+-- !generate --
+2020-02-05T00:00
+
+-- !zero_offset --
+2020-01-01T00:00
+
diff --git
a/regression-test/suites/external_table_p0/tvf/test_date_expression_filter.groovy
b/regression-test/suites/external_table_p0/tvf/test_date_expression_filter.groovy
new file mode 100644
index 00000000000..9e453ec3124
--- /dev/null
+++
b/regression-test/suites/external_table_p0/tvf/test_date_expression_filter.groovy
@@ -0,0 +1,107 @@
+// 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("test_date_expression_filter", "p0,external,hive,external_docker") {
+ if
(!context.config.otherConfigs.get("enableHiveTest")?.equalsIgnoreCase("true")) {
+ logger.info("Hive external tests are disabled")
+ return
+ }
+ def host = context.config.otherConfigs.get("externalEnvIp")
+ def port = context.config.otherConfigs.get("hive2HdfsPort")
+ def defaultFS = "hdfs://${host}:${port}"
+ def rows = """SELECT CAST(1 AS INT) AS id,
+ CAST('9999-12-31 23:59:59' AS DATETIME(6)) AS dt,
+ CAST('2025-01-01' AS DATETIME(6)) AS filter_dt
+ UNION ALL SELECT 2, CAST('2020-01-01' AS DATETIME(6)),
+ CAST('2020-01-01' AS DATETIME(6))"""
+ sql "set short_circuit_evaluation=true"
+ for (def format : ["orc", "parquet"]) {
+ def path =
"${defaultFS}/user/doris/tmp_data/test_date_expression_filter/${UUID.randomUUID()}/exp_"
+ def exported = sql """SELECT * FROM (${rows}) AS input
+ INTO OUTFILE "${path}" FORMAT AS ${format}
+ PROPERTIES("fs.defaultFS"="${defaultFS}",
"hadoop.username"="doris")"""
+ def source = """HDFS("uri"="${exported[0][3]}",
+ "hadoop.username"="doris",
"format"="${format}")"""
+ for (def function : ["WEEKS_ADD", "HOURS_ADD", "SECOND_CEIL",
"YEAR_CEIL", "DAYS_ADD"]) {
+ // The outer predicate must not evaluate a partial date function
before the inner WHERE.
+ order_qt_filtered_date """WITH cte1 AS (
+ SELECT CAST(${function}(dt,5) AS DATETIME) AS d,
+ APPROX_COUNT_DISTINCT(id) AS n
+ FROM ${source}
+ WHERE filter_dt <= (SELECT MIN('2024-07-01') FROM ${source})
+ AND CAST(WEEKS_SUB(filter_dt,0) AS DATETIME)='2012-05-05
11:11:11'
+ GROUP BY d ORDER BY d,n
+ ), cte2 AS (SELECT * FROM cte1 WHERE d='2019-10-23 16:50:42')
+ SELECT * FROM cte2"""
+ }
+ order_qt_valid_rows """WITH cte AS (
+ SELECT CAST(WEEKS_ADD(dt,5) AS DATETIME) AS d,
APPROX_COUNT_DISTINCT(id) AS n
+ FROM ${source} WHERE filter_dt='2020-01-01' GROUP BY d
+ ) SELECT DATE_FORMAT(d,'%Y-%m-%d %H:%i:%s'),n FROM cte WHERE
d='2020-02-05'"""
+ // Shared CTE predicates may be collected and pushed back into a
producer with a hidden WHERE.
+ def inlineThreshold = sql("SHOW VARIABLES LIKE
'inline_cte_referenced_threshold'")[0][1]
+ sql "set inline_cte_referenced_threshold=0"
+ try {
+ def branch = """SELECT DATE_FORMAT(d,'%Y-%m-%d %H:%i:%s') FROM (
+ SELECT CAST(WEEKS_ADD(dt,5) AS DATETIME) AS d FROM
filtered_input
+ ) s WHERE d='2020-02-05'"""
+ def sharedCte = """WITH filtered_input AS (
+ SELECT dt FROM ${source} WHERE filter_dt='2020-01-01'
+ ) ${branch} UNION ALL ${branch}"""
+ order_qt_shared_cte sharedCte
+ } finally {
+ sql "set inline_cte_referenced_threshold=${inlineThreshold}"
+ }
+ // Overflow on a row that actually participates in date arithmetic
must still be reported.
+ test {
+ sql "SELECT WEEKS_ADD(dt,5) FROM ${source}"
+ exception "Operation week_add of 9999-12-31 23:59:59, 5 out of
range"
+ }
+ }
+ sql "DROP TABLE IF EXISTS test_date_expression_filter_partition_pruning"
+ // Explicit partitions make the safe alias predicate's pruning boundary
observable.
+ sql """CREATE TABLE test_date_expression_filter_partition_pruning (dt DATE
NOT NULL, id INT)
+ DUPLICATE KEY(dt, id)
+ PARTITION BY RANGE(dt) (
+ PARTITION p1 VALUES [('2020-01-01'), ('2020-01-02')),
+ PARTITION p2 VALUES [('2020-01-02'), ('2020-01-03')),
+ PARTITION p3 VALUES [('2020-01-03'), ('2020-01-04'))
+ )
+ DISTRIBUTED BY HASH(dt) BUCKETS 3
+ PROPERTIES("replication_num"="1")"""
+ sql "INSERT INTO test_date_expression_filter_partition_pruning VALUES
('2020-01-01',1),('2020-01-02',2),('2020-01-03',3)"
+ for (def offset : [0, 1]) {
+ def query = """SELECT id FROM (
+ SELECT DAYS_ADD(dt,${offset}) AS d,id FROM
test_date_expression_filter_partition_pruning
+ ) s WHERE d='2020-01-02'"""
+ explain {
+ sql query
+ contains "partitions=1/3 (p${2 - offset})"
+ }
+ order_qt_partition_pruning query
+ }
+ // A zero-offset alias stays safe even with an unrelated lower WHERE.
+ def zeroOffsetQuery = """SELECT id FROM (
+ SELECT DAYS_ADD(dt,0) AS d,id FROM
test_date_expression_filter_partition_pruning WHERE id > 0
+ ) s WHERE d='2020-01-02'"""
+ explain {
+ sql zeroOffsetQuery
+ contains "partitions=1/3 (p2)"
+ }
+ order_qt_zero_offset_partition_pruning zeroOffsetQuery
+ order_qt_null "SELECT WEEKS_ADD(CAST(NULL AS DATETIME),5)"
+}
diff --git
a/regression-test/suites/nereids_rules_p0/filter_push_down/test_date_arithmetic_rejecting_boundary.groovy
b/regression-test/suites/nereids_rules_p0/filter_push_down/test_date_arithmetic_rejecting_boundary.groovy
new file mode 100644
index 00000000000..b3fcb5399ce
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/filter_push_down/test_date_arithmetic_rejecting_boundary.groovy
@@ -0,0 +1,102 @@
+// 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("test_date_arithmetic_rejecting_boundary") {
+ sql "DROP TABLE IF EXISTS test_date_boundary_input"
+ sql "DROP TABLE IF EXISTS test_date_boundary_right"
+ sql """CREATE TABLE test_date_boundary_input (id INT, dt DATETIME(6), arr
ARRAY<INT>)
+ DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES("replication_num"="1")"""
+ sql """CREATE TABLE test_date_boundary_right (dt DATETIME(6))
+ DUPLICATE KEY(dt) DISTRIBUTED BY HASH(dt) BUCKETS 1
+ PROPERTIES("replication_num"="1")"""
+ sql """INSERT INTO test_date_boundary_input VALUES
+ (1,'9999-12-31 23:59:59.000001',[]), (2,'2020-01-01
00:00:00.000001',[1])"""
+ sql "INSERT INTO test_date_boundary_right VALUES ('9999-12-31
23:59:59.000001')"
+
+ // The cast narrows precision so inverse date comparison rewriting cannot
hide early evaluation.
+ // No optimizer rule is disabled: EXCEPT must reject the overflowing row
before arithmetic.
+ order_qt_except """SELECT d FROM (
+ SELECT CAST(WEEKS_ADD(dt,5) AS DATETIME(0)) AS d FROM (
+ SELECT dt FROM test_date_boundary_input
+ EXCEPT SELECT dt FROM test_date_boundary_right
+ ) difference
+ ) s WHERE d='2020-02-05'"""
+
+ // The compensating rewrite must restore date evaluation above the global
OFFSET.
+ // The right-hand table still contains exactly one maximum-date row here.
+ for (def offset : [1, 100]) {
+ assert sql("""SELECT d FROM (
+ SELECT CAST(WEEKS_ADD(dt,5) AS DATETIME(0)) AS d FROM (
+ SELECT dt FROM test_date_boundary_right LIMIT 1 OFFSET
${offset}
+ ) limited
+ ) s WHERE d='2020-02-05'""").isEmpty()
+ }
+ test {
+ sql """SELECT d FROM (
+ SELECT CAST(WEEKS_ADD(dt,5) AS DATETIME(0)) AS d FROM (
+ SELECT dt FROM test_date_boundary_right LIMIT 1 OFFSET 0
+ ) limited
+ ) s WHERE d='2020-02-05'"""
+ exception "out of range"
+ }
+
+ // INTERVAL syntax retains TimestampArithmetic rather than the scalar
function node.
+ for (def scenario : [
+ ["dt + INTERVAL 5 WEEK", "2020-02-05"],
+ ["dt - INTERVAL -5 WEEK", "2020-02-05"],
+ ["dt - INTERVAL 5 WEEK", "2019-11-27"],
+ ["dt + INTERVAL 0 WEEK", "2020-01-01"],
+ ["dt - INTERVAL 0 WEEK", "2020-01-01"]]) {
+ def actual = sql """SELECT DATE_FORMAT(d, '%Y-%m-%d %H:%i:%s') FROM (
+ SELECT CAST(${scenario[0]} AS DATETIME(0)) AS d FROM (
+ SELECT dt FROM test_date_boundary_input
+ EXCEPT SELECT dt FROM test_date_boundary_right
+ ) difference
+ ) s WHERE d='${scenario[1]}'"""
+ assert actual == [[scenario[1] + " 00:00:00"]]
+ }
+ test {
+ sql "SELECT dt + INTERVAL 5 WEEK FROM test_date_boundary_input"
+ exception "out of range"
+ }
+
+ sql "TRUNCATE TABLE test_date_boundary_right"
+ sql "INSERT INTO test_date_boundary_right VALUES ('2020-01-01
00:00:00.000001')"
+ order_qt_intersect """SELECT d FROM (
+ SELECT CAST(WEEKS_ADD(dt,5) AS DATETIME(0)) AS d FROM (
+ SELECT dt FROM test_date_boundary_input
+ INTERSECT SELECT dt FROM test_date_boundary_right
+ ) common_rows
+ ) s WHERE d='2020-02-05'"""
+
+ // Non-outer explode drops the overflowing row because its array is empty.
+ order_qt_generate """SELECT d FROM (
+ SELECT CAST(WEEKS_ADD(dt,5) AS DATETIME(0)) AS d
+ FROM test_date_boundary_input LATERAL VIEW explode(arr) lv AS item
+ ) s WHERE d='2020-02-05'"""
+
+ // Identity date arithmetic and ordinary aliases remain pushable at these
boundaries.
+ order_qt_zero_offset """SELECT d FROM (
+ SELECT CAST(WEEKS_ADD(dt,0) AS DATETIME(0)) AS d
+ FROM test_date_boundary_input LATERAL VIEW explode(arr) lv AS item
+ ) s WHERE d='2020-01-01'"""
+ test {
+ sql "SELECT WEEKS_ADD(dt,5) FROM test_date_boundary_input"
+ exception "out of range"
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
