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 d5e967c357b [Feature](function) Support function quarters_add/sub for
nereids (#45370)
d5e967c357b is described below
commit d5e967c357bec6a8ee5f6e8a67e865eab220cc80
Author: zclllyybb <zhaochan...@selectdb.com>
AuthorDate: Thu Dec 26 16:31:57 2024 +0800
[Feature](function) Support function quarters_add/sub for nereids (#45370)
### What problem does this PR solve?
Issue Number: close #xxx
Related PR: #xxx
Problem Summary:
1. BE already has its implementation. now add nereids function
signatures.
```sql
mysql> select quarters_add("2020-12-12", 1);
+-----------------------------------------------+
| quarters_add(cast('2020-12-12' as DATEV2), 1) |
+-----------------------------------------------+
| 2021-03-12 |
+-----------------------------------------------+
1 row in set (0.10 sec)
mysql> select date_sub("2020-12-12", interval 10 quarter);
+------------------------------------------------+
| quarters_sub(cast('2020-12-12' as DATEV2), 10) |
+------------------------------------------------+
| 2018-06-12 |
+------------------------------------------------+
1 row in set (0.11 sec)
```
2. for date operations' template implementations in BE, we choose
years_add to cover its standard testcases to test the base template.
---
.../function_date_or_datetime_computation.h | 6 +-
.../antlr4/org/apache/doris/nereids/DorisLexer.g4 | 1 +
.../antlr4/org/apache/doris/nereids/DorisParser.g4 | 9 +-
.../doris/catalog/BuiltinScalarFunctions.java | 4 +
.../doris/nereids/parser/LogicalPlanBuilder.java | 10 +-
.../functions/executable/DateTimeArithmetic.java | 49 ++-
.../expressions/functions/scalar/QuartersAdd.java | 80 +++++
.../expressions/functions/scalar/QuartersSub.java | 80 +++++
.../expressions/visitor/ScalarFunctionVisitor.java | 10 +
.../java/org/apache/doris/rewrite/FEFunctions.java | 44 +++
.../datetime_functions/test_quarters_add.out | 378 +++++++++++++++++++++
.../data/query_p0/sql_functions/test_years_add.out | 163 +++++++++
.../datetime_functions/test_quarters_add.groovy | 219 ++++++++++++
.../query_p0/sql_functions/test_years_add.groovy | 185 ++++++++++
14 files changed, 1228 insertions(+), 10 deletions(-)
diff --git a/be/src/vec/functions/function_date_or_datetime_computation.h
b/be/src/vec/functions/function_date_or_datetime_computation.h
index 8165f57881b..df7dc20a312 100644
--- a/be/src/vec/functions/function_date_or_datetime_computation.h
+++ b/be/src/vec/functions/function_date_or_datetime_computation.h
@@ -142,7 +142,9 @@ struct AddQuartersImpl {
return date_time_add<TimeUnit::MONTH, ArgType, ReturnType>(t, 3 *
delta, is_null);
}
- static DataTypes get_variadic_argument_types() { return
{std::make_shared<ArgType>()}; }
+ static DataTypes get_variadic_argument_types() {
+ return {std::make_shared<ArgType>(),
std::make_shared<DataTypeInt32>()};
+ }
};
template <typename Transform, typename DateType>
@@ -156,7 +158,7 @@ struct SubtractIntervalImpl {
}
static DataTypes get_variadic_argument_types() {
- return {std::make_shared<DateType>(),
std::make_shared<DataTypeInt32>()};
+ return Transform::get_variadic_argument_types();
}
};
diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
index 8ce8d033108..1a684691b77 100644
--- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
+++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
@@ -430,6 +430,7 @@ QUANTILE_UNION: 'QUANTILE_UNION';
QUERY: 'QUERY';
QUOTA: 'QUOTA';
QUALIFY: 'QUALIFY';
+QUARTER: 'QUARTER';
RANDOM: 'RANDOM';
RANGE: 'RANGE';
READ: 'READ';
diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
index f5730bddd54..233e085577b 100644
--- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
+++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
@@ -1508,9 +1508,7 @@ valueExpression
;
datetimeUnit
- : YEAR | MONTH
- | WEEK | DAY
- | HOUR | MINUTE | SECOND
+ :YEAR | MONTH | QUARTER | WEEK | DAY | HOUR | MINUTE | SECOND
;
primaryExpression
@@ -1704,7 +1702,7 @@ interval
;
unitIdentifier
- : YEAR | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND
+ : YEAR | QUARTER | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND
;
dataTypeWithNullable
@@ -2057,7 +2055,8 @@ nonReserved
| PROPERTIES
| PROPERTY
| QUANTILE_STATE
- | QUANTILE_UNION
+ | QUANTILE_UNION
+ | QUARTER
| QUERY
| QUOTA
| QUALIFY
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
index ed3f2895cc8..b173383ff0c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
@@ -345,6 +345,8 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.Protocol;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.QuantilePercent;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.QuantileStateEmpty;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Quarter;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.QuartersAdd;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.QuartersSub;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Quote;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Radians;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Random;
@@ -831,6 +833,8 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(QuantilePercent.class, "quantile_percent"),
scalar(QuantileStateEmpty.class, "quantile_state_empty"),
scalar(Quarter.class, "quarter"),
+ scalar(QuartersAdd.class, "quarters_add"),
+ scalar(QuartersSub.class, "quarters_sub"),
scalar(Radians.class, "radians"),
scalar(Random.class, "rand", "random"),
scalar(Regexp.class, "regexp"),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
index 08a5078214d..d7e5186b9b1 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
@@ -448,6 +448,8 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.MonthsAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MonthsDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MonthsSub;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Now;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.QuartersAdd;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.QuartersSub;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondCeil;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondFloor;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsAdd;
@@ -2367,6 +2369,7 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
Expression start = (Expression) visit(ctx.startTimestamp);
Expression end = (Expression) visit(ctx.endTimestamp);
String unit = ctx.unit.getText();
+ // TODO: support quarters_diff
if ("YEAR".equalsIgnoreCase(unit)) {
return new YearsDiff(end, start);
} else if ("MONTH".equalsIgnoreCase(unit)) {
@@ -2394,6 +2397,8 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
String unit = ctx.unit.getText();
if ("YEAR".equalsIgnoreCase(unit)) {
return new YearsAdd(end, start);
+ } else if ("QUARTER".equalsIgnoreCase(unit)) {
+ return new QuartersAdd(end, start);
} else if ("MONTH".equalsIgnoreCase(unit)) {
return new MonthsAdd(end, start);
} else if ("WEEK".equalsIgnoreCase(unit)) {
@@ -2409,7 +2414,6 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
}
throw new ParseException("Unsupported time stamp add time unit: " +
unit
+ ", supported time unit:
YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND", ctx);
-
}
@Override
@@ -2423,6 +2427,8 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
if ("Year".equalsIgnoreCase(ctx.unit.getText())) {
return new YearsAdd(timeStamp, amount);
+ } else if ("QUARTER".equalsIgnoreCase(ctx.unit.getText())) {
+ return new QuartersAdd(timeStamp, amount);
} else if ("MONTH".equalsIgnoreCase(ctx.unit.getText())) {
return new MonthsAdd(timeStamp, amount);
} else if ("WEEK".equalsIgnoreCase(ctx.unit.getText())) {
@@ -2485,6 +2491,8 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
if ("Year".equalsIgnoreCase(ctx.unit.getText())) {
return new YearsSub(timeStamp, amount);
+ } else if ("QUARTER".equalsIgnoreCase(ctx.unit.getText())) {
+ return new QuartersSub(timeStamp, amount);
} else if ("MONTH".equalsIgnoreCase(ctx.unit.getText())) {
return new MonthsSub(timeStamp, amount);
} else if ("WEEK".equalsIgnoreCase(ctx.unit.getText())) {
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 84e5ebf272a..5547a1f396e 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
@@ -29,8 +29,7 @@ import java.time.LocalDateTime;
import java.time.temporal.ChronoUnit;
/**
- * executable function:
- * date_add/sub, years/months/week/days/hours/minutes/seconds_add/sub, datediff
+ * executable function: date_add/sub,
years/quarters/months/week/days/hours/minutes/seconds_add/sub, datediff
*/
public class DateTimeArithmetic {
/**
@@ -102,6 +101,29 @@ public class DateTimeArithmetic {
return date.plusYears(year.getValue());
}
+ /**
+ * datetime arithmetic function quarters-add.
+ */
+ @ExecFunction(name = "quarters_add")
+ public static Expression quartersAdd(DateLiteral date, IntegerLiteral
quarter) {
+ return date.plusMonths(3 * quarter.getValue());
+ }
+
+ @ExecFunction(name = "quarters_add")
+ public static Expression quartersAdd(DateTimeLiteral date, IntegerLiteral
quarter) {
+ return date.plusMonths(3 * quarter.getValue());
+ }
+
+ @ExecFunction(name = "quarters_add")
+ public static Expression quartersAdd(DateV2Literal date, IntegerLiteral
quarter) {
+ return date.plusMonths(3 * quarter.getValue());
+ }
+
+ @ExecFunction(name = "quarters_add")
+ public static Expression quartersAdd(DateTimeV2Literal date,
IntegerLiteral quarter) {
+ return date.plusMonths(3 * quarter.getValue());
+ }
+
/**
* datetime arithmetic function months-add.
*/
@@ -295,6 +317,29 @@ public class DateTimeArithmetic {
return yearsAdd(date, new IntegerLiteral(-year.getValue()));
}
+ /**
+ * datetime arithmetic function quarters-sub.
+ */
+ @ExecFunction(name = "quarters_sub")
+ public static Expression quartersSub(DateLiteral date, IntegerLiteral
quarter) {
+ return quartersAdd(date, new IntegerLiteral(-quarter.getValue()));
+ }
+
+ @ExecFunction(name = "quarters_sub")
+ public static Expression quartersSub(DateTimeLiteral date, IntegerLiteral
quarter) {
+ return quartersAdd(date, new IntegerLiteral(-quarter.getValue()));
+ }
+
+ @ExecFunction(name = "quarters_sub")
+ public static Expression quartersSub(DateV2Literal date, IntegerLiteral
quarter) {
+ return quartersAdd(date, new IntegerLiteral(-quarter.getValue()));
+ }
+
+ @ExecFunction(name = "quarters_sub")
+ public static Expression quartersSub(DateTimeV2Literal date,
IntegerLiteral quarter) {
+ return quartersAdd(date, new IntegerLiteral(-quarter.getValue()));
+ }
+
/**
* datetime arithmetic function months-sub
*/
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/QuartersAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/QuartersAdd.java
new file mode 100644
index 00000000000..1f24317f351
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/QuartersAdd.java
@@ -0,0 +1,80 @@
+// 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.scalar;
+
+import org.apache.doris.catalog.FunctionSignature;
+import org.apache.doris.common.Config;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import
org.apache.doris.nereids.trees.expressions.functions.ComputeSignatureForDateArithmetic;
+import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
+import
org.apache.doris.nereids.trees.expressions.functions.PropagateNullableOnDateLikeV2Args;
+import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.types.DateTimeType;
+import org.apache.doris.nereids.types.DateTimeV2Type;
+import org.apache.doris.nereids.types.DateType;
+import org.apache.doris.nereids.types.DateV2Type;
+import org.apache.doris.nereids.types.IntegerType;
+
+import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * ScalarFunction 'quarters_add'.
+ */
+public class QuartersAdd extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
+ ComputeSignatureForDateArithmetic, PropagateNullableOnDateLikeV2Args {
+
+ // When enable_date_conversion is true, we prefer to V2 signature.
+ // This preference follows original planner. refer to
ScalarType.getDefaultDateType()
+ private static final List<FunctionSignature> SIGNATURES =
Config.enable_date_conversion
+ ? ImmutableList.of(
+
FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT).args(DateTimeV2Type.SYSTEM_DEFAULT,
+ IntegerType.INSTANCE),
+
FunctionSignature.ret(DateV2Type.INSTANCE).args(DateV2Type.INSTANCE,
IntegerType.INSTANCE),
+
FunctionSignature.ret(DateTimeType.INSTANCE).args(DateTimeType.INSTANCE,
IntegerType.INSTANCE),
+
FunctionSignature.ret(DateType.INSTANCE).args(DateType.INSTANCE,
IntegerType.INSTANCE))
+ : ImmutableList.of(
+
FunctionSignature.ret(DateTimeType.INSTANCE).args(DateTimeType.INSTANCE,
IntegerType.INSTANCE),
+
FunctionSignature.ret(DateType.INSTANCE).args(DateType.INSTANCE,
IntegerType.INSTANCE),
+
FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT).args(DateTimeV2Type.SYSTEM_DEFAULT,
+ IntegerType.INSTANCE),
+
FunctionSignature.ret(DateV2Type.INSTANCE).args(DateV2Type.INSTANCE,
IntegerType.INSTANCE));
+
+ public QuartersAdd(Expression arg0, Expression arg1) {
+ super("quarters_add", arg0, arg1);
+ }
+
+ @Override
+ public QuartersAdd withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 2);
+ return new QuartersAdd(children.get(0), children.get(1));
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitQuartersAdd(this, context);
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/QuartersSub.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/QuartersSub.java
new file mode 100644
index 00000000000..266184e9676
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/QuartersSub.java
@@ -0,0 +1,80 @@
+// 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.scalar;
+
+import org.apache.doris.catalog.FunctionSignature;
+import org.apache.doris.common.Config;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import
org.apache.doris.nereids.trees.expressions.functions.ComputeSignatureForDateArithmetic;
+import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
+import
org.apache.doris.nereids.trees.expressions.functions.PropagateNullableOnDateLikeV2Args;
+import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.types.DateTimeType;
+import org.apache.doris.nereids.types.DateTimeV2Type;
+import org.apache.doris.nereids.types.DateType;
+import org.apache.doris.nereids.types.DateV2Type;
+import org.apache.doris.nereids.types.IntegerType;
+
+import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * ScalarFunction 'quarters_sub'.
+ */
+public class QuartersSub extends ScalarFunction implements BinaryExpression,
ExplicitlyCastableSignature,
+ ComputeSignatureForDateArithmetic, PropagateNullableOnDateLikeV2Args {
+
+ // When enable_date_conversion is true, we prefer to V2 signature.
+ // This preference follows original planner. refer to
ScalarType.getDefaultDateType()
+ private static final List<FunctionSignature> SIGNATURES =
Config.enable_date_conversion
+ ? ImmutableList.of(
+
FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT).args(DateTimeV2Type.SYSTEM_DEFAULT,
+ IntegerType.INSTANCE),
+
FunctionSignature.ret(DateV2Type.INSTANCE).args(DateV2Type.INSTANCE,
IntegerType.INSTANCE),
+
FunctionSignature.ret(DateTimeType.INSTANCE).args(DateTimeType.INSTANCE,
IntegerType.INSTANCE),
+
FunctionSignature.ret(DateType.INSTANCE).args(DateType.INSTANCE,
IntegerType.INSTANCE))
+ : ImmutableList.of(
+
FunctionSignature.ret(DateTimeType.INSTANCE).args(DateTimeType.INSTANCE,
IntegerType.INSTANCE),
+
FunctionSignature.ret(DateType.INSTANCE).args(DateType.INSTANCE,
IntegerType.INSTANCE),
+
FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT).args(DateTimeV2Type.SYSTEM_DEFAULT,
+ IntegerType.INSTANCE),
+
FunctionSignature.ret(DateV2Type.INSTANCE).args(DateV2Type.INSTANCE,
IntegerType.INSTANCE));
+
+ public QuartersSub(Expression arg0, Expression arg1) {
+ super("quarters_sub", arg0, arg1);
+ }
+
+ @Override
+ public QuartersSub withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 2);
+ return new QuartersSub(children.get(0), children.get(1));
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitQuartersSub(this, context);
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
index 2619731cfc8..1a41ba4f23e 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
@@ -344,6 +344,8 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.Protocol;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.QuantilePercent;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.QuantileStateEmpty;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Quarter;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.QuartersAdd;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.QuartersSub;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Quote;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Radians;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Random;
@@ -1763,6 +1765,14 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(quarter, context);
}
+ default R visitQuartersAdd(QuartersAdd quartersAdd, C context) {
+ return visitScalarFunction(quartersAdd, context);
+ }
+
+ default R visitQuartersSub(QuartersSub quartersSub, C context) {
+ return visitScalarFunction(quartersSub, context);
+ }
+
default R visitRadians(Radians radians, C context) {
return visitScalarFunction(radians, context);
}
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 520cdeefff0..85c433ef62e 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
@@ -158,6 +158,30 @@ public class FEFunctions {
return dateLiteral.plusYears((int) year.getLongValue());
}
+ @FEFunction(name = "quarters_add", argTypes = { "DATETIME", "INT" },
returnType = "DATETIME")
+ public static DateLiteral quartersAdd(LiteralExpr date, LiteralExpr
quarter) throws AnalysisException {
+ DateLiteral dateLiteral = (DateLiteral) date;
+ return dateLiteral.plusMonths(3 * (int) quarter.getLongValue());
+ }
+
+ @FEFunction(name = "quarters_add", argTypes = { "DATE", "INT" },
returnType = "DATE")
+ public static DateLiteral quartersAddDate(LiteralExpr date, LiteralExpr
quarter) throws AnalysisException {
+ DateLiteral dateLiteral = (DateLiteral) date;
+ return dateLiteral.plusMonths(3 * (int) quarter.getLongValue());
+ }
+
+ @FEFunction(name = "quarters_add", argTypes = { "DATEV2", "INT" },
returnType = "DATEV2")
+ public static DateLiteral quartersAddDateV2(LiteralExpr date, LiteralExpr
quarter) throws AnalysisException {
+ DateLiteral dateLiteral = (DateLiteral) date;
+ return dateLiteral.plusMonths(3 * (int) quarter.getLongValue());
+ }
+
+ @FEFunction(name = "quarters_add", argTypes = { "DATETIMEV2", "INT" },
returnType = "DATETIMEV2")
+ public static DateLiteral quartersAddDateTimeV2(LiteralExpr date,
LiteralExpr quarter) throws AnalysisException {
+ DateLiteral dateLiteral = (DateLiteral) date;
+ return dateLiteral.plusMonths(3 * (int) quarter.getLongValue());
+ }
+
@FEFunction(name = "months_add", argTypes = { "DATETIME", "INT" },
returnType = "DATETIME")
public static DateLiteral monthsAdd(LiteralExpr date, LiteralExpr month)
throws AnalysisException {
DateLiteral dateLiteral = (DateLiteral) date;
@@ -282,6 +306,26 @@ public class FEFunctions {
return yearsAdd(date, new IntLiteral(-(int) year.getLongValue()));
}
+ @FEFunction(name = "quarters_sub", argTypes = { "DATETIME", "INT" },
returnType = "DATETIME")
+ public static DateLiteral quartersSub(LiteralExpr date, LiteralExpr
quarter) throws AnalysisException {
+ return quartersAdd(date, new IntLiteral(-(int)
quarter.getLongValue()));
+ }
+
+ @FEFunction(name = "quarters_sub", argTypes = { "DATE", "INT" },
returnType = "DATE")
+ public static DateLiteral quartersSubDate(LiteralExpr date, LiteralExpr
quarter) throws AnalysisException {
+ return quartersAdd(date, new IntLiteral(-(int)
quarter.getLongValue()));
+ }
+
+ @FEFunction(name = "quarters_sub", argTypes = { "DATEV2", "INT" },
returnType = "DATEV2")
+ public static DateLiteral quartersSubDateV2(LiteralExpr date, LiteralExpr
quarter) throws AnalysisException {
+ return quartersAdd(date, new IntLiteral(-(int)
quarter.getLongValue()));
+ }
+
+ @FEFunction(name = "quarters_sub", argTypes = { "DATETIMEV2", "INT" },
returnType = "DATETIMEV2")
+ public static DateLiteral quartersSubDateTimeV2(LiteralExpr date,
LiteralExpr quarter) throws AnalysisException {
+ return quartersAdd(date, new IntLiteral(-(int)
quarter.getLongValue()));
+ }
+
@FEFunction(name = "months_sub", argTypes = { "DATETIME", "INT" },
returnType = "DATETIME")
public static DateLiteral monthsSub(LiteralExpr date, LiteralExpr month)
throws AnalysisException {
return monthsAdd(date, new IntLiteral(-(int) month.getLongValue()));
diff --git
a/regression-test/data/query_p0/sql_functions/datetime_functions/test_quarters_add.out
b/regression-test/data/query_p0/sql_functions/datetime_functions/test_quarters_add.out
new file mode 100644
index 00000000000..5abfd09a0ff
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/datetime_functions/test_quarters_add.out
@@ -0,0 +1,378 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !empty_nullable --
+
+-- !empty_not_nullable --
+
+-- !empty_partial_nullable --
+
+-- !all_null --
+\N
+\N
+\N
+
+-- !nullable_d --
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+0800-12-01
+0875-12-01
+0900-09-01
+0901-03-01
+0901-12-01
+0925-12-01
+0931-09-01
+1800-01-01
+1800-02-28
+1875-01-01
+1875-02-28
+1899-10-01
+1899-11-28
+1900-02-28
+1900-02-28
+1900-04-01
+1900-05-28
+1901-01-01
+1901-02-28
+1920-12-12
+1925-01-01
+1925-02-28
+1930-10-01
+1930-11-28
+1975-02-28
+1975-02-28
+1995-12-12
+1999-11-28
+1999-11-29
+2000-05-28
+2000-05-29
+2001-02-28
+2001-02-28
+2020-09-12
+2021-03-12
+2021-12-12
+2025-02-28
+2025-02-28
+2030-11-28
+2030-11-29
+2045-12-12
+2051-09-12
+3900-12-31
+3975-12-31
+4000-09-30
+4001-03-31
+4001-12-31
+4025-12-31
+4031-09-30
+
+-- !partial_nullable_dt --
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+0800-12-01T12:12:12.123456
+0875-12-01T12:12:12.123456
+0900-09-01T12:12:12.123456
+0901-03-01T12:12:12.123456
+0901-06-01T12:12:12.123456
+0901-12-01T12:12:12.123456
+0925-12-01T12:12:12.123456
+0931-09-01T12:12:12.123456
+1800-01-01T00:00:00.000001
+1800-02-28T00:00:00.000001
+1875-01-01T00:00:00.000001
+1875-02-28T00:00:00.000001
+1899-10-01T00:00:00.000001
+1899-11-28T00:00:00.000001
+1900-02-28T23:59:59.999
+1900-02-28T23:59:59.999
+1900-04-01T00:00:00.000001
+1900-05-28T00:00:00.000001
+1900-07-01T00:00:00.000001
+1900-08-28T00:00:00.000001
+1901-01-01T00:00:00.000001
+1901-02-28T00:00:00.000001
+1920-12-12T12:12:12.123456
+1925-01-01T00:00:00.000001
+1925-02-28T00:00:00.000001
+1930-10-01T00:00:00.000001
+1930-11-28T00:00:00.000001
+1975-02-28T23:59:59.999
+1975-02-28T23:59:59.999
+1995-12-12T12:12:12.123456
+1999-11-28T23:59:59.999
+1999-11-29T23:59:59.999
+2000-05-28T23:59:59.999
+2000-05-29T23:59:59.999
+2000-08-28T23:59:59.999
+2000-08-29T23:59:59.999
+2001-02-28T23:59:59.999
+2001-02-28T23:59:59.999
+2020-09-12T12:12:12.123456
+2021-03-12T12:12:12.123456
+2021-06-12T12:12:12.123456
+2021-12-12T12:12:12.123456
+2025-02-28T23:59:59.999
+2025-02-28T23:59:59.999
+2030-11-28T23:59:59.999
+2030-11-29T23:59:59.999
+2045-12-12T12:12:12.123456
+2051-09-12T12:12:12.123456
+3900-12-31T12:12:12.123456
+3975-12-31T12:12:12.123456
+4000-09-30T12:12:12.123456
+4001-03-31T12:12:12.123456
+4001-06-30T12:12:12.123456
+4001-12-31T12:12:12.123456
+4025-12-31T12:12:12.123456
+4031-09-30T12:12:12.123456
+
+-- !not_null_dt --
+0800-12-01
+0875-12-01
+0900-09-01
+0901-03-01
+0901-06-01
+0901-12-01
+0925-12-01
+0931-09-01
+1800-01-01
+1800-02-28
+1875-01-01
+1875-02-28
+1899-10-01
+1899-11-28
+1900-02-28
+1900-02-28
+1900-04-01
+1900-05-28
+1900-07-01
+1900-08-28
+1901-01-01
+1901-02-28
+1920-12-12
+1925-01-01
+1925-02-28
+1930-10-01
+1930-11-28
+1975-02-28
+1975-02-28
+1995-12-12
+1999-11-28
+1999-11-29
+2000-02-28
+2000-05-28
+2000-05-29
+2000-08-28
+2000-08-29
+2001-02-28
+2001-02-28
+2020-09-12
+2021-03-12
+2021-06-12
+2021-12-12
+2025-02-28
+2025-02-28
+2030-11-28
+2030-11-29
+2045-12-12
+2051-09-12
+2075-02-28
+2099-11-28
+2100-05-28
+2100-08-28
+2101-02-28
+2125-02-28
+2130-11-28
+3900-12-31
+3975-12-31
+4000-09-30
+4001-03-31
+4001-06-30
+4001-12-31
+4025-12-31
+4031-09-30
+
+-- !partial_nullable_d --
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+0800-12-01T12:12:12.123
+0875-12-01T12:12:12.123
+0900-09-01T12:12:12.123
+0901-03-01T12:12:12.123
+0901-12-01T12:12:12.123
+0925-12-01T12:12:12.123
+0931-09-01T12:12:12.123
+1800-01-01T00:00
+1800-02-28T00:00
+1875-01-01T00:00
+1875-02-28T00:00
+1899-10-01T00:00
+1899-11-28T00:00
+1900-02-28T23:59:59.999
+1900-02-28T23:59:59.999
+1900-04-01T00:00
+1900-05-28T00:00
+1901-01-01T00:00
+1901-02-28T00:00
+1920-12-12T12:12:12.123
+1920-12-12T12:12:12.123
+1925-01-01T00:00
+1925-02-28T00:00
+1930-10-01T00:00
+1930-11-28T00:00
+1975-02-28T23:59:59.999
+1975-02-28T23:59:59.999
+1995-12-12T12:12:12.123
+1995-12-12T12:12:12.123
+1999-11-28T23:59:59.999
+1999-11-29T23:59:59.999
+2000-05-28T23:59:59.999
+2000-05-29T23:59:59.999
+2001-02-28T23:59:59.999
+2001-02-28T23:59:59.999
+2020-09-12T12:12:12.123
+2020-09-12T12:12:12.123
+2021-03-12T12:12:12.123
+2021-03-12T12:12:12.123
+2021-12-12T12:12:12.123
+2021-12-12T12:12:12.123
+2025-02-28T23:59:59.999
+2025-02-28T23:59:59.999
+2030-11-28T23:59:59.999
+2030-11-29T23:59:59.999
+2045-12-12T12:12:12.123
+2045-12-12T12:12:12.123
+2051-09-12T12:12:12.123
+2051-09-12T12:12:12.123
+3900-12-31T12:12:12.123
+3975-12-31T12:12:12.123
+4000-09-30T12:12:12.123
+4001-03-31T12:12:12.123
+4001-12-31T12:12:12.123
+4025-12-31T12:12:12.123
+4031-09-30T12:12:12.123
+
+-- !not_nullable --
+0875-12-01
+1900-02-28
+1901-02-28
+1925-01-01
+2021-03-12
+2030-11-29
+2100-08-28
+4000-09-30
+
+-- !partial_nullable --
+\N
+0875-12-01T12:12:12.123456
+1900-02-28T23:59:59.999
+1901-02-28T00:00:00.000001
+1925-01-01T00:00:00.000001
+2021-03-12T12:12:12.123456
+2030-11-29T23:59:59.999
+4000-09-30T12:12:12.123456
+
+-- !nullable_no_null --
+0875-12-01T12:12:12.123
+1900-02-28T23:59:59.999
+1901-02-28T00:00
+1925-01-01T00:00
+2021-03-12T12:12:12.123
+2021-06-12T12:12:12.123
+2030-11-29T23:59:59.999
+4000-09-30T12:12:12.123
+
+-- !const_nullable --
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+
+-- !partial_const_nullable --
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+
+-- !const_not_nullable --
+1825-12-31
+1825-12-31
+1825-12-31
+1825-12-31
+1825-12-31
+1825-12-31
+1825-12-31
+1825-12-31
+
+-- !const_other_nullable --
+\N
+8900-12-31
+8975-12-31
+9000-09-30
+9001-03-31
+9001-12-31
+9025-12-31
+9031-09-30
+
+-- !const_other_not_nullable --
+0903-06-01T12:12:12.123
+1902-07-01T00:00
+1902-08-28T00:00
+2002-08-28T23:59:59.999
+2002-08-29T23:59:59.999
+2023-06-12T12:12:12.123
+2023-06-12T12:12:12.123
+4003-06-30T12:12:12.123
+
+-- !const_nullable_no_null --
+1990-10-10T00:00
+
+-- !const_nullable_no_null_multirows --
+9974-01-01T00:00
+9974-01-01T00:00
+9974-01-01T00:00
+9974-01-01T00:00
+9974-01-01T00:00
+9974-01-01T00:00
+9974-01-01T00:00
+9974-01-01T00:00
+
+-- !const_partial_nullable_no_null --
+1209-01-01
+
+-- !datediff1 --
+2020-09-12
+
+-- !datediff2 --
+2021-03-12
+
diff --git a/regression-test/data/query_p0/sql_functions/test_years_add.out
b/regression-test/data/query_p0/sql_functions/test_years_add.out
new file mode 100644
index 00000000000..a12a4598656
--- /dev/null
+++ b/regression-test/data/query_p0/sql_functions/test_years_add.out
@@ -0,0 +1,163 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !empty_nullable --
+
+-- !empty_not_nullable --
+
+-- !empty_partial_nullable --
+
+-- !nullable --
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+\N
+1999-12-31T23:59:59
+2000-12-31T23:59:59
+2001-12-31T23:59:59
+2004-12-31T23:59:59
+2019-01-01T00:00
+2019-02-28T00:00
+2020-01-01T00:00
+2020-02-29T00:00
+2021-01-01T00:00
+2021-02-28T00:00
+2022-06-15T12:30:45
+2023-06-15T12:30:45
+2024-01-01T00:00
+2024-02-29T00:00
+2024-06-15T12:30:45
+2027-06-15T12:30:45
+
+-- !not_nullable --
+1998-12-31T00:00
+1999-12-31T00:00
+1999-12-31T23:59:59
+2000-12-31T00:00
+2000-12-31T23:59:59
+2001-12-31T23:59:59
+2003-12-31T00:00
+2004-12-31T23:59:59
+2019-01-01T00:00
+2019-02-28T00:00
+2020-01-01T00:00
+2020-02-29T00:00
+2021-01-01T00:00
+2021-02-28T00:00
+2022-06-15T12:30:45
+2023-06-15T12:30:45
+2024-01-01T00:00
+2024-02-29T00:00
+2024-06-15T12:30:45
+2027-06-15T12:30:45
+2099-12-31T00:00
+2100-12-31T23:59:59
+2120-01-01T00:00
+2120-02-29T00:00
+2123-06-15T12:30:45
+
+-- !partial_nullable --
+\N
+\N
+\N
+\N
+\N
+1998-12-31T00:00
+1999-12-31T00:00
+1999-12-31T23:59:59
+2000-12-31T00:00
+2000-12-31T23:59:59
+2001-12-31T23:59:59
+2003-12-31T00:00
+2004-12-31T23:59:59
+2019-01-01T00:00
+2019-02-28T00:00
+2020-01-01T00:00
+2020-02-29T00:00
+2021-01-01T00:00
+2021-02-28T00:00
+2022-06-15T12:30:45
+2023-06-15T12:30:45
+2024-01-01T00:00
+2024-02-29T00:00
+2024-06-15T12:30:45
+2027-06-15T12:30:45
+
+-- !nullable_no_null --
+1998-12-31T00:00
+1999-12-31T00:00
+1999-12-31T23:59:59
+2000-12-31T00:00
+2000-12-31T23:59:59
+2001-12-31T23:59:59
+2003-12-31T00:00
+2004-12-31T23:59:59
+2019-01-01T00:00
+2019-02-28T00:00
+2020-01-01T00:00
+2020-02-29T00:00
+2021-01-01T00:00
+2021-02-28T00:00
+2022-06-15T12:30:45
+2023-06-15T12:30:45
+2024-01-01T00:00
+2024-02-29T00:00
+2024-06-15T12:30:45
+2027-06-15T12:30:45
+2099-12-31T00:00
+2100-12-31T23:59:59
+2120-01-01T00:00
+2120-02-29T00:00
+2123-06-15T12:30:45
+
+-- !const_nullable --
+\N
+\N
+\N
+\N
+\N
+
+-- !partial_const_nullable --
+\N
+\N
+\N
+\N
+\N
+
+-- !const_not_nullable --
+2021-01-01T00:00
+2021-01-01T00:00
+2021-01-01T00:00
+2021-01-01T00:00
+2021-01-01T00:00
+
+-- !const_other_nullable --
+\N
+2019-01-01T00:00
+2020-01-01T00:00
+2021-01-01T00:00
+2024-01-01T00:00
+
+-- !const_other_not_nullable --
+2000-12-31T00:00
+2001-12-31T23:59:59
+2021-01-01T00:00
+2021-02-28T00:00
+2024-06-15T12:30:45
+
+-- !const_nullable_no_null --
+2021-01-01T00:00
+
+-- !const_nullable_no_null_multirows --
+2021-01-01T00:00
+2021-01-01T00:00
+2021-01-01T00:00
+2021-01-01T00:00
+2021-01-01T00:00
+
+-- !const_partial_nullable_no_null --
+2021-01-01T00:00
+
diff --git
a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_quarters_add.groovy
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_quarters_add.groovy
new file mode 100644
index 00000000000..915d2eabcb4
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_quarters_add.groovy
@@ -0,0 +1,219 @@
+// 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.
+
+suite("test_quarters_add") {
+ // this table has nothing todo. just make it eaiser to generate query
+ sql " drop table if exists hits_two_args_quar_add "
+ sql """ create table hits_two_args_quar_add(
+ nothing boolean
+ )
+ properties("replication_num" = "1");
+ """
+ sql "insert into hits_two_args_quar_add values(true);"
+
+ sql " drop table if exists quarter_add_table"
+ sql """
+ create table quarter_add_table (
+ k0 int,
+ d1 date not null,
+ d2 date null,
+ dt1 datetime(3) not null,
+ dt2 datetime(6) null,
+ c1 int not null,
+ c2 int null
+ )
+ DISTRIBUTED BY HASH(k0)
+ PROPERTIES
+ (
+ "replication_num" = "1"
+ );
+ """
+
+ order_qt_empty_nullable "select quarters_add(d2, c2) from
quarter_add_table"
+ order_qt_empty_not_nullable "select quarters_add(dt1, c1) from
quarter_add_table"
+ order_qt_empty_partial_nullable "select quarters_add(dt2, c1) from
quarter_add_table"
+
+ sql """insert into quarter_add_table values (1, '2020-12-12', null,
'2020-12-12', null, 1, null),
+ (2, '2020-12-12', null, '2020-12-12', null, 1, null), (3, '2020-12-12',
null, '2020-12-12', null, 1, null)"""
+ order_qt_all_null "select quarters_add(dt2, c2) from quarter_add_table"
+
+ sql "truncate table quarter_add_table"
+ sql """ insert into quarter_add_table values
+ (1, '2020-12-12', '2020-12-12', '2020-12-12 12:12:12.123456',
'2020-12-12 12:12:12.123456', 1, 1),
+ (2, '4000-12-31', '4000-12-31', '4000-12-31 12:12:12.123456',
'4000-12-31 12:12:12.123456', -1, -1),
+ (3, '1900-01-01', '1900-01-01', '1900-01-01 00:00:00.000001',
'1900-01-01 00:00:00.000001', 100, 100),
+ (4, '0900-12-01', '0900-12-01', '0900-12-01 12:12:12.123456',
'0900-12-01 12:12:12.123456', -100, -100),
+ (5, '1900-02-28', '1900-02-28', '1900-02-28 00:00:00.000001',
'1900-02-28 00:00:00.000001', 4, 4),
+ (6, '2000-02-28', '2000-02-28', '2000-02-28 23:59:59.999000',
'2000-02-28 23:59:59.999000', -400, -400),
+ (7, '2000-02-29', '2000-02-29', '2000-02-29 23:59:59.999000',
'2000-02-29 23:59:59.999000', 123, 123),
+ (8, '2100-02-28', null, '2020-12-12 12:12:12.123456', null, 2,
null);
+ """
+
+ /// all values. consider nullity.
+ order_qt_nullable_d """
+ SELECT quarters_add(t.quarter_add_table, t.ARG2) as result
+ FROM (
+ SELECT hits_two_args_quar_add.nothing, TABLE1.quarter_add_table,
TABLE1.order1, TABLE2.ARG2, TABLE2.order2
+ FROM hits_two_args_quar_add
+ CROSS JOIN (
+ SELECT d2 as quarter_add_table, k0 as order1
+ FROM quarter_add_table
+ ) as TABLE1
+ CROSS JOIN (
+ SELECT c2 as ARG2, k0 as order2
+ FROM quarter_add_table
+ ) as TABLE2
+ )t;
+ """
+ order_qt_partial_nullable_dt """
+ SELECT quarters_add(t.quarter_add_table, t.ARG2) as result
+ FROM (
+ SELECT hits_two_args_quar_add.nothing, TABLE1.quarter_add_table,
TABLE1.order1, TABLE2.ARG2, TABLE2.order2
+ FROM hits_two_args_quar_add
+ CROSS JOIN (
+ SELECT dt2 as quarter_add_table, k0 as order1
+ FROM quarter_add_table
+ ) as TABLE1
+ CROSS JOIN (
+ SELECT c1 as ARG2, k0 as order2
+ FROM quarter_add_table
+ ) as TABLE2
+ )t;
+ """
+ order_qt_not_null_dt """
+ SELECT quarters_add(t.quarter_add_table, t.ARG2) as result
+ FROM (
+ SELECT hits_two_args_quar_add.nothing, TABLE1.quarter_add_table,
TABLE1.order1, TABLE2.ARG2, TABLE2.order2
+ FROM hits_two_args_quar_add
+ CROSS JOIN (
+ SELECT d1 as quarter_add_table, k0 as order1
+ FROM quarter_add_table
+ ) as TABLE1
+ CROSS JOIN (
+ SELECT c1 as ARG2, k0 as order2
+ FROM quarter_add_table
+ ) as TABLE2
+ )t;
+ """
+ order_qt_partial_nullable_d """
+ SELECT quarters_add(t.quarter_add_table, t.ARG2) as result
+ FROM (
+ SELECT hits_two_args_quar_add.nothing, TABLE1.quarter_add_table,
TABLE1.order1, TABLE2.ARG2, TABLE2.order2
+ FROM hits_two_args_quar_add
+ CROSS JOIN (
+ SELECT dt1 as quarter_add_table, k0 as order1
+ FROM quarter_add_table
+ ) as TABLE1
+ CROSS JOIN (
+ SELECT c2 as ARG2, k0 as order2
+ FROM quarter_add_table
+ ) as TABLE2
+ )t;
+ """
+
+ /// nullables
+ order_qt_not_nullable "select quarters_add(d1, c1) from quarter_add_table"
+ order_qt_partial_nullable "select quarters_add(dt2, c2) from
quarter_add_table"
+ order_qt_nullable_no_null "select quarters_add(dt1, nullable(c1)) from
quarter_add_table"
+
+ /// consts. most by BE-UT
+ order_qt_const_nullable "select quarters_add(NULL, NULL) from
quarter_add_table"
+ order_qt_partial_const_nullable "select quarters_add(NULL, c1) from
quarter_add_table"
+ order_qt_const_not_nullable "select quarters_add('1800-12-31', 100) from
quarter_add_table"
+ order_qt_const_other_nullable "select quarters_add('9000-12-31', c2) from
quarter_add_table"
+ order_qt_const_other_not_nullable "select quarters_add(dt1, 10) from
quarter_add_table"
+ order_qt_const_nullable_no_null "select
quarters_add(nullable('2015-10-10'), nullable(-100))"
+ order_qt_const_nullable_no_null_multirows "select
quarters_add(nullable('9999-01-01'), nullable(-100)) from quarter_add_table"
+ order_qt_const_partial_nullable_no_null "select quarters_add('1234-01-01',
nullable(-100))"
+
+ /// folding
+ check_fold_consistency "quarters_add('2000-02-29', 3)"
+ check_fold_consistency "quarters_add('2000-02-29', -300)"
+ check_fold_consistency "quarters_add('0000-01-01', 1000)"
+ check_fold_consistency "quarters_add('1999-02-28', 4)"
+ check_fold_consistency "quarters_add('1900-02-28', -400)"
+
+ /// special grammar
+ qt_datediff1 "select date_sub('2020-12-12', interval 1 quarter)"
+ qt_datediff2 "select date_add('2020-12-12', interval 1 quarter)"
+
+ // Exception test cases for boundary conditions on BE
+ sql "set debug_skip_fold_constant=true;"
+ test {
+ sql """select quarters_add('9999-12-31', 1) from
hits_two_args_quar_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ test {
+ sql """select quarters_add('0000-01-01', -1) from
hits_two_args_quar_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ test {
+ sql """select quarters_add('2023-01-01', 40000) from
hits_two_args_quar_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ test {
+ sql """select quarters_add('2023-01-01', -40000) from
hits_two_args_quar_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ // Exception test cases for nullable scenarios
+ test {
+ sql """select quarters_add(nullable('9999-12-31'), 1) from
hits_two_args_quar_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ test {
+ sql """select quarters_add('9999-12-31', nullable(1)) from
hits_two_args_quar_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ test {
+ sql """select quarters_add(nullable('0000-01-01'), nullable(-1)) from
hits_two_args_quar_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ test {
+ sql """select quarters_add(nullable('2023-01-01'), nullable(40000))
from hits_two_args_quar_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ test {
+ sql """select quarters_add(nullable('2023-01-01'), nullable(-40000))
from hits_two_args_quar_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+}
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/sql_functions/test_years_add.groovy
b/regression-test/suites/query_p0/sql_functions/test_years_add.groovy
new file mode 100644
index 00000000000..99e96522666
--- /dev/null
+++ b/regression-test/suites/query_p0/sql_functions/test_years_add.groovy
@@ -0,0 +1,185 @@
+// 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.
+
+suite("test_years_add") {
+ // this table has nothing todo. just make it easier to generate query
+ sql "drop table if exists hits_years_add"
+ sql """
+ create table hits_years_add(
+ nothing boolean
+ )
+ properties("replication_num" = "1");
+ """
+ sql "insert into hits_years_add values(true);"
+
+ sql "drop table if exists dates_tbl"
+ sql """
+ create table dates_tbl (
+ k0 int,
+ dt_val datetime not null,
+ dt_null datetime null
+ )
+ DISTRIBUTED BY HASH(k0)
+ PROPERTIES
+ (
+ "replication_num" = "1"
+ );
+ """
+
+ sql "drop table if exists years_tbl"
+ sql """
+ create table years_tbl (
+ k0 int,
+ years_val int not null,
+ years_null int null
+ )
+ DISTRIBUTED BY HASH(k0)
+ PROPERTIES
+ (
+ "replication_num" = "1"
+ );
+ """
+
+ // Empty table tests
+ order_qt_empty_nullable "select years_add(dt_null, years_val) from
dates_tbl, years_tbl"
+ order_qt_empty_not_nullable "select years_add(dt_val, years_val) from
dates_tbl, years_tbl"
+ order_qt_empty_partial_nullable "select years_add(dt_val, years_null) from
dates_tbl, years_tbl"
+
+ // Insert test data
+ sql """
+ insert into dates_tbl values
+ (1, '2020-01-01 00:00:00', '2020-01-01 00:00:00'), -- regular date
+ (2, '2020-02-29 00:00:00', '2020-02-29 00:00:00'), -- leap year date
+ (3, '2000-12-31 23:59:59', '2000-12-31 23:59:59'), -- century leap
year
+ (4, '2023-06-15 12:30:45', '2023-06-15 12:30:45'), -- date with time
+ (5, '1999-12-31 00:00:00', null) -- null value
+ """
+
+ sql """
+ insert into years_tbl values
+ (1, 0, 0), -- no change
+ (2, 1, 1), -- add one year
+ (3, -1, -1), -- subtract one year
+ (4, 4, 4), -- leap year cycle
+ (5, 100, null) -- null value
+ """
+
+ // All values cross join test
+ order_qt_nullable """
+ SELECT years_add(t.dt, t.years) as result
+ FROM (
+ SELECT hits_years_add.nothing, TABLE1.dt, TABLE1.order1,
TABLE2.years, TABLE2.order2
+ FROM hits_years_add
+ CROSS JOIN (
+ SELECT dt_null as dt, k0 as order1
+ FROM dates_tbl
+ ) as TABLE1
+ CROSS JOIN (
+ SELECT years_null as years, k0 as order2
+ FROM years_tbl
+ ) as TABLE2
+ )t;
+ """
+
+ // Nullable tests
+ order_qt_not_nullable "select years_add(dt_val, years_val) from dates_tbl,
years_tbl"
+ order_qt_partial_nullable "select years_add(dt_val, years_null) from
dates_tbl, years_tbl"
+ order_qt_nullable_no_null "select years_add(dt_val, nullable(years_val))
from dates_tbl, years_tbl"
+
+ // Constant tests
+ order_qt_const_nullable "select years_add(NULL, NULL) from dates_tbl"
+ order_qt_partial_const_nullable "select years_add(NULL, years_val) from
years_tbl"
+ order_qt_const_not_nullable "select years_add('2020-01-01 00:00:00', 1)
from dates_tbl"
+ order_qt_const_other_nullable "select years_add('2020-01-01 00:00:00',
years_null) from years_tbl"
+ order_qt_const_other_not_nullable "select years_add(dt_val, 1) from
dates_tbl"
+ order_qt_const_nullable_no_null "select years_add(nullable('2020-01-01
00:00:00'), nullable(1))"
+ order_qt_const_nullable_no_null_multirows "select
years_add(nullable('2020-01-01 00:00:00'), nullable(1)) from dates_tbl"
+ order_qt_const_partial_nullable_no_null "select years_add('2020-01-01
00:00:00', nullable(1))"
+
+ // Constant folding tests
+ check_fold_consistency "years_add('2020-01-01 00:00:00', 1)"
+ check_fold_consistency "years_add('2020-02-29 00:00:00', 1)" // leap year
to non-leap year
+ check_fold_consistency "years_add('2000-12-31 23:59:59', 100)"
+ check_fold_consistency "years_add('1999-12-31 00:00:00', -10)"
+ check_fold_consistency "years_add('2023-06-15 12:30:45', 0)"
+
+ // Exception test cases for boundary conditions on BE
+ sql "set debug_skip_fold_constant=true;"
+ test {
+ sql """select years_add('9999-12-31', 1) from hits_years_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ test {
+ sql """select years_add('0000-01-01', -1) from hits_years_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ test {
+ sql """select years_add('2023-01-01', 10000) from hits_years_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ test {
+ sql """select years_add('2023-01-01', -10000) from hits_years_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ // Exception test cases for nullable scenarios
+ test {
+ sql """select years_add(nullable('9999-12-31'), 1) from
hits_years_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ test {
+ sql """select years_add('9999-12-31', nullable(1)) from
hits_years_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ test {
+ sql """select years_add(nullable('0000-01-01'), nullable(-1)) from
hits_years_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ test {
+ sql """select years_add(nullable('2023-01-01'), nullable(10000)) from
hits_years_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+
+ test {
+ sql """select years_add(nullable('2023-01-01'), nullable(-10000)) from
hits_years_add;"""
+ check { result, exception, startTime, endTime ->
+ assertTrue(exception != null)
+ }
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
