This is an automated email from the ASF dual-hosted git repository.
Gabriel39 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 d6301781cab [fix](nereids) preserve date arithmetic evaluation
boundaries (#69113)
d6301781cab is described below
commit d6301781cab1ee13d8e127a4e5c25fd04e4043bd
Author: Oliveira <[email protected]>
AuthorDate: Fri Oct 16 22:14:25 2026 +0800
[fix](nereids) preserve date arithmetic evaluation boundaries (#69113)
### What problem does this PR solve?
Issue Number: N/A (no associated GitHub issue)
Related PR: #69073
Problem Summary:
An outer predicate on a projected date expression can be expanded and
pushed below an inner condition. This evaluates date arithmetic on rows
that the inner query would otherwise reject. A filtered ORC/Parquet row
containing `9999-12-31 23:59:59` can therefore cause `WEEKS_ADD(dt, 5)`
to raise an out-of-range error instead of returning the expected empty
result.
Preserve potentially overflowing date arithmetic aliases above lower
Filter, Join, Limit, EXCEPT, INTERSECT, Generate and CTE consumer
boundaries during logical and physical project predicate pushdown.
Retain the existing volatile/non-movable expression checks. Date
arithmetic without a lower boundary and integer zero-offset aliases
remain eligible for pushdown, including partition pruning with an
unrelated inner WHERE.
### 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 test results</summary>
Built and deployed this branch using JDK 17 for FE and clang21 / ASAN
for BE. Compilation, integrated FE Checkstyle, formatting, build hygiene
and whitespace checks passed.
- **FE unit tests:** 17 passed, with no failures, errors or skips:
`DateArithmeticFilterPushDownTest` (11),
`PushDowFilterThroughProjectTest` (4), and
`postprocess.PushDownFilterThroughProjectTest` (2).
- **AI review follow-up (2026-10-16):** added EXCEPT, INTERSECT and
Generate to the existing shared date-arithmetic protection helper.
Ordinary UNION and safe integer zero-offset pushdown remain unchanged.
Added logical/physical tests and
`nereids_rules_p0/filter_push_down/test_date_arithmetic_rejecting_boundary`,
which retains a precision-lowering CAST and runs the full optimizer
without disabling rules. Its stable-input snapshot comparison passed for
the three rejecting boundaries, safe zero offset and expected real
overflow. The snapshot was generated by the regression framework.
- **Follow-up validation:** 17 FE unit tests, FE/BE build, integrated
Checkstyle, and quick format/build-hygiene checks passed. The new P0
suite plus `date_function_rewrite`,
`nereids_rules_p0/cte/test_cte_filter_pushdown` and External
`test_date_expression_filter` all passed with no failed/skipped suites
on the verified branch build in disposable state. Source, artifact,
process and input-stability checks passed. Original personal
metadata/storage were preserved separately throughout validation.
- **External regression:**
`external_table_p0/tvf/test_date_expression_filter` passed in both
snapshot-generation and comparison modes. Covers ORC/Parquet, five date
functions, shared CTEs, real overflow errors, NULL, and `partitions=1/3`
for zero-offset aliases with an unrelated inner WHERE. The output file
was generated by the regression framework.
- **P0 regression:** all four targeted suites passed, with no skips:
- `doc/sql-manual/sql-functions/doc_date_error`
- `nereids_rules_p0/cte/test_cte_filter_pushdown`
- `query_p0/cte/test_cte_filter_pushdown`
- `nereids_rules_p0/date_function_rewrite/date_function_rewrite`
- **Manual replay:** ran the original query body with normal optimizer
rules enabled against reconstructed 200-row ORC and Parquet fixtures,
including the boundary timestamp. Both returned zero rows without
overflow. The original remote dataset was unavailable, so these were
reconstructed fixtures rather than the original files.
Tests ran against a personal local regression configuration with HDFS
enabled. Health checks passed before and after testing. This is targeted
local coverage; the full TeamCity gate, full regression groups and BE
unit tests were not run.
</details>
- Behavior changed:
- [ ] No.
- [x] Yes. Preserve date arithmetic evaluation boundaries while
retaining safe zero-offset predicate pushdown and partition pruning.
- 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 | 9 +-
.../apache/doris/nereids/util/ExpressionUtils.java | 42 +++
.../rewrite/DateArithmeticFilterPushDownTest.java | 314 +++++++++++++++++++++
.../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 | 64 +++++
8 files changed, 594 insertions(+), 5 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..e1f317acdab 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.containsVolatileOrNoneMovableExpression()
+ ||
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 0f3478e0642..f3a791686d7 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());
Set<Expression> remainPredicates = splitConjuncts.first;
Set<Expression> pushDownPredicates = splitConjuncts.second;
if (pushDownPredicates.isEmpty()) {
@@ -101,7 +101,7 @@ public class PushDownFilterThroughProject implements
RewriteRuleFactory {
Map<Slot, Expression> childAlias = project.getAliasToProducer();
// split the conjuncts by child's output
Pair<Set<Expression>, Set<Expression>> splitConjuncts =
- splitConjunctsByChildOutput(filter.getConjuncts(),
childOutputs, childAlias);
+ splitConjunctsByChildOutput(filter.getConjuncts(),
childOutputs, childAlias, limit);
Set<Expression> remainPredicates = splitConjuncts.first;
Set<Expression> pushDownPredicates = splitConjuncts.second;
if (pushDownPredicates.isEmpty()) {
@@ -113,7 +113,7 @@ 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) {
Set<Expression> pushDownPredicates = Sets.newLinkedHashSet();
Set<Expression> remainPredicates = Sets.newLinkedHashSet();
for (Expression conjunct : conjuncts) {
@@ -125,7 +125,8 @@ 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::containsVolatileOrNoneMovableExpression)) {
+ .noneMatch(expression ->
expression.containsVolatileOrNoneMovableExpression()
+ ||
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 17c766b0b70..d0e649a90e8 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
@@ -56,6 +56,8 @@ 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.Udf;
import org.apache.doris.nereids.trees.expressions.functions.agg.Avg;
@@ -82,6 +84,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.Nvl;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.UniqueFunction;
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;
@@ -90,8 +93,16 @@ 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.Except;
+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.Intersect;
+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.LogicalUnion;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalCTEConsumer;
import org.apache.doris.nereids.trees.plans.visitor.ExpressionLineageReplacer;
import org.apache.doris.nereids.types.DataType;
import org.apache.doris.nereids.types.VariantType;
@@ -140,6 +151,37 @@ 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 Except || plan instanceof Intersect
|| plan instanceof Generate
+ || plan instanceof LogicalCTEConsumer || plan
instanceof PhysicalCTEConsumer);
+ }
+
+ private static boolean isUnsafeDateArithmetic(Expression expression) {
+ if (expression instanceof DateCeilFloorMonotonic) {
+ return true;
+ }
+ if (!(expression instanceof DateAddSubMonotonic)) {
+ return false;
+ }
+ DateAddSubMonotonic dateArithmetic = (DateAddSubMonotonic) expression;
+ int dateChildIndex = dateArithmetic.getMonotonicFunctionChildIndex();
+ 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;
+ }
+
/** Whether evaluating these expressions can produce results unsuitable
for reuse across queries. */
public static boolean containsNonCacheableExpression(Collection<? extends
Expression> expressions) {
return expressions.stream().anyMatch(expression ->
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..28f861e8eb4
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/DateArithmeticFilterPushDownTest.java
@@ -0,0 +1,314 @@
+// 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.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.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.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> dateExpressions() {
+ return ImmutableList.of(
+ new WeeksAdd(datetime.toSlot(), new IntegerLiteral(5)),
+ new HoursAdd(datetime.toSlot(), new IntegerLiteral(5)),
+ new SecondCeil(datetime.toSlot(), new IntegerLiteral(5)),
+ new YearCeil(datetime.toSlot(), new IntegerLiteral(5)),
+ new DaysAdd(datetime.toSlot(), new IntegerLiteral(5)));
+ }
+
+ @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}) {
+ Alias result = new Alias(new Cast(new WeeksAdd(datetime.toSlot(),
new IntegerLiteral(offset)),
+ 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))) {
+ Alias result = new Alias(new DaysAdd(datetime.toSlot(), zero),
"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 keepLogicalDatePredicateAboveLimitProject() {
+ Alias result = new Alias(new WeeksAdd(datetime.toSlot(), new
IntegerLiteral(5)), "result");
+ LogicalProject<?> project = new
LogicalProject<>(ImmutableList.of(result), relation);
+ LogicalLimit<?> limit = new LogicalLimit<>(0, 0, LimitPhase.ORIGIN,
project);
+ LogicalFilter<?> filter = new LogicalFilter<>(ImmutableSet.of(
+ new EqualTo(result.toSlot(), new DateTimeV2Literal("2020-01-01
00:00:00"))), limit);
+ Plan rewritten =
PlanChecker.from(MemoTestUtils.createConnectContext(), filter)
+ .applyTopDown(new PushDownFilterThroughProject()).getPlan();
+ Assertions.assertInstanceOf(LogicalFilter.class, rewritten);
+ Assertions.assertInstanceOf(LogicalLimit.class, rewritten.child(0));
+ }
+
+ @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..983d9f260a6
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/filter_push_down/test_date_arithmetic_rejecting_boundary.groovy
@@ -0,0 +1,64 @@
+// 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'"""
+
+ 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]
