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 49a0d5e9c1ef65618425189b22f2a2b5ed51f6d8 Author: TJ Banghart <[email protected]> AuthorDate: Wed Jan 18 10:10:51 2023 -0800 [CALCITE-5357] Add FORMAT_TIME, FORMAT_DATE, FORMAT_DATETIME, FORMAT_TIMESTAMP functions (enabled in BigQuery library) Add interface FormatModel, class FormatModels, interface FormatElement to support other formatting and parsing functions in future. Co-authored-by: TJ Banghart <[email protected]> Co-authored-by: Julian Hyde <[email protected]> Close apache/calcite#3034 --- babel/src/test/resources/sql/big-query.iq | 86 +++---- .../calcite/adapter/enumerable/RexImpTable.java | 42 ++++ .../org/apache/calcite/runtime/SqlFunctions.java | 23 ++ .../java/org/apache/calcite/sql/SqlDialect.java | 14 ++ .../calcite/sql/dialect/BigQuerySqlDialect.java | 11 + .../calcite/sql/fun/SqlLibraryOperators.java | 48 ++++ .../org/apache/calcite/sql/type/OperandTypes.java | 6 + .../org/apache/calcite/util/BuiltInMethod.java | 6 + .../apache/calcite/util/format/FormatElement.java | 58 +++++ .../calcite/util/format/FormatElementEnum.java | 234 +++++++++++++++++ .../apache/calcite/util/format/FormatModel.java | 37 +++ .../apache/calcite/util/format/FormatModels.java | 278 +++++++++++++++++++++ .../apache/calcite/util/format/package-info.java | 21 ++ .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 35 +++ .../org/apache/calcite/util/FormatModelTest.java | 67 +++++ site/_docs/reference.md | 4 + .../org/apache/calcite/test/SqlOperatorTest.java | 89 +++++++ 17 files changed, 1016 insertions(+), 43 deletions(-) diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq index c31141d2db..528ac8797b 100755 --- a/babel/src/test/resources/sql/big-query.iq +++ b/babel/src/test/resources/sql/big-query.iq @@ -2445,35 +2445,35 @@ SELECT * FROM items; # # Returns STRING -!if (false) { SELECT FORMAT_DATE("%x", DATE "2008-12-25") AS US_format; -+------------+ -| US_format | -+------------+ -| 12/25/08 | -+------------+ ++-----------+ +| US_format | ++-----------+ +| 12/25/08 | ++-----------+ +(1 row) + !ok -!} -!if (false) { SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted; +-------------+ | formatted | +-------------+ | Dec-25-2008 | +-------------+ +(1 row) + !ok -!} -!if (false) { SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted; -+-------------+ -| formatted | -+-------------+ -| Dec 2008 | -+-------------+ ++-----------+ +| formatted | ++-----------+ +| Dec 2008 | ++-----------+ +(1 row) + !ok -!} ##################################################################### # FORMAT_DATETIME @@ -2485,7 +2485,6 @@ SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted; # # Returns STRING -!if (false) { SELECT FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00") AS formatted; @@ -2494,10 +2493,10 @@ SELECT +--------------------------+ | Thu Dec 25 15:30:00 2008 | +--------------------------+ +(1 row) + !ok -!} -!if (false) { SELECT FORMAT_DATETIME("%b-%d-%Y", DATETIME "2008-12-25 15:30:00") AS formatted; @@ -2506,20 +2505,21 @@ SELECT +-------------+ | Dec-25-2008 | +-------------+ +(1 row) + !ok -!} -!if (false) { SELECT FORMAT_DATETIME("%b %Y", DATETIME "2008-12-25 15:30:00") AS formatted; -+-------------+ -| formatted | -+-------------+ -| Dec 2008 | -+-------------+ ++-----------+ +| formatted | ++-----------+ +| Dec 2008 | ++-----------+ +(1 row) + !ok -!} ##################################################################### # FORMAT_TIME @@ -2532,15 +2532,15 @@ SELECT # # Returns STRING -!if (false) { SELECT FORMAT_TIME("%R", TIME "15:30:00") as formatted_time; +----------------+ | formatted_time | +----------------+ | 15:30 | +----------------+ +(1 row) + !ok -!} ##################################################################### # FORMAT_TIMESTAMP @@ -2553,36 +2553,36 @@ SELECT FORMAT_TIME("%R", TIME "15:30:00") as formatted_time; # See Supported Format Elements For TIMESTAMP for a list of format # elements that this function supports. -!if (false) { -SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS formatted; +SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00" /* TODO should be "2008-12-25 15:30:00+00" */, "UTC") AS formatted; +--------------------------+ | formatted | +--------------------------+ | Thu Dec 25 15:30:00 2008 | +--------------------------+ +(1 row) + !ok -!} -!if (false) { -SELECT FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2008-12-25 15:30:00+00") AS formatted; +SELECT FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2008-12-25 15:30:00" /* TODO should be "2008-12-25 15:30:00+00" */) AS formatted; +-------------+ | formatted | +-------------+ | Dec-25-2008 | +-------------+ +(1 row) + !ok -!} -!if (false) { -SELECT FORMAT_TIMESTAMP("%b %Y", TIMESTAMP "2008-12-25 15:30:00+00") +SELECT FORMAT_TIMESTAMP("%b %Y", TIMESTAMP "2008-12-25 15:30:00" /* TODO should be "2008-12-25 15:30:00+00" */) AS formatted; -+-------------+ -| formatted | -+-------------+ -| Dec 2008 | -+-------------+ ++-----------+ +| formatted | ++-----------+ +| Dec 2008 | ++-----------+ +(1 row) + !ok -!} ##################################################################### # PARSE_DATE 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 3e3eeb621f..013f748b61 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 @@ -133,6 +133,10 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.ENDS_WITH; import static org.apache.calcite.sql.fun.SqlLibraryOperators.EXISTS_NODE; import static org.apache.calcite.sql.fun.SqlLibraryOperators.EXTRACT_VALUE; import static org.apache.calcite.sql.fun.SqlLibraryOperators.EXTRACT_XML; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_DATE; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_DATETIME; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_TIME; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_TIMESTAMP; import static org.apache.calcite.sql.fun.SqlLibraryOperators.FROM_BASE64; import static org.apache.calcite.sql.fun.SqlLibraryOperators.ILIKE; import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_DEPTH; @@ -553,11 +557,20 @@ public class RexImpTable { defineMethod(DATE_FROM_UNIX_DATE, "dateFromUnixDate", NullPolicy.STRICT); defineMethod(UNIX_DATE, "unixDate", NullPolicy.STRICT); + // Datetime constructors defineMethod(DATE, "date", NullPolicy.STRICT); defineMethod(DATETIME, "datetime", NullPolicy.STRICT); defineMethod(TIMESTAMP, "timestamp", NullPolicy.STRICT); defineMethod(TIME, "time", NullPolicy.STRICT); + // Datetime formatting methods + final FormatDatetimeImplementor datetimeFormatImpl = new FormatDatetimeImplementor(); + map.put(FORMAT_TIMESTAMP, datetimeFormatImpl); + map.put(FORMAT_DATE, datetimeFormatImpl); + map.put(FORMAT_TIME, datetimeFormatImpl); + map.put(FORMAT_DATETIME, datetimeFormatImpl); + + // Boolean operators map.put(IS_NULL, new IsNullImplementor()); map.put(IS_NOT_NULL, new IsNotNullImplementor()); map.put(IS_TRUE, new IsTrueImplementor()); @@ -2349,6 +2362,35 @@ public class RexImpTable { } } + /** + * Implementor for the {@code FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME} and + * {@code FORMAT_DATETIME} functions. + */ + private static class FormatDatetimeImplementor extends MethodNameImplementor { + + FormatDatetimeImplementor() { + super("formatDatetime", NullPolicy.STRICT, false); + } + + @Override Expression implementSafe(final RexToLixTranslator translator, + final RexCall call, final List<Expression> argValueList) { + final Expression operand0 = argValueList.get(0); + final Expression operand1 = argValueList.get(1); + Method method; + switch (call.operands.get(1).getType().getSqlTypeName()) { + case TIME: + method = BuiltInMethod.FORMAT_TIME.method; + break; + case DATE: + method = BuiltInMethod.FORMAT_DATE.method; + break; + default: + method = BuiltInMethod.FORMAT_TIMESTAMP.method; + } + return Expressions.call(method, translator.getRoot(), operand0, operand1); + } + } + /** Implementor for a function that generates calls to a given method. */ private static class MethodImplementor extends AbstractRexCallImplementor { protected final Method method; 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 a44c6390f0..49592abdad 100644 --- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java +++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java @@ -41,6 +41,8 @@ import org.apache.calcite.util.TimeWithTimeZoneString; import org.apache.calcite.util.TimestampWithTimeZoneString; import org.apache.calcite.util.Unsafe; import org.apache.calcite.util.Util; +import org.apache.calcite.util.format.FormatElement; +import org.apache.calcite.util.format.FormatModels; import org.apache.commons.codec.digest.DigestUtils; import org.apache.commons.codec.language.Soundex; @@ -88,6 +90,7 @@ import java.util.TimeZone; import java.util.concurrent.atomic.AtomicLong; import java.util.function.BinaryOperator; import java.util.regex.Pattern; +import java.util.stream.Collectors; import static org.apache.calcite.linq4j.Nullness.castNonNull; import static org.apache.calcite.util.Static.RESOURCE; @@ -2571,6 +2574,26 @@ public class SqlFunctions { .toString(); } + private static String internalFormatDatetime(String fmtString, java.util.Date date) { + List<FormatElement> elements = + FormatModels.BIG_QUERY.parse(fmtString); + return elements.stream() + .map(ele -> ele.format(date)) + .collect(Collectors.joining()); + } + + public static String formatTimestamp(DataContext ctx, String fmtString, long timestamp) { + return internalFormatDatetime(fmtString, internalToTimestamp(timestamp)); + } + + public static String formatDate(DataContext ctx, String fmtString, int date) { + return internalFormatDatetime(fmtString, internalToDate(date)); + } + + public static String formatTime(DataContext ctx, String fmtString, int time) { + return internalFormatDatetime(fmtString, internalToTime(time)); + } + /** * Converts a SQL TIMESTAMP value from the internal representation type * (number of milliseconds since January 1st, 1970) to the Java Type diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java index 4654f8fea1..ffb12c3629 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java @@ -38,6 +38,8 @@ import org.apache.calcite.sql.type.AbstractSqlType; import org.apache.calcite.sql.type.SqlTypeUtil; import org.apache.calcite.sql.validate.SqlConformance; import org.apache.calcite.sql.validate.SqlConformanceEnum; +import org.apache.calcite.util.format.FormatModel; +import org.apache.calcite.util.format.FormatModels; import com.google.common.base.Preconditions; import com.google.common.base.Suppliers; @@ -1002,6 +1004,18 @@ public class SqlDialect { } } + /** + * Returns a description of the format string used by functions in this + * dialect. + * + * <p>Dialects may need to override this element mapping if they differ from + * <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Format-Models.html";> + * Oracle's format elements</a>. By default, this returns {@link FormatModels#DEFAULT}. + */ + public FormatModel getFormatModel() { + return FormatModels.DEFAULT; + } + /** * Returns whether the dialect supports nested aggregations, for instance * {@code SELECT SUM(SUM(1)) }. diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java index df38d9c3a9..4fd8b70a32 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java @@ -43,6 +43,8 @@ import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.type.BasicSqlType; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql.type.SqlTypeUtil; +import org.apache.calcite.util.format.FormatModel; +import org.apache.calcite.util.format.FormatModels; import com.google.common.collect.ImmutableList; @@ -271,6 +273,15 @@ public class BigQuerySqlDialect extends SqlDialect { } } + /** + * {@inheritDoc} + * + * @see FormatModels#BIG_QUERY + */ + @Override public FormatModel getFormatModel() { + return FormatModels.BIG_QUERY; + } + /** {@inheritDoc} * * <p>BigQuery data type reference: 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 6b91ffa1c0..936194d4e1 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 @@ -789,6 +789,54 @@ public abstract class SqlLibraryOperators { OperandTypes.STRING_STRING, SqlFunctionCategory.TIMEDATE); + /** The "FORMAT_TIME(string, time)" function (BigQuery); + * Formats a time object according to the specified string. */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction FORMAT_TIME = + SqlBasicFunction.create("FORMAT_TIME", ReturnTypes.VARCHAR_2000_NULLABLE, + OperandTypes.CHARACTER_TIME, SqlFunctionCategory.STRING); + + /** The "FORMAT_DATE(string, date)" function (BigQuery); + * Formats a date object according to the specified string. */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction FORMAT_DATE = + SqlBasicFunction.create("FORMAT_DATE", ReturnTypes.VARCHAR_2000_NULLABLE, + OperandTypes.CHARACTER_DATE, SqlFunctionCategory.STRING); + + /** The "FORMAT_TIMESTAMP(string, timestamp)" function (BigQuery); + * Formats a timestamp object according to the specified string. + * + * <p>In BigQuery, the "TIMESTAMP" datatype maps to Calcite's + * TIMESTAMP_WITH_LOCAL_TIME_ZONE */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction FORMAT_TIMESTAMP = + SqlBasicFunction.create("FORMAT_TIMESTAMP", + ReturnTypes.VARCHAR_2000_NULLABLE, + OperandTypes.sequence("FORMAT_TIMESTAMP(<CHARACTER>, " + + "<TIMESTAMP WITH LOCAL TIME ZONE>)", + OperandTypes.CHARACTER, OperandTypes.TIMESTAMP_LTZ) + .or( + OperandTypes.sequence("FORMAT_TIMESTAMP(<CHARACTER>, " + + "<TIMESTAMP WITH LOCAL TIME ZONE>, <CHARACTER>)", + OperandTypes.CHARACTER, OperandTypes.TIMESTAMP_LTZ, + OperandTypes.CHARACTER)), + SqlFunctionCategory.STRING); + + /** The "FORMAT_DATETIME(string, timestamp)" function (BigQuery); + * Formats a timestamp object according to the specified string. */ + @LibraryOperator(libraries = {BIG_QUERY}) + public static final SqlFunction FORMAT_DATETIME = + SqlBasicFunction.create("FORMAT_DATETIME", + ReturnTypes.VARCHAR_2000_NULLABLE, + OperandTypes.sequence("FORMAT_DATETIME(<CHARACTER>, <TIMESTAMP>)", + OperandTypes.CHARACTER, OperandTypes.TIMESTAMP_NTZ) + .or( + OperandTypes.sequence("FORMAT_DATETIME(<CHARACTER>, " + + "<TIMESTAMP>, <CHARACTER>)", + OperandTypes.CHARACTER, OperandTypes.TIMESTAMP_NTZ, + OperandTypes.CHARACTER)), + SqlFunctionCategory.STRING); + /** The "TIMESTAMP_ADD(timestamp, interval)" function (BigQuery), the * two-argument variant of the built-in * {@link SqlStdOperatorTable#TIMESTAMP_ADD TIMESTAMPADD} function, which has 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 cd927392aa..1f23a81707 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 @@ -468,6 +468,12 @@ public abstract class OperandTypes { public static final SqlSingleOperandTypeChecker CHARACTER_CHARACTER_DATETIME = family(SqlTypeFamily.CHARACTER, SqlTypeFamily.CHARACTER, SqlTypeFamily.DATETIME); + public static final SqlSingleOperandTypeChecker CHARACTER_DATE = + family(SqlTypeFamily.CHARACTER, SqlTypeFamily.DATE); + + public static final SqlSingleOperandTypeChecker CHARACTER_TIME = + family(SqlTypeFamily.CHARACTER, SqlTypeFamily.TIME); + public static final SqlSingleOperandTypeChecker PERIOD = new PeriodOperandTypeChecker(); diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java index 52ff2bf1ec..2b3eaaf085 100644 --- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java +++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java @@ -500,6 +500,12 @@ public enum BuiltInMethod { "timestampWithLocalTimeZoneToTimestamp", long.class, TimeZone.class), TIMESTAMP_WITH_LOCAL_TIME_ZONE_TO_STRING(SqlFunctions.class, "timestampWithLocalTimeZoneToString", long.class, TimeZone.class), + FORMAT_TIMESTAMP(SqlFunctions.class, "formatTimestamp", DataContext.class, + String.class, long.class), + FORMAT_DATE(SqlFunctions.class, "formatDate", DataContext.class, String.class, + int.class), + FORMAT_TIME(SqlFunctions.class, "formatTime", DataContext.class, String.class, + int.class), UNIX_DATE_TO_STRING(DateTimeUtils.class, "unixDateToString", int.class), UNIX_TIME_TO_STRING(DateTimeUtils.class, "unixTimeToString", int.class), UNIX_TIMESTAMP_TO_STRING(DateTimeUtils.class, "unixTimestampToString", diff --git a/core/src/main/java/org/apache/calcite/util/format/FormatElement.java b/core/src/main/java/org/apache/calcite/util/format/FormatElement.java new file mode 100644 index 0000000000..6670e23423 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/util/format/FormatElement.java @@ -0,0 +1,58 @@ +/* + * 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. + */ +package org.apache.calcite.util.format; + +import org.apache.calcite.linq4j.function.Experimental; + +import java.util.function.Consumer; + +/** + * A format element in a format string. Knows how to parse and unparse itself. + */ +@Experimental +public interface FormatElement { + + /** + * Formats a date to its appropriate string representation for the element. + * + * <p>This API is subject to change. It might be more efficient if the + * signature was one of the following: + * + * <pre> + * void format(StringBuilder, java.util.Date) + * void format(StringBuilder, long) + * </pre> + */ + String format(java.util.Date date); + + /** + * Returns the description of an element. + * + * <p>For example, {@code %H} in MySQL represents the hour in 24-hour format + * (e.g., 00..23). This method returns the string "The hour (24-hour clock) as + * a decimal number (00-23)", which is the description of + * {@link FormatElementEnum#HH24}. + */ + String getDescription(); + + /** + * Applies a consumer to a format element. + */ + default void flatten(Consumer<FormatElement> consumer) { + consumer.accept(this); + } +} diff --git a/core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java b/core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java new file mode 100644 index 0000000000..7aed1fc486 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java @@ -0,0 +1,234 @@ +/* + * 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. + */ +package org.apache.calcite.util.format; + +import org.apache.calcite.avatica.util.DateTimeUtils; + +import org.checkerframework.checker.nullness.qual.Nullable; + +import java.text.DateFormat; +import java.text.SimpleDateFormat; +import java.util.Calendar; +import java.util.Date; +import java.util.Locale; + +import static org.apache.calcite.linq4j.Nullness.castNonNull; + +/** + * Implementation of {@link FormatElement} containing the standard format + * elements. These are based on Oracle's format model documentation. + * + * <p>See + * <a href="https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlqr/Format-Models.html";> + * Oracle format model reference.</a> + * + * @see FormatModels#DEFAULT + */ +public enum FormatElementEnum implements FormatElement { + D("The weekday (Monday as the first day of the week) as a decimal number (1-7)") { + @Override public String format(Date date) { + final Calendar calendar = Work.get().calendar; + calendar.setTime(date); + return String.format(Locale.ROOT, "%d", calendar.get(Calendar.DAY_OF_WEEK)); + } + }, + DAY("The full weekday name") { + @Override public String format(Date date) { + final Work work = Work.get(); + return work.eeeeFormat.format(date); + } + }, + DD("The day of the month as a decimal number (01-31)") { + @Override public String format(Date date) { + final Calendar calendar = Work.get().calendar; + calendar.setTime(date); + return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.DAY_OF_MONTH)); + } + }, + DDD("The day of the year as a decimal number (001-366)") { + @Override public String format(Date date) { + final Calendar calendar = Work.get().calendar; + calendar.setTime(date); + return String.format(Locale.ROOT, "%03d", calendar.get(Calendar.DAY_OF_YEAR)); + } + }, + DY("The abbreviated weekday name") { + @Override public String format(Date date) { + final Work work = Work.get(); + return work.eeeFormat.format(date); + } + }, + FF1("Fractional seconds to 1 digit") { + @Override public String format(Date date) { + final Work work = Work.get(); + return work.sFormat.format(date); + } + }, + FF2("Fractional seconds to 2 digits") { + @Override public String format(Date date) { + final Work work = Work.get(); + return work.ssFormat.format(date); + } + }, + FF3("Fractional seconds to 3 digits") { + @Override public String format(Date date) { + final Work work = Work.get(); + return work.sssFormat.format(date); + } + }, + FF4("Fractional seconds to 4 digits") { + @Override public String format(Date date) { + final Work work = Work.get(); + return work.ssssFormat.format(date); + } + }, + FF5("Fractional seconds to 5 digits") { + @Override public String format(Date date) { + final Work work = Work.get(); + return work.sssssFormat.format(date); + } + }, + FF6("Fractional seconds to 6 digits") { + @Override public String format(Date date) { + final Work work = Work.get(); + return work.ssssssFormat.format(date); + } + }, + HH24("The hour (24-hour clock) as a decimal number (00-23)") { + @Override public String format(Date date) { + final Calendar calendar = Work.get().calendar; + calendar.setTime(date); + return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.HOUR_OF_DAY)); + } + }, + IW("The ISO 8601 week number of the year (Monday as the first day of the week) " + + "as a decimal number (01-53)") { + @Override public String format(Date date) { + // TODO: ensure this is isoweek + final Calendar calendar = Work.get().calendar; + calendar.setTime(date); + calendar.setFirstDayOfWeek(Calendar.MONDAY); + return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.WEEK_OF_YEAR)); + } + }, + MI("The minute as a decimal number (00-59)") { + @Override public String format(Date date) { + final Calendar calendar = Work.get().calendar; + calendar.setTime(date); + return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.MINUTE)); + } + }, + MM("The month as a decimal number (01-12)") { + @Override public String format(Date date) { + final Calendar calendar = Work.get().calendar; + calendar.setTime(date); + return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.MONTH) + 1); + } + }, + MON("The abbreviated month name") { + @Override public String format(Date date) { + final Work work = Work.get(); + return work.mmmFormat.format(date); + } + }, + MONTH("The full month name (English)") { + @Override public String format(Date date) { + final Work work = Work.get(); + return work.mmmmFormat.format(date); + } + }, + Q("The quarter as a decimal number (1-4)") { + @Override public String format(Date date) { + final Calendar calendar = Work.get().calendar; + calendar.setTime(date); + return String.format(Locale.ROOT, "%d", (calendar.get(Calendar.MONTH) / 3) + 1); + } + }, + SS("The second as a decimal number (00-60)") { + @Override public String format(Date date) { + final Calendar calendar = Work.get().calendar; + calendar.setTime(date); + return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.SECOND)); + } + }, + TZR("The time zone name") { + @Override public String format(Date date) { + // TODO: how to support timezones? + throw new UnsupportedOperationException(); + } + }, + WW("The week number of the year (Sunday as the first day of the week) as a decimal " + + "number (00-53)") { + @Override public String format(Date date) { + final Calendar calendar = Work.get().calendar; + calendar.setTime(date); + calendar.setFirstDayOfWeek(Calendar.SUNDAY); + return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.WEEK_OF_YEAR)); + } + }, + YY("Last 2 digits of year") { + @Override public String format(Date date) { + final Work work = Work.get(); + return work.yyFormat.format(date); + } + }, + YYYY("The year with century as a decimal number") { + @Override public String format(Date date) { + final Calendar calendar = Work.get().calendar; + calendar.setTime(date); + return String.format(Locale.ROOT, "%d", calendar.get(Calendar.YEAR)); + } + }; + + private final String description; + + // TODO: be sure to deal with TZ + + FormatElementEnum(String description) { + this.description = description; + } + + @Override public String getDescription() { + return description; + } + + /** Work space. Provides a value for each mutable data structure that might + * be needed by a format element. Ensures thread-safety. */ + static class Work { + private static final ThreadLocal<@Nullable Work> THREAD_WORK = + ThreadLocal.withInitial(Work::new); + + /** Returns an instance of Work for this thread. */ + static Work get() { + return castNonNull(THREAD_WORK.get()); + } + + final Calendar calendar = + Calendar.getInstance(DateTimeUtils.DEFAULT_ZONE, Locale.ROOT); + final DateFormat eeeeFormat = new SimpleDateFormat("EEEE", Locale.ROOT); + final DateFormat eeeFormat = new SimpleDateFormat("EEE", Locale.ROOT); + final DateFormat mmmFormat = new SimpleDateFormat("MMM", Locale.ROOT); + final DateFormat mmmmFormat = new SimpleDateFormat("MMMM", Locale.ROOT); + final DateFormat sFormat = new SimpleDateFormat("S", Locale.ROOT); + final DateFormat ssFormat = new SimpleDateFormat("SS", Locale.ROOT); + final DateFormat sssFormat = new SimpleDateFormat("SSS", Locale.ROOT); + final DateFormat ssssFormat = new SimpleDateFormat("SSSS", Locale.ROOT); + final DateFormat sssssFormat = new SimpleDateFormat("SSSSS", Locale.ROOT); + final DateFormat ssssssFormat = new SimpleDateFormat("SSSSSS", Locale.ROOT); + final DateFormat yyFormat = new SimpleDateFormat("yy", Locale.ROOT); + } +} diff --git a/core/src/main/java/org/apache/calcite/util/format/FormatModel.java b/core/src/main/java/org/apache/calcite/util/format/FormatModel.java new file mode 100644 index 0000000000..39bac2f053 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/util/format/FormatModel.java @@ -0,0 +1,37 @@ +/* + * 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. + */ +package org.apache.calcite.util.format; + +import java.util.List; +import java.util.Map; + +/** + * Describes the format strings used by a formatting function such as + * {@code FORMAT_TIMESTAMP} or {@code CAST(string AS DATE FORMAT formatString)}. + * + * <p>Can parse a format string to a list of format elements. + * + * @see FormatModels + */ +public interface FormatModel { + /** Returns the map used to create the {@link FormatModel} instance. */ + Map<String, FormatElement> getElementMap(); + + /** Parses a format string using element identifiers supplied by + * {@code format}. */ + List<FormatElement> parse(String format); +} diff --git a/core/src/main/java/org/apache/calcite/util/format/FormatModels.java b/core/src/main/java/org/apache/calcite/util/format/FormatModels.java new file mode 100644 index 0000000000..be7a144b3a --- /dev/null +++ b/core/src/main/java/org/apache/calcite/util/format/FormatModels.java @@ -0,0 +1,278 @@ +/* + * 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. + */ +package org.apache.calcite.util.format; + +import com.google.common.collect.ImmutableList; +import com.google.common.collect.ImmutableMap; + +import java.util.Date; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.concurrent.ConcurrentHashMap; +import java.util.function.Consumer; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +import static org.apache.calcite.util.format.FormatElementEnum.D; +import static org.apache.calcite.util.format.FormatElementEnum.DAY; +import static org.apache.calcite.util.format.FormatElementEnum.DD; +import static org.apache.calcite.util.format.FormatElementEnum.DDD; +import static org.apache.calcite.util.format.FormatElementEnum.DY; +import static org.apache.calcite.util.format.FormatElementEnum.FF1; +import static org.apache.calcite.util.format.FormatElementEnum.FF2; +import static org.apache.calcite.util.format.FormatElementEnum.FF3; +import static org.apache.calcite.util.format.FormatElementEnum.FF4; +import static org.apache.calcite.util.format.FormatElementEnum.FF5; +import static org.apache.calcite.util.format.FormatElementEnum.FF6; +import static org.apache.calcite.util.format.FormatElementEnum.HH24; +import static org.apache.calcite.util.format.FormatElementEnum.IW; +import static org.apache.calcite.util.format.FormatElementEnum.MI; +import static org.apache.calcite.util.format.FormatElementEnum.MM; +import static org.apache.calcite.util.format.FormatElementEnum.MON; +import static org.apache.calcite.util.format.FormatElementEnum.MONTH; +import static org.apache.calcite.util.format.FormatElementEnum.Q; +import static org.apache.calcite.util.format.FormatElementEnum.SS; +import static org.apache.calcite.util.format.FormatElementEnum.TZR; +import static org.apache.calcite.util.format.FormatElementEnum.WW; +import static org.apache.calcite.util.format.FormatElementEnum.YY; +import static org.apache.calcite.util.format.FormatElementEnum.YYYY; + +import static java.util.Objects.requireNonNull; + +/** + * Utilities for {@link FormatModel}. + */ +public class FormatModels { + private FormatModels() { + } + + /** The format model consisting of built-in format elements. + * + * <p>Due to the design of {@link FormatElementEnum}, it is similar to + * Oracle's format model. + */ + public static final FormatModel DEFAULT; + + /** Format model for BigQuery. + * + * <p>BigQuery format element reference: + * <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements";> + * BigQuery Standard SQL Format Elements</a>. + */ + public static final FormatModel BIG_QUERY; + + static { + final Map<String, FormatElement> map = new HashMap<>(); + for (FormatElementEnum fe : FormatElementEnum.values()) { + map.put(fe.toString(), fe); + } + DEFAULT = create(map); + + map.clear(); + map.put("%A", DAY); + map.put("%a", DY); + map.put("%B", MONTH); + map.put("%b", MON); + map.put("%c", + compositeElement("The date and time representation (English);", + DY, literalElement(" "), MON, literalElement(" "), + DD, literalElement(" "), HH24, literalElement(":"), + MI, literalElement(":"), SS, literalElement(" "), + YYYY)); + map.put("%d", DD); + map.put("%E1S", FF1); + map.put("%E2S", FF2); + map.put("%E3S", FF3); + map.put("%E4S", FF4); + map.put("%E5S", FF5); + map.put("%E*S", FF6); + map.put("%H", HH24); + map.put("%j", DDD); + map.put("%M", MI); + map.put("%m", MM); + map.put("%Q", Q); + map.put("%R", + compositeElement("The time in the format %H:%M", + HH24, literalElement(":"), MI)); + map.put("%S", SS); + map.put("%u", D); + map.put("%V", IW); + map.put("%W", WW); + map.put("%x", + compositeElement("The date representation in MM/DD/YY format", + MM, literalElement("/"), DD, literalElement("/"), YY)); + map.put("%Y", YYYY); + map.put("%y", YY); + map.put("%Z", TZR); + BIG_QUERY = create(map); + } + + /** + * Generates a {@link Pattern} using the keys of a {@link FormatModel} element + * map. This pattern is used in {@link FormatModel#parse(String)} to help + * locate known format elements in a string. + */ + private static Pattern regexFromMap(Map<String, FormatElement> elementMap) { + StringBuilder regex = new StringBuilder(); + for (String key : elementMap.keySet()) { + regex.append("(").append(Pattern.quote(key)).append(")|"); + } + // remove the last '|' + regex.setLength(regex.length() - 1); + return Pattern.compile(regex.toString()); + } + + /** + * Creates a {@link FormatModel} that uses the provided map to identify + * {@link FormatElement}s while parsing a format string. + */ + public static FormatModel create(Map<String, FormatElement> elementMap) { + final Pattern pattern = regexFromMap(elementMap); + return new FormatModelImpl(pattern, elementMap); + } + + /** + * Creates a literal format element. + */ + public static FormatElement literalElement(String literal) { + return new FormatModelElementLiteral(literal); + } + + /** + * Creates a composite format element from the provided list of elements + * and description. + */ + public static FormatElement compositeElement(String description, + FormatElement... fmtElements) { + return new CompositeFormatElement(ImmutableList.copyOf(fmtElements), + description); + } + + + /** Implementation of {@link FormatModel} based on a list of format + * elements. */ + private static class FormatModelImpl implements FormatModel { + final Pattern pattern; + final Map<String, FormatElement> elementMap; + + /** Cache of parsed format strings. + * + * <p>NOTE: The current implementation could grow without bounds. + * A per-thread cache would be better, or a cache tied to a statement + * execution (e.g. in DataContext). Also limit to a say 100 entries. */ + final Map<String, List<FormatElement>> memoizedElements = + new ConcurrentHashMap<>(); + + FormatModelImpl(Pattern pattern, Map<String, FormatElement> elementMap) { + this.pattern = requireNonNull(pattern, "pattern"); + this.elementMap = ImmutableMap.copyOf(elementMap); + } + + @Override public Map<String, FormatElement> getElementMap() { + return elementMap; + } + + private List<FormatElement> internalParse(String format) { + final ImmutableList.Builder<FormatElement> elements = + ImmutableList.builder(); + final Matcher matcher = pattern.matcher(format); + int i = 0; + String literal; + while (matcher.find()) { + // Add any leading literal text before next element match + literal = format.substring(i, matcher.start()); + if (!literal.isEmpty()) { + elements.add(literalElement(literal)); + } + // add the element match - use literal as default to be safe. + String key = matcher.group(); + elements.add(getElementMap().getOrDefault(key, literalElement(key))); + i = matcher.end(); + } + // add any remaining literal text after last element match + literal = format.substring(i); + if (!literal.isEmpty()) { + elements.add(literalElement(literal)); + } + return elements.build(); + } + + @Override public List<FormatElement> parse(String format) { + return memoizedElements.computeIfAbsent(format, this::internalParse); + } + } + + /** + * A format element that is literal text. + */ + private static class FormatModelElementLiteral implements FormatElement { + private final String literal; + + FormatModelElementLiteral(String literal) { + this.literal = requireNonNull(literal, "literal"); + } + + @Override public String format(Date date) { + return literal; + } + + @Override public String getDescription() { + return "Represents literal text in a format string"; + } + + @Override public String toString() { + return this.literal; + } + } + + /** + * A format element comprised of one or more {@link FormatElement} entries. + */ + private static class CompositeFormatElement implements FormatElement { + private final String description; + private final List<FormatElement> formatElements; + + CompositeFormatElement(List<FormatElement> formatElements, + String description) { + this.formatElements = ImmutableList.copyOf(formatElements); + this.description = requireNonNull(description, "description"); + } + + @Override public String format(Date date) { + StringBuilder buf = new StringBuilder(); + flatten(ele -> buf.append(ele.format(date))); + return buf.toString(); + } + + /** + * Applies a consumer to each format element that make up the composite + * element. + * + * <p>For example, {@code %R} in Google SQL represents the hour in 24-hour + * format (e.g., 00..23) followed by the minute as a decimal number. + * {@code flatten(i -> println(i.toString())); } would print "HH24:MI". + */ + @Override public void flatten(Consumer<FormatElement> consumer) { + formatElements.forEach(consumer); + } + + @Override public String getDescription() { + return this.description; + } + } +} diff --git a/core/src/main/java/org/apache/calcite/util/format/package-info.java b/core/src/main/java/org/apache/calcite/util/format/package-info.java new file mode 100644 index 0000000000..88ed6b551c --- /dev/null +++ b/core/src/main/java/org/apache/calcite/util/format/package-info.java @@ -0,0 +1,21 @@ +/* + * 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. + */ + +/** + * Utility classes for handling format strings. + */ +package org.apache.calcite.util.format; diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java index 827592089a..83f1fd13d1 100644 --- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java +++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java @@ -1891,6 +1891,41 @@ class RelToSqlConverterTest { sql(timeTrunc).withLibrary(SqlLibrary.BIG_QUERY).ok(expectedTimeTrunc); } + @Test void testBigQueryDatetimeFormatFunctions() { + final String formatTime = "select format_time('%H', time '12:45:30')\n" + + "from \"foodmart\".\"product\"\n"; + final String formatDate = "select format_date('%b-%d-%Y', date '2012-02-03')\n" + + "from \"foodmart\".\"product\"\n"; + final String formatTimestamp = "select format_timestamp('%b-%d-%Y',\n" + + " timestamp with local time zone '2012-02-03 12:30:40')\n" + + "from \"foodmart\".\"product\"\n"; + final String formatDatetime = "select format_datetime('%R',\n" + + " timestamp '2012-02-03 12:34:34')\n" + + "from \"foodmart\".\"product\"\n"; + + final String expectedBqFormatTime = + "SELECT FORMAT_TIME('%H', TIME '12:45:30')\n" + + "FROM foodmart.product"; + final String expectedBqFormatDate = + "SELECT FORMAT_DATE('%b-%d-%Y', DATE '2012-02-03')\n" + + "FROM foodmart.product"; + final String expectedBqFormatTimestamp = + "SELECT FORMAT_TIMESTAMP('%b-%d-%Y', TIMESTAMP_WITH_LOCAL_TIME_ZONE '2012-02-03 12:30:40')\n" + + "FROM foodmart.product"; + final String expectedBqFormatDatetime = + "SELECT FORMAT_DATETIME('%R', TIMESTAMP '2012-02-03 12:34:34')\n" + + "FROM foodmart.product"; + final Sql sql = fixture().withBigQuery().withLibrary(SqlLibrary.BIG_QUERY); + sql.withSql(formatTime) + .ok(expectedBqFormatTime); + sql.withSql(formatDate) + .ok(expectedBqFormatDate); + sql.withSql(formatTimestamp) + .ok(expectedBqFormatTimestamp); + sql.withSql(formatDatetime) + .ok(expectedBqFormatDatetime); + } + /** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-3220";>[CALCITE-3220] * HiveSqlDialect should transform the SQL-standard TRIM function to TRIM, diff --git a/core/src/test/java/org/apache/calcite/util/FormatModelTest.java b/core/src/test/java/org/apache/calcite/util/FormatModelTest.java new file mode 100644 index 0000000000..b313b652e0 --- /dev/null +++ b/core/src/test/java/org/apache/calcite/util/FormatModelTest.java @@ -0,0 +1,67 @@ +/* + * 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. + */ +package org.apache.calcite.util; + +import org.apache.calcite.util.format.FormatElement; +import org.apache.calcite.util.format.FormatModel; +import org.apache.calcite.util.format.FormatModels; + +import org.hamcrest.Matcher; +import org.junit.jupiter.api.Test; + +import java.util.ArrayList; +import java.util.Arrays; +import java.util.Collections; +import java.util.List; + +import static org.hamcrest.MatcherAssert.assertThat; +import static org.hamcrest.Matchers.is; + +/** + * Unit test for {@link FormatModel}. + */ +public class FormatModelTest { + + private void assertThatFormatElementParse(String formatString, + Matcher<List<String>> matcher) { + List<FormatElement> elements = FormatModels.BIG_QUERY.parse(formatString); + List<String> stringResults = new ArrayList<>(); + for (FormatElement element : elements) { + element.flatten(i -> stringResults.add(i.toString())); + } + assertThat(stringResults, matcher); + } + + @Test void testSingleElement() { + assertThatFormatElementParse("%j", is(Collections.singletonList("DDD"))); + } + + @Test void testMultipleElements() { + assertThatFormatElementParse("%b-%d-%Y", + is(Arrays.asList("MON", "-", "DD", "-", "YYYY"))); + } + + @Test void testArbitraryText() { + assertThatFormatElementParse("%jtext%b", + is(Arrays.asList("DDD", "text", "MON"))); + } + + @Test void testAliasText() { + assertThatFormatElementParse("%R", + is(Arrays.asList("HH24", ":", "MI"))); + } +} diff --git a/site/_docs/reference.md b/site/_docs/reference.md index cf30819fc2..9fef9858f1 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -2659,6 +2659,10 @@ BigQuery's type system uses confusingly different names for types and functions: | 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 | o | EXISTSNODE(xml, xpath, [, namespaces ]) | Determines whether traversal of a XML document using a specified xpath results in any nodes. Returns 0 if no nodes remain after applying the XPath traversal on the document fragment of the element or elements matched by the XPath expression. Returns 1 if any nodes remain. The optional namespace value that specifies a default mapping or namespace mapping for prefixes, which is used when evaluating the XPath expression. | m | EXTRACTVALUE(xml, xpathExpr)) | Returns the text of the first text node which is a child of the element or elements matched by the XPath expression. +| b | FORMAT_DATE(string, date) | Formats *date* according to the specified format *string* +| b | FORMAT_DATETIME(string, timestamp) | Formats *timestamp* according to the specified format *string* +| b | FORMAT_TIME(string, time) | Formats *time* according to the specified format *string* +| b | FORMAT_TIMESTAMP(string timestamp) | Formats *timestamp* according to the specified format *string* | b o | GREATEST(expr [, expr ]*) | Returns the greatest of the expressions | b h s | IF(condition, value1, value2) | Returns *value1* if *condition* is TRUE, *value2* otherwise | b | IFNULL(value1, value2) | Equivalent to `NVL(value1, value2)` diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java index 45e2a65916..a51531284e 100644 --- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java +++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java @@ -8568,6 +8568,95 @@ public class SqlOperatorTest { "2014-12-29", "DATE NOT NULL"); } + @Test void testFormatTime() { + final SqlOperatorFixture f = fixture() + .withLibrary(SqlLibrary.BIG_QUERY) + .setFor(SqlLibraryOperators.FORMAT_TIME); + f.checkFails("^FORMAT_TIME('%x', timestamp '2008-12-25 15:30:00')^", + "Cannot apply 'FORMAT_TIME' to arguments of type " + + "'FORMAT_TIME\\(<CHAR\\(2\\)>, <TIMESTAMP\\(0\\)>\\)'\\. " + + "Supported form\\(s\\): " + + "'FORMAT_TIME\\(<CHARACTER>, <TIME>\\)'", + false); + f.checkScalar("FORMAT_TIME('%H', TIME '12:34:33')", + "12", + "VARCHAR(2000) NOT NULL"); + f.checkScalar("FORMAT_TIME('%R', TIME '12:34:33')", + "12:34", + "VARCHAR(2000) NOT NULL"); + f.checkScalar("FORMAT_TIME('The time is %M-%S', TIME '12:34:33')", + "The time is 34-33", + "VARCHAR(2000) NOT NULL"); + } + + @Test void testFormatDate() { + final SqlOperatorFixture f = fixture() + .withLibrary(SqlLibrary.BIG_QUERY) + .setFor(SqlLibraryOperators.FORMAT_DATE); + f.checkFails("^FORMAT_DATE('%x', 123)^", + "Cannot apply 'FORMAT_DATE' to arguments of type " + + "'FORMAT_DATE\\(<CHAR\\(2\\)>, <INTEGER>\\)'\\. " + + "Supported form\\(s\\): " + + "'FORMAT_DATE\\(<CHARACTER>, <DATE>\\)'", + false); + // Can implicitly cast TIMESTAMP to DATE + f.checkScalar("FORMAT_DATE('%x', timestamp '2008-12-25 15:30:00')", + "12/25/08", + "VARCHAR(2000) NOT NULL"); + f.checkScalar("FORMAT_DATE('%b-%d-%Y', DATE '2008-12-25')", + "Dec-25-2008", + "VARCHAR(2000) NOT NULL"); + f.checkScalar("FORMAT_DATE('%b %Y', DATE '2008-12-25')", + "Dec 2008", + "VARCHAR(2000) NOT NULL"); + f.checkScalar("FORMAT_DATE('%x', DATE '2008-12-25')", + "12/25/08", + "VARCHAR(2000) NOT NULL"); + f.checkScalar("FORMAT_DATE('The date is: %x', DATE '2008-12-25')", + "The date is: 12/25/08", + "VARCHAR(2000) NOT NULL"); + } + + @Test void testFormatTimestamp() { + final SqlOperatorFixture f = fixture() + .withLibrary(SqlLibrary.BIG_QUERY) + .setFor(SqlLibraryOperators.FORMAT_TIMESTAMP); + f.checkFails("^FORMAT_TIMESTAMP('%x', 123)^", + "Cannot apply 'FORMAT_TIMESTAMP' to arguments of type " + + "'FORMAT_TIMESTAMP\\(<CHAR\\(2\\)>, <INTEGER>\\)'\\. " + + "Supported form\\(s\\): " + + "FORMAT_TIMESTAMP\\(<CHARACTER>, " + + "<TIMESTAMP WITH LOCAL TIME ZONE>\\)\n" + + "FORMAT_TIMESTAMP\\(<CHARACTER>, " + + "<TIMESTAMP WITH LOCAL TIME ZONE>, <CHARACTER>\\)", + false); + f.checkScalar("FORMAT_TIMESTAMP('%c'," + + " TIMESTAMP WITH LOCAL TIME ZONE '2008-12-25 15:30:00')", + "Thu Dec 25 15:30:00 2008", + "VARCHAR(2000) NOT NULL"); + f.checkScalar("FORMAT_TIMESTAMP('%b-%d-%Y'," + + " TIMESTAMP WITH LOCAL TIME ZONE '2008-12-25 15:30:00')", + "Dec-25-2008", + "VARCHAR(2000) NOT NULL"); + f.checkScalar("FORMAT_TIMESTAMP('%b %Y'," + + " TIMESTAMP WITH LOCAL TIME ZONE '2008-12-25 15:30:00')", + "Dec 2008", + "VARCHAR(2000) NOT NULL"); + f.checkScalar("FORMAT_TIMESTAMP('%x'," + + " TIMESTAMP WITH LOCAL TIME ZONE '2008-12-25 15:30:00')", + "12/25/08", + "VARCHAR(2000) NOT NULL"); + f.checkScalar("FORMAT_TIMESTAMP('The time is: %R'," + + " TIMESTAMP WITH LOCAL TIME ZONE '2008-12-25 15:30:00')", + "The time is: 15:30", + "VARCHAR(2000) NOT NULL"); + f.checkScalar("FORMAT_TIMESTAMP('The time is: %R.%E2S'," + + " TIMESTAMP WITH LOCAL TIME ZONE '2008-12-25 15:30:00.1235456')", + "The time is: 15:30.123", + "VARCHAR(2000) NOT NULL"); + } + + @Test void testDenseRankFunc() { final SqlOperatorFixture f = fixture(); f.setFor(SqlStdOperatorTable.DENSE_RANK, VM_FENNEL, VM_JAVA);
