This is an automated email from the ASF dual-hosted git repository.
xiong 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 c8cb56525d [CALCITE-6527] Add DATE_ADD function (enabled in Spark
library)
c8cb56525d is described below
commit c8cb56525dbc908da4a9081f062d87adfe27ab80
Author: cancaicai <[email protected]>
AuthorDate: Tue Aug 13 23:03:36 2024 +0800
[CALCITE-6527] Add DATE_ADD function (enabled in Spark library)
---
.../calcite/sql/fun/SqlLibraryOperators.java | 10 +++
.../calcite/sql2rel/StandardConvertletTable.java | 87 +++++++++++++++-------
site/_docs/reference.md | 3 +-
.../org/apache/calcite/test/SqlOperatorTest.java | 61 +++++++++++++++
4 files changed, 134 insertions(+), 27 deletions(-)
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 2b6466034d..306630e6cc 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,16 @@ public abstract class SqlLibraryOperators {
public static final SqlFunction DATEADD =
new SqlTimestampAddFunction("DATEADD");
+ /** The "DATE_ADD(start_date, num_days)" function
+ * (Spark) Returns the date that is num_days after start_date. */
+ @LibraryOperator(libraries = {SPARK})
+ public static final SqlFunction DATE_ADD_SPARK =
+ SqlBasicFunction.create(SqlKind.DATE_ADD, ReturnTypes.DATE_NULLABLE,
+ OperandTypes.DATE_ANY)
+ .withFunctionType(SqlFunctionCategory.TIMEDATE);
+
+ /** The "ADD_MONTHS(start_date, num_months)" function
+ * (SPARK) Returns the date that is num_months after start_date. */
@LibraryOperator(libraries = {ORACLE, SPARK})
public static final SqlFunction ADD_MONTHS =
SqlBasicFunction.create(SqlKind.ADD_MONTHS, ReturnTypes.ARG0_NULLABLE,
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 216c34fd71..3428c4cea3 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -210,6 +210,8 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
new TimestampAddConvertlet());
registerOp(SqlLibraryOperators.ADD_MONTHS,
new TimestampAddConvertlet());
+ registerOp(SqlLibraryOperators.DATE_ADD_SPARK,
+ new TimestampAddConvertlet());
registerOp(SqlLibraryOperators.DATE_DIFF,
new TimestampDiffConvertlet());
registerOp(SqlLibraryOperators.DATE_SUB,
@@ -2146,6 +2148,57 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
}
}
+ /**
+ * Handles the first parameter of the SQL call.
+ * Converts the first operand to a RexNode and casts it to DATE type if it
is a TIMESTAMP.
+ *
+ * @param cx The SQL to Rex conversion context.
+ * @param rexBuilder The RexBuilder to create RexNode instances.
+ * @param call The SQL call containing the operands.
+ * @return The converted RexNode for the first parameter.
+ */
+ private static RexNode handleFirstParameter(SqlRexContext cx, RexBuilder
rexBuilder,
+ SqlCall call) {
+ RexNode opfirstparameter = cx.convertExpression(call.operand(0));
+ if (opfirstparameter.getType().getSqlTypeName() == SqlTypeName.TIMESTAMP) {
+ RelDataType timestampType =
cx.getTypeFactory().createSqlType(SqlTypeName.DATE);
+ return rexBuilder.makeCast(timestampType, opfirstparameter);
+ } else {
+ return cx.convertExpression(call.operand(0));
+ }
+ }
+
+
+ /**
+ * Handles the second parameter of the SQL call.
+ * Converts the second operand to a RexNode and casts it to INTEGER type if
it is a CHAR.
+ *
+ * @param cx The SQL to Rex conversion context.
+ * @param rexBuilder The RexBuilder to create RexNode instances.
+ * @param call The SQL call containing the operands.
+ * @return The converted RexNode for the second parameter.
+ */
+ private static RexNode handleSecondParameter(SqlRexContext cx, RexBuilder
rexBuilder,
+ SqlCall call) {
+ 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);
+ return rexBuilder.makeCast(intType, opsecondparameter);
+ }
+
/** Convertlet that handles the 3-argument {@code TIMESTAMPADD} function
* and the 2-argument BigQuery-style {@code TIMESTAMP_ADD} function. */
private static class TimestampAddConvertlet implements SqlRexConvertlet {
@@ -2162,34 +2215,16 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
final RexNode op2;
switch (call.operandCount()) {
case 2:
- // Oracle-style 'ADD_MONTHS(date, integer months)'
if (call.getOperator() == SqlLibraryOperators.ADD_MONTHS) {
+ // Oracle-style 'ADD_MONTHS(date, integer 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);
+ op2 = handleFirstParameter(cx, rexBuilder, call);
+ op1 = handleSecondParameter(cx, rexBuilder, call);
+ } else if (call.getOperator() == SqlLibraryOperators.DATE_ADD_SPARK) {
+ // Spark-style 'DATE_ADD(date, integer days)'
+ qualifier = new SqlIntervalQualifier(TimeUnit.DAY, null,
SqlParserPos.ZERO);
+ op2 = handleFirstParameter(cx, rexBuilder, call);
+ op1 = handleSecondParameter(cx, rexBuilder, call);
} else {
// BigQuery-style 'TIMESTAMP_ADD(timestamp, interval)'
final SqlBasicCall operandCall = call.operand(1);
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index b7873ca899..3d855df7fb 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2721,7 +2721,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*
+| o s | ADD_MONTHS(date, numMonths) | Returns the date that
is *numMonths* after *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*
@@ -2804,6 +2804,7 @@ In the following:
| b s | DATE_FROM_UNIX_DATE(integer) | Returns the DATE that
is *integer* days after 1970-01-01
| p r | DATE_PART(timeUnit, datetime) | Equivalent to
`EXTRACT(timeUnit FROM datetime)`
| b | DATE_ADD(date, interval) | Returns the DATE value
that occurs *interval* after *date*
+| s | DATE_ADD(date, numDays) | Returns the DATE that
is *numDays* after *date*
| b | DATE_DIFF(date, date2, timeUnit) | Returns the whole
number of *timeUnit* between *date* and *date2*
| b | DATE_SUB(date, interval) | Returns the DATE value
that occurs *interval* before *date*
| b | DATE_TRUNC(date, timeUnit) | Truncates *date* to the
granularity of *timeUnit*, rounding to the beginning of the unit
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 1b3f720d9a..96772c7ce1 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -13930,6 +13930,67 @@ 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-6527";>[CALCITE-6527]
+ * Add DATE_ADD function (enabled in Spark library)</a>.
+ */
+ @Test void testDateAddSpark() {
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.DATE_ADD_SPARK);
+ f0.checkFails("^date_add(date '2008-12-25', "
+ + "5)^",
+ "No match found for function signature "
+ + "DATE_ADD\\(<DATE>, <NUMERIC>\\)", false);
+
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.SPARK);
+ f.checkScalar("date_add(date '2016-02-22', 2)",
+ "2016-02-24",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '2016-02-28', 2)",
+ "2016-03-01",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '2016-03-01', -2)",
+ "2016-02-28",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '2016-03-01', -2)",
+ "2016-02-28",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '2016-03-01', -2.0)",
+ "2016-02-28",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '2016-02-28', 2.0)",
+ "2016-03-01",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '2016-02-28', '2.0')",
+ "2016-03-01",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(timestamp '2016-02-22 13:00:01', '-2.0')",
+ "2016-02-20",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(timestamp '2016-02-22 13:00:01', -2)",
+ "2016-02-20",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(timestamp '2016-02-22 13:00:01', -2.0)",
+ "2016-02-20",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '0001-01-01', '-2.0')",
+ "0000-12-30",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '0001-01-01', '-367')",
+ "000/-12-31",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '0001-01-01', '-3')",
+ "0000-12-29",
+ "DATE NOT NULL");
+ f.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
+ f.checkNull("date_add(CAST(NULL AS DATE), CAST(NULL AS INTEGER))");
+ f.checkFails("^date_add(time '13:00:01', -2)^",
INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR,
+ false);
+ f.checkFails("^date_add(1, -2)^", INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR,
+ false);
+ }
+
/** 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>.
