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 <[email protected]>
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);
