This is an automated email from the ASF dual-hosted git repository. kxiao pushed a commit to branch branch-2.0 in repository https://gitbox.apache.org/repos/asf/doris.git
commit 9ed12250f5fb63d85187da7e183da6a7aeacbb4f Author: zclllyybb <[email protected]> AuthorDate: Mon Oct 23 11:52:08 2023 +0800 [fix](datetime) fix unstable str_to_date function result (#25707) fix unstable str_to_date function result --- be/src/vec/functions/function_timestamp.cpp | 16 ++++++++++++---- be/test/vec/function/function_string_test.cpp | 19 ------------------- be/test/vec/function/function_time_test.cpp | 17 ----------------- .../java/org/apache/doris/analysis/DateLiteral.java | 19 +++++++++++++++---- .../org/apache/doris/analysis/FunctionCallExpr.java | 6 +++--- .../trees/expressions/functions/scalar/StrToDate.java | 7 +++++-- .../java/org/apache/doris/rewrite/FEFunctions.java | 2 +- .../datetime_functions/test_date_function.out | 4 ++-- 8 files changed, 38 insertions(+), 52 deletions(-) diff --git a/be/src/vec/functions/function_timestamp.cpp b/be/src/vec/functions/function_timestamp.cpp index 84f1df086f4..48ceb6cbb4f 100644 --- a/be/src/vec/functions/function_timestamp.cpp +++ b/be/src/vec/functions/function_timestamp.cpp @@ -63,16 +63,22 @@ namespace doris::vectorized { +template <typename DateType> struct StrToDate { static constexpr auto name = "str_to_date"; static bool is_variadic() { return false; } - static DataTypes get_variadic_argument_types() { return {}; } + static DataTypes get_variadic_argument_types() { + return {std::make_shared<DataTypeString>(), std::make_shared<DataTypeString>()}; + } static DataTypePtr get_return_type_impl(const DataTypes& arguments) { - //TODO: it doesn't matter now. maybe sometime we should find the function signature with return_type together - return make_nullable(std::make_shared<DataTypeDateTime>()); + if constexpr (IsDateType<DateType> || IsDateV2Type<DateType>) { + return make_nullable(std::make_shared<DataTypeDateV2>()); + } + //datetimev2 + return make_nullable(std::make_shared<DataTypeDateTimeV2>(6)); } static StringRef rewrite_specific_format(const char* raw_str, size_t str_size) { @@ -1270,7 +1276,8 @@ public: } }; -using FunctionStrToDate = FunctionOtherTypesToDateType<StrToDate>; +using FunctionStrToDate = FunctionOtherTypesToDateType<StrToDate<DataTypeDateV2>>; +using FunctionStrToDatetime = FunctionOtherTypesToDateType<StrToDate<DataTypeDateTimeV2>>; using FunctionMakeDate = FunctionOtherTypesToDateType<MakeDateImpl>; using FunctionDateTrunc = FunctionOtherTypesToDateType<DateTrunc<VecDateTimeValue, Int64>>; using FunctionDateTruncV2 = @@ -1278,6 +1285,7 @@ using FunctionDateTruncV2 = void register_function_timestamp(SimpleFunctionFactory& factory) { factory.register_function<FunctionStrToDate>(); + factory.register_function<FunctionStrToDatetime>(); factory.register_function<FunctionMakeDate>(); factory.register_function<FromDays>(); factory.register_function<FunctionDateTrunc>(); diff --git a/be/test/vec/function/function_string_test.cpp b/be/test/vec/function/function_string_test.cpp index b7ceecd4e0d..48647d7e2d1 100644 --- a/be/test/vec/function/function_string_test.cpp +++ b/be/test/vec/function/function_string_test.cpp @@ -1121,25 +1121,6 @@ TEST(function_string_test, function_coalesce_test) { } } -TEST(function_string_test, function_str_to_date_test) { - std::string func_name = "str_to_date"; - InputTypeSet input_types = { - TypeIndex::String, - TypeIndex::String, - }; - DataSet data_set = { - {{Null(), std::string("%Y-%m-%d %H:%i:%s")}, {Null()}}, - {{std::string("2014-12-21 12:34:56"), std::string("%Y-%m-%d %H:%i:%s")}, - str_to_date_time("2014-12-21 12:34:56", false)}, - {{std::string("2014-12-21 12:34%3A56"), std::string("%Y-%m-%d %H:%i%%3A%s")}, - str_to_date_time("2014-12-21 12:34:56", false)}, - {{std::string("11/09/2011"), std::string("%m/%d/%Y")}, - str_to_date_time("2011-11-09", false)}, - {{std::string("2020-09-01"), std::string("%Y-%m-%d %H:%i:%s")}, - str_to_date_time("2020-09-01 00:00:00", false)}}; - check_function<DataTypeDateTime, true>(func_name, input_types, data_set); -} - TEST(function_string_test, function_replace) { std::string func_name = "replace"; InputTypeSet input_types = { diff --git a/be/test/vec/function/function_time_test.cpp b/be/test/vec/function/function_time_test.cpp index d87fab8b7b4..05600067d73 100644 --- a/be/test/vec/function/function_time_test.cpp +++ b/be/test/vec/function/function_time_test.cpp @@ -1379,23 +1379,6 @@ TEST(VTimestampFunctionsTest, yearweek_v2_test) { } } -TEST(VTimestampFunctionsTest, str_to_date_test) { - std::string func_name = "str_to_date"; - - InputTypeSet input_types = {TypeIndex::String, TypeIndex::String}; - - { - DataSet data_set = {{{std::string("2021-01-01"), std::string("%Y-%m-%d")}, - str_to_date_time("2021-01-01", false)}, - {{std::string("2022-01-03"), std::string("%Y-%m-%d")}, - str_to_date_time("2022-01-03", false)}, - {{std::string("2021-00-01"), std::string("%Y-%m-%d")}, Null()}, - {{std::string("2021-01-00"), std::string("%Y-%m-%d")}, Null()}}; - - check_function<DataTypeDate, true>(func_name, input_types, data_set); - } -} - TEST(VTimestampFunctionsTest, from_days_test) { std::string func_name = "from_days"; diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java index 0948353377e..336fd314e0e 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java @@ -1276,9 +1276,20 @@ public class DateLiteral extends LiteralExpr { break; // Micro second case 'f': - tmp = vp + Math.min(6, vend - vp); - intValue = strToLong(value.substring(vp, tmp)); - this.microsecond = (long) (intValue * Math.pow(10, 6 - Math.min(6, vend - vp))); + // FIXME: fix same with BE + tmp = vp; + // when there's still something to the end, fix the scale of ms. + while (tmp < vend && Character.isDigit(value.charAt(tmp))) { + tmp += 1; + } + + if (tmp - vp > 6) { + int tmp2 = vp + 6; + intValue = strToLong(value.substring(vp, tmp2)); + } else { + intValue = strToLong(value.substring(vp, tmp)); + } + this.microsecond = (long) (intValue * Math.pow(10, 6 - Math.min(6, tmp - vp))); timePartUsed = true; microSecondPartUsed = true; vp = tmp; @@ -1476,7 +1487,7 @@ public class DateLiteral extends LiteralExpr { // we think it's stable enough if (datePartUsed) { if (microSecondPartUsed) { - this.type = Type.DATETIMEV2; + this.type = Type.DATETIMEV2_WITH_MAX_SCALAR; } else if (timePartUsed) { this.type = ScalarType.getDefaultDateType(Type.DATETIME); } else { 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 c3e0bd84381..e4cb2c2ef2f 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 @@ -1893,12 +1893,12 @@ public class FunctionCallExpr extends Expr { Expr child1Result = getChild(1).getResultValue(false); if (child1Result instanceof StringLiteral) { if (DateLiteral.hasTimePart(child1Result.getStringValue())) { - this.type = Type.DATETIME; + this.type = Type.DATETIMEV2_WITH_MAX_SCALAR; } else { - this.type = Type.DATE; + this.type = Type.DATEV2; } } else { - this.type = Type.DATETIME; + this.type = Type.DATETIMEV2_WITH_MAX_SCALAR; } } else if (TIME_FUNCTIONS_WITH_PRECISION.contains(fnName.getFunction().toLowerCase()) && fn.getReturnType().isDatetimeV2()) { diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/StrToDate.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/StrToDate.java index 4f512ec09fe..1fbf15586d5 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/StrToDate.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/StrToDate.java @@ -44,9 +44,9 @@ public class StrToDate extends ScalarFunction implements BinaryExpression, ExplicitlyCastableSignature, AlwaysNullable { public static final List<FunctionSignature> SIGNATURES = ImmutableList.of( - FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT).args(VarcharType.SYSTEM_DEFAULT, + FunctionSignature.ret(DateTimeV2Type.MAX).args(VarcharType.SYSTEM_DEFAULT, VarcharType.SYSTEM_DEFAULT), - FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT).args(StringType.INSTANCE, StringType.INSTANCE) + FunctionSignature.ret(DateTimeV2Type.MAX).args(StringType.INSTANCE, StringType.INSTANCE) ); /** @@ -95,6 +95,9 @@ public class StrToDate extends ScalarFunction } } else { returnType = DataType.fromCatalogType(ScalarType.getDefaultDateType(Type.DATETIME)); + if (returnType.isDateTimeV2Type()) { + returnType = DataType.fromCatalogType(Type.DATETIMEV2_WITH_MAX_SCALAR); + } } return signature.withReturnType(returnType); } diff --git a/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java b/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java index bce2dd7dfac..a65ca6edb67 100755 --- a/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java +++ b/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java @@ -150,7 +150,7 @@ public class FEFunctions { return new StringLiteral(result); } - @FEFunction(name = "str_to_date", argTypes = { "VARCHAR", "VARCHAR" }, returnType = "DATETIME") + @FEFunction(name = "str_to_date", argTypes = { "VARCHAR", "VARCHAR" }, returnType = "DATETIMEV2") public static DateLiteral dateParse(StringLiteral date, StringLiteral fmtLiteral) throws AnalysisException { DateLiteral dateLiteral = new DateLiteral(); try { diff --git a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out index 5c61bfc31d2..9858f809ea0 100644 --- a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out +++ b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out @@ -273,10 +273,10 @@ February 2014-12-21T12:34:56 -- !sql -- -2014-12-21T12:34:56 +2014-12-21T12:34:56.789 -- !sql -- -2023-07-05T02:09:55 +2023-07-05T02:09:55.880 -- !sql -- 2004-10-18 --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
