This is an automated email from the ASF dual-hosted git repository.
dataroaring pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.0 by this push:
new e8553f85d80 [fix](date_function) fix str_to_date function return wrong
microsecond issue (#47325)
e8553f85d80 is described below
commit e8553f85d80562d092680a5dd74d81f1533124f8
Author: Yulei-Yang <[email protected]>
AuthorDate: Thu Mar 20 14:35:37 2025 +0800
[fix](date_function) fix str_to_date function return wrong microsecond
issue (#47325)
bp https://github.com/apache/doris/pull/47129
fix such issue both in legacy & nereids planner
---
.../org/apache/doris/analysis/DateLiteral.java | 12 +++-
.../apache/doris/analysis/FunctionCallExpr.java | 6 +-
.../executable/DateTimeExtractAndTransform.java | 14 +++-
.../expressions/functions/scalar/StrToDate.java | 3 +-
.../trees/expressions/literal/DateTimeLiteral.java | 7 --
.../doris/analysis/ComparisonPredicateTest.java | 4 +-
.../expressions/literal/DateTimeLiteralTest.java | 10 +--
.../jdbc/test_oracle_jdbc_catalog.groovy | 2 +-
.../test_date_function_v2.groovy | 84 ++++++++++++++++++++++
9 files changed, 122 insertions(+), 20 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 b68f3fc83da..524ca7dd0ca 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> WEEK_DAY_NAME_DICT = Maps.newHashMap();
private static Set<Character> TIME_PART_SET = Sets.newHashSet();
private static final int[] DAYS_IN_MONTH = new int[] {0, 31, 28, 31, 30,
31, 30, 31, 31, 30, 31, 30, 31};
+ private static String MICRO_SECOND_FORMATTER = "%f";
private static final int ALLOW_SPACE_MASK = 4 | 64;
private static final int MAX_DATE_PARTS = 8;
private static final int YY_PART_YEAR = 70;
@@ -685,7 +686,7 @@ public class DateLiteral extends LiteralExpr {
int scale = ((ScalarType) type).getScalarScale();
long scaledMicroseconds = (long) (microsecond /
SCALE_FACTORS[scale]);
- if (scaledMicroseconds != 0) {
+ if (scale > 0) {
dateTimeChars[19] = '.';
fillPaddedValue(dateTimeChars, 20, (int) scaledMicroseconds,
scale);
return new String(dateTimeChars, 0, 20 + scale);
@@ -1000,6 +1001,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 {
@@ -1542,6 +1547,9 @@ public class DateLiteral extends LiteralExpr {
case 'T':
partUsed |= timePart;
break;
+ case 'f':
+ partUsed |= fracPart;
+ break;
default:
break;
}
@@ -1603,7 +1611,7 @@ public class DateLiteral extends LiteralExpr {
// Compute timestamp type
if ((partUsed & datePart) != 0) { // Ymd part only
- if ((partUsed & fracPart) != 0) {
+ if (hasMicroSecondPart(format)) {
this.type = Type.DATETIMEV2_WITH_MAX_SCALAR;
} else if ((partUsed & timePart) != 0) {
this.type = ScalarType.getDefaultDateType(Type.DATETIME);
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 a5fb20bcfbd..c111c6563f1 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
@@ -1978,7 +1978,11 @@ public class FunctionCallExpr extends Expr {
Expr child1Result = getChild(1).getResultValue(false);
if (child1Result instanceof StringLiteral) {
if (DateLiteral.hasTimePart(child1Result.getStringValue())) {
- this.type = Type.DATETIMEV2_WITH_MAX_SCALAR;
+ if
(DateLiteral.hasMicroSecondPart(child1Result.getStringValue())) {
+ this.type = Type.DATETIMEV2_WITH_MAX_SCALAR;
+ } else {
+ this.type = Type.DEFAULT_DATETIMEV2;
+ }
} else {
this.type = Type.DATEV2;
}
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 0a282281cd5..fef06d48182 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
@@ -17,6 +17,8 @@
package org.apache.doris.nereids.trees.expressions.functions.executable;
+import org.apache.doris.catalog.ScalarType;
+import org.apache.doris.catalog.Type;
import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.trees.expressions.ExecFunction;
import org.apache.doris.nereids.trees.expressions.Expression;
@@ -30,6 +32,8 @@ import
org.apache.doris.nereids.trees.expressions.literal.NullLiteral;
import org.apache.doris.nereids.trees.expressions.literal.SmallIntLiteral;
import org.apache.doris.nereids.trees.expressions.literal.TinyIntLiteral;
import org.apache.doris.nereids.trees.expressions.literal.VarcharLiteral;
+import org.apache.doris.nereids.types.DataType;
+import org.apache.doris.nereids.types.DateTimeV2Type;
import org.apache.doris.nereids.types.DateType;
import org.apache.doris.nereids.types.VarcharType;
import org.apache.doris.nereids.util.DateUtils;
@@ -575,8 +579,16 @@ public class DateTimeExtractAndTransform {
@ExecFunction(name = "str_to_date", argTypes = {"VARCHAR", "VARCHAR"},
returnType = "DATETIMEV2")
public static Expression strToDate(VarcharLiteral str, VarcharLiteral
format) {
if
(org.apache.doris.analysis.DateLiteral.hasTimePart(format.getStringValue())) {
- return
DateTimeV2Literal.fromJavaDateType(DateUtils.getTime(DateUtils.formatBuilder(format.getValue())
+ 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()), hasMicroPart ? 6 : 0);
+ } else {
+ return
DateTimeLiteral.fromJavaDateType(DateUtils.getTime(DateUtils.formatBuilder(format.getValue())
.toFormatter(), str.getValue()));
+ }
} else {
return
DateV2Literal.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 1fbf15586d5..d1242016b7a 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
@@ -87,7 +87,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/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteral.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteral.java
index 3ceaae2f102..77e82c5327f 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteral.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteral.java
@@ -117,13 +117,6 @@ public class DateTimeLiteral extends DateLiteral {
}
scale++;
}
- // trim the tailing zero
- for (int i = 19 + scale; i >= 19; i--) {
- if (s.charAt(i) != '0') {
- break;
- }
- scale--;
- }
return scale;
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/analysis/ComparisonPredicateTest.java
b/fe/fe-core/src/test/java/org/apache/doris/analysis/ComparisonPredicateTest.java
index 6824db14172..24e52eb75d3 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/analysis/ComparisonPredicateTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/analysis/ComparisonPredicateTest.java
@@ -159,7 +159,7 @@ public class ComparisonPredicateTest {
Assert.assertEquals(dateTimeExpr, range1.upperEndpoint());
Assert.assertEquals(dateTimeV2Expr1, range1.upperEndpoint());
- Assert.assertEquals(dateTimeV2Expr2, range1.upperEndpoint());
+ Assert.assertNotEquals(dateTimeV2Expr2, range1.upperEndpoint());
Assert.assertEquals(BoundType.CLOSED, range1.upperBoundType());
Assert.assertFalse(range1.hasLowerBound());
@@ -167,6 +167,6 @@ public class ComparisonPredicateTest {
Assert.assertEquals(dateTimeV2Expr1, range2.upperEndpoint());
Assert.assertEquals(BoundType.CLOSED, range2.upperBoundType());
Assert.assertFalse(range2.hasLowerBound());
- Assert.assertEquals(dateTimeV2Expr2, range2.upperEndpoint());
+ Assert.assertNotEquals(dateTimeV2Expr2, range2.upperEndpoint());
}
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteralTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteralTest.java
index e7606cca352..f9e19f17a59 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteralTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteralTest.java
@@ -93,9 +93,9 @@ class DateTimeLiteralTest {
@Test
void testDetermineScale() {
int scale = DateTimeLiteral.determineScale("2022-08-01T01:01:01.0");
- Assertions.assertEquals(0, scale);
+ Assertions.assertEquals(1, scale);
scale = DateTimeLiteral.determineScale("2022-08-01T01:01:01.00000");
- Assertions.assertEquals(0, scale);
+ Assertions.assertEquals(5, scale);
scale = DateTimeLiteral.determineScale("2022-08-01T01:01:01.000001");
Assertions.assertEquals(6, scale);
scale = DateTimeLiteral.determineScale("2022-08-01T01:01:01.123456");
@@ -103,11 +103,11 @@ class DateTimeLiteralTest {
scale = DateTimeLiteral.determineScale("2022-08-01T01:01:01.0001");
Assertions.assertEquals(4, scale);
scale = DateTimeLiteral.determineScale("2022-08-01T01:01:01.00010");
- Assertions.assertEquals(4, scale);
+ Assertions.assertEquals(5, scale);
scale = DateTimeLiteral.determineScale("2022-08-01T01:01:01.12010");
- Assertions.assertEquals(4, scale);
+ Assertions.assertEquals(5, scale);
scale = DateTimeLiteral.determineScale("2022-08-01T01:01:01.02010");
- Assertions.assertEquals(4, scale);
+ Assertions.assertEquals(5, scale);
}
@Test
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
index a954d056eb3..936eb7318d4 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
@@ -132,7 +132,7 @@ suite("test_oracle_jdbc_catalog",
"p0,external,oracle,external_docker,external_d
order_qt_date4 """ select * from TEST_DATE where (T1 > '2022-01-21
00:00:00' and T1 < '2022-01-22 00:00:00') or (T1 > '2022-01-20 00:00:00' and T1
< '2022-01-23 00:00:00'); """
order_qt_date5 """ select * from TEST_DATE where T1 < '2022-01-22
00:00:00' or T1 = '2022-01-21 05:23:01'; """
order_qt_date6 """ select * from TEST_DATE where (T1 < '2022-01-22
00:00:00' or T1 > '2022-01-20 00:00:00') and (T1 < '2022-01-23 00:00:00' or T1
> '2022-01-19 00:00:00'); """
- order_qt_date7 """select * from TEST_TIMESTAMP where T2 <
str_to_date('2020-12-21 12:34:56', '%Y-%m-%d %H:%i:%s');"""
+ order_qt_date7 """select * from TEST_TIMESTAMP where T2 <
str_to_date('2020-12-21 12:34:56', '%Y-%m-%d %H:%i:%s.%f');"""
// test nvl
explain {
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..8b24c9d9c3f
--- /dev/null
+++
b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy
@@ -0,0 +1,84 @@
+// 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");
+ """
+ sql """SET enable_nereids_planner=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");
+
+ // test legacy planner
+ sql """SET enable_nereids_planner=false"""
+ def result5 = try_sql """
+ select cast(str_to_date(dt, '%Y-%m-%d %H:%i:%s') as string) from
${tableName};
+ """
+ assertEquals(result5[0][0], "2024-12-29 10:11:12");
+
+ result5 = try_sql """
+ select cast(str_to_date(dt, '%Y-%m-%d %H:%i:%s.%f') as string) from
${tableName};
+ """
+ assertEquals(result5[0][0], "2024-12-29 10:11:12.000000");
+
+ result5 = try_sql """
+ select cast(str_to_date('2025-01-17 11:59:30', '%Y-%m-%d %H:%i:%s') as
string);
+ """
+ assertEquals(result5[0][0], "2025-01-17 11:59:30");
+
+ result5 = try_sql """
+ select cast(str_to_date('2025-01-17 11:59:30', '%Y-%m-%d %H:%i:%s.%f')
as string);
+ """
+ assertEquals(result5[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]
