This is an automated email from the ASF dual-hosted git repository.
guohongyu 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 b5091fe94d [CALCITE-6241] Enable a few existing functions to Spark
library
b5091fe94d is described below
commit b5091fe94d38ee952bde116ba6e04f00deb7308a
Author: YiwenWu <[email protected]>
AuthorDate: Sat Feb 3 18:03:02 2024 +0800
[CALCITE-6241] Enable a few existing functions to Spark library
Functions List:
DECODE, NVL, IFNULL, LEN, LENGTH, LPAD, RPAD, LTRIM, RTRIM, ENDSWITH,
STARTSWITH, GREATEST, LEAST,
TRANSLATE, BOOL_AND, BOOL_OR, DATE_FROM_UNIX_DATE, UNIX_DATE, LEFT, REPEAT,
RIGHT, SPACE
TIMESTAMP_SECONDS, TIMESTAMP_MILLIS, TIMESTAMP_MICROS, UNIX_SECONDS,
UNIX_MILLIS, UNIX_MICROS, MD5, SHA1, POW
---
.../calcite/sql/fun/SqlLibraryOperators.java | 62 ++++----
site/_docs/reference.md | 62 ++++----
.../org/apache/calcite/test/SqlOperatorTest.java | 159 ++++++++++++---------
3 files changed, 151 insertions(+), 132 deletions(-)
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 d397f008b2..e8dac9d7a9 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
@@ -232,7 +232,7 @@ public abstract class SqlLibraryOperators {
};
/** The "DECODE(v, v1, result1, [v2, result2, ...], resultN)" function. */
- @LibraryOperator(libraries = {ORACLE})
+ @LibraryOperator(libraries = {ORACLE, SPARK})
public static final SqlFunction DECODE =
SqlBasicFunction.create(SqlKind.DECODE, DECODE_RETURN_TYPE,
OperandTypes.VARIADIC);
@@ -263,7 +263,7 @@ public abstract class SqlLibraryOperators {
}
/** The "NVL(value, value)" function. */
- @LibraryOperator(libraries = {ORACLE})
+ @LibraryOperator(libraries = {ORACLE, SPARK})
public static final SqlBasicFunction NVL =
SqlBasicFunction.create(SqlKind.NVL,
ReturnTypes.LEAST_RESTRICTIVE
@@ -271,16 +271,16 @@ public abstract class SqlLibraryOperators {
OperandTypes.SAME_SAME);
/** The "IFNULL(value, value)" function. */
- @LibraryOperator(libraries = {BIG_QUERY})
+ @LibraryOperator(libraries = {BIG_QUERY, SPARK})
public static final SqlFunction IFNULL = NVL.withName("IFNULL");
/** The "LEN(string)" function. */
- @LibraryOperator(libraries = {SNOWFLAKE})
+ @LibraryOperator(libraries = {SNOWFLAKE, SPARK})
public static final SqlFunction LEN =
SqlStdOperatorTable.CHAR_LENGTH.withName("LEN");
/** The "LENGTH(string)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, SNOWFLAKE})
+ @LibraryOperator(libraries = {BIG_QUERY, SNOWFLAKE, SPARK})
public static final SqlFunction LENGTH =
SqlStdOperatorTable.CHAR_LENGTH.withName("LENGTH");
@@ -291,7 +291,7 @@ public abstract class SqlLibraryOperators {
}
/** The "LPAD(original_value, return_length[, pattern])" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
public static final SqlFunction LPAD =
SqlBasicFunction.create(
"LPAD",
@@ -300,7 +300,7 @@ public abstract class SqlLibraryOperators {
SqlFunctionCategory.STRING);
/** The "RPAD(original_value, return_length[, pattern])" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
public static final SqlFunction RPAD =
SqlBasicFunction.create(
"RPAD",
@@ -309,7 +309,7 @@ public abstract class SqlLibraryOperators {
SqlFunctionCategory.STRING);
/** The "LTRIM(string)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
public static final SqlFunction LTRIM =
SqlBasicFunction.create(SqlKind.LTRIM,
ReturnTypes.ARG0.andThen(SqlTypeTransforms.TO_NULLABLE)
@@ -318,7 +318,7 @@ public abstract class SqlLibraryOperators {
.withFunctionType(SqlFunctionCategory.STRING);
/** The "RTRIM(string)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
public static final SqlFunction RTRIM =
SqlBasicFunction.create(SqlKind.RTRIM,
ReturnTypes.ARG0.andThen(SqlTypeTransforms.TO_NULLABLE)
@@ -373,7 +373,7 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING_SAME_SAME);
/** The "ENDSWITH(value1, value2)" function (Snowflake). */
- @LibraryOperator(libraries = {SNOWFLAKE})
+ @LibraryOperator(libraries = {SNOWFLAKE, SPARK})
public static final SqlFunction ENDSWITH = ENDS_WITH.withName("ENDSWITH");
/** The "STARTS_WITH(value1, value2)" function (BigQuery, PostgreSQL). */
@@ -383,7 +383,7 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING_SAME_SAME);
/** The "STARTSWITH(value1, value2)" function (Snowflake). */
- @LibraryOperator(libraries = {SNOWFLAKE})
+ @LibraryOperator(libraries = {SNOWFLAKE, SPARK})
public static final SqlFunction STARTSWITH =
STARTS_WITH.withName("STARTSWITH");
/** BigQuery's "SUBSTR(string, position [, substringLength ])" function. */
@@ -436,14 +436,14 @@ public abstract class SqlLibraryOperators {
SqlFunctionCategory.STRING);
/** The "GREATEST(value, value)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, 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})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
public static final SqlFunction LEAST =
SqlBasicFunction.create(SqlKind.LEAST,
ReturnTypes.LEAST_RESTRICTIVE.andThen(SqlTypeTransforms.TO_NULLABLE),
@@ -472,7 +472,7 @@ public abstract class SqlLibraryOperators {
* <p>It is not defined in the SQL standard, but occurs in Oracle and
* PostgreSQL.
*/
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL, SPARK})
public static final SqlFunction TRANSLATE3 = new SqlTranslate3Function();
@LibraryOperator(libraries = {MYSQL})
@@ -602,13 +602,13 @@ public abstract class SqlLibraryOperators {
/** The "BOOL_AND(condition)" aggregate function, PostgreSQL and Redshift's
* equivalent to {@link SqlStdOperatorTable#EVERY}. */
- @LibraryOperator(libraries = {POSTGRESQL})
+ @LibraryOperator(libraries = {POSTGRESQL, SPARK})
public static final SqlAggFunction BOOL_AND =
new SqlMinMaxAggFunction("BOOL_AND", SqlKind.MIN, OperandTypes.BOOLEAN);
/** The "BOOL_OR(condition)" aggregate function, PostgreSQL and Redshift's
* equivalent to {@link SqlStdOperatorTable#SOME}. */
- @LibraryOperator(libraries = {POSTGRESQL})
+ @LibraryOperator(libraries = {POSTGRESQL, SPARK})
public static final SqlAggFunction BOOL_OR =
new SqlMinMaxAggFunction("BOOL_OR", SqlKind.MAX, OperandTypes.BOOLEAN);
@@ -857,7 +857,7 @@ public abstract class SqlLibraryOperators {
/** The "DATE_FROM_UNIX_DATE(integer)" function; returns a DATE value
* a given number of seconds after 1970-01-01. */
- @LibraryOperator(libraries = {BIG_QUERY})
+ @LibraryOperator(libraries = {BIG_QUERY, SPARK})
public static final SqlFunction DATE_FROM_UNIX_DATE =
SqlBasicFunction.create("DATE_FROM_UNIX_DATE",
ReturnTypes.DATE_NULLABLE, OperandTypes.INTEGER,
@@ -865,7 +865,7 @@ public abstract class SqlLibraryOperators {
/** The "UNIX_DATE(date)" function; returns the number of days since
* 1970-01-01. */
- @LibraryOperator(libraries = {BIG_QUERY})
+ @LibraryOperator(libraries = {BIG_QUERY, SPARK})
public static final SqlFunction UNIX_DATE =
SqlBasicFunction.create("UNIX_DATE",
ReturnTypes.INTEGER_NULLABLE, OperandTypes.DATE,
@@ -896,25 +896,25 @@ public abstract class SqlLibraryOperators {
ReturnTypes.VARCHAR_2000, OperandTypes.DATETIME,
SqlFunctionCategory.TIMEDATE);
- @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL})
+ @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL, SPARK})
public static final SqlFunction LEFT =
SqlBasicFunction.create("LEFT",
ReturnTypes.ARG0_NULLABLE_VARYING,
OperandTypes.CBSTRING_INTEGER, SqlFunctionCategory.STRING);
- @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL})
+ @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL, SPARK})
public static final SqlFunction REPEAT =
SqlBasicFunction.create("REPEAT",
ReturnTypes.VARCHAR_NULLABLE,
OperandTypes.STRING_INTEGER,
SqlFunctionCategory.STRING);
- @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL})
+ @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL, SPARK})
public static final SqlFunction RIGHT =
SqlBasicFunction.create("RIGHT", ReturnTypes.ARG0_NULLABLE_VARYING,
OperandTypes.CBSTRING_INTEGER, SqlFunctionCategory.STRING);
- @LibraryOperator(libraries = {MYSQL})
+ @LibraryOperator(libraries = {MYSQL, SPARK})
public static final SqlFunction SPACE =
SqlBasicFunction.create("SPACE",
ReturnTypes.VARCHAR_NULLABLE,
@@ -1842,7 +1842,7 @@ public abstract class SqlLibraryOperators {
/** The "TIMESTAMP_SECONDS(bigint)" function; returns a TIMESTAMP value
* a given number of seconds after 1970-01-01 00:00:00. */
- @LibraryOperator(libraries = {BIG_QUERY})
+ @LibraryOperator(libraries = {BIG_QUERY, SPARK})
public static final SqlFunction TIMESTAMP_SECONDS =
SqlBasicFunction.create("TIMESTAMP_SECONDS",
ReturnTypes.TIMESTAMP_NULLABLE, OperandTypes.INTEGER,
@@ -1850,7 +1850,7 @@ public abstract class SqlLibraryOperators {
/** The "TIMESTAMP_MILLIS(bigint)" function; returns a TIMESTAMP value
* a given number of milliseconds after 1970-01-01 00:00:00. */
- @LibraryOperator(libraries = {BIG_QUERY})
+ @LibraryOperator(libraries = {BIG_QUERY, SPARK})
public static final SqlFunction TIMESTAMP_MILLIS =
SqlBasicFunction.create("TIMESTAMP_MILLIS",
ReturnTypes.TIMESTAMP_NULLABLE, OperandTypes.INTEGER,
@@ -1858,7 +1858,7 @@ public abstract class SqlLibraryOperators {
/** The "TIMESTAMP_MICROS(bigint)" function; returns a TIMESTAMP value
* a given number of micro-seconds after 1970-01-01 00:00:00. */
- @LibraryOperator(libraries = {BIG_QUERY})
+ @LibraryOperator(libraries = {BIG_QUERY, SPARK})
public static final SqlFunction TIMESTAMP_MICROS =
SqlBasicFunction.create("TIMESTAMP_MICROS",
ReturnTypes.TIMESTAMP_NULLABLE, OperandTypes.INTEGER,
@@ -1866,14 +1866,14 @@ public abstract class SqlLibraryOperators {
/** The "UNIX_SECONDS(bigint)" function; returns the number of seconds
* since 1970-01-01 00:00:00. */
- @LibraryOperator(libraries = {BIG_QUERY})
+ @LibraryOperator(libraries = {BIG_QUERY, SPARK})
public static final SqlFunction UNIX_SECONDS =
SqlBasicFunction.create("UNIX_SECONDS", ReturnTypes.BIGINT_NULLABLE,
OperandTypes.TIMESTAMP, SqlFunctionCategory.TIMEDATE);
/** The "UNIX_MILLIS(bigint)" function; returns the number of milliseconds
* since 1970-01-01 00:00:00. */
- @LibraryOperator(libraries = {BIG_QUERY})
+ @LibraryOperator(libraries = {BIG_QUERY, SPARK})
public static final SqlFunction UNIX_MILLIS =
SqlBasicFunction.create("UNIX_MILLIS",
ReturnTypes.BIGINT_NULLABLE, OperandTypes.TIMESTAMP,
@@ -1881,7 +1881,7 @@ public abstract class SqlLibraryOperators {
/** The "UNIX_MICROS(bigint)" function; returns the number of microseconds
* since 1970-01-01 00:00:00. */
- @LibraryOperator(libraries = {BIG_QUERY})
+ @LibraryOperator(libraries = {BIG_QUERY, SPARK})
public static final SqlFunction UNIX_MICROS =
SqlBasicFunction.create("UNIX_MICROS",
ReturnTypes.BIGINT_NULLABLE, OperandTypes.TIMESTAMP,
@@ -2093,14 +2093,14 @@ public abstract class SqlLibraryOperators {
OperandTypes.INTEGER,
SqlFunctionCategory.NUMERIC);
- @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL})
+ @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL, SPARK})
public static final SqlFunction MD5 =
SqlBasicFunction.create("MD5",
ReturnTypes.VARCHAR_NULLABLE,
OperandTypes.STRING.or(OperandTypes.BINARY),
SqlFunctionCategory.STRING);
- @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL})
+ @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL, SPARK})
public static final SqlFunction SHA1 =
SqlBasicFunction.create("SHA1",
ReturnTypes.VARCHAR_NULLABLE,
@@ -2149,7 +2149,7 @@ public abstract class SqlLibraryOperators {
OperandTypes.NUMERIC_OPTIONAL_NUMERIC,
SqlFunctionCategory.NUMERIC);
- @LibraryOperator(libraries = {BIG_QUERY})
+ @LibraryOperator(libraries = {BIG_QUERY, SPARK})
public static final SqlFunction POW =
SqlStdOperatorTable.POWER.withName("POW");
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 6b9ba28305..433a62ad7f 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2734,15 +2734,15 @@ In the following:
| b | DATETIME_DIFF(timestamp, timestamp2, timeUnit) | Returns the whole
number of *timeUnit* between *timestamp* and *timestamp2*
| 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 | DATE_FROM_UNIX_DATE(integer) | Returns the DATE that
is *integer* days after 1970-01-01
+| 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)`
| 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 | 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
+| 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
-| f | ENDSWITH(string1, string2) | Returns whether
*string2* is a suffix of *string1*
+| f s | ENDSWITH(string1, string2) | Returns whether
*string2* is a suffix of *string1*
| b p | 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.
@@ -2758,9 +2758,9 @@ 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 | GREATEST(expr [, expr ]*) | Returns the greatest of
the expressions
+| b o 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 | IFNULL(value1, value2) | Equivalent to
`NVL(value1, value2)`
+| 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*
@@ -2777,20 +2777,20 @@ 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 | LEAST(expr [, expr ]* ) | Returns the least of
the expressions
-| b m p | LEFT(string, length) | Returns the leftmost
*length* characters from the *string*
-| f | LEN(string) | Equivalent to
`CHAR_LENGTH(string)`
-| b f | LENGTH(string) | Equivalent to
`CHAR_LENGTH(string)`
+| 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*
+| 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
-| b o | LPAD(string, length [, pattern ]) | Returns a string or
bytes value that consists of *string* prepended to *length* with *pattern*
+| b o 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 | LTRIM(string) | Returns *string* with
all blanks removed from the start
+| b o 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
@@ -2801,9 +2801,9 @@ 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 | MD5(string) | Calculates an MD5
128-bit checksum of *string* and returns it as a hex string
+| b m p 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 | NVL(value1, value2) | Returns *value1* if
*value1* is not null, otherwise *value2*
+| o s | NVL(value1, value2) | Returns *value1* if
*value1* is not null, otherwise *value2*
| 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
@@ -2811,20 +2811,20 @@ In the following:
| b | PARSE_TIME(format, string) | Uses format specified
by *format* to convert *string* representation of time to a TIME value
| 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 | POW(numeric1, numeric2) | Returns *numeric1*
raised to the power *numeric2*
+| b s | POW(numeric1, numeric2) | Returns *numeric1*
raised to the power *numeric2*
| 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
| 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
| b | REGEXP_SUBSTR(string, regexp [, position [, occurrence]]) | Synonym for
REGEXP_EXTRACT
-| b m p | 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 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 | RIGHT(string, length) | Returns the rightmost
*length* characters from the *string*
+| b m p s | RIGHT(string, length) | Returns the rightmost
*length* characters from the *string*
| h s | string1 RLIKE string2 | Whether *string1*
matches regex pattern *string2* (similar to `LIKE`, but uses Java regex)
| h s | string1 NOT RLIKE string2 | Whether *string1* does
not match regex pattern *string2* (similar to `NOT LIKE`, but uses Java regex)
-| b o | RPAD(string, length[, pattern ]) | Returns a string or
bytes value that consists of *string* appended to *length* with *pattern*
-| b o | RTRIM(string) | Returns *string* with
all blanks removed from the end
+| 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 | SAFE_ADD(numeric1, numeric2) | Returns *numeric1* +
*numeric2*, or NULL on overflow
| 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
@@ -2835,15 +2835,15 @@ In the following:
| b | SAFE_SUBTRACT(numeric1, numeric2) | Returns *numeric1* -
*numeric2*, or NULL on overflow
| * | SEC(numeric) | Returns the secant of
*numeric* in radians
| * | SECH(numeric) | Returns the hyperbolic
secant of *numeric*
-| b m p | SHA1(string) | Calculates a SHA-1 hash
value of *string* and returns it as a hex string
+| b m p 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
| 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 | SPACE(integer) | Returns a string of
*integer* spaces; returns an empty string if *integer* is less than 1
+| 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 | STARTSWITH(string1, string2) | Returns whether
*string2* is a prefix of *string1*
+| 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)`
@@ -2861,9 +2861,9 @@ In the following:
| b | TIMESTAMP(timestamp, timeZone) | Converts *timestamp* to
a TIMESTAMP WITH LOCAL TIME ZONE, in *timeZone*
| b | TIMESTAMP_ADD(timestamp, interval) | Returns the TIMESTAMP
value that occurs *interval* after *timestamp*
| b | TIMESTAMP_DIFF(timestamp, timestamp2, timeUnit) | Returns the whole
number of *timeUnit* between *timestamp* and *timestamp2*. Equivalent to
`TIMESTAMPDIFF(timeUnit, timestamp2, timestamp)` and `(timestamp - timestamp2)
timeUnit`
-| b | TIMESTAMP_MICROS(integer) | Returns the TIMESTAMP
that is *integer* microseconds after 1970-01-01 00:00:00
-| b | TIMESTAMP_MILLIS(integer) | Returns the TIMESTAMP
that is *integer* milliseconds after 1970-01-01 00:00:00
-| b | TIMESTAMP_SECONDS(integer) | Returns the TIMESTAMP
that is *integer* seconds after 1970-01-01 00:00:00
+| b s | TIMESTAMP_MICROS(integer) | Returns the TIMESTAMP
that is *integer* microseconds after 1970-01-01 00:00:00
+| b s | TIMESTAMP_MILLIS(integer) | Returns the TIMESTAMP
that is *integer* milliseconds after 1970-01-01 00:00:00
+| b s | TIMESTAMP_SECONDS(integer) | Returns the TIMESTAMP
that is *integer* seconds after 1970-01-01 00:00:00
| b | TIMESTAMP_SUB(timestamp, interval) | Returns the TIMESTAMP
value that is *interval* before *timestamp*
| b | TIMESTAMP_TRUNC(timestamp, timeUnit) | Truncates *timestamp*
to the granularity of *timeUnit*, rounding to the beginning of the unit
| b | TIME_ADD(time, interval) | Adds *interval* to
*time*, independent of any time zone
@@ -2874,13 +2874,13 @@ In the following:
| 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 | 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 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
| b | TRUNC(numeric1 [, numeric2 ]) | Truncates *numeric1* to
optionally *numeric2* (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 | UNIX_MICROS(timestamp) | Returns the number of
microseconds since 1970-01-01 00:00:00
-| b | UNIX_MILLIS(timestamp) | Returns the number of
milliseconds since 1970-01-01 00:00:00
-| b | UNIX_SECONDS(timestamp) | Returns the number of
seconds since 1970-01-01 00:00:00
-| b | UNIX_DATE(date) | Returns the number of
days since 1970-01-01
+| b s | UNIX_MICROS(timestamp) | Returns the number of
microseconds since 1970-01-01 00:00:00
+| b s | UNIX_MILLIS(timestamp) | Returns the number of
milliseconds since 1970-01-01 00:00:00
+| b s | UNIX_SECONDS(timestamp) | Returns the number of
seconds since 1970-01-01 00:00:00
+| b s | UNIX_DATE(date) | Returns the number of
days since 1970-01-01
| s | URL_DECODE(string) | Decodes a *string* in
'application/x-www-form-urlencoded' format using a specific encoding scheme,
returns original *string* when decoded error
| s | URL_ENCODE(string) | Translates a *string*
into 'application/x-www-form-urlencoded' format using a specific encoding scheme
| o | XMLTRANSFORM(xml, xslt) | Applies XSLT transform
*xslt* to XML string *xml* and returns the result
@@ -2920,8 +2920,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 | BOOL_AND(condition) | Synonym for `EVERY`
-| p | BOOL_OR(condition) | Synonym for `SOME`
+| p s | BOOL_AND(condition) | Synonym for `EVERY`
+| p s | BOOL_OR(condition) | Synonym for `SOME`
| b | COUNTIF(condition) | Returns the number of
rows for which *condition* is TRUE; equivalent to `COUNT(*) FILTER (WHERE
condition)`
| m | GROUP_CONCAT( [ ALL | DISTINCT ] value [, value ]* [ ORDER BY
orderItem [, orderItem ]* ] [ SEPARATOR separator ] ) | MySQL-specific variant
of `LISTAGG`
| b | LOGICAL_AND(condition) | Synonym for `EVERY`
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 9f3898c221..badde6f889 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -4022,7 +4022,7 @@ public class SqlOperatorTest {
};
final List<SqlLibrary> libraries =
ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE,
- SqlLibrary.POSTGRESQL);
+ SqlLibrary.POSTGRESQL, SqlLibrary.SPARK);
f0.forEachLibrary(libraries, consumer);
}
@@ -4520,7 +4520,7 @@ public class SqlOperatorTest {
false);
final List<SqlLibrary> libraries =
ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL,
- SqlLibrary.POSTGRESQL);
+ SqlLibrary.POSTGRESQL, SqlLibrary.SPARK);
final Consumer<SqlOperatorFixture> consumer = f -> {
f.checkString("md5(x'')",
"d41d8cd98f00b204e9800998ecf8427e",
@@ -4545,7 +4545,7 @@ public class SqlOperatorTest {
false);
final List<SqlLibrary> libraries =
ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL,
- SqlLibrary.POSTGRESQL);
+ SqlLibrary.POSTGRESQL, SqlLibrary.SPARK);
final Consumer<SqlOperatorFixture> consumer = f -> {
f.checkString("sha1(x'')",
"da39a3ee5e6b4b0d3255bfef95601890afd80709",
@@ -4665,19 +4665,23 @@ 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(list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL), consumer);
+ f0.forEachLibrary(
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL,
+ SqlLibrary.POSTGRESQL, SqlLibrary.SPARK), consumer);
}
@Test void testSpaceFunc() {
- final SqlOperatorFixture f = fixture()
- .setFor(SqlLibraryOperators.SPACE)
- .withLibrary(SqlLibrary.MYSQL);
- f.checkString("SPACE(-100)", "", "VARCHAR NOT NULL");
- f.checkString("SPACE(-1)", "", "VARCHAR NOT NULL");
- f.checkString("SPACE(0)", "", "VARCHAR NOT NULL");
- f.checkString("SPACE(2)", " ", "VARCHAR NOT NULL");
- f.checkString("SPACE(5)", " ", "VARCHAR NOT NULL");
- f.checkNull("SPACE(cast(null as integer))");
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.SPACE);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkString("SPACE(-100)", "", "VARCHAR NOT NULL");
+ f.checkString("SPACE(-1)", "", "VARCHAR NOT NULL");
+ f.checkString("SPACE(0)", "", "VARCHAR NOT NULL");
+ f.checkString("SPACE(2)", " ", "VARCHAR NOT NULL");
+ f.checkString("SPACE(5)", " ", "VARCHAR NOT NULL");
+ f.checkNull("SPACE(cast(null as integer))");
+ };
+ f0.forEachLibrary(list(SqlLibrary.MYSQL, SqlLibrary.SPARK), consumer);
}
@Test void testStrcmpFunc() {
@@ -4872,7 +4876,9 @@ public class SqlOperatorTest {
f.checkNull("left(cast(null as binary(1)), -2)");
f.checkNull("left(x'ABCdef', cast(null as Integer))");
};
- f0.forEachLibrary(list(SqlLibrary.MYSQL, SqlLibrary.POSTGRESQL), consumer);
+ f0.forEachLibrary(
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL, SqlLibrary.POSTGRESQL,
+ SqlLibrary.SPARK), consumer);
}
@Test void testRightFunc() {
@@ -4896,7 +4902,9 @@ public class SqlOperatorTest {
f.checkNull("right(x'ABCdef', cast(null as Integer))");
};
- f0.forEachLibrary(list(SqlLibrary.MYSQL, SqlLibrary.POSTGRESQL), consumer);
+ f0.forEachLibrary(
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL, SqlLibrary.POSTGRESQL,
+ SqlLibrary.SPARK), consumer);
}
@Test void testRegexpContainsFunc() {
@@ -7869,12 +7877,14 @@ public class SqlOperatorTest {
}
@Test void testPowFunc() {
- final SqlOperatorFixture f = fixture()
- .setFor(SqlLibraryOperators.POW)
- .withLibrary(SqlLibrary.BIG_QUERY);
- f.checkScalarApprox("pow(2,3)", "DOUBLE NOT NULL", isExactly("8.0"));
- f.checkNull("pow(2, cast(null as integer))");
- f.checkNull("pow(cast(null as integer), 2)");
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.POW);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkScalarApprox("pow(2,3)", "DOUBLE NOT NULL", isExactly("8.0"));
+ f.checkNull("pow(2, cast(null as integer))");
+ f.checkNull("pow(cast(null as integer), 2)");
+ };
+ f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.SPARK), consumer);
}
@Test void testInfinity() {
@@ -8893,54 +8903,60 @@ public class SqlOperatorTest {
}
@Test void testLpadFunction() {
- final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.BIG_QUERY);
- f.setFor(SqlLibraryOperators.LPAD);
- f.check("select lpad('12345', 8, 'a')", "VARCHAR NOT NULL", "aaa12345");
- f.checkString("lpad('12345', 8)", " 12345", "VARCHAR NOT NULL");
- f.checkString("lpad('12345', 8, 'ab')", "aba12345", "VARCHAR NOT NULL");
- f.checkString("lpad('12345', 3, 'a')", "123", "VARCHAR NOT NULL");
- f.checkFails("lpad('12345', -3, 'a')",
- "Second argument for LPAD/RPAD must not be negative", true);
- f.checkFails("lpad('12345', -3)",
- "Second argument for LPAD/RPAD must not be negative", true);
- f.checkFails("lpad('12345', 3, '')",
- "Third argument \\(pad pattern\\) for LPAD/RPAD must not be empty",
true);
- f.checkString("lpad(x'aa', 4, x'bb')", "bbbbbbaa", "VARBINARY NOT NULL");
- f.checkString("lpad(x'aa', 4)", "202020aa", "VARBINARY NOT NULL");
- f.checkString("lpad(x'aaaaaa', 2)", "aaaa", "VARBINARY NOT NULL");
- f.checkString("lpad(x'aaaaaa', 2, x'bb')", "aaaa", "VARBINARY NOT NULL");
- f.checkFails("lpad(x'aa', -3, x'bb')",
- "Second argument for LPAD/RPAD must not be negative", true);
- f.checkFails("lpad(x'aa', -3)",
- "Second argument for LPAD/RPAD must not be negative", true);
- f.checkFails("lpad(x'aa', 3, x'')",
- "Third argument \\(pad pattern\\) for LPAD/RPAD must not be empty",
true);
+ final SqlOperatorFixture f0 = fixture();
+ f0.setFor(SqlLibraryOperators.LPAD);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.check("select lpad('12345', 8, 'a')", "VARCHAR NOT NULL", "aaa12345");
+ f.checkString("lpad('12345', 8)", " 12345", "VARCHAR NOT NULL");
+ f.checkString("lpad('12345', 8, 'ab')", "aba12345", "VARCHAR NOT NULL");
+ f.checkString("lpad('12345', 3, 'a')", "123", "VARCHAR NOT NULL");
+ f.checkFails("lpad('12345', -3, 'a')",
+ "Second argument for LPAD/RPAD must not be negative", true);
+ f.checkFails("lpad('12345', -3)",
+ "Second argument for LPAD/RPAD must not be negative", true);
+ f.checkFails("lpad('12345', 3, '')",
+ "Third argument \\(pad pattern\\) for LPAD/RPAD must not be empty",
true);
+ f.checkString("lpad(x'aa', 4, x'bb')", "bbbbbbaa", "VARBINARY NOT NULL");
+ f.checkString("lpad(x'aa', 4)", "202020aa", "VARBINARY NOT NULL");
+ f.checkString("lpad(x'aaaaaa', 2)", "aaaa", "VARBINARY NOT NULL");
+ f.checkString("lpad(x'aaaaaa', 2, x'bb')", "aaaa", "VARBINARY NOT NULL");
+ f.checkFails("lpad(x'aa', -3, x'bb')",
+ "Second argument for LPAD/RPAD must not be negative", true);
+ f.checkFails("lpad(x'aa', -3)",
+ "Second argument for LPAD/RPAD must not be negative", true);
+ 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);
}
@Test void testRpadFunction() {
- final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.BIG_QUERY);
- f.setFor(SqlLibraryOperators.RPAD);
- f.check("select rpad('12345', 8, 'a')", "VARCHAR NOT NULL", "12345aaa");
- f.checkString("rpad('12345', 8)", "12345 ", "VARCHAR NOT NULL");
- f.checkString("rpad('12345', 8, 'ab')", "12345aba", "VARCHAR NOT NULL");
- f.checkString("rpad('12345', 3, 'a')", "123", "VARCHAR NOT NULL");
- f.checkFails("rpad('12345', -3, 'a')",
- "Second argument for LPAD/RPAD must not be negative", true);
- f.checkFails("rpad('12345', -3)",
- "Second argument for LPAD/RPAD must not be negative", true);
- f.checkFails("rpad('12345', 3, '')",
- "Third argument \\(pad pattern\\) for LPAD/RPAD must not be empty",
true);
-
- f.checkString("rpad(x'aa', 4, x'bb')", "aabbbbbb", "VARBINARY NOT NULL");
- f.checkString("rpad(x'aa', 4)", "aa202020", "VARBINARY NOT NULL");
- f.checkString("rpad(x'aaaaaa', 2)", "aaaa", "VARBINARY NOT NULL");
- f.checkString("rpad(x'aaaaaa', 2, x'bb')", "aaaa", "VARBINARY NOT NULL");
- f.checkFails("rpad(x'aa', -3, x'bb')",
- "Second argument for LPAD/RPAD must not be negative", true);
- f.checkFails("rpad(x'aa', -3)",
- "Second argument for LPAD/RPAD must not be negative", true);
- f.checkFails("rpad(x'aa', 3, x'')",
- "Third argument \\(pad pattern\\) for LPAD/RPAD must not be empty",
true);
+ final SqlOperatorFixture f0 = fixture();
+ f0.setFor(SqlLibraryOperators.RPAD);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.check("select rpad('12345', 8, 'a')", "VARCHAR NOT NULL", "12345aaa");
+ f.checkString("rpad('12345', 8)", "12345 ", "VARCHAR NOT NULL");
+ f.checkString("rpad('12345', 8, 'ab')", "12345aba", "VARCHAR NOT NULL");
+ f.checkString("rpad('12345', 3, 'a')", "123", "VARCHAR NOT NULL");
+ f.checkFails("rpad('12345', -3, 'a')",
+ "Second argument for LPAD/RPAD must not be negative", true);
+ f.checkFails("rpad('12345', -3)",
+ "Second argument for LPAD/RPAD must not be negative", true);
+ f.checkFails("rpad('12345', 3, '')",
+ "Third argument \\(pad pattern\\) for LPAD/RPAD must not be empty",
true);
+
+ f.checkString("rpad(x'aa', 4, x'bb')", "aabbbbbb", "VARBINARY NOT NULL");
+ f.checkString("rpad(x'aa', 4)", "aa202020", "VARBINARY NOT NULL");
+ f.checkString("rpad(x'aaaaaa', 2)", "aaaa", "VARBINARY NOT NULL");
+ f.checkString("rpad(x'aaaaaa', 2, x'bb')", "aaaa", "VARBINARY NOT NULL");
+ f.checkFails("rpad(x'aa', -3, x'bb')",
+ "Second argument for LPAD/RPAD must not be negative", true);
+ f.checkFails("rpad(x'aa', -3)",
+ "Second argument for LPAD/RPAD must not be negative", true);
+ 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);
}
@Test void testContainsSubstrFunc() {
@@ -9660,7 +9676,7 @@ 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), consumer);
+ f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE,
SqlLibrary.SPARK), consumer);
}
@Test void testLtrimFunc() {
@@ -9673,7 +9689,7 @@ 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), consumer);
+ f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE,
SqlLibrary.SPARK), consumer);
}
@Test void testGreatestFunc() {
@@ -9695,7 +9711,7 @@ public class SqlOperatorTest {
f12.checkString("greatest('show', 'on', 'earth')", "show",
"VARCHAR(5) NOT NULL");
};
- f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE), consumer);
+ f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE,
SqlLibrary.SPARK), consumer);
}
@Test void testLeastFunc() {
@@ -9717,7 +9733,7 @@ public class SqlOperatorTest {
f12.checkString("least('show', 'on', 'earth')", "earth",
"VARCHAR(5) NOT NULL");
};
- f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE), consumer);
+ f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE,
SqlLibrary.SPARK), consumer);
}
@Test void testIfNullFunc() {
@@ -9765,6 +9781,7 @@ public class SqlOperatorTest {
@Test void testDecodeFunc() {
checkDecodeFunc(fixture().withLibrary(SqlLibrary.ORACLE));
+ checkDecodeFunc(fixture().withLibrary(SqlLibrary.SPARK));
}
private static void checkDecodeFunc(SqlOperatorFixture f) {
@@ -13490,6 +13507,7 @@ public class SqlOperatorTest {
"No match found for function signature BOOL_AND\\(<BOOLEAN>\\)",
false);
checkBoolAndFunc(f.withLibrary(SqlLibrary.POSTGRESQL));
+ checkBoolAndFunc(f.withLibrary(SqlLibrary.SPARK));
}
private static void checkBoolAndFunc(SqlOperatorFixture f) {
@@ -13537,6 +13555,7 @@ public class SqlOperatorTest {
"No match found for function signature BOOL_OR\\(<BOOLEAN>\\)", false);
checkBoolOrFunc(f.withLibrary(SqlLibrary.POSTGRESQL));
+ checkBoolOrFunc(f.withLibrary(SqlLibrary.SPARK));
}
private static void checkBoolOrFunc(SqlOperatorFixture f) {
