This is an automated email from the ASF dual-hosted git repository.
jacktengg 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 19dfb8711ae [fix](partition prunning) Correct partition pruning for
calendar arithmetic (#69828)
19dfb8711ae is described below
commit 19dfb8711ae3dbb8f56cc945f5dc7af20a844607
Author: TengJianPing <[email protected]>
AuthorDate: Wed Nov 4 21:02:46 2026 +0800
[fix](partition prunning) Correct partition pruning for calendar arithmetic
(#69828)
### What problem does this PR solve?
Issue Number: None
Related PR: None
Problem Summary: Calendar month, quarter, year and YEAR_MONTH additions
and subtractions clamp dates at month end while preserving the time of
day. Consecutive input dates can therefore produce decreasing datetime
values. Treating these functions as globally monotonic lets RANGE
partition pruning remove partitions containing matching rows, such as
January 29 filtered by months_add(datetime_column, 1) < '2023-02-28
10:00:00'. Share a calendar arithmetic monotonicity trait that retains
the optimization for DATE and single-day ranges and keeps other datetime
ranges conservatively. This also covers DATE_ADD, DATE_SUB,
TIMESTAMPADD, INTERVAL expressions and runtime-filter classification.
MONTHS_DIFF, QUARTERS_DIFF and YEARS_DIFF also fold some month-end
datetime arguments differently from BE because LocalDateTime.until
adjusts the end date before counting calendar units. Count date units
first, then compare time parts only when the remaining date fields
match, preserving BE's truncation semantics for both argument orders and
all supported date-like literal types.
### Release note
Fix missing query rows caused by calendar arithmetic partition pruning
and align month, quarter and year difference constant folding with
runtime results.
### Check List (For Author)
- Test: FE build with Checkstyle; 94 FE unit tests; regression tests
- Unit Test: CalendarAddSubMonotonicTest,
DateTimeExtractAndTransformTest, PartitionPrunerTest,
RuntimeFilterPruneClassifierTest, TimestampNsMonotonicityTest and
InferPredicateFromMonotonicFunctionTest
- Regression test: test_calendar_arithmetic_prune (73 generated result
checks) and test_date_function_prune; verify the new suite fails on the
original FE
- Behavior changed: Yes, retain matching rows and correct calendar
difference constants
- Does this need documentation: No
### What problem does this PR solve?
Issue Number: close #xxx
Related PR: #xxx
Problem Summary:
### Release note
None
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [ ] Regression test
- [ ] Unit Test
- [ ] 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? -->
- Behavior changed:
- [ ] No.
- [ ] Yes. <!-- Explain the behavior change -->
- Does this need documentation?
- [ ] 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 -->
---
.../rules/OneRangePartitionEvaluator.java | 72 ++-----
.../functions/CalendarAddSubMonotonic.java | 87 ++++++++
.../trees/expressions/functions/Monotonic.java | 25 ++-
.../expressions/functions/RangeInferable.java | 72 +++++++
.../executable/DateTimeExtractAndTransform.java | 49 +++--
.../expressions/functions/scalar/MonthsAdd.java | 4 +-
.../expressions/functions/scalar/MonthsSub.java | 4 +-
.../expressions/functions/scalar/QuartersAdd.java | 4 +-
.../expressions/functions/scalar/QuartersSub.java | 4 +-
.../expressions/functions/scalar/YearMonthAdd.java | 4 +-
.../expressions/functions/scalar/YearMonthSub.java | 4 +-
.../expressions/functions/scalar/YearsAdd.java | 4 +-
.../expressions/functions/scalar/YearsSub.java | 4 +-
.../nereids/rules/rewrite/PartitionPrunerTest.java | 89 ++++++++
.../expressions/functions/RangeInferableTest.java | 112 ++++++++++
.../DateTimeExtractAndTransformTest.java | 43 ++++
.../scalar/CalendarAddSubMonotonicTest.java | 235 +++++++++++++++++++++
.../test_calendar_arithmetic_prune.out | 220 +++++++++++++++++++
.../test_calendar_arithmetic_prune.groovy | 157 ++++++++++++++
19 files changed, 1102 insertions(+), 91 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/OneRangePartitionEvaluator.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/OneRangePartitionEvaluator.java
index d5c6f7debdf..6fe4b1db6f1 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/OneRangePartitionEvaluator.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/OneRangePartitionEvaluator.java
@@ -40,13 +40,12 @@ import org.apache.doris.nereids.trees.expressions.Not;
import org.apache.doris.nereids.trees.expressions.NullSafeEqual;
import org.apache.doris.nereids.trees.expressions.Or;
import org.apache.doris.nereids.trees.expressions.Slot;
-import org.apache.doris.nereids.trees.expressions.functions.Monotonic;
+import org.apache.doris.nereids.trees.expressions.functions.RangeInferable;
import org.apache.doris.nereids.trees.expressions.functions.scalar.NonNullable;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Nullable;
import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
import org.apache.doris.nereids.trees.expressions.literal.MaxLiteral;
-import org.apache.doris.nereids.trees.expressions.literal.NullLiteral;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.types.BooleanType;
import org.apache.doris.nereids.util.ExpressionUtils;
@@ -69,6 +68,7 @@ import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Objects;
+import java.util.Optional;
import java.util.Set;
import java.util.function.BiFunction;
@@ -532,8 +532,8 @@ public class OneRangePartitionEvaluator<K>
for (int i = 0; i < children.size(); i++) {
Expression child = children.get(i);
EvaluateRangeResult childResult;
- if (child instanceof Monotonic) {
- childResult = visitMonotonic(child, context);
+ if (child instanceof RangeInferable) {
+ childResult = visitRangeInferable(child, context);
} else {
childResult = child.accept(this, context);
}
@@ -1005,21 +1005,21 @@ public class OneRangePartitionEvaluator<K>
}
}
- public EvaluateRangeResult visitMonotonic(Expression monotonic,
EvaluateRangeInput context) {
- EvaluateRangeResult rangeResult = evaluateChildrenThenThis(monotonic,
context);
- if (!rangeResult.result.getClass().equals(monotonic.getClass())) {
+ public EvaluateRangeResult visitRangeInferable(Expression expression,
EvaluateRangeInput context) {
+ EvaluateRangeResult rangeResult = evaluateChildrenThenThis(expression,
context);
+ if (!rangeResult.result.getClass().equals(expression.getClass())) {
return rangeResult;
}
- return computeMonotonicFunctionRange(rangeResult, context);
+ return computeFunctionRange(rangeResult, context);
}
- private EvaluateRangeResult
computeMonotonicFunctionRange(EvaluateRangeResult result, EvaluateRangeInput
context) {
- Monotonic func = (Monotonic) result.result;
+ private EvaluateRangeResult computeFunctionRange(EvaluateRangeResult
result, EvaluateRangeInput context) {
+ RangeInferable func = (RangeInferable) result.result;
if (context.rangeMap.containsKey(func)) {
return new EvaluateRangeResult((Expression) func,
ImmutableMap.of((Expression) func,
context.rangeMap.get(func)), result.childrenResult);
}
- int childIndex = func.getMonotonicFunctionChildIndex();
+ int childIndex = func.getRangeInputChildIndex();
Expression funcChild = func.child(childIndex);
boolean isNullable = partitionSlotContainsNull.getOrDefault(funcChild,
true);
Expression withNullable = func.withConstantArgs(isNullable ? new
Nullable(funcChild)
@@ -1039,51 +1039,17 @@ public class OneRangePartitionEvaluator<K>
Literal lower = span.hasLowerBound() ? span.lowerEndpoint().getValue()
: null;
Literal upper = span.hasUpperBound() &&
!(span.upperEndpoint().getValue() instanceof MaxLiteral)
? span.upperEndpoint().getValue() : null;
- if (!func.isMonotonic(lower, upper)) {
+ Optional<ColumnRange> inferredRange = func.inferRange(lower, upper,
expressionRewriteContext);
+ if (!inferredRange.isPresent()) {
return result;
}
- Expression lowerValue = lower != null ?
FoldConstantRuleOnFE.evaluate(func.withConstantArgs(lower),
- expressionRewriteContext) : null;
- Expression upperValue = upper != null ?
FoldConstantRuleOnFE.evaluate(func.withConstantArgs(upper),
- expressionRewriteContext) : null;
- if (!checkFoldConstantValueIsValid(lowerValue, upperValue)) {
- return result;
- }
-
- if (!func.isPositive()) {
- Expression temp = lowerValue;
- lowerValue = upperValue;
- upperValue = temp;
- }
- LinkedHashMap<Expression, ColumnRange> newRanges =
Maps.newLinkedHashMap();
- ColumnRange newRange = ColumnRange.all();
- if (lowerValue instanceof Literal && upperValue instanceof Literal &&
lowerValue.equals(upperValue)) {
- newRange = ColumnRange.singleton((Literal) lowerValue);
- context.rangeMap.put((Expression) func, newRange);
- newRanges.put((Expression) func, newRange);
- return new EvaluateRangeResult(lowerValue, newRanges,
result.childrenResult);
- } else {
- if (lowerValue instanceof Literal) {
- newRange = newRange.withLowerBound((Literal) lowerValue);
- }
- if (upperValue instanceof Literal) {
- newRange = newRange.withUpperBound((Literal) upperValue);
- }
- if (newRange.isEmptyRange() || !newRange.span().hasLowerBound() &&
!newRange.span().hasUpperBound()) {
- return result;
- }
- context.rangeMap.put((Expression) func, newRange);
- newRanges.put((Expression) func, newRange);
- return new EvaluateRangeResult((Expression) func, newRanges,
result.childrenResult);
- }
- }
- // only allow literal(except NullLiteral) and null
- private boolean checkFoldConstantValueIsValid(Expression lowerValue,
Expression upperValue) {
- if (lowerValue instanceof NullLiteral || upperValue instanceof
NullLiteral) {
- return false;
+ ColumnRange newRange = inferredRange.get();
+ context.rangeMap.put((Expression) func, newRange);
+ Map<Expression, ColumnRange> newRanges = ImmutableMap.of((Expression)
func, newRange);
+ if (newRange.isSingleton()) {
+ return new
EvaluateRangeResult(newRange.getLowerBound().getValue(), newRanges,
result.childrenResult);
}
- return (lowerValue == null || lowerValue instanceof Literal)
- && (upperValue == null || upperValue instanceof Literal);
+ return new EvaluateRangeResult((Expression) func, newRanges,
result.childrenResult);
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/CalendarAddSubMonotonic.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/CalendarAddSubMonotonic.java
new file mode 100644
index 00000000000..c374ed2305c
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/CalendarAddSubMonotonic.java
@@ -0,0 +1,87 @@
+// 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.trees.expressions.functions;
+
+import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext;
+import org.apache.doris.nereids.rules.expression.rules.ColumnRange;
+import org.apache.doris.nereids.trees.expressions.literal.DateLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.DateTimeV2Literal;
+import org.apache.doris.nereids.trees.expressions.literal.Literal;
+import org.apache.doris.nereids.trees.expressions.literal.TimeStampNsLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.TimestampTzLiteral;
+
+import java.time.LocalDateTime;
+import java.time.LocalTime;
+import java.util.Optional;
+
+/** Monotonicity of date arithmetic that can clamp the day to the end of a
month. */
+public interface CalendarAddSubMonotonic extends DateAddSubMonotonic {
+ @Override
+ default boolean isMonotonic(Literal lower, Literal upper) {
+ // Clamping consecutive dates to the same date resets the time at
midnight. DATE has no
+ // time part, and within one input day the calendar adjustment
preserves the time order.
+ return canEvaluateRange()
+ && (child(0).getDataType().isDateV2Type() ||
MonotonicityUtils.isWithinSameDay(lower, upper));
+ }
+
+ /** The date part remains non-decreasing even when clamping resets the
time part. */
+ default boolean canEvaluateRange() {
+ return MonotonicityUtils.hasConstantOtherArguments(this, 0);
+ }
+
+ @Override
+ default Optional<ColumnRange> inferRange(Literal lower, Literal upper,
ExpressionRewriteContext context) {
+ if (!canEvaluateRange()) {
+ return Optional.empty();
+ }
+ boolean monotonic = isMonotonic(lower, upper);
+ return evaluateRangeEndpoints(lower, upper, context).flatMap(bounds ->
RangeInferable.createRange(
+ monotonic || lower == null ? bounds.first
+ : getRangeLowerBound((DateLiteral) lower,
(DateLiteral) bounds.first),
+ monotonic || upper == null ? bounds.second
+ : getRangeUpperBound((DateLiteral) upper,
(DateLiteral) bounds.second)));
+ }
+
+ /** Later input days can map to this output date with an earlier time. */
+ default Literal getRangeLowerBound(DateLiteral input, DateLiteral output) {
+ if (input.getDay() <
input.toJavaDateType().toLocalDate().lengthOfMonth()
+ && output.getDay() ==
output.toJavaDateType().toLocalDate().lengthOfMonth()) {
+ return withTime(output, LocalTime.MIN);
+ }
+ return output;
+ }
+
+ /** Earlier input days can map to a clamped output date with a later time.
*/
+ default Literal getRangeUpperBound(DateLiteral input, DateLiteral output) {
+ if (output.getDay() < input.getDay()) {
+ return withTime(output, LocalTime.MAX);
+ }
+ return output;
+ }
+
+ private static Literal withTime(DateLiteral output, LocalTime time) {
+ LocalDateTime dateTime = output.toJavaDateType().with(time);
+ if (output instanceof TimeStampNsLiteral) {
+ return TimeStampNsLiteral.fromJavaDateType(dateTime);
+ }
+ if (output instanceof TimestampTzLiteral) {
+ return TimestampTzLiteral.fromJavaDateType(dateTime,
((TimestampTzLiteral) output).getScale());
+ }
+ return DateTimeV2Literal.fromJavaDateType(dateTime,
((DateTimeV2Literal) output).getDataType().getScale());
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/Monotonic.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/Monotonic.java
index feec5933890..e819f3b511c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/Monotonic.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/Monotonic.java
@@ -17,11 +17,14 @@
package org.apache.doris.nereids.trees.expressions.functions;
-import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext;
+import org.apache.doris.nereids.rules.expression.rules.ColumnRange;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
+import java.util.Optional;
+
/** monotonicity of expressions */
-public interface Monotonic extends ExpressionTrait {
+public interface Monotonic extends RangeInferable {
default boolean isMonotonic(Literal lower, Literal upper) {
return true;
}
@@ -33,8 +36,18 @@ public interface Monotonic extends ExpressionTrait {
// e.g. date_trunc(dt,'xxx') return 0
int getMonotonicFunctionChildIndex();
- // return the function with the arguments replaced by literal
- // e.g. date_trunc(dt, 'day'), dt in range ['2020-01-01 10:00:00',
'2020-01-03 10:00:00']
- // return date_trunc('2020-01-01 10:00:00', 'day')
- Expression withConstantArgs(Expression literal);
+ @Override
+ default int getRangeInputChildIndex() {
+ return getMonotonicFunctionChildIndex();
+ }
+
+ @Override
+ default Optional<ColumnRange> inferRange(Literal lower, Literal upper,
ExpressionRewriteContext context) {
+ if (!isMonotonic(lower, upper)) {
+ return Optional.empty();
+ }
+ return evaluateRangeEndpoints(lower, upper, context).flatMap(bounds ->
isPositive()
+ ? RangeInferable.createRange(bounds.first, bounds.second)
+ : RangeInferable.createRange(bounds.second, bounds.first));
+ }
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/RangeInferable.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/RangeInferable.java
new file mode 100644
index 00000000000..064f1158338
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/RangeInferable.java
@@ -0,0 +1,72 @@
+// 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.trees.expressions.functions;
+
+import org.apache.doris.common.Pair;
+import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext;
+import org.apache.doris.nereids.rules.expression.rules.ColumnRange;
+import org.apache.doris.nereids.rules.expression.rules.FoldConstantRuleOnFE;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.literal.Literal;
+import org.apache.doris.nereids.trees.expressions.literal.NullLiteral;
+
+import java.util.Optional;
+
+/** Infer an enclosing result range without requiring the expression to be
monotonic. */
+public interface RangeInferable extends ExpressionTrait {
+ int getRangeInputChildIndex();
+
+ // Replace the range input with a constant while preserving the other
arguments.
+ Expression withConstantArgs(Expression literal);
+
+ /**
+ * Return a range containing every possible result, or empty when no safe
range can be inferred.
+ * A null lower or upper endpoint means that the input is unbounded on
that side.
+ */
+ Optional<ColumnRange> inferRange(Literal lower, Literal upper,
ExpressionRewriteContext context);
+
+ /** Fold the input endpoints, retaining null for an unbounded side, and
reject SQL NULL or unfolded results. */
+ default Optional<Pair<Literal, Literal>> evaluateRangeEndpoints(
+ Literal lower, Literal upper, ExpressionRewriteContext context) {
+ Expression lowerValue = lower == null ? null
+ : FoldConstantRuleOnFE.evaluate(withConstantArgs(lower),
context);
+ Expression upperValue = upper == null ? null
+ : FoldConstantRuleOnFE.evaluate(withConstantArgs(upper),
context);
+ if (lowerValue instanceof NullLiteral || upperValue instanceof
NullLiteral
+ || lowerValue != null && !(lowerValue instanceof Literal)
+ || upperValue != null && !(upperValue instanceof Literal)) {
+ return Optional.empty();
+ }
+ return Optional.of(Pair.of((Literal) lowerValue, (Literal)
upperValue));
+ }
+
+ /** Build inclusive result bounds. Input endpoint exclusions need not hold
after applying a function. */
+ static Optional<ColumnRange> createRange(Literal lower, Literal upper) {
+ ColumnRange range = ColumnRange.all();
+ if (lower != null) {
+ range = range.withLowerBound(lower);
+ }
+ if (upper != null) {
+ range = range.withUpperBound(upper);
+ }
+ if (range.isEmptyRange() || !range.span().hasLowerBound() &&
!range.span().hasUpperBound()) {
+ return Optional.empty();
+ }
+ return Optional.of(range);
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
index 051a191d5fd..bf60ad786cb 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
@@ -1652,82 +1652,99 @@ public class DateTimeExtractAndTransform {
@ExecFunction(name = "months_diff")
public static Expression monthsDiff(DateTimeV2Literal t1,
DateTimeV2Literal t2) {
- return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.MONTHS));
}
@ExecFunction(name = "months_diff")
public static Expression monthsDiff(TimestampTzLiteral t1,
TimestampTzLiteral t2) {
- return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.MONTHS));
}
@ExecFunction(name = "months_diff")
public static Expression monthsDiff(TimeStampNsLiteral t1,
TimeStampNsLiteral t2) {
- return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.MONTHS));
}
@ExecFunction(name = "months_diff")
public static Expression monthsDiff(DateTimeV2Literal t1, DateV2Literal
t2) {
- return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.MONTHS));
}
@ExecFunction(name = "months_diff")
public static Expression monthsDiff(DateV2Literal t1, DateTimeV2Literal
t2) {
- return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.MONTHS));
}
@ExecFunction(name = "months_diff")
public static Expression monthsDiff(DateV2Literal t1, DateV2Literal t2) {
- return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.MONTHS));
}
@ExecFunction(name = "quarters_diff")
public static Expression quartersDiff(DateTimeV2Literal t1,
DateTimeV2Literal t2) {
- return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()) / 3);
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.MONTHS) / 3);
}
@ExecFunction(name = "quarters_diff")
public static Expression quartersDiff(TimestampTzLiteral t1,
TimestampTzLiteral t2) {
- return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()) / 3);
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.MONTHS) / 3);
}
@ExecFunction(name = "quarters_diff")
public static Expression quartersDiff(TimeStampNsLiteral t1,
TimeStampNsLiteral t2) {
- return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()) / 3);
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.MONTHS) / 3);
}
@ExecFunction(name = "quarters_diff")
public static Expression quartersDiff(DateV2Literal t1, DateV2Literal t2) {
- return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()) / 3);
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.MONTHS) / 3);
}
@ExecFunction(name = "years_diff")
public static Expression yearsDiff(DateTimeV2Literal t1, DateTimeV2Literal
t2) {
- return new BigIntLiteral(ChronoUnit.YEARS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.YEARS));
}
@ExecFunction(name = "years_diff")
public static Expression yearsDiff(TimestampTzLiteral t1,
TimestampTzLiteral t2) {
- return new BigIntLiteral(ChronoUnit.YEARS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.YEARS));
}
@ExecFunction(name = "years_diff")
public static Expression yearsDiff(TimeStampNsLiteral t1,
TimeStampNsLiteral t2) {
- return new BigIntLiteral(ChronoUnit.YEARS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.YEARS));
}
@ExecFunction(name = "years_diff")
public static Expression yearsDiff(DateTimeV2Literal t1, DateV2Literal t2)
{
- return new BigIntLiteral(ChronoUnit.YEARS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.YEARS));
}
@ExecFunction(name = "years_diff")
public static Expression yearsDiff(DateV2Literal t1, DateTimeV2Literal t2)
{
- return new BigIntLiteral(ChronoUnit.YEARS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.YEARS));
}
@ExecFunction(name = "years_diff")
public static Expression yearsDiff(DateV2Literal t1, DateV2Literal t2) {
- return new BigIntLiteral(ChronoUnit.YEARS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new BigIntLiteral(dateDiffInCalendarUnits(t1, t2,
ChronoUnit.YEARS));
+ }
+
+ private static long dateDiffInCalendarUnits(DateLiteral t1, DateLiteral
t2, ChronoUnit unit) {
+ // LocalDateTime.until adjusts the end date by one day before counting
months or years.
+ // At a month end that can remove an extra calendar unit, unlike BE's
remainder comparison.
+ long diff = unit.between(t2.toJavaDateType().toLocalDate(),
t1.toJavaDateType().toLocalDate());
+ boolean sameRemainderDate = t1.getDay() == t2.getDay()
+ && (unit == ChronoUnit.MONTHS || t1.getMonth() ==
t2.getMonth());
+ if (sameRemainderDate) {
+ long timeDiff = t1.getTimePartInNanoseconds() -
t2.getTimePartInNanoseconds();
+ if (diff > 0 && timeDiff < 0) {
+ diff--;
+ } else if (diff < 0 && timeDiff > 0) {
+ diff++;
+ }
+ }
+ return diff;
}
/**
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MonthsAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MonthsAdd.java
index e9f44840d78..000e8db3b34 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MonthsAdd.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MonthsAdd.java
@@ -19,8 +19,8 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
+import
org.apache.doris.nereids.trees.expressions.functions.CalendarAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ComputeSignatureForDateArithmetic;
-import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
@@ -40,7 +40,7 @@ import java.util.List;
* ScalarFunction 'months_add'.
*/
public class MonthsAdd extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+ ComputeSignatureForDateArithmetic, PropagateNullable,
CalendarAddSubMonotonic {
//ATTN: must place Datetime before Date, because for castring from string
like literal, date and datetime has
// the same precedence, but we prefer datetime to avoid data loss. for
string literal which could cast to Date
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MonthsSub.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MonthsSub.java
index da58e6270a6..67b15a3e5c3 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MonthsSub.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MonthsSub.java
@@ -19,8 +19,8 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
+import
org.apache.doris.nereids.trees.expressions.functions.CalendarAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ComputeSignatureForDateArithmetic;
-import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
@@ -40,7 +40,7 @@ import java.util.List;
* ScalarFunction 'months_sub'.
*/
public class MonthsSub extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+ ComputeSignatureForDateArithmetic, PropagateNullable,
CalendarAddSubMonotonic {
//ATTN: must place Datetime before Date, because for castring from string
like literal, date and datetime has
// the same precedence, but we prefer datetime to avoid data loss. for
string literal which could cast to Date
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/QuartersAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/QuartersAdd.java
index 7e9865c4a5d..ec056f1779a 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/QuartersAdd.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/QuartersAdd.java
@@ -19,8 +19,8 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
+import
org.apache.doris.nereids.trees.expressions.functions.CalendarAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ComputeSignatureForDateArithmetic;
-import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
@@ -40,7 +40,7 @@ import java.util.List;
* ScalarFunction 'quarters_add'.
*/
public class QuartersAdd extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+ ComputeSignatureForDateArithmetic, PropagateNullable,
CalendarAddSubMonotonic {
//ATTN: must place Datetime before Date, because for castring from string
like literal, date and datetime has
// the same precedence, but we prefer datetime to avoid data loss. for
string literal which could cast to Date
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/QuartersSub.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/QuartersSub.java
index d56bab92048..13d81f08b89 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/QuartersSub.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/QuartersSub.java
@@ -19,8 +19,8 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
+import
org.apache.doris.nereids.trees.expressions.functions.CalendarAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ComputeSignatureForDateArithmetic;
-import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
@@ -40,7 +40,7 @@ import java.util.List;
* ScalarFunction 'quarters_sub'.
*/
public class QuartersSub extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+ ComputeSignatureForDateArithmetic, PropagateNullable,
CalendarAddSubMonotonic {
//ATTN: must place Datetime before Date, because for castring from string
like literal, date and datetime has
// the same precedence, but we prefer datetime to avoid data loss. for
string literal which could cast to Date
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearMonthAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearMonthAdd.java
index 49f52d7a6cc..0f42e3a8b8e 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearMonthAdd.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearMonthAdd.java
@@ -19,8 +19,8 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
+import
org.apache.doris.nereids.trees.expressions.functions.CalendarAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ComputeSignatureForDateArithmetic;
-import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
@@ -40,7 +40,7 @@ import java.util.List;
*/
public class YearMonthAdd extends ScalarFunction
implements BinaryExpression, ExplicitlyCastableSignature,
- ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+ ComputeSignatureForDateArithmetic, PropagateNullable,
CalendarAddSubMonotonic {
public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
FunctionSignature.ret(DateTimeV2Type.WILDCARD)
.args(DateTimeV2Type.WILDCARD, VarcharType.SYSTEM_DEFAULT),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearMonthSub.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearMonthSub.java
index 2dd05536cf3..14233b1f408 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearMonthSub.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearMonthSub.java
@@ -19,8 +19,8 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
+import
org.apache.doris.nereids.trees.expressions.functions.CalendarAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ComputeSignatureForDateArithmetic;
-import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
@@ -40,7 +40,7 @@ import java.util.List;
*/
public class YearMonthSub extends ScalarFunction
implements BinaryExpression, ExplicitlyCastableSignature,
- ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+ ComputeSignatureForDateArithmetic, PropagateNullable,
CalendarAddSubMonotonic {
public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
FunctionSignature.ret(DateTimeV2Type.WILDCARD)
.args(DateTimeV2Type.WILDCARD, VarcharType.SYSTEM_DEFAULT),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsAdd.java
index d09e3d4c517..c675b09e9f0 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsAdd.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsAdd.java
@@ -19,8 +19,8 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
+import
org.apache.doris.nereids.trees.expressions.functions.CalendarAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ComputeSignatureForDateArithmetic;
-import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
@@ -40,7 +40,7 @@ import java.util.List;
* ScalarFunction 'years_add'.
*/
public class YearsAdd extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+ ComputeSignatureForDateArithmetic, PropagateNullable,
CalendarAddSubMonotonic {
//ATTN: must place Datetime before Date, because for castring from string
like literal, date and datetime has
// the same precedence, but we prefer datetime to avoid data loss. for
string literal which could cast to Date
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsSub.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsSub.java
index cfd373ba70a..1f346f990bd 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsSub.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsSub.java
@@ -19,8 +19,8 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
+import
org.apache.doris.nereids.trees.expressions.functions.CalendarAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ComputeSignatureForDateArithmetic;
-import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
@@ -40,7 +40,7 @@ import java.util.List;
* ScalarFunction 'days_add'.
*/
public class YearsSub extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+ ComputeSignatureForDateArithmetic, PropagateNullable,
CalendarAddSubMonotonic {
//ATTN: must place Datetime before Date, because for castring from string
like literal, date and datetime has
// the same precedence, but we prefer datetime to avoid data loss. for
string literal which could cast to Date
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/PartitionPrunerTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/PartitionPrunerTest.java
index f26c4701ce7..8b3c0bd408c 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/PartitionPrunerTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/PartitionPrunerTest.java
@@ -27,6 +27,8 @@ import org.apache.doris.catalog.RangePartitionItem;
import org.apache.doris.common.AnalysisException;
import org.apache.doris.common.Pair;
import org.apache.doris.nereids.CascadesContext;
+import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext;
+import org.apache.doris.nereids.rules.expression.rules.ColumnRange;
import
org.apache.doris.nereids.rules.expression.rules.OneListPartitionEvaluator;
import org.apache.doris.nereids.rules.expression.rules.OnePartitionEvaluator;
import
org.apache.doris.nereids.rules.expression.rules.OneRangePartitionEvaluator;
@@ -45,8 +47,16 @@ 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.functions.RangeInferable;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.Abs;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.MonthsAdd;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.YearsAdd;
+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.Literal;
import org.apache.doris.nereids.trees.expressions.literal.NullLiteral;
+import org.apache.doris.nereids.types.DateTimeV2Type;
import org.apache.doris.nereids.types.IntegerType;
import org.apache.doris.nereids.types.VarcharType;
import org.apache.doris.nereids.util.ExpressionUtils;
@@ -83,6 +93,85 @@ public class PartitionPrunerTest extends TestWithFeService {
cascadesContext = createCascadesContext("select * from t1");
}
+ @Test
+ public void testCalendarRangeBoundsRetainSafePartitionPruning() throws
Exception {
+ SlotReference date = new SlotReference("dt",
DateTimeV2Type.SYSTEM_DEFAULT);
+ Expression month = new MonthsAdd(date, new IntegerLiteral(1));
+ Expression year = new YearsAdd(date, new IntegerLiteral(1));
+ assertCalendarRangePruned(date, "2023-01-28 10:00:00", "2023-01-31
23:59:59",
+ new LessThan(month, new DateTimeV2Literal("2023-02-28
10:00:00")), false);
+ assertCalendarRangePruned(date, "2023-01-27 00:00:00", "2023-01-31
00:00:00",
+ new GreaterThan(month, new DateTimeV2Literal("2023-02-28
12:00:00")), false);
+ assertCalendarRangePruned(date, "2017-01-01 00:00:00", "2018-01-01
00:00:00",
+ new GreaterThan(year, new DateTimeV2Literal("2019-01-01
00:00:00")), true);
+ assertCalendarRangePruned(date, "2018-01-01 00:00:00", "2019-01-01
00:00:00",
+ new GreaterThan(year, new DateTimeV2Literal("2019-01-01
00:00:00")), false);
+ assertCalendarRangePruned(date, "2023-01-01 00:00:00", "2023-02-01
00:00:00",
+ new EqualTo(month, new DateTimeV2Literal("2023-11-15
10:00:00")), true);
+ assertCalendarRangePruned(date, "2023-10-01 00:00:00", "2023-11-01
00:00:00",
+ new EqualTo(month, new DateTimeV2Literal("2023-11-15
10:00:00")), false);
+ }
+
+ private void assertCalendarRangePruned(SlotReference date, String lower,
String upper,
+ Expression predicate, boolean expectedPruned) throws Exception {
+ List<Column> columns = ImmutableList.of(new Column("dt",
PrimitiveType.DATETIMEV2));
+ PartitionKey lowerKey =
PartitionKey.createPartitionKey(ImmutableList.of(new PartitionValue(lower)),
columns);
+ PartitionKey upperKey =
PartitionKey.createPartitionKey(ImmutableList.of(new PartitionValue(upper)),
columns);
+ OneRangePartitionEvaluator<String> evaluator = new
OneRangePartitionEvaluator<>(
+ "p1", ImmutableList.of(date), new
RangePartitionItem(Range.closedOpen(lowerKey, upperKey)),
+ cascadesContext, 0);
+ Pair<Boolean, Boolean> result =
+ (Pair<Boolean, Boolean>) canBePrunedOutMethod.invoke(null,
predicate, evaluator);
+ Assertions.assertEquals(expectedPruned, result.first,
predicate.toSql());
+ }
+
+ @Test
+ public void testRangeInferenceWithoutMonotonicityInterface() throws
Exception {
+ PartitionKey lower =
PartitionKey.createPartitionKey(ImmutableList.of(new PartitionValue("-5")),
+ ImmutableList.of(partitionColumn));
+ PartitionKey upper =
PartitionKey.createPartitionKey(ImmutableList.of(new PartitionValue("11")),
+ ImmutableList.of(partitionColumn));
+ Expression absolute = new RangeOnlyAbs(slotA);
+ for (int value : new int[] {5, 12}) {
+ OneRangePartitionEvaluator<String> evaluator = new
OneRangePartitionEvaluator<>(
+ "p1", ImmutableList.of(slotA), new
RangePartitionItem(Range.closedOpen(lower, upper)),
+ cascadesContext, 0);
+ Expression predicate = new EqualTo(absolute, new
BigIntLiteral(value));
+ Pair<Boolean, Boolean> result =
+ (Pair<Boolean, Boolean>) canBePrunedOutMethod.invoke(null,
predicate, evaluator);
+ Assertions.assertEquals(value == 12, result.first);
+ }
+ }
+
+ // Test a range capability that does not implement Monotonic and crosses
ABS's turning point.
+ private static class RangeOnlyAbs extends Abs implements RangeInferable {
+ RangeOnlyAbs(Expression child) {
+ super(child);
+ }
+
+ @Override
+ public int getRangeInputChildIndex() {
+ return 0;
+ }
+
+ @Override
+ public Expression withConstantArgs(Expression literal) {
+ return new Abs(literal);
+ }
+
+ @Override
+ public RangeOnlyAbs withChildren(List<Expression> children) {
+ return new RangeOnlyAbs(children.get(0));
+ }
+
+ @Override
+ public Optional<ColumnRange> inferRange(Literal lower, Literal upper,
ExpressionRewriteContext context) {
+ long max = Math.max(Math.abs(((IntegerLiteral)
lower).getIntValue()),
+ Math.abs(((IntegerLiteral) upper).getIntValue()));
+ return Optional.of(ColumnRange.between(new BigIntLiteral(0), new
BigIntLiteral(max)));
+ }
+ }
+
// test canBePrunedOut res
// list partition p1, partition value is 1
// predicate: a = 1
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/RangeInferableTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/RangeInferableTest.java
new file mode 100644
index 00000000000..a1848652946
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/RangeInferableTest.java
@@ -0,0 +1,112 @@
+// 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.trees.expressions.functions;
+
+import org.apache.doris.nereids.CascadesContext;
+import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext;
+import org.apache.doris.nereids.rules.expression.rules.ColumnRange;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.DaysAdd;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.MonthsAdd;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.MonthsDiff;
+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.NullLiteral;
+import org.apache.doris.nereids.types.DateTimeV2Type;
+import org.apache.doris.nereids.types.IntegerType;
+import org.apache.doris.qe.ConnectContext;
+
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+import java.util.Optional;
+
+class RangeInferableTest {
+ private final SlotReference date = new SlotReference("dt",
DateTimeV2Type.SYSTEM_DEFAULT);
+ private ConnectContext previousContext;
+ private ExpressionRewriteContext context;
+
+ @BeforeEach
+ void setUp() {
+ previousContext = ConnectContext.get();
+ new ConnectContext().setThreadLocalInfo();
+ context = new
ExpressionRewriteContext(CascadesContext.initTempContext());
+ }
+
+ @AfterEach
+ void tearDown() {
+ ConnectContext.remove();
+ if (previousContext != null) {
+ previousContext.setThreadLocalInfo();
+ }
+ }
+
+ @Test
+ void testIncreasingAndSingletonRanges() {
+ DaysAdd function = new DaysAdd(date, new IntegerLiteral(2));
+ DateTimeV2Literal lower = new DateTimeV2Literal("2023-01-01 10:00:00");
+ DateTimeV2Literal upper = new DateTimeV2Literal("2023-01-03 11:00:00");
+ DateTimeV2Literal outputLower = new DateTimeV2Literal("2023-01-03
10:00:00");
+ DateTimeV2Literal outputUpper = new DateTimeV2Literal("2023-01-05
11:00:00");
+ assertRange(ColumnRange.between(outputLower, outputUpper),
function.inferRange(lower, upper, context));
+ assertRange(ColumnRange.singleton(outputLower),
function.inferRange(lower, lower, context));
+ assertRange(ColumnRange.atLeast(outputLower),
function.inferRange(lower, null, context));
+ assertRange(ColumnRange.atMost(outputUpper), function.inferRange(null,
upper, context));
+ Assertions.assertFalse(function.inferRange(null, null,
context).isPresent());
+ }
+
+ @Test
+ void testDecreasingRangesSwapBoundedAndUnboundedSides() {
+ MonthsDiff function = new MonthsDiff(new DateTimeV2Literal("2023-04-01
00:00:00"), date);
+ DateTimeV2Literal lower = new DateTimeV2Literal("2023-01-01 00:00:00");
+ DateTimeV2Literal upper = new DateTimeV2Literal("2023-03-01 00:00:00");
+ Assertions.assertEquals(1, function.getRangeInputChildIndex());
+ Assertions.assertFalse(function.isPositive());
+ assertRange(ColumnRange.between(new BigIntLiteral(1), new
BigIntLiteral(3)),
+ function.inferRange(lower, upper, context));
+ assertRange(ColumnRange.atMost(new BigIntLiteral(3)),
function.inferRange(lower, null, context));
+ assertRange(ColumnRange.atLeast(new BigIntLiteral(1)),
function.inferRange(null, upper, context));
+ }
+
+ @Test
+ void testUnknownIntervalsAndInvalidEndpointResultsHaveNoRange() {
+ DateTimeV2Literal lower = new DateTimeV2Literal("2023-01-01 00:00:00");
+ DateTimeV2Literal upper = new DateTimeV2Literal("2023-01-02 00:00:00");
+ DaysAdd variable = new DaysAdd(date, new SlotReference("days",
IntegerType.INSTANCE));
+ Assertions.assertFalse(variable.inferRange(lower, upper,
context).isPresent());
+ MonthsAdd calendarVariable = new MonthsAdd(date, new
SlotReference("months", IntegerType.INSTANCE));
+ Assertions.assertFalse(calendarVariable.inferRange(lower, upper,
context).isPresent());
+
+ DaysAdd nullInterval = new DaysAdd(date, new
NullLiteral(IntegerType.INSTANCE));
+ Assertions.assertFalse(nullInterval.inferRange(lower, upper,
context).isPresent());
+
+ DaysAdd overflow = new DaysAdd(date, new IntegerLiteral(1));
+ DateTimeV2Literal lastDay = new DateTimeV2Literal("9999-12-31
00:00:00");
+ Assertions.assertFalse(overflow.inferRange(lastDay, lastDay,
context).isPresent());
+ MonthsAdd calendarOverflow = new MonthsAdd(date, new
IntegerLiteral(1));
+ Assertions.assertFalse(calendarOverflow.inferRange(lower, lastDay,
context).isPresent());
+ }
+
+ private void assertRange(ColumnRange expected, Optional<ColumnRange>
actual) {
+ Assertions.assertTrue(actual.isPresent());
+ Assertions.assertEquals(expected.rangeSet, actual.get().rangeSet);
+ }
+}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransformTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransformTest.java
index 239f13c5578..29cb9774566 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransformTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransformTest.java
@@ -28,6 +28,7 @@ import
org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
import org.apache.doris.nereids.trees.expressions.literal.SmallIntLiteral;
import org.apache.doris.nereids.trees.expressions.literal.StringLiteral;
import org.apache.doris.nereids.trees.expressions.literal.TimeStampNsLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.TimestampTzLiteral;
import org.apache.doris.nereids.trees.expressions.literal.TinyIntLiteral;
import org.apache.doris.nereids.trees.expressions.literal.VarcharLiteral;
import org.apache.doris.nereids.types.DateTimeV2Type;
@@ -40,6 +41,48 @@ import java.math.BigDecimal;
import java.time.LocalDateTime;
class DateTimeExtractAndTransformTest {
+ @Test
+ void testCalendarDiffAtMonthEnd() {
+ assertCalendarDiff("2023-03-01 11:00:00", "2023-01-31 12:00:00", 1, 0,
0);
+ assertCalendarDiff("2023-03-01 11:00:00", "2022-11-30 12:00:00", 3, 1,
0);
+ assertCalendarDiff("2021-03-01 11:00:00", "2020-02-29 12:00:00", 12,
4, 1);
+ assertCalendarDiff("2023-02-28 11:00:00", "2023-01-31 12:00:00", 0, 0,
0);
+ assertCalendarDiff("2023-03-31 11:00:00", "2023-01-31 12:00:00", 1, 0,
0);
+ assertCalendarDiff("2021-02-28 11:00:00", "2020-02-29 12:00:00", 11,
3, 0);
+ assertCalendarDiff("2021-03-01 11:00:00", "2020-03-01 12:00:00", 11,
3, 0);
+ assertCalendarDiff("2021-03-01 12:00:00", "2020-03-01 12:00:00", 12,
4, 1);
+ Assertions.assertEquals(new BigIntLiteral(1),
DateTimeExtractAndTransform.monthsDiff(
+ new DateV2Literal("2023-03-01"), new
DateTimeV2Literal("2023-01-31 12:00:00")));
+ Assertions.assertEquals(new BigIntLiteral(-1),
DateTimeExtractAndTransform.monthsDiff(
+ new DateTimeV2Literal("2023-01-31 12:00:00"), new
DateV2Literal("2023-03-01")));
+ }
+
+ private void assertCalendarDiff(String end, String start, long months,
long quarters, long years) {
+ // Check both argument positions, including the negative differences
used by pruning.
+ for (int sign : new int[] {1, -1}) {
+ String left = sign == 1 ? end : start;
+ String right = sign == 1 ? start : end;
+ DateTimeV2Literal dt1 = new DateTimeV2Literal(left);
+ DateTimeV2Literal dt2 = new DateTimeV2Literal(right);
+ TimeStampNsLiteral ns1 = new TimeStampNsLiteral(left);
+ TimeStampNsLiteral ns2 = new TimeStampNsLiteral(right);
+ TimestampTzLiteral tz1 = new TimestampTzLiteral(left);
+ TimestampTzLiteral tz2 = new TimestampTzLiteral(right);
+ BigIntLiteral expectedMonths = new BigIntLiteral(sign * months);
+ BigIntLiteral expectedQuarters = new BigIntLiteral(sign *
quarters);
+ BigIntLiteral expectedYears = new BigIntLiteral(sign * years);
+ Assertions.assertEquals(expectedMonths,
DateTimeExtractAndTransform.monthsDiff(dt1, dt2));
+ Assertions.assertEquals(expectedMonths,
DateTimeExtractAndTransform.monthsDiff(ns1, ns2));
+ Assertions.assertEquals(expectedMonths,
DateTimeExtractAndTransform.monthsDiff(tz1, tz2));
+ Assertions.assertEquals(expectedQuarters,
DateTimeExtractAndTransform.quartersDiff(dt1, dt2));
+ Assertions.assertEquals(expectedQuarters,
DateTimeExtractAndTransform.quartersDiff(ns1, ns2));
+ Assertions.assertEquals(expectedQuarters,
DateTimeExtractAndTransform.quartersDiff(tz1, tz2));
+ Assertions.assertEquals(expectedYears,
DateTimeExtractAndTransform.yearsDiff(dt1, dt2));
+ Assertions.assertEquals(expectedYears,
DateTimeExtractAndTransform.yearsDiff(ns1, ns2));
+ Assertions.assertEquals(expectedYears,
DateTimeExtractAndTransform.yearsDiff(tz1, tz2));
+ }
+ }
+
@Test
void testAdditionalTimestampNsCalendarFunctions() {
TimeStampNsLiteral leapDay = new TimeStampNsLiteral("2024-02-29
12:34:56.123456789");
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/scalar/CalendarAddSubMonotonicTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/scalar/CalendarAddSubMonotonicTest.java
new file mode 100644
index 00000000000..337084ea693
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/scalar/CalendarAddSubMonotonicTest.java
@@ -0,0 +1,235 @@
+// 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.trees.expressions.functions.scalar;
+
+import org.apache.doris.nereids.CascadesContext;
+import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext;
+import org.apache.doris.nereids.rules.expression.rules.ColumnRange;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import
org.apache.doris.nereids.trees.expressions.functions.CalendarAddSubMonotonic;
+import org.apache.doris.nereids.trees.expressions.literal.DateLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.DateTimeV2Literal;
+import org.apache.doris.nereids.trees.expressions.literal.DateV2Literal;
+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.StringLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.TimeStampNsLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.TimestampTzLiteral;
+import org.apache.doris.nereids.types.DataType;
+import org.apache.doris.nereids.types.DateTimeV2Type;
+import org.apache.doris.nereids.types.DateV2Type;
+import org.apache.doris.nereids.types.IntegerType;
+import org.apache.doris.nereids.types.TimeStampNsType;
+import org.apache.doris.nereids.types.TimeStampTzType;
+import org.apache.doris.qe.ConnectContext;
+
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+import java.time.LocalDateTime;
+import java.time.LocalTime;
+import java.util.Arrays;
+import java.util.List;
+
+class CalendarAddSubMonotonicTest {
+ private ConnectContext previousContext;
+ private ExpressionRewriteContext context;
+
+ @BeforeEach
+ void setUp() {
+ previousContext = ConnectContext.get();
+ ConnectContext connectContext = new ConnectContext();
+ connectContext.getSessionVariable().setTimeZone("+00:00");
+ connectContext.setThreadLocalInfo();
+ context = new
ExpressionRewriteContext(CascadesContext.initTempContext());
+ }
+
+ @AfterEach
+ void tearDown() {
+ ConnectContext.remove();
+ if (previousContext != null) {
+ previousContext.setThreadLocalInfo();
+ }
+ }
+
+ @Test
+ void testClampingCanResetTimeAcrossDays() {
+ List<DataType> types = Arrays.asList(DateTimeV2Type.SYSTEM_DEFAULT,
+ TimeStampNsType.INSTANCE, TimeStampTzType.SYSTEM_DEFAULT);
+ List<Literal> lowerBounds = Arrays.asList(new
DateTimeV2Literal("2023-01-28 10:00:00"),
+ new TimeStampNsLiteral("2023-01-28 10:00:00"), new
TimestampTzLiteral("2023-01-28 10:00:00"));
+ List<Literal> upperBounds = Arrays.asList(new
DateTimeV2Literal("2023-01-31 23:59:59"),
+ new TimeStampNsLiteral("2023-01-31 23:59:59"), new
TimestampTzLiteral("2023-01-31 23:59:59"));
+ for (int i = 0; i < types.size(); i++) {
+ for (CalendarAddSubMonotonic function : calendarFunctions(new
SlotReference("dt", types.get(i)))) {
+ Literal lower = lowerBounds.get(i);
+ Assertions.assertFalse(function.isMonotonic(lower,
upperBounds.get(i)));
+ Assertions.assertFalse(function.isMonotonic(lower, null));
+ Assertions.assertFalse(function.isMonotonic(null,
upperBounds.get(i)));
+ Assertions.assertTrue(function.isMonotonic(lower, lower));
+ }
+ }
+ }
+
+ @Test
+ void testDateOnlyAndSingleDayRemainMonotonic() {
+ for (CalendarAddSubMonotonic function : calendarFunctions(new
SlotReference("d", DateV2Type.INSTANCE))) {
+ Assertions.assertTrue(function.isMonotonic(
+ new DateV2Literal("2023-01-28"), new
DateV2Literal("2023-01-31")));
+ Assertions.assertTrue(function.isMonotonic(null, null));
+ }
+ for (CalendarAddSubMonotonic function : calendarFunctions(
+ new SlotReference("dt", DateTimeV2Type.SYSTEM_DEFAULT))) {
+ Assertions.assertTrue(function.isMonotonic(new
DateTimeV2Literal("2023-01-29 00:00:00"),
+ new DateTimeV2Literal("2023-01-29 23:59:59")));
+ }
+ }
+
+ @Test
+ void testVariableIntervalsAreNotMonotonic() {
+ SlotReference date = new SlotReference("dt",
DateTimeV2Type.SYSTEM_DEFAULT);
+ Assertions.assertFalse(new MonthsAdd(date, new SlotReference("months",
IntegerType.INSTANCE)).isMonotonic(
+ new DateTimeV2Literal("2023-01-29 00:00:00"), new
DateTimeV2Literal("2023-01-29 23:59:59")));
+ }
+
+ @Test
+ void testClampedRangeBoundsPreserveTypesAndPrecision() {
+ List<DateLiteral> inputs = calendarLiterals("2023-01-28
10:00:00.123456");
+ List<DateLiteral> outputs = calendarLiterals("2023-02-28
10:00:00.123456");
+ List<DateLiteral> clampedInputs = calendarLiterals("2023-01-31
10:00:00.123456");
+ for (int i = 0; i < inputs.size(); i++) {
+ CalendarAddSubMonotonic function = new MonthsAdd(
+ new SlotReference("dt", inputs.get(i).getDataType()), new
IntegerLiteral(1));
+ DateLiteral output = outputs.get(i);
+ Literal lower = function.getRangeLowerBound(inputs.get(i), output);
+ Literal upper = function.getRangeUpperBound(clampedInputs.get(i),
output);
+ Assertions.assertEquals(output.getDataType(), lower.getDataType());
+ Assertions.assertEquals(output.getDataType(), upper.getDataType());
+
Assertions.assertEquals(output.toJavaDateType().with(LocalTime.MIN),
+ ((DateLiteral) lower).toJavaDateType());
+ int maxNano = output instanceof TimeStampNsLiteral ? 999999999 :
999999000;
+
Assertions.assertEquals(output.toJavaDateType().with(LocalTime.of(23, 59, 59,
maxNano)),
+ ((DateLiteral) upper).toJavaDateType());
+ // The last source day has no later day that can reset its lower
bound's clock.
+ Assertions.assertSame(output,
function.getRangeLowerBound(clampedInputs.get(i), output));
+ // The first day mapping to the last destination day has no
earlier collapsed day.
+ Assertions.assertSame(output,
function.getRangeUpperBound(inputs.get(i), output));
+ }
+ }
+
+ @Test
+ void testOrdinaryMonthBoundariesKeepExactBounds() {
+ List<DateLiteral> inputs = calendarLiterals("2023-01-01
10:00:00.123456");
+ List<DateLiteral> outputs = calendarLiterals("2023-02-01
10:00:00.123456");
+ for (int i = 0; i < inputs.size(); i++) {
+ CalendarAddSubMonotonic function = new MonthsAdd(
+ new SlotReference("dt", inputs.get(i).getDataType()), new
IntegerLiteral(1));
+ Assertions.assertTrue(function.canEvaluateRange());
+ Assertions.assertSame(outputs.get(i),
function.getRangeLowerBound(inputs.get(i), outputs.get(i)));
+ Assertions.assertSame(outputs.get(i),
function.getRangeUpperBound(inputs.get(i), outputs.get(i)));
+ }
+ }
+
+ @Test
+ void testRangeInferenceDoesNotImplyMonotonicity() {
+ List<DateLiteral> lowerBounds = calendarLiterals("2023-01-28
10:00:00.123456");
+ List<DateLiteral> upperBounds = calendarLiterals("2023-01-31
00:00:00.123456");
+ for (int i = 0; i < lowerBounds.size(); i++) {
+ DateLiteral lower = lowerBounds.get(i);
+ DateLiteral upper = upperBounds.get(i);
+ CalendarAddSubMonotonic function = new MonthsAdd(
+ new SlotReference("dt", lower.getDataType()), new
IntegerLiteral(1));
+ Assertions.assertFalse(function.isMonotonic(lower, upper));
+ ColumnRange range = function.inferRange(lower, upper,
context).get();
+ Assertions.assertEquals(lower.getDataType(),
range.getLowerBound().getValue().getDataType());
+ Assertions.assertEquals(upper.getDataType(),
range.getUpperBound().getValue().getDataType());
+ Assertions.assertEquals(LocalDateTime.of(2023, 2, 28, 0, 0),
+ ((DateLiteral)
range.getLowerBound().getValue()).toJavaDateType());
+ int maxNano = lower instanceof TimeStampNsLiteral ? 999999999 :
999999000;
+ Assertions.assertEquals(LocalDateTime.of(2023, 2, 28, 23, 59, 59,
maxNano),
+ ((DateLiteral)
range.getUpperBound().getValue()).toJavaDateType());
+ Assertions.assertFalse(function.inferRange(lower, null,
context).get().span().hasUpperBound());
+ Assertions.assertFalse(function.inferRange(null, upper,
context).get().span().hasLowerBound());
+ }
+ }
+
+ @Test
+ void testDateOnlyAndSameDayInferenceKeepsEndpointTimes() {
+ CalendarAddSubMonotonic dateOnly = new MonthsAdd(
+ new SlotReference("d", DateV2Type.INSTANCE), new
IntegerLiteral(1));
+ ColumnRange dateRange = dateOnly.inferRange(new
DateV2Literal("2023-01-28"),
+ new DateV2Literal("2023-01-31"), context).get();
+ Assertions.assertTrue(dateRange.isSingleton());
+ Assertions.assertEquals(new DateV2Literal("2023-02-28"),
dateRange.getLowerBound().getValue());
+
+ DateLiteral lower = new DateTimeV2Literal("2023-01-29 10:00:00");
+ DateLiteral upper = new DateTimeV2Literal("2023-01-29 11:00:00");
+ CalendarAddSubMonotonic sameDay = new MonthsAdd(
+ new SlotReference("dt", lower.getDataType()), new
IntegerLiteral(1));
+ ColumnRange range = sameDay.inferRange(lower, upper, context).get();
+ Assertions.assertEquals(new DateTimeV2Literal("2023-02-28 10:00:00"),
range.getLowerBound().getValue());
+ Assertions.assertEquals(new DateTimeV2Literal("2023-02-28 11:00:00"),
range.getUpperBound().getValue());
+ }
+
+ @Test
+ void testRangeEnclosesMonthEndAndLeapDayValues() {
+ String[][] ranges = {
+ {"2023-01-28T10:00:00", "2023-01-31T23:00:00"},
+ {"2023-01-27T00:00:00", "2023-01-31T00:00:00"},
+ {"2023-03-28T10:00:00", "2023-03-31T23:00:00"},
+ {"2024-02-28T10:00:00", "2024-02-29T23:00:00"},
+ {"2023-01-01T00:00:00", "2024-01-01T00:00:00"}
+ };
+ for (String[] range : ranges) {
+ LocalDateTime start = LocalDateTime.parse(range[0]);
+ LocalDateTime end = LocalDateTime.parse(range[1]);
+ for (int months : new int[] {-12, -3, -1, 0, 1, 3, 12}) {
+ for (int type = 0; type < 3; type++) {
+ DateLiteral inputLower =
calendarLiterals(start.toString()).get(type);
+ DateLiteral inputUpper =
calendarLiterals(end.toString()).get(type);
+ CalendarAddSubMonotonic function = new MonthsAdd(
+ new SlotReference("dt", inputLower.getDataType()),
new IntegerLiteral(months));
+ ColumnRange rangeResult = function.inferRange(inputLower,
inputUpper, context).get();
+ LocalDateTime lower = ((DateLiteral)
rangeResult.getLowerBound().getValue()).toJavaDateType();
+ LocalDateTime upper = ((DateLiteral)
rangeResult.getUpperBound().getValue()).toJavaDateType();
+ for (LocalDateTime value = start; !value.isAfter(end);
value = value.plusHours(1)) {
+ LocalDateTime shifted = value.plusMonths(months);
+ Assertions.assertFalse(shifted.isBefore(lower));
+ Assertions.assertFalse(shifted.isAfter(upper));
+ }
+ }
+ }
+ }
+ }
+
+ private List<DateLiteral> calendarLiterals(String value) {
+ return Arrays.asList(new DateTimeV2Literal(DateTimeV2Type.MAX, value),
+ new TimeStampNsLiteral(value), new
TimestampTzLiteral(TimeStampTzType.of(6), value));
+ }
+
+ private List<CalendarAddSubMonotonic> calendarFunctions(SlotReference
date) {
+ IntegerLiteral one = new IntegerLiteral(1);
+ return Arrays.asList(new MonthsAdd(date, one), new MonthsSub(date,
one),
+ new QuartersAdd(date, one), new QuartersSub(date, one),
+ new YearsAdd(date, one), new YearsSub(date, one),
+ new YearMonthAdd(date, new StringLiteral("0-1")),
+ new YearMonthSub(date, new StringLiteral("0-1")));
+ }
+}
diff --git
a/regression-test/data/nereids_rules_p0/partition_prune/test_calendar_arithmetic_prune.out
b/regression-test/data/nereids_rules_p0/partition_prune/test_calendar_arithmetic_prune.out
new file mode 100644
index 00000000000..aa5913791ab
--- /dev/null
+++
b/regression-test/data/nereids_rules_p0/partition_prune/test_calendar_arithmetic_prune.out
@@ -0,0 +1,220 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !0_months_add --
+2023-01-29T00:00 2023-02-28T00:00
+
+-- !0_months_add_equal --
+2023-01-29T00:00
+
+-- !0_months_add_in --
+2023-01-29T00:00
+
+-- !0_months_sub --
+2023-03-29T00:00 2023-02-28T00:00
+
+-- !0_quarters_add --
+2023-05-31T00:00 2024-02-29T00:00
+
+-- !0_quarters_sub --
+2023-05-31T00:00 2023-02-28T00:00
+
+-- !0_years_add --
+2024-02-29T00:00 2025-02-28T00:00
+
+-- !0_years_sub --
+2024-02-29T00:00 2023-02-28T00:00
+
+-- !0_year_month_add --
+2023-01-29T00:00
+
+-- !0_year_month_sub --
+2023-03-29T00:00
+
+-- !0_date_add --
+2023-01-29T00:00
+
+-- !0_date_sub --
+2023-03-29T00:00
+
+-- !0_timestampadd --
+2023-01-29T00:00
+
+-- !0_nested --
+2023-01-29T00:00
+
+-- !0_upper_bound --
+2023-01-29T23:00
+
+-- !0_months_diff --
+2021-03-01T10:00 1
+
+-- !0_months_diff_reverse --
+2021-03-01T10:00 -1
+
+-- !0_months_diff_constant --
+1
+
+-- !0_quarters_diff --
+2021-03-01T10:00 1
+
+-- !0_quarters_diff_reverse --
+2021-03-01T10:00 -1
+
+-- !0_quarters_diff_constant --
+1
+
+-- !0_years_diff --
+2021-03-01T10:00 1
+
+-- !0_years_diff_reverse --
+2021-03-01T10:00 -1
+
+-- !0_years_diff_constant --
+1
+
+-- !1_months_add --
+2023-01-29 00:00:00.000000000 2023-02-28 00:00:00.000000000
+
+-- !1_months_add_equal --
+2023-01-29 00:00:00.000000000
+
+-- !1_months_add_in --
+2023-01-29 00:00:00.000000000
+
+-- !1_months_sub --
+2023-03-29 00:00:00.000000000 2023-02-28 00:00:00.000000000
+
+-- !1_quarters_add --
+2023-05-31 00:00:00.000000000 2024-02-29 00:00:00.000000000
+
+-- !1_quarters_sub --
+2023-05-31 00:00:00.000000000 2023-02-28 00:00:00.000000000
+
+-- !1_years_add --
+2024-02-29 00:00:00.000000000 2025-02-28 00:00:00.000000000
+
+-- !1_years_sub --
+2024-02-29 00:00:00.000000000 2023-02-28 00:00:00.000000000
+
+-- !1_year_month_add --
+2023-01-29 00:00:00.000000000
+
+-- !1_year_month_sub --
+2023-03-29 00:00:00.000000000
+
+-- !1_date_add --
+2023-01-29 00:00:00.000000000
+
+-- !1_date_sub --
+2023-03-29 00:00:00.000000000
+
+-- !1_timestampadd --
+2023-01-29 00:00:00.000000000
+
+-- !1_nested --
+2023-01-29 00:00:00.000000000
+
+-- !1_upper_bound --
+2023-01-29 23:00:00.000000000
+
+-- !1_months_diff --
+2021-03-01 10:00:00.000000000 1
+
+-- !1_months_diff_reverse --
+2021-03-01 10:00:00.000000000 -1
+
+-- !1_months_diff_constant --
+1
+
+-- !1_quarters_diff --
+2021-03-01 10:00:00.000000000 1
+
+-- !1_quarters_diff_reverse --
+2021-03-01 10:00:00.000000000 -1
+
+-- !1_quarters_diff_constant --
+1
+
+-- !1_years_diff --
+2021-03-01 10:00:00.000000000 1
+
+-- !1_years_diff_reverse --
+2021-03-01 10:00:00.000000000 -1
+
+-- !1_years_diff_constant --
+1
+
+-- !2_months_add --
+2023-01-29 00:00:00.000000+00:00 2023-02-28 00:00:00.000000+00:00
+
+-- !2_months_add_equal --
+2023-01-29 00:00:00.000000+00:00
+
+-- !2_months_add_in --
+2023-01-29 00:00:00.000000+00:00
+
+-- !2_months_sub --
+2023-03-29 00:00:00.000000+00:00 2023-02-28 00:00:00.000000+00:00
+
+-- !2_quarters_add --
+2023-05-31 00:00:00.000000+00:00 2024-02-29 00:00:00.000000+00:00
+
+-- !2_quarters_sub --
+2023-05-31 00:00:00.000000+00:00 2023-02-28 00:00:00.000000+00:00
+
+-- !2_years_add --
+2024-02-29 00:00:00.000000+00:00 2025-02-28 00:00:00.000000+00:00
+
+-- !2_years_sub --
+2024-02-29 00:00:00.000000+00:00 2023-02-28 00:00:00.000000+00:00
+
+-- !2_year_month_add --
+2023-01-29 00:00:00.000000+00:00
+
+-- !2_year_month_sub --
+2023-03-29 00:00:00.000000+00:00
+
+-- !2_date_add --
+2023-01-29 00:00:00.000000+00:00
+
+-- !2_date_sub --
+2023-03-29 00:00:00.000000+00:00
+
+-- !2_timestampadd --
+2023-01-29 00:00:00.000000+00:00
+
+-- !2_nested --
+2023-01-29 00:00:00.000000+00:00
+
+-- !2_upper_bound --
+2023-01-29 23:00:00.000000+00:00
+
+-- !2_months_diff --
+2021-03-01 10:00:00.000000+00:00 1
+
+-- !2_months_diff_reverse --
+2021-03-01 10:00:00.000000+00:00 -1
+
+-- !2_months_diff_constant --
+1
+
+-- !2_quarters_diff --
+2021-03-01 10:00:00.000000+00:00 1
+
+-- !2_quarters_diff_reverse --
+2021-03-01 10:00:00.000000+00:00 -1
+
+-- !2_quarters_diff_constant --
+1
+
+-- !2_years_diff --
+2021-03-01 10:00:00.000000+00:00 1
+
+-- !2_years_diff_reverse --
+2021-03-01 10:00:00.000000+00:00 -1
+
+-- !2_years_diff_constant --
+1
+
+-- !date_only --
+2023-01-29
+
diff --git
a/regression-test/suites/nereids_rules_p0/partition_prune/test_calendar_arithmetic_prune.groovy
b/regression-test/suites/nereids_rules_p0/partition_prune/test_calendar_arithmetic_prune.groovy
new file mode 100644
index 00000000000..ca34149485d
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/partition_prune/test_calendar_arithmetic_prune.groovy
@@ -0,0 +1,157 @@
+// 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_calendar_arithmetic_prune") {
+ sql "set time_zone = '+00:00'"
+ // Each type carries a time part that can go backwards when month-end
clamping merges dates.
+ ["datetimev2(6)", "timestamp_ns", "timestamptz(6)"].eachWithIndex {
dateType, typeIndex ->
+ sql "drop table if exists calendar_add_sub_prune"
+ sql """
+ create table calendar_add_sub_prune(d ${dateType})
+ duplicate key(d)
+ partition by range(d) (
+ partition p_month values [('2023-01-28 10:00:00'),
('2023-01-31 23:59:59')),
+ partition p_sub values [('2023-03-28 10:00:00'), ('2023-03-31
23:59:59')),
+ partition p_quarter values [('2023-05-30 10:00:00'),
('2023-05-31 23:59:59')),
+ partition p_leap values [('2024-02-28 10:00:00'), ('2024-02-29
23:59:59'))
+ )
+ distributed by hash(d) buckets 1
+ properties('replication_num' = '1')
+ """
+ sql """insert into calendar_add_sub_prune values
+ ('2023-01-29 00:00:00'), ('2023-03-29 00:00:00'),
+ ('2023-05-31 00:00:00'), ('2024-02-29 00:00:00')"""
+
+ // Cross-day inputs still have useful bounds even when the time part
is not monotonic.
+ explain {
+ sql "select d from calendar_add_sub_prune where months_add(d, 1) >
'2024-03-15 00:00:00'"
+ contains "partitions=1/4 (p_leap)"
+ }
+ explain {
+ sql "select d from calendar_add_sub_prune where years_add(d, 1) >
'2024-02-29 00:00:00'"
+ contains "partitions=3/4 (p_sub,p_quarter,p_leap)"
+ }
+
+ "order_qt_${typeIndex}_months_add" """
+ select d, months_add(d, 1) from calendar_add_sub_prune
+ where months_add(d, 1) < '2023-02-28 10:00:00'
+ """
+ "order_qt_${typeIndex}_months_add_equal" """
+ select d from calendar_add_sub_prune where months_add(d, 1) =
'2023-02-28 00:00:00'
+ """
+ "order_qt_${typeIndex}_months_add_in" """
+ select d from calendar_add_sub_prune
+ where months_add(d, 1) in ('2023-02-28 00:00:00', '2023-02-28
01:00:00')
+ """
+ "order_qt_${typeIndex}_months_sub" """
+ select d, months_sub(d, 1) from calendar_add_sub_prune
+ where d >= '2023-03-01' and d < '2023-04-01'
+ and months_sub(d, 1) < '2023-02-28 10:00:00'
+ """
+ "order_qt_${typeIndex}_quarters_add" """
+ select d, quarters_add(d, 3) from calendar_add_sub_prune
+ where d >= '2023-05-01' and d < '2023-06-01'
+ and quarters_add(d, 3) < '2024-02-29 10:00:00'
+ """
+ "order_qt_${typeIndex}_quarters_sub" """
+ select d, quarters_sub(d, 1) from calendar_add_sub_prune
+ where d >= '2023-05-01' and d < '2023-06-01'
+ and quarters_sub(d, 1) < '2023-02-28 10:00:00'
+ """
+ "order_qt_${typeIndex}_years_add" """
+ select d, years_add(d, 1) from calendar_add_sub_prune
+ where d >= '2024-02-01' and years_add(d, 1) < '2025-02-28 10:00:00'
+ """
+ "order_qt_${typeIndex}_years_sub" """
+ select d, years_sub(d, 1) from calendar_add_sub_prune
+ where d >= '2024-02-01' and years_sub(d, 1) < '2023-02-28 10:00:00'
+ """
+ "order_qt_${typeIndex}_year_month_add" """
+ select d from calendar_add_sub_prune
+ where d + interval '0-1' year_month < '2023-02-28 10:00:00'
+ """
+ "order_qt_${typeIndex}_year_month_sub" """
+ select d from calendar_add_sub_prune where d >= '2023-03-01' and d
< '2023-04-01'
+ and d - interval '0-1' year_month < '2023-02-28 10:00:00'
+ """
+ "order_qt_${typeIndex}_date_add" """
+ select d from calendar_add_sub_prune where date_add(d, interval 1
month) < '2023-02-28 10:00:00'
+ """
+ "order_qt_${typeIndex}_date_sub" """
+ select d from calendar_add_sub_prune where d >= '2023-03-01' and d
< '2023-04-01'
+ and date_sub(d, interval 1 month) < '2023-02-28 10:00:00'
+ """
+ "order_qt_${typeIndex}_timestampadd" """
+ select d from calendar_add_sub_prune where timestampadd(month, 1,
d) < '2023-02-28 10:00:00'
+ """
+ "order_qt_${typeIndex}_nested" """
+ select d from calendar_add_sub_prune where
date_trunc(months_add(d, 1), 'hour') < '2023-02-28 10:00:00'
+ """
+
+ sql "drop table if exists calendar_add_upper_prune"
+ sql """
+ create table calendar_add_upper_prune(d ${dateType})
+ duplicate key(d)
+ partition by range(d) (partition p1 values [('2023-01-27
00:00:00'), ('2023-01-31 00:00:00')))
+ distributed by hash(d) buckets 1 properties('replication_num' =
'1')
+ """
+ sql "insert into calendar_add_upper_prune values ('2023-01-29
23:00:00')"
+ "order_qt_${typeIndex}_upper_bound" """
+ select d from calendar_add_upper_prune where months_add(d, 1) >
'2023-02-28 12:00:00'
+ """
+
+ // LocalDateTime.until used to fold both partition endpoints to zero
instead of one.
+ sql "drop table if exists calendar_diff_prune"
+ sql """
+ create table calendar_diff_prune(d ${dateType})
+ duplicate key(d)
+ partition by range(d) (partition p1 values [('2021-03-01
00:00:00'), ('2021-03-01 11:00:00')))
+ distributed by hash(d) buckets 1 properties('replication_num' =
'1')
+ """
+ sql "insert into calendar_diff_prune values ('2021-03-01 10:00:00')"
+ ["months": "2021-01-31 12:00:00", "quarters": "2020-11-30 12:00:00",
+ "years": "2020-02-29 12:00:00"].each { unit, origin ->
+ "order_qt_${typeIndex}_${unit}_diff" """
+ select d, ${unit}_diff(d, '${origin}') from calendar_diff_prune
+ where ${unit}_diff(d, '${origin}') = 1
+ """
+ "order_qt_${typeIndex}_${unit}_diff_reverse" """
+ select d, ${unit}_diff('${origin}', d) from calendar_diff_prune
+ where ${unit}_diff('${origin}', d) = -1
+ """
+ "order_qt_${typeIndex}_${unit}_diff_constant" """
+ select ${unit}_diff(cast('2021-03-01 10:00:00' as
${dateType}), cast('${origin}' as ${dateType}))
+ """
+ }
+ }
+
+ // DATE has no time reset and must keep its existing monotonic partition
pruning.
+ sql "drop table if exists calendar_date_prune"
+ sql """
+ create table calendar_date_prune(d datev2) duplicate key(d)
+ partition by range(d) (
+ partition p1 values [('2023-01-28'), ('2023-02-01')),
+ partition p2 values [('2023-03-01'), ('2023-04-01'))
+ ) distributed by hash(d) buckets 1 properties('replication_num' = '1')
+ """
+ sql "insert into calendar_date_prune values ('2023-01-29'), ('2023-03-29')"
+ order_qt_date_only "select d from calendar_date_prune where months_add(d,
1) = '2023-02-28'"
+ explain {
+ sql "select d from calendar_date_prune where months_add(d, 1) =
'2023-02-28'"
+ contains "partitions=1/2 (p1)"
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
