You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2019/08/20 22:04:10 UTC
[calcite] 04/06: [CALCITE-3252] Add CONVERT_TIMEZONE,
TO_DATE and TO_TIMESTAMP non-standard functions (Lindsey Meyer)
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 <la...@users.noreply.github.com>
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: