(calcite) branch main updated: [CALCITE-6949] ClickHouse not support floor date to SECOND/MILLISECOND/MICROSECOND/NANOSECOND

[mbudiu]

This is an automated email from the ASF dual-hosted git repository.

mbudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git


The following commit(s) were added to refs/heads/main by this push:
     new e9c28c64f3 [CALCITE-6949] ClickHouse not support floor date to 
SECOND/MILLISECOND/MICROSECOND/NANOSECOND
e9c28c64f3 is described below

commit e9c28c64f32ef1f4dfa394325b9e141a026adb1c
Author: Yu Xu <1206332...@qq.com>
AuthorDate: Wed Apr 9 10:58:28 2025 +0800

    [CALCITE-6949] ClickHouse not support floor date to 
SECOND/MILLISECOND/MICROSECOND/NANOSECOND
---
 .../calcite/sql/dialect/ClickHouseSqlDialect.java  | 12 +++++
 .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 55 ++++++++++++++++++++++
 2 files changed, 67 insertions(+)

diff --git 
a/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java 
b/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
index dcba521a57..96bff7ad05 100644
--- 
a/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
+++ 
b/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
@@ -313,6 +313,18 @@ private static void unparseFloor(SqlWriter writer, SqlCall 
call) {
     case MINUTE:
       funName = "toStartOfMinute";
       break;
+    case SECOND:
+      funName = "toStartOfSecond";
+      break;
+    case MILLISECOND:
+      funName = "toStartOfMillisecond";
+      break;
+    case MICROSECOND:
+      funName = "toStartOfMicrosecond";
+      break;
+    case NANOSECOND:
+      funName = "toStartOfNanosecond";
+      break;
     default:
       throw new RuntimeException("ClickHouse does not support FLOOR for time 
unit: "
           + unit);
diff --git 
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java 
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 188806ceb9..81b971009b 100644
--- 
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++ 
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -5355,6 +5355,61 @@ private void checkLiteral2(String expression, String 
expected) {
         .withMysql().ok(expectedMysql);
   }
 
+  /** Test case for
+   * <a 
href="https://issues.apache.org/jira/browse/CALCITE-6949";>[CALCITE-6949]
+   * ClickHouse not support floor date to 
SECOND/MILLISECOND/MICROSECOND/NANOSECOND</a>. */
+  @Test void testFloorClickHouse() {
+    String query = "SELECT floor(\"hire_date\" TO YEAR) FROM \"employee\"";
+    String expectedClickHouse = "SELECT toStartOfYear(`hire_date`)\n"
+        + "FROM `foodmart`.`employee`";
+    sql(query).withClickHouse().ok(expectedClickHouse);
+
+    String query1 = "SELECT floor(\"hire_date\" TO MONTH) FROM \"employee\"";
+    String expectedClickHouse1 = "SELECT toStartOfMonth(`hire_date`)\n"
+        + "FROM `foodmart`.`employee`";
+    sql(query1).withClickHouse().ok(expectedClickHouse1);
+
+    String query2 = "SELECT floor(\"hire_date\" TO WEEK) FROM \"employee\"";
+    String expectedClickHouse2 = "SELECT toMonday(`hire_date`)\n"
+        + "FROM `foodmart`.`employee`";
+    sql(query2).withClickHouse().ok(expectedClickHouse2);
+
+    String query3 = "SELECT floor(\"hire_date\" TO DAY) FROM \"employee\"";
+    String expectedClickHouse3 = "SELECT toDate(`hire_date`)\n"
+        + "FROM `foodmart`.`employee`";
+    sql(query3).withClickHouse().ok(expectedClickHouse3);
+
+    String query4 = "SELECT floor(\"hire_date\" TO HOUR) FROM \"employee\"";
+    String expectedClickHouse4 = "SELECT toStartOfHour(`hire_date`)\n"
+        + "FROM `foodmart`.`employee`";
+    sql(query4).withClickHouse().ok(expectedClickHouse4);
+
+    String query5 = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\"";
+    String expectedClickHouse5 = "SELECT toStartOfMinute(`hire_date`)\n"
+        + "FROM `foodmart`.`employee`";
+    sql(query5).withClickHouse().ok(expectedClickHouse5);
+
+    String query6 = "SELECT floor(\"hire_date\" TO SECOND) FROM \"employee\"";
+    String expectedClickHouse6 = "SELECT toStartOfSecond(`hire_date`)\n"
+        + "FROM `foodmart`.`employee`";
+    sql(query6).withClickHouse().ok(expectedClickHouse6);
+
+    String query7 = "SELECT floor(\"hire_date\" TO MILLISECOND) FROM 
\"employee\"";
+    String expectedClickHouse7 = "SELECT toStartOfMillisecond(`hire_date`)\n"
+        + "FROM `foodmart`.`employee`";
+    sql(query7).withClickHouse().ok(expectedClickHouse7);
+
+    String query8 = "SELECT floor(\"hire_date\" TO MICROSECOND) FROM 
\"employee\"";
+    String expectedClickHouse8 = "SELECT toStartOfMicrosecond(`hire_date`)\n"
+        + "FROM `foodmart`.`employee`";
+    sql(query8).withClickHouse().ok(expectedClickHouse8);
+
+    String query9 = "SELECT floor(\"hire_date\" TO NANOSECOND) FROM 
\"employee\"";
+    String expectedClickHouse9 = "SELECT toStartOfNanosecond(`hire_date`)\n"
+        + "FROM `foodmart`.`employee`";
+    sql(query9).withClickHouse().ok(expectedClickHouse9);
+  }
+
   @Test void testUnparseSqlIntervalQualifierDb2() {
     String queryDatePlus = "select  * from \"employee\" where  \"hire_date\" + 
"
         + "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' ";