This is an automated email from the ASF dual-hosted git repository.
starocean999 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 f8a40ad95cc [Fix](Nereids) fix date and date time arithmatic (#40745)
f8a40ad95cc is described below
commit f8a40ad95cc9314d133f9ab50dc6e9d93bba7f53
Author: LiBinfeng <[email protected]>
AuthorDate: Wed Sep 25 18:00:18 2024 +0800
[Fix](Nereids) fix date and date time arithmatic (#40745)
fix date and time arithmatic of: hours_add, minutes_add, seconds_add,
to_days
---
.../functions/executable/DateTimeArithmetic.java | 40 ++++++++++++
.../nereids/rules/expression/FoldConstantTest.java | 72 ++++++++++++++++++++++
.../fold_constant/fold_constant_by_fe.groovy | 2 +
3 files changed, 114 insertions(+)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java
index 15588871016..84e5ebf272a 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java
@@ -174,6 +174,16 @@ public class DateTimeArithmetic {
/**
* datetime arithmetic function hours-add.
*/
+ @ExecFunction(name = "hours_add")
+ public static Expression hoursAdd(DateLiteral date, IntegerLiteral hour) {
+ return date.toBeginOfTheDay().plusHours(hour.getValue());
+ }
+
+ @ExecFunction(name = "hours_add")
+ public static Expression hoursAdd(DateV2Literal date, IntegerLiteral hour)
{
+ return date.toBeginOfTheDay().plusHours(hour.getValue());
+ }
+
@ExecFunction(name = "hours_add")
public static Expression hoursAdd(DateTimeLiteral date, IntegerLiteral
hour) {
return date.plusHours(hour.getValue());
@@ -187,6 +197,16 @@ public class DateTimeArithmetic {
/**
* datetime arithmetic function minutes-add.
*/
+ @ExecFunction(name = "minutes_add")
+ public static Expression minutesAdd(DateLiteral date, IntegerLiteral
minute) {
+ return date.toBeginOfTheDay().plusMinutes(minute.getValue());
+ }
+
+ @ExecFunction(name = "minutes_add")
+ public static Expression minutesAdd(DateV2Literal date, IntegerLiteral
minute) {
+ return date.toBeginOfTheDay().plusMinutes(minute.getValue());
+ }
+
@ExecFunction(name = "minutes_add")
public static Expression minutesAdd(DateTimeLiteral date, IntegerLiteral
minute) {
return date.plusMinutes(minute.getValue());
@@ -200,6 +220,16 @@ public class DateTimeArithmetic {
/**
* datetime arithmetic function seconds-add.
*/
+ @ExecFunction(name = "seconds_add")
+ public static Expression secondsAdd(DateLiteral date, IntegerLiteral
second) {
+ return date.toBeginOfTheDay().plusSeconds(second.getValue());
+ }
+
+ @ExecFunction(name = "seconds_add")
+ public static Expression secondsAdd(DateV2Literal date, IntegerLiteral
second) {
+ return date.toBeginOfTheDay().plusSeconds(second.getValue());
+ }
+
@ExecFunction(name = "seconds_add")
public static Expression secondsAdd(DateTimeLiteral date, IntegerLiteral
second) {
return date.plusSeconds(second.getValue());
@@ -404,4 +434,14 @@ public class DateTimeArithmetic {
private static int dateDiff(LocalDateTime date1, LocalDateTime date2) {
return ((int) ChronoUnit.DAYS.between(date2.toLocalDate(),
date1.toLocalDate()));
}
+
+ @ExecFunction(name = "to_days")
+ public static Expression toDays(DateLiteral date) {
+ return new IntegerLiteral((int) date.getDay());
+ }
+
+ @ExecFunction(name = "to_days")
+ public static Expression toDays(DateV2Literal date) {
+ return new IntegerLiteral((int) date.getDay());
+ }
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
index bd26306c2a0..9009bc13e8a 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
@@ -39,7 +39,11 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.ConvertTz;
import org.apache.doris.nereids.trees.expressions.functions.scalar.DateFormat;
import org.apache.doris.nereids.trees.expressions.functions.scalar.DateTrunc;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.FromUnixtime;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.HoursAdd;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesAdd;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.StrToDate;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.ToDays;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.UnixTimestamp;
import org.apache.doris.nereids.trees.expressions.literal.BigIntLiteral;
import org.apache.doris.nereids.trees.expressions.literal.DateLiteral;
@@ -50,6 +54,7 @@ import
org.apache.doris.nereids.trees.expressions.literal.DecimalV3Literal;
import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
import org.apache.doris.nereids.trees.expressions.literal.Interval.TimeUnit;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
+import org.apache.doris.nereids.trees.expressions.literal.NullLiteral;
import org.apache.doris.nereids.trees.expressions.literal.StringLiteral;
import org.apache.doris.nereids.trees.expressions.literal.VarcharLiteral;
import org.apache.doris.nereids.trees.plans.RelationId;
@@ -180,6 +185,73 @@ class FoldConstantTest extends ExpressionRewriteTestHelper
{
Assertions.assertEquals(rewritten, expected);
}
+ @Test
+ void testFoldDate() {
+ executor = new ExpressionRuleExecutor(ImmutableList.of(
+ bottomUp(FoldConstantRuleOnFE.VISITOR_INSTANCE)
+ ));
+ HoursAdd hoursAdd = new
HoursAdd(DateLiteral.fromJavaDateType(LocalDateTime.of(1, 1, 1, 1, 1, 1)),
+ new IntegerLiteral(1));
+ Expression rewritten = executor.rewrite(hoursAdd, context);
+ Assertions.assertEquals(new DateTimeLiteral("0001-01-01 01:00:00"),
rewritten);
+ hoursAdd = new
HoursAdd(DateV2Literal.fromJavaDateType(LocalDateTime.of(1, 1, 1, 1, 1, 1)),
+ new IntegerLiteral(1));
+ rewritten = executor.rewrite(hoursAdd, context);
+ Assertions.assertEquals(new DateTimeV2Literal("0001-01-01 01:00:00"),
rewritten);
+ hoursAdd = new
HoursAdd(DateV2Literal.fromJavaDateType(LocalDateTime.of(9999, 12, 31, 23, 1,
1)),
+ new IntegerLiteral(24));
+ rewritten = executor.rewrite(hoursAdd, context);
+ Assertions.assertEquals(new NullLiteral(hoursAdd.getDataType()),
rewritten);
+ hoursAdd = new
HoursAdd(DateV2Literal.fromJavaDateType(LocalDateTime.of(0, 1, 1, 1, 1, 1)),
+ new IntegerLiteral(-25));
+ rewritten = executor.rewrite(hoursAdd, context);
+ Assertions.assertEquals(new NullLiteral(hoursAdd.getDataType()),
rewritten);
+
+ MinutesAdd minutesAdd = new
MinutesAdd(DateLiteral.fromJavaDateType(LocalDateTime.of(1, 1, 1, 1, 1, 1)),
+ new IntegerLiteral(1));
+ rewritten = executor.rewrite(minutesAdd, context);
+ Assertions.assertEquals(new DateTimeLiteral("0001-01-01 00:01:00"),
rewritten);
+ minutesAdd = new
MinutesAdd(DateV2Literal.fromJavaDateType(LocalDateTime.of(1, 1, 1, 1, 1, 1)),
+ new IntegerLiteral(1));
+ rewritten = executor.rewrite(minutesAdd, context);
+ Assertions.assertEquals(new DateTimeV2Literal("0001-01-01 00:01:00"),
rewritten);
+ minutesAdd = new
MinutesAdd(DateV2Literal.fromJavaDateType(LocalDateTime.of(9999, 12, 31, 23,
59, 1)),
+ new IntegerLiteral(1440));
+ rewritten = executor.rewrite(minutesAdd, context);
+ Assertions.assertEquals(new NullLiteral(minutesAdd.getDataType()),
rewritten);
+ minutesAdd = new
MinutesAdd(DateV2Literal.fromJavaDateType(LocalDateTime.of(0, 1, 1, 0, 1, 1)),
+ new IntegerLiteral(-2));
+ rewritten = executor.rewrite(minutesAdd, context);
+ Assertions.assertEquals(new NullLiteral(minutesAdd.getDataType()),
rewritten);
+
+ SecondsAdd secondsAdd = new
SecondsAdd(DateLiteral.fromJavaDateType(LocalDateTime.of(1, 1, 1, 1, 1, 1)),
+ new IntegerLiteral(1));
+ rewritten = executor.rewrite(secondsAdd, context);
+ Assertions.assertEquals(new DateTimeLiteral("0001-01-01 00:00:01"),
rewritten);
+ secondsAdd = new
SecondsAdd(DateV2Literal.fromJavaDateType(LocalDateTime.of(1, 1, 1, 1, 1, 1)),
+ new IntegerLiteral(1));
+ rewritten = executor.rewrite(secondsAdd, context);
+ Assertions.assertEquals(new DateTimeV2Literal("0001-01-01 00:00:01"),
rewritten);
+ secondsAdd = new
SecondsAdd(DateV2Literal.fromJavaDateType(LocalDateTime.of(9999, 12, 31, 23,
59, 59)),
+ new IntegerLiteral(86400));
+ rewritten = executor.rewrite(secondsAdd, context);
+ Assertions.assertEquals(new NullLiteral(secondsAdd.getDataType()),
rewritten);
+ secondsAdd = new
SecondsAdd(DateV2Literal.fromJavaDateType(LocalDateTime.of(0, 1, 1, 0, 1, 1)),
+ new IntegerLiteral(-61));
+ rewritten = executor.rewrite(secondsAdd, context);
+ Assertions.assertEquals(new NullLiteral(secondsAdd.getDataType()),
rewritten);
+
+ ToDays toDays = new
ToDays(DateLiteral.fromJavaDateType(LocalDateTime.of(1, 1, 1, 1, 1, 1)));
+ rewritten = executor.rewrite(toDays, context);
+ Assertions.assertEquals(new IntegerLiteral(366), rewritten);
+ toDays = new ToDays(DateV2Literal.fromJavaDateType(LocalDateTime.of(1,
1, 1, 1, 1, 1)));
+ rewritten = executor.rewrite(toDays, context);
+ Assertions.assertEquals(new IntegerLiteral(366), rewritten);
+ toDays = new
ToDays(DateV2Literal.fromJavaDateType(LocalDateTime.of(9999, 12, 31, 1, 1, 1)));
+ rewritten = executor.rewrite(toDays, context);
+ Assertions.assertEquals(new IntegerLiteral(3652424), rewritten);
+ }
+
@Test
void testFoldString() {
executor = new ExpressionRuleExecutor(ImmutableList.of(
diff --git
a/regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_by_fe.groovy
b/regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_by_fe.groovy
index cc63662657c..23deb274501 100644
---
a/regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_by_fe.groovy
+++
b/regression-test/suites/nereids_p0/expression/fold_constant/fold_constant_by_fe.groovy
@@ -163,4 +163,6 @@ suite("test_fold_constant_by_fe") {
res = sql """explain select "12" like '%123%'"""
assertTrue(res.contains("like"))
+ testFoldConst("select DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) + INTERVAL
3600 SECOND")
+
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
