Repository: calcite Updated Branches: refs/heads/master 54ed57f82 -> 7321c8708
[CALCITE-1936] Allow ROUND() and TRUNCATE() to take one operand, defaulting scale to 0 Also, make ROUND and TRUNCATE nullable if their 2nd operand is nullable. Close apache/calcite#513 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/bfaea7cc Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/bfaea7cc Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/bfaea7cc Branch: refs/heads/master Commit: bfaea7ccfd33e9a4b5fee1617f56f2ccdd78db08 Parents: 54ed57f Author: Minji Kim <[email protected]> Authored: Thu Jan 12 09:03:24 2017 -0800 Committer: Julian Hyde <[email protected]> Committed: Fri Aug 11 08:02:24 2017 -0700 ---------------------------------------------------------------------- .../apache/calcite/runtime/SqlFunctions.java | 38 +++++++++++++++++++- .../calcite/sql/fun/SqlStdOperatorTable.java | 8 ++--- .../apache/calcite/sql/type/OperandTypes.java | 11 ++++++ .../calcite/sql/test/SqlOperatorBaseTest.java | 38 ++++++++++++++++++++ site/_docs/reference.md | 4 +-- 5 files changed, 92 insertions(+), 7 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/bfaea7cc/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java index fec73c6..c52abfa 100644 --- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java +++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java @@ -1197,45 +1197,81 @@ public class SqlFunctions { } // SQL ROUND - /** SQL <code>ROUND</code> operator applied to long values. */ + /** SQL <code>ROUND</code> operator applied to int values. */ + public static int sround(int b0) { + return sround(b0, 0); + } + + /** SQL <code>ROUND</code> operator applied to int values. */ public static int sround(int b0, int b1) { return sround(BigDecimal.valueOf(b0), b1).intValue(); } /** SQL <code>ROUND</code> operator applied to long values. */ + public static long sround(long b0) { + return sround(b0, 0); + } + + /** SQL <code>ROUND</code> operator applied to long values. */ public static long sround(long b0, int b1) { return sround(BigDecimal.valueOf(b0), b1).longValue(); } /** SQL <code>ROUND</code> operator applied to BigDecimal values. */ + public static BigDecimal sround(BigDecimal b0) { + return sround(b0, 0); + } + + /** SQL <code>ROUND</code> operator applied to BigDecimal values. */ public static BigDecimal sround(BigDecimal b0, int b1) { return b0.movePointRight(b1) .setScale(0, RoundingMode.HALF_UP).movePointLeft(b1); } /** SQL <code>ROUND</code> operator applied to double values. */ + public static double sround(double b0) { + return sround(b0, 0); + } + + /** SQL <code>ROUND</code> operator applied to double values. */ public static double sround(double b0, int b1) { return sround(BigDecimal.valueOf(b0), b1).doubleValue(); } // SQL TRUNCATE /** SQL <code>TRUNCATE</code> operator applied to int values. */ + public static int struncate(int b0) { + return struncate(b0, 0); + } + public static int struncate(int b0, int b1) { return struncate(BigDecimal.valueOf(b0), b1).intValue(); } /** SQL <code>TRUNCATE</code> operator applied to long values. */ + public static long struncate(long b0) { + return struncate(b0, 0); + } + public static long struncate(long b0, int b1) { return struncate(BigDecimal.valueOf(b0), b1).longValue(); } /** SQL <code>TRUNCATE</code> operator applied to BigDecimal values. */ + public static BigDecimal struncate(BigDecimal b0) { + return struncate(b0, 0); + } + public static BigDecimal struncate(BigDecimal b0, int b1) { return b0.movePointRight(b1) .setScale(0, RoundingMode.DOWN).movePointLeft(b1); } /** SQL <code>TRUNCATE</code> operator applied to double values. */ + public static double struncate(double b0) { + return struncate(b0, 0); + } + public static double struncate(double b0, int b1) { return struncate(BigDecimal.valueOf(b0), b1).doubleValue(); } http://git-wip-us.apache.org/repos/asf/calcite/blob/bfaea7cc/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java index 8300add..e4227f7 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java @@ -1442,9 +1442,9 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { new SqlFunction( "ROUND", SqlKind.OTHER_FUNCTION, - ReturnTypes.ARG0, + ReturnTypes.ARG0_NULLABLE, null, - OperandTypes.NUMERIC_INTEGER, + OperandTypes.NUMERIC_OPTIONAL_INTEGER, SqlFunctionCategory.NUMERIC); public static final SqlFunction SIGN = @@ -1479,9 +1479,9 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { new SqlFunction( "TRUNCATE", SqlKind.OTHER_FUNCTION, - ReturnTypes.ARG0, + ReturnTypes.ARG0_NULLABLE, null, - OperandTypes.NUMERIC_INTEGER, + OperandTypes.NUMERIC_OPTIONAL_INTEGER, SqlFunctionCategory.NUMERIC); public static final SqlFunction PI = http://git-wip-us.apache.org/repos/asf/calcite/blob/bfaea7cc/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java index 15d3b70..75366d9 100644 --- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java +++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java @@ -18,6 +18,7 @@ package org.apache.calcite.sql.type; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeComparability; +import org.apache.calcite.runtime.PredicateImpl; import org.apache.calcite.sql.SqlCallBinding; import org.apache.calcite.sql.SqlLiteral; import org.apache.calcite.sql.SqlNode; @@ -200,6 +201,16 @@ public abstract class OperandTypes { public static final SqlSingleOperandTypeChecker NUMERIC = family(SqlTypeFamily.NUMERIC); + + public static final SqlSingleOperandTypeChecker NUMERIC_OPTIONAL_INTEGER = + family(ImmutableList.of(SqlTypeFamily.NUMERIC, SqlTypeFamily.INTEGER), + // Second operand optional (operand index 0, 1) + new PredicateImpl<Integer>() { + public boolean test(Integer number) { + return number == 1; + } + }); + public static final SqlSingleOperandTypeChecker NUMERIC_INTEGER = family(SqlTypeFamily.NUMERIC, SqlTypeFamily.INTEGER); http://git-wip-us.apache.org/repos/asf/calcite/blob/bfaea7cc/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index 7925bf6..a54e2d0 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -1640,6 +1640,8 @@ public abstract class SqlOperatorBaseTest { tester.checkScalarApprox("{fn RADIANS(90)}", "DOUBLE NOT NULL", 1.57080, 0.001); tester.checkScalarApprox("{fn RAND(42)}", "DOUBLE NOT NULL", 0.63708, 0.001); tester.checkScalar("{fn ROUND(1251, -2)}", 1300, "INTEGER NOT NULL"); + tester.checkFails("^{fn ROUND(1251)}^", "Cannot apply '\\{fn ROUND\\}' to " + + "arguments of type '\\{fn ROUND\\}\\(<INTEGER>\\)'.*", false); tester.checkScalar("{fn SIGN(-1)}", -1, "INTEGER NOT NULL"); tester.checkScalarApprox("{fn SIN(0.2)}", "DOUBLE NOT NULL", 0.19867, 0.001); tester.checkScalarApprox("{fn SQRT(4.2)}", "DOUBLE NOT NULL", 2.04939, 0.001); @@ -4388,9 +4390,28 @@ public abstract class SqlOperatorBaseTest { "round(cast(42.346 as decimal(2, 3)), 2)", BigDecimal.valueOf(4235, 2), "DECIMAL(2, 3) NOT NULL"); + tester.checkScalar( + "round(cast(-42.346 as decimal(2, 3)), 2)", + BigDecimal.valueOf(-4235, 2), + "DECIMAL(2, 3) NOT NULL"); tester.checkNull("round(cast(null as integer), 1)"); tester.checkNull("round(cast(null as double), 1)"); + tester.checkNull("round(43.21, cast(null as integer))"); + + tester.checkNull("round(cast(null as double))"); + tester.checkScalar("round(42)", 42, "INTEGER NOT NULL"); + tester.checkScalar( + "round(cast(42.346 as decimal(2, 3)))", + BigDecimal.valueOf(42, 0), + "DECIMAL(2, 3) NOT NULL"); + tester.checkScalar("round(42.324)", + BigDecimal.valueOf(42, 0), + "DECIMAL(5, 3) NOT NULL"); + tester.checkScalar("round(42.724)", + BigDecimal.valueOf(43, 0), + "DECIMAL(5, 3) NOT NULL"); } + @Test public void testSignFunc() { tester.setFor( SqlStdOperatorTable.SIGN); @@ -4487,8 +4508,25 @@ public abstract class SqlOperatorBaseTest { "truncate(cast(42.345 as decimal(2, 3)), 2)", BigDecimal.valueOf(4234, 2), "DECIMAL(2, 3) NOT NULL"); + tester.checkScalar( + "truncate(cast(-42.345 as decimal(2, 3)), 2)", + BigDecimal.valueOf(-4234, 2), + "DECIMAL(2, 3) NOT NULL"); tester.checkNull("truncate(cast(null as integer), 1)"); tester.checkNull("truncate(cast(null as double), 1)"); + tester.checkNull("truncate(43.21, cast(null as integer))"); + + tester.checkScalar("truncate(42)", 42, "INTEGER NOT NULL"); + tester.checkScalar("truncate(42.324)", + BigDecimal.valueOf(42, 0), + "DECIMAL(5, 3) NOT NULL"); + tester.checkScalar("truncate(cast(42.324 as float))", 42F, "FLOAT NOT NULL"); + tester.checkScalar( + "truncate(cast(42.345 as decimal(2, 3)))", + BigDecimal.valueOf(42, 0), + "DECIMAL(2, 3) NOT NULL"); + tester.checkNull("truncate(cast(null as integer))"); + tester.checkNull("truncate(cast(null as double))"); } @Test public void testNullifFunc() { http://git-wip-us.apache.org/repos/asf/calcite/blob/bfaea7cc/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index e911765..dc4f3db 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -1108,11 +1108,11 @@ comp: | DEGREES(numeric) | Converts *numeric* from radians to degrees | PI() | Returns a value that is closer than any other value to *pi* | RADIANS(numeric) | Converts *numeric* from degrees to radians -| ROUND(numeric1, numeric2) | Rounds *numeric1* to *numeric2* places right to the decimal point +| ROUND(numeric1 [, numeric2]) | Rounds *numeric1* to optionally *numeric2* (if not specified 0) places right to the decimal point | SIGN(numeric) | Returns the signum of *numeric* | SIN(numeric) | Returns the sine of *numeric* | TAN(numeric) | Returns the tangent of *numeric* -| TRUNCATE(numeric1, numeric2) | Truncates *numeric1* to *numeric2* places right to the decimal point +| TRUNCATE(numeric1 [, numeric2]) | Truncates *numeric1* to optionally *numeric2* (if not specified 0) places right to the decimal point. ### Character string operators and functions
