This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.1 by this push:
new 1eff4538aa3 branch-2.1: [fix](nereids) fix convert to date literal
throw exception #48980 (#48985)
1eff4538aa3 is described below
commit 1eff4538aa30966e0ec5c3bec8c1c24d0d88f912
Author: yujun <[email protected]>
AuthorDate: Sat Mar 15 10:44:40 2025 +0800
branch-2.1: [fix](nereids) fix convert to date literal throw exception
#48980 (#48985)
---
.../expression/rules/FoldConstantRuleOnFE.java | 19 +-
.../doris/nereids/stats/ExpressionEstimation.java | 3 +-
.../trees/expressions/literal/DateLiteral.java | 28 +-
.../trees/expressions/literal/DateTimeLiteral.java | 4 +-
.../apache/doris/nereids/types/DateTimeV2Type.java | 10 +-
.../doris/nereids/util/TypeCoercionUtils.java | 5 +-
.../trees/expressions/literal/DateLiteralTest.java | 9 +-
.../data/nereids_syntax_p0/test_cast_datetime.out | Bin 351 -> 258 bytes
.../nereids_syntax_p0/test_cast_datetime.groovy | 504 ++++++++++++++++++++-
9 files changed, 535 insertions(+), 47 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/FoldConstantRuleOnFE.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/FoldConstantRuleOnFE.java
index cb4bdc07e98..6014cbbcf35 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/FoldConstantRuleOnFE.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/FoldConstantRuleOnFE.java
@@ -83,6 +83,7 @@ import
org.apache.doris.nereids.trees.expressions.literal.NullLiteral;
import org.apache.doris.nereids.trees.expressions.literal.StringLikeLiteral;
import org.apache.doris.nereids.trees.expressions.literal.StringLiteral;
import org.apache.doris.nereids.trees.expressions.literal.VarcharLiteral;
+import
org.apache.doris.nereids.trees.expressions.literal.format.DateTimeChecker;
import org.apache.doris.nereids.types.BooleanType;
import org.apache.doris.nereids.types.DataType;
import org.apache.doris.nereids.types.coercion.DateLikeType;
@@ -98,7 +99,6 @@ import com.google.common.collect.ImmutableList.Builder;
import com.google.common.collect.Lists;
import org.apache.commons.codec.digest.DigestUtils;
-import java.time.DateTimeException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
@@ -452,17 +452,14 @@ public class FoldConstantRuleOnFE extends
AbstractExpressionRewriteRule
if (child.isNullLiteral()) {
return new NullLiteral(dataType);
} else if (child instanceof StringLikeLiteral && dataType instanceof
DateLikeType) {
+ String dateStr = ((StringLikeLiteral) child).getStringValue();
+ if (!DateTimeChecker.isValidDateTime(dateStr)) {
+ return cast;
+ }
try {
- return ((DateLikeType)
dataType).fromString(((StringLikeLiteral) child).getStringValue());
- } catch (AnalysisException t) {
- if (cast.isExplicitType()) {
- return cast;
- } else {
- // If cast is from type coercion, we don't use NULL
literal and will throw exception.
- throw t;
- }
- } catch (DateTimeException e) {
- return new NullLiteral(dataType);
+ return ((DateLikeType) dataType).fromString(dateStr);
+ } catch (Exception t) {
+ return cast;
}
}
try {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/ExpressionEstimation.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/ExpressionEstimation.java
index 39656b6636b..780c5922c6a 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/ExpressionEstimation.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/ExpressionEstimation.java
@@ -100,7 +100,6 @@ import org.apache.doris.statistics.Statistics;
import com.google.common.base.Preconditions;
import org.apache.commons.collections.CollectionUtils;
-import java.time.DateTimeException;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
@@ -223,7 +222,7 @@ public class ExpressionEstimation extends
ExpressionVisitor<ColumnStatistic, Sta
long max = dateMaxLiteral.getValue();
builder.setMaxValue(max);
builder.setMaxExpr(dateMaxLiteral.toLegacyLiteral());
- } catch (DateTimeException | AnalysisException e) {
+ } catch (AnalysisException e) {
convertSuccess = false;
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java
index 54a74944a22..56bbcb5ca96 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java
@@ -30,7 +30,6 @@ import org.apache.doris.nereids.util.DateUtils;
import com.google.common.collect.ImmutableSet;
-import java.time.DateTimeException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.Year;
@@ -274,8 +273,8 @@ public class DateLiteral extends Literal {
}
/** parseDateLiteral */
- public static Result<DateLiteral, ? extends Exception>
parseDateLiteral(String s) {
- Result<TemporalAccessor, ? extends Exception> parseResult =
parseDateTime(s);
+ public static Result<DateLiteral, AnalysisException>
parseDateLiteral(String s) {
+ Result<TemporalAccessor, AnalysisException> parseResult =
parseDateTime(s);
if (parseResult.isError()) {
return parseResult.cast();
}
@@ -291,17 +290,17 @@ public class DateLiteral extends Literal {
}
/** parseDateTime */
- public static Result<TemporalAccessor, ? extends Exception>
parseDateTime(String s) {
- // fast parse '2022-01-01'
- if (s.length() == 10 && s.charAt(4) == '-' && s.charAt(7) == '-') {
- TemporalAccessor date = fastParseDate(s);
- if (date != null) {
- return Result.ok(date);
- }
- }
-
+ public static Result<TemporalAccessor, AnalysisException>
parseDateTime(String s) {
String originalString = s;
try {
+ // fast parse '2022-01-01'
+ if (s.length() == 10 && s.charAt(4) == '-' && s.charAt(7) == '-') {
+ TemporalAccessor date = fastParseDate(s);
+ if (date != null) {
+ return Result.ok(date);
+ }
+ }
+
TemporalAccessor dateTime;
// remove suffix/prefix ' '
@@ -342,14 +341,11 @@ public class DateLiteral extends Literal {
// if Year is not present, throw exception
if (!dateTime.isSupported(ChronoField.YEAR)) {
return Result.err(
- () -> new DateTimeException("date/datetime literal ["
+ originalString + "] is invalid")
+ () -> new AnalysisException("date/datetime literal ["
+ originalString + "] is invalid")
);
}
return Result.ok(dateTime);
- } catch (DateTimeException e) {
- return Result.err(() ->
- new DateTimeException("date/datetime literal [" +
originalString + "] is invalid", e));
} catch (Exception ex) {
return Result.err(() -> new AnalysisException("date/datetime
literal [" + originalString + "] is invalid"));
}
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 f056bed9169..17c5678b051 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
@@ -132,8 +132,8 @@ public class DateTimeLiteral extends DateLiteral {
}
/** parseDateTimeLiteral */
- public static Result<DateTimeLiteral, ? extends Exception>
parseDateTimeLiteral(String s, boolean isV2) {
- Result<TemporalAccessor, ? extends Exception> parseResult =
parseDateTime(s);
+ public static Result<DateTimeLiteral, AnalysisException>
parseDateTimeLiteral(String s, boolean isV2) {
+ Result<TemporalAccessor, AnalysisException> parseResult =
parseDateTime(s);
if (parseResult.isError()) {
return parseResult.cast();
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/types/DateTimeV2Type.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/types/DateTimeV2Type.java
index 94de55aea61..c374ef87203 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/types/DateTimeV2Type.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/types/DateTimeV2Type.java
@@ -21,6 +21,7 @@ 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.literal.DateTimeLiteral;
+import
org.apache.doris.nereids.trees.expressions.literal.format.DateTimeChecker;
import org.apache.doris.nereids.types.coercion.DateLikeType;
import org.apache.doris.nereids.types.coercion.IntegralType;
@@ -87,7 +88,14 @@ public class DateTimeV2Type extends DateLikeType {
* maybe we need to check for validity?
*/
public static DateTimeV2Type forTypeFromString(String s) {
- int scale = DateTimeLiteral.determineScale(s);
+ int scale = MAX_SCALE;
+ if (DateTimeChecker.isValidDateTime(s)) {
+ try {
+ scale = DateTimeLiteral.determineScale(s);
+ } catch (Exception e) {
+ // let be to process it
+ }
+ }
if (scale > MAX_SCALE) {
scale = MAX_SCALE;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java
index 29066b0c3b2..6c4cafa2517 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java
@@ -600,12 +600,11 @@ public class TypeCoercionUtils {
} else if (dataType.isDateTimeType() &&
DateTimeChecker.isValidDateTime(value)) {
ret = DateTimeLiteral.parseDateTimeLiteral(value,
false).orElse(null);
} else if (dataType.isDateV2Type() &&
DateTimeChecker.isValidDateTime(value)) {
- Result<DateLiteral, ? extends Exception> parseResult
- = DateV2Literal.parseDateLiteral(value);
+ Result<DateLiteral, AnalysisException> parseResult =
DateV2Literal.parseDateLiteral(value);
if (parseResult.isOk()) {
ret = parseResult.get();
} else {
- Result<DateTimeLiteral, ? extends Exception> parseResult2
+ Result<DateTimeLiteral, AnalysisException> parseResult2
= DateTimeV2Literal.parseDateTimeLiteral(value,
true);
if (parseResult2.isOk()) {
ret = parseResult2.get();
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteralTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteralTest.java
index 1919238ac7e..f9455e5d623 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteralTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteralTest.java
@@ -23,7 +23,6 @@ import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Test;
-import java.time.DateTimeException;
import java.util.function.Consumer;
class DateLiteralTest {
@@ -68,8 +67,8 @@ class DateLiteralTest {
new DateLiteral("2022-1-1");
new DateLiteral("20220101");
- Assertions.assertThrows(DateTimeException.class, () -> new
DateLiteral("-01-01"));
- Assertions.assertThrows(DateTimeException.class, () -> new
DateLiteral("01-01"));
+ Assertions.assertThrows(AnalysisException.class, () -> new
DateLiteral("-01-01"));
+ Assertions.assertThrows(AnalysisException.class, () -> new
DateLiteral("01-01"));
}
@Test
@@ -130,8 +129,8 @@ class DateLiteralTest {
@Test
void testWrongPunctuationDate() {
- Assertions.assertThrows(DateTimeException.class, () -> new
DateTimeV2Literal("2020€02€01"));
- Assertions.assertThrows(DateTimeException.class, () -> new
DateTimeV2Literal("2020【02】01"));
+ Assertions.assertThrows(AnalysisException.class, () -> new
DateTimeV2Literal("2020€02€01"));
+ Assertions.assertThrows(AnalysisException.class, () -> new
DateTimeV2Literal("2020【02】01"));
}
@Test
diff --git a/regression-test/data/nereids_syntax_p0/test_cast_datetime.out
b/regression-test/data/nereids_syntax_p0/test_cast_datetime.out
index a2a359c2c43..c0fec349189 100644
Binary files a/regression-test/data/nereids_syntax_p0/test_cast_datetime.out
and b/regression-test/data/nereids_syntax_p0/test_cast_datetime.out differ
diff --git a/regression-test/suites/nereids_syntax_p0/test_cast_datetime.groovy
b/regression-test/suites/nereids_syntax_p0/test_cast_datetime.groovy
index 5e00ecd0789..5ffddd853bf 100644
--- a/regression-test/suites/nereids_syntax_p0/test_cast_datetime.groovy
+++ b/regression-test/suites/nereids_syntax_p0/test_cast_datetime.groovy
@@ -15,6 +15,9 @@
// specific language governing permissions and limitations
// under the License.
+import java.sql.Date
+import java.time.LocalDateTime
+
suite("test_cast_datetime") {
sql "drop table if exists casttbl"
@@ -49,11 +52,498 @@ suite("test_cast_datetime") {
qt_2 "select * from casttbl"
qt_3 "select a, '' = mydate, '' = mydatev2, '' = mydatetime, '' =
mydatetimev2 from casttbl"
- qt_4 "select '' > date '2019-06-01'"
- qt_5 "select '' > date_sub('2019-06-01', -10)"
- qt_7 "select '' > cast('2019-06-01 00:00:00' as datetime)"
- qt_8 "select date_add('', 10)"
- qt_9 "select date_add('2020', 10)"
- qt_10 "select date_add('08-09', 10)"
- qt_12 "select date_add('abcd', 10)"
+ def wrong_date_strs = [
+ "'' > date '2019-06-01'",
+ "'' > date_sub('2019-06-01', -10)",
+ "'' > cast('2019-06-01 00:00:00' as datetime)",
+ "date_add('', 1)",
+ "date_add('2020',1)",
+ "date_add('08-09',1)",
+ "date_add('abcd',1)",
+ "date_add('2020-15-20',1)",
+ "date_add('2020-10-32',1)",
+ "date_add('2021-02-29',1)",
+ "date_add('99999-10-10',1)",
+ "date_add('10-30',1)",
+ "date_add('10-30 10:10:10',1)",
+ "date_add('2020-01 00:00:00', 1)",
+ "MICROSECOND('invalid_time')",
+ "MICROSECOND('12.34.56.123456')",
+ "MICROSECOND('12:34:56')",
+ "MICROSECOND('12:34:56.1234')",
+ "MICROSECOND('12345')",
+ "MICROSECOND('12:34:56.1')",
+ "MICROSECOND('12:34:56.01')",
+ "MICROSECOND('12:34:56.abcdef')",
+ "MICROSECOND('NaN')",
+ "MonthName('abcd-ef-gh')",
+ "DATE('2023-02-28 24:00:00')",
+ "DATE('2023-02-28 23:59:60')",
+ "DATE('170141183460469231731687303715884105727')",
+ "DATE('１月１日')",
+ "DATE('１２３４５６７８')",
+ "DATE('2023-1-32')",
+ "DATE('1-1-2023')",
+ "DATE('January 32, 2023')",
+ "DATE('February 29, 2023')",
+ "DATE('April 31, 2023')",
+ "DATE('02/29/2023')",
+ "DATE('13/01/2023')",
+ "DATEV2('20230229')",
+ "DATEV2('abc')",
+ "DATEV2('日本語')",
+ "DATEV2('ññó')",
+ "DATEV2('')",
+ "DATEV2(' ')",
+ "DATEV2('12:34:56')",
+ "DATEV2('NaN')",
+ "DATEV2('infinity')",
+ "DATEV2('\$2023')",
+ "DATEV2('0xFF')",
+ "DATEV2('123,456')",
+ "DATEV2('１２３４５')",
+ "DATEV2('1.2e+3')",
+ "DATEV2(' -2023 ')",
+ "DATEV2('3.1415π')",
+ "DATEV2('12/31/2023')",
+ "DATEV2('32-01-2023')",
+ "DATEV2('2023/02/29')",
+ "DATEV2('1.7976931348623157E308')",
+ "DATEV2('1E-30')",
+ "DATEV2('true')",
+ "DATEV2('false')",
+ "DATEV2('NULL')",
+ "DATEV2('123LATIN')",
+ "DATEV2('0x2023')",
+ "DATEV2('123.45test')",
+ "DATEV2('2023-W50-5')",
+ "DATEV2('2023-367')",
+ "DATEV2('3.14.15')",
+ "DATEV2('+-2023')",
+ "DATEV2('１２３.４５')",
+ "DATEV2('2023-02-28 25:00')",
+ "DATEV2('2023-02-29T00:00:00')",
+ "DATEV2('0000-00-00 00:00:00')",
+ "DATEV2('January 32, 2023')",
+ "DATEV2('February 29, 2023')",
+ "DATEV2('April 31, 2023')",
+ "DATEV2('13/01/2023')",
+ "DATEV2('2023-1-32')",
+ "DATEV2('1-1-2023')",
+ "DATEV2('１月１日')",
+ "DATEV2('１２３４５６７８')",
+ "DATEV2('2023-02-28 24:00:00')",
+ "DATEV2('2023-02-28 23:59:60')",
+ "DATEV2('170141183460469231731687303715884105727')",
+ "DATEV2('0')",
+ "DATEV2('123456789012345678901234567890')",
+ "DATEV2('１２３４５６７８９０')",
+ "QUARTER('20230229')",
+ "QUARTER('abc')",
+ "QUARTER('日本語')",
+ "QUARTER('ññó')",
+ "QUARTER('')",
+ "QUARTER(' ')",
+ "QUARTER('12:34:56')",
+ "QUARTER('NaN')",
+ "QUARTER('infinity')",
+ "QUARTER('\$2023')",
+ "QUARTER('0xFF')",
+ "QUARTER('123,456')",
+ "QUARTER('１２３４５')",
+ "QUARTER('1.2e+3')",
+ "QUARTER(' -2023 ')",
+ "QUARTER('3.1415π')",
+ "QUARTER('12/31/2023')",
+ "QUARTER('32-01-2023')",
+ "QUARTER('2023/02/29')",
+ "QUARTER('1.7976931348623157E308')",
+ "QUARTER('1E-30')",
+ "QUARTER('true')",
+ "QUARTER('false')",
+ "QUARTER('NULL')",
+ "QUARTER('123LATIN')",
+ "QUARTER('0x2023')",
+ "QUARTER('123.45test')",
+ "QUARTER('2023-W50-5')",
+ "QUARTER('2023-367')",
+ "QUARTER('3.14.15')",
+ "QUARTER('+-2023')",
+ "QUARTER('１２３.４５')",
+ "QUARTER('2023-02-28 25:00')",
+ "QUARTER('10000-01-01')",
+ "QUARTER('January 32, 2023')",
+ "QUARTER('February 29, 2023')",
+ "QUARTER('April 31, 2023')",
+ "QUARTER('13/01/2023')",
+ "QUARTER('１月１日')",
+ "QUARTER('170141183460469231731687303715884105727')",
+ "QUARTER('0')",
+ "QUARTER('123456789012345678901234567890')",
+ "QUARTER('999999999999999999999999999999-99-99')",
+ "QUARTER('１２３４５６７８９０')",
+ "QUARTER('2023-02-28 24:00:00')",
+ "QUARTER('2023-02-28 23:59:60')",
+ "QUARTER('2023-1-32')",
+ "QUARTER('1-1-2023')",
+ "QUARTER('9999999999999999-99-99')",
+ "QUARTER('123.456.789')",
+ "QUARTER('+-1.57')",
+ "QUARTER('0x1A')",
+ "QUARTER('3π/2')",
+ "QUARTER('2023-02-29T00:00:00')",
+ "QUARTER('2023年七月十五日')",
+ "QUARTER('１２３４５六七八')",
+ "QUARTER('1/0')",
+ "QUARTER('Q4-2023')",
+ "QUARTER('2023-Q4')",
+ "QUARTER('2023Q4')",
+ "WEEK('invalid_date')",
+ "WEEK('12:34:56.789')",
+ "WEEK('')",
+ "WEEK('2023-W30-1')",
+ "WEEK('2023-06-15', WEEK('invalid_date'))",
+ "YEAR('20230229')",
+ "YEAR('abc')",
+ "YEAR('日本語')",
+ "YEAR('ññó')",
+ "YEAR('')",
+ "YEAR(' ')",
+ "YEAR('12:34:56')",
+ "YEAR('NaN')",
+ "YEAR('infinity')",
+ "YEAR('\$2023')",
+ "YEAR('0xFF')",
+ "YEAR('123,456')",
+ "YEAR('１２３４５')",
+ "YEAR('1.2e+3')",
+ "YEAR(' -2023 ')",
+ "YEAR('3.1415π')",
+ "YEAR('12/31/2023')",
+ "YEAR('32-01-2023')",
+ "YEAR('2023/02/29')",
+ "YEAR('1.7976931348623157E308')",
+ "YEAR('1E-30')",
+ "YEAR('true')",
+ "YEAR('false')",
+ "YEAR('NULL')",
+ "YEAR('123LATIN')",
+ "YEAR('0x2023')",
+ "YEAR('123.45test')",
+ "YEAR('2023-W50-5')",
+ "YEAR('2023-367')",
+ "YEAR('3.14.15')",
+ "YEAR('+-2023')",
+ "YEAR('１２３.４５')",
+ // "YEAR('2023-02-28 25:00')",
+ "YEAR('10000-01-01')",
+ "YEAR('January 32, 2023')",
+ "YEAR('February 29, 2023')",
+ "YEAR('April 31, 2023')",
+ "YEAR('13/01/2023')",
+ "YEAR('１月１日')",
+ "YEAR('170141183460469231731687303715884105727')",
+ "YEAR('0')",
+ "YEAR('123456789012345678901234567890')",
+ "YEAR('999999999999999999999999999999-99-99')",
+ "YEAR('１２３４５６７８９０')",
+ // "YEAR('2023-02-28 24:00:00')",
+ // "YEAR('2023-02-28 23:59:60')",
+ "YEAR('2023-1-32')",
+ "YEAR('1-1-2023')",
+ "YEAR('9999999999999999-99-99')",
+ "YEAR('123.456.789')",
+ "YEAR('+-1.57')",
+ "YEAR('0x1A')",
+ "YEAR('3π/2')",
+ "YEAR('2023-02-29T00:00:00')",
+ "DATE_TRUNC('1st Jun 2007 14:15:20', 'second')",
+ "DATE_TRUNC('1st Jun 2007 14:15:20', 'minute')",
+ "DATE_TRUNC('1st Jun 2007 14:15:20', 'hour')",
+ "DATE_TRUNC('1st Jun 2007', 'day')",
+ "DATE_TRUNC('1st Jun 2007', 'week')",
+ "DATE_TRUNC('1st Jun 2007', 'month')",
+ "DATE_TRUNC('1st Jun 2007', 'quarter')",
+ "DATE_TRUNC('1st Jun 2007', 'year')",
+ "DATE_TRUNC('15th Dec 2012 20:30:40', 'second')",
+ "DATE_TRUNC('15th Dec 2012 20:30:40', 'minute')",
+ "DATE_TRUNC('15th Dec 2012 20:30:40', 'hour')",
+ "DATE_TRUNC('15th Dec 2012', 'day')",
+ "DATE_TRUNC('15th Dec 2012', 'week')",
+ "DATE_TRUNC('15th Dec 2012', 'month')",
+ "DATE_TRUNC('15th Dec 2012', 'quarter')",
+ "DATE_TRUNC('15th Dec 2012', 'year')",
+ "DATE_TRUNC('22nd Mar 2020 07:55:05', 'second')",
+ "DATE_TRUNC('22nd Mar 2020 07:55:05', 'minute')",
+ "DATE_TRUNC('22nd Mar 2020 07:55:05', 'hour')",
+ "DATE_TRUNC('22nd Mar 2020', 'day')",
+ "DATE_TRUNC('22nd Mar 2020', 'week')",
+ "DATE_TRUNC('22nd Mar 2020', 'month')",
+ "DATE_TRUNC('22nd Mar 2020', 'quarter')",
+ "DATE_TRUNC('22nd Mar 2020', 'year')",
+ "DATE_TRUNC('2021-02-29 11:25:35', 'second')",
+ "DATE_TRUNC('2021-02-29 11:25:35', 'minute')",
+ "DATE_TRUNC('2021-02-29 11:25:35', 'hour')",
+ "DATE_TRUNC('2023/04/31 18:40:10', 'second')",
+ "DATE_TRUNC('2023/04/31 18:40:10', 'minute')",
+ "DATE_TRUNC('2023/04/31 18:40:10', 'hour')",
+ "DATE_TRUNC('2023/04/31', 'day')",
+ "DATE_TRUNC('2023/04/31', 'week')",
+ "DATE_TRUNC('2023/04/31', 'month')",
+ "DATE_TRUNC('2023/04/31', 'quarter')",
+ "DATE_TRUNC('2023/04/31', 'year')",
+ "WEEKDAY('invalid_date')",
+ "WEEKDAY('12:34:56.789')",
+ "WEEKDAY('')",
+ "WEEKDAY('2023-W40-1')",
+ "WEEKDAY('10-Oct-2023')",
+ "WEEKDAY('October 10, 2023')",
+ "WEEKOFYEAR('invalid_date')",
+ "weekofyear('12:34:56.789')",
+ "weekofyear('')",
+ "weekofyear('2023-W30-1')",
+ "YEARWEEK('invalid_date')",
+ "yearweek('12:34:56.789')",
+ "yearweek('')",
+ "yearweek('2023-W30-1')",
+ "YEARWEEK('2023-06-15', WEEK('invalid_date'))",
+ "yearweek('2023-06-15', WEEK('invalid_date'))",
+ "YEARWEEK('2023-06-15', WEEK('invalid_date'))",
+ "yearweek('2023-06-15', WEEK('invalid_date'))",
+ "HOUR('2024-01-01 12:00:00+')",
+ "timestamp('2023-02-29 14:30:00')",
+ "timestamp('1999-04-31 08:15:00')",
+ "timestamp('2000-00-00 00:00:00')",
+ "timestamp('1st Jun 2007 09:45:30')",
+ "timestamp('三〇〇〇-一-一')",
+ "timestamp('31/04/2022 16:20')",
+ "TIMESTAMP('2023/04/31')",
+ "TO_DATE('1st Jun 2007')",
+ "TO_DATE('15th Dec 2012')",
+ "TO_DATE('22nd Mar 2020')",
+ "TO_DATE('2023/04/31')",
+ "TO_DATE('1st Jun 2007 11:15:00')",
+ "TO_DATE('15th Dec 2012 17:30:00')",
+ "TO_DATE('22nd Mar 2020 05:45:00')",
+ "TO_DATE('2024-04-31 10:30:45')",
+ "TO_DATE('1st Jun 2007 14:20')",
+ "TO_DATE('31/12/1999')",
+ "TO_DATE('2025-13-01 00:00')",
+ "TO_DATE('2007-Jun-1st')",
+ "TO_DATE('12-31-1999 23:59')",
+ "TO_DATE('30-Feb-2023')",
+ "TO_DATE('2026-02-28 24:00:00')",
+ "to_monday('1st Jun 2007')",
+ "to_monday('15th Dec 2012')",
+ "to_monday('22nd Mar 2020')",
+ "to_monday('2023/04/31')",
+ "to_monday('2023-2-29')",
+ "to_monday('31st Apr 1999')",
+ "to_monday('0th Mar 2025')",
+ "to_monday('2025-04-31 08:15:00')",
+ "to_monday('1st Jun 2007')",
+ "LAST_DAY('1st Jun 2007')",
+ "LAST_DAY('15th Dec 2012')",
+ "LAST_DAY('22nd Mar 2020')",
+ "LAST_DAY('2023/04/31')",
+ "LAST_DAY('1st Jun 2007')",
+ "LAST_DAY('07/20/1969')",
+ "LAST_DAY('15-May-1999')",
+ "LAST_DAY('31十二月2023')",
+ "LAST_DAY('29-Feb-2023')",
+ "LAST_DAY('2023/04/31')",
+ "LAST_DAY('0-0-0')",
+ "TO_DAYS('1st Jun 2007')",
+ "TO_DAYS('15th Dec 2012')",
+ "TO_DAYS('22nd Mar 2020')",
+ "TO_DAYS('2023/04/31')",
+ "TO_DAYS('1st Jun 2007 11:15:00')",
+ "TO_DAYS('15th Dec 2012 17:30:00')",
+ "TO_DAYS('22nd Mar 2020 05:45:00')",
+ "TO_DAYS('1st Jun 2007')",
+ "TO_DAYS('3rd Mar 1990')",
+ "TO_DAYS('20230431')",
+ "TO_DAYS('2007-Jun-01')",
+ "TO_DAYS('2007/Jun/01')",
+ "TO_DAYS('31-Apr-2023')",
+ "FROM_DAYS(TO_DAYS('1st Jun 2007'))",
+ "FROM_DAYS(TO_DAYS('15th Dec 2012'))",
+ "FROM_DAYS(TO_DAYS('22nd Mar 2020'))",
+ "FROM_DAYS(TO_DAYS('2023/04/31'))",
+ ]
+
+ def hour_strs = [
+ "HOUR('2024-01-01 12:00:00')",
+ "HOUR('2024-01-01 12:00:00:')",
+ "HOUR('2024-01-01 12:00:00\\\"')",
+ "HOUR('2024-01-01 12:00:00\\'')",
+ "HOUR('2024-01-01 12:00:00<')",
+ "HOUR('2024-01-01 12:00:00>')",
+ "HOUR('2024-01-01 12:00:00,')",
+ "HOUR('2024-01-01 12:00:00.')",
+ "HOUR('2024-01-01 12:00:00?')",
+ "HOUR('2024-01-01 12:00:00/')",
+ "HOUR('2024-01-01 12:00:00!')",
+ "HOUR('2024-01-01 12:00:00@')",
+ "HOUR('2024-01-01 12:00:00#')",
+ "HOUR('2024-01-01 12:00:00\$')",
+ "HOUR('2024-01-01 12:00:00%')",
+ "HOUR('2024-01-01 12:00:00^')",
+ "HOUR('2024-01-01 12:00:00&')",
+ "HOUR('2024-01-01 12:00:00*')",
+ "HOUR('2024-01-01 12:00:00(')",
+ "HOUR('2024-01-01 12:00:00)')",
+ "HOUR('2024-01-01 12:00:00-')",
+ "HOUR('2024-01-01 12:00:00_')",
+ "HOUR('2024-01-01 12:00:00=')",
+ "HOUR('2024-01-01 12:00:00[')",
+ "HOUR('2024-01-01 12:00:00]')",
+ "HOUR('2024-01-01 12:00:00{')",
+ "HOUR('2024-01-01 12:00:00}')",
+ "HOUR('2024-01-01 12:00:00|')",
+ "HOUR('2024-01-01 12:00:00;')",
+ "HOUR('2024-01-01 12:00:00:')",
+ "HOUR('2024-01-01 12:00:00:')",
+ "HOUR('2024-01-01 12:00:00\\'')",
+ "HOUR('2024-01-01 12:00:00<')",
+ "HOUR('2024-01-01 12:00:00>')",
+ "HOUR('2024-01-01 12:00:00,')",
+ "HOUR('2024-01-01 12:00:00.')",
+ "HOUR('2024-01-01 12:00:00?')",
+ "HOUR('2024-01-01 12:00:00/')",
+ ]
+
+ for (def val : [true, false]) {
+ sql "set debug_skip_fold_constant = ${val}"
+
+ for (def s : wrong_date_strs) {
+ test {
+ sql "SELECT ${s}"
+ result([[null]])
+ }
+ }
+
+ for (def s : hour_strs) {
+ test {
+ sql "SELECT ${s}"
+ result([[12]])
+ }
+ }
+
+ test {
+ sql "select cast('123.123' as date)"
+ result([[Date.valueOf('2012-03-12')]])
+ }
+
+ test {
+ sql "select DATE('2023年01月01日')"
+ result([[Date.valueOf('2023-01-01')]])
+ }
+
+ test {
+ sql "select DATEV2('2023年01月01日')"
+ result([[Date.valueOf('2023-01-01')]])
+ }
+
+ test {
+ sql "select QUARTER('2023年01月01日')"
+ result([[1]])
+ }
+
+ test {
+ sql "select YEAR('2023年01月01日')"
+ result([[2023]])
+ }
+
+ test {
+ sql "select to_monday('1970-01-04')"
+ result([[Date.valueOf('1970-01-01')]])
+ }
+
+ test {
+ sql "select cast('123.123' as datetime)"
+ result([[LocalDateTime.parse('2012-03-12T03:00:00')]])
+ }
+
+ test {
+ sql "select cast('123.123.123' as datetime)"
+ result([[LocalDateTime.parse('2012-03-12T03:12:03')]])
+ }
+
+ test {
+ sql "SELECT DATEADD(DAY, 1, '2025年06月20日')"
+ result([[LocalDateTime.parse('2025-06-21T00:00:00')]])
+ }
+
+ test {
+ sql "select date_add('2023-02-28 16:00:00 UTC', INTERVAL 1 DAY)"
+ result([[LocalDateTime.parse('2023-03-01T16:00:00')]])
+ }
+
+ test {
+ sql "select date_add('2023-02-28 16:00:00 America/New_York',
INTERVAL 1 DAY)"
+ result([[LocalDateTime.parse('2023-03-01T16:00:00')]])
+ }
+
+ test {
+ sql "select date_add('2023-02-28 16:00:00UTC', INTERVAL 1 DAY)"
+ result([[LocalDateTime.parse('2023-03-02T00:00:00')]])
+ }
+
+ test {
+ sql "select date_add('2023-02-28 16:00:00America/New_York',
INTERVAL 1 DAY)"
+ result([[LocalDateTime.parse('2023-03-02T05:00:00')]])
+ }
+
+ test {
+ sql "select date_add('2023-02-28 UTC', INTERVAL 1 DAY)"
+ result([[LocalDateTime.parse('2023-03-01T00:00:00')]])
+ }
+
+ test {
+ sql "select date_add('2023-02-28 America/New_York', INTERVAL 1
DAY)"
+ result([[LocalDateTime.parse('2023-03-01T00:00:00')]])
+ }
+
+ test {
+ sql "select date_add('2023-02-28UTC', INTERVAL 1 DAY)"
+ result([[LocalDateTime.parse('2023-03-01T00:00:00')]])
+ }
+
+ test {
+ sql "select date_add('2023-02-28America/New_York', INTERVAL 1 DAY)"
+ result([[LocalDateTime.parse('2023-03-01T00:00:00')]])
+ }
+
+ test {
+ sql "select date_add('2023-02-28 UTC', INTERVAL 1 DAY)"
+ result([[LocalDateTime.parse('2023-03-01T00:00:00')]])
+ }
+
+ test {
+ sql "select date_add('2023-02-28 America/New_York', INTERVAL 1
DAY)"
+ result([[LocalDateTime.parse('2023-03-01T00:00:00')]])
+ }
+
+ test {
+ sql "select date_add('2024-02-29 UTC', INTERVAL 6 DAY)"
+ result([[LocalDateTime.parse('2024-03-06T00:00:00')]])
+ }
+
+ test {
+ sql "select date_add('2020-02-29 America/New_York', INTERVAL -3
DAY)"
+ result([[LocalDateTime.parse('2020-02-26T00:00:00')]])
+ }
+
+ test {
+ sql "select date_add('2023-03-12 01:30:00 Europe/London', INTERVAL
1 DAY)"
+ result([[LocalDateTime.parse('2023-03-13T01:30:00')]])
+ }
+
+ test {
+ sql "select date_add('2023-11-05 01:30:00 America/New_York',
INTERVAL 1 DAY)"
+ result([[LocalDateTime.parse('2023-11-06T01:30:00')]])
+ }
+
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
