This is an automated email from the ASF dual-hosted git repository.
zhangstar333 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 0c5703fd129 [fix](function) Fix wrong result of week/yearweek in
constant folding (#37376)
0c5703fd129 is described below
commit 0c5703fd12928a5751c832ee495bbbd06ed3991a
Author: zclllyybb <[email protected]>
AuthorDate: Mon Jul 8 11:47:09 2024 +0800
[fix](function) Fix wrong result of week/yearweek in constant folding
(#37376)
## Proposed changes
Issue Number: close #xxx
Java doesn't support the calculation of `0000-01-01`(0000 year is 1 B.C.
which ISO-8601 defines). so some of our calc was wrong in FE. now fixed
them.
new result:
```sql
mysql> select week('0000-01-01', 0),week('0000-01-01',
1),week('0000-01-01', 2),week('0000-01-01', 3),week('0000-01-01', 4),
week('0000-01-01', 5), week('0000-01-01', 6), week('0000-01-01', 7);
+------+------+------+------+------+------+------+------+
| 1 | 0 | 1 | 52 | 1 | 0 | 1 | 52 |
+------+------+------+------+------+------+------+------+
| 1 | 0 | 1 | 52 | 1 | 0 | 1 | 52 |
+------+------+------+------+------+------+------+------+
1 row in set (0.10 sec)
mysql> select week('0000-01-02', 0),week('0000-01-02',
1),week('0000-01-02', 2),week('0000-01-02', 3),week('0000-01-02', 4),
week('0000-01-02', 5), week('0000-01-02', 6), week('0000-01-02', 7);
+------+------+------+------+------+------+------+------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
+------+------+------+------+------+------+------+------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
+------+------+------+------+------+------+------+------+
1 row in set (0.10 sec)
```
same with `yearweek`
---
.../executable/DateTimeExtractAndTransform.java | 43 +++++++-------
.../functions/DateTimeExtractAndTransformTest.java | 65 +++++++++++++---------
2 files changed, 60 insertions(+), 48 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 a5cffe3dabd..f719eea44b3 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
@@ -707,6 +707,15 @@ public class DateTimeExtractAndTransform {
* the impl of function week(date/datetime, mode)
*/
public static Expression week(LocalDateTime localDateTime, int mode) {
+ final byte[] resultOfFirstDayBC1 = new byte[] { 1, 0, 1, 52, 1, 0, 1,
52 };
+ if (isSpecificDate(localDateTime) && mode >= 0 && mode <= 7) { //
0000-01-01/02
+ if (localDateTime.getDayOfMonth() == 1) {
+ return new TinyIntLiteral(resultOfFirstDayBC1[mode]);
+ } else { // 0001-01-02
+ return new TinyIntLiteral((byte) 1);
+ }
+ }
+
switch (mode) {
case 0: {
return new TinyIntLiteral(
@@ -716,13 +725,6 @@ 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()));
}
@@ -753,6 +755,14 @@ public class DateTimeExtractAndTransform {
}
}
+ /**
+ * 0000-01-01/02 are specific dates, sometime need handle them alone.
+ */
+ private static boolean isSpecificDate(LocalDateTime localDateTime) {
+ return localDateTime.getYear() == 0 && localDateTime.getMonthValue()
== 1
+ && (localDateTime.getDayOfMonth() == 1 ||
localDateTime.getDayOfMonth() == 2);
+ }
+
@ExecFunction(name = "yearweek", argTypes = {"DATEV2", "INT"}, returnType
= "INT")
public static Expression yearWeek(DateV2Literal date, IntegerLiteral mode)
{
return yearWeek(date.toJavaDateType(), mode.getIntValue());
@@ -787,11 +797,12 @@ public class DateTimeExtractAndTransform {
* the impl of function yearWeek(date/datetime, mode)
*/
public static Expression yearWeek(LocalDateTime localDateTime, int mode) {
+ if (localDateTime.getYear() == 0) {
+ return week(localDateTime, 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(
@@ -802,9 +813,6 @@ 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(
@@ -840,18 +848,11 @@ public class DateTimeExtractAndTransform {
}
default: {
throw new AnalysisException(
- String.format("unknown mode %d in week function",
mode));
+ String.format("unknown mode %d in yearweek function",
mode));
}
}
}
- /**
- * 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
index 10aa5939e31..51e77e3feda 100644
---
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
@@ -18,7 +18,6 @@
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;
@@ -28,32 +27,44 @@ 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));
- }
+ void testSpecialDateWeeks() {
+ // test week/yearweek for 0000-01-01/02
+ LocalDateTime time1 = LocalDateTime.of(0, 1, 1, 0, 0, 0, 0);
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.week(time1, 0));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 0),
DateTimeExtractAndTransform.week(time1, 1));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.week(time1, 2));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 52),
DateTimeExtractAndTransform.week(time1, 3));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.week(time1, 4));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 0),
DateTimeExtractAndTransform.week(time1, 5));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.week(time1, 6));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 52),
DateTimeExtractAndTransform.week(time1, 7));
- @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));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.yearWeek(time1, 0));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 0),
DateTimeExtractAndTransform.yearWeek(time1, 1));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.yearWeek(time1, 2));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 52),
DateTimeExtractAndTransform.yearWeek(time1, 3));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.yearWeek(time1, 4));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 0),
DateTimeExtractAndTransform.yearWeek(time1, 5));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.yearWeek(time1, 6));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 52),
DateTimeExtractAndTransform.yearWeek(time1, 7));
+
+ LocalDateTime time2 = LocalDateTime.of(0, 1, 2, 0, 0, 0, 0);
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.week(time2, 0));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.week(time2, 1));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.week(time2, 2));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.week(time2, 3));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.week(time2, 4));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.week(time2, 5));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.week(time2, 6));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.week(time2, 7));
+
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.yearWeek(time2, 0));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.yearWeek(time2, 1));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.yearWeek(time2, 2));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.yearWeek(time2, 3));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.yearWeek(time2, 4));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.yearWeek(time2, 5));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.yearWeek(time2, 6));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.yearWeek(time2, 7));
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
