This is an automated email from the ASF dual-hosted git repository.
yangyulei 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 fbac36499c5 [fix](date_function) fix str_to_date function return wrong
microsecond issue (#47129)
fbac36499c5 is described below
commit fbac36499c51689f3c666e0058f6a5d806744758
Author: Yulei-Yang <[email protected]>
AuthorDate: Mon Jan 20 14:08:57 2025 +0800
[fix](date_function) fix str_to_date function return wrong microsecond
issue (#47129)
### What problem does this PR solve?
Issue Number: close #47105
Related PR: #24932
Problem Summary:
### Release note
str_to_date always return microsecond part for datetime even if user
does not specfic %f in date format string. This is wrong.
mysql> select id,str_to_date(dt, '%Y-%m-%d %H:%i:%s') from test1 limit
1;
+------+--------------------------------------+
| id | str_to_date(dt, '%Y-%m-%d %H:%i:%s') |
+------+--------------------------------------+
| 2 | 2024-12-28 10:11:12.000000 |
+------+--------------------------------------+
and constant fold scenario is wrong too:
mysql> select cast(str_to_date('2025-01-17 11:59:30', '%Y-%m-%d
%H:%i:%s') as string);
+--------------------------------------------------------------------------+
| cast(str_to_date('2025-01-17 11:59:30', '%Y-%m-%d %H:%i:%s') as TEXT)
|
+--------------------------------------------------------------------------+
| 2025-01-17 11:59:30.000000 |
+--------------------------------------------------------------------------+
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [x] Regression test
- [ ] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
- Behavior changed:
- [x] No.
- [ ] Yes. <!-- Explain the behavior change -->
- Does this need documentation?
- [x] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR
should merge into -->
---
.../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: [email protected]
For additional commands, e-mail: [email protected]
