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: