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 659ea980b0 [CALCITE-6612] Add DATE_SUB function(enabled in Spark
library)
659ea980b0 is described below
commit 659ea980b077ed6721debc27f19eedaa9616e0ab
Author: Cancai Cai <[email protected]>
AuthorDate: Mon Sep 30 18:06:37 2024 +0800
[CALCITE-6612] Add DATE_SUB function(enabled in Spark library)
---
.../calcite/sql/fun/SqlLibraryOperators.java | 10 ++++++-
.../calcite/sql2rel/StandardConvertletTable.java | 20 ++++++++++---
site/_docs/reference.md | 1 +
.../org/apache/calcite/test/SqlOperatorTest.java | 34 ++++++++++++++++++++++
4 files changed, 60 insertions(+), 5 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 306630e6cc..edde88f758 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,7 +132,7 @@ public abstract class SqlLibraryOperators {
public static final SqlFunction DATEADD =
new SqlTimestampAddFunction("DATEADD");
- /** The "DATE_ADD(start_date, num_days)" function
+ /** The "DATE_ADD(date, numDays)" function
* (Spark) Returns the date that is num_days after start_date. */
@LibraryOperator(libraries = {SPARK})
public static final SqlFunction DATE_ADD_SPARK =
@@ -140,6 +140,14 @@ public abstract class SqlLibraryOperators {
OperandTypes.DATE_ANY)
.withFunctionType(SqlFunctionCategory.TIMEDATE);
+ /** The "DATE_SUB(date, numDays)" function
+ * (Spark) Returns the date that is num_days before start_date.*/
+ @LibraryOperator(libraries = {SPARK})
+ public static final SqlFunction DATE_SUB_SPARK =
+ SqlBasicFunction.create(SqlKind.DATE_SUB, 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})
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 3428c4cea3..7d9c807cd0 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -216,6 +216,8 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
new TimestampDiffConvertlet());
registerOp(SqlLibraryOperators.DATE_SUB,
new TimestampSubConvertlet());
+ registerOp(SqlLibraryOperators.DATE_SUB_SPARK,
+ new TimestampSubConvertlet());
registerOp(SqlLibraryOperators.DATETIME_ADD,
new TimestampAddConvertlet());
registerOp(SqlLibraryOperators.DATETIME_DIFF,
@@ -2309,10 +2311,20 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
// => timestamp - count * INTERVAL '1' UNIT
final RexBuilder rexBuilder = cx.getRexBuilder();
final SqlParserPos pos = call.getParserPosition();
- final SqlBasicCall operandCall = call.operand(1);
- SqlIntervalQualifier qualifier = operandCall.operand(1);
- final RexNode op1 = cx.convertExpression(operandCall.operand(0));
- final RexNode op2 = cx.convertExpression(call.operand(0));
+ SqlIntervalQualifier qualifier;
+ final RexNode op1;
+ final RexNode op2;
+ if (call.getOperator() == SqlLibraryOperators.DATE_SUB_SPARK) {
+ // Spark-style 'DATE_SUB(date, integer days)'
+ qualifier = new SqlIntervalQualifier(TimeUnit.DAY, null,
SqlParserPos.ZERO);
+ op2 = handleFirstParameter(cx, rexBuilder, call);
+ op1 = handleSecondParameter(cx, rexBuilder, call);
+ } else {
+ final SqlBasicCall operandCall = call.operand(1);
+ qualifier = operandCall.operand(1);
+ op1 = cx.convertExpression(operandCall.operand(0));
+ op2 = cx.convertExpression(call.operand(0));
+ }
final TimeFrame timeFrame =
cx.getValidator().validateTimeFrame(qualifier);
final TimeUnit unit = first(timeFrame.unit(), TimeUnit.EPOCH);
final RexNode interval2Sub;
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index c18bf6f691..9924940c59 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2808,6 +2808,7 @@ In the following:
| 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*
+| s | DATE_SUB(date, numDays) | Returns the DATE that
is *numDays* before *date*
| b | DATE_TRUNC(date, timeUnit) | Truncates *date* to the
granularity of *timeUnit*, rounding to the beginning of the unit
| o r s | DECODE(value, value1, result1 [, valueN, resultN ]* [, default ]) |
Compares *value* to each *valueN* value one by one; if *value* is equal to a
*valueN*, returns the corresponding *resultN*, else returns *default*, or NULL
if *default* is not specified
| p r | DIFFERENCE(string, string) | Returns a measure of
the similarity of two strings, namely the number of character positions that
their `SOUNDEX` values have in common: 4 if the `SOUNDEX` values are same and 0
if the `SOUNDEX` values are totally different
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 5a4c7ecc3b..5ba92aa601 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -14060,6 +14060,40 @@ public class SqlOperatorTest {
false);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6612";>[CALCITE-6612]
+ * Add DATE_SUB function (enabled in Spark library)</a>.
+ */
+ @Test void testDateSubSpark() {
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.DATE_SUB_SPARK);
+ f0.checkFails("^date_sub(date '2008-12-25', "
+ + "5)^",
+ "No match found for function signature "
+ + "DATE_SUB\\(<DATE>, <NUMERIC>\\)", false);
+
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.SPARK);
+ f.checkScalar("date_sub(date '2016-02-22', 2)",
+ "2016-02-20",
+ "DATE NOT NULL");
+ f.checkScalar("date_sub(date '2016-03-01', 2)",
+ "2016-02-28",
+ "DATE NOT NULL");
+ f.checkScalar("date_sub(timestamp '2016-02-22 13:00:01', '-2.0')",
+ "2016-02-24",
+ "DATE NOT NULL");
+ f.checkScalar("date_sub(timestamp '2016-02-22 13:00:01', -2)",
+ "2016-02-24",
+ "DATE NOT NULL");
+ f.checkNull("date_sub(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_sub(date '2016-02-22', CAST(NULL AS INTEGER))");
+ f.checkNull("date_sub(CAST(NULL AS DATE), CAST(NULL AS INTEGER))");
+ f.checkFails("^date_sub(time '13:00:01', -2)^",
INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR,
+ false);
+ f.checkFails("^date_sub(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>.
