This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new 55e9be832c1 branch-4.0: [feature](function) support add/sub_time
functions #56200 (#58428)
55e9be832c1 is described below
commit 55e9be832c1d0a078ac6e788df40b9ae552584b8
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Thu Nov 27 14:19:55 2025 +0800
branch-4.0: [feature](function) support add/sub_time functions #56200
(#58428)
Cherry-picked from #56200
Co-authored-by: dwdwqfwe <[email protected]>
---
.../function_date_or_datetime_computation.cpp | 12 ++-
.../function_date_or_datetime_computation.h | 115 ++++++++++++++++++++-
.../doris/catalog/BuiltinScalarFunctions.java | 4 +
.../scalar/{YearsAdd.java => AddTime.java} | 31 +++---
.../scalar/{YearsAdd.java => SubTime.java} | 33 +++---
.../expressions/functions/scalar/YearsAdd.java | 2 +-
.../expressions/visitor/ScalarFunctionVisitor.java | 10 ++
.../datetime_functions/test_date_function_v2.out | 81 +++++++++++++++
.../test_date_function_v2.groovy | 58 ++++++++++-
9 files changed, 304 insertions(+), 42 deletions(-)
diff --git a/be/src/vec/functions/function_date_or_datetime_computation.cpp
b/be/src/vec/functions/function_date_or_datetime_computation.cpp
index 938edb8a9d3..ba0fc8c9c7b 100644
--- a/be/src/vec/functions/function_date_or_datetime_computation.cpp
+++ b/be/src/vec/functions/function_date_or_datetime_computation.cpp
@@ -113,6 +113,11 @@ using FunctionDatetimeSubQuarters =
using FunctionDatetimeSubYears =
FunctionDateOrDateTimeComputation<SubtractYearsImpl<TYPE_DATETIMEV2>>;
+using FunctionAddTimeDatetime = FunctionAddTime<TYPE_DATETIMEV2, AddTimeImpl>;
+using FunctionAddTimeTime = FunctionAddTime<TYPE_TIMEV2, AddTimeImpl>;
+using FunctionSubTimeDatetime = FunctionAddTime<TYPE_DATETIMEV2, SubTimeImpl>;
+using FunctionSubTimeTime = FunctionAddTime<TYPE_TIMEV2, SubTimeImpl>;
+
#define FUNCTION_TIME_DIFF(NAME, IMPL, TYPE) using NAME##_##TYPE =
FunctionTimeDiff<IMPL<TYPE>>;
#define ALL_FUNCTION_TIME_DIFF(NAME, IMPL) \
@@ -205,6 +210,11 @@ void
register_function_date_time_computation(SimpleFunctionFactory& factory) {
factory.register_function<FunctionDatetimeSubQuarters>();
factory.register_function<FunctionDatetimeSubWeeks>();
+ factory.register_function<FunctionAddTimeDatetime>();
+ factory.register_function<FunctionAddTimeTime>();
+ factory.register_function<FunctionSubTimeDatetime>();
+ factory.register_function<FunctionSubTimeTime>();
+
#define REGISTER_DATEV2_FUNCTIONS_DIFF(NAME, TYPE)
factory.register_function<NAME##_##TYPE>();
#define REGISTER_ALL_DATEV2_FUNCTIONS_DIFF(NAME) \
@@ -230,4 +240,4 @@ void
register_function_date_time_computation(SimpleFunctionFactory& factory) {
factory.register_function<FunctionDatetimeToWeekTwoArgs>();
}
-} // namespace doris::vectorized
+} // namespace doris::vectorized
\ No newline at end of file
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 2eb8e9f74fc..ab3d6914af3 100644
--- a/be/src/vec/functions/function_date_or_datetime_computation.h
+++ b/be/src/vec/functions/function_date_or_datetime_computation.h
@@ -66,6 +66,7 @@
namespace doris::vectorized {
#include "common/compile_check_avoid_begin.h"
+
/// because all these functions(xxx_add/xxx_sub) defined in FE use Integer as
the second value
/// so Int64 as delta is needed to support large values. For
upstream(FunctionDateOrDateTimeComputation) we use Int64.
@@ -116,7 +117,6 @@ auto date_time_add(const typename
PrimitiveTypeTraits<ArgType>::DataType::FieldT
std::make_shared<typename
PrimitiveTypeTraits<IntervalPType>::DataType>()}; \
}
\
}
-
ADD_TIME_FUNCTION_IMPL(AddMicrosecondsImpl, microseconds_add, MICROSECOND);
ADD_TIME_FUNCTION_IMPL(AddMillisecondsImpl, milliseconds_add, MILLISECOND);
ADD_TIME_FUNCTION_IMPL(AddSecondsImpl, seconds_add, SECOND);
@@ -1613,5 +1613,118 @@ public:
}
};
+struct AddTimeImpl {
+ static constexpr auto name = "add_time";
+ static bool is_negative() { return false; }
+};
+
+struct SubTimeImpl {
+ static constexpr auto name = "sub_time";
+ static bool is_negative() { return true; }
+};
+
+template <PrimitiveType PType, typename Impl>
+class FunctionAddTime : public IFunction {
+public:
+ static constexpr auto name = Impl::name;
+ static constexpr PrimitiveType ReturnType = PType;
+ static constexpr PrimitiveType ArgType1 = PType;
+ static constexpr PrimitiveType ArgType2 = TYPE_TIMEV2;
+ using ColumnType1 = typename PrimitiveTypeTraits<PType>::ColumnType;
+ using ColumnType2 = typename PrimitiveTypeTraits<TYPE_TIMEV2>::ColumnType;
+ using InputType1 = typename
PrimitiveTypeTraits<PType>::DataType::FieldType;
+ using InputType2 = typename
PrimitiveTypeTraits<TYPE_TIMEV2>::DataType::FieldType;
+ using ReturnNativeType = InputType1;
+ using ReturnDataType = typename PrimitiveTypeTraits<PType>::DataType;
+
+ String get_name() const override { return name; }
+ size_t get_number_of_arguments() const override { return 2; }
+ DataTypes get_variadic_argument_types_impl() const override {
+ return {std::make_shared<typename
PrimitiveTypeTraits<PType>::DataType>(),
+ std::make_shared<typename
PrimitiveTypeTraits<TYPE_TIMEV2>::DataType>()};
+ }
+ DataTypePtr get_return_type_impl(const ColumnsWithTypeAndName& arguments)
const override {
+ return std::make_shared<ReturnDataType>();
+ }
+
+ ReturnNativeType compute(const InputType1& arg1, const InputType2& arg2)
const {
+ if constexpr (PType == TYPE_DATETIMEV2) {
+ DateV2Value<DateTimeV2ValueType> dtv1 =
+ binary_cast<InputType1,
DateV2Value<DateTimeV2ValueType>>(arg1);
+ auto tv2 = static_cast<TimeValue::TimeType>(arg2);
+ TimeInterval interval(TimeUnit::MICROSECOND, tv2,
Impl::is_negative());
+ bool out_range = dtv1.template
date_add_interval<TimeUnit::MICROSECOND>(interval);
+ if (UNLIKELY(!out_range)) {
+ throw Exception(ErrorCode::INVALID_ARGUMENT,
+ "datetime value is out of range in function
{}", name);
+ }
+ return binary_cast<DateV2Value<DateTimeV2ValueType>,
ReturnNativeType>(dtv1);
+ } else if constexpr (PType == TYPE_TIMEV2) {
+ auto tv1 = static_cast<TimeValue::TimeType>(arg1);
+ auto tv2 = static_cast<TimeValue::TimeType>(arg2);
+ double res = TimeValue::limit_with_bound(Impl::is_negative() ? tv1
- tv2 : tv1 + tv2);
+ return res;
+ } else {
+ throw Exception(ErrorCode::FATAL_ERROR, "not support type for
function {}", name);
+ }
+ }
+
+ static FunctionPtr create() { return std::make_shared<FunctionAddTime>(); }
+
+ Status execute_impl(FunctionContext* context, Block& block, const
ColumnNumbers& arguments,
+ uint32_t result, size_t input_rows_count) const
override {
+ DCHECK_EQ(arguments.size(), 2);
+ const auto& [left_col, left_const] =
+ unpack_if_const(block.get_by_position(arguments[0]).column);
+ const auto& [right_col, right_const] =
+ unpack_if_const(block.get_by_position(arguments[1]).column);
+ ColumnPtr nest_col1 = remove_nullable(left_col);
+ ColumnPtr nest_col2 = remove_nullable(right_col);
+ auto res = ColumnVector<ReturnType>::create(input_rows_count, 0);
+
+ if (left_const) {
+ execute_constant_vector(assert_cast<const
ColumnType1&>(*nest_col1).get_element(0),
+ assert_cast<const
ColumnType2&>(*nest_col2).get_data(),
+ res->get_data(), input_rows_count);
+ } else if (right_const) {
+ execute_vector_constant(assert_cast<const
ColumnType1&>(*nest_col1).get_data(),
+ assert_cast<const
ColumnType2&>(*nest_col2).get_element(0),
+ res->get_data(), input_rows_count);
+ } else {
+ execute_vector_vector(assert_cast<const
ColumnType1&>(*nest_col1).get_data(),
+ assert_cast<const
ColumnType2&>(*nest_col2).get_data(),
+ res->get_data(), input_rows_count);
+ }
+
+ block.replace_by_position(result, std::move(res));
+ return Status::OK();
+ }
+ void execute_vector_vector(const PaddedPODArray<InputType1>& left_col,
+ const PaddedPODArray<InputType2>& right_col,
+ PaddedPODArray<ReturnNativeType>& res_data,
+ size_t input_rows_count) const {
+ for (size_t i = 0; i < input_rows_count; ++i) {
+ res_data[i] = compute(left_col[i], right_col[i]);
+ }
+ }
+
+ void execute_vector_constant(const PaddedPODArray<InputType1>& left_col,
+ const InputType2 right_value,
+ PaddedPODArray<ReturnNativeType>& res_data,
+ size_t input_rows_count) const {
+ for (size_t i = 0; i < input_rows_count; ++i) {
+ res_data[i] = compute(left_col[i], right_value);
+ }
+ }
+
+ void execute_constant_vector(const InputType1 left_value,
+ const PaddedPODArray<InputType2>& right_col,
+ PaddedPODArray<ReturnNativeType>& res_data,
+ size_t input_rows_count) const {
+ for (size_t i = 0; i < input_rows_count; ++i) {
+ res_data[i] = compute(left_value, right_col[i]);
+ }
+ }
+};
#include "common/compile_check_avoid_end.h"
} // namespace doris::vectorized
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
index e1d4413cf40..e8730d5a538 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
@@ -33,6 +33,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.ai.Embed;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Abs;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Acos;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Acosh;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.AddTime;
import org.apache.doris.nereids.trees.expressions.functions.scalar.AesDecrypt;
import org.apache.doris.nereids.trees.expressions.functions.scalar.AesEncrypt;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.AppendTrailingCharIfAbsent;
@@ -475,6 +476,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.StructElement
import org.apache.doris.nereids.trees.expressions.functions.scalar.SubBinary;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SubBitmap;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SubReplace;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.SubTime;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Substring;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.SubstringIndex;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Tan;
@@ -570,6 +572,7 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(AIMask.class, "ai_mask"),
scalar(AISummarize.class, "ai_summarize"),
scalar(AISimilarity.class, "ai_similarity"),
+ scalar(AddTime.class, "add_time"),
scalar(AesDecrypt.class, "aes_decrypt"),
scalar(AesEncrypt.class, "aes_encrypt"),
scalar(AppendTrailingCharIfAbsent.class,
"append_trailing_char_if_absent"),
@@ -1023,6 +1026,7 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(SubBitmap.class, "sub_bitmap"),
scalar(SubReplace.class, "sub_replace"),
scalar(Substring.class, "substr", "substring", "mid"),
+ scalar(SubTime.class, "sub_time"),
scalar(SubstringIndex.class, "substring_index"),
scalar(Tan.class, "tan"),
scalar(Tanh.class, "tanh"),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/AddTime.java
similarity index 63%
copy from
fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsAdd.java
copy to
fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/AddTime.java
index 7a6dd1df8e2..364ee6cf675 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsAdd.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/AddTime.java
@@ -19,15 +19,13 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
-import
org.apache.doris.nereids.trees.expressions.functions.ComputeSignatureForDateArithmetic;
import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
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.TimeV2Type;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
@@ -35,32 +33,29 @@ import com.google.common.collect.ImmutableList;
import java.util.List;
/**
- * ScalarFunction 'days_add'.
+ * ScalarFunction 'add_time'.
*/
-public class YearsAdd extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+public class AddTime extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
+ PropagateNullable, DateAddSubMonotonic {
- //ATTN: must place Datetime before Date, because for castring from string
like literal, date and datetime has
- // the same precedence, but we prefer datetime to avoid data loss. for
string literal which could cast to Date
- // without loss, we handle it by ComputeSignatureForDateArithmetic.
private static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
FunctionSignature.ret(DateTimeV2Type.WILDCARD).args(DateTimeV2Type.WILDCARD,
- IntegerType.INSTANCE),
-
FunctionSignature.ret(DateV2Type.INSTANCE).args(DateV2Type.INSTANCE,
IntegerType.INSTANCE));
+ TimeV2Type.WILDCARD),
+
FunctionSignature.ret(TimeV2Type.WILDCARD).args(TimeV2Type.WILDCARD,
TimeV2Type.WILDCARD));
- public YearsAdd(Expression arg0, Expression arg1) {
- super("years_add", arg0, arg1);
+ public AddTime(Expression arg0, Expression arg1) {
+ super("add_time", arg0, arg1);
}
/** constructor for withChildren and reuse signature */
- private YearsAdd(ScalarFunctionParams functionParams) {
+ private AddTime(ScalarFunctionParams functionParams) {
super(functionParams);
}
@Override
- public YearsAdd withChildren(List<Expression> children) {
+ public AddTime withChildren(List<Expression> children) {
Preconditions.checkArgument(children.size() == 2);
- return new YearsAdd(getFunctionParams(children));
+ return new AddTime(getFunctionParams(children));
}
@Override
@@ -70,11 +65,11 @@ public class YearsAdd extends ScalarFunction implements
BinaryExpression, Explic
@Override
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
- return visitor.visitYearsAdd(this, context);
+ return visitor.visitAddTime(this, context);
}
@Override
public Expression withConstantArgs(Expression literal) {
- return new YearsAdd(literal, child(1));
+ return new AddTime(literal, child(1));
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SubTime.java
similarity index 61%
copy from
fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsAdd.java
copy to
fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SubTime.java
index 7a6dd1df8e2..347afeed513 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsAdd.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SubTime.java
@@ -19,15 +19,13 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
-import
org.apache.doris.nereids.trees.expressions.functions.ComputeSignatureForDateArithmetic;
import
org.apache.doris.nereids.trees.expressions.functions.DateAddSubMonotonic;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
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.TimeV2Type;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
@@ -35,32 +33,29 @@ import com.google.common.collect.ImmutableList;
import java.util.List;
/**
- * ScalarFunction 'days_add'.
+ * ScalarFunction 'sub_time'.
*/
-public class YearsAdd extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
- ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+public class SubTime extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
+ PropagateNullable, DateAddSubMonotonic {
- //ATTN: must place Datetime before Date, because for castring from string
like literal, date and datetime has
- // the same precedence, but we prefer datetime to avoid data loss. for
string literal which could cast to Date
- // without loss, we handle it by ComputeSignatureForDateArithmetic.
private static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
-
FunctionSignature.ret(DateTimeV2Type.WILDCARD).args(DateTimeV2Type.WILDCARD,
- IntegerType.INSTANCE),
-
FunctionSignature.ret(DateV2Type.INSTANCE).args(DateV2Type.INSTANCE,
IntegerType.INSTANCE));
+
FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT).args(DateTimeV2Type.SYSTEM_DEFAULT,
+ TimeV2Type.WILDCARD),
+
FunctionSignature.ret(TimeV2Type.WILDCARD).args(TimeV2Type.WILDCARD,
TimeV2Type.WILDCARD));
- public YearsAdd(Expression arg0, Expression arg1) {
- super("years_add", arg0, arg1);
+ public SubTime(Expression arg0, Expression arg1) {
+ super("sub_time", arg0, arg1);
}
/** constructor for withChildren and reuse signature */
- private YearsAdd(ScalarFunctionParams functionParams) {
+ private SubTime(ScalarFunctionParams functionParams) {
super(functionParams);
}
@Override
- public YearsAdd withChildren(List<Expression> children) {
+ public SubTime withChildren(List<Expression> children) {
Preconditions.checkArgument(children.size() == 2);
- return new YearsAdd(getFunctionParams(children));
+ return new SubTime(getFunctionParams(children));
}
@Override
@@ -70,11 +65,11 @@ public class YearsAdd extends ScalarFunction implements
BinaryExpression, Explic
@Override
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
- return visitor.visitYearsAdd(this, context);
+ return visitor.visitSubTime(this, context);
}
@Override
public Expression withConstantArgs(Expression literal) {
- return new YearsAdd(literal, child(1));
+ return new SubTime(literal, child(1));
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsAdd.java
index 7a6dd1df8e2..fb4df179006 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsAdd.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/YearsAdd.java
@@ -35,7 +35,7 @@ import com.google.common.collect.ImmutableList;
import java.util.List;
/**
- * ScalarFunction 'days_add'.
+ * ScalarFunction 'years_add'.
*/
public class YearsAdd extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
index 2724f19360e..a26a3b0123a 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
@@ -35,6 +35,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.combinator.StateComb
import org.apache.doris.nereids.trees.expressions.functions.scalar.Abs;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Acos;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Acosh;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.AddTime;
import org.apache.doris.nereids.trees.expressions.functions.scalar.AesDecrypt;
import org.apache.doris.nereids.trees.expressions.functions.scalar.AesEncrypt;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.AppendTrailingCharIfAbsent;
@@ -476,6 +477,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.StructElement
import org.apache.doris.nereids.trees.expressions.functions.scalar.SubBinary;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SubBitmap;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SubReplace;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.SubTime;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Substring;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.SubstringIndex;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Tan;
@@ -566,6 +568,10 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(acosh, context);
}
+ default R visitAddTime(AddTime addTime, C context) {
+ return visitScalarFunction(addTime, context);
+ }
+
default R visitAesDecrypt(AesDecrypt aesDecrypt, C context) {
return visitScalarFunction(aesDecrypt, context);
}
@@ -2247,6 +2253,10 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(subReplace, context);
}
+ default R visitSubTime(SubTime subTime, C context) {
+ return visitScalarFunction(subTime, context);
+ }
+
default R visitSubstring(Substring substring, C context) {
return visitScalarFunction(substring, context);
}
diff --git
a/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.out
b/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.out
index 11b715513a3..a6ea92dcafb 100644
---
a/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.out
+++
b/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.out
@@ -121,4 +121,85 @@
-- !get_format_time_4 --
\N
+-- !sql_addtime1 --
+2023-10-14T22:35:22
+
+-- !sql_addtime2 --
+2023-10-14T22:35:22.243456
+
+-- !sql_addtime3 --
+2025-01-03T12:46:34.123456
+2023-01-20T11:06:07.123456
+2025-07-05T18:20:52.123456
+
+-- !sql_addtime4 --
+838:59:59.000000
+
+-- !sql_addtime5 --
+-503:36:23.123456
+
+-- !sql_addtime6 --
+2024-12-29T22:46:08.123456
+2023-01-16T08:30:44
+2025-06-30T03:10:34.999999
+
+-- !sql_addtime7 --
+14:34:56.123
+25:59:59.123
+-10:34:55.877
+
+-- !sql_addtime8 --
+2024-12-29T11:41:12.123456
+2023-01-15T10:00:45
+2025-06-30T17:15:30.999999
+
+-- !sql_subtime1 --
+2023-10-13T01:24:38
+
+-- !sql_subtime2 --
+2023-10-13T01:24:37.996544
+
+-- !sql_subtime3 --
+2024-12-28T11:35:49.876544
+2023-01-14T09:55:22.876544
+2025-06-29T17:10:07.876544
+
+-- !sql_subtime4 --
+-838:59:59
+
+-- !sql_subtime5 --
+503:36:23.123456
+
+-- !sql_subtime6 --
+2024-12-28T21:36:16.123456
+2023-01-14T08:30:46
+2025-07-01T04:20:26.999999
+
+-- !sql_subtime7 --
+10:34:55.877
+21:59:58.877
+-14:34:56.123
+
+-- !sql_subtime8 --
+2024-12-29T08:41:12.123456
+2023-01-15T07:00:45
+2025-06-30T14:15:30.999999
+
+-- !sql_addtime10 --
+44:22:32
+
+-- !sql_addtime11 --
+44:22:32
+
+-- !sql_addtime12 --
+2022-12-22T22:10:10
+
+-- !sql_subtime10 --
+00:02:12
+
+-- !sql_subtime11 --
+-44:22:32
+
+-- !sql_subtime12 --
+2022-12-21T01:49:50
diff --git
a/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy
b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy
index 8d5158ea87a..d96a6f7c770 100644
---
a/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy
+++
b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy
@@ -27,7 +27,9 @@ suite("test_date_function_v2") {
CREATE TABLE IF NOT EXISTS ${tableName} (
`id` INT,
`name` varchar(32),
- `dt` varchar(32)
+ `dt` varchar(32),
+ `time_str` varchar(32),
+ `datetime_val` datetime(6)
) ENGINE=OLAP
UNIQUE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 1
@@ -35,7 +37,11 @@ suite("test_date_function_v2") {
"replication_allocation" = "tag.location.default: 1"
)
"""
- sql """ insert into ${tableName} values (3, 'Carl','2024-12-29 10:11:12')
"""
+ sql """ insert into ${tableName} values
+ (1, 'Carl', '2024-12-29 10:11:12', '12:34:56', '2024-12-29
10:11:12.123456'),
+ (2, 'Bob', '2023-01-15 08:30:45', '23:59:59', '2023-01-15
08:30:45.000000'),
+ (3, 'Alice', '2025-06-30 15:45:30', '-12:34:56', '2025-06-30
15:45:30.999999')
+ """
def result1 = try_sql """
select cast(str_to_date(dt, '%Y-%m-%d %H:%i:%s') as string) from
${tableName};
"""
@@ -154,4 +160,52 @@ suite("test_date_function_v2") {
testFoldConst("SELECT GET_FORMAT(TImE, 'INTERNAL');")
testFoldConst("SELECT GET_FORMAT(TIME, 'Doris');")
testFoldConst("SELECT GET_FORMAT(TIME, '你好');")
+ qt_sql_addtime1 "select add_time('2023-10-14 00:00:00', '22:35:22');"
+ testFoldConst("select add_time('2023-10-14 00:00:00', '22:35:22');")
+ qt_sql_addtime2 "select add_time('2023-10-14 00:00:00.12',
'22:35:22.123456');"
+ testFoldConst("select add_time('2023-10-14 00:00:00.12',
'22:35:22.123456');")
+ qt_sql_addtime3 "select add_time(dt, '122:35:22.123456') from
${tableName};"
+ qt_sql_addtime4 "select add_time(cast('822:35:22.123456' as time(6)),
cast('421:01:01' as time(6)));"
+ qt_sql_addtime5 "select add_time(cast('-82:35:22.123456' as time(6)),
cast('-421:01:01' as time(6)));"
+
+ // test time string and datetime type in table
+ qt_sql_addtime6 "select add_time(datetime_val, time_str) from ${tableName}
order by id;"
+ qt_sql_addtime7 "select add_time(cast(time_str as time),
cast('02:00:00.123' as time(3))) from ${tableName} order by id;"
+ qt_sql_addtime8 "select add_time(datetime_val, cast('01:30:00' as time))
from ${tableName} order by id;"
+
+ test{
+ sql("select add_time('9999-12-29 00:00:00', '122:35:22.123456');")
+ exception "datetime value is out of range in function add_time"
+ }
+
+ qt_sql_subtime1("select sub_time('2023-10-14 00:00:00', '22:35:22');")
+ testFoldConst("select sub_time('2023-10-14 00:00:00', '22:35:22');")
+ qt_sql_subtime2("select sub_time('2023-10-14 00:00:00.12',
'22:35:22.123456');")
+ testFoldConst("select sub_time('2023-10-14 00:00:00.12',
'22:35:22.123456');")
+ qt_sql_subtime3("select sub_time(dt, '22:35:22.123456') from
${tableName};")
+ qt_sql_subtime4("select sub_time('-421:01:01', '822:35:22');")
+ qt_sql_subtime5("select sub_time('421:01:01', '-82:35:22.123456');")
+
+ // test time string and datetime type in table
+ qt_sql_subtime6("select sub_time(datetime_val, time_str) from ${tableName}
order by id;")
+ qt_sql_subtime7("select sub_time(cast(time_str as time),
cast('02:00:00.123' as time(3))) from ${tableName} order by id;")
+ qt_sql_subtime8("select sub_time(datetime_val, cast('01:30:00' as time))
from ${tableName} order by id;")
+
+ test{
+ sql("select sub_time('0000-01-01 00:00:00', '122:35:22.123456');")
+ exception "datetime value is out of range in function sub_time"
+ }
+
+ //test computetimearithmetic regular
+
+ qt_sql_addtime10 "select add_time('22:12:22', '221010');"
+ qt_sql_addtime11 "select add_time('+22:12:22', '221010');"
+
+ //datetime parameter
+ qt_sql_addtime12 "select add_time('22/12/22', '221010');"
+
+ qt_sql_subtime10 "select sub_time('22:12:22', '221010');"
+ qt_sql_subtime11 "select sub_time('-22:12:22', '221010');"
+ //datetime parameter
+ qt_sql_subtime12 "select sub_time('22/12/22', '221010');"
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
