Repository: calcite Updated Branches: refs/heads/master b6175f807 -> f8ab9078b
[CALCITE-1798] Generate dialect-specific SQL for FLOOR operator (Chris Baynes) Close apache/calcite#453 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/f8ab9078 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/f8ab9078 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/f8ab9078 Branch: refs/heads/master Commit: f8ab9078bae8c519ce4c86d741490779ea899a14 Parents: b6175f8 Author: Chris Baynes <[email protected]> Authored: Sun May 21 15:59:13 2017 +0200 Committer: Julian Hyde <[email protected]> Committed: Mon May 22 10:30:30 2017 -0700 ---------------------------------------------------------------------- .../calcite/sql/fun/SqlFloorFunction.java | 86 ++++++++++++++++++-- .../rel/rel2sql/RelToSqlConverterTest.java | 11 +++ .../java/org/apache/calcite/test/JdbcTest.java | 8 ++ 3 files changed, 100 insertions(+), 5 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/f8ab9078/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 c5986a0..9e0e5ba 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 @@ -16,9 +16,11 @@ */ package org.apache.calcite.sql.fun; +import org.apache.calcite.avatica.util.TimeUnitRange; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlFunctionCategory; 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.type.OperandTypes; @@ -55,16 +57,90 @@ public class SqlFloorFunction extends SqlMonotonicUnaryFunction { @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec, int rightPrec) { - final SqlWriter.Frame frame = writer.startFunCall(getName()); if (call.operandCount() == 2) { - call.operand(0).unparse(writer, 0, 100); - writer.sep("TO"); - call.operand(1).unparse(writer, 100, 0); + unparseDatetime(writer, call); } else { - call.operand(0).unparse(writer, 0, 0); + unparseNumeric(writer, call); } + } + + private void unparseNumeric(SqlWriter writer, SqlCall call) { + final SqlWriter.Frame frame = writer.startFunCall(getName()); + call.operand(0).unparse(writer, 0, 0); writer.endFunCall(frame); } + + private void unparseDatetime(SqlWriter writer, SqlCall call) { + // FLOOR (not CEIL) is the only function that works in most dialects + if (kind != SqlKind.FLOOR) { + unparseDatetimeDefault(writer, call); + return; + } + + switch (writer.getDialect().getDatabaseProduct()) { + case ORACLE: + 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); + + unparseDatetimeFunction(writer, call, "TRUNC", true); + break; + case POSTGRESQL: + unparseDatetimeFunction(writer, call, "DATE_TRUNC", false); + break; + default: + unparseDatetimeDefault(writer, call); + } + } + + private void unparseDatetimeDefault(SqlWriter writer, SqlCall call) { + final SqlWriter.Frame frame = writer.startFunCall(getName()); + call.operand(0).unparse(writer, 0, 100); + writer.sep("TO"); + call.operand(1).unparse(writer, 100, 0); + writer.endFunCall(frame); + } + + 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); + writer.sep(",", true); + call.operand(secondOp).unparse(writer, 0, 0); + writer.endFunCall(frame); + } + + private static String convertToHsqlDb(TimeUnitRange unit) { + switch (unit) { + case YEAR: + return "YYYY"; + case MONTH: + return "MM"; + case DAY: + return "DD"; + case WEEK: + return "WW"; + case HOUR: + return "HH24"; + case MINUTE: + return "MI"; + case SECOND: + return "SS"; + default: + throw new AssertionError("could not convert time unit to an HsqlDb equivalent: " + + unit); + } + } } // End SqlFloorFunction.java http://git-wip-us.apache.org/repos/asf/calcite/blob/f8ab9078/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 295e443..e48bb03 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 @@ -636,6 +636,17 @@ public class RelToSqlConverterTest { .ok(expected); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1798";>[CALCITE-1798] + * Generate dialect-specific SQL for FLOOR operator</a>. */ + @Test public void testFloor() { + String query = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\""; + String expected = "SELECT TRUNC(hire_date, 'MI')\nFROM foodmart.employee"; + sql(query) + .dialect(DatabaseProduct.HSQLDB.getDialect()) + .ok(expected); + } + @Test public void testMatchRecognizePatternExpression() { String sql = "select *\n" + " from \"product\" match_recognize\n" http://git-wip-us.apache.org/repos/asf/calcite/blob/f8ab9078/core/src/test/java/org/apache/calcite/test/JdbcTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java index fb576dc..d97035d 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java @@ -1765,6 +1765,14 @@ public class JdbcTest { }); } + @Test public void testFloorDate() { + CalciteAssert.that() + .with(CalciteAssert.Config.JDBC_FOODMART) + .query("select floor(timestamp '2011-9-14 19:27:23' to month) as c \n" + + "from \"foodmart\".\"employee\" limit 1") + .returns("C=2011-09-01 00:00:00\n"); + } + /** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-387";>[CALCITE-387] * CompileException when cast TRUE to nullable boolean</a>. */
