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 2022/11/06 01:05:16 UTC
[calcite] 01/01: Add "mssql" (Microsoft SQL Server) function library
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit f7bf867ba96578e7cc40e2aac1adf47015d06913
Author: Julian Hyde <jh...@apache.org>
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 18ed78622a..87d5aae262 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;
@@ -46,6 +48,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;
@@ -76,6 +79,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 d2403ad204..18d145e667 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 66daae80bd..15abb9b23b 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2565,6 +2565,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).
@@ -2589,7 +2590,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
@@ -2644,6 +2649,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: