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 704792c593 [CALCITE-6958] JDBC adapter for MySQL not support floor
date to MILLISECOND/MICROSECOND
704792c593 is described below
commit 704792c59326c29299bb88a595203f33d078c618
Author: Yu Xu <[email protected]>
AuthorDate: Tue Apr 15 18:02:52 2025 +0800
[CALCITE-6958] JDBC adapter for MySQL not support floor date to
MILLISECOND/MICROSECOND
---
.../calcite/sql/dialect/MysqlSqlDialect.java | 25 ++++++++++++++++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 19 ++++++++++++++++
2 files changed, 44 insertions(+)
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
index b8913ecb2e..e76b13c84d 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
@@ -54,6 +54,7 @@
import org.checkerframework.checker.nullness.qual.Nullable;
import java.util.List;
+import java.util.Locale;
/**
* A <code>SqlDialect</code> implementation for the MySQL database.
@@ -347,6 +348,8 @@ private static void unparseFloor(SqlWriter writer, SqlCall
call) {
}
String format;
+ boolean needSubStr = false;
+ int substringLength = 0;
switch (unit) {
case YEAR:
format = "%Y-01-01";
@@ -366,17 +369,39 @@ private static void unparseFloor(SqlWriter writer,
SqlCall call) {
case SECOND:
format = "%Y-%m-%d %H:%i:%s";
break;
+ case MILLISECOND:
+ needSubStr = true;
+ format = "%Y-%m-%d %H:%i:%s.%f";
+ substringLength = 23;
+ break;
+ case MICROSECOND:
+ needSubStr = true;
+ format = "%Y-%m-%d %H:%i:%s.%f";
+ substringLength = 26;
+ break;
default:
throw new AssertionError("MYSQL does not support FLOOR for time unit: "
+ unit);
}
+ SqlWriter.Frame substringFrame = null;
+ if (needSubStr) {
+ writer.print("SUBSTRING");
+ substringFrame = writer.startList("(", ")");
+ }
+
writer.print("DATE_FORMAT");
SqlWriter.Frame frame = writer.startList("(", ")");
call.operand(0).unparse(writer, 0, 0);
writer.sep(",", true);
writer.print("'" + format + "'");
writer.endList(frame);
+
+ if (needSubStr) {
+ String substringFormat = String.format(Locale.ROOT, ", 1, %s",
substringLength);
+ writer.print(substringFormat);
+ writer.endList(substringFrame);
+ }
}
@Override public boolean supportsAggregateFunctionFilter() {
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 2bafbe156c..e0818367d7 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
@@ -5590,6 +5590,25 @@ private void checkLiteral2(String expression, String
expected) {
.withMysql().ok(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6958";>[CALCITE-6958]
+ * JDBC adapter for MySQL not support floor date to
MILLISECOND/MICROSECOND</a>. */
+ @Test void testFloorMysqlMillisecond() {
+ String query = "SELECT floor(\"hire_date\" TO MILLISECOND) FROM
\"employee\"";
+ String expected = "SELECT SUBSTRING(DATE_FORMAT(`hire_date`, '%Y-%m-%d
%H:%i:%s.%f') , 1, 23)\n"
+ + "FROM `foodmart`.`employee`";
+ sql(query)
+ .withMysql().ok(expected);
+ }
+
+ @Test void testFloorMysqlMicrosecond() {
+ String query = "SELECT floor(\"hire_date\" TO MICROSECOND) FROM
\"employee\"";
+ String expected = "SELECT SUBSTRING(DATE_FORMAT(`hire_date`, '%Y-%m-%d
%H:%i:%s.%f') , 1, 26)\n"
+ + "FROM `foodmart`.`employee`";
+ sql(query)
+ .withMysql().ok(expected);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-1826";>[CALCITE-1826]
* JDBC dialect-specific FLOOR fails when in GROUP BY</a>. */
