You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by "caicancai (via GitHub)" <gi...@apache.org> on 2024/08/13 15:08:40 UTC
[PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
caicancai opened a new pull request, #3915:
URL: https://github.com/apache/calcite/pull/3915
https://issues.apache.org/jira/browse/CALCITE-6396
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1719176399
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
I thought I would do some tests and describe them later. I did a simple test yesterday and it looks like calcite does some processing but it's not perfect. I will fix it in this PR.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1732013424
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,63 @@ void testTimestampDiff(boolean coercionEnabled) {
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')",
Review Comment:
I don't understand what you are saying.
If you add two very large numbers in Calcite you will get a runtime error.
How is this different?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1720867290
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
Sorry for the late reply.
After testing, the date_add function will format the date. For example, invalid dates such as `0000-01-01` will fail the date format.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1720867290
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
@mihaibudiu Sorry for the late reply.
After testing, the date_add function will format the date. For example, invalid dates such as `0000-01-01` will fail the date format.
For example:
```
f.checkScalar("date_add(date '0000-01-01', '-3')",
"0000-12-29",
"DATE NOT NULL");
throw org.apache.calcite.runtime.CalciteContextException: From line 1, column 18 to line 1, column 34: Illegal DATE literal '0000-01-01': not in format 'yyyy-MM-dd'
```
But there doesn't seem to be any date format for the output, but I think that's another question.
```
f.checkScalar("date_add(date '0001-01-01', '-3')",
"0000-12-29",
"DATE NOT NULL");
```
and the input date may fail to format such as` date_add(date '000/-12-31', '2')`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1719176399
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
I think I will do some tests and describe them later. It seems that calcite has done some processing but it is not perfect. I will fix it in this PR
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1715579727
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
You should check the issues open for spark, this looks like a bug to me. There is no year 0.
If there is no bug maybe you can open one.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1720867290
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
@mihaibudiu Sorry for the late reply.
After testing, the date_add function will format the date. For example, invalid dates such as `0000-01-01` will fail the date format.
For example:
```
f.checkScalar("date_add(date '0000-01-01', '-3')",
"0000-12-29",
"DATE NOT NULL");
throw org.apache.calcite.runtime.CalciteContextException: From line 1, column 18 to line 1, column 34: Illegal DATE literal '0000-01-01': not in format 'yyyy-MM-dd'
```
AND
`date_add(date '000/-12-31', '2') not in format 'yyyy-MM-dd'`
But there doesn't seem to be any date format for the output, but I think that's another question.
```
f.checkScalar("date_add(date '0001-01-01', '-3')",
"0000-12-29",
"DATE NOT NULL");
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1732006280
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,63 @@ void testTimestampDiff(boolean coercionEnabled) {
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')",
Review Comment:
@mihaibudiu I'm not sure how to solve this bug, it looks a bit complicated to me ðŸ˜
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1732012637
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,63 @@ void testTimestampDiff(boolean coercionEnabled) {
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')",
Review Comment:
> I don't think Calcite can offer exact compatibility to Spark in this case.
> The best solution for Calcite is to give a runtime error.
> But I would still like to know why the date with a slash didn't report a different error.
@mihaibudiu Calcite will perform parser validation on the input parameters.
```
final Calendar cal =
DateTimeUtils.parseDateFormat(s, Format.get().date,
DateTimeUtils.UTC_ZONE);
```
However, there is no restriction on the output result set. I've been researching for a long time and I'm not sure where to limit the result set
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1718448437
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
@mihaibudiu I have already raised a jira case in spark
https://issues.apache.org/jira/browse/SPARK-49247
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1720867290
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
@mihaibudiu Sorry for the late reply.
After testing, the date_add function will format the date. For example, invalid dates such as `0000-01-01` will fail the date format.
For example:
```
f.checkScalar("date_add(date '0000-01-01', '-3')",
"0000-12-29",
"DATE NOT NULL");
throw org.apache.calcite.runtime.CalciteContextException: From line 1, column 18 to line 1, column 34: Illegal DATE literal '0000-01-01': not in format 'yyyy-MM-dd'
```
But there doesn't seem to be any date format for the output, but I think that's another question.
```
f.checkScalar("date_add(date '0001-01-01', '-3')",
"0000-12-29",
"DATE NOT NULL");
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on PR #3915:
URL: https://github.com/apache/calcite/pull/3915#issuecomment-2296705638
@mihaibudiu I added some tests that I thought were borderline and found some issues, I'd like to hear your thoughts
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1732012637
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,63 @@ void testTimestampDiff(boolean coercionEnabled) {
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')",
Review Comment:
> I don't think Calcite can offer exact compatibility to Spark in this case.
> The best solution for Calcite is to give a runtime error.
> But I would still like to know why the date with a slash didn't report a different error.
@mihaibudiu Calcite will perform parser validation on the input parameters.
```
final Calendar cal =
DateTimeUtils.parseDateFormat(s, Format.get().date,
DateTimeUtils.UTC_ZONE);
```
However, there is no restriction on the output result set. I've been researching for a long time and I'm not sure where to limit the result set.
Similar discussions: https://issues.apache.org/jira/browse/CALCITE-6544
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1715587559
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
OK, I will try again in the latest spark code. If there is no corresponding jira record, I will file a jira case to describe this bug.
I feel that 000-00-01 is reasonable, but negative numbers are unreasonable. Calcite currently follows this principle. The following are my test results in Calcite.
```
f.checkScalar("date_add('0001-01-01', -2)",
"0000-12-30",
"DATE NOT NULL");
f.checkFails("date_add('0000-01-01', -2)",
"Value of YEAR field is out of range in '0000-01-01'",
true);
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3915:
URL: https://github.com/apache/calcite/pull/3915#issuecomment-2291559193
## [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3915) **Quality Gate passed**
Issues
 [6 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3915&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3915&issueStatuses=ACCEPTED)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3915&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3915&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3915&metric=new_duplicated_lines_density&view=list)
[See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3915)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1715587559
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
OK, I will try again in the latest spark code. If there is no corresponding jira record in spark community, I will file a jira case to describe this bug.
I feel that 000-00-01 is reasonable, but negative numbers are unreasonable. Calcite currently follows this principle. The following are my test results in Calcite.
```
f.checkScalar("date_add('0001-01-01', -2)",
"0000-12-30",
"DATE NOT NULL");
f.checkFails("date_add('0000-01-01', -2)",
"Value of YEAR field is out of range in '0000-01-01'",
true);
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1732009110
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,63 @@ void testTimestampDiff(boolean coercionEnabled) {
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')",
Review Comment:
I don't think Calcite can offer exact compatibility to Spark in this case.
The best solution for Calcite is to give a runtime error.
But I would still like to know why the date with a slash didn't report a different error.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1722566476
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,63 @@ void testTimestampDiff(boolean coercionEnabled) {
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')",
Review Comment:
If a test passes that shouldn't it means that there's somewhere another bug that is masking a problem.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1722565426
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,63 @@ void testTimestampDiff(boolean coercionEnabled) {
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')",
Review Comment:
@mihaibudiu I raised a jira to discuss this issue. Should I delete this test?
https://issues.apache.org/jira/browse/CALCITE-6536
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1722566097
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,63 @@ void testTimestampDiff(boolean coercionEnabled) {
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')",
Review Comment:
I would still like to know why this test passes.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3915:
URL: https://github.com/apache/calcite/pull/3915#issuecomment-2295054789
## [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3915) **Quality Gate passed**
Issues
 [6 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3915&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3915&issueStatuses=ACCEPTED)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3915&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3915&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3915&metric=new_duplicated_lines_density&view=list)
[See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3915)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1720867290
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
Sorry for the late reply.
After testing, the date_add function will format the date. For example, invalid dates such as `0000-01-01` will fail the date format.
For example:
```
f.checkScalar("date_add(date '0000-01-01', '-3')",
"0000-12-29",
"DATE NOT NULL");
```
throw org.apache.calcite.runtime.CalciteContextException: From line 1, column 18 to line 1, column 34: Illegal DATE literal '0000-01-01': not in format 'yyyy-MM-dd'
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on PR #3915:
URL: https://github.com/apache/calcite/pull/3915#issuecomment-2319579188
@mihaibudiu Almost all date functions have this problem, I consider it another bug, I will fix it. Before fixing it, this PR is marked as draft.
Thank you for your review
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1720867290
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
@mihaibudiu Sorry for the late reply.
After testing, the date_add function will format the date. For example, invalid dates such as `0000-01-01` will fail the date format.
For example:
```
f.checkScalar("date_add(date '0000-01-01', '-3')",
"0000-12-29",
"DATE NOT NULL");
date_add(date '000/-12-31', '2')
throw org.apache.calcite.runtime.CalciteContextException: From line 1, column 18 to line 1, column 34: Illegal DATE literal '0000-01-01': not in format 'yyyy-MM-dd'
```
But there doesn't seem to be any date format for the output, but I think that's another question.
```
f.checkScalar("date_add(date '0001-01-01', '-3')",
"0000-12-29",
"DATE NOT NULL");
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1722193677
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,63 @@ void testTimestampDiff(boolean coercionEnabled) {
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')",
Review Comment:
what is going on here? Why is `000/` a valid year?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "julianhyde (via GitHub)" <gi...@apache.org>.
julianhyde commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1715674061
##########
site/_docs/reference.md:
##########
@@ -2772,6 +2772,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(start_date, num_days) | Returns the DATE that is *num_days* after *start_date*
Review Comment:
underscore?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1718566057
##########
site/_docs/reference.md:
##########
@@ -2772,6 +2772,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(start_date, num_days) | Returns the DATE that is *num_days* after *start_date*
Review Comment:
done
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1720867290
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
Sorry for the late reply.
After testing, the date_add function will format the date. For example, invalid dates such as `0000-01-01` will fail the date format.
For example:
```
f.checkScalar("date_add(date '0000-01-01', '-3')",
"0000-12-29",
"DATE NOT NULL");
```
throw org.apache.calcite.runtime.CalciteContextException: From line 1, column 18 to line 1, column 34: Illegal DATE literal '0000-01-01': not in format 'yyyy-MM-dd'
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1715575506
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
@mihaibudiu Good suggestion. I tested it in spark and it seems that spark is not aware of this error. Should we fix this error in calcite or keep this behavior? I am very happy to try it.
```
scala> val df = spark.sql("SELECT date_add('0001-01-01', -2)")
df: org.apache.spark.sql.DataFrame = [date_add(0001-01-01, -2): date]
scala> df.show()
+------------------------+
|date_add(0001-01-01, -2)|
+------------------------+
| 0000-12-30|
+------------------------+
scala> val df = spark.sql("SELECT date_add('0000-01-01', -2)")
df: org.apache.spark.sql.DataFrame = [date_add(0000-01-01, -2): date]
scala> df.show()
+------------------------+
|date_add(0000-01-01, -2)|
+------------------------+
| -0001-12-30|
+------------------------+
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1715562592
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
how about a few tests around year 0 and the Gregorian calendar cutoff date?
For example, date_add('0001-01-01', -2). Notice that there is no year 0, so this probably should throw.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1715591217
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
on the contrary, year 0 is not legal, it never existed.
and people lived before Christ, so years BC are completely legal in many databases. whether they show up as negative numbers, that's a different question. Calcite doesn't support them, though
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1718448437
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
I have already raised a jira case in spark
https://issues.apache.org/jira/browse/SPARK-49247
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1718953093
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
I think that in my previous review I asked for a few more tests around boundary conditions, I don't see any here.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "julianhyde (via GitHub)" <gi...@apache.org>.
julianhyde commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1719147766
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
_Au contraire_. `0000-00-00` is a valid 8601 date.
See https://en.wikipedia.org/wiki/ISO_8601#Calendar_dates
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1719148917
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
Will the Calcite DATE datatype accept such a value?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "julianhyde (via GitHub)" <gi...@apache.org>.
julianhyde commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1719223113
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
@mihaibudiu, I don't recall. I just happened to reading about ISO-8601 so I thought I'd correct misinformation.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1722599804
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,63 @@ void testTimestampDiff(boolean coercionEnabled) {
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')",
Review Comment:
> If a test passes that shouldn't it means that there's somewhere another bug that is masking a problem.
agree.
I will fix it, the date function does validation when it handles 000/or 0000 as a parameter, but when 000/or 0000 is returned as a result, no such validation is done, I think this is the bug
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1732033984
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,63 @@ void testTimestampDiff(boolean coercionEnabled) {
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')",
Review Comment:
I mean, I don't know why I get a runtime error.
Sorry, I didn't express myself clearly.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1732033984
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,63 @@ void testTimestampDiff(boolean coercionEnabled) {
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')",
Review Comment:
I'm not sure why it doesn't throw an error at runtime, I've found that almost all date functions have this problem, but I don't know how to solve it
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai closed pull request #3915: [CALCITE-6527] Add DATE_ADD function (enabled in Spark library)
URL: https://github.com/apache/calcite/pull/3915
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1715575506
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
Good suggestion. I tested it in spark and it seems that spark is not aware of this error. Should we fix this error in calcite or keep this behavior?
```
scala> val df = spark.sql("SELECT date_add('0001-01-01', -2)")
df: org.apache.spark.sql.DataFrame = [date_add(0001-01-01, -2): date]
scala> df.show()
+------------------------+
|date_add(0001-01-01, -2)|
+------------------------+
| 0000-12-30|
+------------------------+
scala> val df = spark.sql("SELECT date_add('0000-01-01', -2)")
df: org.apache.spark.sql.DataFrame = [date_add(0000-01-01, -2): date]
scala> df.show()
+------------------------+
|date_add(0000-01-01, -2)|
+------------------------+
| -0001-12-30|
+------------------------+
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1715575506
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
Good suggestion. I tested it in spark and it seems that spark is not aware of this error. Should we fix this error in calcite or keep this behavior?
scala> val df = spark.sql("SELECT date_add('0001-01-01', -2)")
df: org.apache.spark.sql.DataFrame = [date_add(0001-01-01, -2): date]
```
scala> df.show()
+------------------------+
|date_add(0001-01-01, -2)|
+------------------------+
| 0000-12-30|
+------------------------+
scala> val df = spark.sql("SELECT date_add('0000-01-01', -2)")
df: org.apache.spark.sql.DataFrame = [date_add(0000-01-01, -2): date]
scala> df.show()
+------------------------+
|date_add(0000-01-01, -2)|
+------------------------+
| -0001-12-30|
+------------------------+
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1716234962
##########
site/_docs/reference.md:
##########
@@ -2772,6 +2772,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(start_date, num_days) | Returns the DATE that is *num_days* after *start_date*
Review Comment:
Thanks for the reminder, I'll fix it later.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1720867290
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
Sorry for the late reply.
After testing, the date_add function will format the date. For example, invalid dates such as `0000-01-01` will fail the date format.
For example:
```
f.checkScalar("date_add(date '0000-01-01', '-3')",
"0000-12-29",
"DATE NOT NULL");
throw org.apache.calcite.runtime.CalciteContextException: From line 1, column 18 to line 1, column 34: Illegal DATE literal '0000-01-01': not in format 'yyyy-MM-dd'
```
But there doesn't seem to be any date format for the output, but I think that's another question.
```
f.checkScalar("date_add(date '0001-01-01', '-3')",
"0000-12-29",
"DATE NOT NULL");
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-6527] Add DATE_ADD function (enabled in Spark library) [calcite]
Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3915:
URL: https://github.com/apache/calcite/pull/3915#discussion_r1720867290
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -13824,6 +13824,54 @@ void testTimestampDiff(boolean coercionEnabled) {
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.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
Review Comment:
@mihaibudiu Sorry for the late reply.
After testing, the date_add function will format the date. For example, invalid dates such as `0000-01-01` will fail the date format.
For example:
```
f.checkScalar("date_add(date '0000-01-01', '-3')",
"0000-12-29",
"DATE NOT NULL");
throw org.apache.calcite.runtime.CalciteContextException: From line 1, column 18 to line 1, column 34: Illegal DATE literal '0000-01-01': not in format 'yyyy-MM-dd'
date_add(date '000/-12-31', '2') not in format 'yyyy-MM-dd'
```
But there doesn't seem to be any date format for the output, but I think that's another question.
```
f.checkScalar("date_add(date '0001-01-01', '-3')",
"0000-12-29",
"DATE NOT NULL");
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org