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 e8b08c490ab4270945c35f31846cd36b5788cc23 Author: Lindsey Meyer <[email protected]> AuthorDate: Wed Aug 14 11:34:15 2019 -0700 [CALCITE-3252] Add CONVERT_TIMEZONE, TO_DATE and TO_TIMESTAMP non-standard functions (Lindsey Meyer) CONVERT_TIMEZONE is for Redshift; TO_DATE and TO_TIMESTAMP are for PostgreSQL (including Redshift) and Oracle. Close apache/calcite#1380 --- .../apache/calcite/sql/SqlJdbcFunctionCall.java | 7 ++- .../calcite/sql/fun/SqlLibraryOperators.java | 35 ++++++++++++++ .../org/apache/calcite/sql/type/OperandTypes.java | 3 ++ .../calcite/materialize/LatticeSuggesterTest.java | 8 +++- .../org/apache/calcite/test/SqlValidatorTest.java | 56 ++++++++++++++++++++++ site/_docs/reference.md | 3 ++ 6 files changed, 108 insertions(+), 4 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java index d778908..9757b1f 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java @@ -382,9 +382,9 @@ public class SqlJdbcFunctionCall extends SqlFunction { /** List of all time/date function names defined by JDBC. */ private static final String TIME_DATE_FUNCTIONS = constructFuncList( - "CURDATE", "CURTIME", "DAYNAME", "DAYOFMONTH", "DAYOFWEEK", + "CONVERT_TIMEZONE", "CURDATE", "CURTIME", "DAYNAME", "DAYOFMONTH", "DAYOFWEEK", "DAYOFYEAR", "HOUR", "MINUTE", "MONTH", "MONTHNAME", "NOW", - "QUARTER", "SECOND", "TIMESTAMPADD", "TIMESTAMPDIFF", + "QUARTER", "SECOND", "TIMESTAMPADD", "TIMESTAMPDIFF", "TO_DATE", "TO_TIMESTAMP", "WEEK", "YEAR"); /** List of all system function names defined by JDBC. */ @@ -729,11 +729,14 @@ public class SqlJdbcFunctionCall extends SqlFunction { map.put("MONTHNAME", simple(SqlLibraryOperators.MONTHNAME)); map.put("SECOND", simple(SqlStdOperatorTable.SECOND)); + map.put("CONVERT_TIMEZONE", simple(SqlLibraryOperators.CONVERT_TIMEZONE)); map.put("CURDATE", simple(SqlStdOperatorTable.CURRENT_DATE)); map.put("CURTIME", simple(SqlStdOperatorTable.LOCALTIME)); map.put("NOW", simple(SqlStdOperatorTable.CURRENT_TIMESTAMP)); map.put("TIMESTAMPADD", simple(SqlStdOperatorTable.TIMESTAMP_ADD)); map.put("TIMESTAMPDIFF", simple(SqlStdOperatorTable.TIMESTAMP_DIFF)); + map.put("TO_DATE", simple(SqlLibraryOperators.TO_DATE)); + map.put("TO_TIMESTAMP", simple(SqlLibraryOperators.TO_TIMESTAMP)); map.put("DATABASE", simple(SqlStdOperatorTable.CURRENT_CATALOG)); map.put("IFNULL", 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 6acadec..896b9f7 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 @@ -49,6 +49,19 @@ public abstract class SqlLibraryOperators { private SqlLibraryOperators() { } + /** The "CONVERT_TIMEZONE(tz1, tz2, datetime)" function; + * converts the timezone of {@code datetime} from {@code tz1} to {@code tz2}. + * This function is only on Redshift, but we list it in PostgreSQL + * because Redshift does not have its own library. */ + @LibraryOperator(libraries = {POSTGRESQL}) + public static final SqlFunction CONVERT_TIMEZONE = + new SqlFunction("CONVERT_TIMEZONE", + SqlKind.OTHER_FUNCTION, + ReturnTypes.DATE_NULLABLE, + null, + OperandTypes.CHARACTER_CHARACTER_DATETIME, + SqlFunctionCategory.TIMEDATE); + /** Return type inference for {@code DECODE}. */ private static final SqlReturnTypeInference DECODE_RETURN_TYPE = opBinding -> { @@ -269,6 +282,28 @@ public abstract class SqlLibraryOperators { OperandTypes.or(OperandTypes.STRING, OperandTypes.BINARY), SqlFunctionCategory.STRING); + /** The "TO_DATE(string1, string2)" function; casts string1 + * to a DATE using the format specified in string2. */ + @LibraryOperator(libraries = {POSTGRESQL, ORACLE}) + public static final SqlFunction TO_DATE = + new SqlFunction("TO_DATE", + SqlKind.OTHER_FUNCTION, + ReturnTypes.DATE_NULLABLE, + null, + OperandTypes.STRING_STRING, + SqlFunctionCategory.TIMEDATE); + + /** The "TO_TIMESTAMP(string1, string2)" function; casts string1 + * to a TIMESTAMP using the format specified in string2. */ + @LibraryOperator(libraries = {POSTGRESQL, ORACLE}) + public static final SqlFunction TO_TIMESTAMP = + new SqlFunction("TO_TIMESTAMP", + SqlKind.OTHER_FUNCTION, + ReturnTypes.DATE_NULLABLE, + null, + OperandTypes.STRING_STRING, + 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 ba1cd10..5f24058 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 @@ -240,6 +240,9 @@ public abstract class OperandTypes { public static final SqlSingleOperandTypeChecker INTERVAL = family(SqlTypeFamily.DATETIME_INTERVAL); + public static final SqlSingleOperandTypeChecker CHARACTER_CHARACTER_DATETIME = + family(SqlTypeFamily.CHARACTER, SqlTypeFamily.CHARACTER, SqlTypeFamily.DATETIME); + public static final SqlSingleOperandTypeChecker PERIOD = new PeriodOperandTypeChecker(); diff --git a/core/src/test/java/org/apache/calcite/materialize/LatticeSuggesterTest.java b/core/src/test/java/org/apache/calcite/materialize/LatticeSuggesterTest.java index d996965..6043eda 100644 --- a/core/src/test/java/org/apache/calcite/materialize/LatticeSuggesterTest.java +++ b/core/src/test/java/org/apache/calcite/materialize/LatticeSuggesterTest.java @@ -594,14 +594,18 @@ public class LatticeSuggesterTest { assertThat(derivedColumns.get(1).tables, is(tables)); } - @Test public void testDialect() throws Exception { + @Test public void testRedshiftDialect() throws Exception { final Tester t = new Tester().foodmart().withEvolve(true) .withDialect(SqlDialect.DatabaseProduct.REDSHIFT.getDialect()) .withLibrary(SqlLibrary.POSTGRESQL); final String q0 = "select\n" - + " left(\"fname\", 1) as \"initial\",\n" + " CONCAT(\"fname\", ' ', \"lname\") as \"full_name\",\n" + + " convert_timezone('UTC', 'America/Los_Angeles',\n" + + " cast('2019-01-01 01:00:00' as timestamp)),\n" + + " left(\"fname\", 1) as \"initial\",\n" + + " to_date('2019-01-01', 'YYYY-MM-DD'),\n" + + " to_timestamp('2019-01-01 01:00:00', 'YYYY-MM-DD HH:MM:SS'),\n" + " count(*) as c,\n" + " avg(\"total_children\" - \"num_children_at_home\")\n" + "from \"customer\" join \"sales_fact_1997\" using (\"customer_id\")\n" diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java index 54e4589..5fe0a66 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -1217,6 +1217,62 @@ public class SqlValidatorTest extends SqlValidatorTestCase { checkExp("CAST( '2004-12-21 10:12:21' AS TIMESTAMP)"); } + @Test public void testConvertTimezoneFunction() { + checkWholeExpFails( + "CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', CAST('2000-01-01' AS TIMESTAMP))", + "No match found for function signature CONVERT_TIMEZONE\\(<CHARACTER>, <CHARACTER>, <TIMESTAMP>\\)"); + tester = tester.withOperatorTable( + SqlLibraryOperatorTableFactory.INSTANCE + .getOperatorTable(SqlLibrary.STANDARD, SqlLibrary.POSTGRESQL)); + checkExpType( + "CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', CAST('2000-01-01' AS TIMESTAMP))", + "DATE NOT NULL"); + checkWholeExpFails("CONVERT_TIMEZONE('UTC', 'America/Los_Angeles')", + "Invalid number of arguments to function 'CONVERT_TIMEZONE'. Was expecting 3 arguments"); + checkWholeExpFails("CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', '2000-01-01')", + "Cannot apply 'CONVERT_TIMEZONE' to arguments of type 'CONVERT_TIMEZONE\\(<CHAR\\(3\\)>, <CHAR\\(19\\)>, " + + "<CHAR\\(10\\)>\\)'\\. Supported form\\(s\\): 'CONVERT_TIMEZONE\\(<CHARACTER>, <CHARACTER>, <DATETIME>\\)'"); + checkWholeExpFails("CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', " + + "'UTC', CAST('2000-01-01' AS TIMESTAMP))", + "Invalid number of arguments to function 'CONVERT_TIMEZONE'. Was expecting 3 arguments"); + } + + @Test public void testToDateFunction() { + checkWholeExpFails( + "TO_DATE('2000-01-01', 'YYYY-MM-DD')", + "No match found for function signature TO_DATE\\(<CHARACTER>, <CHARACTER>\\)"); + tester = tester.withOperatorTable( + SqlLibraryOperatorTableFactory.INSTANCE + .getOperatorTable(SqlLibrary.STANDARD, SqlLibrary.POSTGRESQL)); + checkExpType("TO_DATE('2000-01-01', 'YYYY-MM-DD')", + "DATE NOT NULL"); + checkWholeExpFails("TO_DATE('2000-01-01')", + "Invalid number of arguments to function 'TO_DATE'. Was expecting 2 arguments"); + checkWholeExpFails("TO_DATE(2000, 'YYYY')", + "Cannot apply 'TO_DATE' to arguments of type 'TO_DATE\\(<INTEGER>, <CHAR\\(4\\)>\\)'\\. " + + "Supported form\\(s\\): 'TO_DATE\\(<STRING>, <STRING>\\)'"); + checkWholeExpFails("TO_DATE('2000-01-01', 'YYYY-MM-DD', 'YYYY-MM-DD')", + "Invalid number of arguments to function 'TO_DATE'. Was expecting 2 arguments"); + } + + @Test public void testToTimestampFunction() { + checkWholeExpFails( + "TO_TIMESTAMP('2000-01-01 01:00:00', 'YYYY-MM-DD HH:MM:SS')", + "No match found for function signature TO_TIMESTAMP\\(<CHARACTER>, <CHARACTER>\\)"); + tester = tester.withOperatorTable( + SqlLibraryOperatorTableFactory.INSTANCE + .getOperatorTable(SqlLibrary.STANDARD, SqlLibrary.POSTGRESQL)); + checkExpType("TO_TIMESTAMP('2000-01-01 01:00:00', 'YYYY-MM-DD HH:MM:SS')", + "DATE NOT NULL"); + checkWholeExpFails("TO_TIMESTAMP('2000-01-01 01:00:00')", + "Invalid number of arguments to function 'TO_TIMESTAMP'. Was expecting 2 arguments"); + checkWholeExpFails("TO_TIMESTAMP(2000, 'YYYY')", + "Cannot apply 'TO_TIMESTAMP' to arguments of type 'TO_TIMESTAMP\\(<INTEGER>, <CHAR\\(4\\)>\\)'\\. " + + "Supported form\\(s\\): 'TO_TIMESTAMP\\(<STRING>, <STRING>\\)'"); + checkWholeExpFails("TO_TIMESTAMP('2000-01-01 01:00:00', 'YYYY-MM-DD HH:MM:SS', 'YYYY-MM-DD')", + "Invalid number of arguments to function 'TO_TIMESTAMP'. Was expecting 2 arguments"); + } + @Test public void testInvalidFunction() { checkWholeExpFails("foo()", "No match found for function signature FOO.."); checkWholeExpFails("mod(123)", diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 327dac4..72de0f5 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -2173,6 +2173,7 @@ semantics. | p | expr :: type | Casts *expr* to *type* | o | CHR(integer) | Returns the character having the binary equivalent to *integer* as a CHAR value | m o p | CONCAT(string [, string ]*) | Concatenates two or more strings +| p | CONVERT_TIMEZONE(tz1, tz2, datetime) | Converts the timezone of *datetime* from *tz1* to *tz2* | 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 | GREATEST(expr [, expr ]*) | Returns the greatest of the expressions @@ -2198,6 +2199,8 @@ 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 [, substring_length ]) | Returns a portion of *string*, beginning at character *position*, *substring_length* characters long. SUBSTR calculates lengths using characters as defined by the input character set +| 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 Note:
