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 b5232d2bc95 branch-3.0: [fix](Nereids) fold str_to_date to wrong
result when parameter out range #49033 (#49151)
b5232d2bc95 is described below
commit b5232d2bc95802f6054fd87212ca71d0f3928d59
Author: LiBinfeng <libinf...@selectdb.com>
AuthorDate: Thu Mar 27 17:40:01 2025 +0800
branch-3.0: [fix](Nereids) fold str_to_date to wrong result when parameter
out range #49033 (#49151)
pick: #49033
Related PR: #18209
Problem Summary:
SELECT STR_TO_DATE('2025-04-31', '%Y-%m-%d') AS result; would be changed
to result 2025-04-30 in smart mode by mistake
---
.../executable/DateTimeExtractAndTransform.java | 26 +++++++++++-----------
.../org/apache/doris/nereids/util/DateUtils.java | 6 +++--
.../fold_constant_date_arithmatic.groovy | 10 +++++++++
3 files changed, 27 insertions(+), 15 deletions(-)
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 763cc492ef6..0cf8e38fcbc 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
@@ -295,14 +295,14 @@ public class DateTimeExtractAndTransform {
@ExecFunction(name = "date_format")
public static Expression dateFormat(DateLiteral date, StringLikeLiteral
format) {
format = (StringLikeLiteral)
SupportJavaDateFormatter.translateJavaFormatter(format);
- return new
VarcharLiteral(DateUtils.formatBuilder(format.getValue()).toFormatter(Locale.US).format(
+ return new
VarcharLiteral(DateUtils.dateTimeFormatter(format.getValue()).format(
java.time.LocalDate.of(((int) date.getYear()), ((int)
date.getMonth()), ((int) date.getDay()))));
}
@ExecFunction(name = "date_format")
public static Expression dateFormat(DateTimeLiteral date,
StringLikeLiteral format) {
format = (StringLikeLiteral)
SupportJavaDateFormatter.translateJavaFormatter(format);
- return new
VarcharLiteral(DateUtils.formatBuilder(format.getValue()).toFormatter(Locale.US).format(
+ return new
VarcharLiteral(DateUtils.dateTimeFormatter(format.getValue()).format(
java.time.LocalDateTime.of(((int) date.getYear()), ((int)
date.getMonth()), ((int) date.getDay()),
((int) date.getHour()), ((int) date.getMinute()),
((int) date.getSecond()))));
}
@@ -310,14 +310,14 @@ public class DateTimeExtractAndTransform {
@ExecFunction(name = "date_format")
public static Expression dateFormat(DateV2Literal date, StringLikeLiteral
format) {
format = (StringLikeLiteral)
SupportJavaDateFormatter.translateJavaFormatter(format);
- return new
VarcharLiteral(DateUtils.formatBuilder(format.getValue()).toFormatter(Locale.US).format(
+ return new
VarcharLiteral(DateUtils.dateTimeFormatter(format.getValue()).format(
java.time.LocalDate.of(((int) date.getYear()), ((int)
date.getMonth()), ((int) date.getDay()))));
}
@ExecFunction(name = "date_format")
public static Expression dateFormat(DateTimeV2Literal date,
StringLikeLiteral format) {
format = (StringLikeLiteral)
SupportJavaDateFormatter.translateJavaFormatter(format);
- return new
VarcharLiteral(DateUtils.formatBuilder(format.getValue()).toFormatter(Locale.US).format(
+ return new
VarcharLiteral(DateUtils.dateTimeFormatter(format.getValue()).format(
java.time.LocalDateTime.of(((int) date.getYear()), ((int)
date.getMonth()), ((int) date.getDay()),
((int) date.getHour()), ((int) date.getMinute()),
((int) date.getSecond()))));
}
@@ -539,7 +539,7 @@ public class DateTimeExtractAndTransform {
@ExecFunction(name = "unix_timestamp")
public static Expression unixTimestamp(StringLikeLiteral date,
StringLikeLiteral format) {
format = (StringLikeLiteral)
SupportJavaDateFormatter.translateJavaFormatter(format);
- DateTimeFormatter formatter =
DateUtils.formatBuilder(format.getValue()).toFormatter();
+ DateTimeFormatter formatter =
DateUtils.dateTimeFormatter(format.getValue());
LocalDateTime dateObj;
try {
dateObj = LocalDateTime.parse(date.getValue(), formatter);
@@ -628,20 +628,20 @@ public class DateTimeExtractAndTransform {
if
(org.apache.doris.analysis.DateLiteral.hasTimePart(format.getStringValue())) {
DataType returnType =
DataType.fromCatalogType(ScalarType.getDefaultDateType(Type.DATETIME));
if (returnType instanceof DateTimeV2Type) {
- return
DateTimeV2Literal.fromJavaDateType(DateUtils.getTime(DateUtils.formatBuilder(format.getValue())
- .toFormatter(), str.getValue()));
+ return
DateTimeV2Literal.fromJavaDateType(DateUtils.getTime(DateUtils
+ .dateTimeFormatter(format.getValue()),
str.getValue()));
} else {
- return
DateTimeLiteral.fromJavaDateType(DateUtils.getTime(DateUtils.formatBuilder(format.getValue())
- .toFormatter(), str.getValue()));
+ return
DateTimeLiteral.fromJavaDateType(DateUtils.getTime(DateUtils
+ .dateTimeFormatter(format.getValue()),
str.getValue()));
}
} else {
DataType returnType =
DataType.fromCatalogType(ScalarType.getDefaultDateType(Type.DATE));
if (returnType instanceof DateV2Type) {
- return
DateV2Literal.fromJavaDateType(DateUtils.getTime(DateUtils.formatBuilder(format.getValue())
- .toFormatter(), str.getValue()));
+ return
DateV2Literal.fromJavaDateType(DateUtils.getTime(DateUtils.dateTimeFormatter(format.getValue()),
+ str.getValue()));
} else {
- return
DateLiteral.fromJavaDateType(DateUtils.getTime(DateUtils.formatBuilder(format.getValue())
- .toFormatter(), str.getValue()));
+ return
DateLiteral.fromJavaDateType(DateUtils.getTime(DateUtils.dateTimeFormatter(format.getValue()),
+ str.getValue()));
}
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/DateUtils.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/DateUtils.java
index c0563de5d10..e26c792a9bd 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/DateUtils.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/DateUtils.java
@@ -25,12 +25,14 @@ import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
+import java.time.format.ResolverStyle;
import java.time.format.SignStyle;
import java.time.format.TextStyle;
import java.time.temporal.ChronoField;
import java.time.temporal.IsoFields;
import java.time.temporal.TemporalAccessor;
import java.time.temporal.WeekFields;
+import java.util.Locale;
/**
* date util tools.
@@ -41,7 +43,7 @@ public class DateUtils {
/**
* format builder.
*/
- public static DateTimeFormatterBuilder formatBuilder(String pattern)
throws AnalysisException {
+ public static DateTimeFormatter dateTimeFormatter(String pattern) throws
AnalysisException {
DateTimeFormatterBuilder builder = new DateTimeFormatterBuilder();
boolean escaped = false;
for (int i = 0; i < pattern.length(); i++) {
@@ -153,7 +155,7 @@ public class DateUtils {
builder.appendLiteral(character);
}
}
- return builder;
+ return
builder.toFormatter(Locale.US).withResolverStyle(ResolverStyle.STRICT);
}
/**
diff --git
a/regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_date_arithmatic.groovy
b/regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_date_arithmatic.groovy
index e19a01b2a38..ccd3547deef 100644
---
a/regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_date_arithmatic.groovy
+++
b/regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_date_arithmatic.groovy
@@ -38,4 +38,14 @@ suite("fold_constant_date_arithmatic") {
testFoldConst("SELECT date_format('2020-12-01 12:00:30.01', '%I');")
testFoldConst("SELECT date_format('2020-12-01 12:00:30.01', '%l');")
testFoldConst("SELECT date_format('2020-12-01 12:00:30.01', '%r');")
+
+ testFoldConst("select str_to_date('2023-02-29', '%Y-%m-%d') AS result;")
+ testFoldConst("select str_to_date('1900-02-29', '%Y-%m-%d') AS result;")
+ testFoldConst("select str_to_date('2025-04-31', '%Y-%m-%d') AS result;")
+ testFoldConst("select str_to_date('31-12-2020 23:59:59', '%d-%m-%Y
%H:%i:%s');")
+ testFoldConst("select str_to_date('2020-12-31T23:59:59',
'%Y-%m-%dT%H:%i:%s');")
+ testFoldConst("select str_to_date('20201231235959', '%Y%m%d%H%i%s');")
+ testFoldConst("select str_to_date('31/12/2020 23:59', '%d/%m/%Y %H:%i');")
+ testFoldConst("select str_to_date('31/12/2020 11:59 PM', '%d/%m/%Y %h:%i
%p');")
+ testFoldConst("select str_to_date('20201231T235959', '%Y%m%dT%H%i%s');")
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
