This is an automated email from the ASF dual-hosted git repository.
kxiao pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.0 by this push:
new f86376a2674 [branch-2.0](function) fix some date functions #26475
#35080 #35104 (#35169)
f86376a2674 is described below
commit f86376a267445b3857d4fc74d2d55c1eff1833c5
Author: zclllyybb <[email protected]>
AuthorDate: Thu May 23 20:01:25 2024 +0800
[branch-2.0](function) fix some date functions #26475 #35080 #35104 (#35169)
Co-authored-by: zhiqiang <[email protected]>
---
be/src/common/status.h | 15 ++
.../function_date_or_datetime_computation.h | 39 ++-
be/src/vec/runtime/vdatetime_value.cpp | 74 +++---
be/src/vec/runtime/vdatetime_value.h | 55 +++-
.../data/correctness_p0/test_date_diff.out | 21 ++
.../test_date_or_datetime_computation_negative.out | 91 +++++++
.../correctness/test_date_function_const.groovy | 7 +-
.../suites/correctness_p0/test_date_diff.groovy | 32 +++
.../datetime_functions/test_date_function.groovy | 25 +-
...st_date_or_datetime_computation_negative.groovy | 276 +++++++++++++++++++++
10 files changed, 569 insertions(+), 66 deletions(-)
diff --git a/be/src/common/status.h b/be/src/common/status.h
index 32f60e72bae..39ff9bad581 100644
--- a/be/src/common/status.h
+++ b/be/src/common/status.h
@@ -552,6 +552,21 @@ inline std::string Status::to_string() const {
} \
} while (false)
+#define PROPAGATE_FALSE(stmt) \
+ do { \
+ if (UNLIKELY(!static_cast<bool>(stmt))) { \
+ return false; \
+ } \
+ } while (false)
+
+#define THROW_IF_ERROR(stmt) \
+ do { \
+ Status _status_ = (stmt); \
+ if (UNLIKELY(!_status_.ok())) { \
+ throw Exception(_status_); \
+ } \
+ } while (false)
+
#define RETURN_ERROR_IF_NON_VEC \
return Status::NotSupported("Non-vectorized engine is not supported since
Doris 2.0.");
diff --git a/be/src/vec/functions/function_date_or_datetime_computation.h
b/be/src/vec/functions/function_date_or_datetime_computation.h
index 609113a0bd5..b1ccce6b063 100644
--- a/be/src/vec/functions/function_date_or_datetime_computation.h
+++ b/be/src/vec/functions/function_date_or_datetime_computation.h
@@ -27,6 +27,8 @@
#include <type_traits>
#include <utility>
+#include "common/compiler_util.h"
+#include "common/exception.h"
#include "common/logging.h"
#include "common/status.h"
#include "fmt/format.h"
@@ -413,7 +415,11 @@ struct DateTimeOp {
// otherwise it will be implicitly converted to bool, causing the
rvalue to fail to match the lvalue.
// the same goes for the following.
vec_to[i] = Transform::execute(vec_from0[i], vec_from1[i],
invalid);
- DCHECK(!invalid);
+
+ if (UNLIKELY(invalid)) {
+ throw Exception(ErrorCode::OUT_OF_BOUND, "Operation {} {} {}
out of range",
+ Transform::name, vec_from0[i], vec_from1[i]);
+ }
}
}
@@ -438,7 +444,11 @@ struct DateTimeOp {
bool invalid = true;
for (size_t i = 0; i < size; ++i) {
vec_to[i] = Transform::execute(vec_from0[i], vec_from1[i],
invalid);
- DCHECK(!invalid);
+
+ if (UNLIKELY(invalid)) {
+ throw Exception(ErrorCode::OUT_OF_BOUND, "Operation {} {} {}
out of range",
+ Transform::name, vec_from0[i], vec_from1[i]);
+ }
}
}
@@ -462,7 +472,11 @@ struct DateTimeOp {
bool invalid = true;
for (size_t i = 0; i < size; ++i) {
vec_to[i] = Transform::execute(vec_from[i], delta, invalid);
- DCHECK(!invalid);
+
+ if (UNLIKELY(invalid)) {
+ throw Exception(ErrorCode::OUT_OF_BOUND, "Operation {} {} {}
out of range",
+ Transform::name, vec_from[i], delta);
+ }
}
}
@@ -486,7 +500,11 @@ struct DateTimeOp {
for (size_t i = 0; i < size; ++i) {
vec_to[i] = Transform::execute(vec_from[i], delta, invalid);
- DCHECK(!invalid);
+
+ if (UNLIKELY(invalid)) {
+ throw Exception(ErrorCode::OUT_OF_BOUND, "Operation {} {} {}
out of range",
+ Transform::name, vec_from[i], delta);
+ }
}
}
@@ -510,7 +528,11 @@ struct DateTimeOp {
for (size_t i = 0; i < size; ++i) {
vec_to[i] = Transform::execute(from, delta.get_int(i), invalid);
- DCHECK(!invalid);
+
+ if (UNLIKELY(invalid)) {
+ throw Exception(ErrorCode::OUT_OF_BOUND, "Operation {} {} {}
out of range",
+ Transform::name, from, delta.get_int(i));
+ }
}
}
@@ -524,6 +546,7 @@ struct DateTimeOp {
vec_to[i] = Transform::execute(from, delta[i],
reinterpret_cast<bool&>(null_map[i]));
}
}
+
static void constant_vector(const FromType1& from, PaddedPODArray<ToType>&
vec_to,
const PaddedPODArray<FromType2>& delta) {
size_t size = delta.size();
@@ -532,7 +555,11 @@ struct DateTimeOp {
for (size_t i = 0; i < size; ++i) {
vec_to[i] = Transform::execute(from, delta[i], invalid);
- DCHECK(!invalid);
+
+ if (UNLIKELY(invalid)) {
+ throw Exception(ErrorCode::OUT_OF_BOUND, "Operation {} {} {}
out of range",
+ Transform::name, from, delta[i]);
+ }
}
}
};
diff --git a/be/src/vec/runtime/vdatetime_value.cpp
b/be/src/vec/runtime/vdatetime_value.cpp
index 51cb87f100c..fa82f05c64f 100644
--- a/be/src/vec/runtime/vdatetime_value.cpp
+++ b/be/src/vec/runtime/vdatetime_value.cpp
@@ -42,8 +42,6 @@
namespace doris::vectorized {
-static constexpr int s_days_in_month[13] = {0, 31, 28, 31, 30, 31, 30, 31, 31,
30, 31, 30, 31};
-
static const char* s_ab_month_name[] = {"", "Jan", "Feb", "Mar", "Apr",
"May", "Jun",
"Jul", "Aug", "Sep", "Oct", "Nov",
"Dec", nullptr};
@@ -74,7 +72,7 @@ bool VecDateTimeValue::check_range(uint32_t year, uint32_t
month, uint32_t day,
bool VecDateTimeValue::check_date(uint32_t year, uint32_t month, uint32_t day)
{
if (month == 2 && day == 29 && doris::is_leap(year)) return false;
- if (year > 9999 || month == 0 || month > 12 || day >
s_days_in_month[month] || day == 0) {
+ if (year > 9999 || month == 0 || month > 12 || day >
S_DAYS_IN_MONTH[month] || day == 0) {
return true;
}
return false;
@@ -520,8 +518,8 @@ bool VecDateTimeValue::get_date_from_daynr(uint64_t daynr) {
}
}
month = 1;
- while (days_of_year > s_days_in_month[month]) {
- days_of_year -= s_days_in_month[month];
+ while (days_of_year > S_DAYS_IN_MONTH[month]) {
+ days_of_year -= S_DAYS_IN_MONTH[month];
month++;
}
day = days_of_year + leap_day;
@@ -1675,8 +1673,8 @@ bool VecDateTimeValue::date_add_interval(const
TimeInterval& interval) {
return false;
}
_month = (months % 12) + 1;
- if (_day > s_days_in_month[_month]) {
- _day = s_days_in_month[_month];
+ if (_day > S_DAYS_IN_MONTH[_month]) {
+ _day = S_DAYS_IN_MONTH[_month];
if (_month == 2 && doris::is_leap(_year)) {
_day++;
}
@@ -1927,11 +1925,11 @@ bool DateV2Value<T>::is_invalid(uint32_t year, uint32_t
month, uint32_t day, uin
if (only_time_part) {
return false;
}
- if (year < MIN_YEAR || year > MAX_YEAR) {
+ if (year > MAX_YEAR) {
return true;
}
if (month == 2 && day == 29 && doris::is_leap(year)) return false;
- if (month == 0 || month > 12 || day > s_days_in_month[month] || day == 0) {
+ if (month == 0 || month > 12 || day > S_DAYS_IN_MONTH[month] || day == 0) {
return true;
}
return false;
@@ -1956,7 +1954,7 @@ void DateV2Value<T>::format_datetime(uint32_t* date_val,
bool* carry_bits) const
date_val[1] += 1;
carry_bits[2] = true;
}
- } else if (date_val[2] == s_days_in_month[date_val[1]] + 1 &&
carry_bits[3]) {
+ } else if (date_val[2] == S_DAYS_IN_MONTH[date_val[1]] + 1 &&
carry_bits[3]) {
date_val[2] = 1;
date_val[1] += 1;
carry_bits[2] = true;
@@ -2925,8 +2923,8 @@ bool DateV2Value<T>::get_date_from_daynr(uint64_t daynr) {
}
}
month = 1;
- while (days_of_year > s_days_in_month[month]) {
- days_of_year -= s_days_in_month[month];
+ while (days_of_year > S_DAYS_IN_MONTH[month]) {
+ days_of_year -= S_DAYS_IN_MONTH[month];
month++;
}
day = days_of_year + leap_day;
@@ -2983,30 +2981,27 @@ bool DateV2Value<T>::date_add_interval(const
TimeInterval& interval, DateV2Value
to_value.set_time(seconds / 3600, (seconds / 60) % 60, seconds % 60,
microseconds);
} else if constexpr (unit == YEAR) {
// This only change year information
- to_value.template set_time_unit<TimeUnit::YEAR>(date_v2_value_.year_ +
interval.year);
- if (to_value.year() > 9999) {
- return false;
- }
+ PROPAGATE_FALSE(to_value.template
set_time_unit<TimeUnit::YEAR>(date_v2_value_.year_ +
+
interval.year));
if (date_v2_value_.month_ == 2 && date_v2_value_.day_ == 29 &&
!doris::is_leap(to_value.year())) {
- to_value.template set_time_unit<TimeUnit::DAY>(28);
+ // add year. so if from Leap Year to Equal Year, use last day of
Feb(29 to 28)
+ PROPAGATE_FALSE(to_value.template
set_time_unit<TimeUnit::DAY>(28));
}
} else if constexpr (unit == QUARTER || unit == MONTH || unit ==
YEAR_MONTH) {
// This will change month and year information, maybe date.
int64_t months = date_v2_value_.year_ * 12 + date_v2_value_.month_ - 1
+
12 * interval.year + interval.month;
- to_value.template set_time_unit<TimeUnit::YEAR>(months / 12);
if (months < 0) {
return false;
}
- if (to_value.year() > MAX_YEAR) {
- return false;
- }
- to_value.template set_time_unit<TimeUnit::MONTH>((months % 12) + 1);
- if (date_v2_value_.day_ > s_days_in_month[to_value.month()]) {
- date_v2_value_.day_ = s_days_in_month[to_value.month()];
+ PROPAGATE_FALSE(to_value.template set_time_unit<TimeUnit::YEAR>(months
/ 12));
+ PROPAGATE_FALSE(to_value.template
set_time_unit<TimeUnit::MONTH>((months % 12) + 1));
+ if (date_v2_value_.day_ > S_DAYS_IN_MONTH[to_value.month()]) {
+ date_v2_value_.day_ = S_DAYS_IN_MONTH[to_value.month()];
if (to_value.month() == 2 && doris::is_leap(to_value.year())) {
- to_value.template
set_time_unit<TimeUnit::DAY>(date_v2_value_.day_ + 1);
+ PROPAGATE_FALSE(
+ to_value.template
set_time_unit<TimeUnit::DAY>(date_v2_value_.day_ + 1));
}
}
}
@@ -3057,30 +3052,27 @@ bool DateV2Value<T>::date_add_interval(const
TimeInterval& interval) {
}
} else if constexpr (unit == YEAR) {
// This only change year information
- this->template set_time_unit<TimeUnit::YEAR>(date_v2_value_.year_ +
interval.year);
- if (this->year() > 9999) {
- return false;
- }
+ PROPAGATE_FALSE(
+ this->template
set_time_unit<TimeUnit::YEAR>(date_v2_value_.year_ + interval.year));
if (date_v2_value_.month_ == 2 && date_v2_value_.day_ == 29 &&
!doris::is_leap(this->year())) {
- this->template set_time_unit<TimeUnit::DAY>(28);
+ // add year. so if from Leap Year to Equal Year, use last day of
Feb(29 to 28)
+ PROPAGATE_FALSE(this->template set_time_unit<TimeUnit::DAY>(28));
}
} else if constexpr (unit == QUARTER || unit == MONTH || unit ==
YEAR_MONTH) {
// This will change month and year information, maybe date.
int64_t months = date_v2_value_.year_ * 12 + date_v2_value_.month_ - 1
+
12 * interval.year + interval.month;
- this->template set_time_unit<TimeUnit::YEAR>(months / 12);
if (months < 0) {
return false;
}
- if (this->year() > MAX_YEAR) {
- return false;
- }
- this->template set_time_unit<TimeUnit::MONTH>((months % 12) + 1);
- if (date_v2_value_.day_ > s_days_in_month[this->month()]) {
- date_v2_value_.day_ = s_days_in_month[this->month()];
+ PROPAGATE_FALSE(this->template set_time_unit<TimeUnit::YEAR>(months /
12));
+ PROPAGATE_FALSE(this->template set_time_unit<TimeUnit::MONTH>((months
% 12) + 1));
+ if (date_v2_value_.day_ > S_DAYS_IN_MONTH[this->month()]) {
+ date_v2_value_.day_ = S_DAYS_IN_MONTH[this->month()];
if (this->month() == 2 && doris::is_leap(this->year())) {
- this->template
set_time_unit<TimeUnit::DAY>(date_v2_value_.day_ + 1);
+ PROPAGATE_FALSE(
+ this->template
set_time_unit<TimeUnit::DAY>(date_v2_value_.day_ + 1));
}
}
}
@@ -3109,13 +3101,13 @@ bool DateV2Value<T>::date_set_interval(const
TimeInterval& interval) {
}
} else if constexpr (unit == YEAR) {
this->set_time(0, 1, 1, 0, 0, 0, 0);
- this->template set_time_unit<TimeUnit::YEAR>(interval.year);
+ PROPAGATE_FALSE(this->template
set_time_unit<TimeUnit::YEAR>(interval.year));
} else if constexpr (unit == MONTH) {
// This will change month and year information, maybe date.
this->set_time(0, 1, 1, 0, 0, 0, 0);
int64_t months = 12 * interval.year + interval.month;
- this->template set_time_unit<TimeUnit::YEAR>(months / 12);
- this->template set_time_unit<TimeUnit::MONTH>((months % 12) + 1);
+ PROPAGATE_FALSE(this->template set_time_unit<TimeUnit::YEAR>(months /
12));
+ PROPAGATE_FALSE(this->template set_time_unit<TimeUnit::MONTH>((months
% 12) + 1));
}
return true;
}
diff --git a/be/src/vec/runtime/vdatetime_value.h
b/be/src/vec/runtime/vdatetime_value.h
index bbfbbb28226..ec4e8e11848 100644
--- a/be/src/vec/runtime/vdatetime_value.h
+++ b/be/src/vec/runtime/vdatetime_value.h
@@ -19,11 +19,11 @@
#include <glog/logging.h>
#include <re2/re2.h>
-#include <stdint.h>
-#include <string.h>
#include <algorithm>
#include <cstddef>
+#include <cstdint>
+#include <cstring>
#include <iostream>
#include <iterator>
#include <string>
@@ -162,6 +162,8 @@ constexpr int HOUR_PER_DAY = 24;
constexpr int64_t SECOND_PER_HOUR = 3600;
constexpr int64_t SECOND_PER_MINUTE = 60;
+inline constexpr int S_DAYS_IN_MONTH[13] = {0, 31, 28, 31, 30, 31, 30, 31, 31,
30, 31, 30, 31};
+
constexpr size_t const_length(const char* str) {
return (str == nullptr || *str == 0) ? 0 : const_length(str + 1) + 1;
}
@@ -195,7 +197,11 @@ static constexpr uint64_t MAX_DATETIME_V2 =
((uint64_t)MAX_DATE_V2 << TIME_PART_
static constexpr uint64_t MIN_DATETIME_V2 = (uint64_t)MIN_DATE_V2 <<
TIME_PART_LENGTH;
static constexpr uint32_t MAX_YEAR = 9999;
-static constexpr uint32_t MIN_YEAR = 0;
+static constexpr uint32_t MAX_MONTH = 12;
+static constexpr uint32_t MAX_HOUR = 23;
+static constexpr uint32_t MAX_MINUTE = 59;
+static constexpr uint32_t MAX_SECOND = 59;
+static constexpr uint32_t MAX_MICROSECOND = 999999;
static constexpr uint32_t DATEV2_YEAR_WIDTH = 23;
static constexpr uint32_t DATETIMEV2_YEAR_WIDTH = 18;
@@ -1150,31 +1156,58 @@ public:
bool get_date_from_daynr(uint64_t);
+ // should do check
template <TimeUnit unit>
- void set_time_unit(uint32_t val) {
+ bool set_time_unit(uint32_t val) {
+ // is uint so need check upper bound only
if constexpr (unit == TimeUnit::YEAR) {
+ if (val > MAX_YEAR) [[unlikely]] {
+ return false;
+ }
date_v2_value_.year_ = val;
} else if constexpr (unit == TimeUnit::MONTH) {
+ if (val > MAX_MONTH) [[unlikely]] {
+ return false;
+ }
date_v2_value_.month_ = val;
} else if constexpr (unit == TimeUnit::DAY) {
+ DCHECK(date_v2_value_.month_ <= MAX_MONTH);
+ DCHECK(date_v2_value_.month_ != 0);
+ if (val > S_DAYS_IN_MONTH[date_v2_value_.month_] &&
+ !(is_leap(date_v2_value_.year_) && date_v2_value_.month_ == 2
&& val == 29)) {
+ return false;
+ }
date_v2_value_.day_ = val;
} else if constexpr (unit == TimeUnit::HOUR) {
if constexpr (is_datetime) {
+ if (val > MAX_HOUR) [[unlikely]] {
+ return false;
+ }
date_v2_value_.hour_ = val;
}
} else if constexpr (unit == TimeUnit::MINUTE) {
if constexpr (is_datetime) {
+ if (val > MAX_MINUTE) [[unlikely]] {
+ return false;
+ }
date_v2_value_.minute_ = val;
}
} else if constexpr (unit == TimeUnit::SECOND) {
if constexpr (is_datetime) {
+ if (val > MAX_SECOND) [[unlikely]] {
+ return false;
+ }
date_v2_value_.second_ = val;
}
- } else if constexpr (unit == TimeUnit::SECOND_MICROSECOND) {
+ } else if constexpr (unit == TimeUnit::MICROSECOND) {
if constexpr (is_datetime) {
+ if (val > MAX_MICROSECOND) [[unlikely]] {
+ return false;
+ }
date_v2_value_.microsecond_ = val;
}
}
+ return true;
}
operator int64_t() const { return to_int64(); }
@@ -1397,10 +1430,22 @@ int64_t datetime_diff(const DateV2Value<T0>& ts_value1,
const DateV2Value<T1>& t
}
case WEEK: {
int day = ts_value2.daynr() - ts_value1.daynr();
+ int64_t ms_diff = ts_value2.time_part_diff_microsecond(ts_value1);
+ if (day > 0 && ms_diff < 0) {
+ day--;
+ } else if (day < 0 && ms_diff > 0) {
+ day++;
+ }
return day / 7;
}
case DAY: {
int day = ts_value2.daynr() - ts_value1.daynr();
+ int64_t ms_diff = ts_value2.time_part_diff_microsecond(ts_value1);
+ if (day > 0 && ms_diff < 0) {
+ day--;
+ } else if (day < 0 && ms_diff > 0) {
+ day++;
+ }
return day;
}
case HOUR: {
diff --git a/regression-test/data/correctness_p0/test_date_diff.out
b/regression-test/data/correctness_p0/test_date_diff.out
new file mode 100644
index 00000000000..4f2470fe4ee
--- /dev/null
+++ b/regression-test/data/correctness_p0/test_date_diff.out
@@ -0,0 +1,21 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !days --
+0
+0
+1
+2
+3
+4
+5
+6
+
+-- !weeks --
+0
+0
+0
+0
+0
+0
+0
+0
+
diff --git
a/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_or_datetime_computation_negative.out
b/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_or_datetime_computation_negative.out
new file mode 100644
index 00000000000..9a3dd1db9cc
--- /dev/null
+++
b/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_or_datetime_computation_negative.out
@@ -0,0 +1,91 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select_nullable_1 --
+\N \N \N
+\N \N \N
+9998-12-31 9998-12-31 9998-12-31T23:59:59
+\N \N \N
+
+-- !select_nullable_2 --
+\N \N \N
+\N \N \N
+9999-11-30 9999-11-30 9999-11-30T23:59:59
+\N \N \N
+
+-- !select_nullable_3 --
+\N \N \N
+\N \N \N
+9999-12-24 9999-12-24 9999-12-24T23:59:59
+\N \N \N
+
+-- !select_nullable_4 --
+\N \N \N
+\N \N \N
+9999-12-30 9999-12-30 9999-12-30T23:59:59
+\N \N \N
+
+-- !select_nullable_5 --
+\N \N \N
+\N \N \N
+9999-12-30T23:00 9999-12-30T23:00 9999-12-31T22:59:59
+\N \N \N
+
+-- !select_nullable_6 --
+\N \N \N
+\N \N \N
+9999-12-30T23:59 9999-12-30T23:59 9999-12-31T23:58:59
+\N \N \N
+
+-- !select_nullable_7 --
+\N \N \N
+\N \N \N
+9999-12-30T23:59:59 9999-12-30T23:59:59 9999-12-31T23:59:58
+\N \N \N
+
+-- !select_nullable_8 --
+0001-01-01 0001-01-01 0001-01-01T00:00
+\N \N \N
+\N \N \N
+\N \N \N
+
+-- !select_nullable_9 --
+0000-02-01 0000-02-01 0000-02-01T00:00
+\N \N \N
+\N \N \N
+\N \N \N
+
+-- !select_nullable_10 --
+0000-01-08 0000-01-08 0000-01-08T00:00
+\N \N \N
+\N \N \N
+\N \N \N
+
+-- !select_nullable_11 --
+0000-01-02 0000-01-02 0000-01-02T00:00
+\N \N \N
+\N \N \N
+\N \N \N
+
+-- !select_nullable_12 --
+0000-01-01T01:00 0000-01-01T01:00 0000-01-01T01:00
+\N \N \N
+9999-12-31T01:00 9999-12-31T01:00 \N
+\N \N \N
+
+-- !select_nullable_13 --
+0000-01-01T00:01 0000-01-01T00:01 0000-01-01T00:01
+\N \N \N
+9999-12-31T00:01 9999-12-31T00:01 \N
+\N \N \N
+
+-- !select_nullable_14 --
+0000-01-01T00:00:01 0000-01-01T00:00:01 0000-01-01T00:00:01
+\N \N \N
+9999-12-31T00:00:01 9999-12-31T00:00:01 \N
+\N \N \N
+
+-- !select_nullable_15 --
+0000-01-02T00:00 0000-01-02T00:00 0000-01-02T00:00
+\N \N \N
+\N \N \N
+\N \N \N
+
diff --git a/regression-test/suites/correctness/test_date_function_const.groovy
b/regression-test/suites/correctness/test_date_function_const.groovy
index 2b423e4e933..53c80ed0e32 100644
--- a/regression-test/suites/correctness/test_date_function_const.groovy
+++ b/regression-test/suites/correctness/test_date_function_const.groovy
@@ -59,4 +59,9 @@ suite("test_date_function_const") {
sql("""select date_add(CURRENT_DATE(),-2);""")
notContains("00:00:00")
}
-}
\ No newline at end of file
+
+ test {
+ sql """select date_add("1900-01-01 12:00:00.123456", interval
10000000000 month);"""
+ exception "Operation months_add 133705200962757184 1410065408 out of
range"
+ }
+}
diff --git a/regression-test/suites/correctness_p0/test_date_diff.groovy
b/regression-test/suites/correctness_p0/test_date_diff.groovy
new file mode 100644
index 00000000000..91e9cef6eb3
--- /dev/null
+++ b/regression-test/suites/correctness_p0/test_date_diff.groovy
@@ -0,0 +1,32 @@
+// 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_diff") {
+ sql """
+ create table dt6(
+ k0 datetime(6) null
+ )
+ DISTRIBUTED BY HASH(`k0`) BUCKETS auto
+ properties("replication_num" = "1");
+ """
+ sql """
+ insert into dt6 values ("0000-01-01 12:00:00"), ("0000-01-02 12:00:00"),
("0000-01-03 12:00:00"), ("0000-01-04 12:00:00"),
+ ("0000-01-05 12:00:00"), ("0000-01-06 12:00:00"), ("0000-01-07
12:00:00"), ("0000-01-08 12:00:00");
+ """
+ qt_days """ select days_diff(k0, '0000-01-01 13:00:00') from dt6 order by
k0; """
+ qt_weeks """ select weeks_diff(k0, '0000-01-01 13:00:00') from dt6 order
by k0; """
+}
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function.groovy
b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function.groovy
index 9db6c65547e..9a66b90aa35 100644
---
a/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function.groovy
+++
b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function.groovy
@@ -594,31 +594,30 @@ suite("test_date_function") {
qt_sql """ select seconds_sub(test_time2,1) result from ${tableName}; """
// test last_day for vec
- sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """ DROP TABLE IF EXISTS test_time_add_sub_function; """
sql """
- CREATE TABLE IF NOT EXISTS ${tableName} (
- birth date,
- birth1 datev2,
- birth2 datetime,
+ CREATE TABLE IF NOT EXISTS test_time_add_sub_function (
+ birth date,
+ birth1 datev2,
+ birth2 datetime,
birth3 datetimev2)
UNIQUE KEY(birth, birth1, birth2, birth3)
DISTRIBUTED BY HASH (birth) BUCKETS 1
PROPERTIES( "replication_allocation" = "tag.location.default: 1");
"""
sql """
- insert into ${tableName} values
- ('2022-01-01', '2022-01-01', '2022-01-01 00:00:00', '2022-01-01
00:00:00'),
- ('2000-02-01', '2000-02-01', '2000-02-01 00:00:00', '2000-02-01
00:00:00.123'),
+ insert into test_time_add_sub_function values
+ ('2022-01-01', '2022-01-01', '2022-01-01 00:00:00', '2022-01-01
00:00:00'),
+ ('2000-02-01', '2000-02-01', '2000-02-01 00:00:00', '2000-02-01
00:00:00.123'),
('2022-02-27', '2022-02-27', '2022-02-27 00:00:00', '2022-02-27
00:00:00'),
('2022-02-28', '2022-02-28', '2022-02-28 23:59:59', '2022-02-28
23:59:59');"""
qt_sql """
- select last_day(birth), last_day(birth1),
- last_day(birth2), last_day(birth3)
- from ${tableName};
+ select last_day(birth), last_day(birth1),
+ last_day(birth2), last_day(birth3)
+ from test_time_add_sub_function;
"""
- sql """ DROP TABLE IF EXISTS ${tableName}; """
- sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """ DROP TABLE IF EXISTS test_time_add_sub_function; """
sql """
CREATE TABLE IF NOT EXISTS ${tableName} (
birth date,
diff --git
a/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_or_datetime_computation_negative.groovy
b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_or_datetime_computation_negative.groovy
new file mode 100644
index 00000000000..83431ede6ab
--- /dev/null
+++
b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_or_datetime_computation_negative.groovy
@@ -0,0 +1,276 @@
+// 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.
+test_date_or_datetime_computation_negative
+suite("test_date_or_datetime_computation_negative") {
+ sql """ CREATE TABLE IF NOT EXISTS
test_date_or_datetime_computation_negative (
+ `row_id` LARGEINT NOT NULL,
+ `date` DATE NOT NULL,
+ `date_null` DATE NULL,
+ `dateV2` DATEV2 NOT NULL,
+ `dateV2_null` DATEV2 NULL,
+ `datetime` DATETIME NOT NULL,
+ `datetime_null` DATETIME NULL, )
+ DUPLICATE KEY(`row_id`)
+ DISTRIBUTED BY HASH(`row_id`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql "set enable_insert_strict = false;"
+ sql "set parallel_fragment_exec_instance_num = 3;"
+ sql "set enable_nereids_planner = true;"
+
+ sql """INSERT INTO test_date_or_datetime_computation_negative VALUES (1,
'0000-01-01', '0000-01-01', '0000-01-01', '0000-01-01', '0000-01-01 00:00:00',
'0000-01-01 00:00:00');"""
+ sql """INSERT INTO test_date_or_datetime_computation_negative VALUES (2,
'0000-01-01', NULL, '0000-01-01', NULL, '0000-01-01 00:00:00', NULL);"""
+ sql """INSERT INTO test_date_or_datetime_computation_negative VALUES (3,
'9999-12-31', '9999-12-31', '9999-12-31', '9999-12-31', '9999-12-31 23:59:59',
'9999-12-31 23:59:59');"""
+ sql """INSERT INTO test_date_or_datetime_computation_negative VALUES (4,
'9999-12-31', NULL, '9999-12-31', NULL, '9999-12-31 23:59:59', NULL);"""
+
+ test {
+ sql """SELECT date_sub(date, interval 1 year) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_sub(dateV2, interval 1 year) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_sub(datetime, interval 1 year) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+ qt_select_nullable_1 """SELECT date_sub(date_null, interval 1 year),
date_sub(dateV2_null, interval 1 year), date_sub(datetime_null, interval 1
year) FROM test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+ test {
+ sql """SELECT date_sub(date, interval 1 month) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_sub(dateV2, interval 1 month) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_sub(datetime, interval 1 month) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+ qt_select_nullable_2 """SELECT date_sub(date_null, interval 1 month),
date_sub(dateV2_null, interval 1 month), date_sub(datetime_null, interval 1
month) FROM test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+ test {
+ sql """ SELECT date_sub(date, interval 1 week) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """ SELECT date_sub(dateV2, interval 1 week) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """ SELECT date_sub(datetime, interval 1 week) FROM
test_date_or_datetime_computation_negative WHERE row_id=1; """
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+
+ qt_select_nullable_3 """SELECT date_sub(date_null, interval 1 week),
date_sub(dateV2_null, interval 1 week), date_sub(datetime_null, interval 1
week) FROM test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+ test {
+ sql """SELECT date_sub(date, interval 1 day) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_sub(dateV2, interval 1 day) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_sub(datetime, interval 1 day) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+
+ qt_select_nullable_4 """SELECT date_sub(date_null, interval 1 day),
date_sub(dateV2_null, interval 1 day), date_sub(datetime_null, interval 1 day)
FROM test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+ test {
+ sql """SELECT date_sub(date, interval 1 hour) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_sub(dateV2, interval 1 hour) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_sub(datetime, interval 1 hour) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+ qt_select_nullable_5 """ SELECT date_sub(date_null, interval 1 hour),
date_sub(dateV2_null, interval 1 hour), date_sub(datetime_null, interval 1
hour) FROM test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+ test {
+ sql """SELECT date_sub(date, interval 1 minute) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_sub(dateV2, interval 1 minute) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_sub(datetime, interval 1 minute) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+ qt_select_nullable_6 """SELECT date_sub(date_null, interval 1 minute),
date_sub(dateV2_null, interval 1 minute), date_sub(datetime_null, interval 1
minute) FROM test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+ test {
+ sql """SELECT date_sub(date, interval 1 second) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_sub(dateV2, interval 1 second) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_sub(datetime, interval 1 second) FROM
test_date_or_datetime_computation_negative WHERE row_id=1;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+ qt_select_nullable_7 """SELECT date_sub(date_null, interval 1 second),
date_sub(dateV2_null, interval 1 second), date_sub(datetime_null, interval 1
second) FROM test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+
+ test {
+ sql """SELECT date_add(date, interval 1 year) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_add(dateV2, interval 1 year) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_add(datetime, interval 1 year) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+ qt_select_nullable_8 """SELECT date_add(date_null, interval 1 year),
date_add(dateV2_null, interval 1 year), date_add(datetime_null, interval 1
year) FROM test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+ test {
+ sql """SELECT date_add(date, interval 1 month) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_add(dateV2, interval 1 month) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_add(datetime, interval 1 month) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+ qt_select_nullable_9 """SELECT date_add(date_null, interval 1 month),
date_add(dateV2_null, interval 1 month), date_add(datetime_null, interval 1
month) FROM test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+ test {
+ sql """ SELECT date_add(date, interval 1 week) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """ SELECT date_add(dateV2, interval 1 week) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """ SELECT date_add(datetime, interval 1 week) FROM
test_date_or_datetime_computation_negative WHERE row_id=3; """
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+
+ qt_select_nullable_10 """SELECT date_add(date_null, interval 1 week),
date_add(dateV2_null, interval 1 week), date_add(datetime_null, interval 1
week) FROM test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+ test {
+ sql """SELECT date_add(date, interval 1 day) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_add(dateV2, interval 1 day) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_add(datetime, interval 1 day) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+
+ qt_select_nullable_11 """SELECT date_add(date_null, interval 1 day),
date_add(dateV2_null, interval 1 day), date_add(datetime_null, interval 1 day)
FROM test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+ test {
+ sql """SELECT date_add(date, interval 1 hour) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_add(dateV2, interval 1 hour) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_add(datetime, interval 1 hour) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+ qt_select_nullable_12 """ SELECT date_add(date_null, interval 1 hour),
date_add(dateV2_null, interval 1 hour), date_add(datetime_null, interval 1
hour) FROM test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+ test {
+ sql """SELECT date_add(date, interval 1 minute) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_add(dateV2, interval 1 minute) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_add(datetime, interval 1 minute) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+ qt_select_nullable_13 """SELECT date_add(date_null, interval 1 minute),
date_add(dateV2_null, interval 1 minute), date_add(datetime_null, interval 1
minute) FROM test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+ test {
+ sql """SELECT date_add(date, interval 1 second) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_add(dateV2, interval 1 second) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT date_add(datetime, interval 1 second) FROM
test_date_or_datetime_computation_negative WHERE row_id=3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+ qt_select_nullable_14 """SELECT date_add(date_null, interval 1 second),
date_add(dateV2_null, interval 1 second), date_add(datetime_null, interval 1
second) FROM test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+ // TODO:
+ // nagetive test for
microseconds_add/milliseconds_add/seconds_add/minutes_add/hours_add/days_add/weeks_add/months_add/years_add
+
+ test {
+ sql """SELECT hours_add(date, 24) FROM
test_date_or_datetime_computation_negative WHERE row_id = 3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT hours_add(dateV2, 24) FROM
test_date_or_datetime_computation_negative WHERE row_id = 3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+
+ sql """SELECT hours_add(datetime, 24) FROM
test_date_or_datetime_computation_negative WHERE row_id = 3;"""
+ check {result, exception, startTime, endTime ->
+ assertTrue (exception != null)}
+ }
+ qt_select_nullable_15 """SELECT hours_add(date_null, 24),
hours_add(dateV2_null, 24), hours_add(datetime_null, 24) FROM
test_date_or_datetime_computation_negative ORDER BY row_id;"""
+
+ sql "DROP TABLE test_date_or_datetime_computation_negative"
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
