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 2023/12/18 14:47:36 UTC

[PR] [CALCITE-6165] Support date_diff and date_add functions [calcite]

caicancai opened a new pull request, #3586:
URL: https://github.com/apache/calcite/pull/3586

   (no comment)


-- 
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-6165] Add DATE_SUB test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "chucheng92 (via GitHub)" <gi...@apache.org>.
chucheng92 commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1867156919

   The commit name "Add DATE_SUB test and DATE_DIFF" but you actually add DATE_ADD and DATE_DIFF. could you recheck 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-6165] Add date_sub test and date_diff test on SqlOperatorTest [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1864561154

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586) **Quality Gate passed**  
   The SonarCloud Quality Gate passed, but some issues were introduced.
   
   [1 New issue](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_coverage&view=list)  
   [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586)
   
   


-- 
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-6165] Add DATE_ADD test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1867374512

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586) **Quality Gate passed**  
   The SonarCloud Quality Gate passed, but some issues were introduced.
   
   [1 New issue](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_coverage&view=list)  
   [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586)
   
   


-- 
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-6165] Add date_sub test and date_diff test on SqlOperatorTest [calcite]

Posted by "chucheng92 (via GitHub)" <gi...@apache.org>.
chucheng92 commented on code in PR #3586:
URL: https://github.com/apache/calcite/pull/3586#discussion_r1432198009


##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -11920,6 +11957,24 @@ void testTimestampDiff(boolean coercionEnabled) {
     f.checkFails("datediff(^\"MONTH\"^, '2019-09-14',  '2019-09-15')",
         "(?s)Column 'MONTH' not found in any table",
         false);
+
+    final SqlOperatorFixture f0 = f.withLibrary(SqlLibrary.BIG_QUERY);
+    f0.checkScalar("date_diff(DATE '2010-07-07', DATE '2008-12-25', DAY)",
+        "559",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2010-07-14', DATE '2010-07-07', WEEK)",
+        "1",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2011-12-14', DATE '2011-07-14', MONTH)",
+        "5",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2011-10-14', DATE '2011-07-14', QUARTER)",
+        "1",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2021-07-14', DATE '2011-07-14', YEAR)",
+        "10",
+        "INTEGER NOT NULL");

Review Comment:
   you can reference other tests, use `checkFail` to check exception.



-- 
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-6165] Add DATE_SUB test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "chucheng92 (via GitHub)" <gi...@apache.org>.
chucheng92 commented on code in PR #3586:
URL: https://github.com/apache/calcite/pull/3586#discussion_r1433384145


##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -11859,6 +11859,42 @@ void testTimestampDiff(boolean coercionEnabled) {
     f.checkNull("time_sub(CAST(NULL AS TIME), interval 5 minute)");
   }
 
+  @Test void testDateAdd() {
+    final SqlOperatorFixture f0 = fixture()
+        .setFor(SqlLibraryOperators.DATE_ADD);
+    f0.checkFails("^date_add(date '2008-12-25', "
+            + "interval 5 day)^",
+        "No match found for function signature "
+            + "DATE_ADD\\(<DATE>, <INTERVAL_DAY_TIME>\\)", false);
+
+    final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
+    f.checkScalar("date_add(date '2016-02-22', interval 2 day)",
+        "2016-02-24",
+        "DATE NOT NULL");
+    f.checkScalar("date_add(date '2016-02-17', interval 1 week)",
+        "2016-02-24",
+        "DATE NOT NULL");
+    f.checkScalar("date_add(date '2016-02-10', interval 2 weeks)",
+        "2016-02-24",
+        "DATE NOT NULL");
+    f.checkScalar("date_add(date '2020-10-17', interval 0 week)",
+        "2020-10-17",
+        "DATE NOT NULL");
+    f.checkScalar("date_add(date '2016-11-24', interval 3 month)",
+        "2017-02-24",
+        "DATE NOT NULL");
+    f.checkScalar("date_add(date '2015-11-24', interval 1 quarter)",
+        "2016-02-24",
+        "DATE NOT NULL");
+    f.checkScalar("date_add(date '2015-08-24', interval 2 quarters)",
+        "2016-02-24",
+        "DATE NOT NULL");
+    f.checkScalar("date_add(date '2011-02-24', interval 5 year)",
+        "2016-02-24",
+        "DATE NOT NULL");
+    f.checkNull("date_add(CAST(NULL AS DATE), interval 5 day)");

Review Comment:
   could u add a case that next arg is NULL?



##########
core/src/main/java/org/apache/calcite/sql/SqlKind.java:
##########
@@ -443,6 +443,9 @@ public enum SqlKind {
   LEAST,
 
   /** {@code DATE_DIFF} function (BigQuery Semantics). */
+  DATE_DIFF,

Review Comment:
   why add a new SqlKind but never use?



##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -11948,6 +11984,25 @@ void testTimestampDiff(boolean coercionEnabled) {
     f.checkFails("datediff(^\"MONTH\"^, '2019-09-14',  '2019-09-15')",
         "(?s)Column 'MONTH' not found in any table",
         false);
+    final SqlOperatorFixture f0 = f.withLibrary(SqlLibrary.BIG_QUERY);
+    f0.checkScalar("date_diff(DATE '2010-07-07', DATE '2008-12-25', DAY)",
+        "559",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2010-07-14', DATE '2010-07-07', WEEK)",
+        "1",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2011-12-14', DATE '2011-07-14', MONTH)",
+        "5",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2011-10-14', DATE '2011-07-14', QUARTER)",
+        "1",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2021-07-14', DATE '2011-07-14', YEAR)",
+        "10",
+        "INTEGER NOT NULL");
+    f0.checkNull("date_diff(CAST(NULL AS DATE), CAST(NULL AS DATE), DAY)");
+    f0.checkNull("date_diff(CAST(NULL AS DATE), CAST(NULL AS DATE), DAY)");

Review Comment:
   duplicated lines



-- 
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-6165] Add date_sub test and date_diff test on SqlOperatorTest [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3586:
URL: https://github.com/apache/calcite/pull/3586#discussion_r1432731370


##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -11920,6 +11957,24 @@ void testTimestampDiff(boolean coercionEnabled) {
     f.checkFails("datediff(^\"MONTH\"^, '2019-09-14',  '2019-09-15')",
         "(?s)Column 'MONTH' not found in any table",
         false);
+
+    final SqlOperatorFixture f0 = f.withLibrary(SqlLibrary.BIG_QUERY);
+    f0.checkScalar("date_diff(DATE '2010-07-07', DATE '2008-12-25', DAY)",
+        "559",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2010-07-14', DATE '2010-07-07', WEEK)",
+        "1",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2011-12-14', DATE '2011-07-14', MONTH)",
+        "5",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2011-10-14', DATE '2011-07-14', QUARTER)",
+        "1",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2021-07-14', DATE '2011-07-14', YEAR)",
+        "10",
+        "INTEGER NOT NULL");

Review Comment:
   But this exception is not included in checkfail.
   ![2023-12-20 21-42-21屏幕截图](https://github.com/apache/calcite/assets/77189278/672e0f70-525b-4a7a-a6da-5f8485a325b0)
   



-- 
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-6165] Add DATE_SUB test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "chucheng92 (via GitHub)" <gi...@apache.org>.
chucheng92 commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1865396363

   could you also check reference.md?


-- 
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-6165] Add date_sub test and date_diff test on SqlOperatorTest [calcite]

Posted by "chucheng92 (via GitHub)" <gi...@apache.org>.
chucheng92 commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1863778061

   Can u change your PR name to `Add DATE_SUB test and DATE_DIFF test on SqlOperatorTest`, It is more recommended to use uppercase letters for function names.


-- 
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-6165] Add DATE_ADD test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3586:
URL: https://github.com/apache/calcite/pull/3586#discussion_r1434803576


##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -11948,6 +11984,25 @@ void testTimestampDiff(boolean coercionEnabled) {
     f.checkFails("datediff(^\"MONTH\"^, '2019-09-14',  '2019-09-15')",
         "(?s)Column 'MONTH' not found in any table",
         false);
+    final SqlOperatorFixture f0 = f.withLibrary(SqlLibrary.BIG_QUERY);
+    f0.checkScalar("date_diff(DATE '2010-07-07', DATE '2008-12-25', DAY)",
+        "559",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2010-07-14', DATE '2010-07-07', WEEK)",
+        "1",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2011-12-14', DATE '2011-07-14', MONTH)",
+        "5",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2011-10-14', DATE '2011-07-14', QUARTER)",
+        "1",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2021-07-14', DATE '2011-07-14', YEAR)",
+        "10",
+        "INTEGER NOT NULL");
+    f0.checkNull("date_diff(CAST(NULL AS DATE), CAST(NULL AS DATE), DAY)");
+    f0.checkNull("date_diff(CAST(NULL AS DATE), CAST(NULL AS DATE), DAY)");

Review Comment:
   outdated



-- 
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-6165] Add DATE_ADD test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "chucheng92 (via GitHub)" <gi...@apache.org>.
chucheng92 commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1867518570

   @caicancai thanks for your PR, merging ...


-- 
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-6165] Add date_sub test and date_diff test on SqlOperatorTest [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1862897559

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586) **Quality Gate passed**  
   The SonarCloud Quality Gate passed, but some issues were introduced.
   
   [1 New issue](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_coverage&view=list)  
   [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586)
   
   


-- 
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-6165] Add date_sub test and date_diff test on SqlOperatorTest [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1863778855

   > Can u change your PR name to `Add DATE_SUB test and DATE_DIFF test on SqlOperatorTest`? It is more recommended to use uppercase letters for function names.
   
   Ok,thank you


-- 
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-6165] Support date_diff and date_add functions [calcite]

Posted by "chucheng92 (via GitHub)" <gi...@apache.org>.
chucheng92 commented on code in PR #3586:
URL: https://github.com/apache/calcite/pull/3586#discussion_r1430864427


##########
testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java:
##########
@@ -1387,30 +1387,30 @@ protected static SortedSet<String> keywords(@Nullable String dialect) {
 
   @Test void testRowValueExpression() {
     final String expected0 = "INSERT INTO \"EMPS\"\n"
-            + "VALUES (ROW(1, 'Fred')),\n"

Review Comment:
   I don't think so. you can rebase the latest main and have a try.



-- 
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-6165] Add DATE_SUB test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1866332917

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586) **Quality Gate passed**  
   The SonarCloud Quality Gate passed, but some issues were introduced.
   
   [1 New issue](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_coverage&view=list)  
   [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586)
   
   


-- 
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-6165] Support date_diff and date_add functions [calcite]

Posted by "chucheng92 (via GitHub)" <gi...@apache.org>.
chucheng92 commented on code in PR #3586:
URL: https://github.com/apache/calcite/pull/3586#discussion_r1430862064


##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -11920,6 +11957,24 @@ void testTimestampDiff(boolean coercionEnabled) {
     f.checkFails("datediff(^\"MONTH\"^, '2019-09-14',  '2019-09-15')",
         "(?s)Column 'MONTH' not found in any table",
         false);
+
+    final SqlOperatorFixture f0 = f.withLibrary(SqlLibrary.BIG_QUERY);
+    f0.checkScalar("date_diff(DATE '2010-07-07', DATE '2008-12-25', DAY)",
+        "559",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2010-07-14', DATE '2010-07-07', WEEK)",
+        "1",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2011-12-14', DATE '2011-07-14', MONTH)",
+        "5",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2011-10-14', DATE '2011-07-14', QUARTER)",
+        "1",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2021-07-14', DATE '2011-07-14', YEAR)",
+        "10",
+        "INTEGER NOT NULL");

Review Comment:
   can you add some special case, such as illegal date or NULL and NULL etc.



##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -11920,6 +11957,24 @@ void testTimestampDiff(boolean coercionEnabled) {
     f.checkFails("datediff(^\"MONTH\"^, '2019-09-14',  '2019-09-15')",
         "(?s)Column 'MONTH' not found in any table",
         false);
+
+    final SqlOperatorFixture f0 = f.withLibrary(SqlLibrary.BIG_QUERY);
+    f0.checkScalar("date_diff(DATE '2010-07-07', DATE '2008-12-25', DAY)",
+        "559",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2010-07-14', DATE '2010-07-07', WEEK)",
+        "1",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2011-12-14', DATE '2011-07-14', MONTH)",
+        "5",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2011-10-14', DATE '2011-07-14', QUARTER)",
+        "1",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2021-07-14', DATE '2011-07-14', YEAR)",
+        "10",
+        "INTEGER NOT NULL");

Review Comment:
   can you add some special cases, such as illegal date or NULL and NULL etc.



-- 
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-6165] Add DATE_ADD test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1867426388

   Thank you for your patient 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-6165] Add DATE_ADD test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "chucheng92 (via GitHub)" <gi...@apache.org>.
chucheng92 merged PR #3586:
URL: https://github.com/apache/calcite/pull/3586


-- 
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-6165] Support date_diff and date_add functions [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai closed pull request #3586: [CALCITE-6165] Support date_diff and date_add functions
URL: https://github.com/apache/calcite/pull/3586


-- 
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-6165] Support date_diff and date_add functions [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1860855074

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586) **Quality Gate passed**  
   The SonarCloud Quality Gate passed, but some issues were introduced.
   
   [1 New issue](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_coverage&view=list)  
   [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586)
   
   


-- 
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-6165] Add date_sub test and date_diff test on SqlOperatorTest [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1862857142

   > I didn’t see implementation-related logic in your PR, so I can understand that this PR just adds test cases of `date_diff` and `date_add` to SqlOperatorTest, right? In this case, it is recommended to modify the titles of jira and PR.
   
   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-6165] Add DATE_SUB test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1866441880

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586) **Quality Gate passed**  
   The SonarCloud Quality Gate passed, but some issues were introduced.
   
   [1 New issue](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_coverage&view=list)  
   [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586)
   
   


-- 
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-6165] Add DATE_SUB test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1865358655

   cc @chucheng92 


-- 
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-6165] Add DATE_SUB test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1866326301

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586) **Quality Gate passed**  
   The SonarCloud Quality Gate passed, but some issues were introduced.
   
   [1 New issue](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_coverage&view=list)  
   [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586)
   
   


-- 
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-6165] Support date_diff and date_add functions [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1860828123

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586) **Quality Gate passed**  
   The SonarCloud Quality Gate passed, but some issues were introduced.
   
   [1 New issue](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3586&resolved=false&inNewCodePeriod=true)  
   [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_coverage&view=list)  
   [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3586&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3586)
   
   


-- 
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-6165] Support date_diff and date_add functions [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai closed pull request #3586: [CALCITE-6165] Support date_diff and date_add functions
URL: https://github.com/apache/calcite/pull/3586


-- 
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-6165] Support date_diff and date_add functions [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3586:
URL: https://github.com/apache/calcite/pull/3586#discussion_r1430861403


##########
testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java:
##########
@@ -1387,30 +1387,30 @@ protected static SortedSet<String> keywords(@Nullable String dialect) {
 
   @Test void testRowValueExpression() {
     final String expected0 = "INSERT INTO \"EMPS\"\n"
-            + "VALUES (ROW(1, 'Fred')),\n"

Review Comment:
   This seems to be a github display issue, I did not fix this part. I have also tried to modify it but it still shows, I will try again 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-6165] Add DATE_SUB test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1865397151

   > could you also check reference.md?
   
   reference.md has the corresponding function description
   
   


-- 
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-6165] Support date_diff and date_add functions [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3586:
URL: https://github.com/apache/calcite/pull/3586#discussion_r1431441871


##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -11920,6 +11957,24 @@ void testTimestampDiff(boolean coercionEnabled) {
     f.checkFails("datediff(^\"MONTH\"^, '2019-09-14',  '2019-09-15')",
         "(?s)Column 'MONTH' not found in any table",
         false);
+
+    final SqlOperatorFixture f0 = f.withLibrary(SqlLibrary.BIG_QUERY);
+    f0.checkScalar("date_diff(DATE '2010-07-07', DATE '2008-12-25', DAY)",
+        "559",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2010-07-14', DATE '2010-07-07', WEEK)",
+        "1",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2011-12-14', DATE '2011-07-14', MONTH)",
+        "5",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2011-10-14', DATE '2011-07-14', QUARTER)",
+        "1",
+        "INTEGER NOT NULL");
+    f0.checkScalar("date_diff(DATE '2021-07-14', DATE '2011-07-14', YEAR)",
+        "10",
+        "INTEGER NOT NULL");

Review Comment:
   illegal date will throw exception, so I added NULL test example



-- 
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-6165] Add DATE_ADD test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on code in PR #3586:
URL: https://github.com/apache/calcite/pull/3586#discussion_r1434801168


##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -11859,6 +11859,42 @@ void testTimestampDiff(boolean coercionEnabled) {
     f.checkNull("time_sub(CAST(NULL AS TIME), interval 5 minute)");
   }
 
+  @Test void testDateAdd() {
+    final SqlOperatorFixture f0 = fixture()
+        .setFor(SqlLibraryOperators.DATE_ADD);
+    f0.checkFails("^date_add(date '2008-12-25', "
+            + "interval 5 day)^",
+        "No match found for function signature "
+            + "DATE_ADD\\(<DATE>, <INTERVAL_DAY_TIME>\\)", false);
+
+    final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
+    f.checkScalar("date_add(date '2016-02-22', interval 2 day)",
+        "2016-02-24",
+        "DATE NOT NULL");
+    f.checkScalar("date_add(date '2016-02-17', interval 1 week)",
+        "2016-02-24",
+        "DATE NOT NULL");
+    f.checkScalar("date_add(date '2016-02-10', interval 2 weeks)",
+        "2016-02-24",
+        "DATE NOT NULL");
+    f.checkScalar("date_add(date '2020-10-17', interval 0 week)",
+        "2020-10-17",
+        "DATE NOT NULL");
+    f.checkScalar("date_add(date '2016-11-24', interval 3 month)",
+        "2017-02-24",
+        "DATE NOT NULL");
+    f.checkScalar("date_add(date '2015-11-24', interval 1 quarter)",
+        "2016-02-24",
+        "DATE NOT NULL");
+    f.checkScalar("date_add(date '2015-08-24', interval 2 quarters)",
+        "2016-02-24",
+        "DATE NOT NULL");
+    f.checkScalar("date_add(date '2011-02-24', interval 5 year)",
+        "2016-02-24",
+        "DATE NOT NULL");
+    f.checkNull("date_add(CAST(NULL AS DATE), interval 5 day)");

Review Comment:
   Next pr fix



-- 
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-6165] Support date_diff and date_add functions [calcite]

Posted by "chucheng92 (via GitHub)" <gi...@apache.org>.
chucheng92 commented on code in PR #3586:
URL: https://github.com/apache/calcite/pull/3586#discussion_r1430860714


##########
testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java:
##########
@@ -1387,30 +1387,30 @@ protected static SortedSet<String> keywords(@Nullable String dialect) {
 
   @Test void testRowValueExpression() {
     final String expected0 = "INSERT INTO \"EMPS\"\n"
-            + "VALUES (ROW(1, 'Fred')),\n"

Review Comment:
   why change these lines? This is not part of your PR. If the previous format is not right and you want to improve it, it may be better to submit a new 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-6165] Add DATE_SUB test and DATE_DIFF test on SqlOperatorTest [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1866257065

   @chucheng92 Regarding the failure information related to date_add(date '2011-02-24', CAST(NULL AS interval)), I will add it in the next pr. Because date_sub does not have a corresponding checkfail, I will check where SqlOperatorTest can add related tests. If If there are more, I will open a jira for discussion.


-- 
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-6165] Support date_diff and date_add functions [calcite]

Posted by "caicancai (via GitHub)" <gi...@apache.org>.
caicancai commented on PR #3586:
URL: https://github.com/apache/calcite/pull/3586#issuecomment-1860833160

   cc @mihaibudiu @JiajunBernoulli 


-- 
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