This is an automated email from the ASF dual-hosted git repository.
cancai pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 9caceac13d [CALCITE-6810] Update tests for CONVERT_TIMEZONE and CONCAT
function in RedShift library
9caceac13d is described below
commit 9caceac13d48cc73efb6b2a477699832b804abbb
Author: Zhe Hu <[email protected]>
AuthorDate: Thu Jan 30 12:40:02 2025 +0800
[CALCITE-6810] Update tests for CONVERT_TIMEZONE and CONCAT function in
RedShift library
---
babel/src/test/resources/sql/redshift.iq | 8 +++-----
.../main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java | 6 ++----
.../java/org/apache/calcite/materialize/LatticeSuggesterTest.java | 7 ++++---
core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java | 2 +-
site/_docs/reference.md | 2 +-
5 files changed, 11 insertions(+), 14 deletions(-)
diff --git a/babel/src/test/resources/sql/redshift.iq
b/babel/src/test/resources/sql/redshift.iq
index f706ecb164..14e1023efc 100755
--- a/babel/src/test/resources/sql/redshift.iq
+++ b/babel/src/test/resources/sql/redshift.iq
@@ -106,9 +106,6 @@
#
# pluralInterval - allow INTERVAL '2' DAYS as well as INTERVAL '2' DAY;
[CALCITE-3383]
#
-# TODO:
-# * Why does CONCAT not work?
-#
!use scott-redshift
!set outputformat csv
@@ -1748,8 +1745,9 @@ A
# CONCAT (Oracle Compatibility Function)
select concat('a', 'b');
-SELECT "CONCAT"('a', 'b')
-!explain-validated-on calcite
+EXPR$0
+ab
+!ok
# CRC32
-- returns 'f2726906'
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 9028e16c7b..71c46a1aa8 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
@@ -102,10 +102,8 @@ private SqlLibraryOperators() {
OperandTypes.MEASURE);
/** 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})
+ * converts the timezone of {@code datetime} from {@code tz1} to {@code
tz2}. */
+ @LibraryOperator(libraries = {REDSHIFT})
public static final SqlFunction CONVERT_TIMEZONE =
SqlBasicFunction.create("CONVERT_TIMEZONE",
ReturnTypes.DATE_NULLABLE, OperandTypes.CHARACTER_CHARACTER_DATETIME,
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 4203a1b7fe..8cefa4c205 100644
---
a/core/src/test/java/org/apache/calcite/materialize/LatticeSuggesterTest.java
+++
b/core/src/test/java/org/apache/calcite/materialize/LatticeSuggesterTest.java
@@ -614,12 +614,13 @@ private void checkDerivedColumn(Lattice lattice,
List<String> tables,
@Test void testRedshiftDialect() throws Exception {
final Tester t = new Tester().foodmart().withEvolve(true)
.withDialect(SqlDialect.DatabaseProduct.REDSHIFT.getDialect())
- .withLibrary(SqlLibrary.POSTGRESQL);
+ .withLibrary(SqlLibrary.REDSHIFT);
final String q0 = "select\n"
- + " CONCAT(\"fname\", ' ', \"lname\") as \"full_name\",\n"
+ // CONCAT function in RedShift only accepts two arguments
+ + " CONCAT(\"fname\", \"lname\") as \"full_name\",\n"
+ " convert_timezone('UTC', 'America/Los_Angeles',\n"
- + " cast('2019-01-01 01:00:00' as timestamp)),\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"
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 b25bf13957..9fc0a0f5e3 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -1631,7 +1631,7 @@ void testLikeAndSimilarFails() {
.fails("No match found for function signature "
+ "CONVERT_TIMEZONE\\(<CHARACTER>, <CHARACTER>, <TIMESTAMP>\\)");
- final SqlOperatorTable opTable = operatorTableFor(SqlLibrary.POSTGRESQL);
+ final SqlOperatorTable opTable = operatorTableFor(SqlLibrary.REDSHIFT);
expr("CONVERT_TIMEZONE('UTC', 'America/Los_Angeles',\n"
+ " CAST('2000-01-01' AS TIMESTAMP))")
.withOperatorTable(opTable)
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 48d2d62a97..3a6ae0238a 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2838,7 +2838,7 @@ ### Dialect-specific Operators
| b | CONTAINS_SUBSTR(expression, string [ , json_scope => json_scope_value
]) | Returns whether *string* exists as a substring in *expression*. Optional
*json_scope* argument specifies what scope to search if *expression* is in JSON
format. Returns NULL if a NULL exists in *expression* that does not result in a
match
| q | CONVERT(type, expression [ , style ]) | Equivalent to
`CAST(expression AS type)`; ignores the *style* operand
| o | CONVERT(string, destCharSet[, srcCharSet]) | Converts *string* from
*srcCharSet* to *destCharSet*. If the *srcCharSet* parameter is not specified,
then it uses the default CharSet
-| p r | CONVERT_TIMEZONE(tz1, tz2, datetime) | Converts the timezone
of *datetime* from *tz1* to *tz2*
+| r | CONVERT_TIMEZONE(tz1, tz2, datetime) | Converts the timezone
of *datetime* from *tz1* to *tz2*
| p | COSD(numeric) | Returns the cosine of
*numeric* in degrees as a double. Returns NaN if *numeric* is NaN. Fails if
*numeric* is greater than the maximum double value.
| * | COSH(numeric) | Returns the hyperbolic
cosine of *numeric*
| * | COTH(numeric) | Returns the hyperbolic
cotangent of *numeric*
