This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit d839a57fc35a4bd46d1ef4d03e67e22db2e802d7
Author: Julian Hyde <jh...@apache.org>
AuthorDate: Thu Jun 25 09:34:42 2020 -0700
[CALCITE-4089] In Babel, allow 'CAST(integer AS DATE)' even though it is
illegal in Calcite SQL
Add BIG_QUERY (abbreviation 'b', and 'fun=bigquery' in
connect string) as a function library.
Add functions: DATE, DATE_FROM_UNIX_DATE, TIMESTAMP_MICROS,
TIMESTAMP_MILLIS, TIMESTAMP_SECONDS, UNIX_DATE, UNIX_MICROS,
UNIX_MILLIS, UNIX_SECONDS.
---
.../org/apache/calcite/test/BabelQuidemTest.java | 10 ++
babel/src/test/resources/sql/big-query.iq | 137 +++++++++++++++++++++
.../calcite/adapter/enumerable/EnumUtils.java | 8 +-
.../calcite/adapter/enumerable/RexImpTable.java | 18 +++
.../calcite/config/CalciteConnectionProperty.java | 4 +-
.../org/apache/calcite/runtime/SqlFunctions.java | 49 +++++++-
.../org/apache/calcite/sql/fun/SqlLibrary.java | 20 +--
.../calcite/sql/fun/SqlLibraryOperators.java | 72 +++++++++++
.../org/apache/calcite/sql/type/OperandTypes.java | 6 +
.../org/apache/calcite/sql/type/ReturnTypes.java | 28 ++++-
.../calcite/sql/test/SqlOperatorBaseTest.java | 36 +++++-
site/_docs/reference.md | 14 ++-
12 files changed, 379 insertions(+), 23 deletions(-)
diff --git a/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java
b/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java
index 1a83439..506ad0e 100644
--- a/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java
+++ b/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java
@@ -101,6 +101,16 @@ class BabelQuidemTest extends QuidemTest {
SqlConformanceEnum.BABEL)
.with(CalciteConnectionProperty.LENIENT_OPERATOR_LOOKUP, true)
.connect();
+ case "scott-big-query":
+ return CalciteAssert.that()
+ .with(CalciteAssert.Config.SCOTT)
+ .with(CalciteConnectionProperty.FUN, "standard,bigquery")
+ .with(CalciteConnectionProperty.PARSER_FACTORY,
+ SqlBabelParserImpl.class.getName() + "#FACTORY")
+ .with(CalciteConnectionProperty.CONFORMANCE,
+ SqlConformanceEnum.BABEL)
+ .with(CalciteConnectionProperty.LENIENT_OPERATOR_LOOKUP, true)
+ .connect();
default:
return super.connect(name, reference);
}
diff --git a/babel/src/test/resources/sql/big-query.iq
b/babel/src/test/resources/sql/big-query.iq
new file mode 100755
index 0000000..6792fb6
--- /dev/null
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -0,0 +1,137 @@
+# big-query.iq - Babel test for BigQuery dialect of SQL
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to you under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+!use scott-big-query
+!set outputformat csv
+
+# TIMESTAMP_SECONDS, TIMESTAMP_MILLIS, TIMESTAMP_MICROS
+select v,
+ timestamp_seconds(v) as t0,
+ timestamp_millis(v * 1000) as t1,
+ timestamp_micros(v * 1000 * 1000) as t2
+from (values cast(0 as bigint),
+ cast(null as bigint),
+ cast(1230219000 as bigint),
+ cast(-1230219000 as bigint)) as t (v)
+order by v;
+V, T0, T1, T2
+-1230219000, 1931-01-07 08:30:00, 1931-01-07 08:30:00, 1931-01-07 08:30:00
+0, 1970-01-01 00:00:00, 1970-01-01 00:00:00, 1970-01-01 00:00:00
+1230219000, 2008-12-25 15:30:00, 2008-12-25 15:30:00, 2008-12-25 15:30:00
+null, null, null, null
+!ok
+
+select timestamp_seconds(1234567890) as t;
+T
+2009-02-13 23:31:30
+!ok
+
+select timestamp_millis(1234567890) as t;
+T
+1970-01-15 06:56:07
+!ok
+
+select timestamp_micros(1234567890) as t;
+T
+1970-01-01 00:20:34
+!ok
+
+# UNIX_SECONDS, UNIX_MILLIS, UNIX_MICROS
+select v,
+ unix_seconds(v) as t0,
+ unix_millis(v) as t1,
+ unix_micros(v) as t2
+from (values TIMESTAMP '1970-01-01 00:00:00',
+ cast(null as timestamp),
+ TIMESTAMP '2008-12-25 15:30:00',
+ TIMESTAMP '1931-01-07 08:30:00') as t (v)
+order by v;
+V, T0, T1, T2
+1931-01-07 08:30:00, -1230219000, -1230219000000, -1230219000000000
+1970-01-01 00:00:00, 0, 0, 0
+2008-12-25 15:30:00, 1230219000, 1230219000000, 1230219000000000
+null, null, null, null
+!ok
+
+select unix_seconds(timestamp '2008-12-25 15:30:00') as t;
+T
+1230219000
+!ok
+
+select unix_millis(timestamp '2008-12-25 15:30:00') as t;
+T
+1230219000000
+!ok
+
+select unix_micros(timestamp '2008-12-25 15:30:00') as t;
+T
+1230219000000000
+!ok
+
+# DATE_FROM_UNIX_DATE
+select v,
+ date_from_unix_date(v) as d
+from (values 0,
+ cast(null as integer),
+ 1230219000 / 86400,
+ -1230219000 / 86400) as t (v)
+order by v;
+V, D
+-14238, 1931-01-08
+0, 1970-01-01
+14238, 2008-12-25
+null, null
+!ok
+
+select date_from_unix_date(14238);
+EXPR$0
+2008-12-25
+!ok
+
+# UNIX_DATE
+select v,
+ unix_date(v) as d
+from (values date '1970-01-01',
+ cast(null as date),
+ DATE '2008-12-25',
+ DATE '1931-01-07') as t (v)
+order by v;
+V, D
+1931-01-07, -14239
+1970-01-01, 0
+2008-12-25, 14238
+null, null
+!ok
+
+select unix_date(timestamp '2008-12-25');
+EXPR$0
+14238
+!ok
+
+# DATE
+# 'date(x) is shorthand for 'cast(x as date)'
+select date('1970-01-01') as d;
+D
+1970-01-01
+!ok
+
+select date(cast(null as varchar(10))) as d;
+D
+null
+!ok
+
+# End big-query.iq
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumUtils.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumUtils.java
index edf544b..cd192e1 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumUtils.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumUtils.java
@@ -292,12 +292,16 @@ public class EnumUtils {
}
private static Type toInternal(RelDataType type) {
+ return toInternal(type, false);
+ }
+
+ static Type toInternal(RelDataType type, boolean forceNotNull) {
switch (type.getSqlTypeName()) {
case DATE:
case TIME:
- return type.isNullable() ? Integer.class : int.class;
+ return type.isNullable() && !forceNotNull ? Integer.class : int.class;
case TIMESTAMP:
- return type.isNullable() ? Long.class : long.class;
+ return type.isNullable() && !forceNotNull ? Long.class : long.class;
default:
return null; // we don't care; use the default storage type
}
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 e0457e9..e315aa8 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
@@ -106,6 +106,8 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.COMPRESS;
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.DATE;
+import static
org.apache.calcite.sql.fun.SqlLibraryOperators.DATE_FROM_UNIX_DATE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.DAYNAME;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.DIFFERENCE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.EXISTS_NODE;
@@ -132,8 +134,15 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.SOUNDEX;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SPACE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.STRCMP;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TANH;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIMESTAMP_MICROS;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIMESTAMP_MILLIS;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIMESTAMP_SECONDS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_BASE64;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_DATE;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_MICROS;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_MILLIS;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_SECONDS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.XML_TRANSFORM;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ABS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ACOS;
@@ -430,6 +439,14 @@ public class RexImpTable {
new PeriodNameImplementor("monthName",
BuiltInMethod.MONTHNAME_WITH_TIMESTAMP,
BuiltInMethod.MONTHNAME_WITH_DATE));
+ defineMethod(TIMESTAMP_SECONDS, "timestampSeconds", NullPolicy.STRICT);
+ defineMethod(TIMESTAMP_MILLIS, "timestampMillis", NullPolicy.STRICT);
+ defineMethod(TIMESTAMP_MICROS, "timestampMicros", NullPolicy.STRICT);
+ defineMethod(UNIX_SECONDS, "unixSeconds", NullPolicy.STRICT);
+ defineMethod(UNIX_MILLIS, "unixMillis", NullPolicy.STRICT);
+ defineMethod(UNIX_MICROS, "unixMicros", NullPolicy.STRICT);
+ defineMethod(DATE_FROM_UNIX_DATE, "dateFromUnixDate", NullPolicy.STRICT);
+ defineMethod(UNIX_DATE, "unixDate", NullPolicy.STRICT);
map.put(IS_NULL, new IsNullImplementor());
map.put(IS_NOT_NULL, new IsNotNullImplementor());
@@ -498,6 +515,7 @@ public class RexImpTable {
map.put(COALESCE, new CoalesceImplementor());
map.put(CAST, new CastImplementor());
+ map.put(DATE, new CastImplementor());
map.put(REINTERPRET, new ReinterpretImplementor());
diff --git
a/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
b/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
index f09b052..adb9833 100644
---
a/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
+++
b/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
@@ -78,8 +78,8 @@ public enum CalciteConnectionProperty implements
ConnectionProperty {
LEX("lex", Type.ENUM, Lex.ORACLE, false),
/** Collection of built-in functions and operators. Valid values include
- * "standard", "mysql", "oracle", "postgresql" and "spatial", and also
- * comma-separated lists, for example "oracle,spatial". */
+ * "standard", "bigquery", "mysql", "oracle", "postgresql" and "spatial",
+ * and also comma-separated lists, for example "oracle,spatial". */
FUN("fun", Type.STRING, "standard", true),
/** How identifiers are quoted.
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 18c9312..248990e 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -33,6 +33,7 @@ import org.apache.calcite.linq4j.function.Function1;
import org.apache.calcite.linq4j.function.NonDeterministic;
import org.apache.calcite.linq4j.tree.Primitive;
import org.apache.calcite.runtime.FlatLists.ComparableList;
+import org.apache.calcite.sql.fun.SqlLibraryOperators;
import org.apache.calcite.util.NumberUtil;
import org.apache.calcite.util.TimeWithTimeZoneString;
import org.apache.calcite.util.TimestampWithTimeZoneString;
@@ -1774,7 +1775,7 @@ public class SqlFunctions {
@NonDeterministic
private static Object cannotConvert(Object o, Class toType) {
- throw RESOURCE.cannotConvert(o.toString(), toType.toString()).ex();
+ throw RESOURCE.cannotConvert(String.valueOf(o), toType.toString()).ex();
}
/** CAST(VARCHAR AS BOOLEAN). */
@@ -2099,6 +2100,52 @@ public class SqlFunctions {
.getMillisOfDay();
}
+ /** For {@link SqlLibraryOperators#TIMESTAMP_SECONDS}. */
+ public static long timestampSeconds(long v) {
+ return v * 1000;
+ }
+
+ /** For {@link SqlLibraryOperators#TIMESTAMP_MILLIS}. */
+ public static long timestampMillis(long v) {
+ // translation is trivial, because Calcite represents TIMESTAMP values as
+ // millis since epoch
+ return v;
+ }
+
+ /** For {@link SqlLibraryOperators#TIMESTAMP_MICROS}. */
+ public static long timestampMicros(long v) {
+ return v / 1000;
+ }
+
+ /** For {@link SqlLibraryOperators#UNIX_SECONDS}. */
+ public static long unixSeconds(long v) {
+ return v / 1000;
+ }
+
+ /** For {@link SqlLibraryOperators#UNIX_MILLIS}. */
+ public static long unixMillis(long v) {
+ // translation is trivial, because Calcite represents TIMESTAMP values as
+ // millis since epoch
+ return v;
+ }
+
+ /** For {@link SqlLibraryOperators#UNIX_MICROS}. */
+ public static long unixMicros(long v) {
+ return v * 1000;
+ }
+
+ /** For {@link SqlLibraryOperators#DATE_FROM_UNIX_DATE}. */
+ public static int dateFromUnixDate(int v) {
+ // translation is trivial, because Calcite represents dates as Unix
integers
+ return v;
+ }
+
+ /** For {@link SqlLibraryOperators#UNIX_DATE}. */
+ public static int unixDate(int v) {
+ // translation is trivial, because Calcite represents dates as Unix
integers
+ return v;
+ }
+
public static Long toTimestampWithLocalTimeZone(String v) {
return v == null ? null : new TimestampWithTimeZoneString(v)
.withTimeZone(DateTimeUtils.UTC_ZONE)
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 9722977..10e9d66 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
@@ -18,6 +18,7 @@ package org.apache.calcite.sql.fun;
import org.apache.calcite.config.CalciteConnectionProperty;
+import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
@@ -41,16 +42,19 @@ import java.util.Objects;
*/
public enum SqlLibrary {
/** The standard operators. */
- STANDARD(""),
+ STANDARD("", "standard"),
/** Geospatial operators. */
- SPATIAL("s"),
+ SPATIAL("s", "spatial"),
+ /** A collection of operators that are in Google BigQuery but not in standard
+ * SQL. */
+ BIG_QUERY("b", "bigquery"),
/** A collection of operators that are in MySQL but not in standard SQL. */
- MYSQL("m"),
+ MYSQL("m", "mysql"),
/** A collection of operators that are in Oracle but not in standard SQL. */
- ORACLE("o"),
+ ORACLE("o", "oracle"),
/** A collection of operators that are in PostgreSQL but not in standard
* SQL. */
- POSTGRESQL("p");
+ POSTGRESQL("p", "postgresql");
/** Abbreviation for the library used in SQL reference. */
public final String abbrev;
@@ -59,9 +63,11 @@ public enum SqlLibrary {
* see {@link CalciteConnectionProperty#FUN}. */
public final String fun;
- SqlLibrary(String abbrev) {
+ SqlLibrary(String abbrev, String fun) {
this.abbrev = Objects.requireNonNull(abbrev);
- this.fun = name().toLowerCase(Locale.ROOT);
+ this.fun = Objects.requireNonNull(fun);
+ Preconditions.checkArgument(
+ fun.equals(name().toLowerCase(Locale.ROOT).replace("_", "")));
}
/** Looks up a value.
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 26589d2..f23c884 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
@@ -33,6 +33,7 @@ import org.apache.calcite.sql.type.SqlTypeTransforms;
import java.util.ArrayList;
import java.util.List;
+import static org.apache.calcite.sql.fun.SqlLibrary.BIG_QUERY;
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;
@@ -202,6 +203,29 @@ public abstract class SqlLibraryOperators {
ReturnTypes.cascade(ReturnTypes.INTEGER_NULLABLE,
SqlTypeTransforms.FORCE_NULLABLE),
null, OperandTypes.STRING_STRING_OPTIONAL_STRING,
SqlFunctionCategory.SYSTEM);
+ /** The "DATE(string)" function, equivalent to "CAST(string AS DATE). */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction DATE =
+ new SqlFunction("DATE", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.DATE_NULLABLE, null, OperandTypes.STRING,
+ SqlFunctionCategory.TIMEDATE);
+
+ /** The "DATE_FROM_UNIX_DATE(integer)" function; returns a DATE value
+ * a given number of seconds after 1970-01-01. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction DATE_FROM_UNIX_DATE =
+ new SqlFunction("DATE_FROM_UNIX_DATE", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.DATE_NULLABLE, null, OperandTypes.INTEGER,
+ SqlFunctionCategory.TIMEDATE);
+
+ /** The "UNIX_DATE(date)" function; returns the number of days since
+ * 1970-01-01. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction UNIX_DATE =
+ new SqlFunction("UNIX_DATE", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.INTEGER_NULLABLE, null, OperandTypes.DATE,
+ SqlFunctionCategory.TIMEDATE);
+
/** The "MONTHNAME(datetime)" function; returns the name of the month,
* in the current locale, of a TIMESTAMP or DATE argument. */
@LibraryOperator(libraries = {MYSQL})
@@ -350,6 +374,54 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING_STRING,
SqlFunctionCategory.TIMEDATE);
+ /** The "TIMESTAMP_SECONDS(bigint)" function; returns a TIMESTAMP value
+ * a given number of seconds after 1970-01-01 00:00:00. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction TIMESTAMP_SECONDS =
+ new SqlFunction("TIMESTAMP_SECONDS", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.TIMESTAMP_NULLABLE, null, OperandTypes.INTEGER,
+ SqlFunctionCategory.TIMEDATE);
+
+ /** The "TIMESTAMP_MILLIS(bigint)" function; returns a TIMESTAMP value
+ * a given number of milliseconds after 1970-01-01 00:00:00. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction TIMESTAMP_MILLIS =
+ new SqlFunction("TIMESTAMP_MILLIS", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.TIMESTAMP_NULLABLE, null, OperandTypes.INTEGER,
+ SqlFunctionCategory.TIMEDATE);
+
+ /** The "TIMESTAMP_MICROS(bigint)" function; returns a TIMESTAMP value
+ * a given number of micro-seconds after 1970-01-01 00:00:00. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction TIMESTAMP_MICROS =
+ new SqlFunction("TIMESTAMP_MICROS", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.TIMESTAMP_NULLABLE, null, OperandTypes.INTEGER,
+ SqlFunctionCategory.TIMEDATE);
+
+ /** The "UNIX_SECONDS(bigint)" function; returns the number of seconds
+ * since 1970-01-01 00:00:00. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction UNIX_SECONDS =
+ new SqlFunction("UNIX_SECONDS", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.BIGINT_NULLABLE, null, OperandTypes.TIMESTAMP,
+ SqlFunctionCategory.TIMEDATE);
+
+ /** The "UNIX_MILLIS(bigint)" function; returns the number of milliseconds
+ * since 1970-01-01 00:00:00. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction UNIX_MILLIS =
+ new SqlFunction("UNIX_MILLIS", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.BIGINT_NULLABLE, null, OperandTypes.TIMESTAMP,
+ SqlFunctionCategory.TIMEDATE);
+
+ /** The "UNIX_MICROS(bigint)" function; returns the number of microseconds
+ * since 1970-01-01 00:00:00. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction UNIX_MICROS =
+ new SqlFunction("UNIX_MICROS", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.BIGINT_NULLABLE, null, OperandTypes.TIMESTAMP,
+ SqlFunctionCategory.TIMEDATE);
+
@LibraryOperator(libraries = {ORACLE})
public static final SqlFunction CHR =
new SqlFunction("CHR",
diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
index eddba18..b601aa3 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
@@ -242,6 +242,12 @@ public abstract class OperandTypes {
public static final SqlSingleOperandTypeChecker DATETIME =
family(SqlTypeFamily.DATETIME);
+ public static final SqlSingleOperandTypeChecker DATE =
+ family(SqlTypeFamily.DATE);
+
+ public static final SqlSingleOperandTypeChecker TIMESTAMP =
+ family(SqlTypeFamily.TIMESTAMP);
+
public static final SqlSingleOperandTypeChecker INTERVAL =
family(SqlTypeFamily.DATETIME_INTERVAL);
diff --git a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
index 4d5a905..1a31917 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
@@ -210,35 +210,51 @@ public abstract class ReturnTypes {
cascade(BOOLEAN, SqlTypeTransforms.FORCE_NULLABLE);
/**
- * Type-inference strategy whereby the result type of a call is Boolean
- * not null.
+ * Type-inference strategy whereby the result type of a call is BOOLEAN
+ * NOT NULL.
*/
public static final SqlReturnTypeInference BOOLEAN_NOT_NULL =
cascade(BOOLEAN, SqlTypeTransforms.TO_NOT_NULLABLE);
+
/**
- * Type-inference strategy whereby the result type of a call is Date.
+ * Type-inference strategy whereby the result type of a call is DATE.
*/
public static final SqlReturnTypeInference DATE =
explicit(SqlTypeName.DATE);
/**
* Type-inference strategy whereby the result type of a call is nullable
- * Date.
+ * DATE.
*/
public static final SqlReturnTypeInference DATE_NULLABLE =
cascade(DATE, SqlTypeTransforms.TO_NULLABLE);
/**
- * Type-inference strategy whereby the result type of a call is Time(0).
+ * Type-inference strategy whereby the result type of a call is TIME(0).
*/
public static final SqlReturnTypeInference TIME =
explicit(SqlTypeName.TIME, 0);
+
/**
* Type-inference strategy whereby the result type of a call is nullable
- * Time(0).
+ * TIME(0).
*/
public static final SqlReturnTypeInference TIME_NULLABLE =
cascade(TIME, SqlTypeTransforms.TO_NULLABLE);
+
+ /**
+ * Type-inference strategy whereby the result type of a call is TIMESTAMP.
+ */
+ public static final SqlReturnTypeInference TIMESTAMP =
+ explicit(SqlTypeName.TIMESTAMP);
+
+ /**
+ * Type-inference strategy whereby the result type of a call is nullable
+ * TIMESTAMP.
+ */
+ public static final SqlReturnTypeInference TIMESTAMP_NULLABLE =
+ cascade(TIMESTAMP, SqlTypeTransforms.TO_NULLABLE);
+
/**
* Type-inference strategy whereby the result type of a call is Double.
*/
diff --git
a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
index 1bff997..18a3313 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
@@ -324,15 +324,23 @@ public abstract class SqlOperatorBaseTest {
tester.setFor(null);
}
- protected SqlTester oracleTester() {
+ private SqlTester oracleTester() {
+ return libraryTester(SqlLibrary.ORACLE);
+ }
+
+ private SqlTester bigQueryTester() {
+ return libraryTester(SqlLibrary.BIG_QUERY);
+ }
+
+ protected SqlTester libraryTester(SqlLibrary library) {
return tester.withOperatorTable(
SqlLibraryOperatorTableFactory.INSTANCE
- .getOperatorTable(SqlLibrary.STANDARD, SqlLibrary.ORACLE))
+ .getOperatorTable(SqlLibrary.STANDARD, library))
.withConnectionFactory(
CalciteAssert.EMPTY_CONNECTION_FACTORY
.with(new CalciteAssert
.AddSchemaSpecPostProcessor(CalciteAssert.SchemaSpec.HR))
- .with(CalciteConnectionProperty.FUN, "oracle"));
+ .with(CalciteConnectionProperty.FUN, library.fun));
}
protected SqlTester oracleTester(SqlConformance conformance) {
@@ -5599,6 +5607,28 @@ public abstract class SqlOperatorBaseTest {
tester.checkScalar("rand_integer(2, 11)", 1, "INTEGER NOT NULL");
}
+ /** Tests {@code UNIX_SECONDS} and other datetime functions from BigQuery. */
+ @Test void testUnixSecondsFunc() {
+ SqlTester tester = bigQueryTester();
+ tester.setFor(SqlLibraryOperators.UNIX_SECONDS);
+ tester.checkScalar("unix_seconds(timestamp '1970-01-01 00:00:00')", 0,
+ "BIGINT NOT NULL");
+ tester.checkNull("unix_seconds(cast(null as timestamp))");
+ tester.checkNull("unix_millis(cast(null as timestamp))");
+ tester.checkNull("unix_micros(cast(null as timestamp))");
+ tester.checkScalar("timestamp_seconds(0)", "1970-01-01 00:00:00",
+ "TIMESTAMP(0) NOT NULL");
+ tester.checkNull("timestamp_seconds(cast(null as bigint))");
+ tester.checkNull("timestamp_millis(cast(null as bigint))");
+ tester.checkNull("timestamp_micros(cast(null as bigint))");
+ tester.checkScalar("date_from_unix_date(0)", "1970-01-01", "DATE NOT
NULL");
+
+ // Have to quote the "DATE" function because we're not using the Babel
+ // parser. In the regular parser, DATE is a reserved keyword.
+ tester.checkNull("\"DATE\"(null)");
+ tester.checkScalar("\"DATE\"('1985-12-06')", "1985-12-06", "DATE NOT
NULL");
+ }
+
@Test void testAbsFunc() {
tester.setFor(SqlStdOperatorTable.ABS);
tester.checkScalarExact("abs(-1)", "1");
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 7fd2930..5682677 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2340,6 +2340,7 @@ To enable an operator table, set the
connect string parameter.
The 'C' (compatibility) column contains value
+'b' for BigQuery ('fun=bigquery' in the connect string),
'm' for MySQL ('fun=mysql' in the connect string),
'o' for Oracle ('fun=oracle' in the connect string),
'p' for PostgreSQL ('fun=postgresql' in the connect string).
@@ -2357,6 +2358,8 @@ semantics.
| m | COMPRESS(string) | Compresses a string
using zlib compression and returns the result as a binary string.
| p | CONVERT_TIMEZONE(tz1, tz2, datetime) | Converts the timezone
of *datetime* from *tz1* to *tz2*
| 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)`
+| b | DATE_FROM_UNIX_DATE(integer) | Returns the DATE that
is *integer* days after 1970-01-01
| 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
@@ -2388,12 +2391,19 @@ semantics.
| m o p | SOUNDEX(string) | Returns the phonetic
representation of *string*; throws if *string* is encoded with multi-byte
encoding such as UTF-8
| m | SPACE(integer) | Returns a string of
*integer* spaces; returns an empty string if *integer* is less than 1
| o | SUBSTR(string, position [, substringLength ]) | Returns a portion of
*string*, beginning at character *position*, *substringLength* characters long.
SUBSTR calculates lengths using characters as defined by the input character set
-| m | STRCMP(string, string) | Returns 0 if both of
the strings are same and returns -1 when the first argument is smaller than the
second and 1 when the second one is smaller the first one.
+| m | STRCMP(string, string) | Returns 0 if both of
the strings are same and returns -1 when the first argument is smaller than the
second and 1 when the second one is smaller than the first one
| o | TANH(numeric) | Returns the hyperbolic
tangent of *numeric*
+| b | TIMESTAMP_MICROS(integer) | Returns the TIMESTAMP
that is *integer* microseconds after 1970-01-01 00:00:00
+| b | TIMESTAMP_MILLIS(integer) | Returns the TIMESTAMP
that is *integer* milliseconds after 1970-01-01 00:00:00
+| b | TIMESTAMP_SECONDS(integer) | Returns the TIMESTAMP
that is *integer* seconds after 1970-01-01 00:00:00
| o p | TO_DATE(string, format) | Converts *string* to a
date using the format *format*
| o p | TO_TIMESTAMP(string, format) | Converts *string* to a
timestamp using the format *format*
| o p | TRANSLATE(expr, fromString, toString) | Returns *expr* with all
occurrences of each character in *fromString* replaced by its corresponding
character in *toString*. Characters in *expr* that are not in *fromString* are
not replaced
-| o | XMLTRANSFORM(xml, xslt) | Returns a string after
applying xslt to supplied xml.
+| b | UNIX_MICROS(timestamp) | Returns the number of
microseconds since 1970-01-01 00:00:00
+| b | UNIX_MILLIS(timestamp) | Returns the number of
milliseconds since 1970-01-01 00:00:00
+| b | UNIX_SECONDS(timestamp) | Returns the number of
seconds since 1970-01-01 00:00:00
+| b | UNIX_DATE(date) | Returns the number of
days since 1970-01-01
+| o | XMLTRANSFORM(xml, xslt) | Returns a string after
applying xslt to supplied XML
Note:
