This is an automated email from the ASF dual-hosted git repository.
github-bot pushed a commit to branch site
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/site by this push:
new 07421a67a0 Add "mssql" (Microsoft SQL Server) function library
07421a67a0 is described below
commit 07421a67a021e68d7a6772f9f94b8d994000a54f
Author: Julian Hyde <[email protected]>
AuthorDate: Wed Nov 2 15:19:14 2022 -0700
Add "mssql" (Microsoft SQL Server) function library
Add functions DATEADD, DATEDIFF, DATEPART, DATE_PART as
aliases of TIMESTAMPADD, TIMESTAMPDIFF, EXTRACT. (Not fully
implemented yet.)
---
.../apache/calcite/sql/fun/SqlExtractFunction.java | 4 +--
.../org/apache/calcite/sql/fun/SqlLibrary.java | 3 ++
.../calcite/sql/fun/SqlLibraryOperators.java | 41 ++++++++++++++++++++++
.../calcite/sql/fun/SqlStdOperatorTable.java | 8 +++--
.../calcite/sql/fun/SqlTimestampAddFunction.java | 4 +--
.../calcite/sql/fun/SqlTimestampDiffFunction.java | 33 ++++++++---------
site/_docs/reference.md | 10 ++++++
7 files changed, 80 insertions(+), 23 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
index 97e6e4951b..a77792a16e 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
@@ -41,8 +41,8 @@ public class SqlExtractFunction extends SqlFunction {
// SQL2003, Part 2, Section 4.4.3 - extract returns a exact numeric
// TODO: Return type should be decimal for seconds
- public SqlExtractFunction() {
- super("EXTRACT", SqlKind.EXTRACT, ReturnTypes.BIGINT_NULLABLE, null,
+ public SqlExtractFunction(String name) {
+ super(name, SqlKind.EXTRACT, ReturnTypes.BIGINT_NULLABLE, null,
OperandTypes.INTERVALINTERVAL_INTERVALDATETIME,
SqlFunctionCategory.SYSTEM);
}
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibrary.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibrary.java
index e9418745e7..d69e3985cf 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibrary.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibrary.java
@@ -55,6 +55,9 @@ public enum SqlLibrary {
HIVE("h", "hive"),
/** A collection of operators that are in MySQL but not in standard SQL. */
MYSQL("m", "mysql"),
+ /** A collection of operators that are in Microsoft SQL Server (MSSql) but
not
+ * in standard SQL. */
+ MSSQL("q", "mssql"),
/** A collection of operators that are in Oracle but not in standard SQL. */
ORACLE("o", "oracle"),
/** A collection of operators that are in PostgreSQL but not in standard
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 794270682c..8b67b906fc 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
@@ -20,6 +20,7 @@ import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlBinaryOperator;
+import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlFunctionCategory;
import org.apache.calcite.sql.SqlKind;
@@ -28,6 +29,7 @@ import org.apache.calcite.sql.SqlOperatorBinding;
import org.apache.calcite.sql.SqlOperatorTable;
import org.apache.calcite.sql.SqlSpecialOperator;
import org.apache.calcite.sql.SqlSyntax;
+import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.type.InferTypes;
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
@@ -49,6 +51,7 @@ import java.util.List;
import static org.apache.calcite.sql.fun.SqlLibrary.BIG_QUERY;
import static org.apache.calcite.sql.fun.SqlLibrary.HIVE;
+import static org.apache.calcite.sql.fun.SqlLibrary.MSSQL;
import static org.apache.calcite.sql.fun.SqlLibrary.MYSQL;
import static org.apache.calcite.sql.fun.SqlLibrary.ORACLE;
import static org.apache.calcite.sql.fun.SqlLibrary.POSTGRESQL;
@@ -79,6 +82,44 @@ public abstract class SqlLibraryOperators {
OperandTypes.CHARACTER_CHARACTER_DATETIME,
SqlFunctionCategory.TIMEDATE);
+ /** The "DATEADD(timeUnit, numeric, datetime)" function
+ * (Microsoft SQL Server, Redshift, Snowflake). */
+ @LibraryOperator(libraries = {MSSQL, POSTGRESQL})
+ public static final SqlFunction DATEADD =
+ new SqlTimestampAddFunction("DATEADD");
+
+ /** The "DATEDIFF(timeUnit, datetime, datetime2)" function
+ * (Microsoft SQL Server, Redshift, Snowflake).
+ *
+ * <p>MySQL has "DATEDIFF(date, date2)" and "TIMEDIFF(time, time2)" functions
+ * but Calcite does not implement these because they have no "timeUnit"
+ * argument. */
+ @LibraryOperator(libraries = {MSSQL, POSTGRESQL})
+ public static final SqlFunction DATEDIFF =
+ new SqlTimestampDiffFunction("DATEDIFF");
+
+ /** The "DATE_PART(timeUnit, datetime)" function
+ * (Databricks, Postgres, Redshift, Snowflake). */
+ @LibraryOperator(libraries = {POSTGRESQL})
+ public static final SqlFunction DATE_PART =
+ new SqlExtractFunction("DATE_PART") {
+ @Override public void unparse(SqlWriter writer, SqlCall call,
+ int leftPrec, int rightPrec) {
+ getSyntax().unparse(writer, this, call, leftPrec, rightPrec);
+ }
+ };
+
+ /** The "DATEPART(timeUnit, datetime)" function
+ * (Microsoft SQL Server). */
+ @LibraryOperator(libraries = {MSSQL})
+ public static final SqlFunction DATEPART =
+ new SqlExtractFunction("DATEPART") {
+ @Override public void unparse(SqlWriter writer, SqlCall call,
+ int leftPrec, int rightPrec) {
+ getSyntax().unparse(writer, this, call, leftPrec, rightPrec);
+ }
+ };
+
/** Return type inference for {@code DECODE}. */
private static final SqlReturnTypeInference DECODE_RETURN_TYPE =
opBinding -> {
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 6e5c72e139..5c31f0979b 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
@@ -1968,10 +1968,12 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
new SqlCurrentDateFunction();
/** The <code>TIMESTAMPADD</code> function. */
- public static final SqlFunction TIMESTAMP_ADD = new
SqlTimestampAddFunction();
+ public static final SqlFunction TIMESTAMP_ADD =
+ new SqlTimestampAddFunction("TIMESTAMPADD");
/** The <code>TIMESTAMPDIFF</code> function. */
- public static final SqlFunction TIMESTAMP_DIFF = new
SqlTimestampDiffFunction();
+ public static final SqlFunction TIMESTAMP_DIFF =
+ new SqlTimestampDiffFunction("TIMESTAMPDIFF");
/**
* Use of the <code>IN_FENNEL</code> operator forces the argument to be
@@ -2017,7 +2019,7 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
* <code>EXTRACT(HOUR FROM INTERVAL '364 23:59:59')</code> returns <code>
* 23</code>
*/
- public static final SqlFunction EXTRACT = new SqlExtractFunction();
+ public static final SqlFunction EXTRACT = new SqlExtractFunction("EXTRACT");
/**
* The SQL <code>YEAR</code> operator. Returns the Year
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampAddFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampAddFunction.java
index 1af0056acf..6870b5bdb2 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampAddFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampAddFunction.java
@@ -103,8 +103,8 @@ public class SqlTimestampAddFunction extends SqlFunction {
}
/** Creates a SqlTimestampAddFunction. */
- SqlTimestampAddFunction() {
- super("TIMESTAMPADD", SqlKind.TIMESTAMP_ADD, RETURN_TYPE_INFERENCE, null,
+ SqlTimestampAddFunction(String name) {
+ super(name, SqlKind.TIMESTAMP_ADD, RETURN_TYPE_INFERENCE, null,
OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.INTEGER,
SqlTypeFamily.DATETIME),
SqlFunctionCategory.TIMEDATE);
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampDiffFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampDiffFunction.java
index 3b62f56f35..c7bfdcdb6a 100644
---
a/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampDiffFunction.java
+++
b/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampDiffFunction.java
@@ -17,12 +17,13 @@
package org.apache.calcite.sql.fun;
import org.apache.calcite.avatica.util.TimeUnit;
+import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
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.type.OperandTypes;
-import org.apache.calcite.sql.type.SqlReturnTypeInference;
import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeName;
@@ -54,23 +55,23 @@ import org.apache.calcite.sql.type.SqlTypeName;
* interval.
*/
class SqlTimestampDiffFunction extends SqlFunction {
- /** Creates a SqlTimestampDiffFunction. */
- private static final SqlReturnTypeInference RETURN_TYPE_INFERENCE =
- opBinding -> {
- final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
- SqlTypeName sqlTypeName =
- opBinding.getOperandLiteralValue(0, TimeUnit.class) ==
TimeUnit.NANOSECOND
- ? SqlTypeName.BIGINT
- : SqlTypeName.INTEGER;
- return typeFactory.createTypeWithNullability(
- typeFactory.createSqlType(sqlTypeName),
- opBinding.getOperandType(1).isNullable()
+ private static RelDataType inferReturnType2(SqlOperatorBinding opBinding) {
+ final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
+ TimeUnit timeUnit = opBinding.getOperandLiteralValue(0, TimeUnit.class);
+ SqlTypeName sqlTypeName =
+ timeUnit == TimeUnit.NANOSECOND
+ ? SqlTypeName.BIGINT
+ : SqlTypeName.INTEGER;
+ return typeFactory.createTypeWithNullability(
+ typeFactory.createSqlType(sqlTypeName),
+ opBinding.getOperandType(1).isNullable()
|| opBinding.getOperandType(2).isNullable());
- };
+ }
- SqlTimestampDiffFunction() {
- super("TIMESTAMPDIFF", SqlKind.TIMESTAMP_DIFF,
- RETURN_TYPE_INFERENCE, null,
+ /** Creates a SqlTimestampDiffFunction. */
+ SqlTimestampDiffFunction(String name) {
+ super(name, SqlKind.TIMESTAMP_DIFF,
+ SqlTimestampDiffFunction::inferReturnType2, null,
OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.DATETIME,
SqlTypeFamily.DATETIME),
SqlFunctionCategory.TIMEDATE);
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index e49c5fa6dd..cbc3662d4f 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2561,6 +2561,7 @@ The 'C' (compatibility) column contains value:
* 'b' for Google BigQuery ('fun=bigquery' in the connect string),
* 'h' for Apache Hive ('fun=hive' in the connect string),
* 'm' for MySQL ('fun=mysql' in the connect string),
+* 'q' for Microsoft SQL Server ('fun=mssql' in the connect string),
* 'o' for Oracle ('fun=oracle' in the connect string),
* 'p' for PostgreSQL ('fun=postgresql' in the connect string),
* 's' for Apache Spark ('fun=spark' in the connect string).
@@ -2585,7 +2586,11 @@ semantics.
| 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'
| b | DATE(string) | Equivalent to
`CAST(string AS DATE)`
+| p q | DATEADD(timeUnit, integer, datetime) | Equivalent to
`TIMESTAMPADD(timeUnit, integer, datetime)`
+| p q | DATEDIFF(timeUnit, datetime, datetime2) | Equivalent to
`TIMESTAMPDIFF(timeUnit, datetime, datetime2)`
+| q | DATEPART(timeUnit, datetime) | Equivalent to
`EXTRACT(timeUnit FROM datetime)`
| b | DATE_FROM_UNIX_DATE(integer) | Returns the DATE that
is *integer* days after 1970-01-01
+| p | DATE_PART(timeUnit, datetime) | Equivalent to
`EXTRACT(timeUnit FROM datetime)`
| o | DECODE(value, value1, result1 [, valueN, resultN ]* [, default ]) |
Compares *value* to each *valueN* value one by one; if *value* is equal to a
*valueN*, returns the corresponding *resultN*, else returns *default*, or NULL
if *default* is not specified
| p | DIFFERENCE(string, string) | Returns a measure of
the similarity of two strings, namely the number of character positions that
their `SOUNDEX` values have in common: 4 if the `SOUNDEX` values are same and 0
if the `SOUNDEX` values are totally different
| o | EXTRACT(xml, xpath, [, namespaces ]) | Returns the xml
fragment of the element or elements matched by the XPath expression. The
optional namespace value that specifies a default mapping or namespace mapping
for prefixes, which is used when evaluating the XPath expression
@@ -2638,6 +2643,11 @@ semantics.
Note:
+* Calcite has no Redshift library, so the Postgres library
+ is used instead. The functions `DATEADD`, `DATEDIFF` are
+ implemented in Redshift and not Postgres but nevertheless
+ appear in Calcite's Postgres library
+* Functions `DATEADD`, `DATEDIFF`, `DATE_PART` require the Babel parser
* `JSON_TYPE` / `JSON_DEPTH` / `JSON_PRETTY` / `JSON_STORAGE_SIZE` return null
if the argument is null
* `JSON_LENGTH` / `JSON_KEYS` / `JSON_REMOVE` return null if the first
argument is null
* `JSON_TYPE` generally returns an upper-case string flag indicating the type
of the JSON input. Currently supported supported type flags are:
