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
The following commit(s) were added to refs/heads/main by this push:
new cdb6522d33 [CALCITE-6444] Add a function library for Amazon Redshift
cdb6522d33 is described below
commit cdb6522d3320776f6c7ce66c01747447a39a8c84
Author: Norman Jordan <[email protected]>
AuthorDate: Fri Jun 21 13:32:51 2024 -0700
[CALCITE-6444] Add a function library for Amazon Redshift
To use the Redshift library, add 'fun=redshift' to your
connect string. The Redshift library contains all functions
in the Postgres library by default, unless you add
`excludeLibraries` in the annotation on the function
definition, and of course you can add functions that are
Redshift-only.
* A SqlLibrary can now have parent.
* SqlLibraries will inherit functions from the parent by default.
* An SqlOperator can specify libraries that it should be excluded from;
it is only needed when a library might inherit the function.
* The new Amazon Redshift function library extends the PostgreSQL
function library.
PostgreSQL function library:
* Removed ENDS_WITH function
Amazon Redshift function library is as Postgres, except:
* Added DECODE function
* Added NVL function
* Added NVL2 function
* Added GREATEST function
* Added LEAST function
* Added REGEXP_REPLACE
* Added CONCAT function (only 2 arguments)
* Added TO_CHAR function (default implementation)
* Removed STARTS_WITH function
* Removed ARRAY_AGG function
* Removed ARRAY_CONCAT_AGG function
* Removed STRING_AGG function
* Removed ILIKE function
* Removed NOT_ILIKE function
* Removed CONCAT_FUNCTION_WITH_NULL function
* Removed CONCAT function (variable arguments)
* Removed CONCAT_WS function
* Removed TO_CHAR (PostgreSQL implementation)
* Removed SHA256 function
* Removed SHA512 function
Close apache/calcite#3829
---
.../org/apache/calcite/test/BabelQuidemTest.java | 2 +-
.../apache/calcite/sql/fun/LibraryOperator.java | 15 +
.../org/apache/calcite/sql/fun/SqlLibrary.java | 50 ++-
.../sql/fun/SqlLibraryOperatorTableFactory.java | 18 +
.../calcite/sql/fun/SqlLibraryOperators.java | 55 +--
.../java/org/apache/calcite/util/UtilTest.java | 8 +-
site/_docs/reference.md | 73 ++--
.../org/apache/calcite/test/SqlOperatorTest.java | 445 ++++++++++++---------
8 files changed, 399 insertions(+), 267 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 2ef04c9583..dff24ba13a 100644
--- a/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java
+++ b/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java
@@ -107,7 +107,7 @@ class BabelQuidemTest extends QuidemTest {
case "scott-redshift":
return CalciteAssert.that()
.with(CalciteAssert.Config.SCOTT)
- .with(CalciteConnectionProperty.FUN,
"standard,postgresql,oracle")
+ .with(CalciteConnectionProperty.FUN, "standard,redshift")
.with(CalciteConnectionProperty.PARSER_FACTORY,
SqlBabelParserImpl.class.getName() + "#FACTORY")
.with(CalciteConnectionProperty.CONFORMANCE,
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/LibraryOperator.java
b/core/src/main/java/org/apache/calcite/sql/fun/LibraryOperator.java
index d16075d1b8..f4cf369b96 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/LibraryOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/LibraryOperator.java
@@ -40,4 +40,19 @@ public @interface LibraryOperator {
/** The set of libraries that this function or operator belongs to.
* Must not be null or empty. */
SqlLibrary[] libraries();
+
+ /** The set of libraries that this function should be excluded from.
+ *
+ * <p>Only needed when a library inherits functions from another library.
+ * For example, {@link SqlLibrary#REDSHIFT} inherits from
+ * {@link SqlLibrary#POSTGRESQL}, and therefore contains all of PostgreSQL's
+ * operators by default. The {@code STARTS_WITH} function is in BigQuery and
+ * PostgreSQL but not in Redshift and therefore has the annotation
+ *
+ * <blockquote>
+ * <pre>@LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL},
+ * exceptLibraries = {REDSHIFT})</pre>
+ * </blockquote>
+ */
+ SqlLibrary[] exceptLibraries() default {};
}
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 164e7cbb98..ef889928d5 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
@@ -20,9 +20,11 @@ import org.apache.calcite.config.CalciteConnectionProperty;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
+import com.google.common.collect.ImmutableSet;
import org.checkerframework.checker.nullness.qual.Nullable;
+import java.util.Arrays;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Locale;
@@ -31,6 +33,9 @@ import java.util.Set;
import static com.google.common.base.Preconditions.checkArgument;
+import static org.apache.calcite.util.Util.filter;
+import static org.apache.calcite.util.Util.first;
+
import static java.util.Objects.requireNonNull;
/**
@@ -72,7 +77,11 @@ public enum SqlLibrary {
/** A collection of operators that are in PostgreSQL but not in standard
* SQL. */
POSTGRESQL("p", "postgresql"),
- /** A collection of operators that are in Snowflake but not in standard SQL.
*/
+ /** A collection of operators that are in Redshift
+ * but not in standard SQL or PostgreSQL. */
+ REDSHIFT("r", "redshift", POSTGRESQL),
+ /** A collection of operators that are in Snowflake
+ * but not in standard SQL. */
SNOWFLAKE("f", "snowflake"),
/** A collection of operators that are in Apache Spark but not in standard
* SQL. */
@@ -81,6 +90,10 @@ public enum SqlLibrary {
/** Map from {@link Enum#name() name} and {@link #fun} to library. */
public static final Map<String, SqlLibrary> MAP;
+ /** Map of libraries to the set of libraries whose {@link SqlLibrary#parent}
+ * link points to them. */
+ private static final Map<SqlLibrary, Set<SqlLibrary>> INHERITOR_MAP;
+
/** Abbreviation for the library used in SQL reference. */
public final String abbrev;
@@ -88,9 +101,17 @@ public enum SqlLibrary {
* see {@link CalciteConnectionProperty#FUN}. */
public final String fun;
+ /** The current library will by default inherit functions from parent. */
+ public final @Nullable SqlLibrary parent;
+
SqlLibrary(String abbrev, String fun) {
+ this(abbrev, fun, null);
+ }
+
+ SqlLibrary(String abbrev, String fun, @Nullable SqlLibrary parent) {
this.abbrev = requireNonNull(abbrev, "abbrev");
this.fun = requireNonNull(fun, "fun");
+ this.parent = parent;
checkArgument(fun.equals(name().toLowerCase(Locale.ROOT).replace("_",
"")));
}
@@ -99,12 +120,21 @@ public enum SqlLibrary {
switch (this) {
case ALL:
return ImmutableList.of(BIG_QUERY, CALCITE, HIVE, MSSQL, MYSQL, ORACLE,
- POSTGRESQL, SNOWFLAKE, SPARK);
+ POSTGRESQL, REDSHIFT, SNOWFLAKE, SPARK);
default:
return ImmutableList.of();
}
}
+ /** Returns the libraries that inherit this library's functions,
+ * because their {@link #parent} field points to this.
+ *
+ * <p>For example, {@link #REDSHIFT} inherits from {@link #POSTGRESQL}.
+ * Never returns null. */
+ public Set<SqlLibrary> inheritors() {
+ return first(INHERITOR_MAP.get(this), ImmutableSet.of());
+ }
+
/** Looks up a value.
* Returns null if not found.
* You can use upper- or lower-case name. */
@@ -167,10 +197,20 @@ public enum SqlLibrary {
static {
final ImmutableMap.Builder<String, SqlLibrary> builder =
ImmutableMap.builder();
- for (SqlLibrary value : values()) {
- builder.put(value.name(), value);
- builder.put(value.fun, value);
+ final List<SqlLibrary> libraries = Arrays.asList(values());
+ for (SqlLibrary library : libraries) {
+ builder.put(library.name(), library);
+ builder.put(library.fun, library);
}
MAP = builder.build();
+ final ImmutableMap.Builder<SqlLibrary, Set<SqlLibrary>> map =
+ ImmutableMap.builder();
+ for (SqlLibrary library : libraries) {
+ map.put(library,
+ ImmutableSet.copyOf(
+ filter(libraries,
+ inheritor -> inheritor.parent == library)));
+ }
+ INHERITOR_MAP = map.build();
}
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperatorTableFactory.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperatorTableFactory.java
index 52e2e28128..7cea1c9bde 100644
---
a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperatorTableFactory.java
+++
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperatorTableFactory.java
@@ -30,6 +30,7 @@ import com.google.common.collect.ImmutableSet;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
+import java.util.Objects;
import java.util.Set;
import java.util.concurrent.ExecutionException;
@@ -141,6 +142,23 @@ public class SqlLibraryOperatorTableFactory {
if (seekLibrarySet.contains(library)) {
return true;
}
+ // Also check inheritor libraries (if any) that are not excluded
+ for (SqlLibrary inheritor : library.inheritors()) {
+ if (seekLibrarySet.contains(inheritor)
+ && !arrayContains(libraryOperator.exceptLibraries(), inheritor)) {
+ return true;
+ }
+ }
+ }
+ return false;
+ }
+
+ /** Returns whether an array contains a given element. */
+ private static <E> boolean arrayContains(E[] elements, E seek) {
+ for (E element : elements) {
+ if (Objects.equals(element, seek)) {
+ return true;
+ }
}
return false;
}
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 d95713de3e..5fc393b53d 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
@@ -68,6 +68,7 @@ import static org.apache.calcite.sql.fun.SqlLibrary.MSSQL;
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;
+import static org.apache.calcite.sql.fun.SqlLibrary.REDSHIFT;
import static org.apache.calcite.sql.fun.SqlLibrary.SNOWFLAKE;
import static org.apache.calcite.sql.fun.SqlLibrary.SPARK;
import static org.apache.calcite.util.Static.RESOURCE;
@@ -233,7 +234,7 @@ public abstract class SqlLibraryOperators {
};
/** The "DECODE(v, v1, result1, [v2, result2, ...], resultN)" function. */
- @LibraryOperator(libraries = {ORACLE, SPARK})
+ @LibraryOperator(libraries = {ORACLE, REDSHIFT, SPARK})
public static final SqlFunction DECODE =
SqlBasicFunction.create(SqlKind.DECODE, DECODE_RETURN_TYPE,
OperandTypes.VARIADIC);
@@ -264,7 +265,7 @@ public abstract class SqlLibraryOperators {
}
/** The "NVL(value, value)" function. */
- @LibraryOperator(libraries = {ORACLE, SPARK})
+ @LibraryOperator(libraries = {ORACLE, REDSHIFT, SPARK})
public static final SqlBasicFunction NVL =
SqlBasicFunction.create(SqlKind.NVL,
ReturnTypes.LEAST_RESTRICTIVE
@@ -272,7 +273,7 @@ public abstract class SqlLibraryOperators {
OperandTypes.SAME_SAME);
/** The "NVL2(value, value, value)" function. */
- @LibraryOperator(libraries = {ORACLE, SPARK})
+ @LibraryOperator(libraries = {ORACLE, REDSHIFT, SPARK})
public static final SqlBasicFunction NVL2 =
SqlBasicFunction.create(SqlKind.NVL2,
ReturnTypes.NVL2_RESTRICTIVE
@@ -300,7 +301,7 @@ public abstract class SqlLibraryOperators {
}
/** The "LPAD(original_value, return_length[, pattern])" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL, SPARK})
public static final SqlFunction LPAD =
SqlBasicFunction.create(
"LPAD",
@@ -309,7 +310,7 @@ public abstract class SqlLibraryOperators {
SqlFunctionCategory.STRING);
/** The "RPAD(original_value, return_length[, pattern])" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL, SPARK})
public static final SqlFunction RPAD =
SqlBasicFunction.create(
"RPAD",
@@ -318,7 +319,7 @@ public abstract class SqlLibraryOperators {
SqlFunctionCategory.STRING);
/** The "LTRIM(string)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL, SPARK})
public static final SqlFunction LTRIM =
SqlBasicFunction.create(SqlKind.LTRIM,
ReturnTypes.ARG0.andThen(SqlTypeTransforms.TO_NULLABLE)
@@ -327,7 +328,7 @@ public abstract class SqlLibraryOperators {
.withFunctionType(SqlFunctionCategory.STRING);
/** The "RTRIM(string)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL, SPARK})
public static final SqlFunction RTRIM =
SqlBasicFunction.create(SqlKind.RTRIM,
ReturnTypes.ARG0.andThen(SqlTypeTransforms.TO_NULLABLE)
@@ -375,8 +376,8 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING_INTEGER_OPTIONAL_INTEGER,
SqlFunctionCategory.STRING);
- /** The "ENDS_WITH(value1, value2)" function (BigQuery, PostgreSQL). */
- @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL})
+ /** The "ENDS_WITH(value1, value2)" function (BigQuery). */
+ @LibraryOperator(libraries = {BIG_QUERY})
public static final SqlBasicFunction ENDS_WITH =
SqlBasicFunction.create(SqlKind.ENDS_WITH, ReturnTypes.BOOLEAN_NULLABLE,
OperandTypes.STRING_SAME_SAME);
@@ -386,7 +387,7 @@ public abstract class SqlLibraryOperators {
public static final SqlFunction ENDSWITH = ENDS_WITH.withName("ENDSWITH");
/** The "STARTS_WITH(value1, value2)" function (BigQuery, PostgreSQL). */
- @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL})
+ @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL}, exceptLibraries =
{REDSHIFT})
public static final SqlBasicFunction STARTS_WITH =
SqlBasicFunction.create(SqlKind.STARTS_WITH,
ReturnTypes.BOOLEAN_NULLABLE,
OperandTypes.STRING_SAME_SAME);
@@ -445,14 +446,14 @@ public abstract class SqlLibraryOperators {
SqlFunctionCategory.STRING);
/** The "GREATEST(value, value)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, REDSHIFT, SPARK})
public static final SqlFunction GREATEST =
SqlBasicFunction.create(SqlKind.GREATEST,
ReturnTypes.LEAST_RESTRICTIVE.andThen(SqlTypeTransforms.TO_NULLABLE),
OperandTypes.SAME_VARIADIC);
/** The "LEAST(value, value)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, REDSHIFT, SPARK})
public static final SqlFunction LEAST =
SqlBasicFunction.create(SqlKind.LEAST,
ReturnTypes.LEAST_RESTRICTIVE.andThen(SqlTypeTransforms.TO_NULLABLE),
@@ -550,7 +551,7 @@ public abstract class SqlLibraryOperators {
/** The "REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence [,
matchType]]])"
* function. Replaces all substrings of value that match regexp with
* {@code rep} and returns modified value. */
- @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE})
+ @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE, REDSHIFT})
public static final SqlFunction REGEXP_REPLACE = new
SqlRegexpReplaceFunction();
/** The "REGEXP_SUBSTR(value, regexp[, position[, occurrence]])" function.
@@ -672,7 +673,7 @@ public abstract class SqlLibraryOperators {
/** The "ARRAY_AGG(value [ ORDER BY ...])" aggregate function,
* in BigQuery and PostgreSQL, gathers values into arrays. */
- @LibraryOperator(libraries = {POSTGRESQL, BIG_QUERY})
+ @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL}, exceptLibraries =
{REDSHIFT})
public static final SqlAggFunction ARRAY_AGG =
SqlBasicAggFunction
.create(SqlKind.ARRAY_AGG,
@@ -684,7 +685,7 @@ public abstract class SqlLibraryOperators {
/** The "ARRAY_CONCAT_AGG(value [ ORDER BY ...])" aggregate function,
* in BigQuery and PostgreSQL, concatenates array values into arrays. */
- @LibraryOperator(libraries = {POSTGRESQL, BIG_QUERY})
+ @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL}, exceptLibraries =
{REDSHIFT})
public static final SqlAggFunction ARRAY_CONCAT_AGG =
SqlBasicAggFunction
.create(SqlKind.ARRAY_CONCAT_AGG, ReturnTypes.ARG0,
@@ -698,7 +699,7 @@ public abstract class SqlLibraryOperators {
*
* <p>{@code STRING_AGG(v, sep ORDER BY x, y)} is implemented by
* rewriting to {@code LISTAGG(v, sep) WITHIN GROUP (ORDER BY x, y)}. */
- @LibraryOperator(libraries = {POSTGRESQL, BIG_QUERY})
+ @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL}, exceptLibraries =
{REDSHIFT})
public static final SqlAggFunction STRING_AGG =
SqlBasicAggFunction
.create(SqlKind.STRING_AGG, ReturnTypes.ARG0_NULLABLE,
@@ -984,12 +985,12 @@ public abstract class SqlLibraryOperators {
SqlFunctionCategory.STRING);
/** The case-insensitive variant of the LIKE operator. */
- @LibraryOperator(libraries = {POSTGRESQL})
+ @LibraryOperator(libraries = {POSTGRESQL}, exceptLibraries = {REDSHIFT})
public static final SqlSpecialOperator ILIKE =
new SqlLikeOperator("ILIKE", SqlKind.LIKE, false, false);
/** The case-insensitive variant of the NOT LIKE operator. */
- @LibraryOperator(libraries = {POSTGRESQL})
+ @LibraryOperator(libraries = {POSTGRESQL}, exceptLibraries = {REDSHIFT})
public static final SqlSpecialOperator NOT_ILIKE =
new SqlLikeOperator("NOT ILIKE", SqlKind.LIKE, true, false);
@@ -1033,7 +1034,7 @@ public abstract class SqlLibraryOperators {
*
* <p>It differs from {@link #CONCAT_FUNCTION} when processing
* null values. */
- @LibraryOperator(libraries = {MSSQL, POSTGRESQL})
+ @LibraryOperator(libraries = {MSSQL, POSTGRESQL}, exceptLibraries =
{REDSHIFT})
public static final SqlFunction CONCAT_FUNCTION_WITH_NULL =
SqlBasicFunction.create("CONCAT",
ReturnTypes.MULTIVALENT_STRING_SUM_PRECISION_NOT_NULLABLE,
@@ -1054,7 +1055,7 @@ public abstract class SqlLibraryOperators {
*
* <p>It is assigned {@link SqlKind#CONCAT2} to make it not equal to
* {@link #CONCAT_FUNCTION}. */
- @LibraryOperator(libraries = {ORACLE})
+ @LibraryOperator(libraries = {ORACLE, REDSHIFT})
public static final SqlFunction CONCAT2 =
SqlBasicFunction.create("CONCAT",
ReturnTypes.MULTIVALENT_STRING_SUM_PRECISION_NULLABLE_ALL,
@@ -1078,7 +1079,7 @@ public abstract class SqlLibraryOperators {
* <p>If all the arguments except the separator are null,
* it also returns the empty string.
* For example, {@code CONCAT_WS(',', null, null)} returns "". */
- @LibraryOperator(libraries = {MYSQL, POSTGRESQL})
+ @LibraryOperator(libraries = {MYSQL, POSTGRESQL}, exceptLibraries =
{REDSHIFT})
public static final SqlFunction CONCAT_WS =
SqlBasicFunction.create("CONCAT_WS",
ReturnTypes.MULTIVALENT_STRING_WITH_SEP_SUM_PRECISION_ARG0_NULLABLE,
@@ -1672,7 +1673,7 @@ public abstract class SqlLibraryOperators {
*
* <p>({@code TO_CHAR} is not supported in MySQL, but it is supported in
* MariaDB, a variant of MySQL covered by {@link SqlLibrary#MYSQL}.) */
- @LibraryOperator(libraries = {MYSQL, ORACLE})
+ @LibraryOperator(libraries = {MYSQL, ORACLE, REDSHIFT})
public static final SqlFunction TO_CHAR =
SqlBasicFunction.create("TO_CHAR",
ReturnTypes.VARCHAR_NULLABLE,
@@ -1681,7 +1682,7 @@ public abstract class SqlLibraryOperators {
/** The "TO_CHAR(timestamp, format)" function;
* converts {@code timestamp} to string according to the given {@code
format}. */
- @LibraryOperator(libraries = {POSTGRESQL})
+ @LibraryOperator(libraries = {POSTGRESQL}, exceptLibraries = {REDSHIFT})
public static final SqlFunction TO_CHAR_PG =
new SqlBasicFunction("TO_CHAR", SqlKind.OTHER_FUNCTION,
SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
@@ -1690,7 +1691,7 @@ public abstract class SqlLibraryOperators {
/** The "TO_DATE(string1, string2)" function; casts string1
* to a DATE using the format specified in string2. */
- @LibraryOperator(libraries = {POSTGRESQL, ORACLE})
+ @LibraryOperator(libraries = {ORACLE, POSTGRESQL})
public static final SqlFunction TO_DATE =
SqlBasicFunction.create("TO_DATE",
ReturnTypes.DATE_NULLABLE,
@@ -1699,7 +1700,7 @@ public abstract class SqlLibraryOperators {
/** The "TO_TIMESTAMP(string1, string2)" function; casts string1
* to a TIMESTAMP using the format specified in string2. */
- @LibraryOperator(libraries = {POSTGRESQL, ORACLE})
+ @LibraryOperator(libraries = {ORACLE, POSTGRESQL})
public static final SqlFunction TO_TIMESTAMP =
SqlBasicFunction.create("TO_TIMESTAMP",
ReturnTypes.TIMESTAMP_NULLABLE,
@@ -2194,14 +2195,14 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING.or(OperandTypes.BINARY),
SqlFunctionCategory.STRING);
- @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL})
+ @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL}, exceptLibraries =
{REDSHIFT})
public static final SqlFunction SHA256 =
SqlBasicFunction.create("SHA256",
ReturnTypes.VARCHAR_NULLABLE,
OperandTypes.STRING.or(OperandTypes.BINARY),
SqlFunctionCategory.STRING);
- @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL})
+ @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL}, exceptLibraries =
{REDSHIFT})
public static final SqlFunction SHA512 =
SqlBasicFunction.create("SHA512",
ReturnTypes.VARCHAR_NULLABLE,
diff --git a/core/src/test/java/org/apache/calcite/util/UtilTest.java
b/core/src/test/java/org/apache/calcite/util/UtilTest.java
index 2802e561c8..324465acfd 100644
--- a/core/src/test/java/org/apache/calcite/util/UtilTest.java
+++ b/core/src/test/java/org/apache/calcite/util/UtilTest.java
@@ -951,16 +951,16 @@ class UtilTest {
assertThat(SqlLibrary.expand(ImmutableList.of(a)),
hasToString("[ALL, BIG_QUERY, CALCITE, HIVE, MSSQL, MYSQL, ORACLE, "
- + "POSTGRESQL, SNOWFLAKE, SPARK]"));
+ + "POSTGRESQL, REDSHIFT, SNOWFLAKE, SPARK]"));
assertThat(SqlLibrary.expand(ImmutableList.of(a, c)),
hasToString("[ALL, BIG_QUERY, CALCITE, HIVE, MSSQL, MYSQL, ORACLE, "
- + "POSTGRESQL, SNOWFLAKE, SPARK]"));
+ + "POSTGRESQL, REDSHIFT, SNOWFLAKE, SPARK]"));
assertThat(SqlLibrary.expand(ImmutableList.of(c, a)),
hasToString("[CALCITE, ALL, BIG_QUERY, HIVE, MSSQL, MYSQL, ORACLE, "
- + "POSTGRESQL, SNOWFLAKE, SPARK]"));
+ + "POSTGRESQL, REDSHIFT, SNOWFLAKE, SPARK]"));
assertThat(SqlLibrary.expand(ImmutableList.of(c, o, a)),
hasToString("[CALCITE, ORACLE, ALL, BIG_QUERY, HIVE, MSSQL, MYSQL, "
- + "POSTGRESQL, SNOWFLAKE, SPARK]"));
+ + "POSTGRESQL, REDSHIFT, SNOWFLAKE, SPARK]"));
assertThat(SqlLibrary.expand(ImmutableList.of(o, c, o)),
hasToString("[ORACLE, CALCITE]"));
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 2b7777d790..1b83fd99e1 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2665,6 +2665,7 @@ The 'C' (compatibility) column contains value:
* 'q' for Microsoft SQL Server ('fun=mssql' in the connect string),
* 'o' for Oracle ('fun=oracle' in the connect string),
* 'p' for PostgreSQL ('fun=postgresql' in the connect string),
+* 'r' for Amazon RedShift ('fun=redshift' in the connect string),
* 's' for Apache Spark ('fun=spark' in the connect string).
One operator name may correspond to multiple SQL dialects, but with different
@@ -2722,10 +2723,10 @@ In the following:
| s | BIT_GET(value, position) | Returns the bit (0 or
1) value at the specified *position* of numeric *value*. The positions are
numbered from right to left, starting at zero. The *position* argument cannot
be negative
| b | CEIL(value) | Similar to standard
`CEIL(value)` except if *value* is an integer type, the return type is a double
| m s | CHAR(integer) | Returns the character
whose ASCII code is *integer* % 256, or null if *integer* < 0
-| b o p | CHR(integer) | Returns the character
whose UTF-8 code is *integer*
+| b o p r | CHR(integer) | Returns the character
whose UTF-8 code is *integer*
| b | CODE_POINTS_TO_BYTES(integers) | Converts *integers*, an
array of integers between 0 and 255 inclusive, into bytes; throws error if any
element is out of range
| b | CODE_POINTS_TO_STRING(integers) | Converts *integers*, an
array of integers between 0 and 0xD7FF or between 0xE000 and 0x10FFFF
inclusive, into string; throws error if any element is out of range
-| o | CONCAT(string, string) | Concatenates two
strings, returns null only when both string arguments are null, otherwise
treats null as empty string
+| o r | CONCAT(string, string) | Concatenates two
strings, returns null only when both string arguments are null, otherwise
treats null as empty string
| b m | CONCAT(string [, string ]*) | Concatenates one or
more strings, returns null if any of the arguments is null
| p q | CONCAT(string [, string ]*) | Concatenates one or
more strings, null is treated as empty string
| m p | CONCAT_WS(separator, str1 [, string ]*) | Concatenates one or
more strings, returns null only when separator is null, otherwise treats null
arguments as empty strings
@@ -2733,7 +2734,7 @@ In the following:
| m | COMPRESS(string) | Compresses a string
using zlib compression and returns the result as a binary string
| 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
-| p | CONVERT_TIMEZONE(tz1, tz2, datetime) | Converts the timezone
of *datetime* from *tz1* to *tz2*
+| p r | CONVERT_TIMEZONE(tz1, tz2, datetime) | Converts the timezone
of *datetime* from *tz1* to *tz2*
| * | COSH(numeric) | Returns the hyperbolic
cosine of *numeric*
| * | COTH(numeric) | Returns the hyperbolic
cotangent of *numeric*
| * | CSC(numeric) | Returns the cosecant of
*numeric* in radians
@@ -2745,8 +2746,8 @@ In the following:
| b | DATE(timestampLtz, timeZone) | Extracts the DATE from
*timestampLtz* (an instant; BigQuery's TIMESTAMP type) in *timeZone*
| b | DATE(string) | Equivalent to
`CAST(string AS DATE)`
| b | DATE(year, month, day) | Returns a DATE value
for *year*, *month*, and *day* (all of type INTEGER)
-| p q | DATEADD(timeUnit, integer, datetime) | Equivalent to
`TIMESTAMPADD(timeUnit, integer, datetime)`
-| p q | DATEDIFF(timeUnit, datetime, datetime2) | Equivalent to
`TIMESTAMPDIFF(timeUnit, datetime, datetime2)`
+| p q r | DATEADD(timeUnit, integer, datetime) | Equivalent to
`TIMESTAMPADD(timeUnit, integer, datetime)`
+| p q r | DATEDIFF(timeUnit, datetime, datetime2) | Equivalent to
`TIMESTAMPDIFF(timeUnit, datetime, datetime2)`
| q | DATEPART(timeUnit, datetime) | Equivalent to
`EXTRACT(timeUnit FROM datetime)`
| b | DATETIME(date, time) | Converts *date* and
*time* to a TIMESTAMP
| b | DATETIME(date) | Converts *date* to a
TIMESTAMP value (at midnight)
@@ -2757,15 +2758,15 @@ In the following:
| b | DATETIME_SUB(timestamp, interval) | Returns the TIMESTAMP
that occurs *interval* before *timestamp*
| b | DATETIME_TRUNC(timestamp, timeUnit) | Truncates *timestamp*
to the granularity of *timeUnit*, rounding to the beginning of the unit
| b s | DATE_FROM_UNIX_DATE(integer) | Returns the DATE that
is *integer* days after 1970-01-01
-| p | DATE_PART(timeUnit, datetime) | Equivalent to
`EXTRACT(timeUnit FROM datetime)`
+| p r | DATE_PART(timeUnit, datetime) | Equivalent to
`EXTRACT(timeUnit FROM datetime)`
| b | DATE_ADD(date, interval) | Returns the DATE value
that occurs *interval* after *date*
| b | DATE_DIFF(date, date2, timeUnit) | Returns the whole
number of *timeUnit* between *date* and *date2*
| b | DATE_SUB(date, interval) | Returns the DATE value
that occurs *interval* before *date*
| b | DATE_TRUNC(date, timeUnit) | Truncates *date* to the
granularity of *timeUnit*, rounding to the beginning of the unit
-| o s | 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 r s | 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 r | 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
| f s | ENDSWITH(string1, string2) | Returns whether
*string2* is a suffix of *string1*
-| b p | ENDS_WITH(string1, string2) | Equivalent to
`ENDSWITH(string1, string2)`
+| b | ENDS_WITH(string1, string2) | Equivalent to
`ENDSWITH(string1, string2)`
| s | EXISTS(array, func) | Returns whether a
predicate *func* holds for one or more elements in the *array*
| 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.
| 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
@@ -2780,13 +2781,13 @@ In the following:
| 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*
| s | GETBIT(value, position) | Equivalent to
`BIT_GET(value, position)`
-| b o s | GREATEST(expr [, expr ]*) | Returns the greatest of
the expressions
+| b o r s | 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 s | IFNULL(value1, value2) | Equivalent to
`NVL(value1, value2)`
| p | string1 ILIKE string2 [ ESCAPE string3 ] | Whether *string1*
matches pattern *string2*, ignoring case (similar to `LIKE`)
| p | string1 NOT ILIKE string2 [ ESCAPE string3 ] | Whether *string1* does
not match pattern *string2*, ignoring case (similar to `NOT LIKE`)
| b o | INSTR(string, substring [, from [, occurrence ] ]) | Returns the
position of *substring* in *string*, searching starting at *from* (default 1),
and until locating the nth *occurrence* (default 1) of *substring*
-| m | INSTR(string, substring) | Equivalent to
`POSITION(substring IN string)`
+| b m o | INSTR(string, substring) | Equivalent to
`POSITION(substring IN string)`
| b | IS_INF(value) | Returns whether *value*
is infinite
| b | IS_NAN(value) | Returns whether *value*
is NaN
| m | JSON_TYPE(jsonValue) | Returns a string value
indicating the type of *jsonValue*
@@ -2799,21 +2800,21 @@ In the following:
| m | JSON_REPLACE(jsonValue, path, val [, path, val ]*) | Returns a JSON
document replace a data of *jsonValue*, *path*, *val*
| m | JSON_SET(jsonValue, path, val [, path, val ]*) | Returns a JSON document
set a data of *jsonValue*, *path*, *val*
| m | JSON_STORAGE_SIZE(jsonValue) | Returns the number of
bytes used to store the binary representation of *jsonValue*
-| b o s | LEAST(expr [, expr ]* ) | Returns the least of
the expressions
-| b m p s | LEFT(string, length) | Returns the leftmost
*length* characters from the *string*
+| b o r s | LEAST(expr [, expr ]* ) | Returns the least of
the expressions
+| b m p r s | LEFT(string, length) | Returns the leftmost
*length* characters from the *string*
| f s | LEN(string) | Equivalent to
`CHAR_LENGTH(string)`
| b f s | LENGTH(string) | Equivalent to
`CHAR_LENGTH(string)`
| h s | LEVENSHTEIN(string1, string2) | Returns the Levenshtein
distance between *string1* and *string2*
| b | LOG(numeric1 [, numeric2 ]) | Returns the logarithm
of *numeric1* to base *numeric2*, or base e if *numeric2* is not present
| m s | LOG2(numeric) | Returns the base 2
logarithm of *numeric*
-| b o s | LPAD(string, length [, pattern ]) | Returns a string or
bytes value that consists of *string* prepended to *length* with *pattern*
+| b o p r s | LPAD(string, length [, pattern ]) | Returns a string or
bytes value that consists of *string* prepended to *length* with *pattern*
| b | TO_BASE32(string) | Converts the *string*
to base-32 encoded form and returns an encoded string
| b | FROM_BASE32(string) | Returns the decoded
result of a base-32 *string* as a string
| m | TO_BASE64(string) | Converts the *string*
to base-64 encoded form and returns a encoded string
| b m | FROM_BASE64(string) | Returns the decoded
result of a base-64 *string* as a string
| b | TO_HEX(binary) | Converts *binary* into
a hexadecimal varchar
| b | FROM_HEX(varchar) | Converts a
hexadecimal-encoded *varchar* into bytes
-| b o s | LTRIM(string) | Returns *string* with
all blanks removed from the start
+| b o p r s | LTRIM(string) | Returns *string* with
all blanks removed from the start
| s | MAP() | Returns an empty map
| s | MAP(key, value [, key, value]*) | Returns a map with the
given *key*/*value* pairs
| s | MAP_CONCAT(map [, map]*) | Concatenates one or
more maps. If any input argument is `NULL` the function returns `NULL`. Note
that calcite is using the LAST_WIN strategy
@@ -2824,10 +2825,10 @@ In the following:
| s | MAP_FROM_ARRAYS(array1, array2) | Returns a map created
from an *array1* and *array2*. Note that the lengths of two arrays should be
the same and calcite is using the LAST_WIN strategy
| s | MAP_FROM_ENTRIES(arrayOfRows) | Returns a map created
from an arrays of row with two fields. Note that the number of fields in a row
must be 2. Note that calcite is using the LAST_WIN strategy
| s | STR_TO_MAP(string [, stringDelimiter [, keyValueDelimiter]]) | Returns a
map after splitting the *string* into key/value pairs using delimiters. Default
delimiters are ',' for *stringDelimiter* and ':' for *keyValueDelimiter*. Note
that calcite is using the LAST_WIN strategy
-| b m p s | MD5(string) | Calculates an MD5
128-bit checksum of *string* and returns it as a hex string
+| b m p r s | MD5(string) | Calculates an MD5
128-bit checksum of *string* and returns it as a hex string
| m | MONTHNAME(date) | Returns the name, in
the connection's locale, of the month in *datetime*; for example, it returns
'二月' for both DATE '2020-02-10' and TIMESTAMP '2020-02-10 10:10:10'
-| o s | NVL(value1, value2) | Returns *value1* if
*value1* is not null, otherwise *value2*
-| o s | NVL2(value1, value2, value3) | Returns *value2* if
*value1* is not null, otherwise *value3*
+| o r s | NVL(value1, value2) | Returns *value1* if
*value1* is not null, otherwise *value2*
+| o r s | NVL2(value1, value2, value3) | Returns *value2* if
*value1* is not null, otherwise *value3*
| b | OFFSET(index) | When indexing an array,
wrapping *index* in `OFFSET` returns the value at the 0-based *index*; throws
error if *index* is out of bounds
| b | ORDINAL(index) | Similar to `OFFSET`
except *index* begins at 1
| b | PARSE_DATE(format, string) | Uses format specified
by *format* to convert *string* representation of date to a DATE value
@@ -2836,23 +2837,23 @@ In the following:
| b | PARSE_TIMESTAMP(format, string[, timeZone]) | Uses format specified
by *format* to convert *string* representation of timestamp to a TIMESTAMP WITH
LOCAL TIME ZONE value in *timeZone*
| h s | PARSE_URL(urlString, partToExtract [, keyToExtract] ) | Returns the
specified *partToExtract* from the *urlString*. Valid values for
*partToExtract* include HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and
USERINFO. *keyToExtract* specifies which query to extract
| b s | POW(numeric1, numeric2) | Returns *numeric1*
raised to the power *numeric2*
-| b c h q m o f s p | POWER(numeric1, numeric2) | Returns *numeric1* raised to
the power of *numeric2*
-| p | RANDOM() | Generates a random
double between 0 and 1 inclusive
+| b c h q m o f s p r | POWER(numeric1, numeric2) | Returns *numeric1* raised
to the power of *numeric2*
+| p r | RANDOM() | Generates a random
double between 0 and 1 inclusive
| s | REGEXP(string, regexp) | Equivalent to `string1
RLIKE string2`
| b | REGEXP_CONTAINS(string, regexp) | Returns whether
*string* is a partial match for the *regexp*
| b | REGEXP_EXTRACT(string, regexp [, position [, occurrence]]) | Returns the
substring in *string* that matches the *regexp*, starting search at *position*
(default 1), and until locating the nth *occurrence* (default 1). Returns NULL
if there is no match
| b | REGEXP_EXTRACT_ALL(string, regexp) | Returns an array of all
substrings in *string* that matches the *regexp*. Returns an empty array if
there is no match
| b | REGEXP_INSTR(string, regexp [, position [, occurrence [,
occurrence_position]]]) | Returns the lowest 1-based position of the substring
in *string* that matches the *regexp*, starting search at *position* (default
1), and until locating the nth *occurrence* (default 1). Setting
occurrence_position (default 0) to 1 returns the end position of substring + 1.
Returns 0 if there is no match
-| m o p s | REGEXP_LIKE(string, regexp [, flags]) | Equivalent to
`string1 RLIKE string2` with an optional parameter for search flags. Supported
flags are: <ul><li>i: case-insensitive matching</li><li>c: case-sensitive
matching</li><li>n: newline-sensitive matching</li><li>s: non-newline-sensitive
matching</li><li>m: multi-line</li></ul>
-| b m o | REGEXP_REPLACE(string, regexp, rep [, pos [, occurrence [,
matchType]]]) | Replaces all substrings of *string* that match *regexp* with
*rep* at the starting *pos* in expr (if omitted, the default is 1),
*occurrence* specifies which occurrence of a match to search for (if omitted,
the default is 1), *matchType* specifies how to perform matching
+| m o p r s | REGEXP_LIKE(string, regexp [, flags]) | Equivalent to `string1
RLIKE string2` with an optional parameter for search flags. Supported flags
are: <ul><li>i: case-insensitive matching</li><li>c: case-sensitive
matching</li><li>n: newline-sensitive matching</li><li>s: non-newline-sensitive
matching</li><li>m: multi-line</li></ul>
+| b m o r | REGEXP_REPLACE(string, regexp, rep [, pos [, occurrence [,
matchType]]]) | Replaces all substrings of *string* that match *regexp* with
*rep* at the starting *pos* in expr (if omitted, the default is 1),
*occurrence* specifies which occurrence of a match to search for (if omitted,
the default is 1), *matchType* specifies how to perform matching
| b | REGEXP_SUBSTR(string, regexp [, position [, occurrence]]) | Synonym for
REGEXP_EXTRACT
-| b m p s | REPEAT(string, integer) | Returns a string
consisting of *string* repeated of *integer* times; returns an empty string if
*integer* is less than 1
+| b m p r s | REPEAT(string, integer) | Returns a string
consisting of *string* repeated of *integer* times; returns an empty string if
*integer* is less than 1
| b m | REVERSE(string) | Returns *string* with
the order of the characters reversed
-| b m p s | RIGHT(string, length) | Returns the rightmost
*length* characters from the *string*
+| b m p r s | RIGHT(string, length) | Returns the rightmost
*length* characters from the *string*
| h m s | string1 RLIKE string2 | Whether *string1*
matches regex pattern *string2* (similar to `LIKE`, but uses Java regex)
| h m s | string1 NOT RLIKE string2 | Whether *string1* does
not match regex pattern *string2* (similar to `NOT LIKE`, but uses Java regex)
-| b o s | RPAD(string, length[, pattern ]) | Returns a string or
bytes value that consists of *string* appended to *length* with *pattern*
-| b o s | RTRIM(string) | Returns *string* with
all blanks removed from the end
+| b o p r s | RPAD(string, length[, pattern ]) | Returns a string or
bytes value that consists of *string* appended to *length* with *pattern*
+| b o p r s | RTRIM(string) | Returns *string* with
all blanks removed from the end
| b | SAFE_ADD(numeric1, numeric2) | Returns *numeric1* +
*numeric2*, or NULL on overflow. Arguments are implicitly cast to one of the
types BIGINT, DOUBLE, or DECIMAL
| b | SAFE_CAST(value AS type) | Converts *value* to
*type*, returning NULL if conversion fails
| b | SAFE_DIVIDE(numeric1, numeric2) | Returns *numeric1* /
*numeric2*, or NULL on overflow or if *numeric2* is zero. Arguments implicitly
are cast to one of the types BIGINT, DOUBLE, or DECIMAL
@@ -2863,19 +2864,19 @@ In the following:
| b | SAFE_SUBTRACT(numeric1, numeric2) | Returns *numeric1* -
*numeric2*, or NULL on overflow. Arguments are implicitly cast to one of the
types BIGINT, DOUBLE, or DECIMAL
| * | SEC(numeric) | Returns the secant of
*numeric* in radians
| * | SECH(numeric) | Returns the hyperbolic
secant of *numeric*
-| b m p s | SHA1(string) | Calculates a SHA-1 hash
value of *string* and returns it as a hex string
+| b m p r s | SHA1(string) | Calculates a SHA-1 hash
value of *string* and returns it as a hex string
| b p | SHA256(string) | Calculates a SHA-256
hash value of *string* and returns it as a hex string
| b p | SHA512(string) | Calculates a SHA-512
hash value of *string* and returns it as a hex string
| * | SINH(numeric) | Returns the hyperbolic
sine of *numeric*
-| b m o p | SOUNDEX(string) | Returns the phonetic
representation of *string*; throws if *string* is encoded with multi-byte
encoding such as UTF-8
+| b m o p r | SOUNDEX(string) | Returns the phonetic
representation of *string*; throws if *string* is encoded with multi-byte
encoding such as UTF-8
| s | SOUNDEX(string) | Returns the phonetic
representation of *string*; return original *string* if *string* is encoded
with multi-byte encoding such as UTF-8
| m s | SPACE(integer) | Returns a string of
*integer* spaces; returns an empty string if *integer* is less than 1
| b | SPLIT(string [, delimiter ]) | Returns the string
array of *string* split at *delimiter* (if omitted, default is comma). If the
*string* is empty it returns an empty array, otherwise, if the *delimiter* is
empty, it returns an array containing the original *string*.
| f s | STARTSWITH(string1, string2) | Returns whether
*string2* is a prefix of *string1*
| b p | STARTS_WITH(string1, string2) | Equivalent to
`STARTSWITH(string1, string2)`
| 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
-| b p | STRPOS(string, substring) | Equivalent to
`POSITION(substring IN string)`
-| b m o p | 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
+| b r p | STRPOS(string, substring) | Equivalent to
`POSITION(substring IN string)`
+| b m o p r | 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
| * | TANH(numeric) | Returns the hyperbolic
tangent of *numeric*
| b | TIME(hour, minute, second) | Returns a TIME value
*hour*, *minute*, *second* (all of type INTEGER)
| b | TIME(timestamp) | Extracts the TIME from
*timestamp* (a local time; BigQuery's DATETIME type)
@@ -2898,11 +2899,11 @@ In the following:
| b | TIME_DIFF(time, time2, timeUnit) | Returns the whole
number of *timeUnit* between *time* and *time2*
| b | TIME_SUB(time, interval) | Returns the TIME value
that is *interval* before *time*
| b | TIME_TRUNC(time, timeUnit) | Truncates *time* to the
granularity of *timeUnit*, rounding to the beginning of the unit
-| m o p | TO_CHAR(timestamp, format) | Converts *timestamp* to
a string using the format *format*
+| m o p r | TO_CHAR(timestamp, format) | Converts *timestamp* to
a string using the format *format*
| b | TO_CODE_POINTS(string) | Converts *string* to an
array of integers that represent code points or extended ASCII character values
-| 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*
-| b o p s | 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 p r | TO_DATE(string, format) | Converts *string* to a
date using the format *format*
+| o p r | TO_TIMESTAMP(string, format) | Converts *string* to a
timestamp using the format *format*
+| b o p r s | 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
| b | TRUNC(numeric1 [, integer2 ]) | Truncates *numeric1* to
optionally *integer2* (if not specified 0) places right to the decimal point
| q | TRY_CAST(value AS type) | Converts *value* to
*type*, returning NULL if conversion fails
| b s | UNIX_MICROS(timestamp) | Returns the number of
microseconds since 1970-01-01 00:00:00
@@ -2948,8 +2949,8 @@ Dialect-specific aggregate functions.
| c | AGGREGATE(m) | Computes measure *m* in
the context of the current GROUP BY key
| b p | ARRAY_AGG( [ ALL | DISTINCT ] value [ RESPECT NULLS | IGNORE
NULLS ] [ ORDER BY orderItem [, orderItem ]* ] ) | Gathers values into arrays
| b p | ARRAY_CONCAT_AGG( [ ALL | DISTINCT ] value [ ORDER BY orderItem
[, orderItem ]* ] ) | Concatenates arrays into arrays
-| p s | BOOL_AND(condition) | Synonym for `EVERY`
-| p s | BOOL_OR(condition) | Synonym for `SOME`
+| p r s | BOOL_AND(condition) | Synonym for `EVERY`
+| p r s | BOOL_OR(condition) | Synonym for `SOME`
| f | BOOLAND_AGG(condition) | Synonym for `EVERY`
| f | BOOLOR_AGG(condition) | Synonym for `SOME`
| b | COUNTIF(condition) | Returns the number of
rows for which *condition* is TRUE; equivalent to `COUNT(*) FILTER (WHERE
condition)`
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 d018b5de76..094e8eda09 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -2117,8 +2117,9 @@ public class SqlOperatorTest {
f.checkScalar("chr(0)", String.valueOf('\u0000'), "CHAR(1) NOT NULL");
f.checkNull("chr(null)");
};
- f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE,
SqlLibrary.POSTGRESQL),
- consumer);
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testCodePointsToBytes() {
@@ -2368,6 +2369,7 @@ public class SqlOperatorTest {
checkConcatFuncWithNull(f.withLibrary(SqlLibrary.POSTGRESQL));
checkConcatFuncWithNull(f.withLibrary(SqlLibrary.MSSQL));
checkConcat2Func(f.withLibrary(SqlLibrary.ORACLE));
+ checkConcat2Func(f.withLibrary(SqlLibrary.REDSHIFT));
}
private static void checkConcatFunc(SqlOperatorFixture f) {
@@ -3802,6 +3804,7 @@ public class SqlOperatorTest {
checkRlikeFunc(f, SqlLibrary.POSTGRESQL, SqlLibraryOperators.REGEXP_LIKE);
checkRlikeFunc(f, SqlLibrary.MYSQL, SqlLibraryOperators.REGEXP_LIKE);
checkRlikeFunc(f, SqlLibrary.ORACLE, SqlLibraryOperators.REGEXP_LIKE);
+ checkRlikeFunc(f, SqlLibrary.REDSHIFT, SqlLibraryOperators.REGEXP_LIKE);
}
void checkRlikeFunc(SqlOperatorFixture f0, SqlLibrary library, SqlOperator
operator) {
@@ -4004,9 +4007,10 @@ public class SqlOperatorTest {
f1.checkNull("REGEXP_LIKE('atest\nstr', NULL, 'sn')");
f1.checkNull("REGEXP_LIKE('atest\nstr', 'test.str', NULL)");
};
- f.forEachLibrary(
- list(SqlLibrary.MYSQL, SqlLibrary.SPARK,
- SqlLibrary.POSTGRESQL, SqlLibrary.ORACLE), consumer);
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.MYSQL, SqlLibrary.SPARK, SqlLibrary.POSTGRESQL,
+ SqlLibrary.ORACLE);
+ f.forEachLibrary(libraries, consumer);
}
/** Test case for
@@ -4322,8 +4326,8 @@ public class SqlOperatorTest {
f.checkNull("translate('aabbcc', 'ab', cast(null as varchar(2)))");
};
final List<SqlLibrary> libraries =
- ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE,
- SqlLibrary.POSTGRESQL, SqlLibrary.SPARK);
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL,
+ SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
f0.forEachLibrary(libraries, consumer);
}
@@ -4719,83 +4723,88 @@ public class SqlOperatorTest {
}
@Test void testToChar() {
- final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.MYSQL);
- f.setFor(SqlLibraryOperators.TO_CHAR);
- f.checkString("to_char(timestamp '2022-06-03 12:15:48.678', 'YYYY-MM-DD
HH24:MI:SS.MS TZ')",
- "2022-06-03 12:15:48.678",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 12:15:48.678', 'Day')",
- "Friday",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '0001-01-01 00:00:00.000', 'Day')",
- "Monday",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 12:15:48.678', 'DY')",
- "FRI",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '0001-01-01 00:00:00.000', 'DY')",
- "MON",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 12:15:48.678', 'CC')",
- "21",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'HH12')",
- "01",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'HH24')",
- "13",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'MI')",
- "15",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'MS')",
- "678",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'Q')",
- "2",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'IW')",
- "23",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'YYYY')",
- "2022",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'YY')",
- "22",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'Month')",
- "June",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'Mon')",
- "Jun",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'MM')",
- "06",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'CC')",
- "21",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'DDD')",
- "154",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'DD')",
- "03",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'D')",
- "6",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'W')",
- "1",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'WW')",
- "23",
- "VARCHAR NOT NULL");
- f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'gggggg')",
- "gggggg",
- "VARCHAR NOT NULL");
- f.checkNull("to_char(timestamp '2022-06-03 12:15:48.678', NULL)");
- f.checkNull("to_char(cast(NULL as timestamp), NULL)");
- f.checkNull("to_char(cast(NULL as timestamp), 'Day')");
+ final SqlOperatorFixture f0 =
fixture().setFor(SqlLibraryOperators.TO_CHAR);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkString(
+ "to_char(timestamp '2022-06-03 12:15:48.678', 'YYYY-MM-DD
HH24:MI:SS.MS TZ')",
+ "2022-06-03 12:15:48.678",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 12:15:48.678', 'Day')",
+ "Friday",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '0001-01-01 00:00:00.000', 'Day')",
+ "Monday",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 12:15:48.678', 'DY')",
+ "FRI",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '0001-01-01 00:00:00.000', 'DY')",
+ "MON",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 12:15:48.678', 'CC')",
+ "21",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'HH12')",
+ "01",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'HH24')",
+ "13",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'MI')",
+ "15",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'MS')",
+ "678",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'Q')",
+ "2",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'IW')",
+ "23",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'YYYY')",
+ "2022",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'YY')",
+ "22",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'Month')",
+ "June",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'Mon')",
+ "Jun",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'MM')",
+ "06",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'CC')",
+ "21",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'DDD')",
+ "154",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'DD')",
+ "03",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'D')",
+ "6",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'W')",
+ "1",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'WW')",
+ "23",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'gggggg')",
+ "gggggg",
+ "VARCHAR NOT NULL");
+ f.checkNull("to_char(timestamp '2022-06-03 12:15:48.678', NULL)");
+ f.checkNull("to_char(cast(NULL as timestamp), NULL)");
+ f.checkNull("to_char(cast(NULL as timestamp), 'Day')");
+ };
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.MYSQL, SqlLibrary.ORACLE, SqlLibrary.REDSHIFT);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testToCharPg() {
@@ -5066,69 +5075,77 @@ public class SqlOperatorTest {
}
@Test void testToDate() {
- final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL);
- f.setFor(SqlLibraryOperators.TO_DATE);
+ final SqlOperatorFixture f0 =
fixture().setFor(SqlLibraryOperators.TO_DATE);
- f.checkString("to_date('2022-06-03', 'YYYY-MM-DD')",
- "2022-06-03",
- "DATE NOT NULL");
- f.checkString("to_date('0001-01-01', 'YYYY-MM-DD')",
- "0001-01-01",
- "DATE NOT NULL");
- f.checkString("to_date('Jun 03, 2022', 'Mon DD, YYYY')",
- "2022-06-03",
- "DATE NOT NULL");
- f.checkString("to_date('2022-June-03', 'YYYY-Month-DD')",
- "2022-06-03",
- "DATE NOT NULL");
- f.checkString("to_date('2022-Jun-03', 'YYYY-Mon-DD')",
- "2022-06-03",
- "DATE NOT NULL");
- f.checkString("to_date('2022-154', 'YYYY-DDD')",
- "2022-06-03",
- "DATE NOT NULL");
- f.checkFails("to_date('ABCD', 'YYYY-MM-DD')",
- "java.sql.SQLException: Invalid format: 'YYYY-MM-DD' for datetime
string: 'ABCD'.",
- true);
- f.checkFails("to_date('2022-06-03', 'Invalid')",
- "Illegal pattern character 'I'",
- true);
- f.checkNull("to_date(NULL, 'YYYY-MM-DD')");
- f.checkNull("to_date('2022-06-03', NULL)");
- f.checkNull("to_date(NULL, NULL)");
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkString("to_date('2022-06-03', 'YYYY-MM-DD')",
+ "2022-06-03",
+ "DATE NOT NULL");
+ f.checkString("to_date('0001-01-01', 'YYYY-MM-DD')",
+ "0001-01-01",
+ "DATE NOT NULL");
+ f.checkString("to_date('Jun 03, 2022', 'Mon DD, YYYY')",
+ "2022-06-03",
+ "DATE NOT NULL");
+ f.checkString("to_date('2022-June-03', 'YYYY-Month-DD')",
+ "2022-06-03",
+ "DATE NOT NULL");
+ f.checkString("to_date('2022-Jun-03', 'YYYY-Mon-DD')",
+ "2022-06-03",
+ "DATE NOT NULL");
+ f.checkString("to_date('2022-154', 'YYYY-DDD')",
+ "2022-06-03",
+ "DATE NOT NULL");
+ f.checkFails("to_date('ABCD', 'YYYY-MM-DD')",
+ "java.sql.SQLException: Invalid format: 'YYYY-MM-DD' for datetime
string: 'ABCD'.",
+ true);
+ f.checkFails("to_date('2022-06-03', 'Invalid')",
+ "Illegal pattern character 'I'",
+ true);
+ f.checkNull("to_date(NULL, 'YYYY-MM-DD')");
+ f.checkNull("to_date('2022-06-03', NULL)");
+ f.checkNull("to_date(NULL, NULL)");
+ };
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testToTimestamp() {
- final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL);
- f.setFor(SqlLibraryOperators.TO_TIMESTAMP);
+ final SqlOperatorFixture f0 =
fixture().setFor(SqlLibraryOperators.TO_TIMESTAMP);
- f.checkString("to_timestamp('2022-06-03 18:34:56', 'YYYY-MM-DD
HH24:MI:SS')",
- "2022-06-03 18:34:56",
- "TIMESTAMP(0) NOT NULL");
- f.checkString("to_timestamp('0001-01-01 18:43:56', 'YYYY-MM-DD
HH24:MI:SS')",
- "0001-01-01 18:43:56",
- "TIMESTAMP(0) NOT NULL");
- f.checkString("to_timestamp('18:34:56 Jun 03, 2022', 'HH24:MI:SS Mon DD,
YYYY')",
- "2022-06-03 18:34:56",
- "TIMESTAMP(0) NOT NULL");
- f.checkString("to_timestamp('18:34:56 2022-June-03', 'HH24:MI:SS
YYYY-Month-DD')",
- "2022-06-03 18:34:56",
- "TIMESTAMP(0) NOT NULL");
- f.checkString("to_timestamp('18:34:56 2022-Jun-03', 'HH24:MI:SS
YYYY-Mon-DD')",
- "2022-06-03 18:34:56",
- "TIMESTAMP(0) NOT NULL");
- f.checkString("to_timestamp('18:34:56 2022-154', 'HH24:MI:SS YYYY-DDD')",
- "2022-06-03 18:34:56",
- "TIMESTAMP(0) NOT NULL");
- f.checkFails("to_timestamp('ABCD', 'YYYY-MM-DD HH24:MI:SS')",
- "java.sql.SQLException: Invalid format: 'YYYY-MM-DD HH24:MI:SS' for
datetime string: 'ABCD'.",
- true);
- f.checkFails("to_timestamp('2022-06-03 18:34:56', 'Invalid')",
- "Illegal pattern character 'I'",
- true);
- f.checkNull("to_timestamp(NULL, 'YYYY-MM-DD HH24:MI:SS')");
- f.checkNull("to_timestamp('2022-06-03 18:34:56', NULL)");
- f.checkNull("to_timestamp(NULL, NULL)");
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkString("to_timestamp('2022-06-03 18:34:56', 'YYYY-MM-DD
HH24:MI:SS')",
+ "2022-06-03 18:34:56",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkString("to_timestamp('0001-01-01 18:43:56', 'YYYY-MM-DD
HH24:MI:SS')",
+ "0001-01-01 18:43:56",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkString("to_timestamp('18:34:56 Jun 03, 2022', 'HH24:MI:SS Mon DD,
YYYY')",
+ "2022-06-03 18:34:56",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkString("to_timestamp('18:34:56 2022-June-03', 'HH24:MI:SS
YYYY-Month-DD')",
+ "2022-06-03 18:34:56",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkString("to_timestamp('18:34:56 2022-Jun-03', 'HH24:MI:SS
YYYY-Mon-DD')",
+ "2022-06-03 18:34:56",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkString("to_timestamp('18:34:56 2022-154', 'HH24:MI:SS YYYY-DDD')",
+ "2022-06-03 18:34:56",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkFails("to_timestamp('ABCD', 'YYYY-MM-DD HH24:MI:SS')",
+ "java.sql.SQLException: Invalid format: 'YYYY-MM-DD HH24:MI:SS' for
datetime string: 'ABCD'.",
+ true);
+ f.checkFails("to_timestamp('2022-06-03 18:34:56', 'Invalid')",
+ "Illegal pattern character 'I'",
+ true);
+ f.checkNull("to_timestamp(NULL, 'YYYY-MM-DD HH24:MI:SS')");
+ f.checkNull("to_timestamp('2022-06-03 18:34:56', NULL)");
+ f.checkNull("to_timestamp(NULL, NULL)");
+ };
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testFromBase64() {
@@ -5199,7 +5216,7 @@ public class SqlOperatorTest {
false);
final List<SqlLibrary> libraries =
ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL,
- SqlLibrary.POSTGRESQL, SqlLibrary.SPARK);
+ SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
final Consumer<SqlOperatorFixture> consumer = f -> {
f.checkString("md5(x'')",
"d41d8cd98f00b204e9800998ecf8427e",
@@ -5224,7 +5241,7 @@ public class SqlOperatorTest {
false);
final List<SqlLibrary> libraries =
ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL,
- SqlLibrary.POSTGRESQL, SqlLibrary.SPARK);
+ SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
final Consumer<SqlOperatorFixture> consumer = f -> {
f.checkString("sha1(x'')",
"da39a3ee5e6b4b0d3255bfef95601890afd80709",
@@ -5344,9 +5361,10 @@ public class SqlOperatorTest {
f.checkNull("REPEAT('abc', cast(null as integer))");
f.checkNull("REPEAT(cast(null as varchar(1)), cast(null as integer))");
};
- f0.forEachLibrary(
+ final List<SqlLibrary> libraries =
list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL,
- SqlLibrary.POSTGRESQL, SqlLibrary.SPARK), consumer);
+ SqlLibrary.POSTGRESQL, SqlLibrary.SPARK);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testSpaceFunc() {
@@ -5380,8 +5398,8 @@ public class SqlOperatorTest {
"No match found for function signature SOUNDEX\\(<CHARACTER>\\)",
false);
final List<SqlLibrary> libraries =
- ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL,
- SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL);
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL,
+ SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT);
final Consumer<SqlOperatorFixture> consumer = f -> {
f.checkString("SOUNDEX('TECH ON THE NET')", "T253", "VARCHAR(4) NOT
NULL");
f.checkString("SOUNDEX('Miller')", "M460", "VARCHAR(4) NOT NULL");
@@ -5422,19 +5440,23 @@ public class SqlOperatorTest {
}
@Test void testDifferenceFunc() {
- final SqlOperatorFixture f = fixture()
- .setFor(SqlLibraryOperators.DIFFERENCE)
- .withLibrary(SqlLibrary.POSTGRESQL);
- f.checkScalarExact("DIFFERENCE('Miller', 'miller')", 4);
- f.checkScalarExact("DIFFERENCE('Miller', 'myller')", 4);
- f.checkScalarExact("DIFFERENCE('muller', 'miller')", 4);
- f.checkScalarExact("DIFFERENCE('muller', 'miller')", 4);
- f.checkScalarExact("DIFFERENCE('muller', 'milk')", 2);
- f.checkScalarExact("DIFFERENCE('muller', 'mile')", 2);
- f.checkScalarExact("DIFFERENCE('muller', 'm')", 1);
- f.checkScalarExact("DIFFERENCE('muller', 'lee')", 0);
- f.checkNull("DIFFERENCE('muller', cast(null as varchar(1)))");
- f.checkNull("DIFFERENCE(cast(null as varchar(1)), 'muller')");
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.DIFFERENCE);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkScalarExact("DIFFERENCE('Miller', 'miller')", 4);
+ f.checkScalarExact("DIFFERENCE('Miller', 'myller')", 4);
+ f.checkScalarExact("DIFFERENCE('muller', 'miller')", 4);
+ f.checkScalarExact("DIFFERENCE('muller', 'miller')", 4);
+ f.checkScalarExact("DIFFERENCE('muller', 'milk')", 2);
+ f.checkScalarExact("DIFFERENCE('muller', 'mile')", 2);
+ f.checkScalarExact("DIFFERENCE('muller', 'm')", 1);
+ f.checkScalarExact("DIFFERENCE('muller', 'lee')", 0);
+ f.checkNull("DIFFERENCE('muller', cast(null as varchar(1)))");
+ f.checkNull("DIFFERENCE(cast(null as varchar(1)), 'muller')");
+ };
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testReverseFunc() {
@@ -5555,9 +5577,10 @@ public class SqlOperatorTest {
f.checkNull("left(cast(null as binary(1)), -2)");
f.checkNull("left(x'ABCdef', cast(null as Integer))");
};
- f0.forEachLibrary(
+ final List<SqlLibrary> libraries =
list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL, SqlLibrary.POSTGRESQL,
- SqlLibrary.SPARK), consumer);
+ SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testRightFunc() {
@@ -5581,9 +5604,10 @@ public class SqlOperatorTest {
f.checkNull("right(x'ABCdef', cast(null as Integer))");
};
- f0.forEachLibrary(
+ final List<SqlLibrary> libraries =
list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL, SqlLibrary.POSTGRESQL,
- SqlLibrary.SPARK), consumer);
+ SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testRegexpContainsFunc() {
@@ -5700,7 +5724,10 @@ public class SqlOperatorTest {
f.checkQuery("select regexp_replace('a b c', 'b', 'X', 1, 3)");
f.checkQuery("select regexp_replace('a b c', 'b', 'X', 1, 3, 'i')");
};
- f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL,
SqlLibrary.ORACLE), consumer);
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL, SqlLibrary.ORACLE,
+ SqlLibrary.REDSHIFT);
+ f0.forEachLibrary(libraries, consumer);
// Tests for double-backslash indexed capturing groups for regexp_replace
in BQ
final SqlOperatorFixture f1 =
@@ -7019,7 +7046,9 @@ public class SqlOperatorTest {
fixture.checkScalarApprox("random()", "DOUBLE NOT NULL", isWithin(0.5,
0.5));
}
};
- f.forEachLibrary(list(SqlLibrary.POSTGRESQL), consumer);
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT);
+ f.forEachLibrary(libraries, consumer);
}
@Test void testRandIntegerSeedFunc() {
@@ -9876,7 +9905,10 @@ public class SqlOperatorTest {
f.checkFails("lpad(x'aa', 3, x'')",
"Third argument \\(pad pattern\\) for LPAD/RPAD must not be empty",
true);
};
- f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE,
SqlLibrary.SPARK), consumer);
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL,
+ SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testRpadFunction() {
@@ -9905,7 +9937,10 @@ public class SqlOperatorTest {
f.checkFails("rpad(x'aa', 3, x'')",
"Third argument \\(pad pattern\\) for LPAD/RPAD must not be empty",
true);
};
- f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE,
SqlLibrary.SPARK), consumer);
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL,
+ SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testContainsSubstrFunc() {
@@ -9973,24 +10008,28 @@ public class SqlOperatorTest {
f0.checkFails("^strpos('abc', 'a')^",
"No match found for function signature STRPOS\\(<CHARACTER>,
<CHARACTER>\\)",
false);
- final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
- f.checkScalar("STRPOS('abc', 'a')", "1", "INTEGER NOT NULL");
- f.checkScalar("STRPOS('abcabc', 'bc')", "2", "INTEGER NOT NULL");
- f.checkScalar("STRPOS('abcabc', 'd')", "0", "INTEGER NOT NULL");
- f.checkScalar("STRPOS('abc', '')", "1", "INTEGER NOT NULL");
- f.checkScalar("STRPOS('', 'a')", "0", "INTEGER NOT NULL");
- f.checkNull("STRPOS(null, 'a')");
- f.checkNull("STRPOS('a', null)");
-
- // test for BINARY
- f.checkScalar("STRPOS(x'2212', x'12')", "2", "INTEGER NOT NULL");
- f.checkScalar("STRPOS(x'2122', x'12')", "0", "INTEGER NOT NULL");
- f.checkScalar("STRPOS(x'1222', x'12')", "1", "INTEGER NOT NULL");
- f.checkScalar("STRPOS(x'1111', x'22')", "0", "INTEGER NOT NULL");
- f.checkScalar("STRPOS(x'2122', x'')", "1", "INTEGER NOT NULL");
- f.checkScalar("STRPOS(x'', x'12')", "0", "INTEGER NOT NULL");
- f.checkNull("STRPOS(null, x'')");
- f.checkNull("STRPOS(x'', null)");
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkScalar("STRPOS('abc', 'a')", "1", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS('abcabc', 'bc')", "2", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS('abcabc', 'd')", "0", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS('abc', '')", "1", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS('', 'a')", "0", "INTEGER NOT NULL");
+ f.checkNull("STRPOS(null, 'a')");
+ f.checkNull("STRPOS('a', null)");
+
+ // test for BINARY
+ f.checkScalar("STRPOS(x'2212', x'12')", "2", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS(x'2122', x'12')", "0", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS(x'1222', x'12')", "1", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS(x'1111', x'22')", "0", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS(x'2122', x'')", "1", "INTEGER NOT NULL");
+ f.checkScalar("STRPOS(x'', x'12')", "0", "INTEGER NOT NULL");
+ f.checkNull("STRPOS(null, x'')");
+ f.checkNull("STRPOS(x'', null)");
+ };
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testInstrFunction() {
@@ -10021,7 +10060,9 @@ public class SqlOperatorTest {
f.checkNull("INSTR(null, x'', 1, 1)");
f.checkNull("INSTR(x'', null, 1, 1)");
};
- f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE), consumer);
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL, SqlLibrary.ORACLE);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testSnowflakeStartsWithFunc() {
@@ -10625,7 +10666,10 @@ public class SqlOperatorTest {
f.checkString("rtrim(' aAa ')", " aAa", "VARCHAR(6) NOT NULL");
f.checkNull("rtrim(CAST(NULL AS VARCHAR(6)))");
};
- f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE,
SqlLibrary.SPARK), consumer);
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL,
+ SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testLtrimFunc() {
@@ -10638,7 +10682,10 @@ public class SqlOperatorTest {
f.checkString("ltrim(' aAa ')", "aAa ", "VARCHAR(6) NOT NULL");
f.checkNull("ltrim(CAST(NULL AS VARCHAR(6)))");
};
- f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE,
SqlLibrary.SPARK), consumer);
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL,
+ SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testGreatestFunc() {
@@ -10660,7 +10707,10 @@ public class SqlOperatorTest {
f12.checkString("greatest('show', 'on', 'earth')", "show",
"VARCHAR(5) NOT NULL");
};
- f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE,
SqlLibrary.SPARK), consumer);
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.REDSHIFT,
+ SqlLibrary.SPARK);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testLeastFunc() {
@@ -10682,7 +10732,10 @@ public class SqlOperatorTest {
f12.checkString("least('show', 'on', 'earth')", "earth",
"VARCHAR(5) NOT NULL");
};
- f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE,
SqlLibrary.SPARK), consumer);
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.REDSHIFT,
+ SqlLibrary.SPARK);
+ f0.forEachLibrary(libraries, consumer);
}
@Test void testIfNullFunc() {
@@ -10744,8 +10797,9 @@ public class SqlOperatorTest {
f12.checkFails("^NVL2(2.0, 1, true)^", "Parameters must be of the same
type", false);
f12.checkFails("^NVL2(NULL, 1, true)^", "Parameters must be of the same
type", false);
};
- f.forEachLibrary(list(SqlLibrary.ORACLE, SqlLibrary.SPARK), consumer);
-
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.ORACLE, SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
+ f.forEachLibrary(libraries, consumer);
}
/** Tests the {@code NVL} and {@code IFNULL} operators. */
@@ -10771,6 +10825,7 @@ public class SqlOperatorTest {
@Test void testDecodeFunc() {
checkDecodeFunc(fixture().withLibrary(SqlLibrary.ORACLE));
+ checkDecodeFunc(fixture().withLibrary(SqlLibrary.REDSHIFT));
checkDecodeFunc(fixture().withLibrary(SqlLibrary.SPARK));
}
@@ -14502,6 +14557,7 @@ public class SqlOperatorTest {
"No match found for function signature BOOLAND_AGG\\(<BOOLEAN>\\)",
false);
checkBoolAndFunc(f.withLibrary(SqlLibrary.POSTGRESQL));
+ checkBoolAndFunc(f.withLibrary(SqlLibrary.REDSHIFT));
checkBoolAndFunc(f.withLibrary(SqlLibrary.SPARK));
checkBoolAndAggFunc(f.withLibrary(SqlLibrary.SNOWFLAKE));
}
@@ -14579,6 +14635,7 @@ public class SqlOperatorTest {
"No match found for function signature BOOLOR_AGG\\(<BOOLEAN>\\)",
false);
checkBoolOrFunc(f.withLibrary(SqlLibrary.POSTGRESQL));
+ checkBoolOrFunc(f.withLibrary(SqlLibrary.REDSHIFT));
checkBoolOrFunc(f.withLibrary(SqlLibrary.SPARK));
checkBoolOrAggFunc(f.withLibrary(SqlLibrary.SNOWFLAKE));
}
