This is an automated email from the ASF dual-hosted git repository.
dataroaring pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new 85c82072375 branch-3.0: [function](date) Support date trunc function
#49540 (#49660)
85c82072375 is described below
commit 85c820723754825af1010e415590a94a7e154dc4
Author: Gabriel <liwenqi...@selectdb.com>
AuthorDate: Mon Mar 31 09:54:43 2025 +0800
branch-3.0: [function](date) Support date trunc function #49540 (#49660)
---
be/src/vec/functions/function_timestamp.cpp | 51 +++++++++----
.../apache/doris/analysis/FunctionCallExpr.java | 2 +-
.../executable/DateTimeExtractAndTransform.java | 20 +++++
.../expressions/functions/scalar/DateTrunc.java | 85 ++++++++++++++-------
.../datetime_functions/test_date_trunc.out | Bin 5294 -> 9785 bytes
.../test_auto_range_partition.groovy | 2 +-
.../datetime_functions/test_date_trunc.groovy | 8 ++
7 files changed, 125 insertions(+), 43 deletions(-)
diff --git a/be/src/vec/functions/function_timestamp.cpp
b/be/src/vec/functions/function_timestamp.cpp
index 428f8c2893f..fb3c78bbb9f 100644
--- a/be/src/vec/functions/function_timestamp.cpp
+++ b/be/src/vec/functions/function_timestamp.cpp
@@ -403,7 +403,7 @@ struct DateTruncState {
Callback_function callback_function;
};
-template <typename DateType>
+template <typename DateType, bool DateArgIsFirst>
struct DateTrunc {
static constexpr auto name = "date_trunc";
@@ -416,7 +416,11 @@ struct DateTrunc {
static size_t get_number_of_arguments() { return 2; }
static DataTypes get_variadic_argument_types() {
- return {std::make_shared<DateType>(),
std::make_shared<DataTypeString>()};
+ if constexpr (DateArgIsFirst) {
+ return {std::make_shared<DateType>(),
std::make_shared<DataTypeString>()};
+ } else {
+ return {std::make_shared<DataTypeString>(),
std::make_shared<DateType>()};
+ }
}
static DataTypePtr get_return_type_impl(const DataTypes& arguments) {
@@ -427,11 +431,12 @@ struct DateTrunc {
if (scope != FunctionContext::THREAD_LOCAL) {
return Status::OK();
}
- if (!context->is_col_constant(1)) {
+ if (!context->is_col_constant(DateArgIsFirst ? 1 : 0)) {
return Status::InvalidArgument(
"date_trunc function of time unit argument must be
constant.");
}
- const auto& data_str =
context->get_constant_col(1)->column_ptr->get_data_at(0);
+ const auto& data_str =
+ context->get_constant_col(DateArgIsFirst ? 1 :
0)->column_ptr->get_data_at(0);
std::string lower_str(data_str.data, data_str.size);
std::transform(lower_str.begin(), lower_str.end(), lower_str.begin(),
[](unsigned char c) { return std::tolower(c); });
@@ -467,8 +472,8 @@ struct DateTrunc {
DCHECK_EQ(arguments.size(), 2);
auto null_map = ColumnUInt8::create(input_rows_count, 0);
- const auto& datetime_column =
-
block.get_by_position(arguments[0]).column->convert_to_full_column_if_const();
+ const auto& datetime_column =
block.get_by_position(arguments[DateArgIsFirst ? 0 : 1])
+
.column->convert_to_full_column_if_const();
ColumnPtr res = ColumnType::create(input_rows_count);
auto* state = reinterpret_cast<DateTruncState*>(
context->get_function_state(FunctionContext::THREAD_LOCAL));
@@ -1169,10 +1174,14 @@ public:
}
Status open(FunctionContext* context, FunctionContext::FunctionStateScope
scope) override {
- if constexpr (std::is_same_v<Impl, DateTrunc<DataTypeDate>> ||
- std::is_same_v<Impl, DateTrunc<DataTypeDateV2>> ||
- std::is_same_v<Impl, DateTrunc<DataTypeDateTime>> ||
- std::is_same_v<Impl, DateTrunc<DataTypeDateTimeV2>>) {
+ if constexpr (std::is_same_v<Impl, DateTrunc<DataTypeDate, true>> ||
+ std::is_same_v<Impl, DateTrunc<DataTypeDateV2, true>> ||
+ std::is_same_v<Impl, DateTrunc<DataTypeDateTime, true>>
||
+ std::is_same_v<Impl, DateTrunc<DataTypeDateTimeV2,
true>> ||
+ std::is_same_v<Impl, DateTrunc<DataTypeDate, false>> ||
+ std::is_same_v<Impl, DateTrunc<DataTypeDateV2, false>> ||
+ std::is_same_v<Impl, DateTrunc<DataTypeDateTime, false>>
||
+ std::is_same_v<Impl, DateTrunc<DataTypeDateTimeV2,
false>>) {
return Impl::open(context, scope);
} else {
return Status::OK();
@@ -1427,10 +1436,20 @@ using FunctionStrToDatetime =
FunctionOtherTypesToDateType<StrToDate<DataTypeDat
using FunctionStrToDateV2 =
FunctionOtherTypesToDateType<StrToDate<DataTypeDateV2>>;
using FunctionStrToDatetimeV2 =
FunctionOtherTypesToDateType<StrToDate<DataTypeDateTimeV2>>;
using FunctionMakeDate = FunctionOtherTypesToDateType<MakeDateImpl>;
-using FunctionDateTruncDate =
FunctionOtherTypesToDateType<DateTrunc<DataTypeDate>>;
-using FunctionDateTruncDateV2 =
FunctionOtherTypesToDateType<DateTrunc<DataTypeDateV2>>;
-using FunctionDateTruncDatetime =
FunctionOtherTypesToDateType<DateTrunc<DataTypeDateTime>>;
-using FunctionDateTruncDatetimeV2 =
FunctionOtherTypesToDateType<DateTrunc<DataTypeDateTimeV2>>;
+using FunctionDateTruncDate =
FunctionOtherTypesToDateType<DateTrunc<DataTypeDate, true>>;
+using FunctionDateTruncDateV2 =
FunctionOtherTypesToDateType<DateTrunc<DataTypeDateV2, true>>;
+using FunctionDateTruncDatetime =
FunctionOtherTypesToDateType<DateTrunc<DataTypeDateTime, true>>;
+using FunctionDateTruncDatetimeV2 =
+ FunctionOtherTypesToDateType<DateTrunc<DataTypeDateTimeV2, true>>;
+
+using FunctionDateTruncDateWithCommonOrder =
+ FunctionOtherTypesToDateType<DateTrunc<DataTypeDate, false>>;
+using FunctionDateTruncDateV2WithCommonOrder =
+ FunctionOtherTypesToDateType<DateTrunc<DataTypeDateV2, false>>;
+using FunctionDateTruncDatetimeWithCommonOrder =
+ FunctionOtherTypesToDateType<DateTrunc<DataTypeDateTime, false>>;
+using FunctionDateTruncDatetimeV2WithCommonOrder =
+ FunctionOtherTypesToDateType<DateTrunc<DataTypeDateTimeV2, false>>;
using FunctionFromIso8601DateV2 =
FunctionOtherTypesToDateType<FromIso8601DateV2>;
void register_function_timestamp(SimpleFunctionFactory& factory) {
@@ -1444,6 +1463,10 @@ void register_function_timestamp(SimpleFunctionFactory&
factory) {
factory.register_function<FunctionDateTruncDateV2>();
factory.register_function<FunctionDateTruncDatetime>();
factory.register_function<FunctionDateTruncDatetimeV2>();
+ factory.register_function<FunctionDateTruncDateWithCommonOrder>();
+ factory.register_function<FunctionDateTruncDateV2WithCommonOrder>();
+ factory.register_function<FunctionDateTruncDatetimeWithCommonOrder>();
+ factory.register_function<FunctionDateTruncDatetimeV2WithCommonOrder>();
factory.register_function<FunctionFromIso8601DateV2>();
factory.register_function<FunctionUnixTimestamp<UnixTimeStampImpl>>();
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
index 40249ae5486..03f6762b438 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
@@ -1761,7 +1761,7 @@ public class FunctionCallExpr extends Expr {
final String constParam = ((StringLiteral)
getChild(1)).getValue().toLowerCase();
if (!Lists.newArrayList("year", "quarter", "month", "week", "day",
"hour", "minute", "second")
.contains(constParam)) {
- throw new AnalysisException("date_trunc function second param
only support argument is "
+ throw new AnalysisException("date_trunc function time unit
param only support argument is "
+ "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/DateTimeExtractAndTransform.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
index 0cf8e38fcbc..0be185d80f9 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
@@ -358,6 +358,26 @@ public class DateTimeExtractAndTransform {
return
DateV2Literal.fromJavaDateType(dateTruncHelper(date.toJavaDateType(),
trunc.getValue()));
}
+ @ExecFunction(name = "date_trunc")
+ public static Expression dateTrunc(StringLikeLiteral trunc,
DateTimeLiteral date) {
+ return
DateTimeLiteral.fromJavaDateType(dateTruncHelper(date.toJavaDateType(),
trunc.getValue()));
+ }
+
+ @ExecFunction(name = "date_trunc")
+ public static Expression dateTrunc(StringLikeLiteral trunc,
DateTimeV2Literal date) {
+ return
DateTimeV2Literal.fromJavaDateType(dateTruncHelper(date.toJavaDateType(),
trunc.getValue()));
+ }
+
+ @ExecFunction(name = "date_trunc")
+ public static Expression dateTrunc(StringLikeLiteral trunc, DateLiteral
date) {
+ return
DateLiteral.fromJavaDateType(dateTruncHelper(date.toJavaDateType(),
trunc.getValue()));
+ }
+
+ @ExecFunction(name = "date_trunc")
+ public static Expression dateTrunc(StringLikeLiteral trunc, DateV2Literal
date) {
+ return
DateV2Literal.fromJavaDateType(dateTruncHelper(date.toJavaDateType(),
trunc.getValue()));
+ }
+
private static LocalDateTime dateTruncHelper(LocalDateTime dateTime,
String trunc) {
int year = dateTime.getYear();
int month = dateTime.getMonthValue();
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateTrunc.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateTrunc.java
index 2a4d838c4fd..86a6bfccf56 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateTrunc.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/DateTrunc.java
@@ -21,20 +21,17 @@ import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.functions.AlwaysNullable;
-import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
+import org.apache.doris.nereids.trees.expressions.functions.CustomSignature;
import org.apache.doris.nereids.trees.expressions.functions.Monotonic;
+import org.apache.doris.nereids.trees.expressions.literal.StringLikeLiteral;
import org.apache.doris.nereids.trees.expressions.literal.VarcharLiteral;
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
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.DateType;
-import org.apache.doris.nereids.types.DateV2Type;
import org.apache.doris.nereids.types.VarcharType;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
-import com.google.common.collect.Lists;
import java.util.List;
@@ -42,16 +39,9 @@ import java.util.List;
* ScalarFunction 'date_trunc'. This class is generated by GenerateFunction.
*/
public class DateTrunc extends ScalarFunction
- implements BinaryExpression, ExplicitlyCastableSignature,
AlwaysNullable, Monotonic {
-
- public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
- FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT)
- .args(DateTimeV2Type.SYSTEM_DEFAULT,
VarcharType.SYSTEM_DEFAULT),
-
FunctionSignature.ret(DateTimeType.INSTANCE).args(DateTimeType.INSTANCE,
VarcharType.SYSTEM_DEFAULT),
- FunctionSignature.ret(DateV2Type.INSTANCE)
- .args(DateV2Type.INSTANCE, VarcharType.SYSTEM_DEFAULT),
- FunctionSignature.ret(DateType.INSTANCE).args(DateType.INSTANCE,
VarcharType.SYSTEM_DEFAULT)
- );
+ implements BinaryExpression, AlwaysNullable, Monotonic,
CustomSignature {
+ private static final List<String> LEGAL_TIME_UNIT =
+ ImmutableList.of("year", "quarter", "month", "week", "day",
"hour", "minute", "second");
/**
* constructor with 2 arguments.
@@ -62,15 +52,27 @@ public class DateTrunc extends ScalarFunction
@Override
public void checkLegalityBeforeTypeCoercion() {
- if (getArgument(1).isConstant() == false || !(getArgument(1)
instanceof VarcharLiteral)) {
- throw new AnalysisException("the second parameter of "
+ boolean firstArgIsStringLiteral =
+ getArgument(0).isConstant() && getArgument(0) instanceof
StringLikeLiteral;
+ boolean secondArgIsStringLiteral =
+ getArgument(1).isConstant() && getArgument(1) instanceof
StringLikeLiteral;
+ if (!firstArgIsStringLiteral && !secondArgIsStringLiteral) {
+ throw new AnalysisException("the time unit parameter of "
+ getName() + " function must be a string constant: " +
toSql());
- }
- final String constParam = ((VarcharLiteral)
getArgument(1)).getStringValue().toLowerCase();
- if (!Lists.newArrayList("year", "quarter", "month", "week", "day",
"hour", "minute", "second")
- .contains(constParam)) {
- throw new AnalysisException("date_trunc function second param only
support argument is "
- + "year|quarter|month|week|day|hour|minute|second");
+ } else if (firstArgIsStringLiteral && secondArgIsStringLiteral) {
+ if (!LEGAL_TIME_UNIT.contains(((VarcharLiteral)
getArgument(0)).getStringValue().toLowerCase())
+ && !LEGAL_TIME_UNIT.contains(((VarcharLiteral)
getArgument(1))
+ .getStringValue().toLowerCase())) {
+ throw new AnalysisException("date_trunc function time unit
param only support argument is "
+ + String.join("|", LEGAL_TIME_UNIT));
+ }
+ } else {
+ final String constParam = ((VarcharLiteral)
getArgument(firstArgIsStringLiteral ? 0 : 1))
+ .getStringValue().toLowerCase();
+ if (!LEGAL_TIME_UNIT.contains(constParam)) {
+ throw new AnalysisException("date_trunc function time unit
param only support argument is "
+ + String.join("|", LEGAL_TIME_UNIT));
+ }
}
}
@@ -84,8 +86,36 @@ public class DateTrunc extends ScalarFunction
}
@Override
- public List<FunctionSignature> getSignatures() {
- return SIGNATURES;
+ public FunctionSignature customSignature() {
+ if (getArgument(0).getDataType().isDateLikeType()) {
+ return FunctionSignature.ret(getArgument(0).getDataType())
+ .args(getArgument(0).getDataType(),
VarcharType.SYSTEM_DEFAULT);
+ } else if (getArgument(1).getDataType().isDateLikeType()) {
+ return FunctionSignature.ret(getArgument(1).getDataType())
+ .args(VarcharType.SYSTEM_DEFAULT,
getArgument(1).getDataType());
+ }
+ boolean firstArgIsStringLiteral =
+ getArgument(0).isConstant() && getArgument(0) instanceof
VarcharLiteral;
+ boolean secondArgIsStringLiteral =
+ getArgument(1).isConstant() && getArgument(1) instanceof
VarcharLiteral;
+ if (firstArgIsStringLiteral && !secondArgIsStringLiteral) {
+ return FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT)
+ .args(VarcharType.SYSTEM_DEFAULT,
DateTimeV2Type.SYSTEM_DEFAULT);
+ } else if (!firstArgIsStringLiteral && secondArgIsStringLiteral) {
+ return FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT)
+ .args(DateTimeV2Type.SYSTEM_DEFAULT,
VarcharType.SYSTEM_DEFAULT);
+ } else if (firstArgIsStringLiteral && secondArgIsStringLiteral) {
+ boolean timeUnitIsFirst =
LEGAL_TIME_UNIT.contains(((VarcharLiteral) getArgument(0))
+ .getStringValue().toLowerCase());
+ return timeUnitIsFirst ?
FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT)
+ .args(VarcharType.SYSTEM_DEFAULT,
DateTimeV2Type.SYSTEM_DEFAULT)
+ : FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT)
+ .args(DateTimeV2Type.SYSTEM_DEFAULT,
VarcharType.SYSTEM_DEFAULT);
+ }
+ // if both of args are not constant, `checkLegalityBeforeTypeCoercion`
will throw exception so just return
+ // a signature here.
+ return FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT)
+ .args(VarcharType.SYSTEM_DEFAULT,
DateTimeV2Type.SYSTEM_DEFAULT);
}
@Override
@@ -100,11 +130,12 @@ public class DateTrunc extends ScalarFunction
@Override
public int getMonotonicFunctionChildIndex() {
- return 0;
+ return getArgument(0).getDataType().isDateLikeType() ? 0 : 1;
}
@Override
public Expression withConstantArgs(Expression literal) {
- return new DateTrunc(literal, child(1));
+ return getArgument(0).getDataType().isDateLikeType()
+ ? new DateTrunc(literal, child(1)) : new DateTrunc(child(0),
literal);
}
}
diff --git
a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_trunc.out
b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_trunc.out
index a9492698e0d..642b21010ed 100644
Binary files
a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_trunc.out
and
b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_trunc.out
differ
diff --git
a/regression-test/suites/partition_p0/auto_partition/test_auto_range_partition.groovy
b/regression-test/suites/partition_p0/auto_partition/test_auto_range_partition.groovy
index 557560dfefc..f54f316b3fd 100644
---
a/regression-test/suites/partition_p0/auto_partition/test_auto_range_partition.groovy
+++
b/regression-test/suites/partition_p0/auto_partition/test_auto_range_partition.groovy
@@ -241,6 +241,6 @@ suite("test_auto_range_partition") {
"""
test {
sql "insert into awh_test_range_auto values (1,'20201212')"
- exception "date_trunc function second param only support argument is"
+ exception "date_trunc function time unit param only support argument
is"
}
}
diff --git
a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_trunc.groovy
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_trunc.groovy
index 7ef142d410e..fff5204f623 100644
---
a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_trunc.groovy
+++
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_trunc.groovy
@@ -59,6 +59,14 @@ suite("test_date_trunc") {
qt_select_date_trunc_month """ SELECT k11, date_trunc(k11,'MONTH') FROM
baseall order by k1,k2,k3;"""
qt_select_date_trunc_quarter """ SELECT k11, date_trunc(k11,'quarter')
FROM baseall order by k1,k2,k3;"""
qt_select_date_trunc_year """ SELECT k11, date_trunc(k11,'YeaR') FROM
baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_second """ SELECT k11, date_trunc('SECoND', k11) FROM
baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_minute """ SELECT k11, date_trunc('MINutE', k11) FROM
baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_hour """ SELECT k11, date_trunc('Hour', k11) FROM
baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_day """ SELECT k11, date_trunc('DAY', k11) FROM
baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_week """ SELECT k11, date_trunc('Week', k11) FROM
baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_month """ SELECT k11, date_trunc('MONTH', k11) FROM
baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_quarter """ SELECT k11, date_trunc('quarter', k11)
FROM baseall order by k1,k2,k3;"""
+ qt_select_date_trunc_year """ SELECT k11, date_trunc('YeaR', k11) FROM
baseall order by k1,k2,k3;"""
qt_date_week """ SELECT k10, date_trunc(k10,'Week') FROM baseall order by
k1,k2,k3;"""
qt_date_year """ SELECT k10, date_trunc(k10,'YeaR') FROM baseall order by
k1,k2,k3;"""
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
