This is an automated email from the ASF dual-hosted git repository. rubenql pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push: new dbefd68825 [CALCITE-5602] Implement CSC and SEC functions dbefd68825 is described below commit dbefd6882585e0044cd23784dd1bb2352554d391 Author: zoudan <zou...@bytedance.com> AuthorDate: Mon May 8 16:08:20 2023 +0800 [CALCITE-5602] Implement CSC and SEC functions --- .../calcite/adapter/enumerable/RexImpTable.java | 4 ++ .../org/apache/calcite/runtime/SqlFunctions.java | 22 +++++++++++ .../calcite/sql/fun/SqlLibraryOperators.java | 14 +++++++ site/_docs/reference.md | 2 + .../org/apache/calcite/test/SqlOperatorTest.java | 44 ++++++++++++++++++++++ 5 files changed, 86 insertions(+) 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 16a9df8632..2bda9908bb 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 @@ -130,6 +130,7 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.CONCAT2; import static org.apache.calcite.sql.fun.SqlLibraryOperators.CONCAT_FUNCTION; import static org.apache.calcite.sql.fun.SqlLibraryOperators.COSH; import static org.apache.calcite.sql.fun.SqlLibraryOperators.COTH; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.CSC; import static org.apache.calcite.sql.fun.SqlLibraryOperators.CSCH; import static org.apache.calcite.sql.fun.SqlLibraryOperators.DATE; import static org.apache.calcite.sql.fun.SqlLibraryOperators.DATEADD; @@ -184,6 +185,7 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.RPAD; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_CAST; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_OFFSET; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_ORDINAL; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.SEC; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SECH; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SHA1; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SHA256; @@ -533,11 +535,13 @@ public class RexImpTable { defineMethod(COSH, "cosh", NullPolicy.STRICT); defineMethod(COT, "cot", NullPolicy.STRICT); defineMethod(COTH, "coth", NullPolicy.STRICT); + defineMethod(CSC, "csc", NullPolicy.STRICT); defineMethod(CSCH, "csch", NullPolicy.STRICT); defineMethod(DEGREES, "degrees", NullPolicy.STRICT); defineMethod(POW, "power", NullPolicy.STRICT); defineMethod(RADIANS, "radians", NullPolicy.STRICT); defineMethod(ROUND, "sround", NullPolicy.STRICT); + defineMethod(SEC, "sec", NullPolicy.STRICT); defineMethod(SECH, "sech", NullPolicy.STRICT); defineMethod(SIGN, "sign", NullPolicy.STRICT); defineMethod(SIN, "sin", NullPolicy.STRICT); 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 52c2da6878..29ba098dda 100644 --- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java +++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java @@ -2138,6 +2138,28 @@ public class SqlFunctions { return Math.tanh(b); } + // CSC + /** SQL <code>CSC</code> operator applied to BigDecimal values. */ + public static double csc(BigDecimal b0) { + return 1.0d / Math.sin(b0.doubleValue()); + } + + /** SQL <code>CSC</code> operator applied to double values. */ + public static double csc(double b0) { + return 1.0d / Math.sin(b0); + } + + // SEC + /** SQL <code>SEC</code> operator applied to BigDecimal values. */ + public static double sec(BigDecimal b0) { + return 1.0d / Math.cos(b0.doubleValue()); + } + + /** SQL <code>SEC</code> operator applied to double values. */ + public static double sec(double b0) { + return 1.0d / Math.cos(b0); + } + // Helpers /** Helper for implementing MIN. Somewhat similar to LEAST operator. */ diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java index 4ae48490e5..fe3269d18a 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java @@ -1288,6 +1288,20 @@ public abstract class SqlLibraryOperators { OperandTypes.NUMERIC, SqlFunctionCategory.NUMERIC); + @LibraryOperator(libraries = {ALL}) + public static final SqlFunction CSC = + SqlBasicFunction.create("CSC", + ReturnTypes.DOUBLE_NULLABLE, + OperandTypes.NUMERIC, + SqlFunctionCategory.NUMERIC); + + @LibraryOperator(libraries = {ALL}) + public static final SqlFunction SEC = + SqlBasicFunction.create("SEC", + ReturnTypes.DOUBLE_NULLABLE, + OperandTypes.NUMERIC, + SqlFunctionCategory.NUMERIC); + @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL}) public static final SqlFunction MD5 = SqlBasicFunction.create("MD5", diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 47262a2d71..64adc009df 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -2666,6 +2666,7 @@ BigQuery's type system uses confusingly different names for types and functions: | p | CONVERT_TIMEZONE(tz1, tz2, datetime) | Converts the timezone of *datetime* from *tz1* to *tz2* | * | COSH(numeric) | Returns the hyperbolic cosine of *numeric* | * | COTH(numeric) | Returns the hyperbolic cotangent of *numeric* +| * | CSC(numeric) | Returns the cosecant of *numeric* in radians | * | CSCH(numeric) | Returns the hyperbolic cosecant of *numeric* | b | CURRENT_DATETIME([ timeZone ]) | Returns the current time as a TIMESTAMP from *timezone* | m | DAYNAME(datetime) | Returns the name, in the connection's locale, of the weekday in *datetime*; for example, it returns '星期日' for both DATE '2020-02-10' and TIMESTAMP '2020-02-10 10:10:10' @@ -2747,6 +2748,7 @@ BigQuery's type system uses confusingly different names for types and functions: | b | SAFE_CAST(value AS type) | Converts *value* to *type*, returning NULL if conversion fails | b | SAFE_OFFSET(index) | Similar to `OFFSET` except null is returned if *index* is out of bounds | b | SAFE_ORDINAL(index) | Similar to `OFFSET` except *index* begins at 1 and null is returned if *index* is out of bounds +| * | SEC(numeric) | Returns the secant of *numeric* in radians | * | SECH(numeric) | Returns the hyperbolic secant of *numeric* | b m p | SHA1(string) | Calculates a SHA-1 hash value of *string* and returns it as a hex string | b p | SHA256(string) | Calculates a SHA-256 hash value of *string* and returns it as a hex string diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java index fe4a8cac0c..1d231fd4cb 100644 --- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java +++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java @@ -5661,6 +5661,50 @@ public class SqlOperatorTest { f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE), consumer); } + @Test void testCscFunc() { + final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.CSC); + f0.checkFails("^csc(1)^", + "No match found for function signature CSC\\(<NUMERIC>\\)", + false); + final Consumer<SqlOperatorFixture> consumer = f -> { + f.checkType("csc(cast (1 as float))", "DOUBLE NOT NULL"); + f.checkType("csc('a')", "DOUBLE NOT NULL"); + f.checkScalarApprox("csc(100)", "DOUBLE NOT NULL", + isWithin(-1.9748d, 0.0001d)); + f.checkScalarApprox("csc(cast(10 as float))", "DOUBLE NOT NULL", + isWithin(-1.8381d, 0.0001d)); + f.checkScalarApprox("csc(cast(10 as decimal))", "DOUBLE NOT NULL", + isWithin(-1.8381d, 0.0001d)); + f.checkScalarApprox("csc(-1)", "DOUBLE NOT NULL", + isWithin(-1.1883d, 0.0001d)); + f.checkNull("csc(cast(null as double))"); + f.checkNull("csc(cast(null as integer))"); + }; + f0.forEachLibrary(list(SqlLibrary.ALL), consumer); + } + + @Test void testSecFunc() { + final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.SEC); + f0.checkFails("^sec(1)^", + "No match found for function signature SEC\\(<NUMERIC>\\)", + false); + final Consumer<SqlOperatorFixture> consumer = f -> { + f.checkType("sec(cast (1 as float))", "DOUBLE NOT NULL"); + f.checkType("sec('a')", "DOUBLE NOT NULL"); + f.checkScalarApprox("sec(100)", "DOUBLE NOT NULL", + isWithin(1.1596d, 0.0001d)); + f.checkScalarApprox("sec(cast(10 as float))", "DOUBLE NOT NULL", + isWithin(-1.1917d, 0.0001d)); + f.checkScalarApprox("sec(cast(10 as decimal))", "DOUBLE NOT NULL", + isWithin(-1.1917d, 0.0001d)); + f.checkScalarApprox("sec(-1)", "DOUBLE NOT NULL", + isWithin(1.8508d, 0.0001d)); + f.checkNull("sec(cast(null as double))"); + f.checkNull("sec(cast(null as integer))"); + }; + f0.forEachLibrary(list(SqlLibrary.ALL), consumer); + } + @Test void testDegreesFunc() { final SqlOperatorFixture f = fixture(); f.setFor(SqlStdOperatorTable.DEGREES, VmName.EXPAND);