This is an automated email from the ASF dual-hosted git repository.
zhangstar333 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 1a242b8ae09 [cherry-pick](branch2.1) fix week/yearweek function get
wrong result (#36538)
1a242b8ae09 is described below
commit 1a242b8ae09cfd9ed02109729b0a94a00dd308a1
Author: zhangstar333 <87313068+zhangstar...@users.noreply.github.com>
AuthorDate: Thu Jun 20 15:48:19 2024 +0800
[cherry-pick](branch2.1) fix week/yearweek function get wrong result
(#36538)
## Proposed changes
cherry-pick from master #36000 #36159
---
.../executable/DateTimeExtractAndTransform.java | 30 ++++++++++-
.../functions/DateTimeExtractAndTransformTest.java | 59 ++++++++++++++++++++++
.../suites/nereids_syntax_p0/explain.groovy | 6 +++
3 files changed, 93 insertions(+), 2 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 e7a92354440..b6960d4384b 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
@@ -687,7 +687,10 @@ public class DateTimeExtractAndTransform {
return week(date.toJavaDateType(), mode.getIntValue());
}
- private static Expression week(LocalDateTime localDateTime, int mode) {
+ /**
+ * the impl of function week(date/datetime, mode)
+ */
+ public static Expression week(LocalDateTime localDateTime, int mode) {
switch (mode) {
case 0: {
return new TinyIntLiteral(
@@ -697,6 +700,13 @@ public class DateTimeExtractAndTransform {
return new TinyIntLiteral((byte)
localDateTime.get(WeekFields.ISO.weekOfYear()));
}
case 2: {
+ //
https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_week
+ // mode 2 is start with a Sunday day as first week in this
year.
+ // and special case for 0000-01-01, as it's SATURDAY,
calculate result of 52 is
+ // last year, so it's meaningless.
+ if (checkIsSpecificDate(localDateTime)) {
+ return new TinyIntLiteral((byte) 1);
+ }
return new TinyIntLiteral(
(byte)
localDateTime.get(WeekFields.of(DayOfWeek.SUNDAY, 7).weekOfWeekBasedYear()));
}
@@ -757,9 +767,15 @@ public class DateTimeExtractAndTransform {
return yearWeek(dateTime.toJavaDateType(), 0);
}
- private static Expression yearWeek(LocalDateTime localDateTime, int mode) {
+ /**
+ * the impl of function yearWeek(date/datetime, mode)
+ */
+ public static Expression yearWeek(LocalDateTime localDateTime, int mode) {
switch (mode) {
case 0: {
+ if (checkIsSpecificDate(localDateTime)) {
+ return new IntegerLiteral(1);
+ }
return new IntegerLiteral(
localDateTime.get(WeekFields.of(DayOfWeek.SUNDAY,
7).weekBasedYear()) * 100
+ localDateTime.get(
@@ -770,6 +786,9 @@ public class DateTimeExtractAndTransform {
+
localDateTime.get(WeekFields.ISO.weekOfWeekBasedYear()));
}
case 2: {
+ if (checkIsSpecificDate(localDateTime)) {
+ return new IntegerLiteral(1);
+ }
return new IntegerLiteral(
localDateTime.get(WeekFields.of(DayOfWeek.SUNDAY,
7).weekBasedYear()) * 100
+ localDateTime.get(
@@ -810,6 +829,13 @@ public class DateTimeExtractAndTransform {
}
}
+ /**
+ * 0000-01-01 is specific date, sometime need handle it alone.
+ */
+ private static boolean checkIsSpecificDate(LocalDateTime localDateTime) {
+ return localDateTime.getYear() == 0 && localDateTime.getMonthValue()
== 1 && localDateTime.getDayOfMonth() == 1;
+ }
+
@ExecFunction(name = "weekofyear", argTypes = {"DATETIMEV2"}, returnType =
"TINYINT")
public static Expression weekOfYear(DateTimeV2Literal dateTime) {
return new TinyIntLiteral((byte)
dateTime.toJavaDateType().get(WeekFields.ISO.weekOfWeekBasedYear()));
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/DateTimeExtractAndTransformTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/DateTimeExtractAndTransformTest.java
new file mode 100644
index 00000000000..10aa5939e31
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/functions/DateTimeExtractAndTransformTest.java
@@ -0,0 +1,59 @@
+// 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.
+
+package org.apache.doris.nereids.trees.expressions.functions;
+
+import
org.apache.doris.nereids.trees.expressions.functions.executable.DateTimeExtractAndTransform;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.TinyIntLiteral;
+
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+import java.time.LocalDateTime;
+
+class DateTimeExtractAndTransformTest {
+ @Test
+ void testWeekMode2Function() {
+ LocalDateTime localDateTime = LocalDateTime.of(0, 1, 1, 0, 0, 0, 0);
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.week(localDateTime, 2));
+ LocalDateTime localDateTime2 = localDateTime.plusDays(1);
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.week(localDateTime2, 2));
+ LocalDateTime localDateTime3 = LocalDateTime.of(0, 1, 9, 0, 0, 0, 0);
+ Assertions.assertEquals(new TinyIntLiteral((byte) 2),
DateTimeExtractAndTransform.week(localDateTime3, 2));
+
+ LocalDateTime localDateTime4 = LocalDateTime.of(0, 12, 30, 0, 0, 0, 0);
+ Assertions.assertEquals(new TinyIntLiteral((byte) 52),
DateTimeExtractAndTransform.week(localDateTime4, 2));
+ LocalDateTime localDateTime5 = localDateTime4.plusDays(1);
+ Assertions.assertEquals(new TinyIntLiteral((byte) 53),
DateTimeExtractAndTransform.week(localDateTime5, 2));
+ }
+
+ @Test
+ void testYearWeekMode2Function() {
+ LocalDateTime localDateTime = LocalDateTime.of(0, 1, 1, 0, 0, 0, 0);
+ Assertions.assertEquals(new IntegerLiteral(1),
DateTimeExtractAndTransform.yearWeek(localDateTime, 2));
+ LocalDateTime localDateTime2 = localDateTime.plusDays(1);
+ Assertions.assertEquals(new IntegerLiteral(1),
DateTimeExtractAndTransform.yearWeek(localDateTime2, 2));
+ LocalDateTime localDateTime3 = LocalDateTime.of(0, 1, 9, 0, 0, 0, 0);
+ Assertions.assertEquals(new IntegerLiteral(2),
DateTimeExtractAndTransform.yearWeek(localDateTime3, 2));
+
+ LocalDateTime localDateTime4 = LocalDateTime.of(0, 12, 30, 0, 0, 0, 0);
+ Assertions.assertEquals(new IntegerLiteral(52),
DateTimeExtractAndTransform.yearWeek(localDateTime4, 2));
+ LocalDateTime localDateTime5 = localDateTime4.plusDays(1);
+ Assertions.assertEquals(new IntegerLiteral(53),
DateTimeExtractAndTransform.yearWeek(localDateTime5, 2));
+ }
+}
diff --git a/regression-test/suites/nereids_syntax_p0/explain.groovy
b/regression-test/suites/nereids_syntax_p0/explain.groovy
index f0554473ec3..0e599b1beaa 100644
--- a/regression-test/suites/nereids_syntax_p0/explain.groovy
+++ b/regression-test/suites/nereids_syntax_p0/explain.groovy
@@ -68,4 +68,10 @@ suite("nereids_explain") {
def explainStr = sql("select sum(if(lo_tax=1,lo_tax,0)) from lineorder
where false").toString()
assertTrue(!explainStr.contains("projections"))
+
+ explain {
+ sql("select week(cast('0000-01-01' as DATEV2), cast(2 as INT));")
+ notContains "week"
+ contains "1"
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
