You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2024/10/24 03:09:43 UTC
(doris) branch branch-2.1 updated: [feature](function) hour/minute/second functions support time as an a… 41008 (#42232)
This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.1 by this push:
new cbdaaa62b23 [feature](function) hour/minute/second functions support time as an a… 41008 (#42232)
cbdaaa62b23 is described below
commit cbdaaa62b2327b7a3343fe05923cb51000b3a3b9
Author: Mryange <59...@users.noreply.github.com>
AuthorDate: Thu Oct 24 11:09:36 2024 +0800
[feature](function) hour/minute/second functions support time as an a… 41008 (#42232)
…rgument. (#41008)
## Proposed changes
```
mysql> select cast(4562632 as time),hour(cast(4562632 as time)), minute(cast(4562632 as time)),second(cast(4562632 as time));
+-----------------------+-----------------------------+-------------------------------+-------------------------------+
| cast(4562632 as TIME) | hour(cast(4562632 as TIME)) | minute(cast(4562632 as TIME)) | second(cast(4562632 as TIME)) |
+-----------------------+-----------------------------+-------------------------------+-------------------------------+
| 456:26:32 | 456 | 26 | 32 |
+-----------------------+-----------------------------+-------------------------------+-------------------------------+
```
<!--Describe your changes.-->
---------
## Proposed changes
Issue Number: close #xxx
<!--Describe your changes.-->
Co-authored-by: Dongyang Li <he...@qq.com>
---
.../vec/functions/function_time_value_to_field.cpp | 94 ++++++++++++++++++++++
be/src/vec/functions/simple_function_factory.h | 2 +
be/src/vec/runtime/time_value.h | 36 ++++++++-
.../trees/expressions/functions/scalar/Hour.java | 6 +-
.../trees/expressions/functions/scalar/Minute.java | 5 +-
.../trees/expressions/functions/scalar/Second.java | 5 +-
.../datetime_functions/test_date_function.out | 14 ++++
.../datetime_functions/test_date_function.groovy | 18 +++++
8 files changed, 173 insertions(+), 7 deletions(-)
diff --git a/be/src/vec/functions/function_time_value_to_field.cpp b/be/src/vec/functions/function_time_value_to_field.cpp
new file mode 100644
index 00000000000..da5fbca58cd
--- /dev/null
+++ b/be/src/vec/functions/function_time_value_to_field.cpp
@@ -0,0 +1,94 @@
+// 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.
+
+#include <memory>
+#include <variant>
+
+#include "common/status.h"
+#include "vec/data_types/data_type_number.h"
+#include "vec/data_types/data_type_time.h"
+#include "vec/functions/function.h"
+#include "vec/functions/simple_function_factory.h"
+#include "vec/runtime/time_value.h"
+#include "vec/utils/template_helpers.hpp"
+namespace doris::vectorized {
+
+template <typename ToDataType, typename Transform>
+class FunctionTimeValueToField : public IFunction {
+public:
+ static constexpr auto name = Transform::name;
+ static FunctionPtr create() { return std::make_shared<FunctionTimeValueToField>(); }
+ String get_name() const override { return name; }
+
+ // is_variadic and get_number_of_arguments are consistent with FunctionDateOrDateTimeToSomething,
+ // as FunctionDateOrDateTimeToSomething supports Date and DateTime types as arguments.
+ bool is_variadic() const override { return true; }
+ size_t get_number_of_arguments() const override { return 0; }
+
+ DataTypes get_variadic_argument_types_impl() const override {
+ return {std::make_shared<DataTypeTimeV2>()};
+ }
+
+ DataTypePtr get_return_type_impl(const ColumnsWithTypeAndName& arguments) const override {
+ return std::make_shared<ToDataType>();
+ }
+
+ bool use_default_implementation_for_nulls() const override { return true; }
+
+ Status execute_impl(FunctionContext* context, Block& block, const ColumnNumbers& arguments,
+ size_t result, size_t input_rows_count) const override {
+ DCHECK_EQ(arguments.size(), 1);
+
+ const auto* column_time = assert_cast<const TimeValue::ColumnTime*>(
+ block.get_by_position(arguments[0]).column.get());
+
+ auto col_res = ToDataType::ColumnType::create();
+
+ col_res->resize(input_rows_count);
+ auto& col_res_data = col_res->get_data();
+
+ for (size_t i = 0; i < input_rows_count; i++) {
+ col_res_data[i] = Transform::execute(column_time->get_element(i));
+ }
+
+ block.replace_by_position(result, std::move(col_res));
+ return Status::OK();
+ }
+};
+
+struct HourImpl {
+ constexpr static auto name = "hour";
+ static inline auto execute(const TimeValue::TimeType& t) { return TimeValue::hour(t); }
+};
+
+struct MintuImpl {
+ constexpr static auto name = "minute";
+ static inline auto execute(const TimeValue::TimeType& t) { return TimeValue::minute(t); }
+};
+
+struct SecondImpl {
+ constexpr static auto name = "second";
+ static inline auto execute(const TimeValue::TimeType& t) { return TimeValue::second(t); }
+};
+
+void register_function_time_value_field(SimpleFunctionFactory& factory) {
+ factory.register_function<FunctionTimeValueToField<DataTypeInt32, HourImpl>>();
+ factory.register_function<FunctionTimeValueToField<DataTypeInt8, MintuImpl>>();
+ factory.register_function<FunctionTimeValueToField<DataTypeInt8, SecondImpl>>();
+}
+
+} // namespace doris::vectorized
\ No newline at end of file
diff --git a/be/src/vec/functions/simple_function_factory.h b/be/src/vec/functions/simple_function_factory.h
index b3ea809c13c..d164e40abb1 100644
--- a/be/src/vec/functions/simple_function_factory.h
+++ b/be/src/vec/functions/simple_function_factory.h
@@ -58,6 +58,7 @@ void register_function_is_null(SimpleFunctionFactory& factory);
void register_function_is_not_null(SimpleFunctionFactory& factory);
void register_function_nullables(SimpleFunctionFactory& factory);
void register_function_to_time_function(SimpleFunctionFactory& factory);
+void register_function_time_value_field(SimpleFunctionFactory& factory);
void register_function_time_of_function(SimpleFunctionFactory& factory);
void register_function_string(SimpleFunctionFactory& factory);
void register_function_running_difference(SimpleFunctionFactory& factory);
@@ -248,6 +249,7 @@ public:
register_function_is_not_null(instance);
register_function_nullables(instance);
register_function_to_time_function(instance);
+ register_function_time_value_field(instance);
register_function_time_of_function(instance);
register_function_string(instance);
register_function_in(instance);
diff --git a/be/src/vec/runtime/time_value.h b/be/src/vec/runtime/time_value.h
index 8283e12f8af..d94e62b977c 100644
--- a/be/src/vec/runtime/time_value.h
+++ b/be/src/vec/runtime/time_value.h
@@ -22,17 +22,51 @@
#include "runtime/define_primitive_type.h"
#include "runtime/primitive_type.h"
#include "util/date_func.h"
+#include "vec/data_types/data_type_time.h"
namespace doris {
/// TODO: Due to the "Time type is not supported for OLAP table" issue, a lot of basic content is missing.It will be supplemented later.
class TimeValue {
+public:
+ constexpr static int64_t ONE_SECOND_MICROSECONDS = 1000000;
+ constexpr static int64_t ONE_MINUTE_MICROSECONDS = 60 * ONE_SECOND_MICROSECONDS;
+ constexpr static int64_t ONE_HOUR_MICROSECONDS = 60 * ONE_MINUTE_MICROSECONDS;
+ constexpr static int64_t ONE_MINUTE_SECONDS = 60;
+ constexpr static int64_t ONE_HOUR_SECONDS = 60 * ONE_MINUTE_SECONDS;
+
using TimeType = typename PrimitiveTypeTraits<TYPE_TIMEV2>::CppType;
+ using ColumnTime = vectorized::DataTypeTimeV2::ColumnType;
+
+ // refer to https://dev.mysql.com/doc/refman/5.7/en/time.html
+ // the time value between '-838:59:59' and '838:59:59'
+ /// TODO: Why is the time type stored as double? Can we directly use int64 and remove the time limit?
+ static int64_t check_over_max_time(double time) {
+ const static int64_t max_time = 3020399LL * 1000 * 1000;
+ // cast(-4562632 as time)
+ // -456:26:32
+ // hour(cast(-4562632 as time))
+ // 456
+ // second(cast(-4562632 as time))
+ // 32
+ if (time > max_time || time < -max_time) {
+ return max_time;
+ }
+ return static_cast<int64_t>(time);
+ }
-public:
static std::string to_string(TimeType time, int scale) {
return timev2_to_buffer_from_double(time, scale);
}
+ static int32_t hour(TimeType time) { return check_over_max_time(time) / ONE_HOUR_MICROSECONDS; }
+
+ static int32_t minute(TimeType time) {
+ return (check_over_max_time(time) % ONE_HOUR_MICROSECONDS) / ONE_MINUTE_MICROSECONDS;
+ }
+
+ static int32_t second(TimeType time) {
+ return (check_over_max_time(time) / ONE_SECOND_MICROSECONDS) % ONE_MINUTE_SECONDS;
+ }
};
} // namespace doris
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Hour.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Hour.java
index 4d2248b9403..60a57ff96be 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Hour.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Hour.java
@@ -26,6 +26,8 @@ import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.types.DateTimeType;
import org.apache.doris.nereids.types.DateTimeV2Type;
import org.apache.doris.nereids.types.DateV2Type;
+import org.apache.doris.nereids.types.IntegerType;
+import org.apache.doris.nereids.types.TimeType;
import org.apache.doris.nereids.types.TinyIntType;
import com.google.common.base.Preconditions;
@@ -42,8 +44,8 @@ public class Hour extends ScalarFunction
public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
FunctionSignature.ret(TinyIntType.INSTANCE).args(DateTimeV2Type.SYSTEM_DEFAULT),
FunctionSignature.ret(TinyIntType.INSTANCE).args(DateV2Type.INSTANCE),
- FunctionSignature.ret(TinyIntType.INSTANCE).args(DateTimeType.INSTANCE)
- );
+ FunctionSignature.ret(TinyIntType.INSTANCE).args(DateTimeType.INSTANCE),
+ FunctionSignature.ret(IntegerType.INSTANCE).args(TimeType.INSTANCE));
/**
* constructor with 1 argument.
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Minute.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Minute.java
index 642fcd8b148..790b0378235 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Minute.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Minute.java
@@ -26,6 +26,7 @@ import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.types.DateTimeType;
import org.apache.doris.nereids.types.DateTimeV2Type;
import org.apache.doris.nereids.types.DateV2Type;
+import org.apache.doris.nereids.types.TimeType;
import org.apache.doris.nereids.types.TinyIntType;
import com.google.common.base.Preconditions;
@@ -42,8 +43,8 @@ public class Minute extends ScalarFunction
private static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
FunctionSignature.ret(TinyIntType.INSTANCE).args(DateTimeV2Type.SYSTEM_DEFAULT),
FunctionSignature.ret(TinyIntType.INSTANCE).args(DateTimeType.INSTANCE),
- FunctionSignature.ret(TinyIntType.INSTANCE).args(DateV2Type.INSTANCE)
- );
+ FunctionSignature.ret(TinyIntType.INSTANCE).args(DateV2Type.INSTANCE),
+ FunctionSignature.ret(TinyIntType.INSTANCE).args(TimeType.INSTANCE));
/**
* constructor with 1 argument.
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Second.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Second.java
index 91167cfc9a1..8f36817b4d0 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Second.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Second.java
@@ -26,6 +26,7 @@ import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.types.DateTimeType;
import org.apache.doris.nereids.types.DateTimeV2Type;
import org.apache.doris.nereids.types.DateV2Type;
+import org.apache.doris.nereids.types.TimeType;
import org.apache.doris.nereids.types.TinyIntType;
import com.google.common.base.Preconditions;
@@ -42,8 +43,8 @@ public class Second extends ScalarFunction
private static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
FunctionSignature.ret(TinyIntType.INSTANCE).args(DateTimeV2Type.SYSTEM_DEFAULT),
FunctionSignature.ret(TinyIntType.INSTANCE).args(DateV2Type.INSTANCE),
- FunctionSignature.ret(TinyIntType.INSTANCE).args(DateTimeType.INSTANCE)
- );
+ FunctionSignature.ret(TinyIntType.INSTANCE).args(DateTimeType.INSTANCE),
+ FunctionSignature.ret(TinyIntType.INSTANCE).args(TimeType.INSTANCE));
/**
* constructor with 1 argument.
diff --git a/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function.out b/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function.out
index 8c256e42d57..fe77b8ffdb6 100644
--- a/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function.out
+++ b/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function.out
@@ -670,3 +670,17 @@ true
1607746332.1230 1607746332 1607702400 1607702400 1607746332
1607746332.6667 1607746333 1607702400 1607702400 1607746333
+-- !sql_time_value --
+1 00:00:00 0 0 0
+2 00:01:00 0 1 0
+3 00:01:23 0 1 23
+4 \N \N \N \N
+5 -01:29:23 -1 -29 -23
+6 -03:33:13 -3 -33 -13
+7 838:59:59 838 59 59
+8 -838:59:59 838 59 59
+9 111:23:45 111 23 45
+
+-- !sql_time_value --
+456:26:32 456 26 32
+
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 0a986f249e5..b3d18af00f0 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
@@ -695,4 +695,22 @@ suite("test_date_function") {
('5', '2020-12-12 12:12:12.666666', '2020-12-12 12:12:12.666666', '2020-12-12 12:12:12.666666', '2020-12-12 12:12:12.666666', '2020-12-12 12:12:12.666666'); """
qt_sql_dt_null_1 """ select unix_timestamp(dtv24), unix_timestamp(dtv20n), unix_timestamp(dv2), unix_timestamp(dv2n), unix_timestamp(str) from dt_null order by k1; """
+
+ sql """ DROP TABLE IF EXISTS dt_timenull; """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS dt_timenull(
+ `k1` INT NOT NULL,
+ `k2` BIGINT NOT NULL
+ )
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 5
+ properties("replication_num" = "1");
+ """
+
+ sql """ insert into dt_timenull values (1, 0),(2, 100),(3, 123),(4, 219837),(5, -8923),(6, -29313),(7, 2131321231),(8, -21312313),(9,1112345);"""
+
+ qt_sql_time_value """ select k1 , cast(k2 as time) , hour(cast(k2 as time)) , minute(cast(k2 as time)), second(cast(k2 as time)) from dt_timenull order by k1;"""
+
+
+ qt_sql_time_value """ select cast(4562632 as time), hour(cast(4562632 as time)) , minute(cast(4562632 as time)) , second(cast(4562632 as time)); """
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org