[CALCITE-1866] JDBC adapter generates incorrect code when pushing FLOOR to MySQL (Kang Wang, Sergey Nuyanzin)
Fix format string. DateTime FLOOR to HOUR cause MySQL use '%H' rather than '%k'. (Kang Wang) Add test-cases for MySQL (FLOOR to HOUR, FLOOR to MINUTE, FLOOR to SECOND) (Sergey Nuyanzin) Close apache/calcite#745 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/bc269aab Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/bc269aab Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/bc269aab Branch: refs/heads/master Commit: bc269aab9e87715a250a4e28851d125b195815c7 Parents: cf3eca2 Author: snuyanzin <[email protected]> Authored: Fri Jun 29 16:22:23 2018 +0300 Committer: Julian Hyde <[email protected]> Committed: Sun Jul 8 22:41:09 2018 -0700 ---------------------------------------------------------------------- .../calcite/sql/dialect/MysqlSqlDialect.java | 6 ++-- .../rel/rel2sql/RelToSqlConverterTest.java | 31 ++++++++++++++++++-- 2 files changed, 32 insertions(+), 5 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/bc269aab/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java ---------------------------------------------------------------------- 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 bc05165..e4f6b7f 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 @@ -193,13 +193,13 @@ public class MysqlSqlDialect extends SqlDialect { format = "%Y-%m-%d"; break; case HOUR: - format = "%Y-%m-%d %k:00:00"; + format = "%Y-%m-%d %H:00:00"; break; case MINUTE: - format = "%Y-%m-%d %k:%i:00"; + format = "%Y-%m-%d %H:%i:00"; break; case SECOND: - format = "%Y-%m-%d %k:%i:%s"; + format = "%Y-%m-%d %H:%i:%s"; break; default: throw new AssertionError("MYSQL does not support FLOOR for time unit: " http://git-wip-us.apache.org/repos/asf/calcite/blob/bc269aab/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java ---------------------------------------------------------------------- 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 c5af61a..ec216a1 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 @@ -1286,6 +1286,33 @@ public class RelToSqlConverterTest { .ok(expected); } + @Test public void testFloorMysqlHour() { + String query = "SELECT floor(\"hire_date\" TO HOUR) FROM \"employee\""; + String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:00:00')\n" + + "FROM `foodmart`.`employee`"; + sql(query) + .withMysql() + .ok(expected); + } + + @Test public void testFloorMysqlMinute() { + String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\""; + String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:00')\n" + + "FROM `foodmart`.`employee`"; + sql(query) + .withMysql() + .ok(expected); + } + + @Test public void testFloorMysqlSecond() { + String query = "SELECT floor(\"hire_date\" TO SECOND) FROM \"employee\""; + String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:%s')\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>. */ @@ -1303,9 +1330,9 @@ public class RelToSqlConverterTest { + "FROM \"foodmart\".\"employee\"\n" + "GROUP BY DATE_TRUNC('MINUTE', \"hire_date\")"; final String expectedMysql = "SELECT" - + " DATE_FORMAT(`hire_date`, '%Y-%m-%d %k:%i:00')\n" + + " DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:00')\n" + "FROM `foodmart`.`employee`\n" - + "GROUP BY DATE_FORMAT(`hire_date`, '%Y-%m-%d %k:%i:00')"; + + "GROUP BY DATE_FORMAT(`hire_date`, '%Y-%m-%d %H:%i:00')"; sql(query) .withHsqldb() .ok(expected)
