This is an automated email from the ASF dual-hosted git repository.
csding18 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 58ff21b648e [Enhancement](function) The date_add function supports
SECOND_MICROSECOND as interval type (#58665)
58ff21b648e is described below
commit 58ff21b648ee68c537ea7cdbc5d6d9a785127931
Author: csding <[email protected]>
AuthorDate: Fri Dec 5 16:44:55 2025 +0800
[Enhancement](function) The date_add function supports SECOND_MICROSECOND
as interval type (#58665)
The date_add function supports new type: SECOND_MICROSECOND, now we can
use a string in the format 'second.microsecond' as the INTERVAL
parameter.
```
mysql> select date_add("2025-10-10", INTERVAL "-1.-1" SECOND_MICROSECOND);
+-------------------------------------------------------------+
| date_add("2025-10-10", INTERVAL "-1.-1" SECOND_MICROSECOND) |
+-------------------------------------------------------------+
| 2025-10-09 23:59:58.900000 |
+-------------------------------------------------------------+
```
---
.../function_date_or_datetime_computation.cpp | 3 +
.../function_date_or_datetime_computation.h | 74 ++++++++++++++++++++
be/test/vec/function/function_time_test.cpp | 35 ++++++++++
.../antlr4/org/apache/doris/nereids/DorisLexer.g4 | 1 +
.../antlr4/org/apache/doris/nereids/DorisParser.g4 | 2 +-
.../rules/analysis/DatetimeFunctionBinder.java | 3 +
.../functions/executable/DateTimeArithmetic.java | 8 +++
.../functions/scalar/SecondMicrosecondAdd.java | 81 ++++++++++++++++++++++
.../expressions/literal/DateTimeV2Literal.java | 42 +++++++++++
.../trees/expressions/literal/Interval.java | 1 +
.../expressions/visitor/ScalarFunctionVisitor.java | 5 ++
.../nereids/rules/expression/FoldConstantTest.java | 17 +++++
.../test_dateadd_with_other_timeunit.out | 76 ++++++++++++++++++--
.../test_dateadd_with_other_timeunit.groovy | 65 ++++++++++++++++-
14 files changed, 404 insertions(+), 9 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 2719b43254a..266324f28d6 100644
--- a/be/src/vec/functions/function_date_or_datetime_computation.cpp
+++ b/be/src/vec/functions/function_date_or_datetime_computation.cpp
@@ -96,6 +96,8 @@ using FunctionDatetimeAddDayHour =
FunctionDateOrDateTimeComputation<AddDayHourImpl<TYPE_DATETIMEV2>>;
using FunctionDatetimeAddMinuteSecond =
FunctionDateOrDateTimeComputation<AddMinuteSecondImpl<TYPE_DATETIMEV2>>;
+using FunctionDatetimeAddSecondMicrosecond =
+
FunctionDateOrDateTimeComputation<AddSecondMicrosecondImpl<TYPE_DATETIMEV2>>;
using FunctionDatetimeSubMicroseconds =
FunctionDateOrDateTimeComputation<SubtractMicrosecondsImpl<TYPE_DATETIMEV2>>;
using FunctionDatetimeSubMilliseconds =
@@ -198,6 +200,7 @@ void
register_function_date_time_computation(SimpleFunctionFactory& factory) {
factory.register_function<FunctionDatetimeAddDaySecond>();
factory.register_function<FunctionDatetimeAddDayHour>();
factory.register_function<FunctionDatetimeAddMinuteSecond>();
+ factory.register_function<FunctionDatetimeAddSecondMicrosecond>();
factory.register_function<FunctionSubDays>();
factory.register_function<FunctionSubMonths>();
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 03c95ed46fc..71137aefb6e 100644
--- a/be/src/vec/functions/function_date_or_datetime_computation.h
+++ b/be/src/vec/functions/function_date_or_datetime_computation.h
@@ -19,6 +19,7 @@
#include <algorithm>
#include <boost/iterator/iterator_facade.hpp>
+#include <cmath>
#include <cstddef>
#include <cstdint>
#include <memory>
@@ -352,6 +353,79 @@ struct AddMinuteSecondImpl {
}
};
+template <PrimitiveType PType>
+struct AddSecondMicrosecondImpl {
+ static constexpr PrimitiveType ArgPType = PType;
+ static constexpr PrimitiveType ReturnType = PType;
+ static constexpr PrimitiveType IntervalPType = PrimitiveType ::TYPE_STRING;
+ using InputNativeType = typename PrimitiveTypeTraits<PType>::DataType
::FieldType;
+ using ReturnNativeType = InputNativeType;
+ using IntervalDataType = typename
PrimitiveTypeTraits<IntervalPType>::DataType;
+ using IntervalNativeType = IntervalDataType::FieldType; // string
+ using ConvertedType = typename
PrimitiveTypeTraits<TYPE_BIGINT>::DataType::FieldType;
+
+ static constexpr auto name = "second_microsecond_add";
+ static constexpr auto is_nullable = false;
+
+ static inline ReturnNativeType execute(const InputNativeType& t,
IntervalNativeType delta) {
+ long microseconds =
parse_second_microsecond_string_to_microseconds(delta);
+ return date_time_add<TimeUnit::MICROSECOND, PType, ConvertedType>(t,
microseconds);
+ }
+
+ static DataTypes get_variadic_argument_types() {
+ return {std ::make_shared<typename
PrimitiveTypeTraits<PType>::DataType>(),
+ std ::make_shared<typename
PrimitiveTypeTraits<IntervalPType>::DataType>()};
+ }
+
+ static long
parse_second_microsecond_string_to_microseconds(IntervalNativeType
time_str_ref) {
+ bool is_negative = false;
+ auto time_str = StringRef {time_str_ref.data(),
time_str_ref.length()}.trim();
+ // string format: "s.microsecond"
+ size_t colon_pos = time_str.find_first_of('.');
+ if (colon_pos == std::string::npos) {
+ throw Exception(ErrorCode::INVALID_ARGUMENT,
+ "Invalid time format, missing colon in '{}'",
+ std::string_view {time_str.data, time_str.size});
+ }
+ // second
+ StringRef seconds_sub = time_str.substring(0, colon_pos).trim();
+ StringParser::ParseResult success;
+ int seconds = StringParser::string_to_int_internal<int32_t, true>(
+ seconds_sub.data, seconds_sub.size, &success);
+ if (success != StringParser::PARSE_SUCCESS) {
+ throw Exception(ErrorCode::INVALID_ARGUMENT, "Invalid seconds
format in '{}'",
+ std::string_view {time_str.data, time_str.size});
+ }
+ if (seconds < 0) {
+ is_negative = true;
+ }
+
+ // microsecond
+ StringRef microsecond_sub = time_str.substring(colon_pos + 1).trim();
+ auto microseconds = StringParser::string_to_int_internal<int64_t,
true>(
+ microsecond_sub.data, microsecond_sub.size, &success);
+ if (success != StringParser::PARSE_SUCCESS) {
+ throw Exception(ErrorCode::INVALID_ARGUMENT, "Invalid microseconds
format in '{}'",
+ std::string_view {time_str.data, time_str.size});
+ }
+
+ long part0 = seconds;
+ // NOTE: Compatible with MySQL
+ int microsecond_len = microsecond_sub.to_string().starts_with("-")
+ ? microsecond_sub.size - 1
+ : microsecond_sub.size;
+ if (microsecond_len < 6) {
+ microseconds *= pow(10, 6 - microsecond_len);
+ }
+ long part1 = std::abs(microseconds);
+
+ if (is_negative) {
+ part1 *= -1;
+ }
+ return part0 * 1000000 + part1;
+ }
+};
+
template <PrimitiveType PType>
struct AddQuartersImpl {
static constexpr PrimitiveType ArgPType = PType;
diff --git a/be/test/vec/function/function_time_test.cpp
b/be/test/vec/function/function_time_test.cpp
index a211fb7b227..6c6bc58458f 100644
--- a/be/test/vec/function/function_time_test.cpp
+++ b/be/test/vec/function/function_time_test.cpp
@@ -1230,6 +1230,41 @@ TEST(VTimestampFunctionsTest, minute_second_add_v2_test)
{
}
}
+TEST(VTimestampFunctionsTest, second_microsecond_add_v2_test) {
+ std::string func_name = "second_microsecond_add";
+
+ InputTypeSet input_types = {{PrimitiveType::TYPE_DATETIMEV2, 6},
+ Consted {PrimitiveType::TYPE_STRING}};
+
+ {
+ DataSet data_set = {
+ {{std::string("2020-10-23 00:00:11.123456"),
std::string("1.1")},
+ std::string("2020-10-23 00:00:12.223456")},
+ };
+
+ static_cast<void>(
+ check_function<DataTypeDateTimeV2, true>(func_name,
input_types, data_set, 6));
+ }
+ {
+ DataSet data_set = {
+ {{std::string("2020-05-23 00:00:11.123456"),
std::string("1.12")},
+ std::string("2020-05-23 00:00:12.243456")},
+ };
+
+ static_cast<void>(
+ check_function<DataTypeDateTimeV2, true>(func_name,
input_types, data_set, 6));
+ }
+ {
+ DataSet data_set = {
+ {{std::string("2020-05-23 00:00:11.123456"),
std::string("1.123")},
+ std::string("2020-05-23 00:00:12.246456")},
+ };
+
+ static_cast<void>(
+ check_function<DataTypeDateTimeV2, true>(func_name,
input_types, data_set, 6));
+ }
+}
+
TEST(VTimestampFunctionsTest, to_days_v2_test) {
std::string func_name = "to_days";
diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
index 490fa43cf9a..2bdc4110140 100644
--- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
+++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
@@ -485,6 +485,7 @@ SCHEDULER: 'SCHEDULER';
SCHEMA: 'SCHEMA';
SCHEMAS: 'SCHEMAS';
SECOND: 'SECOND';
+SECOND_MICROSECOND: 'SECOND_MICROSECOND';
SELECT: 'SELECT';
SEMI: 'SEMI';
SEPARATOR: 'SEPARATOR';
diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
index 003f9c36b34..f8159777457 100644
--- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
+++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
@@ -1773,7 +1773,7 @@ interval
unitIdentifier
: YEAR | QUARTER | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND |
DAY_SECOND | DAY_HOUR
- | MINUTE_SECOND
+ | MINUTE_SECOND | SECOND_MICROSECOND
;
dataTypeWithNullable
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/DatetimeFunctionBinder.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/DatetimeFunctionBinder.java
index 474b9f3a0a7..9adc0e92bb0 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/DatetimeFunctionBinder.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/DatetimeFunctionBinder.java
@@ -61,6 +61,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.QuartersDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.QuartersSub;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondCeil;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondFloor;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.SecondMicrosecondAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsSub;
@@ -308,6 +309,8 @@ public class DatetimeFunctionBinder {
return new DayHourAdd(timestamp, amount);
case MINUTE_SECOND:
return new MinuteSecondAdd(timestamp, amount);
+ case SECOND_MICROSECOND:
+ return new SecondMicrosecondAdd(timestamp, amount);
default:
throw new AnalysisException("Unsupported time stamp add time
unit: " + unit
+ ", supported time unit:
YEAR/QUARTER/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND");
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java
index d3647a87220..797a98a0015 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java
@@ -62,6 +62,14 @@ public class DateTimeArithmetic {
return date.plusMinuteSecond(minuteSecond);
}
+ /**
+ * datetime arithmetic function second_microsecond-add.
+ */
+ @ExecFunction(name = "second_microsecond_add")
+ public static Expression secondMicrosecondAdd(DateTimeV2Literal date,
VarcharLiteral secondMicrosecond) {
+ return date.plusSecondMicrosecond(secondMicrosecond);
+ }
+
/**
* datetime arithmetic function date-sub.
*/
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SecondMicrosecondAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SecondMicrosecondAdd.java
new file mode 100644
index 00000000000..0474b404fef
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SecondMicrosecondAdd.java
@@ -0,0 +1,81 @@
+// 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.
+
+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.VarcharType;
+
+import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * ScalarFunction 'second_microsecond_add'.
+ */
+public class SecondMicrosecondAdd extends ScalarFunction
+ implements BinaryExpression, ExplicitlyCastableSignature,
+ ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+ public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+ FunctionSignature.ret(DateTimeV2Type.MAX).args(DateTimeV2Type.MAX,
+ VarcharType.SYSTEM_DEFAULT));
+
+ public SecondMicrosecondAdd(Expression arg0, Expression arg1) {
+ super("second_microsecond_add", arg0, arg1);
+ }
+
+ /** constructor for withChildren and reuse signature */
+ private SecondMicrosecondAdd(ScalarFunctionParams functionParams) {
+ super(functionParams);
+ }
+
+ @Override
+ public SecondMicrosecondAdd withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 2);
+ return new SecondMicrosecondAdd(getFunctionParams(children));
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitSecondMicrosecondAdd(this, context);
+ }
+
+ @Override
+ public Expression withConstantArgs(Expression literal) {
+ return new SecondMicrosecondAdd(literal, child(1));
+ }
+
+ @Override
+ public FunctionSignature computeSignature(FunctionSignature signature) {
+ signature = super.computeSignature(signature);
+ return signature.withArgumentType(0,
DateTimeV2Type.MAX).withReturnType(DateTimeV2Type.MAX);
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeV2Literal.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeV2Literal.java
index 8fd73a69e31..d149a04633c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeV2Literal.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeV2Literal.java
@@ -364,6 +364,48 @@ public class DateTimeV2Literal extends DateTimeLiteral {
}
}
+ /**
+ * plusSecondMicrosecond
+ */
+ public Expression plusSecondMicrosecond(VarcharLiteral secondMicrosecond) {
+ String stringValue = secondMicrosecond.getStringValue().trim();
+
+ if (!stringValue.matches("[0-9\\-\\.\\s]+")) {
+ throw new NotSupportedException("Invalid time format");
+ }
+
+ String[] split = stringValue.split("\\.");
+ if (split.length != 2) {
+ throw new NotSupportedException("Invalid time format");
+ }
+
+ String second = split[0].trim();
+ String microsecond = split[1].trim();
+
+ try {
+ long seconds = Long.parseLong(second);
+ boolean secondPositive = seconds >= 0;
+
+ long microseconds = Long.parseLong(microsecond);
+ int microsecondLen = microsecond.startsWith("-") ?
microsecond.length() - 1 : microsecond.length();
+ if (microsecondLen < 6) {
+ microseconds *= Math.pow(10, 6 - microsecondLen);
+ }
+
+ if (secondPositive) {
+ microseconds = Math.abs(microseconds);
+ } else {
+ microseconds = -Math.abs(microseconds);
+ }
+
+ return fromJavaDateType(toJavaDateType()
+ .plusSeconds(seconds)
+ .plusNanos(Math.multiplyExact(microseconds, 1000L)),
getDataType().getScale());
+ } catch (NumberFormatException e) {
+ throw new NotSupportedException("Invalid time format");
+ }
+ }
+
// When performing addition or subtraction with MicroSeconds, the
precision must be set to 6 to display it
// completely. use multiplyExact to be aware of multiplication overflow
possibility.
public Expression plusMicroSeconds(long microSeconds) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/Interval.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/Interval.java
index 28de8fc3e7b..385ca195684 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/Interval.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/Interval.java
@@ -83,6 +83,7 @@ public class Interval extends Expression implements
UnaryExpression, AlwaysNotNu
* Supported time unit.
*/
public enum TimeUnit {
+ SECOND_MICROSECOND("SECOND_MICROSECOND", false, 1200),
MINUTE_SECOND("MINUTE_SECOND", false, 1100),
DAY_HOUR("DAY_HOUR", false, 1000),
DAY_SECOND("DAY_SECOND", false, 900),
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 1999161c61e..baf5afb2b46 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
@@ -424,6 +424,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.Sec;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Second;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondCeil;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondFloor;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.SecondMicrosecondAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsSub;
@@ -1158,6 +1159,10 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(minuteSecondAdd, context);
}
+ default R visitSecondMicrosecondAdd(SecondMicrosecondAdd
secondMicrosecondAdd, C context) {
+ return visitScalarFunction(secondMicrosecondAdd, context);
+ }
+
default R visitDaysAdd(DaysAdd daysAdd, C context) {
return visitScalarFunction(daysAdd, context);
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
index a68a1eaedcf..e35312aa1f3 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
@@ -91,6 +91,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.ReplaceEmpty;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Right;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Round;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Sec;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.SecondMicrosecondAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Sign;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Sin;
@@ -467,6 +468,22 @@ class FoldConstantTest extends ExpressionRewriteTestHelper
{
new VarcharLiteral("-2:-1"));
rewritten = executor.rewrite(minuteSecondAdd, context);
Assertions.assertEquals(minuteSecondAdd, rewritten);
+
+ SecondMicrosecondAdd secondMicrosecondAdd = new SecondMicrosecondAdd(
+ DateTimeV2Literal.fromJavaDateType(LocalDateTime.of(1, 1, 1,
1, 1, 1), 6),
+ new VarcharLiteral("1.123456"));
+ rewritten = executor.rewrite(secondMicrosecondAdd, context);
+ Assertions.assertEquals(new DateTimeV2Literal("0001-01-01
01:01:02.123456"), rewritten);
+ // fail to fold, because the result is out of range
+ secondMicrosecondAdd = new SecondMicrosecondAdd(
+ DateTimeV2Literal.fromJavaDateType(LocalDateTime.of(9999,
12, 31, 23, 59, 1), 6),
+ new VarcharLiteral("59.123456"));
+ rewritten = executor.rewrite(secondMicrosecondAdd, context);
+ Assertions.assertEquals(secondMicrosecondAdd, rewritten);
+ secondMicrosecondAdd = new
SecondMicrosecondAdd(DateTimeV2Literal.fromJavaDateType(LocalDateTime.of(0, 1,
1, 0, 1, 1), 6),
+ new VarcharLiteral("-600.123456"));
+ rewritten = executor.rewrite(secondMicrosecondAdd, context);
+ Assertions.assertEquals(secondMicrosecondAdd, rewritten);
}
@Test
diff --git
a/regression-test/data/query_p0/sql_functions/datetime_functions/test_dateadd_with_other_timeunit.out
b/regression-test/data/query_p0/sql_functions/datetime_functions/test_dateadd_with_other_timeunit.out
index cf0676a342f..9032bc441f2 100644
---
a/regression-test/data/query_p0/sql_functions/datetime_functions/test_dateadd_with_other_timeunit.out
+++
b/regression-test/data/query_p0/sql_functions/datetime_functions/test_dateadd_with_other_timeunit.out
@@ -1,10 +1,10 @@
-- This file is automatically generated. You should know what you did if you
want to edit this
-- !sql --
-2025-10-25T02:03:04.456700
+2025-10-25T02:03:04.123456
2025-10-30T11:11:11
-- !sql --
-2025-10-25T02:03:04.456700
+2025-10-25T02:03:04.123456
2025-10-30T11:11:11
-- !sql --
@@ -34,11 +34,11 @@
2025-10-27T22:58:59
-- !sql --
-2025-10-25T02:02:03.456700
+2025-10-25T02:02:03.123456
2025-10-30T11:10:10
-- !sql --
-2025-10-25T02:02:03.456700
+2025-10-25T02:02:03.123456
2025-10-30T11:10:10
-- !sql --
@@ -98,11 +98,11 @@
2025-10-27T14:00
-- !sql --
-2025-10-24T01:03:04.456700
+2025-10-24T01:03:04.123456
2025-10-29T10:11:11
-- !sql --
-2025-10-24T01:03:04.456700
+2025-10-24T01:03:04.123456
2025-10-29T10:11:11
-- !sql --
@@ -161,3 +161,67 @@
-- !sql --
2025-10-28T23:58:50
+-- !sql --
+2025-10-24T01:02:04.223456
+2025-10-29T10:10:11.100
+
+-- !sql --
+2025-10-24T01:04:07.546888
+2025-10-29T10:12:14.423432
+
+-- !sql --
+2025-10-23T23:59:58.900
+2025-10-28T23:59:58.900
+
+-- !sql --
+2025-10-23T20:52:45.786877
+2025-10-28T20:52:45.786877
+
+-- !sql --
+2025-10-29T10:10:11.100
+
+-- !sql --
+2025-10-29T10:10:11.100
+
+-- !sql --
+2025-10-29T10:10:11.132310
+
+-- !sql --
+2025-10-29T10:10:11.132310
+
+-- !sql --
+2025-10-29T10:10:08.900
+
+-- !sql --
+2025-10-29T10:10:08.900
+
+-- !sql --
+2025-10-29T10:10:07.765433
+
+-- !sql --
+2025-10-29T10:10:07.765433
+
+-- !sql --
+2025-10-29T00:00:01.100
+
+-- !sql --
+2025-10-29T00:00:01.100
+
+-- !sql --
+2025-10-29T00:00:01.120
+
+-- !sql --
+2025-10-29T00:00:01.120
+
+-- !sql --
+2025-10-28T23:59:58.900
+
+-- !sql --
+2025-10-28T23:59:58.900
+
+-- !sql --
+2025-10-28T23:59:57.765433
+
+-- !sql --
+2025-10-28T23:59:57.765433
+
diff --git
a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_dateadd_with_other_timeunit.groovy
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_dateadd_with_other_timeunit.groovy
index d64ce939461..cdff84f949b 100644
---
a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_dateadd_with_other_timeunit.groovy
+++
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_dateadd_with_other_timeunit.groovy
@@ -23,7 +23,7 @@ suite("test_dateadd_with_other_timeunit") {
sql """ DROP TABLE IF EXISTS ${tableName} """
sql """
CREATE TABLE IF NOT EXISTS ${tableName} (
- test_datetime datetime(4) NULL COMMENT "",
+ test_datetime datetime(6) NULL COMMENT "",
test_date date NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(test_datetime)
@@ -35,7 +35,7 @@ suite("test_dateadd_with_other_timeunit") {
"storage_format" = "V2"
)
"""
- sql """ insert into ${tableName} values ("2025-10-29 10:10:10",
"2025-10-29"), ("2025-10-24 01:02:03.4567", "2025-10-24"); """
+ sql """ insert into ${tableName} values ("2025-10-29 10:10:10",
"2025-10-29"), ("2025-10-24 01:02:03.123456", "2025-10-24"); """
// DAY_SECOND
testFoldConst """ select date_add("2025-10-29 10:10:10", INTERVAL "1
1:1:1" DAY_SECOND); """
@@ -233,4 +233,65 @@ suite("test_dateadd_with_other_timeunit") {
exception "Invalid seconds format"
}
+ // SECOND_MICROSECOND
+ testFoldConst """ select date_add("2025-10-29 10:10:10.123456", INTERVAL
"1.1" SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29 10:10:10.123456", INTERVAL
"-1.1" SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29 10:10:10.123456", INTERVAL
"1.12" SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29 10:10:10.123456", INTERVAL
"1.-12" SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29 10:10:10.123456", INTERVAL
"-1.12" SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29 10:10:10.123456", INTERVAL
"-1.-12" SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29 10:10:10.123456", INTERVAL
"-1.12345678" SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29 10:10:10.123456", INTERVAL
"-1.12345678" SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "1.1"
SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "1.-1"
SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "1.12"
SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "1.-12"
SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "-1.1"
SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "-1.-1"
SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "-1.12"
SECOND_MICROSECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "-1.-123443"
SECOND_MICROSECOND); """
+
+ qt_sql """ select date_add(test_datetime, INTERVAL "1.1"
SECOND_MICROSECOND) result from ${tableName}; """
+ qt_sql """ select date_add(test_datetime, INTERVAL "1.123423432"
SECOND_MICROSECOND) result from ${tableName}; """
+
+ qt_sql """ select date_add(test_date, INTERVAL "-1.1" SECOND_MICROSECOND)
result from ${tableName}; """
+ qt_sql """ select date_add(test_date, INTERVAL "-1.-11233213123"
SECOND_MICROSECOND) result from ${tableName}; """
+
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "1.1"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "1.-1"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "1.13231"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "1.-13231"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "-1.1"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "-1.-1"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "-1.1234567"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "-1.-1234567"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29", INTERVAL "1.1"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29", INTERVAL "1.-1"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29", INTERVAL "1.12"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29", INTERVAL "1.-12"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29", INTERVAL "-1.1"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29", INTERVAL "-1.-1"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29", INTERVAL "-1.1234567"
SECOND_MICROSECOND); """
+ qt_sql """ select date_add("2025-10-29", INTERVAL "-1.-1234567"
SECOND_MICROSECOND); """
+
+ test {
+ sql """ select date_add("2025-10-29 10:10:10", INTERVAL '1'
SECOND_MICROSECOND) ; """
+ exception "Invalid time format"
+
+ sql """ select date_add("2025-10-29 10:10:10", INTERVAL 'xx.10'
SECOND_MICROSECOND) ; """
+ exception "Invalid time format"
+
+ sql """ select date_add("2025-10-29 10:10:10", INTERVAL '1.xx'
SECOND_MICROSECOND) ; """
+ exception "Invalid time format"
+
+ sql """ select date_add(test_datetime, INTERVAL '1'
SECOND_MICROSECOND) result from ${tableName}; """
+ exception "Invalid time format"
+
+ sql """ select date_add(test_datetime, INTERVAL 'xx.10'
SECOND_MICROSECOND) result from ${tableName}; """
+ exception "Invalid seconds format"
+
+ sql """ select date_add(test_datetime, INTERVAL '1.xx'
SECOND_MICROSECOND) result from ${tableName}; """
+ exception "Invalid microseconds format"
+ }
+
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
