Following [CALCITE-1798] implement date-time FLOOR for more JDBC dialects (Chris Baynes)
Add support for MySQL and Microsoft SQL Server; also fix a bug in the PostgreSQL and Oracle implementations. Close apache/calcite#458 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/946b24f4 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/946b24f4 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/946b24f4 Branch: refs/heads/master Commit: 946b24f4dc163dee018de90b2dcf7082bb892cfe Parents: 205af81 Author: Chris Baynes <[email protected]> Authored: Mon May 29 10:45:50 2017 +0200 Committer: Julian Hyde <[email protected]> Committed: Wed May 31 08:17:28 2017 -0700 ---------------------------------------------------------------------- .../calcite/sql/fun/SqlFloorFunction.java | 171 +++++++++++++++++-- .../rel/rel2sql/RelToSqlConverterTest.java | 53 ++++++ 2 files changed, 211 insertions(+), 13 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/946b24f4/core/src/main/java/org/apache/calcite/sql/fun/SqlFloorFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlFloorFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlFloorFunction.java index 9e0e5ba..02b4abe 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlFloorFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlFloorFunction.java @@ -23,6 +23,7 @@ import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlLiteral; import org.apache.calcite.sql.SqlOperatorBinding; import org.apache.calcite.sql.SqlWriter; +import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.sql.validate.SqlMonotonicity; @@ -78,28 +79,51 @@ public class SqlFloorFunction extends SqlMonotonicUnaryFunction { } switch (writer.getDialect().getDatabaseProduct()) { + case UNKNOWN: + case CALCITE: + unparseDatetimeDefault(writer, call); + return; + } + + final SqlLiteral timeUnitNode = call.operand(1); + final TimeUnitRange timeUnit = timeUnitNode.getValueAs(TimeUnitRange.class); + + switch (writer.getDialect().getDatabaseProduct()) { case ORACLE: + replaceTimeUnitOperand(call, timeUnit.name(), timeUnitNode.getParserPosition()); unparseDatetimeFunction(writer, call, "TRUNC", true); break; case HSQLDB: - // translate timeUnit literal - SqlLiteral node = call.operand(1); - String translatedLit = - convertToHsqlDb((TimeUnitRange) node.getValue()); - SqlLiteral newNode = SqlLiteral.createCharString( - translatedLit, null, node.getParserPosition()); - call.setOperand(1, newNode); - + String translatedLit = convertToHsqlDb(timeUnit); + replaceTimeUnitOperand(call, translatedLit, timeUnitNode.getParserPosition()); unparseDatetimeFunction(writer, call, "TRUNC", true); break; case POSTGRESQL: + replaceTimeUnitOperand(call, timeUnit.name(), timeUnitNode.getParserPosition()); unparseDatetimeFunction(writer, call, "DATE_TRUNC", false); break; + case MSSQL: + unparseDatetimeMssql(writer, call); + break; + case MYSQL: + unparseDatetimeMysql(writer, call); + break; default: unparseDatetimeDefault(writer, call); } } + private void replaceTimeUnitOperand(SqlCall call, String literal, SqlParserPos pos) { + SqlLiteral literalNode = SqlLiteral.createCharString(literal, null, pos); + call.setOperand(1, literalNode); + } + + /** + * Default datetime unparse method if the specific dialect was not matched. + * + * @param writer SqlWriter + * @param call SqlCall + */ private void unparseDatetimeDefault(SqlWriter writer, SqlCall call) { final SqlWriter.Frame frame = writer.startFunCall(getName()); call.operand(0).unparse(writer, 0, 100); @@ -108,18 +132,84 @@ public class SqlFloorFunction extends SqlMonotonicUnaryFunction { writer.endFunCall(frame); } + /** + * Most dialects that natively support datetime floor will use this. + * In those cases the call will look like TRUNC(datetime, 'year'). + * + * @param writer SqlWriter + * @param call SqlCall + * @param funName Name of the sql function to call + * @param datetimeFirst Specify the order of the datetime & timeUnit + * arguments + */ private void unparseDatetimeFunction(SqlWriter writer, SqlCall call, String funName, Boolean datetimeFirst) { final SqlWriter.Frame frame = writer.startFunCall(funName); - Integer firstOp = datetimeFirst ? 0 : 1; - Integer secondOp = datetimeFirst ? 1 : 0; - - call.operand(firstOp).unparse(writer, 0, 0); + int firstOpIndex = datetimeFirst ? 0 : 1; + int secondOpIndex = datetimeFirst ? 1 : 0; + call.operand(firstOpIndex).unparse(writer, 0, 0); writer.sep(",", true); - call.operand(secondOp).unparse(writer, 0, 0); + call.operand(secondOpIndex).unparse(writer, 0, 0); writer.endFunCall(frame); } + /** + * Unparse datetime floor for MS SQL. There is no TRUNC function, so simulate this + * using calls to CONVERT. + * + * @param writer SqlWriter + * @param call SqlCall + */ + private void unparseDatetimeMssql(SqlWriter writer, SqlCall call) { + SqlLiteral node = call.operand(1); + TimeUnitRange unit = (TimeUnitRange) node.getValue(); + + switch(unit) { + case YEAR: + unparseMssql(writer, call, 4, "-01-01"); + break; + case MONTH: + unparseMssql(writer, call, 7, "-01"); + break; + case WEEK: + writer.print("CONVERT(DATETIME, CONVERT(VARCHAR(10), " + + "DATEADD(day, - (6 + DATEPART(weekday, "); + call.operand(0).unparse(writer, 0, 0); + writer.print(")) % 7, "); + call.operand(0).unparse(writer, 0, 0); + writer.print("), 126))"); + break; + case DAY: + unparseMssql(writer, call, 10, ""); + break; + case HOUR: + unparseMssql(writer, call, 13, ":00:00"); + break; + case MINUTE: + unparseMssql(writer, call, 16, ":00"); + break; + case SECOND: + unparseMssql(writer, call, 19, ":00"); + break; + default: + throw new AssertionError("MSSQL does not support FLOOR for time unit: " + + unit); + } + } + + private void unparseMssql(SqlWriter writer, SqlCall call, Integer charLen, String offset) { + writer.print("CONVERT"); + SqlWriter.Frame frame = writer.startList("(", ")"); + writer.print("DATETIME, CONVERT(VARCHAR(" + charLen.toString() + "), "); + call.operand(0).unparse(writer, 0, 0); + writer.print(", 126)"); + + if (offset.length() > 0) { + writer.print("+'" + offset + "'"); + } + writer.endList(frame); + } + private static String convertToHsqlDb(TimeUnitRange unit) { switch (unit) { case YEAR: @@ -141,6 +231,61 @@ public class SqlFloorFunction extends SqlMonotonicUnaryFunction { + unit); } } + + /** + * Unparse datetime floor for MySQL. There is no TRUNC function, so simulate this + * using calls to DATE_FORMAT. + * + * @param writer SqlWriter + * @param call SqlCall + */ + private void unparseDatetimeMysql(SqlWriter writer, SqlCall call) { + SqlLiteral node = call.operand(1); + TimeUnitRange unit = (TimeUnitRange) node.getValue(); + + if (unit == TimeUnitRange.WEEK) { + writer.print("STR_TO_DATE"); + SqlWriter.Frame frame = writer.startList("(", ")"); + + writer.print("DATE_FORMAT("); + call.operand(0).unparse(writer, 0, 0); + writer.print(", '%x%v-1'), '%x%v-%w'"); + writer.endList(frame); + return; + } + + String format; + switch(unit) { + case YEAR: + format = "%Y-01-01"; + break; + case MONTH: + format = "%Y-%m-01"; + break; + case DAY: + format = "%Y-%m-%d"; + break; + case HOUR: + format = "%Y-%m-%d %k:00:00"; + break; + case MINUTE: + format = "%Y-%m-%d %k:%i:00"; + break; + case SECOND: + format = "%Y-%m-%d %k:%i:%s"; + break; + default: + throw new AssertionError("MYSQL does not support FLOOR for time unit: " + + unit); + } + + 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); + } } // End SqlFloorFunction.java http://git-wip-us.apache.org/repos/asf/calcite/blob/946b24f4/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 4e4db08..80957c0 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 @@ -690,6 +690,59 @@ public class RelToSqlConverterTest { .ok(expected); } + @Test public void testFloorPostgres() { + String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\""; + String expected = "SELECT DATE_TRUNC('MINUTE', \"hire_date\")\nFROM \"foodmart\".\"employee\""; + sql(query) + .dialect(DatabaseProduct.POSTGRESQL.getDialect()) + .ok(expected); + } + + @Test public void testFloorOracle() { + String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\""; + String expected = "SELECT TRUNC(\"hire_date\", 'MINUTE')\nFROM \"foodmart\".\"employee\""; + sql(query) + .dialect(DatabaseProduct.ORACLE.getDialect()) + .ok(expected); + } + + @Test public void testFloorMssqlWeek() { + String query = "SELECT floor(\"hire_date\" TO WEEK) FROM \"employee\""; + String expected = "SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), " + + "DATEADD(day, - (6 + DATEPART(weekday, [hire_date] )) % 7, [hire_date] ), 126))\n" + + "FROM [foodmart].[employee]"; + sql(query) + .dialect(DatabaseProduct.MSSQL.getDialect()) + .ok(expected); + } + + @Test public void testFloorMssqlMonth() { + String query = "SELECT floor(\"hire_date\" TO MONTH) FROM \"employee\""; + String expected = "SELECT CONVERT(DATETIME, CONVERT(VARCHAR(7), [hire_date] , 126)+'-01')\n" + + "FROM [foodmart].[employee]"; + sql(query) + .dialect(DatabaseProduct.MSSQL.getDialect()) + .ok(expected); + } + + @Test public void testFloorMysqlMonth() { + String query = "SELECT floor(\"hire_date\" TO MONTH) FROM \"employee\""; + String expected = "SELECT DATE_FORMAT(`hire_date`, '%Y-%m-01')\n" + + "FROM `foodmart`.`employee`"; + sql(query) + .dialect(DatabaseProduct.MYSQL.getDialect()) + .ok(expected); + } + + @Test public void testFloorMysqlWeek() { + String query = "SELECT floor(\"hire_date\" TO WEEK) FROM \"employee\""; + String expected = "SELECT STR_TO_DATE(DATE_FORMAT(`hire_date` , '%x%v-1'), '%x%v-%w')\n" + + "FROM `foodmart`.`employee`"; + sql(query) + .dialect(DatabaseProduct.MYSQL.getDialect()) + .ok(expected); + } + @Test public void testMatchRecognizePatternExpression() { String sql = "select *\n" + " from \"product\" match_recognize\n"
