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 <[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 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:
