This is an automated email from the ASF dual-hosted git repository.
morrysnow 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 087076c27a7 [Fix](Planner) add date/month/year add function fe
constant fold with dateV2/dateTimeV2 (#34483)
087076c27a7 is described below
commit 087076c27a7a05f144fd15d028588ee0fb6d511f
Author: LiBinfeng <[email protected]>
AuthorDate: Fri May 10 10:57:49 2024 +0800
[Fix](Planner) add date/month/year add function fe constant fold with
dateV2/dateTimeV2 (#34483)
Problem and Cause:
In original planner, date_add function would choose different data type of
datetime or datev2. Which when original planner choose datev2 as constant date
type. Fe could not folding date_add function because missing of function
signature of folding constant date_add(datev2, int)
Solved:
Add corresponding function signatures of date_add/months_add/years_add in
original planner
---
.../java/org/apache/doris/rewrite/FEFunctions.java | 56 ++++++++++++++++++++++
.../datetime_functions/test_date_function.groovy | 17 +++++++
2 files changed, 73 insertions(+)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java
b/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java
index 0e335fc841a..f5d03d68b26 100755
--- a/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java
@@ -104,29 +104,85 @@ public class FEFunctions {
return daysAdd(date, day);
}
+ @FEFunction(name = "date_add", argTypes = { "DATEV2", "INT" }, returnType
= "DATEV2")
+ public static DateLiteral dateAddDateV2(LiteralExpr date, LiteralExpr day)
throws AnalysisException {
+ return daysAdd(date, day);
+ }
+
+ @FEFunction(name = "date_add", argTypes = { "DATETIMEV2", "INT" },
returnType = "DATETIMEV2")
+ public static DateLiteral dateAddDateTimeV2(LiteralExpr date, LiteralExpr
day) throws AnalysisException {
+ return daysAdd(date, day);
+ }
+
@FEFunction(name = "adddate", argTypes = { "DATETIME", "INT" }, returnType
= "DATETIME")
public static DateLiteral addDate(LiteralExpr date, LiteralExpr day)
throws AnalysisException {
return daysAdd(date, day);
}
+ @FEFunction(name = "adddate", argTypes = { "DATEV2", "INT" }, returnType =
"DATEV2")
+ public static DateLiteral addDateDateV2(LiteralExpr date, LiteralExpr day)
throws AnalysisException {
+ return daysAdd(date, day);
+ }
+
+ @FEFunction(name = "adddate", argTypes = { "DATETIMEV2", "INT" },
returnType = "DATETIMEV2")
+ public static DateLiteral addDateDateTimeV2(LiteralExpr date, LiteralExpr
day) throws AnalysisException {
+ return daysAdd(date, day);
+ }
+
@FEFunction(name = "years_add", argTypes = { "DATETIME", "INT" },
returnType = "DATETIME")
public static DateLiteral yearsAdd(LiteralExpr date, LiteralExpr year)
throws AnalysisException {
DateLiteral dateLiteral = (DateLiteral) date;
return dateLiteral.plusYears(year.getLongValue());
}
+ @FEFunction(name = "years_add", argTypes = { "DATEV2", "INT" }, returnType
= "DATEV2")
+ public static DateLiteral yearsAddDateV2(LiteralExpr date, LiteralExpr
year) throws AnalysisException {
+ DateLiteral dateLiteral = (DateLiteral) date;
+ return dateLiteral.plusYears((int) year.getLongValue());
+ }
+
+ @FEFunction(name = "years_add", argTypes = { "DATETIMEV2", "INT" },
returnType = "DATETIMEV2")
+ public static DateLiteral yearsAddDateTimeV2(LiteralExpr date, LiteralExpr
year) throws AnalysisException {
+ DateLiteral dateLiteral = (DateLiteral) date;
+ return dateLiteral.plusYears((int) year.getLongValue());
+ }
+
@FEFunction(name = "months_add", argTypes = { "DATETIME", "INT" },
returnType = "DATETIME")
public static DateLiteral monthsAdd(LiteralExpr date, LiteralExpr month)
throws AnalysisException {
DateLiteral dateLiteral = (DateLiteral) date;
return dateLiteral.plusMonths(month.getLongValue());
}
+ @FEFunction(name = "months_add", argTypes = { "DATEV2", "INT" },
returnType = "DATEV2")
+ public static DateLiteral monthsAddDateV2(LiteralExpr date, LiteralExpr
month) throws AnalysisException {
+ DateLiteral dateLiteral = (DateLiteral) date;
+ return dateLiteral.plusMonths((int) month.getLongValue());
+ }
+
+ @FEFunction(name = "months_add", argTypes = { "DATETIMEV2", "INT" },
returnType = "DATETIMEV2")
+ public static DateLiteral monthsAddDateTimeV2(LiteralExpr date,
LiteralExpr month) throws AnalysisException {
+ DateLiteral dateLiteral = (DateLiteral) date;
+ return dateLiteral.plusMonths((int) month.getLongValue());
+ }
+
@FEFunction(name = "days_add", argTypes = { "DATETIME", "INT" },
returnType = "DATETIME")
public static DateLiteral daysAdd(LiteralExpr date, LiteralExpr day)
throws AnalysisException {
DateLiteral dateLiteral = (DateLiteral) date;
return dateLiteral.plusDays(day.getLongValue());
}
+ @FEFunction(name = "days_add", argTypes = { "DATEV2", "INT" }, returnType
= "DATEV2")
+ public static DateLiteral daysAddDateV2(LiteralExpr date, LiteralExpr day)
throws AnalysisException {
+ DateLiteral dateLiteral = (DateLiteral) date;
+ return dateLiteral.plusDays((int) day.getLongValue());
+ }
+
+ @FEFunction(name = "days_add", argTypes = { "DATETIMEV2", "INT" },
returnType = "DATETIMEV2")
+ public static DateLiteral daysAddDateTimeV2(LiteralExpr date, LiteralExpr
day) throws AnalysisException {
+ DateLiteral dateLiteral = (DateLiteral) date;
+ return dateLiteral.plusDays((int) day.getLongValue());
+ }
+
@FEFunction(name = "hours_add", argTypes = { "DATETIME", "INT" },
returnType = "DATETIME")
public static DateLiteral hoursAdd(LiteralExpr date, LiteralExpr hour)
throws AnalysisException {
DateLiteral dateLiteral = (DateLiteral) date;
diff --git
a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
index 74163e2c531..04de3abef06 100644
---
a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
+++
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
@@ -222,6 +222,23 @@ suite("test_date_function") {
qt_sql """ select date_add(test_datetime, INTERVAL 2 MINUTE) result from
${tableName}; """
qt_sql """ select date_add(test_datetime, INTERVAL 2 SECOND) result from
${tableName}; """
+ explain {
+ sql """select * from ${tableName} where test_datetime >=
date_add('2024-01-16',INTERVAL 1 day);"""
+ contains "2024-01-17"
+ }
+ explain {
+ sql """select * from ${tableName} where test_datetime >=
adddate('2024-01-16',INTERVAL 1 day);"""
+ contains "2024-01-17"
+ }
+ explain {
+ sql """select * from ${tableName} where test_datetime >=
months_add('2024-01-16',1);"""
+ contains "2024-02-16"
+ }
+ explain {
+ sql """select * from ${tableName} where test_datetime >=
years_add('2024-01-16',1);"""
+ contains "2025-01-16"
+ }
+
// DATE_FORMAT
sql """ truncate table ${tableName} """
sql """ insert into ${tableName} values ("2009-10-04 22:23:00") """
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
