This is an automated email from the ASF dual-hosted git repository. yangyulei 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 1ea44506c8a branch-3.0: [fix](date_function) fix str_to_date function return wrong microsecond issue #47129 (#47261) 1ea44506c8a is described below commit 1ea44506c8a3cbf0381874a551301fab613e8c3a Author: github-actions[bot] <41898282+github-actions[bot]@users.noreply.github.com> AuthorDate: Wed Jan 22 09:13:40 2025 +0800 branch-3.0: [fix](date_function) fix str_to_date function return wrong microsecond issue #47129 (#47261) Cherry-picked from #47129 Co-authored-by: Yulei-Yang <yulei.yang0...@gmail.com> --- .../org/apache/doris/analysis/DateLiteral.java | 5 ++ .../executable/DateTimeExtractAndTransform.java | 4 +- .../expressions/functions/scalar/StrToDate.java | 3 +- .../test_date_function_v2.groovy | 60 ++++++++++++++++++++++ 4 files changed, 70 insertions(+), 2 deletions(-) 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 19c90f32648..6cf83f51476 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 @@ -110,6 +110,7 @@ public class DateLiteral extends LiteralExpr { private static Map<String, Integer> MONTH_ABBR_NAME_DICT = Maps.newHashMap(); private static Map<String, Integer> WEEK_DAY_NAME_DICT = Maps.newHashMap(); private static Set<Character> TIME_PART_SET = Sets.newHashSet(); + private static String MICRO_SECOND_FORMATTER = "%f"; private static final int[] DAYS_IN_MONTH = new int[]{0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31}; private static final WeekFields weekFields = WeekFields.of(DayOfWeek.SUNDAY, 7); @@ -1027,6 +1028,10 @@ public class DateLiteral extends LiteralExpr { return format.chars().anyMatch(c -> TIME_PART_SET.contains((char) c)); } + public static boolean hasMicroSecondPart(String format) { + return format.indexOf(MICRO_SECOND_FORMATTER) != -1; + } + // Return the date stored in the dateliteral as pattern format. // eg : "%Y-%m-%d" or "%Y-%m-%d %H:%i:%s" public String dateFormat(String pattern) throws AnalysisException { 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 c9082715318..7090b08dee9 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 @@ -628,8 +628,10 @@ public class DateTimeExtractAndTransform { if (org.apache.doris.analysis.DateLiteral.hasTimePart(format.getStringValue())) { DataType returnType = DataType.fromCatalogType(ScalarType.getDefaultDateType(Type.DATETIME)); if (returnType instanceof DateTimeV2Type) { + boolean hasMicroPart = org.apache.doris.analysis.DateLiteral + .hasMicroSecondPart(format.getStringValue()); return DateTimeV2Literal.fromJavaDateType(DateUtils.getTime(DateUtils.formatBuilder(format.getValue()) - .toFormatter(), str.getValue())); + .toFormatter(), str.getValue()), hasMicroPart ? 6 : 0); } else { return DateTimeLiteral.fromJavaDateType(DateUtils.getTime(DateUtils.formatBuilder(format.getValue()) .toFormatter(), str.getValue())); 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 ff014db6bca..c768a25ba38 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 @@ -88,7 +88,8 @@ public class StrToDate extends ScalarFunction if (getArgument(1) instanceof StringLikeLiteral) { if (DateLiteral.hasTimePart(((StringLikeLiteral) getArgument(1)).getStringValue())) { returnType = DataType.fromCatalogType(ScalarType.getDefaultDateType(Type.DATETIME)); - if (returnType.isDateTimeV2Type()) { + if (returnType.isDateTimeV2Type() + && DateLiteral.hasMicroSecondPart(((StringLikeLiteral) getArgument(1)).getStringValue())) { returnType = DataType.fromCatalogType(Type.DATETIMEV2_WITH_MAX_SCALAR); } } else { diff --git a/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy new file mode 100644 index 00000000000..bdb8af9ae9b --- /dev/null +++ b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy @@ -0,0 +1,60 @@ +// 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. + +suite("test_date_function_v2") { + sql """ + admin set frontend config ("enable_date_conversion"="true"); + """ + + def tableName = "test_date_function_v2" + + sql """ DROP TABLE IF EXISTS ${tableName} """ + sql """ + CREATE TABLE IF NOT EXISTS ${tableName} ( + `id` INT, + `name` varchar(32), + `dt` varchar(32) + ) ENGINE=OLAP + UNIQUE KEY(`id`) + DISTRIBUTED BY HASH(`id`) BUCKETS 1 + PROPERTIES ( + "replication_allocation" = "tag.location.default: 1" + ) + """ + sql """ insert into ${tableName} values (3, 'Carl','2024-12-29 10:11:12') """ + def result1 = try_sql """ + select cast(str_to_date(dt, '%Y-%m-%d %H:%i:%s') as string) from ${tableName}; + """ + assertEquals(result1[0][0], "2024-12-29 10:11:12"); + + def result2 = try_sql """ + select cast(str_to_date(dt, '%Y-%m-%d %H:%i:%s.%f') as string) from ${tableName}; + """ + assertEquals(result2[0][0], "2024-12-29 10:11:12.000000"); + + def result3 = try_sql """ + select cast(str_to_date("2025-01-17 11:59:30", '%Y-%m-%d %H:%i:%s') as string); + """ + assertEquals(result3[0][0], "2025-01-17 11:59:30"); + + def result4 = try_sql """ + select cast(str_to_date("2025-01-17 11:59:30", '%Y-%m-%d %H:%i:%s.%f') as string); + """ + assertEquals(result4[0][0], "2025-01-17 11:59:30.000000"); + + sql """ drop table ${tableName} """ +} --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org