This is an automated email from the ASF dual-hosted git repository.
starocean999 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 fce83e968db [Enhancement](function) The date_add function supports
MINUTE_SECOND as interval type (#58032)
fce83e968db is described below
commit fce83e968dbf596460d9a24e21877c89d6d509b4
Author: csding <[email protected]>
AuthorDate: Thu Nov 27 14:08:39 2025 +0800
[Enhancement](function) The date_add function supports MINUTE_SECOND as
interval type (#58032)
The date_add function supports new type: MINUTE_SECOND, now we can use a
string in the format 'minute:second' as the INTERVAL parameter.
```
mysql> select DATE_ADD('2025-10-23 10:10:10', INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------------+
| DATE_ADD('2025-10-23 10:10:10', INTERVAL '1:1' MINUTE_SECOND) |
+---------------------------------------------------------------+
| 2025-10-23 10:11:11 |
+---------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select DATE_ADD('2025-10-23 10:10:10', INTERVAL '-1:1'
MINUTE_SECOND);
+----------------------------------------------------------------+
| DATE_ADD('2025-10-23 10:10:10', INTERVAL '-1:1' MINUTE_SECOND) |
+----------------------------------------------------------------+
| 2025-10-23 10:09:09 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
``
---
.../function_date_or_datetime_computation.cpp | 3 +
.../function_date_or_datetime_computation.h | 66 ++++++++++++
be/test/vec/function/function_time_test.cpp | 35 +++++++
.../antlr4/org/apache/doris/nereids/DorisLexer.g4 | 1 +
.../antlr4/org/apache/doris/nereids/DorisParser.g4 | 1 +
.../rules/analysis/DatetimeFunctionBinder.java | 3 +
.../functions/executable/DateTimeArithmetic.java | 8 ++
.../functions/scalar/MinuteSecondAdd.java | 80 +++++++++++++++
.../expressions/literal/DateTimeV2Literal.java | 53 ++++++++--
.../trees/expressions/literal/Interval.java | 1 +
.../expressions/visitor/ScalarFunctionVisitor.java | 5 +
.../nereids/rules/expression/FoldConstantTest.java | 17 ++++
.../test_dateadd_with_other_timeunit.out | 79 ++++++++++++---
.../test_dateadd_with_other_timeunit.groovy | 112 ++++++++++++++++-----
14 files changed, 417 insertions(+), 47 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 ff081c270f5..2719b43254a 100644
--- a/be/src/vec/functions/function_date_or_datetime_computation.cpp
+++ b/be/src/vec/functions/function_date_or_datetime_computation.cpp
@@ -94,6 +94,8 @@ using FunctionDatetimeAddDaySecond =
FunctionDateOrDateTimeComputation<AddDaySecondImpl<TYPE_DATETIMEV2>>;
using FunctionDatetimeAddDayHour =
FunctionDateOrDateTimeComputation<AddDayHourImpl<TYPE_DATETIMEV2>>;
+using FunctionDatetimeAddMinuteSecond =
+
FunctionDateOrDateTimeComputation<AddMinuteSecondImpl<TYPE_DATETIMEV2>>;
using FunctionDatetimeSubMicroseconds =
FunctionDateOrDateTimeComputation<SubtractMicrosecondsImpl<TYPE_DATETIMEV2>>;
using FunctionDatetimeSubMilliseconds =
@@ -195,6 +197,7 @@ void
register_function_date_time_computation(SimpleFunctionFactory& factory) {
factory.register_function<FunctionDatetimeAddQuarters>();
factory.register_function<FunctionDatetimeAddDaySecond>();
factory.register_function<FunctionDatetimeAddDayHour>();
+ factory.register_function<FunctionDatetimeAddMinuteSecond>();
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 dfc57f1694e..03c95ed46fc 100644
--- a/be/src/vec/functions/function_date_or_datetime_computation.h
+++ b/be/src/vec/functions/function_date_or_datetime_computation.h
@@ -286,6 +286,72 @@ struct AddDayHourImpl {
}
};
+template <PrimitiveType PType>
+struct AddMinuteSecondImpl {
+ 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 = "minute_second_add";
+ static constexpr auto is_nullable = false;
+
+ static inline ReturnNativeType execute(const InputNativeType& t,
IntervalNativeType delta) {
+ long seconds = parse_minute_second_string_to_seconds(delta);
+ return date_time_add<TimeUnit::SECOND, PType, ConvertedType>(t,
seconds);
+ }
+
+ static DataTypes get_variadic_argument_types() {
+ return {std ::make_shared<typename
PrimitiveTypeTraits<PType>::DataType>(),
+ std ::make_shared<typename
PrimitiveTypeTraits<IntervalPType>::DataType>()};
+ }
+
+ static long parse_minute_second_string_to_seconds(IntervalNativeType
time_str_ref) {
+ bool is_negative = false;
+ auto time_str = StringRef {time_str_ref.data(),
time_str_ref.length()}.trim();
+ // string format: "m:s"
+ 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});
+ }
+ // minute
+ StringRef minutes_sub = time_str.substring(0, colon_pos).trim();
+ StringParser::ParseResult success;
+ int minutes = StringParser::string_to_int_internal<int32_t, true>(
+ minutes_sub.data, minutes_sub.size, &success);
+ if (success != StringParser::PARSE_SUCCESS) {
+ throw Exception(ErrorCode::INVALID_ARGUMENT, "Invalid minutes
format in '{}'",
+ std::string_view {time_str.data, time_str.size});
+ }
+ if (minutes < 0) {
+ is_negative = true;
+ }
+
+ // second
+ StringRef second_sub = time_str.substring(colon_pos + 1).trim();
+ int seconds = StringParser::string_to_int_internal<int32_t, true>(
+ second_sub.data, second_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});
+ }
+
+ long part0 = minutes * 60;
+ // NOTE: Compatible with MySQL
+ long part1 = std::abs(seconds);
+ if (is_negative) {
+ part1 *= -1;
+ }
+ return part0 + 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 4ba2b16bdf5..a211fb7b227 100644
--- a/be/test/vec/function/function_time_test.cpp
+++ b/be/test/vec/function/function_time_test.cpp
@@ -1195,6 +1195,41 @@ TEST(VTimestampFunctionsTest, day_hour_add_v2_test) {
}
}
+TEST(VTimestampFunctionsTest, minute_second_add_v2_test) {
+ std::string func_name = "minute_second_add";
+
+ InputTypeSet input_types = {PrimitiveType::TYPE_DATETIMEV2,
+ Consted {PrimitiveType::TYPE_STRING}};
+
+ {
+ DataSet data_set = {
+ {{std::string("2020-10-23 00:00:11.123"), std::string("1:1")},
+ std::string("2020-10-23 00:01:12.123")},
+ };
+
+ static_cast<void>(
+ check_function<DataTypeDateTimeV2, true>(func_name,
input_types, data_set));
+ }
+ {
+ DataSet data_set = {
+ {{std::string("2020-05-23 00:00:11.123"), std::string("1:10")},
+ std::string("2020-05-23 00:01:21.123")},
+ };
+
+ static_cast<void>(
+ check_function<DataTypeDateTimeV2, true>(func_name,
input_types, data_set));
+ }
+ {
+ DataSet data_set = {
+ {{std::string("2020-05-23 00:00:11.123"), std::string("5:0")},
+ std::string("2020-05-23 00:05:11.123")},
+ };
+
+ static_cast<void>(
+ check_function<DataTypeDateTimeV2, true>(func_name,
input_types, data_set));
+ }
+}
+
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 8cf376907f0..490fa43cf9a 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
@@ -362,6 +362,7 @@ MIN: 'MIN';
MINUS: 'MINUS';
MINUTE: 'MINUTE';
MINUTES: 'MINUTES';
+MINUTE_SECOND: 'MINUTE_SECOND';
MODIFY: 'MODIFY';
MONTH: 'MONTH';
MTMV: 'MTMV';
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 282a303f582..8bf46106d15 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
@@ -1769,6 +1769,7 @@ interval
unitIdentifier
: YEAR | QUARTER | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND |
DAY_SECOND | DAY_HOUR
+ | MINUTE_SECOND
;
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 8dc9df95ea0..474b9f3a0a7 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
@@ -45,6 +45,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.HoursDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.HoursSub;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinuteCeil;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinuteFloor;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.MinuteSecondAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesSub;
@@ -305,6 +306,8 @@ public class DatetimeFunctionBinder {
return new DaySecondAdd(timestamp, amount);
case DAY_HOUR:
return new DayHourAdd(timestamp, amount);
+ case MINUTE_SECOND:
+ return new MinuteSecondAdd(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 e879bf7ce38..d3647a87220 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
@@ -54,6 +54,14 @@ public class DateTimeArithmetic {
return date.plusDayHour(dayHour);
}
+ /**
+ * datetime arithmetic function minute_second-add.
+ */
+ @ExecFunction(name = "minute_second_add")
+ public static Expression minuteSecondAdd(DateTimeV2Literal date,
VarcharLiteral minuteSecond) {
+ return date.plusMinuteSecond(minuteSecond);
+ }
+
/**
* datetime arithmetic function date-sub.
*/
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MinuteSecondAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MinuteSecondAdd.java
new file mode 100644
index 00000000000..c34ede1eff2
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MinuteSecondAdd.java
@@ -0,0 +1,80 @@
+// 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 'minute_second_add'.
+ */
+public class MinuteSecondAdd extends ScalarFunction
+ implements BinaryExpression, ExplicitlyCastableSignature,
+ ComputeSignatureForDateArithmetic, PropagateNullable,
DateAddSubMonotonic {
+ public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+
FunctionSignature.ret(DateTimeV2Type.WILDCARD).args(DateTimeV2Type.WILDCARD,
+ VarcharType.SYSTEM_DEFAULT));
+
+ public MinuteSecondAdd(Expression arg0, Expression arg1) {
+ super("minute_second_add", arg0, arg1);
+ }
+
+ /** constructor for withChildren and reuse signature */
+ private MinuteSecondAdd(ScalarFunctionParams functionParams) {
+ super(functionParams);
+ }
+
+ @Override
+ public MinuteSecondAdd withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 2);
+ return new MinuteSecondAdd(getFunctionParams(children));
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitMinuteSecondAdd(this, context);
+ }
+
+ @Override
+ public Expression withConstantArgs(Expression literal) {
+ return new MinuteSecondAdd(literal, child(1));
+ }
+
+ @Override
+ public FunctionSignature computeSignature(FunctionSignature signature) {
+ return super.computeSignature(signature);
+ }
+}
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 b91390afb2e..8fd73a69e31 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
@@ -19,6 +19,7 @@ package org.apache.doris.nereids.trees.expressions.literal;
import org.apache.doris.analysis.LiteralExpr;
import org.apache.doris.nereids.exceptions.AnalysisException;
+import org.apache.doris.nereids.exceptions.NotSupportedException;
import org.apache.doris.nereids.exceptions.UnboundException;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
@@ -220,19 +221,19 @@ public class DateTimeV2Literal extends DateTimeLiteral {
String stringValue = daySecond.getStringValue().trim();
if (!stringValue.matches("[0-9:\\-\\s]+")) {
- return new NullLiteral(dataType);
+ throw new NotSupportedException("Invalid time format");
}
String[] split = stringValue.split("\\s+");
if (split.length != 2) {
- return new NullLiteral(dataType);
+ throw new NotSupportedException("Invalid time format");
}
String day = split[0];
String[] hourMinuteSecond = split[1].split(":");
if (hourMinuteSecond.length != 3) {
- return new NullLiteral(dataType);
+ throw new NotSupportedException("Invalid time format");
}
try {
@@ -259,7 +260,7 @@ public class DateTimeV2Literal extends DateTimeLiteral {
.plusMinutes(minutes)
.plusSeconds(seconds), getDataType().getScale());
} catch (NumberFormatException e) {
- return new NullLiteral(dataType);
+ throw new NotSupportedException("Invalid time format");
}
}
@@ -294,12 +295,12 @@ public class DateTimeV2Literal extends DateTimeLiteral {
String stringValue = dayHour.getStringValue().trim();
if (!stringValue.matches("[0-9\\-\\s]+")) {
- return new NullLiteral(dataType);
+ throw new NotSupportedException("Invalid time format");
}
String[] split = stringValue.split("\\s+");
if (split.length != 2) {
- return new NullLiteral(dataType);
+ throw new NotSupportedException("Invalid time format");
}
String day = split[0];
@@ -321,7 +322,45 @@ public class DateTimeV2Literal extends DateTimeLiteral {
.plusDays(days)
.plusHours(hours), getDataType().getScale());
} catch (NumberFormatException e) {
- return new NullLiteral(dataType);
+ throw new NotSupportedException("Invalid time format");
+ }
+ }
+
+ /**
+ * plusMinuteSecond
+ */
+ public Expression plusMinuteSecond(VarcharLiteral minuteSecond) {
+ String stringValue = minuteSecond.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 minute = split[0].trim();
+ String second = split[1].trim();
+
+ try {
+ long minutes = Long.parseLong(minute);
+ boolean minutePositive = minutes >= 0;
+
+ long seconds = Long.parseLong(second);
+
+ if (minutePositive) {
+ seconds = Math.abs(seconds);
+ } else {
+ seconds = -Math.abs(seconds);
+ }
+
+ return fromJavaDateType(toJavaDateType()
+ .plusMinutes(minutes)
+ .plusSeconds(seconds), getDataType().getScale());
+ } catch (NumberFormatException e) {
+ throw new NotSupportedException("Invalid time format");
}
}
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 eb98ceb48c1..28de8fc3e7b 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 {
+ MINUTE_SECOND("MINUTE_SECOND", false, 1100),
DAY_HOUR("DAY_HOUR", false, 1000),
DAY_SECOND("DAY_SECOND", false, 900),
YEAR("YEAR", false, 800),
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 ecc9706702c..1999161c61e 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
@@ -348,6 +348,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.MilliSecondsS
import org.apache.doris.nereids.trees.expressions.functions.scalar.Minute;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinuteCeil;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinuteFloor;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.MinuteSecondAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesSub;
@@ -1153,6 +1154,10 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(dayHourAdd, context);
}
+ default R visitMinuteSecondAdd(MinuteSecondAdd minuteSecondAdd, C context)
{
+ return visitScalarFunction(minuteSecondAdd, 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 5e2fbbf09ea..a68a1eaedcf 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
@@ -80,6 +80,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.Log10;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Log2;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.MicroSecondsAdd;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.MilliSecondsAdd;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.MinuteSecondAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesAdd;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.MonthsBetween;
import org.apache.doris.nereids.trees.expressions.functions.scalar.NextDay;
@@ -450,6 +451,22 @@ class FoldConstantTest extends ExpressionRewriteTestHelper
{
new VarcharLiteral("-1 -1"));
rewritten = executor.rewrite(dayHourAdd, context);
Assertions.assertEquals(dayHourAdd, rewritten);
+
+ MinuteSecondAdd minuteSecondAdd = new MinuteSecondAdd(
+ DateTimeV2Literal.fromJavaDateType(LocalDateTime.of(1, 1, 1,
1, 1, 1), 0),
+ new VarcharLiteral("1:1"));
+ rewritten = executor.rewrite(minuteSecondAdd, context);
+ Assertions.assertEquals(new DateTimeV2Literal("0001-01-01 01:02:02"),
rewritten);
+ // fail to fold, because the result is out of range
+ minuteSecondAdd = new MinuteSecondAdd(
+ DateTimeV2Literal.fromJavaDateType(LocalDateTime.of(9999,
12, 31, 23, 59, 1), 0),
+ new VarcharLiteral("3:1"));
+ rewritten = executor.rewrite(minuteSecondAdd, context);
+ Assertions.assertEquals(minuteSecondAdd, rewritten);
+ minuteSecondAdd = new
MinuteSecondAdd(DateTimeV2Literal.fromJavaDateType(LocalDateTime.of(0, 1, 1, 0,
1, 1), 0),
+ new VarcharLiteral("-2:-1"));
+ rewritten = executor.rewrite(minuteSecondAdd, context);
+ Assertions.assertEquals(minuteSecondAdd, 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 33be94a3355..cf0676a342f 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
@@ -33,21 +33,6 @@
-- !sql --
2025-10-27T22:58:59
--- !sql --
-\N
-
--- !sql --
-\N
-
--- !sql --
-\N
-
--- !sql --
-\N
-
--- !sql --
-\N
-
-- !sql --
2025-10-25T02:02:03.456700
2025-10-30T11:10:10
@@ -112,3 +97,67 @@
-- !sql --
2025-10-27T14:00
+-- !sql --
+2025-10-24T01:03:04.456700
+2025-10-29T10:11:11
+
+-- !sql --
+2025-10-24T01:03:04.456700
+2025-10-29T10:11:11
+
+-- !sql --
+2025-10-23T23:58:59
+2025-10-28T23:58:59
+
+-- !sql --
+2025-10-23T23:58:59
+2025-10-28T23:58:59
+
+-- !sql --
+2025-10-29T10:11:11
+
+-- !sql --
+2025-10-29T10:11:11
+
+-- !sql --
+2025-10-29T10:11:20
+
+-- !sql --
+2025-10-29T10:11:20
+
+-- !sql --
+2025-10-29T10:09:09
+
+-- !sql --
+2025-10-29T10:09:09
+
+-- !sql --
+2025-10-29T10:09
+
+-- !sql --
+2025-10-29T10:09
+
+-- !sql --
+2025-10-29T00:01:01
+
+-- !sql --
+2025-10-29T00:01:01
+
+-- !sql --
+2025-10-29T00:01:10
+
+-- !sql --
+2025-10-29T00:01:10
+
+-- !sql --
+2025-10-28T23:58:59
+
+-- !sql --
+2025-10-28T23:58:59
+
+-- !sql --
+2025-10-28T23:58:50
+
+-- !sql --
+2025-10-28T23:58:50
+
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 c05f9201fbb..d64ce939461 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
@@ -68,53 +68,48 @@ suite("test_dateadd_with_other_timeunit") {
qt_sql """ select date_add("2025-10-29", INTERVAL "-1 -1:1:1" DAY_SECOND);
"""
qt_sql """ select date_add("2025-10-29", INTERVAL "-1 -1:-1:1"
DAY_SECOND); """
qt_sql """ select date_add("2025-10-29", INTERVAL "-1 -1:-1:-1"
DAY_SECOND); """
- qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "-1 -1:1:1 43"
DAY_SECOND); """
- qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "-1 -1:1:1xxx"
DAY_SECOND); """
- qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "1 1:1:1.1234"
DAY_SECOND); """
- qt_sql """ select date_add("2025-10-29", INTERVAL "-1 -1:1:1 34"
DAY_SECOND); """
- qt_sql """ select date_add("2025-10-29", INTERVAL "-1 -1:1:1xx"
DAY_SECOND); """
test {
+ sql """ select date_add("2025-10-29 10:10:10", INTERVAL "-1 -1:1:1 43"
DAY_SECOND); """
+ exception "Invalid time format"
+
+ sql """ select date_add("2025-10-29 10:10:10", INTERVAL "-1 -1:1:1xxx"
DAY_SECOND); """
+ exception "Invalid time format"
+
+ sql """ select date_add("2025-10-29 10:10:10", INTERVAL "1 1:1:1.1234"
DAY_SECOND); """
+ exception "Invalid time format"
+
+ sql """ select date_add("2025-10-29", INTERVAL "-1 -1:1:1 34"
DAY_SECOND); """
+ exception "Invalid time format"
+
+ sql """ select date_add("2025-10-29", INTERVAL "-1 -1:1:1xx"
DAY_SECOND); """
+ exception "Invalid time format"
+
sql """ select date_add(test_datetime, INTERVAL '1' DAY_SECOND) result
from ${tableName}; """
exception "Invalid time format"
- }
- test {
sql """ select date_add(test_datetime, INTERVAL '1 2' DAY_SECOND)
result from ${tableName}; """
exception "Invalid time format"
- }
- test {
sql """ select date_add(test_datetime, INTERVAL '1 2:3' DAY_SECOND)
result from ${tableName}; """
exception "Invalid time format"
- }
- test {
sql """ select date_add(test_datetime, INTERVAL '1 2:3:4.5678'
DAY_SECOND) result from ${tableName}; """
exception "Invalid seconds format"
- }
- test {
sql """ select date_add(test_datetime, INTERVAL 'xx 00:00:01'
DAY_SECOND) result from ${tableName}; """
exception "Invalid days format"
- }
- test {
- sql """ select date_add(test_datetime, INTERVAL '1 xx:00:01'
DAY_SECOND) result from ${tableName}; """
+ sql """ select date_add(test_datetime, interval '1 xx:00:01'
day_second) result from ${tableName}; """
exception "Invalid hours format"
- }
- test {
sql """ select date_add(test_datetime, INTERVAL '1 00:xx:01'
DAY_SECOND) result from ${tableName}; """
exception "Invalid minutes format"
- }
- test {
sql """ select date_add(test_datetime, INTERVAL '1 00:00:xx'
DAY_SECOND) result from ${tableName}; """
exception "Invalid seconds format"
}
-
// DAY_HOUR
testFoldConst """ select date_add("2025-10-29 10:10:10", INTERVAL "1 1"
DAY_HOUR); """
testFoldConst """ select date_add("2025-10-29 10:10:10", INTERVAL "1 -1"
DAY_HOUR); """
@@ -157,18 +152,85 @@ suite("test_dateadd_with_other_timeunit") {
qt_sql """ select date_add("2025-10-29", INTERVAL "-1 -10" DAY_HOUR); """
test {
+ sql """ select date_add("2025-10-29 10:10:10", INTERVAL '1' DAY_HOUR)
; """
+ exception "Invalid time format"
+
+ sql """ select date_add("2025-10-29 10:10:10", INTERVAL 'xx 10'
DAY_HOUR) ; """
+ exception "Invalid time format"
+
+ sql """ select date_add("2025-10-29 10:10:10", INTERVAL '1 xx'
DAY_HOUR) ; """
+ exception "Invalid time format"
+
sql """ select date_add(test_datetime, INTERVAL '1' DAY_HOUR) result
from ${tableName}; """
exception "Invalid time format"
- }
- test {
sql """ select date_add(test_datetime, INTERVAL 'xx 10' DAY_HOUR)
result from ${tableName}; """
exception "Invalid days format"
- }
- test {
sql """ select date_add(test_datetime, INTERVAL '1 xx' DAY_HOUR)
result from ${tableName}; """
exception "Invalid hours format"
}
+
+ // MINUTE_SECOND
+ testFoldConst """ select date_add("2025-10-29 10:10:10", INTERVAL "1:1"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29 10:10:10", INTERVAL "1:-1"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29 10:10:10", INTERVAL "1:10"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29 10:10:10", INTERVAL "1:-10"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29 10:10:10", INTERVAL "-1:1"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29 10:10:10", INTERVAL "-1:-1"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29 10:10:10", INTERVAL "-1:10"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29 10:10:10", INTERVAL "-1:-10"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "1:1"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "1:-1"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "1:10"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "1:-10"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "-1:1"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "-1:-1"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "-1:10"
MINUTE_SECOND); """
+ testFoldConst """ select date_add("2025-10-29", INTERVAL "-1:-10"
MINUTE_SECOND); """
+
+ qt_sql """ select date_add(test_datetime, INTERVAL "1:1" MINUTE_SECOND)
result from ${tableName}; """
+ qt_sql """ select date_add(test_datetime, INTERVAL "1:1" MINUTE_SECOND)
result from ${tableName}; """
+
+ qt_sql """ select date_add(test_date, INTERVAL "-1:1" MINUTE_SECOND)
result from ${tableName}; """
+ qt_sql """ select date_add(test_date, INTERVAL "-1:-1" MINUTE_SECOND)
result from ${tableName}; """
+
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "1:1"
MINUTE_SECOND); """
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "1:-1"
MINUTE_SECOND); """
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "1:10"
MINUTE_SECOND); """
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "1:-10"
MINUTE_SECOND); """
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "-1:1"
MINUTE_SECOND); """
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "-1:-1"
MINUTE_SECOND); """
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "-1:10"
MINUTE_SECOND); """
+ qt_sql """ select date_add("2025-10-29 10:10:10", INTERVAL "-1:-10"
MINUTE_SECOND); """
+ qt_sql """ select date_add("2025-10-29", INTERVAL "1:1" MINUTE_SECOND); """
+ qt_sql """ select date_add("2025-10-29", INTERVAL "1:-1" MINUTE_SECOND);
"""
+ qt_sql """ select date_add("2025-10-29", INTERVAL "1:10" MINUTE_SECOND);
"""
+ qt_sql """ select date_add("2025-10-29", INTERVAL "1:-10" MINUTE_SECOND);
"""
+ qt_sql """ select date_add("2025-10-29", INTERVAL "-1:1" MINUTE_SECOND);
"""
+ qt_sql """ select date_add("2025-10-29", INTERVAL "-1:-1" MINUTE_SECOND);
"""
+ qt_sql """ select date_add("2025-10-29", INTERVAL "-1:10" MINUTE_SECOND);
"""
+ qt_sql """ select date_add("2025-10-29", INTERVAL "-1:-10" MINUTE_SECOND);
"""
+
+ test {
+ sql """ select date_add("2025-10-29 10:10:10", INTERVAL '1'
MINUTE_SECOND) ; """
+ exception "Invalid time format"
+
+ sql """ select date_add("2025-10-29 10:10:10", INTERVAL 'xx:10'
MINUTE_SECOND) ; """
+ exception "Invalid time format"
+
+ sql """ select date_add("2025-10-29 10:10:10", INTERVAL '1:xx'
MINUTE_SECOND) ; """
+ exception "Invalid time format"
+
+ sql """ select date_add(test_datetime, INTERVAL '1' MINUTE_SECOND)
result from ${tableName}; """
+ exception "Invalid time format"
+
+ sql """ select date_add(test_datetime, INTERVAL 'xx:10' MINUTE_SECOND)
result from ${tableName}; """
+ exception "Invalid minutes format"
+
+ sql """ select date_add(test_datetime, INTERVAL '1:xx' MINUTE_SECOND)
result from ${tableName}; """
+ exception "Invalid seconds format"
+ }
+
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
