This is an automated email from the ASF dual-hosted git repository.
mbudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 010000e2c5 [CALCITE-6396] Add ADD_MONTHS function (enabled in Oracle,
Spark library)
010000e2c5 is described below
commit 010000e2c571e49fc725f4b3a7b339e16a4dd7be
Author: cancaicai <[email protected]>
AuthorDate: Sat Aug 10 16:11:05 2024 +0800
[CALCITE-6396] Add ADD_MONTHS function (enabled in Oracle, Spark library)
---
.../main/java/org/apache/calcite/sql/SqlKind.java | 3 +
.../calcite/sql/fun/SqlLibraryOperators.java | 6 ++
.../org/apache/calcite/sql/type/OperandTypes.java | 3 +
.../calcite/sql2rel/StandardConvertletTable.java | 42 ++++++++++++--
site/_docs/reference.md | 1 +
.../org/apache/calcite/test/SqlOperatorTest.java | 67 ++++++++++++++++++++++
6 files changed, 117 insertions(+), 5 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index dccab4755f..0b6b48bd09 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -487,6 +487,9 @@ public enum SqlKind {
/** {@code DATE_ADD} function (BigQuery Semantics). */
DATE_ADD,
+ /** {@code ADD_MONTHS} function (Oracle, Spark). */
+ ADD_MONTHS,
+
/** {@code DATE_TRUNC} function (BigQuery). */
DATE_TRUNC,
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index a3069cec11..131ac7c3a7 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -132,6 +132,12 @@ public abstract class SqlLibraryOperators {
public static final SqlFunction DATEADD =
new SqlTimestampAddFunction("DATEADD");
+ @LibraryOperator(libraries = {ORACLE, SPARK})
+ public static final SqlFunction ADD_MONTHS =
+ SqlBasicFunction.create(SqlKind.ADD_MONTHS, ReturnTypes.ARG0_NULLABLE,
+ OperandTypes.DATE_ANY)
+ .withFunctionType(SqlFunctionCategory.TIMEDATE);
+
/** The "DATEDIFF(timeUnit, datetime, datetime2)" function
* (Microsoft SQL Server, Redshift, Snowflake).
*
diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
index 5ba35b889b..6182a406ec 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
@@ -1200,6 +1200,9 @@ public abstract class OperandTypes {
public static final SqlSingleOperandTypeChecker DATE_INTERVAL =
family(SqlTypeFamily.DATE, SqlTypeFamily.DATETIME_INTERVAL);
+ public static final SqlSingleOperandTypeChecker DATE_ANY =
+ family(SqlTypeFamily.DATE, SqlTypeFamily.ANY);
+
public static final SqlSingleOperandTypeChecker DATE_CHARACTER =
family(SqlTypeFamily.DATE, SqlTypeFamily.CHARACTER);
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index 57d7eba15d..4e8b4fb892 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -207,6 +207,8 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
registerOp(SqlLibraryOperators.DATE_ADD,
new TimestampAddConvertlet());
+ registerOp(SqlLibraryOperators.ADD_MONTHS,
+ new TimestampAddConvertlet());
registerOp(SqlLibraryOperators.DATE_DIFF,
new TimestampDiffConvertlet());
registerOp(SqlLibraryOperators.DATE_SUB,
@@ -2140,11 +2142,41 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
final RexNode op2;
switch (call.operandCount()) {
case 2:
- // BigQuery-style 'TIMESTAMP_ADD(timestamp, interval)'
- final SqlBasicCall operandCall = call.operand(1);
- qualifier = operandCall.operand(1);
- op1 = cx.convertExpression(operandCall.operand(0));
- op2 = cx.convertExpression(call.operand(0));
+ // Oracle-style 'ADD_MONTHS(date, integer months)'
+ if (call.getOperator() == SqlLibraryOperators.ADD_MONTHS) {
+ qualifier = new SqlIntervalQualifier(TimeUnit.MONTH, null,
SqlParserPos.ZERO);
+ RexNode opfirstparameter = cx.convertExpression(call.operand(0));
+ if (opfirstparameter.getType().getSqlTypeName() ==
SqlTypeName.TIMESTAMP) {
+ RelDataType timestampType =
cx.getTypeFactory().createSqlType(SqlTypeName.DATE);
+ op2 = rexBuilder.makeCast(timestampType, opfirstparameter);
+ } else {
+ op2 = cx.convertExpression(call.operand(0));
+ }
+
+ RexNode opsecondparameter = cx.convertExpression(call.operand(1));
+ RelDataTypeFactory typeFactory = cx.getTypeFactory();
+
+ // In Calcite, cast('1.2' as integer) is invalid.
+ // For details, see
https://issues.apache.org/jira/browse/CALCITE-1439
+ // When trying to cast a string value to an integer type, Calcite may
+ // encounter errors if the string value cannot be successfully
converted.
+ // To handle this, the string needs to be first converted to a
double type,
+ // and then the double value can be converted to an integer type.
+ // Since the final target type is integer, converting the string to
double first
+ // will not lose precision for the add_months operation.
+ if (opsecondparameter.getType().getSqlTypeName() ==
SqlTypeName.CHAR) {
+ RelDataType doubleType =
typeFactory.createSqlType(SqlTypeName.DOUBLE);
+ opsecondparameter = rexBuilder.makeCast(doubleType,
opsecondparameter);
+ }
+ RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
+ op1 = rexBuilder.makeCast(intType, opsecondparameter);
+ } else {
+ // BigQuery-style 'TIMESTAMP_ADD(timestamp, interval)'
+ final SqlBasicCall operandCall = call.operand(1);
+ qualifier = operandCall.operand(1);
+ op1 = cx.convertExpression(operandCall.operand(0));
+ op2 = cx.convertExpression(call.operand(0));
+ }
break;
default:
// JDBC-style 'TIMESTAMPADD(unit, count, timestamp)'
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index fe8ff90365..02b81864ed 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2696,6 +2696,7 @@ In the following:
| m | expr1 <=> expr2 | Whether two values are
equal, treating null values as the same, and it's similar to `IS NOT DISTINCT
FROM`
| p | ACOSD(numeric) | Returns the inverse
cosine of *numeric* in degrees as a double. Returns NaN if *numeric* is NaN.
Fails if *numeric* is less than -1.0 or greater than 1.0.
| * | ACOSH(numeric) | Returns the inverse
hyperbolic cosine of *numeric*
+| o s | ADD_MONTHS(start_date, num_months) | Returns the date that
is *num_months* after *start_date*
| s | ARRAY([expr [, expr ]*]) | Construct an array in
Apache Spark. The function allows users to use `ARRAY()` to create an empty
array
| s | ARRAY_APPEND(array, element) | Appends an *element* to
the end of the *array* and returns the result. Type of *element* should be
similar to type of the elements of the *array*. If the *array* is null, the
function will return null. If an *element* that is null, the null *element*
will be added to the end of the *array*
| s | ARRAY_COMPACT(array) | Removes null values
from the *array*
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 18f4a4e9fb..44e5978670 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -13824,6 +13824,73 @@ public class SqlOperatorTest {
f.checkNull("date_add(CAST(NULL AS DATE), interval 5 day)");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6396";>[CALCITE-6396]
+ * Add ADD_MONTHS function (enabled in Oracle, Spark library)</a>.
+ */
+ @Test void testAddMonths() {
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.ADD_MONTHS);
+ f0.checkFails("^add_months(date '2008-12-25', "
+ + "5)^",
+ "No match found for function signature "
+ + "ADD_MONTHS\\(<DATE>, <NUMERIC>\\)", false);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkScalar("add_months(date '2016-02-22', 2)",
+ "2016-04-22",
+ "DATE NOT NULL");
+ f.checkScalar("add_months(date '2016-02-22', -2)",
+ "2015-12-22",
+ "DATE NOT NULL");
+ f.checkScalar("add_months('2016-08-31',1)",
+ "2016-09-30",
+ "DATE NOT NULL");
+ f.checkScalar("add_months('2016-08-31',1.0)",
+ "2016-09-30",
+ "DATE NOT NULL");
+ f.checkScalar("add_months('2016-08-31','1.0')",
+ "2016-09-30",
+ "DATE NOT NULL");
+ f.checkScalar("add_months('2016-08-31','1.1')",
+ "2016-09-30",
+ "DATE NOT NULL");
+ f.checkScalar("add_months('2016-08-31','1.6')",
+ "2016-09-30",
+ "DATE NOT NULL");
+ f.checkScalar("add_months(date '2016-02-22', -2.0)",
+ "2015-12-22",
+ "DATE NOT NULL");
+ f.checkScalar("add_months(date '2016-02-22', 2.0)",
+ "2016-04-22",
+ "DATE NOT NULL");
+ f.checkScalar("add_months(date '2016-02-22', '2')",
+ "2016-04-22",
+ "DATE NOT NULL");
+ f.checkScalar("add_months(date '2016-02-22', '-2')",
+ "2015-12-22",
+ "DATE NOT NULL");
+ f.checkScalar("add_months(timestamp '2016-02-22 13:00:01', '-2')",
+ "2015-12-22",
+ "DATE NOT NULL");
+ f.checkScalar("add_months(timestamp '2016-02-22 13:00:01', '-2.0')",
+ "2015-12-22",
+ "DATE NOT NULL");
+ f.checkScalar("add_months(timestamp '2016-02-22 13:00:01', -2)",
+ "2015-12-22",
+ "DATE NOT NULL");
+ f.checkScalar("add_months(timestamp '2016-02-22 13:00:01', -2.0)",
+ "2015-12-22",
+ "DATE NOT NULL");
+ f.checkFails("add_months(date '2016-02-22', '1e+1000')",
+ "Value Infinity out of range",
+ true);
+ f.checkNull("add_months(CAST(NULL AS DATE), 5)");
+ f.checkNull("add_months(date '2016-02-22', CAST(NULL AS INTEGER))");
+ f.checkNull("add_months(CAST(NULL AS DATE), CAST(NULL AS INTEGER))");
+ };
+ f0.forEachLibrary(list(SqlLibrary.ORACLE, SqlLibrary.SPARK), consumer);
+ }
+
@Test void testDateSub() {
final SqlOperatorFixture f0 = fixture()
.setFor(SqlLibraryOperators.DATE_SUB);
