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]

Reply via email to