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 e04a785e20a [fix](be) Preserve local time in TIMESTAMPTZ day
arithmetic (#70003)
e04a785e20a is described below
commit e04a785e20a87ef910ec756ff6b4d269298addb6
Author: TengJianPing <[email protected]>
AuthorDate: Tue Nov 17 17:44:37 2026 +0800
[fix](be) Preserve local time in TIMESTAMPTZ day arithmetic (#70003)
### What problem does this PR solve?
Issue Number: None
Related PR: None
Problem Summary:
TIMESTAMPTZ day arithmetic currently changes UTC fields, so adding one
day across a DST transition adds exactly 24 hours and shifts the local
clock. In an America/New_York session, `days_add(CAST('2024-03-09
12:00:00-05:00' AS TIMESTAMPTZ(6)), 1)` produces 13:00 the following day
instead of 12:00.
Apply DAY intervals to the session-local date in BE and FE constant
folding, then resolve the result back to UTC. Use a civil calendar
intermediate consistent with FE, including local February 29 in year 0
when both UTC endpoints are valid. Preserve fractional seconds, the
input offset for repeated local times, existing cctz gap resolution, and
date-range errors. Zero-day intervals remain an identity at valid UTC
boundaries so predicate movement remains safe. This covers
days_add/days_sub and their SQL aliases; hours_add remains elapsed-time
arithmetic.
Local-day arithmetic can reverse UTC ordering at a fall-back and is not
always invertible. Restrict its monotonicity declaration in
variable-offset zones and prevent comparison rearrangement from
replacing a DAY predicate with an invalid inverse operation. For
example, moving the interval in `days_add(ts, 1) < '2024-11-04
01:30:00-05:00'` can incorrectly include the first 01:50 from the
preceding fall-back day.
### Release note
TIMESTAMPTZ day addition and subtraction now preserve session-local
calendar time across daylight-saving transitions. One day may represent
23 or 25 elapsed hours; explicitly adding 24 hours retains elapsed-time
semantics.
### Check List (For Author)
- Test
- [x] Regression test: test_timestamptz_days_add_dst,
test_timestamptz_dst_fold, test_timestamptz_dst_gap,
test_timestamptz_utc_functions
- [x] Unit Test: 20 FE tests (TimestampTzLiteralTest and
SimplifyArithmeticComparisonRuleTest); 11 BE tests (TimeStampTzValueTest
and selected VTimestampFunctionsTest cases)
- [x] Manual test: New York spring transition returns local 12:00 and
82800 seconds; explicit 24 hours returns 13:00
- Validation: ASAN BE build, FE build with Checkstyle, clang-format 16,
build hygiene, and clang-tidy on modified C++ files
- Regression expected results generated by run-regression-test.sh; 10 FE
folding and BE result groups compared
- Behavior changed:
- [x] Yes. TIMESTAMPTZ DAY intervals use the session-local calendar;
DST-sensitive monotonicity and comparison rearrangement are restricted.
- Does this need documentation?
- [x] No. Existing date arithmetic bug fix; release note included.
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label
---
be/src/core/value/timestamptz_value.cpp | 29 ++++
be/src/core/value/timestamptz_value.h | 3 +
.../function_date_or_datetime_computation.h | 122 ++++++++------
be/test/exprs/function/function_time_test.cpp | 64 +++++++
be/test/runtime/timestamptz_value_test.cpp | 99 +++++++++++
.../rules/SimplifyArithmeticComparisonRule.java | 28 +++-
.../functions/DateAddSubRearrangeable.java | 37 +++++
.../expressions/functions/MonotonicityUtils.java | 6 +
.../expressions/functions/scalar/DaysAdd.java | 19 ++-
.../expressions/functions/scalar/DaysSub.java | 19 ++-
.../expressions/functions/scalar/HoursAdd.java | 3 +-
.../expressions/functions/scalar/HoursSub.java | 3 +-
.../expressions/functions/scalar/MinutesAdd.java | 3 +-
.../expressions/functions/scalar/MinutesSub.java | 3 +-
.../expressions/functions/scalar/SecondsAdd.java | 3 +-
.../expressions/functions/scalar/SecondsSub.java | 3 +-
.../expressions/functions/scalar/WeeksAdd.java | 3 +-
.../expressions/functions/scalar/WeeksSub.java | 3 +-
.../expressions/literal/TimestampTzLiteral.java | 19 ++-
.../SimplifyArithmeticComparisonRuleTest.java | 178 ++++++++++++++++++++
.../literal/TimestampTzLiteralTest.java | 131 +++++++++++++++
.../timestamptz/test_timestamptz_days_add_dst.out | 87 ++++++++++
.../expression/test_date_comparison_boundary.out | 184 +++++++++++++++++++++
.../test_timestamptz_day_comparison_errors.out | 13 ++
.../test_timestamptz_days_add_dst.groovy | 149 +++++++++++++++++
.../test_date_comparison_boundary.groovy | 122 ++++++++++++++
.../test_timestamptz_day_comparison_errors.groovy | 87 ++++++++++
27 files changed, 1361 insertions(+), 59 deletions(-)
diff --git a/be/src/core/value/timestamptz_value.cpp
b/be/src/core/value/timestamptz_value.cpp
index 1d849ff0ca4..b660038841c 100644
--- a/be/src/core/value/timestamptz_value.cpp
+++ b/be/src/core/value/timestamptz_value.cpp
@@ -89,6 +89,35 @@ int TimestampTzValue::to_buffer(char* buffer, const
cctz::time_zone& tz, int sca
return len;
}
+bool TimestampTzValue::date_add_days(int64_t days, const cctz::time_zone&
local_time_zone) {
+ // Predicate movement treats a zero interval as an infallible identity,
including
+ // UTC year 0000/9999 whose local display year can be outside [0, 9999].
+ if (days == 0) {
+ return true;
+ }
+ const cctz::civil_second utc_cs(year(), month(), day(), hour(), minute(),
second());
+ const auto local = local_time_zone.lookup(cctz::convert(utc_cs,
cctz::utc_time_zone()));
+ const auto target = cctz::civil_day(local.cs) + days;
+ // UTC boundaries and a day delta can produce an unrepresentable local
year.
+ if (local.cs.year() < 0 || local.cs.year() > 9999 || target.year() < 0 ||
+ target.year() > 9999) {
+ return false;
+ }
+ // Match Java's civil calendar, which permits February 29 in year 0. For
example,
+ // UTC 0000-02-28 20:00 at +08:00 passes through that local date before
subtracting
+ // a day. Validate the final UTC value rather than this local intermediate
as DateV2.
+ DateV2Value<DateTimeV2ValueType> local_dt;
+ local_dt.unchecked_set_time(
+ static_cast<uint16_t>(target.year()),
static_cast<uint8_t>(target.month()),
+ static_cast<uint8_t>(target.day()),
static_cast<uint8_t>(local.cs.hour()),
+ static_cast<uint8_t>(local.cs.minute()),
static_cast<uint8_t>(local.cs.second()),
+ microsecond());
+ // Preserve the input offset when the target repeats, e.g. the second
autumn 01:30.
+ convert_local_to_utc(local_time_zone, local_dt, local.offset);
+ // A valid local result can still fall outside the supported UTC year
range.
+ return is_valid_date();
+}
+
bool TimestampTzValue::from_datetime(const DateV2Value<DateTimeV2ValueType>&
origin_dt,
const cctz::time_zone& local_time_zone,
int dt_scale,
int tz_scale) {
diff --git a/be/src/core/value/timestamptz_value.h
b/be/src/core/value/timestamptz_value.h
index 7409e0eb56c..34d4cb283eb 100644
--- a/be/src/core/value/timestamptz_value.h
+++ b/be/src/core/value/timestamptz_value.h
@@ -134,6 +134,9 @@ public:
return _utc_dt.date_add_interval<unit, need_check>(interval);
}
+ // Add session-local calendar days, preserving the input offset in a DST
overlap.
+ bool date_add_days(int64_t days, const cctz::time_zone& local_time_zone);
+
// truncate datetime to specified unit
template <TimeUnit unit>
bool datetime_trunc() {
diff --git a/be/src/exprs/function/function_date_or_datetime_computation.h
b/be/src/exprs/function/function_date_or_datetime_computation.h
index 1212fad82b9..856fcdc2c43 100644
--- a/be/src/exprs/function/function_date_or_datetime_computation.h
+++ b/be/src/exprs/function/function_date_or_datetime_computation.h
@@ -76,11 +76,17 @@ namespace doris {
template <TimeUnit unit, PrimitiveType ArgType, typename IntervalType>
auto date_time_add(const typename
PrimitiveTypeTraits<ArgType>::DataType::FieldType& t,
- IntervalType delta) {
+ IntervalType delta, const cctz::time_zone& time_zone) {
// e.g.: for DatatypeDatetimeV2, cast from u64 to
DateV2Value<DateTimeV2ValueType>
auto ts_value = t;
- if constexpr ((unit == TimeUnit::DAY || unit == TimeUnit::WEEK) &&
- is_date_v2_or_datetime_v2(ArgType)) {
+ if constexpr (ArgType == TYPE_TIMESTAMPTZ && unit == TimeUnit::DAY) {
+ // In New York, 2024-03-09 12:00 + 1 calendar day is only 23 elapsed
hours.
+ if (!ts_value.date_add_days(delta, time_zone)) [[unlikely]] {
+ throw_out_of_bound_date_int(get_time_unit_name(unit), t, delta);
+ }
+ return ts_value;
+ } else if constexpr ((unit == TimeUnit::DAY || unit == TimeUnit::WEEK) &&
+ is_date_v2_or_datetime_v2(ArgType)) {
// DAY / WEEK only move the date part. `date_add_days` is inline, so
the whole per-row
// computation folds into the caller's loop: no TimeInterval, no
second-level arithmetic,
// just two L1-resident dictionary lookups. The input comes from a
column and is already
@@ -102,34 +108,36 @@ auto date_time_add(const typename
PrimitiveTypeTraits<ArgType>::DataType::FieldT
}
}
-#define ADD_TIME_FUNCTION_IMPL(CLASS, NAME, UNIT)
\
- template <PrimitiveType PType>
\
- struct CLASS {
\
- /* return type must be same with arg type. cast have already be
planned in FE */ \
- static constexpr PrimitiveType ArgPType = PType;
\
- static constexpr PrimitiveType ReturnType = PType;
\
- /* for interval <= minute, use bigint. otherwise int. */
\
- static constexpr PrimitiveType IntervalPType =
\
- (TimeUnit::UNIT == TimeUnit::YEAR || TimeUnit::UNIT ==
TimeUnit::QUARTER || \
- TimeUnit::UNIT == TimeUnit::MONTH || TimeUnit::UNIT ==
TimeUnit::WEEK || \
- TimeUnit::UNIT == TimeUnit::DAY || TimeUnit::UNIT ==
TimeUnit::HOUR) \
- ? PrimitiveType::TYPE_INT
\
- : PrimitiveType::TYPE_BIGINT;
\
- using InputValueType = typename
PrimitiveTypeTraits<PType>::DataType::FieldType; \
- using ReturnValueType = InputValueType;
\
- using IntervalDataType = typename
PrimitiveTypeTraits<IntervalPType>::DataType; \
- using IntervalValueType = IntervalDataType::FieldType;
\
-
\
- static constexpr auto name = #NAME;
\
- static constexpr auto is_nullable = false;
\
- static inline ReturnValueType execute(const InputValueType& t,
IntervalValueType delta) { \
- return date_time_add<TimeUnit::UNIT, PType, IntervalValueType>(t,
delta); \
- }
\
-
\
- static DataTypes get_variadic_argument_types() {
\
- return {std::make_shared<typename
PrimitiveTypeTraits<PType>::DataType>(), \
- std::make_shared<typename
PrimitiveTypeTraits<IntervalPType>::DataType>()}; \
- }
\
+#define ADD_TIME_FUNCTION_IMPL(CLASS, NAME, UNIT)
\
+ template <PrimitiveType PType>
\
+ struct CLASS {
\
+ /* return type must be same with arg type. cast have already be
planned in FE */ \
+ static constexpr PrimitiveType ArgPType = PType;
\
+ static constexpr PrimitiveType ReturnType = PType;
\
+ /* for interval <= minute, use bigint. otherwise int. */
\
+ static constexpr PrimitiveType IntervalPType =
\
+ (TimeUnit::UNIT == TimeUnit::YEAR || TimeUnit::UNIT ==
TimeUnit::QUARTER || \
+ TimeUnit::UNIT == TimeUnit::MONTH || TimeUnit::UNIT ==
TimeUnit::WEEK || \
+ TimeUnit::UNIT == TimeUnit::DAY || TimeUnit::UNIT ==
TimeUnit::HOUR) \
+ ? PrimitiveType::TYPE_INT
\
+ : PrimitiveType::TYPE_BIGINT;
\
+ using InputValueType = typename
PrimitiveTypeTraits<PType>::DataType::FieldType; \
+ using ReturnValueType = InputValueType;
\
+ using IntervalDataType = typename
PrimitiveTypeTraits<IntervalPType>::DataType; \
+ using IntervalValueType = IntervalDataType::FieldType;
\
+
\
+ static constexpr auto name = #NAME;
\
+ static constexpr auto is_nullable = false;
\
+ static inline ReturnValueType execute(
\
+ const InputValueType& t, IntervalValueType delta,
\
+ const cctz::time_zone& time_zone = cctz::utc_time_zone()) {
\
+ return date_time_add<TimeUnit::UNIT, PType, IntervalValueType>(t,
delta, time_zone); \
+ }
\
+
\
+ static DataTypes get_variadic_argument_types() {
\
+ return {std::make_shared<typename
PrimitiveTypeTraits<PType>::DataType>(), \
+ std::make_shared<typename
PrimitiveTypeTraits<IntervalPType>::DataType>()}; \
+ }
\
}
ADD_TIME_FUNCTION_IMPL(AddMicrosecondsImpl, microseconds_add, MICROSECOND);
ADD_TIME_FUNCTION_IMPL(AddMillisecondsImpl, milliseconds_add, MILLISECOND);
@@ -422,9 +430,11 @@ struct AddQuartersImpl {
static constexpr auto name = "quarters_add";
static constexpr auto is_nullable = false;
- static inline ReturnValueType execute(const InputValueType& t, Int64
delta) {
+ static inline ReturnValueType execute(
+ const InputValueType& t, Int64 delta,
+ const cctz::time_zone& time_zone = cctz::utc_time_zone()) {
// Preserve both the month offset and the negation of INT_MIN in
quarters_sub.
- return date_time_add<TimeUnit::MONTH, PType, Int64>(t, 3 * delta);
+ return date_time_add<TimeUnit::MONTH, PType, Int64>(t, 3 * delta,
time_zone);
}
static DataTypes get_variadic_argument_types() {
@@ -440,8 +450,10 @@ struct SubtractIntervalImpl {
using InputValueType = typename Transform::InputValueType;
using ReturnValueType = typename Transform::ReturnValueType;
static constexpr auto is_nullable = false;
- static inline ReturnValueType execute(const InputValueType& t, Int64
delta) {
- return Transform::execute(t, -delta);
+ static inline ReturnValueType execute(
+ const InputValueType& t, Int64 delta,
+ const cctz::time_zone& time_zone = cctz::utc_time_zone()) {
+ return Transform::execute(t, -delta, time_zone);
}
static DataTypes get_variadic_argument_types() {
@@ -760,6 +772,18 @@ struct DateTimeOp {
// arg1 maybe just delta value(e.g. DataTypeInt32, not datelike type)
constexpr static bool CastType1 = is_date_type(DataType1);
+ static ToType execute(const ValueType0& from, const ValueType1& delta,
+ FunctionContext* context) {
+ // Date add/sub needs the session zone; diff and extract transforms
take two arguments.
+ if constexpr (requires {
+ Transform::execute(from, delta,
context->state()->timezone_obj());
+ }) {
+ return Transform::execute(from, delta,
context->state()->timezone_obj());
+ } else {
+ return Transform::execute(from, delta);
+ }
+ }
+
// Use for compatibility with TimeUnionType interval(string type)
static inline ValueType1 get_element(const IntervalColumnType& data,
size_t i) {
if constexpr (DataType1 == TYPE_STRING) {
@@ -772,18 +796,20 @@ struct DateTimeOp {
// execute on the null value's nested value may cause false positive
exception, so use nullmaps to skip them.
static void vector_vector(const PaddedPODArray<ValueType0>& vec_from0,
const IntervalColumnType& vec_from1,
PaddedPODArray<ToType>& vec_to,
- const NullMap* nullmap0, const NullMap*
nullmap1) {
+ const NullMap* nullmap0, const NullMap* nullmap1,
+ FunctionContext* context) {
for (size_t i = 0; i < vec_from0.size(); ++i) {
if ((nullmap0 && (*nullmap0)[i]) || (nullmap1 && (*nullmap1)[i]))
[[unlikely]] {
continue;
}
- vec_to[i] = Transform::execute(vec_from0[i],
get_element(vec_from1, i));
+ vec_to[i] = execute(vec_from0[i], get_element(vec_from1, i),
context);
}
}
static void vector_constant(const PaddedPODArray<ValueType0>& vec_from,
PaddedPODArray<ToType>& vec_to, const
ValueType1& delta,
- const NullMap* nullmap0, const NullMap*
nullmap1) {
+ const NullMap* nullmap0, const NullMap*
nullmap1,
+ FunctionContext* context) {
if (nullmap1 && (*nullmap1)[0]) [[unlikely]] {
return;
}
@@ -792,13 +818,13 @@ struct DateTimeOp {
if (nullmap0 && (*nullmap0)[i]) [[unlikely]] {
continue;
}
- vec_to[i] = Transform::execute(vec_from[i], delta);
+ vec_to[i] = execute(vec_from[i], delta, context);
}
}
static void constant_vector(const ValueType0& from,
PaddedPODArray<ToType>& vec_to,
const IntervalColumnType& delta, const
NullMap* nullmap0,
- const NullMap* nullmap1) {
+ const NullMap* nullmap1, FunctionContext*
context) {
if (nullmap0 && (*nullmap0)[0]) [[unlikely]] {
return;
}
@@ -807,7 +833,7 @@ struct DateTimeOp {
if (nullmap1 && (*nullmap1)[i]) [[unlikely]] {
continue;
}
- vec_to[i] = Transform::execute(from, get_element(delta, i));
+ vec_to[i] = execute(from, get_element(delta, i), context);
}
}
};
@@ -877,11 +903,12 @@ public:
const auto& col1_inside_const =
assert_cast<const
RightColumnType&>(nest_col1_const->get_data_column());
Op::vector_constant(sources->get_data(), res_col->get_data(),
- Op::get_element(col1_inside_const, 0),
nullmap0, nullmap1);
+ Op::get_element(col1_inside_const, 0),
nullmap0, nullmap1,
+ context);
} else { // vector-vector
const auto& concrete_col1 = assert_cast<const
RightColumnType&>(*nest_col1);
Op::vector_vector(sources->get_data(), concrete_col1,
res_col->get_data(), nullmap0,
- nullmap1);
+ nullmap1, context);
}
// update result nullmap with inputs
@@ -908,7 +935,7 @@ public:
const ColumnPtr nested_col1 = remove_nullable(col1);
const auto& concrete_col1 = assert_cast<const
RightColumnType&>(*nested_col1);
Op::constant_vector(col0_inside_const.get_data()[0],
res_col->get_data(), concrete_col1,
- nullmap0, nullmap1);
+ nullmap0, nullmap1, context);
// update result nullmap with inputs
if (result_nullable) {
@@ -1002,11 +1029,12 @@ public:
const auto& col1_inside_const =
assert_cast<const
IntervalColumnType&>(nest_col1_const->get_data_column());
Op::vector_constant(sources->get_data(), res_col->get_data(),
- Op::get_element(col1_inside_const, 0),
nullmap0, nullmap1);
+ Op::get_element(col1_inside_const, 0),
nullmap0, nullmap1,
+ context);
} else { // vector-vector
const auto& concrete_col1 = assert_cast<const
IntervalColumnType&>(*nest_col1);
Op::vector_vector(sources->get_data(), concrete_col1,
res_col->get_data(), nullmap0,
- nullmap1);
+ nullmap1, context);
}
// update result nullmap with inputs
@@ -1033,7 +1061,7 @@ public:
const ColumnPtr nested_col1 = remove_nullable(col1);
const auto& concrete_col1 = assert_cast<const
IntervalColumnType&>(*nested_col1);
Op::constant_vector(col0_inside_const.get_data()[0],
res_col->get_data(), concrete_col1,
- nullmap0, nullmap1);
+ nullmap0, nullmap1, context);
// update result nullmap with inputs
if (result_nullable) {
diff --git a/be/test/exprs/function/function_time_test.cpp
b/be/test/exprs/function/function_time_test.cpp
index 9b3a0d3b821..5c40252d51c 100644
--- a/be/test/exprs/function/function_time_test.cpp
+++ b/be/test/exprs/function/function_time_test.cpp
@@ -31,6 +31,7 @@
#include "core/data_type/data_type_number.h"
#include "core/data_type/data_type_string.h"
#include "core/data_type/data_type_time.h"
+#include "core/data_type/data_type_timestamptz.h"
#include "core/types.h"
#include "core/value/time_value.h"
#include "core/value/timestamptz_value.h"
@@ -38,12 +39,75 @@
#include "exprs/function/date_time_transforms.h"
#include "exprs/function/function_date_or_datetime_computation.h"
#include "exprs/function/function_test_util.h"
+#include "testutil/datetime_ut_util.h"
#include "testutil/mock/mock_runtime_state.h"
#include "util/timezone_utils.h"
namespace doris {
using namespace ut_type;
+TEST(VTimestampFunctionsTest, timestamptz_calendar_day_dispatch) {
+ MockRuntimeState state;
+ ASSERT_TRUE(cctz::load_time_zone("America/New_York",
&state._timezone_obj));
+ FunctionContext context;
+ context._state = &state;
+ const auto spring = make_timestamptz(2024, 3, 9, 17, 0, 0, 123456);
+ const auto autumn = make_timestamptz(2024, 11, 2, 16, 0, 0, 123456);
+ using TimestampColumn = ColumnVector<TYPE_TIMESTAMPTZ>;
+ FunctionDateOrDateTimeComputation<AddDaysImpl<TYPE_TIMESTAMPTZ>> function;
+ // Exercise vector/vector, vector/constant and constant/vector with
nullable inputs.
+ for (const bool left_const : {false, true}) {
+ for (const bool right_const : {false, true}) {
+ // The default IFunction wrapper materializes the
constant/constant case.
+ if (left_const && right_const) {
+ continue;
+ }
+ auto timestamps = TimestampColumn::create();
+ timestamps->insert_value(spring);
+ timestamps->insert_value(autumn);
+ // An invalid nested timestamp must never be evaluated for a NULL
row.
+ timestamps->insert_value(TimestampTzValue(0));
+ auto null_map = ColumnBool::create(3, 0);
+ null_map->get_data()[2] = 1;
+ ColumnPtr left = ColumnNullable::create(std::move(timestamps),
std::move(null_map));
+ auto deltas = ColumnInt32::create();
+ deltas->insert_value(1);
+ deltas->insert_value(-1);
+ deltas->insert_value(1);
+ ColumnPtr right = deltas->get_ptr();
+ if (left_const) {
+ left = ColumnConst::create(left->clone_resized(1), 3);
+ }
+ if (right_const) {
+ right = ColumnConst::create(right->clone_resized(1), 3);
+ }
+ const auto type =
make_nullable(std::make_shared<DataTypeTimeStampTz>(6));
+ Block block {{left, type, "ts"},
+ {right, std::make_shared<DataTypeInt32>(), "days"},
+ {nullptr, type, "result"}};
+ ASSERT_TRUE(function.execute_impl(&context, block, {0, 1}, 2,
3).ok());
+ const auto& result =
+ assert_cast<const
ColumnNullable&>(*block.get_by_position(2).column);
+ const auto& values = assert_cast<const
TimestampColumn&>(result.get_nested_column());
+ for (size_t row = 0; row < 3; ++row) {
+ if (!left_const && row == 2) {
+ EXPECT_TRUE(result.is_null_at(row));
+ continue;
+ }
+ auto expected = left_const || row == 0 ? spring : autumn;
+ ASSERT_TRUE(expected.date_add_days(right_const || row != 1 ? 1
: -1,
+ state._timezone_obj));
+ EXPECT_EQ(values.get_data()[row], expected);
+ }
+ }
+ }
+ EXPECT_EQ(AddHoursImpl<TYPE_TIMESTAMPTZ>::execute(spring, 24,
state._timezone_obj),
+ make_timestamptz(2024, 3, 10, 17, 0, 0, 123456));
+ EXPECT_EQ(SubtractDaysImpl<TYPE_TIMESTAMPTZ>::execute(
+ make_timestamptz(2024, 3, 10, 16, 0, 0, 123456), 1,
state._timezone_obj),
+ spring);
+}
+
TEST(VTimestampFunctionsTest, iso8601_preserves_negative_subhour_offset) {
MockRuntimeState state;
state._timezone_obj = cctz::fixed_time_zone(cctz::seconds(-1800));
diff --git a/be/test/runtime/timestamptz_value_test.cpp
b/be/test/runtime/timestamptz_value_test.cpp
index 2b6fd175a7b..6e6678956f4 100644
--- a/be/test/runtime/timestamptz_value_test.cpp
+++ b/be/test/runtime/timestamptz_value_test.cpp
@@ -38,6 +38,105 @@ TEST(TimeStampTzValueTest, make_time) {
EXPECT_EQ(tz.to_date_int_val(), MIN_DATETIME_V2);
}
+TEST(TimeStampTzValueTest, AddLocalCalendarDays) {
+ TimezoneUtils::load_offsets_to_cache();
+ cctz::time_zone new_york;
+ ASSERT_TRUE(cctz::load_time_zone("America/New_York", &new_york));
+ struct TestCase {
+ const char* input;
+ const char* expected;
+ int days;
+ int elapsed_seconds;
+ };
+ const TestCase cases[] = {
+ {.input = "2024-03-09 12:00:00.123456-05:00",
+ .expected = "2024-03-10 12:00:00.123456-04:00",
+ .days = 1,
+ .elapsed_seconds = 82800},
+ {.input = "2024-11-02 12:00:00.123456-04:00",
+ .expected = "2024-11-03 12:00:00.123456-05:00",
+ .days = 1,
+ .elapsed_seconds = 90000},
+ {.input = "2024-03-10 12:00:00.123456-04:00",
+ .expected = "2024-03-09 12:00:00.123456-05:00",
+ .days = -1,
+ .elapsed_seconds = -82800},
+ {.input = "2024-11-03 12:00:00.123456-05:00",
+ .expected = "2024-11-02 12:00:00.123456-04:00",
+ .days = -1,
+ .elapsed_seconds = -90000},
+ {.input = "2024-02-01 12:00:00.123456-05:00",
+ .expected = "2024-02-02 12:00:00.123456-05:00",
+ .days = 1,
+ .elapsed_seconds = 86400},
+ {.input = "2024-11-03 01:30:00.123456-05:00",
+ .expected = "2024-11-03 01:30:00.123456-05:00",
+ .days = 0,
+ .elapsed_seconds = 0},
+ {.input = "2024-11-02 01:30:00.123456-04:00",
+ .expected = "2024-11-03 01:30:00.123456-04:00",
+ .days = 1,
+ .elapsed_seconds = 86400},
+ {.input = "2024-11-04 01:30:00.123456-05:00",
+ .expected = "2024-11-03 01:30:00.123456-05:00",
+ .days = -1,
+ .elapsed_seconds = -86400},
+ // Shift by the gap length and preserve the fractional second in
both directions.
+ {.input = "2024-03-09 02:30:00.123456-05:00",
+ .expected = "2024-03-10 03:30:00.123456-04:00",
+ .days = 1,
+ .elapsed_seconds = 86400},
+ {.input = "2024-03-11 02:30:00.123456-04:00",
+ .expected = "2024-03-10 03:30:00.123456-04:00",
+ .days = -1,
+ .elapsed_seconds = -82800},
+ };
+ for (const auto& test : cases) {
+ SCOPED_TRACE(test.input);
+ CastParameters params;
+ TimestampTzValue input;
+ ASSERT_TRUE(input.from_string(StringRef(test.input), &new_york,
params, 6));
+ auto result = input;
+ ASSERT_TRUE(result.date_add_days(test.days, new_york));
+ EXPECT_EQ(result.to_string(new_york, 6), test.expected);
+ EXPECT_EQ(result.datetime_diff_in_seconds(input),
test.elapsed_seconds);
+ }
+
+ auto utc = make_timestamptz(2024, 3, 9, 12, 0, 0, 123456);
+ ASSERT_TRUE(utc.date_add_days(1, cctz::utc_time_zone()));
+ EXPECT_EQ(utc, make_timestamptz(2024, 3, 10, 12, 0, 0, 123456));
+ auto minimum = make_timestamptz(0, 1, 1, 0, 0, 0, 0);
+ EXPECT_FALSE(minimum.date_add_days(-1, cctz::utc_time_zone()));
+ auto maximum = make_timestamptz(9999, 12, 31, 12, 0, 0, 0);
+ EXPECT_FALSE(maximum.date_add_days(1, cctz::utc_time_zone()));
+ // The local result is valid but its UTC representation would overflow
year 9999.
+ maximum = make_timestamptz(9999, 12, 31, 1, 0, 0, 0);
+ EXPECT_FALSE(maximum.date_add_days(1,
cctz::fixed_time_zone(std::chrono::hours(-8))));
+}
+
+TEST(TimeStampTzValueTest, AddLocalCalendarDaysAtUtcBoundaries) {
+ cctz::time_zone new_york;
+ ASSERT_TRUE(cctz::load_time_zone("America/New_York", &new_york));
+ const auto east = cctz::fixed_time_zone(std::chrono::hours(8));
+ const auto west = cctz::fixed_time_zone(std::chrono::hours(-8));
+ const auto minimum = make_timestamptz(0, 1, 1, 0, 0, 0, 123456);
+ const auto maximum = make_timestamptz(9999, 12, 31, 23, 0, 0, 123456);
+ auto result = minimum;
+ ASSERT_TRUE(result.date_add_days(0, new_york));
+ EXPECT_EQ(result, minimum);
+ result = maximum;
+ ASSERT_TRUE(result.date_add_days(0, east));
+ EXPECT_EQ(result, maximum);
+
+ // Both conversions pass through local February 29, year 0, and finish in
valid UTC.
+ result = make_timestamptz(0, 2, 28, 20, 0, 0, 123456);
+ ASSERT_TRUE(result.date_add_days(-1, east));
+ EXPECT_EQ(result, make_timestamptz(0, 2, 27, 20, 0, 0, 123456));
+ result = make_timestamptz(0, 3, 1, 1, 0, 0, 123456);
+ ASSERT_TRUE(result.date_add_days(1, west));
+ EXPECT_EQ(result, make_timestamptz(0, 3, 2, 1, 0, 0, 123456));
+}
+
TEST(TimeStampTzValueTest, ToStringPreservesHistoricalOffsetSeconds) {
TimezoneUtils::load_offsets_to_cache();
const auto utc = cctz::utc_time_zone();
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SimplifyArithmeticComparisonRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SimplifyArithmeticComparisonRule.java
index a25f1bb81d4..f72e5f73d6f 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SimplifyArithmeticComparisonRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SimplifyArithmeticComparisonRule.java
@@ -27,6 +27,8 @@ import org.apache.doris.nereids.trees.expressions.Divide;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.Multiply;
import org.apache.doris.nereids.trees.expressions.Subtract;
+import org.apache.doris.nereids.trees.expressions.functions.BoundFunction;
+import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubRearrangeable;
import org.apache.doris.nereids.trees.expressions.functions.scalar.DaysAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.DaysSub;
import org.apache.doris.nereids.trees.expressions.functions.scalar.HoursAdd;
@@ -38,6 +40,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsSub;
import org.apache.doris.nereids.trees.expressions.functions.scalar.WeeksAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.WeeksSub;
import org.apache.doris.nereids.trees.expressions.literal.ComparableLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.DateLiteral;
import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
import org.apache.doris.nereids.util.TypeCoercionUtils;
@@ -99,6 +102,9 @@ public class SimplifyArithmeticComparisonRule implements
ExpressionPatternRuleFa
}
try {
List<Expression> children =
tryRearrangeChildren(newComparison.left(), newComparison.right(), context);
+ if (children == null) {
+ return comparison;
+ }
newComparison = (ComparisonPredicate) simplify(
(ComparisonPredicate)
newComparison.withChildren(children), context);
return TypeCoercionUtils.processComparisonPredicate(newComparison);
@@ -112,6 +118,14 @@ public class SimplifyArithmeticComparisonRule implements
ExpressionPatternRuleFa
return false;
}
+ // Date functions must opt in to exact inverse comparisons for their
bound input domain.
+ // Monotonicity alone proves neither invertibility nor preservation of
range errors.
+ if (cmp.left() instanceof BoundFunction
+ && (!(cmp.left() instanceof DateAddSubRearrangeable)
+ || !((DateAddSubRearrangeable)
cmp.left()).canRearrangeComparison())) {
+ return false;
+ }
+
for (Expression child : cmp.left().children()) {
if (child.isConstant()) {
return true;
@@ -120,7 +134,7 @@ public class SimplifyArithmeticComparisonRule implements
ExpressionPatternRuleFa
return false;
}
- private static List<Expression> tryRearrangeChildren(Expression left,
Expression right,
+ private static @Nullable List<Expression> tryRearrangeChildren(Expression
left, Expression right,
ExpressionRewriteContext context) throws Exception {
if (!left.child(1).isConstant()) {
throw new RuntimeException(String.format("Expected literal when
arranging children for Expr %s", left));
@@ -132,6 +146,18 @@ public class SimplifyArithmeticComparisonRule implements
ExpressionPatternRuleFa
Expression newChild =
oppositeOperator.getConstructor(Expression.class, Expression.class)
.newInstance(right, leftLiteral);
+ if (newChild instanceof BoundFunction) {
+ // Date arithmetic has a finite range. For example, days_sub(d, 1)
< MAX must not
+ // become d < days_add(MAX, 1). Check the inverse on FE before
accepting the rewrite.
+ // NULL and expressions that cannot fold are not valid date
boundaries. This also
+ // excludes a boundary supplied by a column, which can contain MAX
at execution time.
+ newChild = FoldConstantRuleOnFE.evaluate(
+ TypeCoercionUtils.processBoundFunction((BoundFunction)
newChild), context);
+ if (!(newChild instanceof DateLiteral)) {
+ return null;
+ }
+ }
+
if (left instanceof Divide && leftLiteral.compareTo(new
IntegerLiteral(0)) < 0) {
// Multiplying by a negative number will change the operator.
return Arrays.asList(newChild, leftExpr);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/DateAddSubRearrangeable.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/DateAddSubRearrangeable.java
new file mode 100644
index 00000000000..898745fd4bf
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/DateAddSubRearrangeable.java
@@ -0,0 +1,37 @@
+// 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;
+
+/**
+ * Opt-in capability for moving a date interval across a comparison.
+ *
+ * <p>The opposite operation must be an exact, order-preserving inverse on the
arithmetic's
+ * valid input domain. This excludes month/year clamping even when the
function is monotonic.
+ * A bound signature with additional input-domain requirements must decline
rearrangement when
+ * those requirements cannot be proven. In particular, a valid UTC timestamp
need not have a
+ * representable session-local date, so TIMESTAMPTZ calendar DAY arithmetic
cannot opt in.
+ *
+ * <p>This capability does not validate a particular comparison boundary. The
caller must also
+ * evaluate the inverse boundary using FE arithmetic and retain the original
comparison if it
+ * fails or does not produce a date literal. Monotonicity is a separate
range-inference property.
+ */
+public interface DateAddSubRearrangeable extends ExpressionTrait {
+ default boolean canRearrangeComparison() {
+ return child(1).isConstant();
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/MonotonicityUtils.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/MonotonicityUtils.java
index 1e3d932501e..c83428ecac4 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/MonotonicityUtils.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/MonotonicityUtils.java
@@ -62,6 +62,12 @@ public final class MonotonicityUtils {
return true;
}
+ /** Calendar DAY arithmetic preserves UTC order only when the session
offset cannot change. */
+ public static boolean isCalendarDayMonotonic(DataType inputType) {
+ // At a fall-back, 01:50-04 precedes 01:10-05, but adding one local
day reverses their UTC order.
+ return !(inputType instanceof TimeStampTzType) ||
DateUtils.getTimeZone().getRules().isFixedOffset();
+ }
+
public static boolean hasOneVariableArgument(ExpressionTrait expression) {
return expression.child(0) instanceof Literal ^ expression.child(1)
instanceof Literal;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DaysAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DaysAdd.java
index ffdcfea289a..3e977c919b8 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DaysAdd.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DaysAdd.java
@@ -21,8 +21,11 @@ import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
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.DateAddSubRearrangeable;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
+import org.apache.doris.nereids.trees.expressions.functions.MonotonicityUtils;
import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
+import org.apache.doris.nereids.trees.expressions.literal.Literal;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.types.DateTimeV2Type;
@@ -40,7 +43,7 @@ import java.util.List;
* ScalarFunction 'days_add'.
*/
public class DaysAdd extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+ ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic, DateAddSubRearrangeable {
//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
@@ -80,6 +83,20 @@ public class DaysAdd extends ScalarFunction implements
BinaryExpression, Explici
return visitor.visitDaysAdd(this, context);
}
+ @Override
+ public boolean isMonotonic(Literal lower, Literal upper) {
+ return DateAddSubMonotonic.super.isMonotonic(lower, upper)
+ &&
MonotonicityUtils.isCalendarDayMonotonic(child(0).getDataType());
+ }
+
+ @Override
+ public boolean canRearrangeComparison() {
+ // Even a fixed offset can put a valid UTC input outside the local
date range. Checking
+ // only the inverse boundary would remove the original operation's
range error.
+ return !(child(0).getDataType() instanceof TimeStampTzType)
+ && DateAddSubRearrangeable.super.canRearrangeComparison();
+ }
+
@Override
public Expression withConstantArgs(Expression literal) {
return new DaysAdd(literal, child(1));
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DaysSub.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DaysSub.java
index e2fd64b60c8..d38977198c1 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DaysSub.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DaysSub.java
@@ -21,8 +21,11 @@ import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
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.DateAddSubRearrangeable;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
+import org.apache.doris.nereids.trees.expressions.functions.MonotonicityUtils;
import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
+import org.apache.doris.nereids.trees.expressions.literal.Literal;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.types.DateTimeV2Type;
@@ -40,7 +43,7 @@ import java.util.List;
* ScalarFunction 'days_sub'.
*/
public class DaysSub extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+ ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic, DateAddSubRearrangeable {
//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
@@ -77,6 +80,20 @@ public class DaysSub extends ScalarFunction implements
BinaryExpression, Explici
return visitor.visitDaysSub(this, context);
}
+ @Override
+ public boolean isMonotonic(Literal lower, Literal upper) {
+ return DateAddSubMonotonic.super.isMonotonic(lower, upper)
+ &&
MonotonicityUtils.isCalendarDayMonotonic(child(0).getDataType());
+ }
+
+ @Override
+ public boolean canRearrangeComparison() {
+ // Even a fixed offset can put a valid UTC input outside the local
date range. Checking
+ // only the inverse boundary would remove the original operation's
range error.
+ return !(child(0).getDataType() instanceof TimeStampTzType)
+ && DateAddSubRearrangeable.super.canRearrangeComparison();
+ }
+
@Override
public Expression withConstantArgs(Expression literal) {
return new DaysSub(literal, child(1));
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/HoursAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/HoursAdd.java
index f05f1072099..d8ad1172262 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/HoursAdd.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/HoursAdd.java
@@ -20,6 +20,7 @@ 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.DateAddSubMonotonic;
+import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubRearrangeable;
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;
@@ -38,7 +39,7 @@ import java.util.List;
* ScalarFunction 'days_add'.
*/
public class HoursAdd extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- PropagateNullable, DateAddSubMonotonic {
+ PropagateNullable, DateAddSubMonotonic, DateAddSubRearrangeable {
public static final List<FunctionSignature> SIGNATURES =
ImmutableList.of(FunctionSignature
.ret(DateTimeV2Type.WILDCARD).args(DateTimeV2Type.WILDCARD,
IntegerType.INSTANCE), FunctionSignature
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/HoursSub.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/HoursSub.java
index 56ca43f588c..5a411abddf8 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/HoursSub.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/HoursSub.java
@@ -20,6 +20,7 @@ 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.DateAddSubMonotonic;
+import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubRearrangeable;
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;
@@ -38,7 +39,7 @@ import java.util.List;
* ScalarFunction 'hours_sub'.
*/
public class HoursSub extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- PropagateNullable, DateAddSubMonotonic {
+ PropagateNullable, DateAddSubMonotonic, DateAddSubRearrangeable {
public static final List<FunctionSignature> SIGNATURES =
ImmutableList.of(FunctionSignature
.ret(DateTimeV2Type.WILDCARD).args(DateTimeV2Type.WILDCARD,
IntegerType.INSTANCE), FunctionSignature
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MinutesAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MinutesAdd.java
index 6b0bd7166b6..2817c7649bd 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MinutesAdd.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MinutesAdd.java
@@ -20,6 +20,7 @@ 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.DateAddSubMonotonic;
+import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubRearrangeable;
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;
@@ -38,7 +39,7 @@ import java.util.List;
* ScalarFunction 'minutes_add'.
*/
public class MinutesAdd extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- PropagateNullable, DateAddSubMonotonic {
+ PropagateNullable, DateAddSubMonotonic, DateAddSubRearrangeable {
public static final List<FunctionSignature> SIGNATURES =
ImmutableList.of(FunctionSignature
.ret(DateTimeV2Type.WILDCARD).args(DateTimeV2Type.WILDCARD,
BigIntType.INSTANCE), FunctionSignature
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MinutesSub.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MinutesSub.java
index e124e0feab2..d5124e5b74a 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MinutesSub.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MinutesSub.java
@@ -20,6 +20,7 @@ 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.DateAddSubMonotonic;
+import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubRearrangeable;
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;
@@ -38,7 +39,7 @@ import java.util.List;
* ScalarFunction 'minutes_sub'.
*/
public class MinutesSub extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- PropagateNullable, DateAddSubMonotonic {
+ PropagateNullable, DateAddSubMonotonic, DateAddSubRearrangeable {
public static final List<FunctionSignature> SIGNATURES =
ImmutableList.of(FunctionSignature
.ret(DateTimeV2Type.WILDCARD).args(DateTimeV2Type.WILDCARD,
BigIntType.INSTANCE), FunctionSignature
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SecondsAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SecondsAdd.java
index 359e49d6063..54e8af9a8a1 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SecondsAdd.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SecondsAdd.java
@@ -20,6 +20,7 @@ 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.DateAddSubMonotonic;
+import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubRearrangeable;
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;
@@ -38,7 +39,7 @@ import java.util.List;
* ScalarFunction 'minutes_add'.
*/
public class SecondsAdd extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- PropagateNullable, DateAddSubMonotonic {
+ PropagateNullable, DateAddSubMonotonic, DateAddSubRearrangeable {
public static final List<FunctionSignature> SIGNATURES =
ImmutableList.of(FunctionSignature
.ret(DateTimeV2Type.WILDCARD).args(DateTimeV2Type.WILDCARD,
BigIntType.INSTANCE), FunctionSignature
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SecondsSub.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SecondsSub.java
index cb99957da0c..c9db6d890c6 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SecondsSub.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SecondsSub.java
@@ -20,6 +20,7 @@ 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.DateAddSubMonotonic;
+import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubRearrangeable;
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;
@@ -38,7 +39,7 @@ import java.util.List;
* ScalarFunction 'Seconds_sub'.
*/
public class SecondsSub extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- PropagateNullable, DateAddSubMonotonic {
+ PropagateNullable, DateAddSubMonotonic, DateAddSubRearrangeable {
public static final List<FunctionSignature> SIGNATURES =
ImmutableList.of(FunctionSignature
.ret(DateTimeV2Type.WILDCARD).args(DateTimeV2Type.WILDCARD,
BigIntType.INSTANCE), FunctionSignature
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/WeeksAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/WeeksAdd.java
index e0ee5abe736..f92d4ec0d4a 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/WeeksAdd.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/WeeksAdd.java
@@ -21,6 +21,7 @@ import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
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.DateAddSubRearrangeable;
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 +41,7 @@ import java.util.List;
* ScalarFunction 'weeks_add'.
*/
public class WeeksAdd extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+ ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic, DateAddSubRearrangeable {
//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/WeeksSub.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/WeeksSub.java
index 18dd1d4880b..aa182262701 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/WeeksSub.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/WeeksSub.java
@@ -21,6 +21,7 @@ import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
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.DateAddSubRearrangeable;
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 +41,7 @@ import java.util.List;
* ScalarFunction 'weeks_sub'.
*/
public class WeeksSub extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+ ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic, DateAddSubRearrangeable {
//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/literal/TimestampTzLiteral.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/TimestampTzLiteral.java
index e298874009d..bde0c9666a8 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/TimestampTzLiteral.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/TimestampTzLiteral.java
@@ -39,6 +39,7 @@ import org.apache.logging.log4j.Logger;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZoneOffset;
+import java.time.ZonedDateTime;
import java.util.Objects;
/**
@@ -200,8 +201,24 @@ public class TimestampTzLiteral extends DateTimeLiteral {
throw new AnalysisException(String.format("Cast from %s to %s not
supported", this, targetType));
}
+ /** Add calendar days in the session timezone, retaining the local time of
day. */
public Expression plusDays(long days) {
- return fromJavaDateType(toJavaDateType().plusDays(days),
getDataType().getScale());
+ // Predicate movement relies on zero days being an identity even when
the local
+ // display year of a valid UTC boundary value is outside [0, 9999].
+ if (days == 0) {
+ return this;
+ }
+ ZoneId zone = TimeZoneParser.parse(getSessionTimeZone(),
TimeZoneParser.Usage.SESSION);
+ ZonedDateTime local =
toJavaDateType().atOffset(ZoneOffset.UTC).atZoneSameInstant(zone);
+ LocalDateTime target = local.toLocalDateTime().plusDays(days);
+ // Like BE, reject local years outside [0, 9999], even when UTC is
representable.
+ if (isDateOutOfRange(local.toLocalDateTime()) ||
isDateOutOfRange(target)) {
+ throw new AnalysisException("datetime out of range: " + target);
+ }
+ // Keep the original offset in an overlap and shift gap input by the
gap length,
+ // matching BE lookup.pre: New York's 2024-03-10 02:30 becomes 03:30.
+ return fromJavaDateType(LocalDateTime.ofInstant(
+ ZonedDateTime.ofLocal(target, zone,
local.getOffset()).toInstant(), ZoneOffset.UTC), getScale());
}
/**
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/rules/SimplifyArithmeticComparisonRuleTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/rules/SimplifyArithmeticComparisonRuleTest.java
index f2f8dfcf555..ac1cb572d16 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/rules/SimplifyArithmeticComparisonRuleTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/rules/SimplifyArithmeticComparisonRuleTest.java
@@ -17,18 +17,125 @@
package org.apache.doris.nereids.rules.expression.rules;
+import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.rules.expression.ExpressionRewrite;
import org.apache.doris.nereids.rules.expression.ExpressionRewriteTestHelper;
import org.apache.doris.nereids.rules.expression.ExpressionRuleExecutor;
+import org.apache.doris.nereids.trees.expressions.ComparisonPredicate;
import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.GreaterThan;
+import org.apache.doris.nereids.trees.expressions.LessThan;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubMonotonic;
+import
org.apache.doris.nereids.trees.expressions.functions.executable.DateTimeArithmetic;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.DaysAdd;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.DaysSub;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.HoursAdd;
+import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.TimestampTzLiteral;
+import org.apache.doris.nereids.types.DataType;
+import org.apache.doris.nereids.types.DateTimeType;
+import org.apache.doris.nereids.types.DateTimeV2Type;
+import org.apache.doris.nereids.types.DateType;
+import org.apache.doris.nereids.types.DateV2Type;
+import org.apache.doris.nereids.types.TimeStampNsType;
+import org.apache.doris.nereids.types.TimeStampTzType;
+import org.apache.doris.qe.ConnectContext;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.Maps;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
+import java.util.Map;
+
class SimplifyArithmeticComparisonRuleTest extends ExpressionRewriteTestHelper
{
+ @Test
+ void testTimestampTzCalendarDaysCannotRearrange() {
+ ConnectContext previous = ConnectContext.get();
+ ConnectContext connectContext = new ConnectContext();
+ connectContext.setThreadLocalInfo();
+ try {
+ SlotReference timestamp = new SlotReference("ts",
TimeStampTzType.of(6));
+ TimestampTzLiteral bound = new
TimestampTzLiteral(TimeStampTzType.of(6),
+ "2024-11-04 01:30:00-05:00");
+ for (String zone : new String[] {"America/New_York", "UTC",
"+08:00", "-08:00"}) {
+ connectContext.getSessionVariable().setTimeZone(zone);
+ for (int days : new int[] {-1, 0, 1}) {
+ IntegerLiteral interval = new IntegerLiteral(days);
+ for (Expression arithmetic : new Expression[] {new
DaysAdd(timestamp, interval),
+ new DaysSub(timestamp, interval)}) {
+ ComparisonPredicate predicate = new
LessThan(arithmetic, bound);
+ Assertions.assertEquals(predicate,
SimplifyArithmeticComparisonRule.simplify(predicate, context));
+ // Range consumers still use monotonicity,
independently of rearrangement eligibility.
+
Assertions.assertEquals(!zone.equals("America/New_York"),
+ ((DateAddSubMonotonic)
arithmetic).inferRange(bound, bound, context).isPresent());
+ }
+ }
+ ComparisonPredicate hours = new LessThan(new
HoursAdd(timestamp, new IntegerLiteral(1)), bound);
+ Assertions.assertNotEquals(hours,
SimplifyArithmeticComparisonRule.simplify(hours, context));
+ }
+ } finally {
+ ConnectContext.remove();
+ if (previous != null) {
+ previous.setThreadLocalInfo();
+ }
+ }
+ }
+
+ @Test
+ void testTimestampTzValidInverseDoesNotHideSourceRangeError() {
+ ConnectContext previous = ConnectContext.get();
+ ConnectContext connectContext = new ConnectContext();
+ connectContext.setThreadLocalInfo();
+ try {
+ SlotReference timestamp = new SlotReference("ts",
TimeStampTzType.of(6));
+ IntegerLiteral one = new IntegerLiteral(1);
+ connectContext.getSessionVariable().setTimeZone("+08:00");
+ TimestampTzLiteral maximum = new
TimestampTzLiteral(TimeStampTzType.of(6),
+ "9999-12-31 23:00:00+00:00");
+ TimestampTzLiteral upperCutoff = new
TimestampTzLiteral(TimeStampTzType.of(6),
+ "9999-12-30 12:00:00+00:00");
+ Expression upperInverse = FoldConstantRuleOnFE.evaluate(new
DaysAdd(upperCutoff, one), context);
+ Assertions.assertEquals(new
TimestampTzLiteral(TimeStampTzType.of(6),
+ "9999-12-31 12:00:00+00:00"), upperInverse);
+ Assertions.assertThrows(AnalysisException.class, () ->
DateTimeArithmetic.daysSub(maximum, one));
+ Assertions.assertEquals(BooleanLiteral.TRUE,
+ FoldConstantRuleOnFE.evaluate(new GreaterThan(maximum,
upperInverse), context));
+ ComparisonPredicate subtract = new GreaterThan(new
DaysSub(timestamp, one), upperCutoff);
+ Assertions.assertEquals(subtract,
SimplifyArithmeticComparisonRule.simplify(subtract, context));
+ for (String operator : ImmutableList.of("<", "<=", ">", ">=", "=",
"<=>")) {
+ assertDateComparisonUnchanged("days_sub(AA, 1) " + operator
+ + " '9999-12-30 12:00:00+00:00'",
TimeStampTzType.of(6));
+ assertDateComparisonUnchanged("days_add(AA, -1) " + operator
+ + " '9999-12-30 12:00:00+00:00'",
TimeStampTzType.of(6));
+ }
+
+ // The symmetric case crosses the lower local-year boundary in a
negative offset.
+ connectContext.getSessionVariable().setTimeZone("-08:00");
+ TimestampTzLiteral minimum = new
TimestampTzLiteral(TimeStampTzType.of(6),
+ "0000-01-01 01:00:00+00:00");
+ TimestampTzLiteral lowerCutoff = new
TimestampTzLiteral(TimeStampTzType.of(6),
+ "0000-01-02 12:00:00+00:00");
+ Expression lowerInverse = FoldConstantRuleOnFE.evaluate(new
DaysSub(lowerCutoff, one), context);
+ Assertions.assertEquals(new
TimestampTzLiteral(TimeStampTzType.of(6),
+ "0000-01-01 12:00:00+00:00"), lowerInverse);
+ Assertions.assertThrows(AnalysisException.class, () ->
DateTimeArithmetic.daysAdd(minimum, one));
+ Assertions.assertEquals(BooleanLiteral.TRUE,
+ FoldConstantRuleOnFE.evaluate(new LessThan(minimum,
lowerInverse), context));
+ ComparisonPredicate add = new LessThan(new DaysAdd(timestamp,
one), lowerCutoff);
+ Assertions.assertEquals(add,
SimplifyArithmeticComparisonRule.simplify(add, context));
+ } finally {
+ ConnectContext.remove();
+ if (previous != null) {
+ previous.setThreadLocalInfo();
+ }
+ }
+ }
+
@Test
public void testNumeric() {
executor = new ExpressionRuleExecutor(ImmutableList.of(
@@ -184,6 +291,77 @@ class SimplifyArithmeticComparisonRuleTest extends
ExpressionRewriteTestHelper {
assertRewriteAfterTypeCoercion("seconds_sub(CA, 1) > '2021-01-01
00:00:00'", "(cast(CA as DATETIMEV2(0)) > '2021-01-01 00:00:01')");
}
+ @Test
+ public void testDateComparisonBoundaries() {
+ // Use a fixed-offset session so the DAY inverse guard is exercised.
+
cascadesContext.getConnectContext().getSessionVariable().setTimeZone("UTC");
+ // Each expression needs one slot and one boundary. No table rows are
needed.
+ // The inverse operation exceeds the type range at both ends of that
range.
+ for (DataType type : ImmutableList.of(DateType.INSTANCE,
DateTimeType.INSTANCE,
+ DateV2Type.INSTANCE, DateTimeV2Type.of(0),
+ DateTimeV2Type.of(6), TimeStampTzType.of(6),
TimeStampNsType.INSTANCE)) {
+ String minimum = "0000-01-01 00:00:00";
+ String maximum = "9999-12-31 23:59:59.999999";
+ if (type.isDateType() || type.isDateV2Type()) {
+ minimum = "0000-01-01";
+ maximum = "9999-12-31";
+ } else if (type.isTimeStampNsType()) {
+ minimum = "1677-09-21 00:12:43.145224192";
+ maximum = "2262-04-11 23:47:16.854775807";
+ } else if (type.isTimeStampTzType()) {
+ minimum += "+00:00";
+ maximum += "+00:00";
+ } else if (type.isDateTimeType() ||
type.equals(DateTimeV2Type.of(0))) {
+ maximum = "9999-12-31 23:59:59";
+ }
+ for (String unit : ImmutableList.of("weeks", "days", "hours",
"minutes", "seconds")) {
+ // Operations on hours, minutes, and seconds return DATETIME
for DATE. Use its upper limit.
+ String upperBoundary = (type.isDateType() ||
type.isDateV2Type())
+ && !unit.equals("weeks") && !unit.equals("days") ?
"9999-12-31 23:59:59" : maximum;
+ for (String operator : ImmutableList.of("<", "<=", ">", ">=",
"=", "<=>")) {
+ assertDateComparisonUnchanged(unit + "_sub(AA, 1) " +
operator + " '" + upperBoundary + "'", type);
+ assertDateComparisonUnchanged(unit + "_add(AA, 1) " +
operator + " '" + minimum + "'", type);
+ // A negative interval changes which inverse operation
exceeds the range.
+ assertDateComparisonUnchanged(unit + "_add(AA, -1) " +
operator + " '" + upperBoundary + "'", type);
+ assertDateComparisonUnchanged(unit + "_sub(AA, -1) " +
operator + " '" + minimum + "'", type);
+ }
+ }
+ // A column can contain a boundary value. Planning cannot prove
that its inverse is valid.
+ assertDateComparisonUnchanged("days_sub(AA, 1) < AB", type);
+ // NULL is not a date boundary. Keep the original null-safe
comparison.
+ assertDateComparisonUnchanged("days_sub(AA, 1) <=> NULL", type);
+ }
+ }
+
+ @Test
+ public void testTimestampComparisonWithinRange() {
+ // Elapsed-time arithmetic keeps the existing optimization and
fractional precision.
+
cascadesContext.getConnectContext().getSessionVariable().setTimeZone("UTC");
+ // TIMESTAMPTZ DAY arithmetic is deliberately excluded even in a
fixed-offset session.
+ for (DataType type : ImmutableList.of(TimeStampTzType.of(6),
TimeStampNsType.INSTANCE)) {
+ String fraction = type.isTimeStampNsType() ? ".123456789" :
".123456+00:00";
+ Map<String, Slot> slots = Maps.newHashMap();
+ slots.put("AA", new SlotReference("AA", type));
+ ComparisonPredicate original = (ComparisonPredicate)
typeCoercion(replaceUnboundSlot(
+ PARSER.parseExpression((type.isTimeStampTzType() ?
"hours_sub(AA, 24)" : "days_sub(AA, 1)")
+ + " < '2026-11-13 15:03:12" + fraction + "'"),
slots));
+ Expression expected = typeCoercion(replaceUnboundSlot(
+ PARSER.parseExpression("AA < '2026-11-14 15:03:12" +
fraction + "'"), slots));
+ Expression actual =
SimplifyArithmeticComparisonRule.simplify(original, context);
+ Assertions.assertEquals(expected,
FoldConstantRuleOnFE.evaluate(actual, context));
+ }
+ }
+
+ private void assertDateComparisonUnchanged(String sql, DataType type) {
+ Map<String, Slot> slots = Maps.newHashMap();
+ slots.put("AA", new SlotReference("AA", type));
+ slots.put("AB", new SlotReference("AB", type));
+ ComparisonPredicate original = (ComparisonPredicate) typeCoercion(
+ replaceUnboundSlot(PARSER.parseExpression(sql), slots));
+ Assertions.assertEquals(original,
SimplifyArithmeticComparisonRule.simplify(original, context),
+ type + ": " + sql);
+ }
+
private void assertRewriteAfterSimplify(String expr, String expected) {
Expression needRewriteExpression = PARSER.parseExpression(expr);
needRewriteExpression = replaceUnboundSlot(needRewriteExpression,
Maps.newHashMap());
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/literal/TimestampTzLiteralTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/literal/TimestampTzLiteralTest.java
index 3317ed2be9e..8777f625f55 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/literal/TimestampTzLiteralTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/literal/TimestampTzLiteralTest.java
@@ -17,15 +17,146 @@
package org.apache.doris.nereids.trees.expressions.literal;
+import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import
org.apache.doris.nereids.trees.expressions.functions.executable.DateTimeArithmetic;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.DaysAdd;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.DaysSub;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.HoursAdd;
+import org.apache.doris.nereids.types.DateTimeV2Type;
+import org.apache.doris.nereids.types.IntegerType;
import org.apache.doris.nereids.types.TimeStampTzType;
import org.apache.doris.qe.ConnectContext;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
+import java.time.Duration;
+
class TimestampTzLiteralTest {
+ @Test
+ void testLocalCalendarDayArithmetic() {
+ ConnectContext previous = ConnectContext.get();
+ ConnectContext context = new ConnectContext();
+ context.getSessionVariable().setTimeZone("America/New_York");
+ context.setThreadLocalInfo();
+ try {
+ String[][] cases = {
+ {"2024-03-09 12:00:00.123456-05:00", "1", "2024-03-10
12:00:00.123456-04:00", "82800"},
+ {"2024-11-02 12:00:00.123456-04:00", "1", "2024-11-03
12:00:00.123456-05:00", "90000"},
+ {"2024-03-10 12:00:00.123456-04:00", "-1", "2024-03-09
12:00:00.123456-05:00", "-82800"},
+ {"2024-11-03 12:00:00.123456-05:00", "-1", "2024-11-02
12:00:00.123456-04:00", "-90000"},
+ {"2024-02-01 12:00:00.123456-05:00", "1", "2024-02-02
12:00:00.123456-05:00", "86400"},
+ {"2024-11-03 01:30:00.123456-05:00", "0", "2024-11-03
01:30:00.123456-05:00", "0"},
+ {"2024-11-02 01:30:00.123456-04:00", "1", "2024-11-03
01:30:00.123456-04:00", "86400"},
+ {"2024-11-04 01:30:00.123456-05:00", "-1", "2024-11-03
01:30:00.123456-05:00", "-86400"},
+ {"2024-03-09 02:30:00.123456-05:00", "1", "2024-03-10
03:30:00.123456-04:00", "86400"},
+ {"2024-03-11 02:30:00.123456-04:00", "-1", "2024-03-10
03:30:00.123456-04:00", "-82800"}
+ };
+ for (String[] testCase : cases) {
+ TimestampTzLiteral input = new
TimestampTzLiteral(TimeStampTzType.of(6), testCase[0]);
+ IntegerLiteral days = new
IntegerLiteral(Integer.parseInt(testCase[1]));
+ TimestampTzLiteral expected = new
TimestampTzLiteral(TimeStampTzType.of(6), testCase[2]);
+ Expression result = DateTimeArithmetic.daysAdd(input, days);
+ Assertions.assertEquals(expected, result, testCase[0]);
+ Assertions.assertEquals(expected,
DateTimeArithmetic.dateAdd(input, days));
+ Assertions.assertEquals(expected,
DateTimeArithmetic.daysSub(input,
+ new IntegerLiteral(-days.getValue())));
+ Assertions.assertEquals(Long.parseLong(testCase[3]),
Duration.between(
+ input.toJavaDateType(), ((TimestampTzLiteral)
result).toJavaDateType()).getSeconds());
+ }
+ TimestampTzLiteral input = new
TimestampTzLiteral(TimeStampTzType.of(6), cases[0][0]);
+ Assertions.assertEquals(new
TimestampTzLiteral(TimeStampTzType.of(6),
+ "2024-03-10 13:00:00.123456-04:00"),
+ DateTimeArithmetic.hoursAdd(input, new
IntegerLiteral(24)));
+ context.getSessionVariable().setTimeZone("UTC");
+ Assertions.assertEquals(new
TimestampTzLiteral(TimeStampTzType.of(6),
+ "2024-03-10 17:00:00.123456+00:00"),
+ DateTimeArithmetic.daysAdd(input, new IntegerLiteral(1)));
+ TimestampTzLiteral minimum = new TimestampTzLiteral(0, 1, 1, 0, 0,
0);
+ TimestampTzLiteral maximum = new TimestampTzLiteral(9999, 12, 31,
1, 0, 0);
+ Assertions.assertThrows(AnalysisException.class, () ->
minimum.plusDays(-1));
+ Assertions.assertThrows(AnalysisException.class, () ->
maximum.plusDays(1));
+ context.getSessionVariable().setTimeZone("-08:00");
+ Assertions.assertThrows(AnalysisException.class, () ->
maximum.plusDays(1));
+ } finally {
+ ConnectContext.remove();
+ if (previous != null) {
+ previous.setThreadLocalInfo();
+ }
+ }
+ }
+
+ @Test
+ void testCalendarDayMonotonicity() {
+ ConnectContext previous = ConnectContext.get();
+ ConnectContext context = new ConnectContext();
+ context.setThreadLocalInfo();
+ try {
+ SlotReference timestamp = new SlotReference("ts",
TimeStampTzType.of(6));
+ TimestampTzLiteral lower = new TimestampTzLiteral("2024-11-03
01:50:00-04:00");
+ TimestampTzLiteral upper = new TimestampTzLiteral("2024-11-03
01:10:00-05:00");
+ DaysAdd add = new DaysAdd(timestamp, new IntegerLiteral(1));
+ DaysSub sub = new DaysSub(timestamp, new IntegerLiteral(1));
+ context.getSessionVariable().setTimeZone("America/New_York");
+ Assertions.assertFalse(add.isMonotonic(lower, upper));
+ Assertions.assertFalse(sub.isMonotonic(lower, upper));
+
Assertions.assertTrue(lower.toJavaDateType().isBefore(upper.toJavaDateType()));
+ Assertions.assertTrue(((TimestampTzLiteral)
lower.plusDays(1)).toJavaDateType().isAfter(
+ ((TimestampTzLiteral)
upper.plusDays(1)).toJavaDateType()));
+ Assertions.assertTrue(new HoursAdd(timestamp, new
IntegerLiteral(24)).isMonotonic(lower, upper));
+ SlotReference datetime = new SlotReference("dt",
DateTimeV2Type.of(6));
+ Assertions.assertTrue(new DaysAdd(datetime, new
IntegerLiteral(1)).isMonotonic(lower, upper));
+ Assertions.assertTrue(new DaysSub(datetime, new
IntegerLiteral(1)).isMonotonic(lower, upper));
+ SlotReference interval = new SlotReference("days",
IntegerType.INSTANCE);
+ Assertions.assertFalse(new DaysAdd(datetime,
interval).isMonotonic(lower, upper));
+ Assertions.assertFalse(new DaysSub(datetime,
interval).isMonotonic(lower, upper));
+ for (String zone : new String[] {"UTC", "+08:00"}) {
+ context.getSessionVariable().setTimeZone(zone);
+ Assertions.assertTrue(add.isMonotonic(lower, upper));
+ Assertions.assertTrue(sub.isMonotonic(lower, upper));
+ Assertions.assertFalse(new DaysAdd(timestamp,
interval).isMonotonic(lower, upper));
+ Assertions.assertFalse(new DaysSub(timestamp,
interval).isMonotonic(lower, upper));
+ }
+ } finally {
+ ConnectContext.remove();
+ if (previous != null) {
+ previous.setThreadLocalInfo();
+ }
+ }
+ }
+
+ @Test
+ void testCalendarDaysAtUtcBoundaries() {
+ ConnectContext previous = ConnectContext.get();
+ ConnectContext context = new ConnectContext();
+ context.setThreadLocalInfo();
+ try {
+ TimestampTzLiteral minimum = new TimestampTzLiteral(0, 1, 1, 0, 0,
0);
+ TimestampTzLiteral maximum = new TimestampTzLiteral(9999, 12, 31,
23, 0, 0);
+ context.getSessionVariable().setTimeZone("America/New_York");
+ Assertions.assertEquals(minimum, minimum.plusDays(0));
+ context.getSessionVariable().setTimeZone("+08:00");
+ Assertions.assertEquals(maximum, maximum.plusDays(0));
+ TimestampTzLiteral east = new
TimestampTzLiteral(TimeStampTzType.of(6),
+ 0, 2, 28, 20, 0, 0, 123456);
+ Assertions.assertEquals(new
TimestampTzLiteral(TimeStampTzType.of(6),
+ 0, 2, 27, 20, 0, 0, 123456), east.plusDays(-1));
+ context.getSessionVariable().setTimeZone("-08:00");
+ TimestampTzLiteral west = new
TimestampTzLiteral(TimeStampTzType.of(6),
+ 0, 3, 1, 1, 0, 0, 123456);
+ Assertions.assertEquals(new
TimestampTzLiteral(TimeStampTzType.of(6),
+ 0, 3, 2, 1, 0, 0, 123456), west.plusDays(1));
+ } finally {
+ ConnectContext.remove();
+ if (previous != null) {
+ previous.setThreadLocalInfo();
+ }
+ }
+ }
+
@Test
void testConstructorsAndParsing() {
TimestampTzLiteral literal;
diff --git
a/regression-test/data/datatype_p0/timestamptz/test_timestamptz_days_add_dst.out
b/regression-test/data/datatype_p0/timestamptz/test_timestamptz_days_add_dst.out
new file mode 100644
index 00000000000..fd3010be4bf
--- /dev/null
+++
b/regression-test/data/datatype_p0/timestamptz/test_timestamptz_days_add_dst.out
@@ -0,0 +1,87 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !fe_literal --
+2024-03-09 12:00:00.123456-05:00 2024-03-10 12:00:00.123456-04:00
2024-03-10 13:00:00.123456-04:00 82800 2024-03-09
12:00:00.123456-05:00 2024-03-10 12:00:00.123456-04:00 2024-03-10
12:00:00.123456-04:00
+
+-- !fe_literal --
+2024-11-02 12:00:00.123456-04:00 2024-11-03 12:00:00.123456-05:00
2024-11-03 11:00:00.123456-05:00 90000 2024-11-02
12:00:00.123456-04:00 2024-11-03 12:00:00.123456-05:00 2024-11-03
12:00:00.123456-05:00
+
+-- !fe_literal --
+2024-02-01 12:00:00.123456-05:00 2024-02-02 12:00:00.123456-05:00
2024-02-02 12:00:00.123456-05:00 86400 2024-02-01
12:00:00.123456-05:00 2024-02-02 12:00:00.123456-05:00 2024-02-02
12:00:00.123456-05:00
+
+-- !fe_overlap_gap --
+2024-11-03 01:30:00.123456-05:00 2024-11-03 01:30:00.123456-04:00
2024-11-03 01:30:00.123456-05:00 2024-03-10 03:30:00.123456-04:00
2024-03-10 03:30:00.123456-04:00
+
+-- !fe_columns --
+1 2024-03-10 12:00:00.123456-04:00 2024-03-10
12:00:00.123456-04:00 82800 2024-03-10 12:00:00.123456-04:00
2024-03-10 12:00:00.123456-04:00
+10 \N \N \N \N 2024-03-10 12:00:00.123456-04:00
+11 \N \N \N 2024-03-10 12:00:00.123456-04:00 \N
+12 2024-11-04 01:50:00.123456-05:00 2024-11-04
01:50:00.123456-05:00 90000 2024-11-04 01:50:00.123456-05:00
2024-03-10 12:00:00.123456-04:00
+13 2024-11-04 01:10:00.123456-05:00 2024-11-04
01:10:00.123456-05:00 86400 2024-11-04 01:10:00.123456-05:00
2024-03-10 12:00:00.123456-04:00
+14 2024-03-10 03:30:00.123456-04:00 2024-03-10
03:30:00.123456-04:00 -82800 2024-03-12 02:30:00.123456-04:00
2024-03-08 12:00:00.123456-05:00
+2 2024-11-03 12:00:00.123456-05:00 2024-11-03
12:00:00.123456-05:00 90000 2024-11-03 12:00:00.123456-05:00
2024-03-10 12:00:00.123456-04:00
+3 2024-03-09 12:00:00.123456-05:00 2024-03-09
12:00:00.123456-05:00 -82800 2024-03-11 12:00:00.123456-04:00
2024-03-08 12:00:00.123456-05:00
+4 2024-11-02 12:00:00.123456-04:00 2024-11-02
12:00:00.123456-04:00 -90000 2024-11-04 12:00:00.123456-05:00
2024-03-08 12:00:00.123456-05:00
+5 2024-02-02 12:00:00.123456-05:00 2024-02-02
12:00:00.123456-05:00 86400 2024-02-02 12:00:00.123456-05:00
2024-03-10 12:00:00.123456-04:00
+6 2024-11-03 01:30:00.123456-05:00 2024-11-03
01:30:00.123456-05:00 0 2024-11-04 01:30:00.123456-05:00
2024-03-09 12:00:00.123456-05:00
+7 2024-11-03 01:30:00.123456-04:00 2024-11-03
01:30:00.123456-04:00 86400 2024-11-03 01:30:00.123456-04:00
2024-03-10 12:00:00.123456-04:00
+8 2024-11-03 01:30:00.123456-05:00 2024-11-03
01:30:00.123456-05:00 -86400 2024-11-05 01:30:00.123456-05:00
2024-03-08 12:00:00.123456-05:00
+9 2024-03-10 03:30:00.123456-04:00 2024-03-10
03:30:00.123456-04:00 86400 2024-03-10 03:30:00.123456-04:00
2024-03-10 12:00:00.123456-04:00
+
+-- !fe_non_monotonic --
+13 2024-11-04 01:10:00.123456-05:00
+
+-- !fe_zero_join --
+1
+
+-- !fe_year_zero_east --
+0000-02-28 04:00:00.123456+08:00 0000-02-28 04:00:00.123456+08:00
true
+
+-- !fe_year_zero_west --
+0000-03-01 17:00:00.123456-08:00 0000-03-01 17:00:00.123456-08:00
+
+-- !fe_utc --
+2024-03-10 17:00:00.123456+00:00 2024-03-10 17:00:00.123456+00:00
86400
+
+-- !be_literal --
+2024-03-09 12:00:00.123456-05:00 2024-03-10 12:00:00.123456-04:00
2024-03-10 13:00:00.123456-04:00 82800 2024-03-09
12:00:00.123456-05:00 2024-03-10 12:00:00.123456-04:00 2024-03-10
12:00:00.123456-04:00
+
+-- !be_literal --
+2024-11-02 12:00:00.123456-04:00 2024-11-03 12:00:00.123456-05:00
2024-11-03 11:00:00.123456-05:00 90000 2024-11-02
12:00:00.123456-04:00 2024-11-03 12:00:00.123456-05:00 2024-11-03
12:00:00.123456-05:00
+
+-- !be_literal --
+2024-02-01 12:00:00.123456-05:00 2024-02-02 12:00:00.123456-05:00
2024-02-02 12:00:00.123456-05:00 86400 2024-02-01
12:00:00.123456-05:00 2024-02-02 12:00:00.123456-05:00 2024-02-02
12:00:00.123456-05:00
+
+-- !be_overlap_gap --
+2024-11-03 01:30:00.123456-05:00 2024-11-03 01:30:00.123456-04:00
2024-11-03 01:30:00.123456-05:00 2024-03-10 03:30:00.123456-04:00
2024-03-10 03:30:00.123456-04:00
+
+-- !be_columns --
+1 2024-03-10 12:00:00.123456-04:00 2024-03-10
12:00:00.123456-04:00 82800 2024-03-10 12:00:00.123456-04:00
2024-03-10 12:00:00.123456-04:00
+10 \N \N \N \N 2024-03-10 12:00:00.123456-04:00
+11 \N \N \N 2024-03-10 12:00:00.123456-04:00 \N
+12 2024-11-04 01:50:00.123456-05:00 2024-11-04
01:50:00.123456-05:00 90000 2024-11-04 01:50:00.123456-05:00
2024-03-10 12:00:00.123456-04:00
+13 2024-11-04 01:10:00.123456-05:00 2024-11-04
01:10:00.123456-05:00 86400 2024-11-04 01:10:00.123456-05:00
2024-03-10 12:00:00.123456-04:00
+14 2024-03-10 03:30:00.123456-04:00 2024-03-10
03:30:00.123456-04:00 -82800 2024-03-12 02:30:00.123456-04:00
2024-03-08 12:00:00.123456-05:00
+2 2024-11-03 12:00:00.123456-05:00 2024-11-03
12:00:00.123456-05:00 90000 2024-11-03 12:00:00.123456-05:00
2024-03-10 12:00:00.123456-04:00
+3 2024-03-09 12:00:00.123456-05:00 2024-03-09
12:00:00.123456-05:00 -82800 2024-03-11 12:00:00.123456-04:00
2024-03-08 12:00:00.123456-05:00
+4 2024-11-02 12:00:00.123456-04:00 2024-11-02
12:00:00.123456-04:00 -90000 2024-11-04 12:00:00.123456-05:00
2024-03-08 12:00:00.123456-05:00
+5 2024-02-02 12:00:00.123456-05:00 2024-02-02
12:00:00.123456-05:00 86400 2024-02-02 12:00:00.123456-05:00
2024-03-10 12:00:00.123456-04:00
+6 2024-11-03 01:30:00.123456-05:00 2024-11-03
01:30:00.123456-05:00 0 2024-11-04 01:30:00.123456-05:00
2024-03-09 12:00:00.123456-05:00
+7 2024-11-03 01:30:00.123456-04:00 2024-11-03
01:30:00.123456-04:00 86400 2024-11-03 01:30:00.123456-04:00
2024-03-10 12:00:00.123456-04:00
+8 2024-11-03 01:30:00.123456-05:00 2024-11-03
01:30:00.123456-05:00 -86400 2024-11-05 01:30:00.123456-05:00
2024-03-08 12:00:00.123456-05:00
+9 2024-03-10 03:30:00.123456-04:00 2024-03-10
03:30:00.123456-04:00 86400 2024-03-10 03:30:00.123456-04:00
2024-03-10 12:00:00.123456-04:00
+
+-- !be_non_monotonic --
+13 2024-11-04 01:10:00.123456-05:00
+
+-- !be_zero_join --
+1
+
+-- !be_year_zero_east --
+0000-02-28 04:00:00.123456+08:00 0000-02-28 04:00:00.123456+08:00
true
+
+-- !be_year_zero_west --
+0000-03-01 17:00:00.123456-08:00 0000-03-01 17:00:00.123456-08:00
+
+-- !be_utc --
+2024-03-10 17:00:00.123456+00:00 2024-03-10 17:00:00.123456+00:00
86400
+
diff --git
a/regression-test/data/nereids_rules_p0/expression/test_date_comparison_boundary.out
b/regression-test/data/nereids_rules_p0/expression/test_date_comparison_boundary.out
new file mode 100644
index 00000000000..67e437be692
--- /dev/null
+++
b/regression-test/data/nereids_rules_p0/expression/test_date_comparison_boundary.out
@@ -0,0 +1,184 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !reported --
+2026-11-13T15:03:12
+
+-- !d_null --
+\N false
+
+-- !d_weeks_limits --
+1
+
+-- !d_weeks_comparisons --
+true true false false false false
+
+-- !d_days_limits --
+1
+
+-- !d_days_comparisons --
+true true false false false false
+
+-- !d_hours_limits --
+1
+
+-- !d_hours_comparisons --
+true true false false false false
+
+-- !d_minutes_limits --
+1
+
+-- !d_minutes_comparisons --
+true true false false false false
+
+-- !d_seconds_limits --
+1
+
+-- !d_seconds_comparisons --
+true true false false false false
+
+-- !dt_null --
+\N false
+
+-- !dt_weeks_limits --
+1
+
+-- !dt_weeks_comparisons --
+true true false false false false
+
+-- !dt_days_limits --
+1
+
+-- !dt_days_comparisons --
+true true false false false false
+
+-- !dt_hours_limits --
+1
+
+-- !dt_hours_comparisons --
+true true false false false false
+
+-- !dt_minutes_limits --
+1
+
+-- !dt_minutes_comparisons --
+true true false false false false
+
+-- !dt_seconds_limits --
+1
+
+-- !dt_seconds_comparisons --
+true true false false false false
+
+-- !dv2_null --
+\N false
+
+-- !dv2_weeks_limits --
+1
+
+-- !dv2_weeks_comparisons --
+true true false false false false
+
+-- !dv2_days_limits --
+1
+
+-- !dv2_days_comparisons --
+true true false false false false
+
+-- !dv2_hours_limits --
+1
+
+-- !dv2_hours_comparisons --
+true true false false false false
+
+-- !dv2_minutes_limits --
+1
+
+-- !dv2_minutes_comparisons --
+true true false false false false
+
+-- !dv2_seconds_limits --
+1
+
+-- !dv2_seconds_comparisons --
+true true false false false false
+
+-- !tz_null --
+\N false
+
+-- !tz_weeks_limits --
+1
+
+-- !tz_weeks_comparisons --
+true true false false false false
+
+-- !tz_days_limits --
+1
+
+-- !tz_days_comparisons --
+true true false false false false
+
+-- !tz_hours_limits --
+1
+
+-- !tz_hours_comparisons --
+true true false false false false
+
+-- !tz_minutes_limits --
+1
+
+-- !tz_minutes_comparisons --
+true true false false false false
+
+-- !tz_seconds_limits --
+1
+
+-- !tz_seconds_comparisons --
+true true false false false false
+
+-- !ns_null --
+\N false
+
+-- !ns_weeks_limits --
+1
+
+-- !ns_weeks_comparisons --
+true true false false false false
+
+-- !ns_days_limits --
+1
+
+-- !ns_days_comparisons --
+true true false false false false
+
+-- !ns_hours_limits --
+1
+
+-- !ns_hours_comparisons --
+true true false false false false
+
+-- !ns_minutes_limits --
+1
+
+-- !ns_minutes_comparisons --
+true true false false false false
+
+-- !ns_seconds_limits --
+1
+
+-- !ns_seconds_comparisons --
+true true false false false false
+
+-- !column_boundary --
+1
+
+-- !aliases --
+1
+
+-- !other_units --
+1
+
+-- !nested --
+1
+
+-- !be_folding --
+1
+
diff --git
a/regression-test/data/nereids_rules_p0/expression/test_timestamptz_day_comparison_errors.out
b/regression-test/data/nereids_rules_p0/expression/test_timestamptz_day_comparison_errors.out
new file mode 100644
index 00000000000..637f1dfc867
--- /dev/null
+++
b/regression-test/data/nereids_rules_p0/expression/test_timestamptz_day_comparison_errors.out
@@ -0,0 +1,13 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !upper_inverse_false --
+9999-12-31 20:00:00.000000+08:00 true
+
+-- !lower_inverse_false --
+0000-01-01 04:00:00.000000-08:00 true
+
+-- !upper_inverse_true --
+9999-12-31 20:00:00.000000+08:00 true
+
+-- !lower_inverse_true --
+0000-01-01 04:00:00.000000-08:00 true
+
diff --git
a/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_days_add_dst.groovy
b/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_days_add_dst.groovy
new file mode 100644
index 00000000000..72f25df67eb
--- /dev/null
+++
b/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_days_add_dst.groovy
@@ -0,0 +1,149 @@
+// 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_timestamptz_days_add_dst") {
+ sql "SET time_zone = 'America/New_York'"
+ sql "SET enable_sql_cache = false"
+ sql "DROP TABLE IF EXISTS timestamptz_days_add_dst"
+ sql """
+ CREATE TABLE timestamptz_days_add_dst (id INT, ts TIMESTAMPTZ(6),
delta INT)
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES('replication_num' = '1')
+ """
+ sql """
+ INSERT INTO timestamptz_days_add_dst VALUES
+ (1, '2024-03-09 12:00:00.123456-05:00', 1),
+ (2, '2024-11-02 12:00:00.123456-04:00', 1),
+ (3, '2024-03-10 12:00:00.123456-04:00', -1),
+ (4, '2024-11-03 12:00:00.123456-05:00', -1),
+ (5, '2024-02-01 12:00:00.123456-05:00', 1),
+ (6, '2024-11-03 01:30:00.123456-05:00', 0),
+ (7, '2024-11-02 01:30:00.123456-04:00', 1),
+ (8, '2024-11-04 01:30:00.123456-05:00', -1),
+ (9, '2024-03-09 02:30:00.123456-05:00', 1),
+ (10, NULL, 1),
+ (11, '2024-03-09 12:00:00.123456-05:00', NULL),
+ (12, '2024-11-03 01:50:00.123456-04:00', 1),
+ (13, '2024-11-03 01:10:00.123456-05:00', 1),
+ (14, '2024-03-11 02:30:00.123456-04:00', -1)
+ """
+ sql "DROP TABLE IF EXISTS timestamptz_days_zero_events"
+ sql "DROP TABLE IF EXISTS timestamptz_days_zero_ids"
+ sql """
+ CREATE TABLE timestamptz_days_zero_events (id INT, ts TIMESTAMPTZ(6))
+ DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES('replication_num' = '1')
+ """
+ sql """
+ CREATE TABLE timestamptz_days_zero_ids (id INT)
+ DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES('replication_num' = '1')
+ """
+ sql """
+ INSERT INTO timestamptz_days_zero_events VALUES
+ (1, '2024-01-01 00:00:00+00:00'),
+ (2, '0000-01-01 00:00:00+00:00'),
+ (3, '0000-02-28 20:00:00.123456+00:00'),
+ (4, '0000-03-01 01:00:00.123456+00:00')
+ """
+ sql "INSERT INTO timestamptz_days_zero_ids VALUES (1)"
+ try {
+ for (def skipFold : [false, true]) {
+ sql "SET debug_skip_fold_constant = ${skipFold}"
+ def tag = skipFold ? "be" : "fe"
+ // Literal expressions exercise FE constant folding and BE
constant evaluation separately.
+ for (def input : ["2024-03-09 12:00:00.123456-05:00",
+ "2024-11-02 12:00:00.123456-04:00",
+ "2024-02-01 12:00:00.123456-05:00"]) {
+ "order_qt_${tag}_literal"("""
+ SELECT CAST(ts AS STRING), CAST(days_add(ts, 1) AS STRING),
+ CAST(hours_add(ts, 24) AS STRING),
seconds_diff(days_add(ts, 1), ts),
+ CAST(days_sub(days_add(ts, 1), 1) AS STRING),
+ CAST(date_add(ts, INTERVAL 1 DAY) AS STRING),
+ CAST(date_sub(ts, INTERVAL -1 DAY) AS STRING)
+ FROM (SELECT CAST('${input}' AS TIMESTAMPTZ(6)) AS ts) t
+ """)
+ }
+ "order_qt_${tag}_overlap_gap"("""
+ SELECT CAST(days_add(CAST('2024-11-03 01:30:00.123456-05:00'
AS TIMESTAMPTZ(6)), 0)
+ AS STRING),
+ CAST(days_add(CAST('2024-11-02 01:30:00.123456-04:00'
AS TIMESTAMPTZ(6)), 1)
+ AS STRING),
+ CAST(days_sub(CAST('2024-11-04 01:30:00.123456-05:00'
AS TIMESTAMPTZ(6)), 1)
+ AS STRING),
+ CAST(days_add(CAST('2024-03-09 02:30:00.123456-05:00'
AS TIMESTAMPTZ(6)), 1)
+ AS STRING),
+ CAST(days_sub(CAST('2024-03-11 02:30:00.123456-04:00'
AS TIMESTAMPTZ(6)), 1)
+ AS STRING)
+ """)
+ // Cover vector/vector, vector/constant, constant/vector, and NULL
propagation.
+ "order_qt_${tag}_columns"("""
+ SELECT id, CAST(days_add(ts, delta) AS STRING),
CAST(days_sub(ts, -delta) AS STRING),
+ seconds_diff(days_add(ts, delta), ts),
CAST(days_add(ts, 1) AS STRING),
+ CAST(days_add(CAST('2024-03-09 12:00:00.123456-05:00'
AS TIMESTAMPTZ(6)), delta)
+ AS STRING)
+ FROM timestamptz_days_add_dst ORDER BY id
+ """)
+ "order_qt_${tag}_non_monotonic"("""
+ SELECT id, CAST(days_add(ts, 1) AS STRING)
+ FROM timestamptz_days_add_dst
+ WHERE id IN (12, 13)
+ AND days_add(ts, 1) < CAST('2024-11-04 01:30:00-05:00' AS
TIMESTAMPTZ(6))
+ ORDER BY id
+ """)
+ // Zero days must remain safe to push before the join, even for a
boundary row
+ // whose local display year is -1. Return only ids to avoid
formatting that row.
+ sql "SET runtime_filter_mode = 'OFF'"
+ "order_qt_${tag}_zero_join"("""
+ SELECT id FROM (
+ SELECT e.id, days_add(e.ts, 0) AS shifted
+ FROM timestamptz_days_zero_events e JOIN
timestamptz_days_zero_ids i ON e.id = i.id
+ ) s WHERE shifted >= CAST('2000-01-01 00:00:00+00:00' AS
TIMESTAMPTZ(6)) ORDER BY id
+ """)
+ sql "SET runtime_filter_mode = default"
+ sql "SET time_zone = '+08:00'"
+ "order_qt_${tag}_year_zero_east"("""
+ SELECT CAST(days_add(ts, -1) AS STRING),
+ CAST(days_sub(CAST('0000-02-28 20:00:00.123456+00:00'
AS TIMESTAMPTZ(6)), 1)
+ AS STRING),
+ days_add(CAST('9999-12-31 23:00:00+00:00' AS
TIMESTAMPTZ(6)), 0)
+ = CAST('9999-12-31 23:00:00+00:00' AS
TIMESTAMPTZ(6))
+ FROM timestamptz_days_zero_events WHERE id = 3
+ """)
+ sql "SET time_zone = '-08:00'"
+ "order_qt_${tag}_year_zero_west"("""
+ SELECT CAST(days_add(ts, 1) AS STRING),
+ CAST(days_add(CAST('0000-03-01 01:00:00.123456+00:00'
AS TIMESTAMPTZ(6)), 1)
+ AS STRING)
+ FROM timestamptz_days_zero_events WHERE id = 4
+ """)
+ sql "SET time_zone = 'UTC'"
+ "order_qt_${tag}_utc"("""
+ SELECT CAST(days_add(ts, 1) AS STRING), CAST(hours_add(ts, 24)
AS STRING),
+ seconds_diff(days_add(ts, 1), ts)
+ FROM (SELECT CAST('2024-03-09 12:00:00.123456-05:00' AS
TIMESTAMPTZ(6)) AS ts) t
+ """)
+ sql "SET time_zone = 'America/New_York'"
+ }
+ } finally {
+ sql "SET enable_sql_cache = default"
+ sql "SET debug_skip_fold_constant = false"
+ sql "SET runtime_filter_mode = default"
+ sql "SET time_zone = default"
+ }
+}
diff --git
a/regression-test/suites/nereids_rules_p0/expression/test_date_comparison_boundary.groovy
b/regression-test/suites/nereids_rules_p0/expression/test_date_comparison_boundary.groovy
new file mode 100644
index 00000000000..d55b3fc5a6c
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/expression/test_date_comparison_boundary.groovy
@@ -0,0 +1,122 @@
+// 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_comparison_boundary") {
+ sql "DROP TABLE IF EXISTS test_date_comparison_boundary"
+ // One row is sufficient. All source values permit each tested date
operation.
+ // The error comes from arithmetic on the comparison boundary, not from
this row.
+ sql """CREATE TABLE test_date_comparison_boundary (
+ id INT, d DATE, dt DATETIME(6), dv2 DATETIMEV2(0),
+ tz TIMESTAMPTZ(6), ns TIMESTAMP_NS, upper_bound DATETIMEV2(0)
+ ) DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES("replication_num"="1")"""
+ sql """INSERT INTO test_date_comparison_boundary VALUES (
+ 1, '2026-11-13', '2026-11-13 15:03:12.123456', '2026-11-13 15:03:12',
+ '2026-11-13 15:03:12.123456+00:00', '2026-11-13 15:03:12.123456789',
+ '9999-12-31 23:59:59')"""
+
+ // This is the reported query. Moving subtraction to the right adds one
day to year 9999.
+ order_qt_reported """SELECT dv2 FROM test_date_comparison_boundary
+ WHERE days_sub(dv2, 1) < '9999-12-31 23:59:59'"""
+
+ // Use each type's exact limits. Explicit UTC offsets make the TZ limits
independent of the session zone.
+ def bounds = [
+ d: ["0000-01-01", "9999-12-31"],
+ dt: ["0000-01-01 00:00:00", "9999-12-31 23:59:59.999999"],
+ dv2: ["0000-01-01 00:00:00", "9999-12-31 23:59:59"],
+ tz: ["0000-01-01 00:00:00+00:00", "9999-12-31 23:59:59.999999+00:00"],
+ ns: ["1677-09-21 00:12:43.145224192", "2262-04-11 23:47:16.854775807"]
+ ]
+ bounds.each { column, limits ->
+ // The same stored row supplies a NULL input. No second row is needed.
+ // A normal comparison must return NULL. Null-safe equality must
return false.
+ "order_qt_${column}_null"("""SELECT
+ days_sub(if(id = 1, NULL, ${column}), 1) < '${limits[1]}',
+ days_sub(if(id = 1, NULL, ${column}), 1) <=> '${limits[1]}'
+ FROM test_date_comparison_boundary""")
+ ["weeks", "days", "hours", "minutes", "seconds"].each { unit ->
+ // Operations on hours, minutes, and seconds return DATETIME for
DATE. Use its upper limit.
+ def upperBoundary = column == "d" && !(unit in ["weeks", "days"])
+ ? "9999-12-31 23:59:59" : limits[1]
+ // Check both limits and both interval signs. All four predicates
must return the one row.
+ "order_qt_${column}_${unit}_limits"("""SELECT id FROM
test_date_comparison_boundary
+ WHERE ${unit}_sub(${column}, 1) < '${upperBoundary}'
+ AND ${unit}_add(${column}, 1) > '${limits[0]}'
+ AND ${unit}_add(${column}, -1) < '${upperBoundary}'
+ AND ${unit}_sub(${column}, -1) > '${limits[0]}'""")
+ // The results must be true, true, false, false, false, false.
+ // An invalid inverse must not replace these results with NULL.
+ def comparisons = ["<", "<=", ">", ">=", "=", "<=>"].collect { op
->
+ "${unit}_sub(${column}, 1) ${op} '${upperBoundary}'"
+ }.join(", ")
+ "order_qt_${column}_${unit}_comparisons"(
+ "SELECT ${comparisons} FROM test_date_comparison_boundary")
+ }
+ }
+
+ // A column can supply the upper limit. It cannot be checked by constant
folding during planning.
+ order_qt_column_boundary """SELECT id FROM test_date_comparison_boundary
+ WHERE days_sub(dv2, 1) < upper_bound"""
+
+ // SQL aliases and INTERVAL expressions bind to the same day arithmetic
functions.
+ order_qt_aliases """SELECT id FROM test_date_comparison_boundary
+ WHERE date_sub(dv2, 1) < '9999-12-31 23:59:59'
+ AND date_add(dv2, -1) < '9999-12-31 23:59:59'
+ AND subdate(dv2, 1) < '9999-12-31 23:59:59'
+ AND adddate(dv2, -1) < '9999-12-31 23:59:59'
+ AND dv2 - INTERVAL 1 DAY < '9999-12-31 23:59:59'
+ AND dv2 + INTERVAL 1 DAY > '0000-01-01 00:00:00'
+ AND timestampadd(DAY, -1, dv2) < '9999-12-31 23:59:59'
+ AND '9999-12-31 23:59:59' > days_sub(dv2, 1)"""
+
+ // Month and year arithmetic cannot be reversed because month lengths
differ.
+ // Smaller time units and compound intervals do not use the inverse
comparison rule.
+ order_qt_other_units """SELECT id FROM test_date_comparison_boundary
+ WHERE years_sub(dv2, 1) < '9999-12-31 23:59:59'
+ AND quarters_sub(dv2, 1) < '9999-12-31 23:59:59'
+ AND months_sub(dv2, 1) < '9999-12-31 23:59:59'
+ AND milliseconds_sub(dt, 1) < '9999-12-31 23:59:59.999999'
+ AND microseconds_sub(tz, 1) < '9999-12-31 23:59:59.999999+00:00'
+ AND nanoseconds_sub(ns, 1) < '2262-04-11 23:47:16.854775807'
+ AND dv2 - INTERVAL '1 00:00:00' DAY_SECOND < '9999-12-31 23:59:59'"""
+
+ // Each step must be checked. The outer inverse is valid; the inner
inverse exceeds the range.
+ order_qt_nested """SELECT id FROM test_date_comparison_boundary
+ WHERE days_sub(days_sub(dv2, 1), 1) < '9999-12-30 23:59:59'"""
+
+ // Valid inverse boundaries must still allow the existing optimization.
+ explain {
+ sql "SELECT id FROM test_date_comparison_boundary WHERE days_sub(dv2,
1) < '2026-11-14 15:03:12'"
+ notContains "days_sub"
+ contains "2026-11-15 15:03:12"
+ }
+ // Checking an optional rewrite must not send an invalid inverse to BE
constant folding.
+ def originalFoldByBe = sql("SELECT @@enable_fold_constant_by_be")[0][0]
+ try {
+ sql "SET enable_fold_constant_by_be = true"
+ order_qt_be_folding """SELECT id FROM test_date_comparison_boundary
+ WHERE days_sub(dv2, 1) < '9999-12-31 23:59:59'"""
+ } finally {
+ sql "SET enable_fold_constant_by_be = ${originalFoldByBe}"
+ }
+ // The fix must not hide an error in date arithmetic that the user
actually requested.
+ test {
+ sql """SELECT id FROM test_date_comparison_boundary
+ WHERE days_add(upper_bound, 1) > '0000-01-01 00:00:00'"""
+ exception "out of range"
+ }
+}
diff --git
a/regression-test/suites/nereids_rules_p0/expression/test_timestamptz_day_comparison_errors.groovy
b/regression-test/suites/nereids_rules_p0/expression/test_timestamptz_day_comparison_errors.groovy
new file mode 100644
index 00000000000..49f45ef65b0
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/expression/test_timestamptz_day_comparison_errors.groovy
@@ -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.
+
+suite("test_timestamptz_day_comparison_errors") {
+ def originalTimeZone = sql("SELECT @@time_zone")[0][0]
+ def originalFoldByBe = sql("SELECT @@enable_fold_constant_by_be")[0][0]
+ sql "DROP TABLE IF EXISTS test_timestamptz_day_comparison_errors"
+ sql """CREATE TABLE test_timestamptz_day_comparison_errors (
+ id INT, upper_ts TIMESTAMPTZ(6), lower_ts TIMESTAMPTZ(6)
+ ) DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES("replication_num"="1")"""
+ try {
+ sql "SET time_zone = 'UTC'"
+ sql """INSERT INTO test_timestamptz_day_comparison_errors VALUES
+ (1, '9999-12-31 23:00:00+00:00', '0000-01-01 01:00:00+00:00')"""
+ [false, true].each { foldByBe ->
+ sql "SET enable_fold_constant_by_be = ${foldByBe}"
+ sql "SET time_zone = '+08:00'"
+ // The inverse is valid and the rearranged comparison is TRUE. The
original DAY
+ // operation must nevertheless fail because upper_ts has local
year 10000.
+ "order_qt_upper_inverse_${foldByBe}"("""SELECT
+ CAST(days_add(CAST('9999-12-30 12:00:00+00:00' AS
TIMESTAMPTZ(6)), 1) AS STRING),
+ upper_ts > days_add(CAST('9999-12-30 12:00:00+00:00' AS
TIMESTAMPTZ(6)), 1)
+ FROM test_timestamptz_day_comparison_errors""")
+ test {
+ sql "SELECT days_sub(upper_ts, 1) FROM
test_timestamptz_day_comparison_errors"
+ exception "out of range"
+ }
+ ["days_sub(upper_ts, 1)", "days_add(upper_ts, -1)"].each {
arithmetic ->
+ [">", ">=", "<", "<=", "=", "<=>"].each { op ->
+ test {
+ sql """SELECT id FROM
test_timestamptz_day_comparison_errors
+ WHERE ${arithmetic} ${op} CAST('9999-12-30
12:00:00+00:00' AS TIMESTAMPTZ(6))"""
+ exception "out of range"
+ }
+ }
+ }
+ explain {
+ sql """SELECT id FROM test_timestamptz_day_comparison_errors
+ WHERE days_sub(upper_ts, 1) > CAST('9999-12-30
12:00:00+00:00' AS TIMESTAMPTZ(6))"""
+ contains "days_sub"
+ }
+ // Aliases and a commuted comparison must also retain the original
operation.
+ ["date_sub(upper_ts, 1)", "subdate(upper_ts, 1)", "upper_ts -
INTERVAL 1 DAY",
+ "timestampadd(DAY, -1, upper_ts)"].each { arithmetic ->
+ test {
+ sql """SELECT id FROM
test_timestamptz_day_comparison_errors
+ WHERE CAST('9999-12-30 12:00:00+00:00' AS
TIMESTAMPTZ(6)) < ${arithmetic}"""
+ exception "out of range"
+ }
+ }
+
+ sql "SET time_zone = '-08:00'"
+ // Symmetric lower-bound case: the UTC input is valid but its
local year is -1.
+ "order_qt_lower_inverse_${foldByBe}"("""SELECT
+ CAST(days_sub(CAST('0000-01-02 12:00:00+00:00' AS
TIMESTAMPTZ(6)), 1) AS STRING),
+ lower_ts < days_sub(CAST('0000-01-02 12:00:00+00:00' AS
TIMESTAMPTZ(6)), 1)
+ FROM test_timestamptz_day_comparison_errors""")
+ ["days_add(lower_ts, 1)", "days_sub(lower_ts, -1)"].each {
arithmetic ->
+ ["<", "<=", ">", ">=", "=", "<=>"].each { op ->
+ test {
+ sql """SELECT id FROM
test_timestamptz_day_comparison_errors
+ WHERE ${arithmetic} ${op} CAST('0000-01-02
12:00:00+00:00' AS TIMESTAMPTZ(6))"""
+ exception "out of range"
+ }
+ }
+ }
+ }
+ } finally {
+ sql "SET time_zone = '${originalTimeZone}'"
+ sql "SET enable_fold_constant_by_be = ${originalFoldByBe}"
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]