This is an automated email from the ASF dual-hosted git repository. morningman pushed a commit to branch dev-1.0.1 in repository https://gitbox.apache.org/repos/asf/incubator-doris.git
commit 8ff79949131728ed54ae76c91e8c2cd665e87b36 Author: zhengshengjun <[email protected]> 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 44bc34bccb..ec968583e5 100644 --- a/be/src/exprs/timestamp_functions.cpp +++ b/be/src/exprs/timestamp_functions.cpp @@ -581,47 +581,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 79afa52f37..058af52a12 100644 --- a/be/src/vec/functions/function_datetime_floor_ceil.cpp +++ b/be/src/vec/functions/function_datetime_floor_ceil.cpp @@ -150,6 +150,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) { @@ -159,43 +161,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: [email protected] For additional commands, e-mail: [email protected]
