You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2017/01/09 20:58:17 UTC
calcite git commit: [CALCITE-1557] Add numeric scalar functions
(Laurent Goujon)
Repository: calcite
Updated Branches:
refs/heads/master fd34b0de2 -> 17dc06fe5
[CALCITE-1557] Add numeric scalar functions (Laurent Goujon)
Add support for the missing JDBC/ODBC numeric scalar functions:
ACOS ASIN ATAN ATAN2 CEILING COS COT DEGREES FLOOR PI RADIANS RAND
ROUND SIGN SIN SQRT TAN TRUNCATE
Close apache/calcite#342
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/17dc06fe
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/17dc06fe
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/17dc06fe
Branch: refs/heads/master
Commit: 17dc06fe5f4a250475517ddbd8c3f966a3c9d1fe
Parents: fd34b0d
Author: Laurent Goujon <la...@dremio.com>
Authored: Thu Oct 13 09:45:27 2016 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Mon Jan 9 11:15:52 2017 -0800
----------------------------------------------------------------------
core/src/main/codegen/templates/Parser.jj | 2 +
.../calcite/adapter/enumerable/RexImpTable.java | 35 ++
.../apache/calcite/runtime/SqlFunctions.java | 239 +++++++++++
.../apache/calcite/sql/SqlJdbcFunctionCall.java | 19 +
.../calcite/sql/fun/SqlBaseContextVariable.java | 59 +++
.../calcite/sql/fun/SqlStdOperatorTable.java | 122 ++++++
.../sql/fun/SqlStringContextVariable.java | 35 +-
.../apache/calcite/sql/type/OperandTypes.java | 3 +
.../calcite/sql/test/SqlOperatorBaseTest.java | 414 ++++++++++++++++---
.../apache/calcite/test/SqlFunctionsTest.java | 126 ++++++
core/src/test/resources/sql/misc.iq | 22 +
site/_docs/reference.md | 73 ++--
12 files changed, 1029 insertions(+), 120 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/17dc06fe/core/src/main/codegen/templates/Parser.jj
----------------------------------------------------------------------
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index 318ed34..931f38f 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -4717,6 +4717,7 @@ SqlIdentifier ReservedFunctionName() :
<ABS>
| <AVG>
| <CARDINALITY>
+ | <CEILING>
| <CHAR_LENGTH>
| <CHARACTER_LENGTH>
| <COALESCE>
@@ -4732,6 +4733,7 @@ SqlIdentifier ReservedFunctionName() :
| <ELEMENT>
| <EXP>
| <FIRST_VALUE>
+ | <FLOOR>
| <FUSION>
| <GROUPING>
| <LAST_VALUE>
http://git-wip-us.apache.org/repos/asf/calcite/blob/17dc06fe/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index 01c740c..1c3e0ea 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -89,8 +89,12 @@ import static org.apache.calcite.linq4j.tree.ExpressionType.Subtract;
import static org.apache.calcite.linq4j.tree.ExpressionType.UnaryPlus;
import static org.apache.calcite.sql.fun.OracleSqlOperatorTable.TRANSLATE3;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ABS;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ACOS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.AND;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ARRAY_VALUE_CONSTRUCTOR;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ASIN;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ATAN;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ATAN2;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CARDINALITY;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CASE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CAST;
@@ -99,6 +103,8 @@ import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CHARACTER_LENGTH;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CHAR_LENGTH;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.COLLECT;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CONCAT;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.COS;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.COT;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.COUNT;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CURRENT_DATE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CURRENT_PATH;
@@ -109,6 +115,7 @@ import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CURRENT_USER;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.CURRENT_VALUE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.DATETIME_PLUS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.DEFAULT;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.DEGREES;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.DENSE_RANK;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.DIVIDE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.DIVIDE_INTEGER;
@@ -154,24 +161,31 @@ import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NOT_SIMILAR_TO;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NTILE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.OR;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.OVERLAY;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.PI;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.PLUS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.POSITION;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.POWER;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.RADIANS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.RAND;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.RAND_INTEGER;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.RANK;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.REINTERPRET;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ROUND;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ROW;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ROW_NUMBER;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SESSION_USER;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SIGN;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SIMILAR_TO;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SIN;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SINGLE_VALUE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SLICE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SUBSTRING;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SUM;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SUM0;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.SYSTEM_USER;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.TAN;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.TRIM;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.TRUNCATE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.UNARY_MINUS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.UNARY_PLUS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.UPPER;
@@ -275,6 +289,27 @@ public class RexImpTable {
}
}, false);
+ defineMethod(ACOS, "acos", NullPolicy.STRICT);
+ defineMethod(ASIN, "asin", NullPolicy.STRICT);
+ defineMethod(ATAN, "atan", NullPolicy.STRICT);
+ defineMethod(ATAN2, "atan2", NullPolicy.STRICT);
+ defineMethod(COS, "cos", NullPolicy.STRICT);
+ defineMethod(COT, "cot", NullPolicy.STRICT);
+ defineMethod(DEGREES, "degrees", NullPolicy.STRICT);
+ defineMethod(RADIANS, "radians", NullPolicy.STRICT);
+ defineMethod(ROUND, "sround", NullPolicy.STRICT);
+ defineMethod(SIGN, "sign", NullPolicy.STRICT);
+ defineMethod(SIN, "sin", NullPolicy.STRICT);
+ defineMethod(TAN, "tan", NullPolicy.STRICT);
+ defineMethod(TRUNCATE, "struncate", NullPolicy.STRICT);
+
+ map.put(PI, new CallImplementor() {
+ @Override public Expression implement(RexToLixTranslator translator,
+ RexCall call, NullAs nullAs) {
+ return Expressions.constant(Math.PI);
+ }
+ });
+
// datetime
defineImplementor(DATETIME_PLUS, NullPolicy.STRICT,
new DatetimeArithmeticImplementor(), false);
http://git-wip-us.apache.org/repos/asf/calcite/blob/17dc06fe/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 87b5528..920f93b 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -36,6 +36,7 @@ import org.apache.calcite.util.Bug;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.math.MathContext;
+import java.math.RoundingMode;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DecimalFormat;
@@ -807,6 +808,10 @@ public class SqlFunctions {
return Math.exp(b0);
}
+ public static double exp(BigDecimal b0) {
+ return Math.exp(b0.doubleValue());
+ }
+
public static double exp(long b0) {
return Math.exp(b0);
}
@@ -822,6 +827,10 @@ public class SqlFunctions {
return Math.pow(b0, b1);
}
+ public static double power(BigDecimal b0, BigDecimal b1) {
+ return Math.pow(b0.doubleValue(), b1.doubleValue());
+ }
+
public static double power(long b0, BigDecimal b1) {
return Math.pow(b0, b1.doubleValue());
}
@@ -1052,6 +1061,236 @@ public class SqlFunctions {
return b0.abs();
}
+ // ACOS
+ /** SQL <code>ACOS</code> operator applied to long values. */
+ public static double acos(long b0) {
+ return Math.acos(b0);
+ }
+
+ /** SQL <code>ACOS</code> operator applied to BigDecimal values. */
+ public static double acos(BigDecimal b0) {
+ return Math.acos(b0.doubleValue());
+ }
+
+ /** SQL <code>ACOS</code> operator applied to double values. */
+ public static double acos(double b0) {
+ return Math.acos(b0);
+ }
+
+ // ASIN
+ /** SQL <code>ASIN</code> operator applied to long values. */
+ public static double asin(long b0) {
+ return Math.asin(b0);
+ }
+
+ /** SQL <code>ASIN</code> operator applied to BigDecimal values. */
+ public static double asin(BigDecimal b0) {
+ return Math.asin(b0.doubleValue());
+ }
+
+ /** SQL <code>ASIN</code> operator applied to double values. */
+ public static double asin(double b0) {
+ return Math.asin(b0);
+ }
+
+ // ATAN
+ /** SQL <code>ATAN</code> operator applied to long values. */
+ public static double atan(long b0) {
+ return Math.atan(b0);
+ }
+
+ /** SQL <code>ATAN</code> operator applied to BigDecimal values. */
+ public static double atan(BigDecimal b0) {
+ return Math.atan(b0.doubleValue());
+ }
+
+ /** SQL <code>ATAN</code> operator applied to double values. */
+ public static double atan(double b0) {
+ return Math.atan(b0);
+ }
+
+ // ATAN2
+ /** SQL <code>ATAN2</code> operator applied to long values. */
+ public static double atan2(long b0, long b1) {
+ return Math.atan2(b0, b1);
+ }
+
+ /** SQL <code>ATAN2</code> operator applied to long/BigDecimal values. */
+ public static double atan2(long b0, BigDecimal b1) {
+ return Math.atan2(b0, b1.doubleValue());
+ }
+
+ /** SQL <code>ATAN2</code> operator applied to BigDecimal values. */
+ public static double atan2(BigDecimal b0, BigDecimal b1) {
+ return Math.atan2(b0.doubleValue(), b1.doubleValue());
+ }
+
+ /** SQL <code>ATAN2</code> operator applied to double values. */
+ public static double atan2(double b0, double b1) {
+ return Math.atan2(b0, b1);
+ }
+
+ // COS
+ /** SQL <code>COS</code> operator applied to long values. */
+ public static double cos(long b0) {
+ return Math.cos(b0);
+ }
+
+ /** SQL <code>COS</code> operator applied to BigDecimal values. */
+ public static double cos(BigDecimal b0) {
+ return Math.cos(b0.doubleValue());
+ }
+
+ /** SQL <code>COS</code> operator applied to double values. */
+ public static double cos(double b0) {
+ return Math.cos(b0);
+ }
+
+ // COT
+ /** SQL <code>COT</code> operator applied to long values. */
+ public static double cot(long b0) {
+ return 1.0d / Math.tan(b0);
+ }
+
+ /** SQL <code>COT</code> operator applied to BigDecimal values. */
+ public static double cot(BigDecimal b0) {
+ return 1.0d / Math.tan(b0.doubleValue());
+ }
+
+ /** SQL <code>COT</code> operator applied to double values. */
+ public static double cot(double b0) {
+ return 1.0d / Math.tan(b0);
+ }
+
+ // DEGREES
+ /** SQL <code>DEGREES</code> operator applied to long values. */
+ public static double degrees(long b0) {
+ return Math.toDegrees(b0);
+ }
+
+ /** SQL <code>DEGREES</code> operator applied to BigDecimal values. */
+ public static double degrees(BigDecimal b0) {
+ return Math.toDegrees(b0.doubleValue());
+ }
+
+ /** SQL <code>DEGREES</code> operator applied to double values. */
+ public static double degrees(double b0) {
+ return Math.toDegrees(b0);
+ }
+
+ // RADIANS
+ /** SQL <code>RADIANS</code> operator applied to long values. */
+ public static double radians(long b0) {
+ return Math.toRadians(b0);
+ }
+
+ /** SQL <code>RADIANS</code> operator applied to BigDecimal values. */
+ public static double radians(BigDecimal b0) {
+ return Math.toRadians(b0.doubleValue());
+ }
+
+ /** SQL <code>RADIANS</code> operator applied to double values. */
+ public static double radians(double b0) {
+ return Math.toRadians(b0);
+ }
+
+ // SQL ROUND
+ /** SQL <code>ROUND</code> operator applied to long 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, int b1) {
+ return sround(BigDecimal.valueOf(b0), b1).longValue();
+ }
+
+ /** 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, 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, int b1) {
+ return struncate(BigDecimal.valueOf(b0), b1).intValue();
+ }
+
+ /** SQL <code>TRUNCATE</code> operator applied to long values. */
+ 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, 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, int b1) {
+ return struncate(BigDecimal.valueOf(b0), b1).doubleValue();
+ }
+
+ // SIGN
+ /** SQL <code>SIGN</code> operator applied to int values. */
+ public static int sign(int b0) {
+ return Integer.signum(b0);
+ }
+
+ /** SQL <code>SIGN</code> operator applied to long values. */
+ public static long sign(long b0) {
+ return Long.signum(b0);
+ }
+
+ /** SQL <code>SIGN</code> operator applied to BigDecimal values. */
+ public static BigDecimal sign(BigDecimal b0) {
+ return BigDecimal.valueOf(b0.signum());
+ }
+
+ /** SQL <code>SIGN</code> operator applied to double values. */
+ public static double sign(double b0) {
+ return Math.signum(b0);
+ }
+
+ // SIN
+ /** SQL <code>SIN</code> operator applied to long values. */
+ public static double sin(long b0) {
+ return Math.sin(b0);
+ }
+
+ /** SQL <code>SIN</code> operator applied to BigDecimal values. */
+ public static double sin(BigDecimal b0) {
+ return Math.sin(b0.doubleValue());
+ }
+
+ /** SQL <code>SIN</code> operator applied to double values. */
+ public static double sin(double b0) {
+ return Math.sin(b0);
+ }
+
+ // TAN
+ /** SQL <code>TAN</code> operator applied to long values. */
+ public static double tan(long b0) {
+ return Math.tan(b0);
+ }
+
+ /** SQL <code>TAN</code> operator applied to BigDecimal values. */
+ public static double tan(BigDecimal b0) {
+ return Math.tan(b0.doubleValue());
+ }
+
+ /** SQL <code>TAN</code> operator applied to double values. */
+ public static double tan(double b0) {
+ return Math.tan(b0);
+ }
+
// Helpers
/** Helper for implementing MIN. Somewhat similar to LEAST operator. */
http://git-wip-us.apache.org/repos/asf/calcite/blob/17dc06fe/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
index ccb14ae..9945057 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
@@ -656,11 +656,30 @@ public class SqlJdbcFunctionCall extends SqlFunction {
// See also SqlOperatorTests.testJdbcFn, which contains the list.
ImmutableMap.Builder<String, MakeCall> map = ImmutableMap.builder();
map.put("ABS", simple(SqlStdOperatorTable.ABS));
+ map.put("ACOS", simple(SqlStdOperatorTable.ACOS));
+ map.put("ASIN", simple(SqlStdOperatorTable.ASIN));
+ map.put("ATAN", simple(SqlStdOperatorTable.ATAN));
+ map.put("ATAN2", simple(SqlStdOperatorTable.ATAN2));
+ map.put("CEILING", simple(SqlStdOperatorTable.CEIL));
+ map.put("COS", simple(SqlStdOperatorTable.COS));
+ map.put("COT", simple(SqlStdOperatorTable.COT));
+ map.put("DEGREES", simple(SqlStdOperatorTable.DEGREES));
map.put("EXP", simple(SqlStdOperatorTable.EXP));
+ map.put("FLOOR", simple(SqlStdOperatorTable.FLOOR));
map.put("LOG", simple(SqlStdOperatorTable.LN));
map.put("LOG10", simple(SqlStdOperatorTable.LOG10));
map.put("MOD", simple(SqlStdOperatorTable.MOD));
+ map.put("PI", simple(SqlStdOperatorTable.PI));
map.put("POWER", simple(SqlStdOperatorTable.POWER));
+ map.put("RADIANS", simple(SqlStdOperatorTable.RADIANS));
+ map.put("RAND", simple(SqlStdOperatorTable.RAND));
+ map.put("ROUND", simple(SqlStdOperatorTable.ROUND));
+ map.put("SIGN", simple(SqlStdOperatorTable.SIGN));
+ map.put("SIN", simple(SqlStdOperatorTable.SIN));
+ map.put("SQRT", simple(SqlStdOperatorTable.SQRT));
+ map.put("TAN", simple(SqlStdOperatorTable.TAN));
+ map.put("TRUNCATE", simple(SqlStdOperatorTable.TRUNCATE));
+
map.put("CONCAT", simple(SqlStdOperatorTable.CONCAT));
map.put("INSERT",
new PermutingMakeCall(SqlStdOperatorTable.OVERLAY, new int[]{0, 2, 3, 1}));
http://git-wip-us.apache.org/repos/asf/calcite/blob/17dc06fe/core/src/main/java/org/apache/calcite/sql/fun/SqlBaseContextVariable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlBaseContextVariable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlBaseContextVariable.java
new file mode 100644
index 0000000..b12f19c
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlBaseContextVariable.java
@@ -0,0 +1,59 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.sql.fun;
+
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlOperatorBinding;
+import org.apache.calcite.sql.SqlSyntax;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.SqlReturnTypeInference;
+import org.apache.calcite.sql.validate.SqlMonotonicity;
+
+/**
+ * Base class for functions such as "PI", "USER", "CURRENT_ROLE", and
+ * "CURRENT_PATH".
+ */
+public class SqlBaseContextVariable extends SqlFunction {
+ //~ Constructors -----------------------------------------------------------
+
+ /** Creates a SqlBaseContextVariable. */
+ protected SqlBaseContextVariable(String name,
+ SqlReturnTypeInference returnType, SqlFunctionCategory category) {
+ super(name, SqlKind.OTHER_FUNCTION, returnType, null, OperandTypes.NILADIC,
+ category);
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ public SqlSyntax getSyntax() {
+ return SqlSyntax.FUNCTION_ID;
+ }
+
+ // All of the string constants are monotonic.
+ @Override public SqlMonotonicity getMonotonicity(SqlOperatorBinding call) {
+ return SqlMonotonicity.CONSTANT;
+ }
+
+ // Plans referencing context variables should never be cached
+ public boolean isDynamicFunction() {
+ return true;
+ }
+}
+
+// End SqlBaseContextVariable.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/17dc06fe/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 4550dfc..fcf1352 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
@@ -1248,6 +1248,69 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
OperandTypes.NUMERIC_OR_INTERVAL,
SqlFunctionCategory.NUMERIC);
+ public static final SqlFunction ACOS =
+ new SqlFunction(
+ "ACOS",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.DOUBLE_NULLABLE,
+ null,
+ OperandTypes.NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+
+ public static final SqlFunction ASIN =
+ new SqlFunction(
+ "ASIN",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.DOUBLE_NULLABLE,
+ null,
+ OperandTypes.NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+
+ public static final SqlFunction ATAN =
+ new SqlFunction(
+ "ATAN",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.DOUBLE_NULLABLE,
+ null,
+ OperandTypes.NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+
+ public static final SqlFunction ATAN2 =
+ new SqlFunction(
+ "ATAN2",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.DOUBLE_NULLABLE,
+ null,
+ OperandTypes.NUMERIC_NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+
+ public static final SqlFunction COS =
+ new SqlFunction(
+ "COS",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.DOUBLE_NULLABLE,
+ null,
+ OperandTypes.NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+
+ public static final SqlFunction COT =
+ new SqlFunction(
+ "COT",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.DOUBLE_NULLABLE,
+ null,
+ OperandTypes.NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+
+ public static final SqlFunction DEGREES =
+ new SqlFunction(
+ "DEGREES",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.DOUBLE_NULLABLE,
+ null,
+ OperandTypes.NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+
public static final SqlFunction EXP =
new SqlFunction(
"EXP",
@@ -1257,6 +1320,65 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
OperandTypes.NUMERIC,
SqlFunctionCategory.NUMERIC);
+ public static final SqlFunction RADIANS =
+ new SqlFunction(
+ "RADIANS",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.DOUBLE_NULLABLE,
+ null,
+ OperandTypes.NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+
+ public static final SqlFunction ROUND =
+ new SqlFunction(
+ "ROUND",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.ARG0,
+ null,
+ OperandTypes.NUMERIC_INTEGER,
+ SqlFunctionCategory.NUMERIC);
+
+ public static final SqlFunction SIGN =
+ new SqlFunction(
+ "SIGN",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.ARG0,
+ null,
+ OperandTypes.NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+
+ public static final SqlFunction SIN =
+ new SqlFunction(
+ "SIN",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.DOUBLE_NULLABLE,
+ null,
+ OperandTypes.NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+
+
+ public static final SqlFunction TAN =
+ new SqlFunction(
+ "TAN",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.DOUBLE_NULLABLE,
+ null,
+ OperandTypes.NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+
+ public static final SqlFunction TRUNCATE =
+ new SqlFunction(
+ "TRUNCATE",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.ARG0,
+ null,
+ OperandTypes.NUMERIC_INTEGER,
+ SqlFunctionCategory.NUMERIC);
+
+ public static final SqlFunction PI =
+ new SqlBaseContextVariable("PI", ReturnTypes.DOUBLE,
+ SqlFunctionCategory.NUMERIC);
+
public static final SqlFunction NULLIF = new SqlNullifFunction();
/**
http://git-wip-us.apache.org/repos/asf/calcite/blob/17dc06fe/core/src/main/java/org/apache/calcite/sql/fun/SqlStringContextVariable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStringContextVariable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStringContextVariable.java
index caedd64..893b4bf 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStringContextVariable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStringContextVariable.java
@@ -16,45 +16,18 @@
*/
package org.apache.calcite.sql.fun;
-import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlFunctionCategory;
-import org.apache.calcite.sql.SqlKind;
-import org.apache.calcite.sql.SqlOperatorBinding;
-import org.apache.calcite.sql.SqlSyntax;
-import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
-import org.apache.calcite.sql.validate.SqlMonotonicity;
/**
- * Base class for functions such as "USER", "CURRENT_ROLE", and "CURRENT_PATH".
+ * Base class for string functions such as "USER", "CURRENT_ROLE", and
+ * "CURRENT_PATH".
*/
-public class SqlStringContextVariable extends SqlFunction {
+public class SqlStringContextVariable extends SqlBaseContextVariable {
//~ Constructors -----------------------------------------------------------
protected SqlStringContextVariable(String name) {
- super(
- name,
- SqlKind.OTHER_FUNCTION,
- ReturnTypes.VARCHAR_2000,
- null,
- OperandTypes.NILADIC,
- SqlFunctionCategory.SYSTEM);
- }
-
- //~ Methods ----------------------------------------------------------------
-
- public SqlSyntax getSyntax() {
- return SqlSyntax.FUNCTION_ID;
- }
-
- // All of the string constants are monotonic.
- @Override public SqlMonotonicity getMonotonicity(SqlOperatorBinding call) {
- return SqlMonotonicity.CONSTANT;
- }
-
- // Plans referencing context variables should never be cached
- public boolean isDynamicFunction() {
- return true;
+ super(name, ReturnTypes.VARCHAR_2000, SqlFunctionCategory.SYSTEM);
}
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/17dc06fe/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 3314398..9a5e351 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
@@ -200,6 +200,9 @@ public abstract class OperandTypes {
public static final SqlSingleOperandTypeChecker NUMERIC =
family(SqlTypeFamily.NUMERIC);
+ public static final SqlSingleOperandTypeChecker NUMERIC_INTEGER =
+ family(SqlTypeFamily.NUMERIC, SqlTypeFamily.INTEGER);
+
public static final SqlSingleOperandTypeChecker NUMERIC_NUMERIC =
family(SqlTypeFamily.NUMERIC, SqlTypeFamily.NUMERIC);
http://git-wip-us.apache.org/repos/asf/calcite/blob/17dc06fe/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 3b90848..8c3fcce 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
@@ -1606,42 +1606,22 @@ public abstract class SqlOperatorBaseTest {
// not implemented or is broken.
// Numeric Functions
- if (!enable) {
-// return;
- }
tester.checkScalar("{fn ABS(-3)}", 3, "INTEGER NOT NULL");
- if (false) {
- tester.checkScalar("{fn ACOS(float)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn ASIN(float)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn ATAN(float)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn ATAN2(float1, float2)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn CEILING(-2.6)}", 2, "");
- }
- if (false) {
- tester.checkScalar("{fn COS(float)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn COT(float)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn DEGREES(number)}", null, "");
- }
+ tester.checkScalarApprox("{fn ACOS(0.2)}", "DOUBLE NOT NULL", 1.36943, 0.001);
+ tester.checkScalarApprox("{fn ASIN(0.2)}", "DOUBLE NOT NULL", 0.20135, 0.001);
+ tester.checkScalarApprox("{fn ATAN(0.2)}", "DOUBLE NOT NULL", 0.19739, 0.001);
+ tester.checkScalarApprox("{fn ATAN2(-2, 2)}", "DOUBLE NOT NULL", -0.78539, 0.001);
+ tester.checkScalar("{fn CEILING(-2.6)}", -2, "DECIMAL(2, 0) NOT NULL");
+ tester.checkScalarApprox("{fn COS(0.2)}", "DOUBLE NOT NULL", 0.98007, 0.001);
+ tester.checkScalarApprox("{fn COT(0.2)}", "DOUBLE NOT NULL", 4.93315, 0.001);
+ tester.checkScalarApprox("{fn DEGREES(-1)}", "DOUBLE NOT NULL", -57.29578, 0.001);
+
tester.checkScalarApprox(
"{fn EXP(2)}",
"DOUBLE NOT NULL",
7.389,
0.001);
- if (false) {
- tester.checkScalar("{fn FLOOR(2.6)}", 2, "DOUBLE NOT NULL");
- }
+ tester.checkScalar("{fn FLOOR(2.6)}", 2, "DECIMAL(2, 0) NOT NULL");
tester.checkScalarApprox(
"{fn LOG(10)}",
"DOUBLE NOT NULL",
@@ -1653,34 +1633,17 @@ public abstract class SqlOperatorBaseTest {
2,
0);
tester.checkScalar("{fn MOD(19, 4)}", 3, "INTEGER NOT NULL");
- if (false) {
- tester.checkScalar("{fn PI()}", null, "");
- }
- tester.checkScalar("{fn POWER(2, 3)}", 8.0, "DOUBLE NOT NULL");
- if (false) {
- tester.checkScalar("{fn RADIANS(number)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn RAND(integer)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn ROUND(number, places)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn SIGN(number)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn SIN(float)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn SQRT(float)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn TAN(float)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn TRUNCATE(number, places)}", null, "");
- }
+ tester.checkScalarApprox("{fn PI()}", "DOUBLE NOT NULL", 3.14159, 0.0001);
+ tester.checkScalarApprox("{fn POWER(2, 3)}", "DOUBLE NOT NULL", 8.0, 0.001);
+ 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.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);
+ tester.checkScalarApprox("{fn TAN(0.2)}", "DOUBLE NOT NULL", 0.20271, 0.001);
+ tester.checkScalar("{fn TRUNCATE(12.34, 1)}", 12.3, "DECIMAL(4, 2) NOT NULL");
+ tester.checkScalar("{fn TRUNCATE(-12.34, -1)}", -10, "DECIMAL(4, 2) NOT NULL");
// String Functions
if (false) {
@@ -3698,6 +3661,11 @@ public abstract class SqlOperatorBaseTest {
"DOUBLE NOT NULL",
1.4142d,
0.0001d);
+ tester.checkScalarApprox(
+ "sqrt(cast(2 as decimal(2, 0)))",
+ "DOUBLE NOT NULL",
+ 1.4142d,
+ 0.0001d);
tester.checkNull("sqrt(cast(null as integer))");
tester.checkNull("sqrt(cast(null as double))");
}
@@ -3894,6 +3862,336 @@ public abstract class SqlOperatorBaseTest {
tester.checkNull("abs(cast(null as interval hour))");
}
+ @Test public void testAcosFunc() {
+ tester.setFor(
+ SqlStdOperatorTable.ACOS);
+ tester.checkType("acos(0)", "DOUBLE NOT NULL");
+ tester.checkType("acos(cast(1 as float))", "DOUBLE NOT NULL");
+ tester.checkType(
+ "acos(case when false then 0.5 else null end)", "DOUBLE");
+ tester.checkFails(
+ "^acos('abc')^",
+ "Cannot apply 'ACOS' to arguments of type 'ACOS\\(<CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): 'ACOS\\(<NUMERIC>\\)'",
+ false);
+ tester.checkScalarApprox(
+ "acos(0.5)",
+ "DOUBLE NOT NULL",
+ 1.0472d,
+ 0.0001d);
+ tester.checkScalarApprox(
+ "acos(cast(0.5 as decimal(1, 1)))",
+ "DOUBLE NOT NULL",
+ 1.0472d,
+ 0.0001d);
+ tester.checkNull("acos(cast(null as integer))");
+ tester.checkNull("acos(cast(null as double))");
+ }
+
+ @Test public void testAsinFunc() {
+ tester.setFor(
+ SqlStdOperatorTable.ASIN);
+ tester.checkType("asin(0)", "DOUBLE NOT NULL");
+ tester.checkType("asin(cast(1 as float))", "DOUBLE NOT NULL");
+ tester.checkType(
+ "asin(case when false then 0.5 else null end)", "DOUBLE");
+ tester.checkFails(
+ "^asin('abc')^",
+ "Cannot apply 'ASIN' to arguments of type 'ASIN\\(<CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): 'ASIN\\(<NUMERIC>\\)'",
+ false);
+ tester.checkScalarApprox(
+ "asin(0.5)",
+ "DOUBLE NOT NULL",
+ 0.5236d,
+ 0.0001d);
+ tester.checkScalarApprox(
+ "asin(cast(0.5 as decimal(1, 1)))",
+ "DOUBLE NOT NULL",
+ 0.5236d,
+ 0.0001d);
+ tester.checkNull("asin(cast(null as integer))");
+ tester.checkNull("asin(cast(null as double))");
+ }
+
+ @Test public void testAtanFunc() {
+ tester.setFor(
+ SqlStdOperatorTable.ATAN);
+ tester.checkType("atan(2)", "DOUBLE NOT NULL");
+ tester.checkType("atan(cast(2 as float))", "DOUBLE NOT NULL");
+ tester.checkType(
+ "atan(case when false then 2 else null end)", "DOUBLE");
+ tester.checkFails(
+ "^atan('abc')^",
+ "Cannot apply 'ATAN' to arguments of type 'ATAN\\(<CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): 'ATAN\\(<NUMERIC>\\)'",
+ false);
+ tester.checkScalarApprox(
+ "atan(2)",
+ "DOUBLE NOT NULL",
+ 1.1071d,
+ 0.0001d);
+ tester.checkScalarApprox(
+ "atan(cast(2 as decimal(1, 0)))",
+ "DOUBLE NOT NULL",
+ 1.1071d,
+ 0.0001d);
+ tester.checkNull("atan(cast(null as integer))");
+ tester.checkNull("atan(cast(null as double))");
+ }
+
+ @Test public void testAtan2Func() {
+ tester.setFor(
+ SqlStdOperatorTable.ATAN2);
+ tester.checkType("atan2(2, -2)", "DOUBLE NOT NULL");
+ tester.checkType("atan2(cast(1 as float), -1)", "DOUBLE NOT NULL");
+ tester.checkType(
+ "atan2(case when false then 0.5 else null end, -1)", "DOUBLE");
+ tester.checkFails(
+ "^atan2('abc', 'def')^",
+ "Cannot apply 'ATAN2' to arguments of type 'ATAN2\\(<CHAR\\(3\\)>, <CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): 'ATAN2\\(<NUMERIC>, <NUMERIC>\\)'",
+ false);
+ tester.checkScalarApprox(
+ "atan2(0.5, -0.5)",
+ "DOUBLE NOT NULL",
+ 2.3562d,
+ 0.0001d);
+ tester.checkScalarApprox(
+ "atan2(cast(0.5 as decimal(1, 1)), cast(-0.5 as decimal(1, 1)))",
+ "DOUBLE NOT NULL",
+ 2.3562d,
+ 0.0001d);
+ tester.checkNull("atan2(cast(null as integer), -1)");
+ tester.checkNull("atan2(1, cast(null as double))");
+ }
+
+ @Test public void testCosFunc() {
+ tester.setFor(
+ SqlStdOperatorTable.COS);
+ tester.checkType("cos(1)", "DOUBLE NOT NULL");
+ tester.checkType("cos(cast(1 as float))", "DOUBLE NOT NULL");
+ tester.checkType(
+ "cos(case when false then 1 else null end)", "DOUBLE");
+ tester.checkFails(
+ "^cos('abc')^",
+ "Cannot apply 'COS' to arguments of type 'COS\\(<CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): 'COS\\(<NUMERIC>\\)'",
+ false);
+ tester.checkScalarApprox(
+ "cos(1)",
+ "DOUBLE NOT NULL",
+ 0.5403d,
+ 0.0001d);
+ tester.checkScalarApprox(
+ "cos(cast(1 as decimal(1, 0)))",
+ "DOUBLE NOT NULL",
+ 0.5403d,
+ 0.0001d);
+ tester.checkNull("cos(cast(null as integer))");
+ tester.checkNull("cos(cast(null as double))");
+ }
+
+ @Test public void testCotFunc() {
+ tester.setFor(
+ SqlStdOperatorTable.COT);
+ tester.checkType("cot(1)", "DOUBLE NOT NULL");
+ tester.checkType("cot(cast(1 as float))", "DOUBLE NOT NULL");
+ tester.checkType(
+ "cot(case when false then 1 else null end)", "DOUBLE");
+ tester.checkFails(
+ "^cot('abc')^",
+ "Cannot apply 'COT' to arguments of type 'COT\\(<CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): 'COT\\(<NUMERIC>\\)'",
+ false);
+ tester.checkScalarApprox(
+ "cot(1)",
+ "DOUBLE NOT NULL",
+ 0.6421d,
+ 0.0001d);
+ tester.checkScalarApprox(
+ "cot(cast(1 as decimal(1, 0)))",
+ "DOUBLE NOT NULL",
+ 0.6421d,
+ 0.0001d);
+ tester.checkNull("cot(cast(null as integer))");
+ tester.checkNull("cot(cast(null as double))");
+ }
+
+ @Test public void testDegreesFunc() {
+ tester.setFor(
+ SqlStdOperatorTable.DEGREES);
+ tester.checkType("degrees(1)", "DOUBLE NOT NULL");
+ tester.checkType("degrees(cast(1 as float))", "DOUBLE NOT NULL");
+ tester.checkType(
+ "degrees(case when false then 1 else null end)", "DOUBLE");
+ tester.checkFails(
+ "^degrees('abc')^",
+ "Cannot apply 'DEGREES' to arguments of type 'DEGREES\\(<CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): 'DEGREES\\(<NUMERIC>\\)'",
+ false);
+ tester.checkScalarApprox(
+ "degrees(1)",
+ "DOUBLE NOT NULL",
+ 57.2958d,
+ 0.0001d);
+ tester.checkScalarApprox(
+ "degrees(cast(1 as decimal(1, 0)))",
+ "DOUBLE NOT NULL",
+ 57.2958d,
+ 0.0001d);
+ tester.checkNull("degrees(cast(null as integer))");
+ tester.checkNull("degrees(cast(null as double))");
+ }
+
+ @Test public void testPiFunc() {
+ tester.setFor(SqlStdOperatorTable.PI);
+ tester.checkScalarApprox("PI", "DOUBLE NOT NULL", 3.1415d, 0.0001d);
+ tester.checkFails("^PI()^",
+ "No match found for function signature PI\\(\\)", false);
+ }
+
+ @Test public void testRadiansFunc() {
+ tester.setFor(
+ SqlStdOperatorTable.RADIANS);
+ tester.checkType("radians(42)", "DOUBLE NOT NULL");
+ tester.checkType("radians(cast(42 as float))", "DOUBLE NOT NULL");
+ tester.checkType(
+ "radians(case when false then 42 else null end)", "DOUBLE");
+ tester.checkFails(
+ "^radians('abc')^",
+ "Cannot apply 'RADIANS' to arguments of type 'RADIANS\\(<CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): 'RADIANS\\(<NUMERIC>\\)'",
+ false);
+ tester.checkScalarApprox(
+ "radians(42)",
+ "DOUBLE NOT NULL",
+ 0.7330d,
+ 0.0001d);
+ tester.checkScalarApprox(
+ "radians(cast(42 as decimal(2, 0)))",
+ "DOUBLE NOT NULL",
+ 0.7330d,
+ 0.0001d);
+ tester.checkNull("radians(cast(null as integer))");
+ tester.checkNull("radians(cast(null as double))");
+ }
+
+
+ @Test public void testRoundFunc() {
+ tester.setFor(
+ SqlStdOperatorTable.ROUND);
+ tester.checkType("round(42, -1)", "INTEGER NOT NULL");
+ tester.checkType("round(cast(42 as float), 1)", "FLOAT NOT NULL");
+ tester.checkType(
+ "round(case when false then 42 else null end, -1)", "INTEGER");
+ tester.checkFails(
+ "^round('abc', 'def')^",
+ "Cannot apply 'ROUND' to arguments of type 'ROUND\\(<CHAR\\(3\\)>, <CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): 'ROUND\\(<NUMERIC>, <INTEGER>\\)'",
+ false);
+ tester.checkScalar(
+ "round(42, -1)",
+ 40,
+ "INTEGER 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)");
+ }
+ @Test public void testSignFunc() {
+ tester.setFor(
+ SqlStdOperatorTable.SIGN);
+ tester.checkType("sign(1)", "INTEGER NOT NULL");
+ tester.checkType("sign(cast(1 as float))", "FLOAT NOT NULL");
+ tester.checkType(
+ "sign(case when false then 1 else null end)", "INTEGER");
+ tester.checkFails(
+ "^sign('abc')^",
+ "Cannot apply 'SIGN' to arguments of type 'SIGN\\(<CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): 'SIGN\\(<NUMERIC>\\)'",
+ false);
+ tester.checkScalar(
+ "sign(1)",
+ 1,
+ "INTEGER NOT NULL");
+ tester.checkScalar(
+ "sign(cast(-1 as decimal(1, 0)))",
+ BigDecimal.valueOf(-1),
+ "DECIMAL(1, 0) NOT NULL");
+ tester.checkScalar(
+ "sign(cast(0 as float))",
+ 0d,
+ "FLOAT NOT NULL");
+ tester.checkNull("sign(cast(null as integer))");
+ tester.checkNull("sign(cast(null as double))");
+ }
+
+ @Test public void testSinFunc() {
+ tester.setFor(
+ SqlStdOperatorTable.SIN);
+ tester.checkType("sin(1)", "DOUBLE NOT NULL");
+ tester.checkType("sin(cast(1 as float))", "DOUBLE NOT NULL");
+ tester.checkType(
+ "sin(case when false then 1 else null end)", "DOUBLE");
+ tester.checkFails(
+ "^sin('abc')^",
+ "Cannot apply 'SIN' to arguments of type 'SIN\\(<CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): 'SIN\\(<NUMERIC>\\)'",
+ false);
+ tester.checkScalarApprox(
+ "sin(1)",
+ "DOUBLE NOT NULL",
+ 0.8415d,
+ 0.0001d);
+ tester.checkScalarApprox(
+ "sin(cast(1 as decimal(1, 0)))",
+ "DOUBLE NOT NULL",
+ 0.8415d,
+ 0.0001d);
+ tester.checkNull("sin(cast(null as integer))");
+ tester.checkNull("sin(cast(null as double))");
+ }
+
+ @Test public void testTanFunc() {
+ tester.setFor(
+ SqlStdOperatorTable.TAN);
+ tester.checkType("tan(1)", "DOUBLE NOT NULL");
+ tester.checkType("tan(cast(1 as float))", "DOUBLE NOT NULL");
+ tester.checkType(
+ "tan(case when false then 1 else null end)", "DOUBLE");
+ tester.checkFails(
+ "^tan('abc')^",
+ "Cannot apply 'TAN' to arguments of type 'TAN\\(<CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): 'TAN\\(<NUMERIC>\\)'",
+ false);
+ tester.checkScalarApprox(
+ "tan(1)",
+ "DOUBLE NOT NULL",
+ 1.5574d,
+ 0.0001d);
+ tester.checkScalarApprox(
+ "tan(cast(1 as decimal(1, 0)))",
+ "DOUBLE NOT NULL",
+ 1.5574d,
+ 0.0001d);
+ tester.checkNull("tan(cast(null as integer))");
+ tester.checkNull("tan(cast(null as double))");
+ }
+
+ @Test public void testTruncateFunc() {
+ tester.setFor(
+ SqlStdOperatorTable.TRUNCATE);
+ tester.checkType("truncate(42, -1)", "INTEGER NOT NULL");
+ tester.checkType("truncate(cast(42 as float), 1)", "FLOAT NOT NULL");
+ tester.checkType(
+ "truncate(case when false then 42 else null end, -1)", "INTEGER");
+ tester.checkFails(
+ "^truncate('abc', 'def')^",
+ "Cannot apply 'TRUNCATE' to arguments of type 'TRUNCATE\\(<CHAR\\(3\\)>, <CHAR\\(3\\)>\\)'\\. Supported form\\(s\\): 'TRUNCATE\\(<NUMERIC>, <INTEGER>\\)'",
+ false);
+ tester.checkScalar(
+ "truncate(42, -1)",
+ 40,
+ "INTEGER 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)");
+ }
+
@Test public void testNullifFunc() {
tester.setFor(SqlStdOperatorTable.NULLIF, VM_EXPAND);
tester.checkNull("nullif(1,1)");
http://git-wip-us.apache.org/repos/asf/calcite/blob/17dc06fe/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
index 2e252c1..00e789d 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -505,6 +505,132 @@ public class SqlFunctionsTest {
assertEquals(-13000, SqlFunctions.round(-12845, 1000));
}
+ @Test public void testSTruncateDouble() {
+ assertEquals(12.345d, SqlFunctions.struncate(12.345d, 3), 0.001);
+ assertEquals(12.340d, SqlFunctions.struncate(12.345d, 2), 0.001);
+ assertEquals(12.300d, SqlFunctions.struncate(12.345d, 1), 0.001);
+ assertEquals(12.000d, SqlFunctions.struncate(12.999d, 0), 0.001);
+
+ assertEquals(-12.345d, SqlFunctions.struncate(-12.345d, 3), 0.001);
+ assertEquals(-12.340d, SqlFunctions.struncate(-12.345d, 2), 0.001);
+ assertEquals(-12.300d, SqlFunctions.struncate(-12.345d, 1), 0.001);
+ assertEquals(-12.000d, SqlFunctions.struncate(-12.999d, 0), 0.001);
+
+ assertEquals(12000d, SqlFunctions.struncate(12345d, -3), 0.001);
+ assertEquals(12000d, SqlFunctions.struncate(12000d, -3), 0.001);
+ assertEquals(12000d, SqlFunctions.struncate(12001d, -3), 0.001);
+ assertEquals(10000d, SqlFunctions.struncate(12000d, -4), 0.001);
+ assertEquals(0d, SqlFunctions.struncate(12000d, -5), 0.001);
+ assertEquals(11000d, SqlFunctions.struncate(11999d, -3), 0.001);
+
+ assertEquals(-12000d, SqlFunctions.struncate(-12345d, -3), 0.001);
+ assertEquals(-12000d, SqlFunctions.struncate(-12000d, -3), 0.001);
+ assertEquals(-11000d, SqlFunctions.struncate(-11999d, -3), 0.001);
+ assertEquals(-10000d, SqlFunctions.struncate(-12000d, -4), 0.001);
+ assertEquals(0d, SqlFunctions.struncate(-12000d, -5), 0.001);
+ }
+
+ @Test public void testSTruncateLong() {
+ assertEquals(12000d, SqlFunctions.struncate(12345L, -3), 0.001);
+ assertEquals(12000d, SqlFunctions.struncate(12000L, -3), 0.001);
+ assertEquals(12000d, SqlFunctions.struncate(12001L, -3), 0.001);
+ assertEquals(10000d, SqlFunctions.struncate(12000L, -4), 0.001);
+ assertEquals(0d, SqlFunctions.struncate(12000L, -5), 0.001);
+ assertEquals(11000d, SqlFunctions.struncate(11999L, -3), 0.001);
+
+ assertEquals(-12000d, SqlFunctions.struncate(-12345L, -3), 0.001);
+ assertEquals(-12000d, SqlFunctions.struncate(-12000L, -3), 0.001);
+ assertEquals(-11000d, SqlFunctions.struncate(-11999L, -3), 0.001);
+ assertEquals(-10000d, SqlFunctions.struncate(-12000L, -4), 0.001);
+ assertEquals(0d, SqlFunctions.struncate(-12000L, -5), 0.001);
+ }
+
+ @Test public void testSTruncateInt() {
+ assertEquals(12000d, SqlFunctions.struncate(12345, -3), 0.001);
+ assertEquals(12000d, SqlFunctions.struncate(12000, -3), 0.001);
+ assertEquals(12000d, SqlFunctions.struncate(12001, -3), 0.001);
+ assertEquals(10000d, SqlFunctions.struncate(12000, -4), 0.001);
+ assertEquals(0d, SqlFunctions.struncate(12000, -5), 0.001);
+ assertEquals(11000d, SqlFunctions.struncate(11999, -3), 0.001);
+
+ assertEquals(-12000d, SqlFunctions.struncate(-12345, -3), 0.001);
+ assertEquals(-12000d, SqlFunctions.struncate(-12000, -3), 0.001);
+ assertEquals(-11000d, SqlFunctions.struncate(-11999, -3), 0.001);
+ assertEquals(-10000d, SqlFunctions.struncate(-12000, -4), 0.001);
+ assertEquals(0d, SqlFunctions.struncate(-12000, -5), 0.001);
+ }
+
+ @Test public void testSRoundDouble() {
+ assertEquals(12.345d, SqlFunctions.sround(12.345d, 3), 0.001);
+ assertEquals(12.350d, SqlFunctions.sround(12.345d, 2), 0.001);
+ assertEquals(12.300d, SqlFunctions.sround(12.345d, 1), 0.001);
+ assertEquals(13.000d, SqlFunctions.sround(12.999d, 2), 0.001);
+ assertEquals(13.000d, SqlFunctions.sround(12.999d, 1), 0.001);
+ assertEquals(13.000d, SqlFunctions.sround(12.999d, 0), 0.001);
+
+ assertEquals(-12.345d, SqlFunctions.sround(-12.345d, 3), 0.001);
+ assertEquals(-12.350d, SqlFunctions.sround(-12.345d, 2), 0.001);
+ assertEquals(-12.300d, SqlFunctions.sround(-12.345d, 1), 0.001);
+ assertEquals(-13.000d, SqlFunctions.sround(-12.999d, 2), 0.001);
+ assertEquals(-13.000d, SqlFunctions.sround(-12.999d, 1), 0.001);
+ assertEquals(-13.000d, SqlFunctions.sround(-12.999d, 0), 0.001);
+
+ assertEquals(12350d, SqlFunctions.sround(12345d, -1), 0.001);
+ assertEquals(12300d, SqlFunctions.sround(12345d, -2), 0.001);
+ assertEquals(12000d, SqlFunctions.sround(12345d, -3), 0.001);
+ assertEquals(12000d, SqlFunctions.sround(12000d, -3), 0.001);
+ assertEquals(12000d, SqlFunctions.sround(12001d, -3), 0.001);
+ assertEquals(10000d, SqlFunctions.sround(12000d, -4), 0.001);
+ assertEquals(0d, SqlFunctions.sround(12000d, -5), 0.001);
+ assertEquals(12000d, SqlFunctions.sround(11999d, -3), 0.001);
+
+ assertEquals(-12350d, SqlFunctions.sround(-12345d, -1), 0.001);
+ assertEquals(-12300d, SqlFunctions.sround(-12345d, -2), 0.001);
+ assertEquals(-12000d, SqlFunctions.sround(-12345d, -3), 0.001);
+ assertEquals(-12000d, SqlFunctions.sround(-12000d, -3), 0.001);
+ assertEquals(-12000d, SqlFunctions.sround(-11999d, -3), 0.001);
+ assertEquals(-10000d, SqlFunctions.sround(-12000d, -4), 0.001);
+ assertEquals(0d, SqlFunctions.sround(-12000d, -5), 0.001);
+ }
+
+ @Test public void testSRoundLong() {
+ assertEquals(12350d, SqlFunctions.sround(12345L, -1), 0.001);
+ assertEquals(12300d, SqlFunctions.sround(12345L, -2), 0.001);
+ assertEquals(12000d, SqlFunctions.sround(12345L, -3), 0.001);
+ assertEquals(12000d, SqlFunctions.sround(12000L, -3), 0.001);
+ assertEquals(12000d, SqlFunctions.sround(12001L, -3), 0.001);
+ assertEquals(10000d, SqlFunctions.sround(12000L, -4), 0.001);
+ assertEquals(0d, SqlFunctions.sround(12000L, -5), 0.001);
+ assertEquals(12000d, SqlFunctions.sround(11999L, -3), 0.001);
+
+ assertEquals(-12350d, SqlFunctions.sround(-12345L, -1), 0.001);
+ assertEquals(-12300d, SqlFunctions.sround(-12345L, -2), 0.001);
+ assertEquals(-12000d, SqlFunctions.sround(-12345L, -3), 0.001);
+ assertEquals(-12000d, SqlFunctions.sround(-12000L, -3), 0.001);
+ assertEquals(-12000d, SqlFunctions.sround(-11999L, -3), 0.001);
+ assertEquals(-10000d, SqlFunctions.sround(-12000L, -4), 0.001);
+ assertEquals(0d, SqlFunctions.sround(-12000L, -5), 0.001);
+ }
+
+ @Test public void testSRoundInt() {
+ assertEquals(12350d, SqlFunctions.sround(12345, -1), 0.001);
+ assertEquals(12300d, SqlFunctions.sround(12345, -2), 0.001);
+ assertEquals(12000d, SqlFunctions.sround(12345, -3), 0.001);
+ assertEquals(12000d, SqlFunctions.sround(12000, -3), 0.001);
+ assertEquals(12000d, SqlFunctions.sround(12001, -3), 0.001);
+ assertEquals(10000d, SqlFunctions.sround(12000, -4), 0.001);
+ assertEquals(0d, SqlFunctions.sround(12000, -5), 0.001);
+ assertEquals(12000d, SqlFunctions.sround(11999, -3), 0.001);
+
+ assertEquals(-12350d, SqlFunctions.sround(-12345, -1), 0.001);
+ assertEquals(-12300d, SqlFunctions.sround(-12345, -2), 0.001);
+ assertEquals(-12000d, SqlFunctions.sround(-12345, -3), 0.001);
+ assertEquals(-12000d, SqlFunctions.sround(-12000, -3), 0.001);
+ assertEquals(-12000d, SqlFunctions.sround(-11999, -3), 0.001);
+ assertEquals(-10000d, SqlFunctions.sround(-12000, -4), 0.001);
+ assertEquals(0d, SqlFunctions.sround(-12000, -5), 0.001);
+ }
+
@Test public void testByteString() {
final byte[] bytes = {(byte) 0xAB, (byte) 0xFF};
final ByteString byteString = new ByteString(bytes);
http://git-wip-us.apache.org/repos/asf/calcite/blob/17dc06fe/core/src/test/resources/sql/misc.iq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/misc.iq b/core/src/test/resources/sql/misc.iq
index eb4a6ce..36a2f93 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -1720,4 +1720,26 @@ from (values 1, 2, 3, 4, 5) as t(i);
!ok
+# PI function
+values pi;
++-------------------+
+| PI |
++-------------------+
+| 3.141592653589793 |
++-------------------+
+(1 row)
+
+!ok
+
+# DEGREES function
+values (degrees(pi), degrees(-pi / 2));
++--------+--------+
+| EXPR$0 | EXPR$1 |
++--------+--------+
+| 180.0 | -90.0 |
++--------+--------+
+(1 row)
+
+!ok
+
# End misc.iq
http://git-wip-us.apache.org/repos/asf/calcite/blob/17dc06fe/site/_docs/reference.md
----------------------------------------------------------------------
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 744be2e..8c6e74c 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -961,10 +961,24 @@ The operator precedence and associativity, highest to lowest.
| LN(numeric) | Returns the natural logarithm (base *e*) of *numeric*
| LOG10(numeric) | Returns the base 10 logarithm of *numeric*
| EXP(numeric) | Returns *e* raised to the power of *numeric*
-| CEIL(numeric) | Rounds *numeric* up, and returns the smallest number that is greater than or equal to *numeric*
-| FLOOR(numeric) | Rounds *numeric* down, and returns the largest number that is less than or equal to *numeric*
+| CEIL(numeric) | Rounds *numeric* up, returning the smallest integer that is greater than or equal to *numeric*
+| FLOOR(numeric) | Rounds *numeric* down, returning the largest integer that is less than or equal to *numeric*
| RAND([seed]) | Generates a random double between 0 and 1 inclusive, optionally initializing the random number generator with *seed*
| RAND_INTEGER([seed, ] numeric) | Generates a random integer between 0 and *numeric* - 1 inclusive, optionally initializing the random number generator with *seed*
+| ACOS(numeric) | Returns the arc cosine of *numeric*
+| ASIN(numeric) | Returns the arc sine of *numeric*
+| ATAN(numeric) | Returns the arc tangent of *numeric*
+| ATAN2(numeric, numeric) | Returns the arc tangent of the *numeric* coordinates
+| COS(numeric) | Returns the cosine of *numeric*
+| COT(numeric) | Returns the cotangent of *numeric*
+| 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
+| 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
### Character string operators and functions
@@ -1078,35 +1092,32 @@ See also: UNNEST relational operator converts a collection to a relation.
#### Numeric
-| Operator syntax | Description
-|:------------------------------ |:-----------
-| {fn ABS(numeric)} | Returns the absolute value of *numeric*
-| {fn EXP(numeric)} | Returns *e* raised to the power of *numeric*
-| {fn LOG(numeric)} | Returns the natural logarithm (base *e*) of *numeric*
-| {fn LOG10(numeric)} | Returns the base-10 logarithm of *numeric*
-| {fn MOD(numeric1, numeric2)} | Returns the remainder (modulus) of *numeric1* divided by *numeric2*. The result is negative only if *numeric1* is negative
-| {fn POWER(numeric1, numeric2)} | Returns *numeric1* raised to the power of *numeric2*
-
-Not implemented:
-
-* {fn ACOS(numeric)} - Returns the arc cosine of *numeric*
-* {fn ASIN(numeric)} - Returns the arc sine of *numeric*
-* {fn ATAN(numeric)} - Returns the arc tangent of *numeric*
-* {fn ATAN2(numeric, numeric)}
-* {fn CEILING(numeric)} - Rounds *numeric* up, and returns the smallest number that is greater than or equal to *numeric*
-* {fn COS(numeric)} - Returns the cosine of *numeric*
-* {fn COT(numeric)}
-* {fn DEGREES(numeric)} - Converts *numeric* from radians to degrees
-* {fn FLOOR(numeric)} - Rounds *numeric* down, and returns the largest number that is less than or equal to *numeric*
-* {fn PI()} - Returns a value that is closer than any other value to *pi*
-* {fn RADIANS(numeric)} - Converts *numeric* from degrees to radians
-* {fn RAND(numeric)}
-* {fn ROUND(numeric, numeric)}
-* {fn SIGN(numeric)}
-* {fn SIN(numeric)} - Returns the sine of *numeric*
-* {fn SQRT(numeric)} - Returns the square root of *numeric*
-* {fn TAN(numeric)} - Returns the tangent of *numeric*
-* {fn TRUNCATE(numeric, numeric)}
+| Operator syntax | Description
+|:--------------------------------- |:-----------
+| {fn ABS(numeric)} | Returns the absolute value of *numeric*
+| {fn ACOS(numeric)} | Returns the arc cosine of *numeric*
+| {fn ASIN(numeric)} | Returns the arc sine of *numeric*
+| {fn ATAN(numeric)} | Returns the arc tangent of *numeric*
+| {fn ATAN2(numeric, numeric)} | Returns the arc tangent of the *numeric* coordinates
+| {fn CEILING(numeric)} | Rounds *numeric* up, and returns the smallest number that is greater than or equal to *numeric*
+| {fn COS(numeric)} | Returns the cosine of *numeric*
+| {fn COT(numeric)} | Returns the cotangent of *numeric*
+| {fn DEGREES(numeric)} | Converts *numeric* from radians to degrees
+| {fn EXP(numeric)} | Returns *e* raised to the power of *numeric*
+| {fn FLOOR(numeric)} | Rounds *numeric* down, and returns the largest number that is less than or equal to *numeric*
+| {fn LOG(numeric)} | Returns the natural logarithm (base *e*) of *numeric*
+| {fn LOG10(numeric)} | Returns the base-10 logarithm of *numeric*
+| {fn MOD(numeric1, numeric2)} | Returns the remainder (modulus) of *numeric1* divided by *numeric2*. The result is negative only if *numeric1* is negative
+| {fn PI()} | Returns a value that is closer than any other value to *pi*
+| {fn POWER(numeric1, numeric2)} | Returns *numeric1* raised to the power of *numeric2*
+| {fn RADIANS(numeric)} | Converts *numeric* from degrees to radians
+| {fn RAND(numeric)} | Returns a random double using *numeric* as the seed value
+| {fn ROUND(numeric1, numeric2)} | Rounds *numeric1* to *numeric2* places right to the decimal point
+| {fn SIGN(numeric)} | Returns the signum of *numeric*
+| {fn SIN(numeric)} | Returns the sine of *numeric*
+| {fn SQRT(numeric)} | Returns the square root of *numeric*
+| {fn TAN(numeric)} | Returns the tangent of *numeric*
+| {fn TRUNCATE(numeric1, numeric2)} | Truncates *numeric1* to *numeric2* places right to the decimal point
#### String