This is an automated email from the ASF dual-hosted git repository.
cancai pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new f10d0cedb5 [CALCITE-6800] Enable a few existing functions to Hive
library
f10d0cedb5 is described below
commit f10d0cedb5983d4053a203376ae30d9ba99f2683
Author: xuyu <[email protected]>
AuthorDate: Fri Jan 24 11:09:27 2025 +0800
[CALCITE-6800] Enable a few existing functions to Hive library
---
.../calcite/sql/fun/SqlLibraryOperators.java | 34 ++--
site/_docs/reference.md | 34 ++--
.../org/apache/calcite/test/SqlOperatorTest.java | 174 +++++++++++----------
3 files changed, 125 insertions(+), 117 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 3d3b321aa8..14ea74815f 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
@@ -132,7 +132,7 @@ private SqlLibraryOperators() {
/** The "DATE_ADD(date, numDays)" function
* (Spark) Returns the date that is num_days after start_date. */
- @LibraryOperator(libraries = {SPARK})
+ @LibraryOperator(libraries = {SPARK, HIVE})
public static final SqlFunction DATE_ADD_SPARK =
SqlBasicFunction.create(SqlKind.DATE_ADD, ReturnTypes.DATE_NULLABLE,
OperandTypes.DATE_ANY)
@@ -140,7 +140,7 @@ private SqlLibraryOperators() {
/** The "DATE_SUB(date, numDays)" function
* (Spark) Returns the date that is num_days before start_date.*/
- @LibraryOperator(libraries = {SPARK})
+ @LibraryOperator(libraries = {SPARK, HIVE})
public static final SqlFunction DATE_SUB_SPARK =
SqlBasicFunction.create(SqlKind.DATE_SUB, ReturnTypes.DATE_NULLABLE,
OperandTypes.DATE_ANY)
@@ -259,7 +259,7 @@ private static SqlCall transformConvert(SqlValidator
validator, SqlCall call) {
};
/** The "DECODE(v, v1, result1, [v2, result2, ...], resultN)" function. */
- @LibraryOperator(libraries = {ORACLE, REDSHIFT, SPARK})
+ @LibraryOperator(libraries = {ORACLE, REDSHIFT, SPARK, HIVE})
public static final SqlFunction DECODE =
SqlBasicFunction.create(SqlKind.DECODE, DECODE_RETURN_TYPE,
OperandTypes.VARIADIC);
@@ -325,7 +325,7 @@ private static RelDataType deriveTypePad(SqlOperatorBinding
binding, RelDataType
}
/** The "LPAD(original_value, return_length[, pattern])" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL, SPARK, HIVE})
public static final SqlFunction LPAD =
SqlBasicFunction.create(
"LPAD",
@@ -334,7 +334,7 @@ private static RelDataType deriveTypePad(SqlOperatorBinding
binding, RelDataType
SqlFunctionCategory.STRING);
/** The "RPAD(original_value, return_length[, pattern])" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL, SPARK, HIVE})
public static final SqlFunction RPAD =
SqlBasicFunction.create(
"RPAD",
@@ -343,7 +343,7 @@ private static RelDataType deriveTypePad(SqlOperatorBinding
binding, RelDataType
SqlFunctionCategory.STRING);
/** The "LTRIM(string)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL, SPARK, HIVE})
public static final SqlFunction LTRIM =
SqlBasicFunction.create(SqlKind.LTRIM,
ReturnTypes.ARG0.andThen(SqlTypeTransforms.TO_NULLABLE)
@@ -352,7 +352,7 @@ private static RelDataType deriveTypePad(SqlOperatorBinding
binding, RelDataType
.withFunctionType(SqlFunctionCategory.STRING);
/** The "RTRIM(string)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL, SPARK, HIVE})
public static final SqlFunction RTRIM =
SqlBasicFunction.create(SqlKind.RTRIM,
ReturnTypes.ARG0.andThen(SqlTypeTransforms.TO_NULLABLE)
@@ -482,7 +482,7 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding
operatorBinding,
SqlFunctionCategory.STRING);
/** The "GREATEST(value, value)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, HIVE})
public static final SqlFunction GREATEST =
SqlBasicFunction.create(SqlKind.GREATEST,
ReturnTypes.LEAST_RESTRICTIVE.andThen(SqlTypeTransforms.TO_NULLABLE),
@@ -497,7 +497,7 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding
operatorBinding,
OperandTypes.SAME_VARIADIC);
/** The "LEAST(value, value)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, HIVE})
public static final SqlFunction LEAST =
SqlBasicFunction.create(SqlKind.LEAST,
ReturnTypes.LEAST_RESTRICTIVE.andThen(SqlTypeTransforms.TO_NULLABLE),
@@ -611,7 +611,7 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding
operatorBinding,
/** The "REGEXP_REPLACE(value, regexp, rep)"
* function. Replaces all substrings of value that match regexp with
* {@code rep} and returns modified value. */
- @LibraryOperator(libraries = {MYSQL, ORACLE, REDSHIFT})
+ @LibraryOperator(libraries = {MYSQL, ORACLE, REDSHIFT, HIVE})
public static final SqlFunction REGEXP_REPLACE_3 =
SqlBasicFunction.create("REGEXP_REPLACE", ReturnTypes.VARCHAR_NULLABLE,
OperandTypes.STRING_STRING_STRING, SqlFunctionCategory.STRING);
@@ -1091,7 +1091,7 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding
operatorBinding,
ReturnTypes.ARG0_NULLABLE_VARYING,
OperandTypes.CBSTRING_INTEGER, SqlFunctionCategory.STRING);
- @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL, SPARK, HIVE})
public static final SqlFunction REPEAT =
SqlBasicFunction.create("REPEAT",
ReturnTypes.VARCHAR_NULLABLE,
@@ -1103,7 +1103,7 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding
operatorBinding,
SqlBasicFunction.create("RIGHT", ReturnTypes.ARG0_NULLABLE_VARYING,
OperandTypes.CBSTRING_INTEGER, SqlFunctionCategory.STRING);
- @LibraryOperator(libraries = {MYSQL, SPARK})
+ @LibraryOperator(libraries = {MYSQL, SPARK, HIVE})
public static final SqlFunction SPACE =
SqlBasicFunction.create("SPACE",
ReturnTypes.VARCHAR_NULLABLE,
@@ -1117,7 +1117,7 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding
operatorBinding,
OperandTypes.STRING_STRING,
SqlFunctionCategory.STRING);
- @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL, ORACLE})
+ @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL, ORACLE, HIVE})
public static final SqlFunction SOUNDEX =
SqlBasicFunction.create("SOUNDEX",
ReturnTypes.VARCHAR_4_NULLABLE,
@@ -1929,7 +1929,7 @@ private static RelDataType
deriveTypeMapFromEntries(SqlOperatorBinding opBinding
/** The "TO_DATE(string1, string2)" function; casts string1
* to a DATE using the format specified in string2. */
- @LibraryOperator(libraries = {ORACLE, REDSHIFT})
+ @LibraryOperator(libraries = {ORACLE, REDSHIFT, HIVE})
public static final SqlFunction TO_DATE =
SqlBasicFunction.create("TO_DATE",
ReturnTypes.DATE_NULLABLE,
@@ -2491,14 +2491,14 @@ private static RelDataType
deriveTypeMapFromEntries(SqlOperatorBinding opBinding
OperandTypes.INTEGER,
SqlFunctionCategory.NUMERIC);
- @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL, SPARK, HIVE})
public static final SqlFunction MD5 =
SqlBasicFunction.create("MD5",
ReturnTypes.VARCHAR_NULLABLE,
OperandTypes.STRING.or(OperandTypes.BINARY),
SqlFunctionCategory.STRING);
- @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL, SPARK, HIVE})
public static final SqlFunction SHA1 =
SqlBasicFunction.create("SHA1",
ReturnTypes.VARCHAR_NULLABLE,
@@ -2549,7 +2549,7 @@ private static RelDataType
deriveTypeMapFromEntries(SqlOperatorBinding opBinding
/** The "LOG(numeric1 [, numeric2 ]) " function. Returns the logarithm of
numeric2
* to base numeric1.*/
- @LibraryOperator(libraries = {MYSQL, SPARK})
+ @LibraryOperator(libraries = {MYSQL, SPARK, HIVE})
public static final SqlFunction LOG_MYSQL =
SqlBasicFunction.create(SqlKind.LOG,
ReturnTypes.DOUBLE_FORCE_NULLABLE,
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index b7621fd94a..2da0234810 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2865,12 +2865,12 @@ ### Dialect-specific Operators
| b s | DATE_FROM_UNIX_DATE(integer) | Returns the DATE that
is *integer* days after 1970-01-01
| 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*
-| s | DATE_ADD(date, numDays) | Returns the DATE that
is *numDays* after *date*
+| s h | DATE_ADD(date, numDays) | Returns the DATE that
is *numDays* 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*
-| s | DATE_SUB(date, numDays) | Returns the DATE that
is *numDays* before *date*
+| s h | DATE_SUB(date, numDays) | Returns the DATE that
is *numDays* before *date*
| b | DATE_TRUNC(date, timeUnit) | Truncates *date* to the
granularity of *timeUnit*, rounding to the beginning of the unit
-| 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
+| o r s h | 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 | ENDS_WITH(string1, string2) | Equivalent to
`ENDSWITH(string1, string2)`
@@ -2888,7 +2888,7 @@ ### Dialect-specific Operators
| 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 p r s | GREATEST(expr [, expr ]*) | Returns the greatest of
the expressions
+| b o p r s h | 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`)
@@ -2907,17 +2907,17 @@ ### Dialect-specific Operators
| 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 p r s | LEAST(expr [, expr ]* ) | Returns the least of
the expressions
+| b o p r s h | 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 r s | LEN(string) | Equivalent to
`CHAR_LENGTH(string)`
| b f h p r s | LENGTH(string) | Equivalent to
`CHAR_LENGTH(string)`
| h s | LEVENSHTEIN(string1, string2) | Returns the Levenshtein
distance between *string1* and *string2*
| b | LOG(numeric1 [, base ]) | Returns the logarithm
of *numeric1* to base *base*, or base e if *base* is not present, or error if
*numeric1* is 0 or negative
-| m s | LOG([, base ], numeric1) | Returns the logarithm
of *numeric1* to base *base*, or base e if *base* is not present, or null if
*numeric1* is 0 or negative
+| m s h | LOG([, base ], numeric1) | Returns the logarithm
of *numeric1* to base *base*, or base e if *base* is not present, or null if
*numeric1* is 0 or negative
| p | LOG([, base ], numeric1 ) | Returns the logarithm
of *numeric1* to base *base*, or base 10 if *numeric1* is not present, or error
if *numeric1* is 0 or negative
| m s | LOG2(numeric) | Returns the base 2
logarithm of *numeric*
| s | LOG1P(numeric) | Returns the natural
logarithm of 1 plus *numeric*
-| 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 o p r s h | 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
@@ -2925,7 +2925,7 @@ ### Dialect-specific Operators
| h s | HEX(string) | Converts *string* into
a hexadecimal varchar
| b | TO_HEX(binary) | Converts *binary* into
a hexadecimal varchar
| b | FROM_HEX(varchar) | Converts a
hexadecimal-encoded *varchar* into bytes
-| b o p r s | LTRIM(string) | Returns *string* with
all blanks removed from the start
+| b o p r s h | 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
@@ -2937,7 +2937,7 @@ ### Dialect-specific Operators
| 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
| s | SUBSTRING_INDEX(string, delim, count) | Returns the substring
from *string* before *count* occurrences of the delimiter *delim*. If *count*
is positive, everything to the left of the final delimiter (counting from the
left) is returned. If *count* is negative, everything to the right of the final
delimiter (counting from the right) is returned. The function substring_index
performs a case-sensitive match when searching for *delim*.
-| b m p r s | MD5(string) | Calculates an MD5
128-bit checksum of *string* and returns it as a hex string
+| b m p r s h | 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, for a locale
of en, it will return 'February' for both DATE '2020-02-10' and TIMESTAMP
'2020-02-10 10:10:10', and for a locale of zh, it will return '二月'
| 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*
@@ -2958,17 +2958,17 @@ ### Dialect-specific Operators
| 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 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>
| r | REGEXP_REPLACE(string, regexp) | Replaces all substrings
of *string* that match *regexp* with the empty string
-| 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 m o r h | 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
| p | REGEXP_REPLACE(string, regexp, rep [, matchType]) | Replaces substrings
of *string* that match *regexp* with *rep* at the starting *pos* in expr,
*matchType* specifies how to perform matching and whether to only replace first
match or all
| b | REGEXP_SUBSTR(string, regexp [, position [, occurrence]]) | Synonym for
REGEXP_EXTRACT
-| 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 p r s h | 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
| s | REVERSE(string \| array) | Returns *string* with
the characters in reverse order or array with elements in reverse order
| 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 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 o p r s h | RPAD(string, length[, pattern ]) | Returns a string or
bytes value that consists of *string* appended to *length* with *pattern*
+| b o p r s h | 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
@@ -2979,14 +2979,14 @@ ### Dialect-specific Operators
| 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 r s | SHA1(string) | Calculates a SHA-1 hash
value of *string* and returns it as a hex string
+| b m p r s h | 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
| p | SIND(numeric) | Returns the sine of
*numeric* in degrees as a double. Returns NaN if *numeric* is NaN. Fails if
*numeric* is greater than the maximum double value.
| * | SINH(numeric) | Returns the hyperbolic
sine of *numeric*
-| 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
+| b m o p r h | 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
+| m s h | 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*.
| p | SPLIT_PART(string, delimiter, n) | Returns the *n*th field
in *string* using *delimiter*; returns empty string if *n* is less than 1 or
greater than the number of fields, and the n can be negative to count from the
end.
| f s | STARTSWITH(string1, string2) | Returns whether
*string2* is a prefix of *string1*
@@ -3021,7 +3021,7 @@ ### Dialect-specific Operators
| b | TIME_TRUNC(time, timeUnit) | Truncates *time* to the
granularity of *timeUnit*, rounding to the beginning of the unit
| 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 r | TO_DATE(string, format) | Converts *string* to a
date using the format *format*
+| o p r h | 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
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 2b348a20e6..f11bf01a12 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -5555,7 +5555,7 @@ void testBitGetFunc(SqlOperatorFixture f, String
functionName) {
f.checkNull("to_date(NULL, NULL)");
};
final List<SqlLibrary> libraries =
- list(SqlLibrary.ORACLE, SqlLibrary.REDSHIFT);
+ list(SqlLibrary.ORACLE, SqlLibrary.REDSHIFT, SqlLibrary.HIVE);
f0.forEachLibrary(libraries, consumer);
}
@@ -5725,7 +5725,8 @@ void testBitGetFunc(SqlOperatorFixture f, String
functionName) {
false);
final List<SqlLibrary> libraries =
ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL,
- SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
+ SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT,
+ SqlLibrary.SPARK, SqlLibrary.HIVE);
final Consumer<SqlOperatorFixture> consumer = f -> {
f.checkString("md5(x'')",
"d41d8cd98f00b204e9800998ecf8427e",
@@ -5750,7 +5751,7 @@ void testBitGetFunc(SqlOperatorFixture f, String
functionName) {
false);
final List<SqlLibrary> libraries =
ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL,
- SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
+ SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT, SqlLibrary.SPARK,
SqlLibrary.HIVE);
final Consumer<SqlOperatorFixture> consumer = f -> {
f.checkString("sha1(x'')",
"da39a3ee5e6b4b0d3255bfef95601890afd80709",
@@ -5902,7 +5903,7 @@ void testBitGetFunc(SqlOperatorFixture f, String
functionName) {
final List<SqlLibrary> libraries =
list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL,
SqlLibrary.POSTGRESQL, SqlLibrary.SPARK,
- SqlLibrary.REDSHIFT);
+ SqlLibrary.REDSHIFT, SqlLibrary.HIVE);
f0.forEachLibrary(libraries, consumer);
}
@@ -5917,7 +5918,7 @@ void testBitGetFunc(SqlOperatorFixture f, String
functionName) {
f.checkString("SPACE(5)", " ", "VARCHAR NOT NULL");
f.checkNull("SPACE(cast(null as integer))");
};
- f0.forEachLibrary(list(SqlLibrary.MYSQL, SqlLibrary.SPARK), consumer);
+ f0.forEachLibrary(list(SqlLibrary.MYSQL, SqlLibrary.SPARK,
SqlLibrary.HIVE), consumer);
}
@Test void testStrcmpFunc() {
@@ -5938,7 +5939,7 @@ void testBitGetFunc(SqlOperatorFixture f, String
functionName) {
false);
final List<SqlLibrary> libraries =
list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL,
- SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT);
+ SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT,
SqlLibrary.HIVE);
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");
@@ -6281,7 +6282,8 @@ private static void checkIf(SqlOperatorFixture f) {
f.checkQuery("select regexp_replace('a b c', 'b', 'X')");
};
final List<SqlLibrary> libraries =
- list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL, SqlLibrary.ORACLE,
SqlLibrary.REDSHIFT);
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL, SqlLibrary.ORACLE,
+ SqlLibrary.REDSHIFT, SqlLibrary.HIVE);
f0.forEachLibrary(libraries, consumer);
// Tests to verify double-backslashes are ignored for indexing in other
dialects
@@ -7716,7 +7718,7 @@ void checkRegexpExtract(SqlOperatorFixture f0,
FunctionAlias functionAlias) {
f.checkNull("log(0)");
f.checkNull("log(-1)");
};
- f0.forEachLibrary(list(SqlLibrary.MYSQL, SqlLibrary.SPARK), consumer);
+ f0.forEachLibrary(list(SqlLibrary.MYSQL, SqlLibrary.SPARK,
SqlLibrary.HIVE), consumer);
}
/** Test case for
@@ -10986,7 +10988,7 @@ private void testSysDateFunc(Pair<String,
Hook.Closeable> pair) {
};
final List<SqlLibrary> libraries =
list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL,
- SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
+ SqlLibrary.REDSHIFT, SqlLibrary.SPARK, SqlLibrary.HIVE);
f0.forEachLibrary(libraries, consumer);
}
@@ -11018,7 +11020,7 @@ private void testSysDateFunc(Pair<String,
Hook.Closeable> pair) {
};
final List<SqlLibrary> libraries =
list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL,
- SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
+ SqlLibrary.REDSHIFT, SqlLibrary.SPARK, SqlLibrary.HIVE);
f0.forEachLibrary(libraries, consumer);
}
@@ -11802,7 +11804,7 @@ void assertSubFunReturns(boolean binary, String s, int
start,
};
final List<SqlLibrary> libraries =
list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL,
- SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
+ SqlLibrary.REDSHIFT, SqlLibrary.SPARK, SqlLibrary.HIVE);
f0.forEachLibrary(libraries, consumer);
}
@@ -11818,7 +11820,7 @@ void assertSubFunReturns(boolean binary, String s, int
start,
};
final List<SqlLibrary> libraries =
list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL,
- SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
+ SqlLibrary.REDSHIFT, SqlLibrary.SPARK, SqlLibrary.HIVE);
f0.forEachLibrary(libraries, consumer);
}
@@ -11842,7 +11844,7 @@ void assertSubFunReturns(boolean binary, String s, int
start,
"VARCHAR(5) NOT NULL");
};
final List<SqlLibrary> libraries =
- list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE);
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.HIVE);
f0.forEachLibrary(libraries, consumer);
}
@@ -11889,7 +11891,7 @@ void assertSubFunReturns(boolean binary, String s, int
start,
"VARCHAR(5) NOT NULL");
};
final List<SqlLibrary> libraries =
- list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE);
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.HIVE);
f0.forEachLibrary(libraries, consumer);
}
@@ -11925,7 +11927,7 @@ void assertSubFunReturns(boolean binary, String s, int
start,
@Test void testNvlFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlLibraryOperators.NVL, VmName.EXPAND);
- final SqlOperatorFixture f12 = f
+ SqlOperatorFixture f12 = f
.withLibrary(SqlLibrary.ORACLE)
.forOracle(SqlConformanceEnum.ORACLE_12);
f12.checkString("nvl('abc', 'de')", "abc", "VARCHAR(3) NOT NULL");
@@ -12005,6 +12007,7 @@ void checkNvl(SqlOperatorFixture f0, FunctionAlias
functionAlias) {
checkDecodeFunc(fixture().withLibrary(SqlLibrary.ORACLE));
checkDecodeFunc(fixture().withLibrary(SqlLibrary.REDSHIFT));
checkDecodeFunc(fixture().withLibrary(SqlLibrary.SPARK));
+ checkDecodeFunc(fixture().withLibrary(SqlLibrary.HIVE));
}
private static void checkDecodeFunc(SqlOperatorFixture f) {
@@ -14409,53 +14412,56 @@ void testTimestampDiff(boolean coercionEnabled) {
"No match found for function signature "
+ "DATE_ADD\\(<DATE>, <NUMERIC>\\)", false);
- final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.SPARK);
- f.checkScalar("date_add(date '2016-02-22', 2)",
- "2016-02-24",
- "DATE NOT NULL");
- f.checkScalar("date_add(date '2016-02-28', 2)",
- "2016-03-01",
- "DATE NOT NULL");
- f.checkScalar("date_add(date '2016-03-01', -2)",
- "2016-02-28",
- "DATE NOT NULL");
- f.checkScalar("date_add(date '2016-03-01', -2)",
- "2016-02-28",
- "DATE NOT NULL");
- f.checkScalar("date_add(date '2016-03-01', -2.0)",
- "2016-02-28",
- "DATE NOT NULL");
- f.checkScalar("date_add(date '2016-02-28', 2.0)",
- "2016-03-01",
- "DATE NOT NULL");
- f.checkScalar("date_add(date '2016-02-28', '2.0')",
- "2016-03-01",
- "DATE NOT NULL");
- f.checkScalar("date_add(timestamp '2016-02-22 13:00:01', '-2.0')",
- "2016-02-20",
- "DATE NOT NULL");
- f.checkScalar("date_add(timestamp '2016-02-22 13:00:01', -2)",
- "2016-02-20",
- "DATE NOT NULL");
- f.checkScalar("date_add(timestamp '2016-02-22 13:00:01', -2.0)",
- "2016-02-20",
- "DATE NOT NULL");
- f.checkScalar("date_add(date '0001-01-01', '-2.0')",
- "0000-12-30",
- "DATE NOT NULL");
- f.checkScalar("date_add(date '0001-01-01', '-367')",
- "000/-12-31",
- "DATE NOT NULL");
- f.checkScalar("date_add(date '0001-01-01', '-3')",
- "0000-12-29",
- "DATE NOT NULL");
- f.checkNull("date_add(CAST(NULL AS DATE), 5)");
- f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
- f.checkNull("date_add(CAST(NULL AS DATE), CAST(NULL AS INTEGER))");
- f.checkFails("^date_add(time '13:00:01', -2)^",
INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR,
- false);
- f.checkFails("^date_add(1, -2)^", INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR,
- false);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkScalar("date_add(date '2016-02-22', 2)",
+ "2016-02-24",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '2016-02-28', 2)",
+ "2016-03-01",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '2016-03-01', -2)",
+ "2016-02-28",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '2016-03-01', -2)",
+ "2016-02-28",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '2016-03-01', -2.0)",
+ "2016-02-28",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '2016-02-28', 2.0)",
+ "2016-03-01",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '2016-02-28', '2.0')",
+ "2016-03-01",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(timestamp '2016-02-22 13:00:01', '-2.0')",
+ "2016-02-20",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(timestamp '2016-02-22 13:00:01', -2)",
+ "2016-02-20",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(timestamp '2016-02-22 13:00:01', -2.0)",
+ "2016-02-20",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '0001-01-01', '-2.0')",
+ "0000-12-30",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '0001-01-01', '-367')",
+ "000/-12-31",
+ "DATE NOT NULL");
+ f.checkScalar("date_add(date '0001-01-01', '-3')",
+ "0000-12-29",
+ "DATE NOT NULL");
+ f.checkNull("date_add(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_add(date '2016-02-22', CAST(NULL AS INTEGER))");
+ f.checkNull("date_add(CAST(NULL AS DATE), CAST(NULL AS INTEGER))");
+ f.checkFails("^date_add(time '13:00:01', -2)^",
INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR,
+ false);
+ f.checkFails("^date_add(1, -2)^",
INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR,
+ false);
+ };
+
+ f0.forEachLibrary(list(SqlLibrary.SPARK, SqlLibrary.HIVE), consumer);
}
/** Test case for
@@ -14465,31 +14471,33 @@ void testTimestampDiff(boolean coercionEnabled) {
@Test void testDateSubSpark() {
final SqlOperatorFixture f0 = fixture()
.setFor(SqlLibraryOperators.DATE_SUB_SPARK);
+
f0.checkFails("^date_sub(date '2008-12-25', "
+ "5)^",
"No match found for function signature "
+ "DATE_SUB\\(<DATE>, <NUMERIC>\\)", false);
-
- final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.SPARK);
- f.checkScalar("date_sub(date '2016-02-22', 2)",
- "2016-02-20",
- "DATE NOT NULL");
- f.checkScalar("date_sub(date '2016-03-01', 2)",
- "2016-02-28",
- "DATE NOT NULL");
- f.checkScalar("date_sub(timestamp '2016-02-22 13:00:01', '-2.0')",
- "2016-02-24",
- "DATE NOT NULL");
- f.checkScalar("date_sub(timestamp '2016-02-22 13:00:01', -2)",
- "2016-02-24",
- "DATE NOT NULL");
- f.checkNull("date_sub(CAST(NULL AS DATE), 5)");
- f.checkNull("date_sub(date '2016-02-22', CAST(NULL AS INTEGER))");
- f.checkNull("date_sub(CAST(NULL AS DATE), CAST(NULL AS INTEGER))");
- f.checkFails("^date_sub(time '13:00:01', -2)^",
INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR,
- false);
- f.checkFails("^date_sub(1, -2)^", INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR,
- false);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkScalar("date_sub(date '2016-02-22', 2)",
+ "2016-02-20",
+ "DATE NOT NULL");
+ f.checkScalar("date_sub(date '2016-03-01', 2)",
+ "2016-02-28",
+ "DATE NOT NULL");
+ f.checkScalar("date_sub(timestamp '2016-02-22 13:00:01', '-2.0')",
+ "2016-02-24",
+ "DATE NOT NULL");
+ f.checkScalar("date_sub(timestamp '2016-02-22 13:00:01', -2)",
+ "2016-02-24",
+ "DATE NOT NULL");
+ f.checkNull("date_sub(CAST(NULL AS DATE), 5)");
+ f.checkNull("date_sub(date '2016-02-22', CAST(NULL AS INTEGER))");
+ f.checkNull("date_sub(CAST(NULL AS DATE), CAST(NULL AS INTEGER))");
+ f.checkFails("^date_sub(time '13:00:01', -2)^",
INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR,
+ false);
+ f.checkFails("^date_sub(1, -2)^",
INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR,
+ false);
+ };
+ f0.forEachLibrary(list(SqlLibrary.SPARK, SqlLibrary.HIVE), consumer);
}
/** Test case for
