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
commit aec9b75635ed2f1266a537faa5ca6f699810c84b
Author: TengJianPing <18241664+jackte...@users.noreply.github.com>
AuthorDate: Thu Oct 19 14:29:47 2023 +0800
[fix](months_diff) fix wrong result of months_diff (#25577)
---
be/src/vec/runtime/vdatetime_value.h | 26 +++++++-----
.../data/nereids_function_p0/scalar_function/M.out | 48 ++++++++++++++++++++++
.../nereids_function_p0/scalar_function/M.groovy | 27 ++++++++++++
3 files changed, 91 insertions(+), 10 deletions(-)
diff --git a/be/src/vec/runtime/vdatetime_value.h
b/be/src/vec/runtime/vdatetime_value.h
index b3cc110529a..1f96d5e9e35 100644
--- a/be/src/vec/runtime/vdatetime_value.h
+++ b/be/src/vec/runtime/vdatetime_value.h
@@ -199,6 +199,7 @@ static constexpr uint32_t MIN_YEAR = 0;
static constexpr uint32_t DATEV2_YEAR_WIDTH = 23;
static constexpr uint32_t DATETIMEV2_YEAR_WIDTH = 18;
+static constexpr uint32_t DATETIMEV2_MONTH_WIDTH = 4;
static RE2 time_zone_offset_format_reg("^[+-]{1}\\d{2}\\:\\d{2}$");
@@ -1336,8 +1337,9 @@ int64_t datetime_diff(const DateV2Value<T0>& ts_value1,
const DateV2Value<T1>& t
int month = (ts_value2.year() - ts_value1.year()) * 12 +
(ts_value2.month() - ts_value1.month());
if constexpr (std::is_same_v<T0, T1>) {
- int shift_bits = DateV2Value<T0>::is_datetime ?
DATETIMEV2_YEAR_WIDTH + 5
- : DATEV2_YEAR_WIDTH
+ 5;
+ int shift_bits = DateV2Value<T0>::is_datetime
+ ? DATETIMEV2_YEAR_WIDTH +
DATETIMEV2_MONTH_WIDTH
+ : DATEV2_YEAR_WIDTH +
DATETIMEV2_MONTH_WIDTH;
decltype(ts_value2.to_date_int_val()) minus_one = -1;
if (month > 0) {
month -= ((ts_value2.to_date_int_val() & (minus_one >>
shift_bits)) <
@@ -1350,23 +1352,27 @@ int64_t datetime_diff(const DateV2Value<T0>& ts_value1,
const DateV2Value<T1>& t
auto ts1_int_value = ((uint64_t)ts_value1.to_date_int_val()) <<
TIME_PART_LENGTH;
if (month > 0) {
month -= ((ts_value2.to_date_int_val() &
- (uint64_minus_one >> (DATETIMEV2_YEAR_WIDTH + 5))) <
- (ts1_int_value & (uint64_minus_one >>
(DATETIMEV2_YEAR_WIDTH + 5))));
+ (uint64_minus_one >> (DATETIMEV2_YEAR_WIDTH +
DATETIMEV2_MONTH_WIDTH))) <
+ (ts1_int_value &
+ (uint64_minus_one >> (DATETIMEV2_YEAR_WIDTH +
DATETIMEV2_MONTH_WIDTH))));
} else if (month < 0) {
month += ((ts_value2.to_date_int_val() &
- (uint64_minus_one >> (DATETIMEV2_YEAR_WIDTH + 5))) >
- (ts1_int_value & (uint64_minus_one >>
(DATETIMEV2_YEAR_WIDTH + 5))));
+ (uint64_minus_one >> (DATETIMEV2_YEAR_WIDTH +
DATETIMEV2_MONTH_WIDTH))) >
+ (ts1_int_value &
+ (uint64_minus_one >> (DATETIMEV2_YEAR_WIDTH +
DATETIMEV2_MONTH_WIDTH))));
}
} else {
auto ts2_int_value = ((uint64_t)ts_value2.to_date_int_val()) <<
TIME_PART_LENGTH;
if (month > 0) {
- month -= ((ts2_int_value & (uint64_minus_one >>
(DATETIMEV2_YEAR_WIDTH + 5))) <
+ month -= ((ts2_int_value &
+ (uint64_minus_one >> (DATETIMEV2_YEAR_WIDTH +
DATETIMEV2_MONTH_WIDTH))) <
(ts_value1.to_date_int_val() &
- (uint64_minus_one >> (DATETIMEV2_YEAR_WIDTH + 5))));
+ (uint64_minus_one >> (DATETIMEV2_YEAR_WIDTH +
DATETIMEV2_MONTH_WIDTH))));
} else if (month < 0) {
- month += ((ts2_int_value & (uint64_minus_one >>
(DATETIMEV2_YEAR_WIDTH + 5))) >
+ month += ((ts2_int_value &
+ (uint64_minus_one >> (DATETIMEV2_YEAR_WIDTH +
DATETIMEV2_MONTH_WIDTH))) >
(ts_value1.to_date_int_val() &
- (uint64_minus_one >> (DATETIMEV2_YEAR_WIDTH + 5))));
+ (uint64_minus_one >> (DATETIMEV2_YEAR_WIDTH +
DATETIMEV2_MONTH_WIDTH))));
}
}
return month;
diff --git a/regression-test/data/nereids_function_p0/scalar_function/M.out
b/regression-test/data/nereids_function_p0/scalar_function/M.out
index 7f3edf72a29..2e71428c60d 100644
--- a/regression-test/data/nereids_function_p0/scalar_function/M.out
+++ b/regression-test/data/nereids_function_p0/scalar_function/M.out
@@ -2957,3 +2957,51 @@ March
-2588750725983543588
-2588750725983543588
+-- !bugfix_months_diff_1 --
+575
+
+-- !bugfix_months_diff_2 --
+575
+
+-- !bugfix_months_diff_3 --
+575
+
+-- !bugfix_months_diff_4 --
+1
+
+-- !bugfix_months_diff_5 --
+1
+
+-- !bugfix_months_diff_6 --
+0
+
+-- !bugfix_months_diff_7 --
+0
+
+-- !bugfix_months_diff_8 --
+0
+
+-- !bugfix_months_diff_9 --
+1
+
+-- !bugfix_months_diff_10 --
+1
+
+-- !bugfix_months_diff_11 --
+1
+
+-- !bugfix_months_diff_12 --
+0
+
+-- !bugfix_months_diff_13 --
+1
+
+-- !bugfix_months_diff_14 --
+1
+
+-- !bugfix_months_diff_16 --
+1
+
+-- !bugfix_months_diff_17 --
+1
+
diff --git
a/regression-test/suites/nereids_function_p0/scalar_function/M.groovy
b/regression-test/suites/nereids_function_p0/scalar_function/M.groovy
index 735d7303062..b7b09829bca 100644
--- a/regression-test/suites/nereids_function_p0/scalar_function/M.groovy
+++ b/regression-test/suites/nereids_function_p0/scalar_function/M.groovy
@@ -231,4 +231,31 @@ suite("nereids_scalar_fn_M") {
qt_sql_murmur_hash3_64_Varchar_notnull "select murmur_hash3_64(kvchrs1)
from fn_test_not_nullable order by kvchrs1"
qt_sql_murmur_hash3_64_String "select murmur_hash3_64(kstr) from
fn_test order by kstr"
qt_sql_murmur_hash3_64_String_notnull "select murmur_hash3_64(kstr)
from fn_test_not_nullable order by kstr"
+
+ // bugfix
+ qt_bugfix_months_diff_1 "select months_diff('2023-10-14 00:00:00',
'1975-10-18 00:00:00');"
+ qt_bugfix_months_diff_2 "select months_diff(cast('2023-10-14' as
datev2), cast('1975-10-18 00:00:00' as datetimev2));"
+ qt_bugfix_months_diff_3 "select months_diff(cast('2023-10-14 00:00:00'
as datetimev2), cast('1975-10-18' as datev2));"
+
+ // 2023.2, max day is 28
+ qt_bugfix_months_diff_4 "select months_diff('2023-02-28 00:00:00',
'2023-01-27 00:00:00');"
+ qt_bugfix_months_diff_5 "select months_diff('2023-02-28 00:00:00',
'2023-01-28 00:00:00');"
+ qt_bugfix_months_diff_6 "select months_diff('2023-02-28 00:00:00',
'2023-01-29 00:00:00');"
+ qt_bugfix_months_diff_7 "select months_diff('2023-02-28 00:00:00',
'2023-01-31 00:00:00');"
+
+ qt_bugfix_months_diff_8 "select months_diff('2023-03-27 00:00:00',
'2023-02-28 00:00:00');"
+ qt_bugfix_months_diff_9 "select months_diff('2023-03-28 00:00:00',
'2023-02-28 00:00:00');"
+
+ // 2023.3, max day is 31
+ // 2023.4, max day is 30
+ // 2023.5, max day is 31
+ qt_bugfix_months_diff_10 "select months_diff('2023-04-30 00:00:00',
'2023-03-29 00:00:00');"
+ qt_bugfix_months_diff_11 "select months_diff('2023-04-30 00:00:00',
'2023-03-30 00:00:00');"
+ qt_bugfix_months_diff_12 "select months_diff('2023-04-30 00:00:00',
'2023-03-31 00:00:00');"
+
+ qt_bugfix_months_diff_13 "select months_diff('2023-05-30 00:00:00',
'2023-04-29 00:00:00');"
+ qt_bugfix_months_diff_14 "select months_diff('2023-05-30 00:00:00',
'2023-04-30 00:00:00');"
+
+ qt_bugfix_months_diff_16 "select months_diff('2023-05-31 00:00:00',
'2023-04-29 00:00:00');"
+ qt_bugfix_months_diff_17 "select months_diff('2023-05-31 00:00:00',
'2023-04-30 00:00:00');"
}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
