You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2022/06/06 00:58:30 UTC
[incubator-doris] branch master updated: [fix](function) fix bug in time_round function (#9712)
This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-doris.git
The following commit(s) were added to refs/heads/master by this push:
new 49d4798276 [fix](function) fix bug in time_round function (#9712)
49d4798276 is described below
commit 49d4798276bc0f2c21f68e4eb18fc8b5c0b95dea
Author: zhengshengjun <zh...@apache.org>
AuthorDate: Mon Jun 6 08:58:22 2022 +0800
[fix](function) fix bug in time_round function (#9712)
---
be/src/exprs/timestamp_functions.cpp | 55 ++++++++++----
.../vec/functions/function_datetime_floor_ceil.cpp | 63 +++++++++++-----
.../data/correctness/test_time_round.out | 85 ++++++++++++++++++++++
.../suites/correctness/test_time_round.groovy | 70 ++++++++++++++++++
4 files changed, 242 insertions(+), 31 deletions(-)
diff --git a/be/src/exprs/timestamp_functions.cpp b/be/src/exprs/timestamp_functions.cpp
index c84bbc8d6c..4ce6ce477e 100644
--- a/be/src/exprs/timestamp_functions.cpp
+++ b/be/src/exprs/timestamp_functions.cpp
@@ -584,47 +584,74 @@ DateTimeVal TimestampFunctions::time_round(FunctionContext* ctx, const DateTimeV
DateTimeValue ts1 = DateTimeValue::from_datetime_val(origin);
DateTimeValue ts2 = DateTimeValue::from_datetime_val(ts_val);
int64_t diff;
+ int64_t trivial_part_ts1;
+ int64_t trivial_part_ts2;
switch (unit) {
case YEAR: {
- int year = (ts2.year() - ts1.year());
- diff = year - (ts2.to_int64() % 10000000000 < ts1.to_int64() % 10000000000);
+ diff = (ts2.year() - ts1.year());
+ trivial_part_ts2 = ts2.to_int64() % 10000000000;
+ trivial_part_ts1 = ts1.to_int64() % 10000000000;
break;
}
case MONTH: {
- int month = (ts2.year() - ts1.year()) * 12 + (ts2.month() - ts1.month());
- diff = month - (ts2.to_int64() % 100000000 < ts1.to_int64() % 100000000);
+ diff = (ts2.year() - ts1.year()) * 12 + (ts2.month() - ts1.month());
+ trivial_part_ts2 = ts2.to_int64() % 100000000;
+ trivial_part_ts1 = ts1.to_int64() % 100000000;
break;
}
case WEEK: {
- int week = ts2.daynr() / 7 - ts1.daynr() / 7;
- diff = week - (ts2.daynr() % 7 < ts1.daynr() % 7 + (ts2.time_part_diff(ts1) < 0));
+ diff = ts2.daynr() / 7 - ts1.daynr() / 7;
+ trivial_part_ts2 =
+ ts2.daynr() % 7 * 24 * 3600 + ts2.hour() * 3600 + ts2.minute() * 60 + ts2.second();
+ trivial_part_ts1 =
+ ts1.daynr() % 7 * 24 * 3600 + ts1.hour() * 3600 + ts1.minute() * 60 + ts1.second();
break;
}
case DAY: {
- int day = ts2.daynr() - ts1.daynr();
- diff = day - (ts2.time_part_diff(ts1) < 0);
+ diff = ts2.daynr() - ts1.daynr();
+ trivial_part_ts2 = ts2.hour() * 3600 + ts2.minute() * 60 + ts2.second();
+ trivial_part_ts1 = ts1.hour() * 3600 + ts1.minute() * 60 + ts1.second();
break;
}
case HOUR: {
- int hour = (ts2.daynr() - ts1.daynr()) * 24 + (ts2.hour() - ts1.hour());
- diff = hour - ((ts2.minute() * 60 + ts2.second()) < (ts1.minute() * 60 - ts1.second()));
+ diff = (ts2.daynr() - ts1.daynr()) * 24 + (ts2.hour() - ts1.hour());
+ trivial_part_ts2 = ts2.minute() * 60 + ts2.second();
+ trivial_part_ts1 = ts1.minute() * 60 + ts1.second();
break;
}
case MINUTE: {
- int minute = (ts2.daynr() - ts1.daynr()) * 24 * 60 + (ts2.hour() - ts1.hour()) * 60 +
- (ts2.minute() - ts1.minute());
- diff = minute - (ts2.second() < ts1.second());
+ diff = (ts2.daynr() - ts1.daynr()) * 24 * 60 + (ts2.hour() - ts1.hour()) * 60 +
+ (ts2.minute() - ts1.minute());
+ trivial_part_ts2 = ts2.second();
+ trivial_part_ts1 = ts1.second();
break;
}
case SECOND: {
diff = ts2.second_diff(ts1);
+ trivial_part_ts1 = 0;
+ trivial_part_ts2 = 0;
break;
}
default:
return DateTimeVal::null();
}
+
+ //round down/up to specific time-unit(HOUR/DAY/MONTH...) by increase/decrease diff variable
+ if (type == CEIL) {
+ //e.g. hour_ceil(ts: 00:00:40, origin: 00:00:30), ts should be rounded to 01:00:30
+ diff += trivial_part_ts2 > trivial_part_ts1;
+ } else if (type == FLOOR) {
+ //e.g. hour_floor(ts: 01:00:20, origin: 00:00:30), ts should be rounded to 00:00:30
+ diff -= trivial_part_ts2 < trivial_part_ts1;
+ }
+
+ //round down/up inside time period(several time-units)
int64_t count = period.val;
- int64_t step = diff - (diff % count + count) % count + (type == FLOOR ? 0 : count);
+ int64_t delta_inside_period = (diff % count + count) % count;
+ int64_t step = diff - delta_inside_period +
+ (type == FLOOR ? 0
+ : delta_inside_period == 0 ? 0
+ : count);
bool is_neg = step < 0;
TimeInterval interval(unit, is_neg ? -step : step, is_neg);
diff --git a/be/src/vec/functions/function_datetime_floor_ceil.cpp b/be/src/vec/functions/function_datetime_floor_ceil.cpp
index abfd678e6b..42e331b9f0 100644
--- a/be/src/vec/functions/function_datetime_floor_ceil.cpp
+++ b/be/src/vec/functions/function_datetime_floor_ceil.cpp
@@ -163,6 +163,8 @@ struct TimeRound {
static constexpr auto name = Impl::name;
static constexpr uint64_t FIRST_DAY = 19700101000000;
static constexpr uint64_t FIRST_SUNDAY = 19700104000000;
+ static constexpr int8_t FLOOR = 0;
+ static constexpr int8_t CEIL = 1;
static void time_round(const doris::vectorized::VecDateTimeValue& ts2, Int32 period,
doris::vectorized::VecDateTimeValue& ts1, UInt8& is_null) {
@@ -172,43 +174,70 @@ struct TimeRound {
}
int64_t diff;
+ int64_t trivial_part_ts1;
+ int64_t trivial_part_ts2;
if constexpr (Impl::Unit == YEAR) {
- int year = (ts2.year() - ts1.year());
- diff = year - (ts2.to_int64() % 10000000000 < ts1.to_int64() % 10000000000);
+ diff = (ts2.year() - ts1.year());
+ trivial_part_ts2 = ts2.to_int64() % 10000000000;
+ trivial_part_ts1 = ts1.to_int64() % 10000000000;
}
if constexpr (Impl::Unit == MONTH) {
- int month = (ts2.year() - ts1.year()) * 12 + (ts2.month() - ts1.month());
- diff = month - (ts2.to_int64() % 100000000 < ts1.to_int64() % 100000000);
+ diff = (ts2.year() - ts1.year()) * 12 + (ts2.month() - ts1.month());
+ trivial_part_ts2 = ts2.to_int64() % 100000000;
+ trivial_part_ts1 = ts1.to_int64() % 100000000;
}
if constexpr (Impl::Unit == MONTH) {
- int month = (ts2.year() - ts1.year()) * 12 + (ts2.month() - ts1.month());
- diff = month - (ts2.to_int64() % 100000000 < ts1.to_int64() % 100000000);
+ diff = (ts2.year() - ts1.year()) * 12 + (ts2.month() - ts1.month());
+ trivial_part_ts2 = ts2.to_int64() % 100000000;
+ trivial_part_ts1 = ts1.to_int64() % 100000000;
}
if constexpr (Impl::Unit == WEEK) {
- int week = ts2.daynr() / 7 - ts1.daynr() / 7;
- diff = week - (ts2.daynr() % 7 < ts1.daynr() % 7 + (ts2.time_part_diff(ts1) < 0));
+ diff = ts2.daynr() / 7 - ts1.daynr() / 7;
+ trivial_part_ts2 = ts2.daynr() % 7 * 24 * 3600 + ts2.hour() * 3600 + ts2.minute() * 60 +
+ ts2.second();
+ trivial_part_ts1 = ts1.daynr() % 7 * 24 * 3600 + ts1.hour() * 3600 + ts1.minute() * 60 +
+ ts1.second();
}
if constexpr (Impl::Unit == DAY) {
- int day = ts2.daynr() - ts1.daynr();
- diff = day - (ts2.time_part_diff(ts1) < 0);
+ diff = ts2.daynr() - ts1.daynr();
+ trivial_part_ts2 = ts2.hour() * 3600 + ts2.minute() * 60 + ts2.second();
+ trivial_part_ts1 = ts1.hour() * 3600 + ts1.minute() * 60 + ts1.second();
}
if constexpr (Impl::Unit == HOUR) {
- int hour = (ts2.daynr() - ts1.daynr()) * 24 + (ts2.hour() - ts1.hour());
- diff = hour - ((ts2.minute() * 60 + ts2.second()) < (ts1.minute() * 60 - ts1.second()));
+ diff = (ts2.daynr() - ts1.daynr()) * 24 + (ts2.hour() - ts1.hour());
+ trivial_part_ts2 = ts2.minute() * 60 + ts2.second();
+ trivial_part_ts1 = ts1.minute() * 60 + ts1.second();
}
if constexpr (Impl::Unit == MINUTE) {
- int minute = (ts2.daynr() - ts1.daynr()) * 24 * 60 + (ts2.hour() - ts1.hour()) * 60 +
- (ts2.minute() - ts1.minute());
- diff = minute - (ts2.second() < ts1.second());
+ diff = (ts2.daynr() - ts1.daynr()) * 24 * 60 + (ts2.hour() - ts1.hour()) * 60 +
+ (ts2.minute() - ts1.minute());
+ trivial_part_ts2 = ts2.second();
+ trivial_part_ts1 = ts1.second();
}
if constexpr (Impl::Unit == SECOND) {
diff = ts2.second_diff(ts1);
+ trivial_part_ts1 = 0;
+ trivial_part_ts2 = 0;
}
+ //round down/up to specific time-unit(HOUR/DAY/MONTH...) by increase/decrease diff variable
+ if constexpr (Impl::Type == CEIL) {
+ //e.g. hour_ceil(ts: 00:00:40, origin: 00:00:30), ts should be rounded to 01:00:30
+ diff += trivial_part_ts2 > trivial_part_ts1;
+ }
+ if constexpr (Impl::Type == FLOOR) {
+ //e.g. hour_floor(ts: 01:00:20, origin: 00:00:30), ts should be rounded to 00:00:30
+ diff -= trivial_part_ts2 < trivial_part_ts1;
+ }
+
+ //round down/up inside time period(several time-units)
int64_t count = period;
- int64_t step = diff - (diff % count + count) % count + (Impl::Type == 0 ? 0 : count);
+ int64_t delta_inside_period = (diff % count + count) % count;
+ int64_t step = diff - delta_inside_period +
+ (Impl::Type == FLOOR ? 0
+ : delta_inside_period == 0 ? 0
+ : count);
bool is_neg = step < 0;
-
TimeInterval interval(Impl::Unit, is_neg ? -step : step, is_neg);
is_null = !ts1.date_add_interval(interval, Impl::Unit);
return;
diff --git a/regression-test/data/correctness/test_time_round.out b/regression-test/data/correctness/test_time_round.out
new file mode 100644
index 0000000000..a3d0aa9ebd
--- /dev/null
+++ b/regression-test/data/correctness/test_time_round.out
@@ -0,0 +1,85 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !select --
+1970-01-01T01:00:30
+
+-- !select --
+1970-01-01T00:00:30
+
+-- !select --
+2022-05-25T00:00
+
+-- !select --
+2022-05-01T00:00
+
+-- !select --
+2022-05-25T00:06
+
+-- !select --
+2022-05-25T00:05
+
+-- !select --
+2022-05-26T00:00
+
+-- !select --
+2022-05-23T00:00
+
+-- !select --
+2022-05-29T00:00
+
+-- !select --
+2022-05-22T00:00
+
+-- !select --
+2022-06-01T00:00
+
+-- !select --
+2022-05-01T00:00
+
+-- !select --
+2023-01-01T00:00
+
+-- !select --
+2022-01-01T00:00
+
+-- !select --
+1970-01-01T01:00:30
+
+-- !select --
+1970-01-01T00:00:30
+
+-- !select --
+2022-05-25T00:00
+
+-- !select --
+2022-05-01T00:00
+
+-- !select --
+2022-05-25T00:06
+
+-- !select --
+2022-05-25T00:05
+
+-- !select --
+2022-05-26T00:00
+
+-- !select --
+2022-05-23T00:00
+
+-- !select --
+2022-05-29T00:00
+
+-- !select --
+2022-05-22T00:00
+
+-- !select --
+2022-06-01T00:00
+
+-- !select --
+2022-05-01T00:00
+
+-- !select --
+2023-01-01T00:00
+
+-- !select --
+2022-01-01T00:00
+
diff --git a/regression-test/suites/correctness/test_time_round.groovy b/regression-test/suites/correctness/test_time_round.groovy
new file mode 100644
index 0000000000..7e3b7d1891
--- /dev/null
+++ b/regression-test/suites/correctness/test_time_round.groovy
@@ -0,0 +1,70 @@
+// 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_time_round") {
+
+ sql """ set enable_vectorized_engine = false """
+ // fix by issues/9711, expect: '1970-01-01T01:00:30'
+ qt_select "select hour_ceil('1970-01-01 01:00:10', 1, '1970-01-01 00:00:30')"
+
+ // fix by issues/9711, expect: '1970-01-01T00:00:30'
+ qt_select "select hour_floor('1970-01-01 01:00:10', 1, '1970-01-01 00:00:30')"
+
+ // fix by issues/9711, expect: '2022-05-25'
+ qt_select "select day_ceil('2022-05-25')"
+
+ // fix by issues/9711, expect: '2022-05-01'
+ qt_select "select month_ceil('2022-05-01')"
+
+ qt_select "select minute_ceil('2022-05-25 00:05:10')"
+ qt_select "select minute_floor('2022-05-25 00:05:10')"
+ qt_select "select day_ceil('2022-05-25 02:00:00', 3, '2022-05-20 00:00:00')"
+ qt_select "select day_floor('2022-05-25 02:00:00', 3, '2022-05-20 00:00:00')"
+ qt_select "select week_ceil('2022-05-25 00:00:00')"
+ qt_select "select week_floor('2022-05-25 00:00:00')"
+ qt_select "select month_ceil('2022-05-25 00:00:00')"
+ qt_select "select month_floor('2022-05-25 00:00:00')"
+ qt_select "select year_ceil('2022-05-25 00:00:00')"
+ qt_select "select year_floor('2022-05-25 00:00:00')"
+
+
+ sql """ set enable_vectorized_engine = true """
+ // fix by issues/9711, expect: '1970-01-01T01:00:30'
+ qt_select "select hour_ceil('1970-01-01 01:00:10', 1, '1970-01-01 00:00:30')"
+
+ // fix by issues/9711, expect: '1970-01-01T00:00:30'
+ qt_select "select hour_floor('1970-01-01 01:00:10', 1, '1970-01-01 00:00:30')"
+
+ // fix by issues/9711, expect: '2022-05-25'
+ qt_select "select day_ceil('2022-05-25')"
+
+ // fix by issues/9711, expect: '2022-05-01'
+ qt_select "select month_ceil('2022-05-01')"
+
+ qt_select "select minute_ceil('2022-05-25 00:05:10')"
+ qt_select "select minute_floor('2022-05-25 00:05:10')"
+ qt_select "select day_ceil('2022-05-25 02:00:00', 3, '2022-05-20 00:00:00')"
+ qt_select "select day_floor('2022-05-25 02:00:00', 3, '2022-05-20 00:00:00')"
+ qt_select "select week_ceil('2022-05-25 00:00:00')"
+ qt_select "select week_floor('2022-05-25 00:00:00')"
+ qt_select "select month_ceil('2022-05-25 00:00:00')"
+ qt_select "select month_floor('2022-05-25 00:00:00')"
+ qt_select "select year_ceil('2022-05-25 00:00:00')"
+ qt_select "select year_floor('2022-05-25 00:00:00')"
+
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org