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
commit 467821a5d96f6da9dd2d810fe17cf73d05a14ed8
Author: zclllhhjj <zhaochan...@selectdb.com>
AuthorDate: Fri Jul 19 14:07:18 2024 +0800
[Fix](function) fix some date function impl in FE with special dates
(#37766)
make `dayofyear`, `dayofweek`, `weekofyear` result of FE folding
constant result same with MySQL. mainly about date in BC01 (year `0000`)
before:
```sql
mysql> select
DAYOFWEEK('0000-01-01'),DAYOFWEEK('0000-01-02'),DAYOFWEEK('0000-01-03'),DAYOFWEEK('0000-01-04'),DAYOFWEEK('0000-01-05'),DAYOFWEEK('0000-01-06'),DAYOFWEEK('0000-01-07'),DAYOFWEEK('0000-01-08');
+------+------+------+------+------+------+------+------+
| 7 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
+------+------+------+------+------+------+------+------+
| 7 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
+------+------+------+------+------+------+------+------+
mysql> select
DAYOFYEAR('0000-02-27'),DAYOFYEAR('0000-02-28'),DAYOFYEAR('0000-03-01'),DAYOFYEAR('0000-03-02');
+------+------+------+------+
| 58 | 59 | 61 | 62 |
+------+------+------+------+
| 58 | 59 | 61 | 62 |
+------+------+------+------+
mysql> select
WEEKOFYEAR('0000-01-01'),WEEKOFYEAR('0000-01-02'),WEEKOFYEAR('0000-01-03'),WEEKOFYEAR('0000-01-04'),WEEKOFYEAR('0000-01-05'),WEEKOFYEAR('0000-01-06'),WEEKOFYEAR('0000-01-07'),WEEKOFYEAR('0000-01-08');
+------+------+------+------+------+------+------+------+
| 52 | 52 | 1 | 1 | 1 | 1 | 1 | 1 |
+------+------+------+------+------+------+------+------+
| 52 | 52 | 1 | 1 | 1 | 1 | 1 | 1 |
+------+------+------+------+------+------+------+------+
mysql> select
WEEKOFYEAR('0000-02-25'),WEEKOFYEAR('0000-02-26'),WEEKOFYEAR('0000-02-27'),WEEKOFYEAR('0000-02-28'),WEEKOFYEAR('0000-03-01'),WEEKOFYEAR('0000-03-02'),WEEKOFYEAR('2022-03-03');
+------+------+------+------+------+------+------+
| 8 | 8 | 8 | 9 | 9 | 9 | 9 |
+------+------+------+------+------+------+------+
| 8 | 8 | 8 | 9 | 9 | 9 | 9 |
+------+------+------+------+------+------+------+
```
after:
```sql
mysql> select
DAYOFWEEK('0000-01-01'),DAYOFWEEK('0000-01-02'),DAYOFWEEK('0000-01-03'),DAYOFWEEK('0000-01-04'),DAYOFWEEK('0000-01-05'),DAYOFWEEK('0000-01-06'),DAYOFWEEK('0000-01-07'),DAYOFWEEK('0000-01-08');
+------+------+------+------+------+------+------+------+
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 1 |
+------+------+------+------+------+------+------+------+
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 1 |
+------+------+------+------+------+------+------+------+
mysql> select
DAYOFYEAR('0000-02-27'),DAYOFYEAR('0000-02-28'),DAYOFYEAR('0000-03-01'),DAYOFYEAR('0000-03-02');
+------+------+------+------+
| 58 | 59 | 60 | 61 |
+------+------+------+------+
| 58 | 59 | 60 | 61 |
+------+------+------+------+
mysql> select
WEEKOFYEAR('0000-01-01'),WEEKOFYEAR('0000-01-02'),WEEKOFYEAR('0000-01-03'),WEEKOFYEAR('0000-01-04'),WEEKOFYEAR('0000-01-05'),WEEKOFYEAR('0000-01-06'),WEEKOFYEAR('0000-01-07'),WEEKOFYEAR('0000-01-08');
+------+------+------+------+------+------+------+------+
| 52 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
+------+------+------+------+------+------+------+------+
| 52 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
+------+------+------+------+------+------+------+------+
mysql> select
WEEKOFYEAR('0000-02-25'),WEEKOFYEAR('0000-02-26'),WEEKOFYEAR('0000-02-27'),WEEKOFYEAR('0000-02-28'),WEEKOFYEAR('0000-03-01'),WEEKOFYEAR('0000-03-02'),WEEKOFYEAR('2022-03-03');
+------+------+------+------+------+------+------+
| 8 | 8 | 9 | 9 | 9 | 9 | 9 |
+------+------+------+------+------+------+------+
| 8 | 8 | 9 | 9 | 9 | 9 | 9 |
+------+------+------+------+------+------+------+
```
---
.../executable/DateTimeExtractAndTransform.java | 45 ++++++++++++++---
.../trees/expressions/literal/DateLiteral.java | 15 ++++++
.../functions/DateTimeExtractAndTransformTest.java | 57 ++++++++++++++++++++++
.../data/correctness_p0/test_cast_date_decimal.out | 5 +-
.../correctness_p0/test_cast_date_decimal.groovy | 6 ++-
5 files changed, 118 insertions(+), 10 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 c14b372f201..9742602a07a 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
@@ -216,22 +216,22 @@ public class DateTimeExtractAndTransform {
*/
@ExecFunction(name = "dayofyear", argTypes = {"DATE"}, returnType =
"SMALLINT")
public static Expression dayOfYear(DateLiteral date) {
- return new SmallIntLiteral((short)
date.toJavaDateType().getDayOfYear());
+ return new SmallIntLiteral((short) date.getDayOfYear());
}
@ExecFunction(name = "dayofyear", argTypes = {"DATETIME"}, returnType =
"SMALLINT")
public static Expression dayOfYear(DateTimeLiteral date) {
- return new SmallIntLiteral((short)
date.toJavaDateType().getDayOfYear());
+ return new SmallIntLiteral((short) date.getDayOfYear());
}
@ExecFunction(name = "dayofyear", argTypes = {"DATEV2"}, returnType =
"SMALLINT")
public static Expression dayOfYear(DateV2Literal date) {
- return new SmallIntLiteral((short)
date.toJavaDateType().getDayOfYear());
+ return new SmallIntLiteral((short) date.getDayOfYear());
}
@ExecFunction(name = "dayofyear", argTypes = {"DATETIMEV2"}, returnType =
"SMALLINT")
public static Expression dayOfYear(DateTimeV2Literal date) {
- return new SmallIntLiteral((short)
date.toJavaDateType().getDayOfYear());
+ return new SmallIntLiteral((short) date.getDayOfYear());
}
/**
@@ -262,22 +262,22 @@ public class DateTimeExtractAndTransform {
*/
@ExecFunction(name = "dayofweek", argTypes = {"DATE"}, returnType =
"TINYINT")
public static Expression dayOfWeek(DateLiteral date) {
- return new TinyIntLiteral((byte)
(date.toJavaDateType().getDayOfWeek().getValue() % 7 + 1));
+ return new TinyIntLiteral((byte) (date.getDayOfWeek() % 7 + 1));
}
@ExecFunction(name = "dayofweek", argTypes = {"DATETIME"}, returnType =
"TINYINT")
public static Expression dayOfWeek(DateTimeLiteral date) {
- return new TinyIntLiteral((byte)
(date.toJavaDateType().getDayOfWeek().getValue() % 7 + 1));
+ return new TinyIntLiteral((byte) (date.getDayOfWeek() % 7 + 1));
}
@ExecFunction(name = "dayofweek", argTypes = {"DATEV2"}, returnType =
"TINYINT")
public static Expression dayOfWeek(DateV2Literal date) {
- return new TinyIntLiteral((byte)
(date.toJavaDateType().getDayOfWeek().getValue() % 7 + 1));
+ return new TinyIntLiteral((byte) (date.getDayOfWeek() % 7 + 1));
}
@ExecFunction(name = "dayofweek", argTypes = {"DATETIMEV2"}, returnType =
"TINYINT")
public static Expression dayOfWeek(DateTimeV2Literal date) {
- return new TinyIntLiteral((byte)
(date.toJavaDateType().getDayOfWeek().getValue() % 7 + 1));
+ return new TinyIntLiteral((byte) (date.getDayOfWeek() % 7 + 1));
}
private static int distanceToFirstDayOfWeek(LocalDateTime dateTime) {
@@ -865,18 +865,47 @@ public class DateTimeExtractAndTransform {
}
}
+ /**
+ * weekofyear
+ */
@ExecFunction(name = "weekofyear", argTypes = {"DATETIMEV2"}, returnType =
"TINYINT")
public static Expression weekOfYear(DateTimeV2Literal dateTime) {
+ if (dateTime.getYear() == 0 && dateTime.getDayOfWeek() == 1) {
+ if (dateTime.getMonth() == 1 && dateTime.getDay() == 2) {
+ return new TinyIntLiteral((byte) 1);
+ }
+ return new TinyIntLiteral(
+ (byte)
(dateTime.toJavaDateType().get(WeekFields.ISO.weekOfWeekBasedYear()) + 1));
+ }
return new TinyIntLiteral((byte)
dateTime.toJavaDateType().get(WeekFields.ISO.weekOfWeekBasedYear()));
}
+ /**
+ * weekofyear
+ */
@ExecFunction(name = "weekofyear", argTypes = {"DATETIME"}, returnType =
"TINYINT")
public static Expression weekOfYear(DateTimeLiteral dateTime) {
+ if (dateTime.getYear() == 0 && dateTime.getDayOfWeek() == 1) {
+ if (dateTime.getMonth() == 1 && dateTime.getDay() == 2) {
+ return new TinyIntLiteral((byte) 1);
+ }
+ return new TinyIntLiteral(
+ (byte)
(dateTime.toJavaDateType().get(WeekFields.ISO.weekOfWeekBasedYear()) + 1));
+ }
return new TinyIntLiteral((byte)
dateTime.toJavaDateType().get(WeekFields.ISO.weekOfWeekBasedYear()));
}
+ /**
+ * weekofyear
+ */
@ExecFunction(name = "weekofyear", argTypes = {"DATEV2"}, returnType =
"TINYINT")
public static Expression weekOfYear(DateV2Literal date) {
+ if (date.getYear() == 0 && date.getDayOfWeek() == 1) {
+ if (date.getMonth() == 1 && date.getDay() == 2) {
+ return new TinyIntLiteral((byte) 1);
+ }
+ return new TinyIntLiteral((byte)
(date.toJavaDateType().get(WeekFields.ISO.weekOfWeekBasedYear()) + 1));
+ }
return new TinyIntLiteral((byte)
date.toJavaDateType().get(WeekFields.ISO.weekOfWeekBasedYear()));
}
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 46345445b2f..6171707b87f 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
@@ -417,6 +417,21 @@ public class DateLiteral extends Literal {
return day;
}
+ public int getDayOfYear() {
+ if (year == 0 && month == 3 && (day == 1 || day == 2)) {
+ return toJavaDateType().getDayOfYear() - 1;
+ }
+ return toJavaDateType().getDayOfYear();
+ }
+
+ public int getDayOfWeek() {
+ if (year == 0 && (month == 1 || (month == 2 && day <= 28))) {
+ // shift right with 1 offset
+ return toJavaDateType().getDayOfWeek().getValue() % 7 + 1;
+ }
+ return toJavaDateType().getDayOfWeek().getValue();
+ }
+
public Expression plusDays(long days) {
return fromJavaDateType(toJavaDateType().plusDays(days));
}
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 51e77e3feda..11ebeb42f56 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,6 +18,8 @@
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.DateTimeV2Literal;
+import org.apache.doris.nereids.trees.expressions.literal.SmallIntLiteral;
import org.apache.doris.nereids.trees.expressions.literal.TinyIntLiteral;
import org.junit.jupiter.api.Assertions;
@@ -67,4 +69,59 @@ class DateTimeExtractAndTransformTest {
Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.yearWeek(time2, 6));
Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.yearWeek(time2, 7));
}
+
+ @Test
+ void testSpecialDates() {
+ DateTimeV2Literal dt1 = new DateTimeV2Literal(0, 1, 1, 0, 0, 0);
+ DateTimeV2Literal dt2 = new DateTimeV2Literal(0, 1, 2, 0, 0, 0);
+ DateTimeV2Literal dt3 = new DateTimeV2Literal(0, 1, 3, 0, 0, 0);
+ DateTimeV2Literal dt4 = new DateTimeV2Literal(0, 1, 4, 0, 0, 0);
+ DateTimeV2Literal dt5 = new DateTimeV2Literal(0, 1, 5, 0, 0, 0);
+ DateTimeV2Literal dt6 = new DateTimeV2Literal(0, 1, 6, 0, 0, 0);
+ DateTimeV2Literal dt7 = new DateTimeV2Literal(0, 1, 7, 0, 0, 0);
+ DateTimeV2Literal dt8 = new DateTimeV2Literal(0, 1, 8, 0, 0, 0);
+ DateTimeV2Literal dtx1 = new DateTimeV2Literal(0, 2, 27, 0, 0, 0);
+ DateTimeV2Literal dtx2 = new DateTimeV2Literal(0, 2, 28, 0, 0, 0);
+ DateTimeV2Literal dtx3 = new DateTimeV2Literal(0, 3, 1, 0, 0, 0);
+ DateTimeV2Literal dtx4 = new DateTimeV2Literal(0, 3, 2, 0, 0, 0);
+
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.dayOfWeek(dt1));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 2),
DateTimeExtractAndTransform.dayOfWeek(dt2));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 3),
DateTimeExtractAndTransform.dayOfWeek(dt3));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 4),
DateTimeExtractAndTransform.dayOfWeek(dt4));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 5),
DateTimeExtractAndTransform.dayOfWeek(dt5));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 6),
DateTimeExtractAndTransform.dayOfWeek(dt6));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 7),
DateTimeExtractAndTransform.dayOfWeek(dt7));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.dayOfWeek(dt8));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 2),
DateTimeExtractAndTransform.dayOfWeek(dtx1));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 3),
DateTimeExtractAndTransform.dayOfWeek(dtx2));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 4),
DateTimeExtractAndTransform.dayOfWeek(dtx3));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 5),
DateTimeExtractAndTransform.dayOfWeek(dtx4));
+
+ Assertions.assertEquals(new SmallIntLiteral((short) 1),
DateTimeExtractAndTransform.dayOfYear(dt1));
+ Assertions.assertEquals(new SmallIntLiteral((short) 2),
DateTimeExtractAndTransform.dayOfYear(dt2));
+ Assertions.assertEquals(new SmallIntLiteral((short) 3),
DateTimeExtractAndTransform.dayOfYear(dt3));
+ Assertions.assertEquals(new SmallIntLiteral((short) 4),
DateTimeExtractAndTransform.dayOfYear(dt4));
+ Assertions.assertEquals(new SmallIntLiteral((short) 5),
DateTimeExtractAndTransform.dayOfYear(dt5));
+ Assertions.assertEquals(new SmallIntLiteral((short) 6),
DateTimeExtractAndTransform.dayOfYear(dt6));
+ Assertions.assertEquals(new SmallIntLiteral((short) 7),
DateTimeExtractAndTransform.dayOfYear(dt7));
+ Assertions.assertEquals(new SmallIntLiteral((short) 8),
DateTimeExtractAndTransform.dayOfYear(dt8));
+ Assertions.assertEquals(new SmallIntLiteral((short) 58),
DateTimeExtractAndTransform.dayOfYear(dtx1));
+ Assertions.assertEquals(new SmallIntLiteral((short) 59),
DateTimeExtractAndTransform.dayOfYear(dtx2));
+ Assertions.assertEquals(new SmallIntLiteral((short) 60),
DateTimeExtractAndTransform.dayOfYear(dtx3));
+ Assertions.assertEquals(new SmallIntLiteral((short) 61),
DateTimeExtractAndTransform.dayOfYear(dtx4));
+
+ Assertions.assertEquals(new TinyIntLiteral((byte) 52),
DateTimeExtractAndTransform.weekOfYear(dt1));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.weekOfYear(dt2));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.weekOfYear(dt3));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.weekOfYear(dt4));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.weekOfYear(dt5));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.weekOfYear(dt6));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.weekOfYear(dt7));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 1),
DateTimeExtractAndTransform.weekOfYear(dt8));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 9),
DateTimeExtractAndTransform.weekOfYear(dtx1));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 9),
DateTimeExtractAndTransform.weekOfYear(dtx2));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 9),
DateTimeExtractAndTransform.weekOfYear(dtx3));
+ Assertions.assertEquals(new TinyIntLiteral((byte) 9),
DateTimeExtractAndTransform.weekOfYear(dtx4));
+ }
}
diff --git a/regression-test/data/correctness_p0/test_cast_date_decimal.out
b/regression-test/data/correctness_p0/test_cast_date_decimal.out
index 64b3833a142..1738d19a49d 100644
--- a/regression-test/data/correctness_p0/test_cast_date_decimal.out
+++ b/regression-test/data/correctness_p0/test_cast_date_decimal.out
@@ -15,5 +15,8 @@ true
2012-03-12
-- !sql6 --
-\N
+\N \N \N \N
+
+-- !sql7 --
+\N \N \N \N
diff --git
a/regression-test/suites/correctness_p0/test_cast_date_decimal.groovy
b/regression-test/suites/correctness_p0/test_cast_date_decimal.groovy
index 8ad0f12591d..f533b885a14 100644
--- a/regression-test/suites/correctness_p0/test_cast_date_decimal.groovy
+++ b/regression-test/suites/correctness_p0/test_cast_date_decimal.groovy
@@ -37,6 +37,10 @@ suite("test_cast_date_decimal") {
"""
qt_sql6 """
- select cast('0000-02-29' as date);
+ select cast('0000-02-29' as date), cast('0000-02-29' as datetime),
cast('00000229' as date), cast('0000-02-29 12:12:12.123' as datetime);
+ """
+
+ qt_sql7 """
+ select /*+SET_VAR(debug_skip_fold_constant=true)*/ cast('0000-02-29'
as date), cast('0000-02-29' as datetime), cast('00000229' as date),
cast('0000-02-29 12:12:12.123' as datetime);
"""
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
