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
commit 326b993fecd1ef93f42fc5d0581dc086abc2506c Author: Tanner Clary <[email protected]> AuthorDate: Mon Nov 21 17:23:51 2022 +0000 [CALCITE-5385] Add BigQuery as a supported library for some functions that are already implemented for other libraries The functions are: MD5, SHA1, GREATEST, LEAST, COSH, SINH, TANH, FROM_BASE64, LEFT, LTRIM, REPEAT, REVERSE, RIGHT, RTRIM, SOUNDEX, TRANSLATE. Close apache/calcite#2978 --- .../calcite/sql/fun/SqlLibraryOperators.java | 32 +- site/_docs/reference.md | 32 +- .../org/apache/calcite/test/SqlOperatorTest.java | 439 ++++++++++++--------- 3 files changed, 285 insertions(+), 218 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 8744b2d550..7566f2331a 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 @@ -189,7 +189,7 @@ public abstract class SqlLibraryOperators { OperandTypes.SAME_SAME); /** The "LTRIM(string)" function. */ - @LibraryOperator(libraries = {ORACLE}) + @LibraryOperator(libraries = {BIG_QUERY, ORACLE}) public static final SqlFunction LTRIM = SqlBasicFunction.create(SqlKind.LTRIM, ReturnTypes.ARG0.andThen(SqlTypeTransforms.TO_NULLABLE) @@ -198,7 +198,7 @@ public abstract class SqlLibraryOperators { .withFunctionType(SqlFunctionCategory.STRING); /** The "RTRIM(string)" function. */ - @LibraryOperator(libraries = {ORACLE}) + @LibraryOperator(libraries = {BIG_QUERY, ORACLE}) public static final SqlFunction RTRIM = SqlBasicFunction.create(SqlKind.RTRIM, ReturnTypes.ARG0.andThen(SqlTypeTransforms.TO_NULLABLE) @@ -246,14 +246,14 @@ public abstract class SqlLibraryOperators { SUBSTR.withKind(SqlKind.SUBSTR_POSTGRESQL); /** The "GREATEST(value, value)" function. */ - @LibraryOperator(libraries = {ORACLE}) + @LibraryOperator(libraries = {BIG_QUERY, ORACLE}) 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 = {ORACLE}) + @LibraryOperator(libraries = {BIG_QUERY, ORACLE}) public static final SqlFunction LEAST = SqlBasicFunction.create(SqlKind.LEAST, ReturnTypes.LEAST_RESTRICTIVE.andThen(SqlTypeTransforms.TO_NULLABLE), @@ -268,7 +268,7 @@ public abstract class SqlLibraryOperators { * <p>It is not defined in the SQL standard, but occurs in Oracle and * PostgreSQL. */ - @LibraryOperator(libraries = {ORACLE, POSTGRESQL}) + @LibraryOperator(libraries = {BIG_QUERY, ORACLE, POSTGRESQL}) public static final SqlFunction TRANSLATE3 = new SqlTranslate3Function(); @LibraryOperator(libraries = {MYSQL}) @@ -462,20 +462,20 @@ public abstract class SqlLibraryOperators { ReturnTypes.VARCHAR_2000, OperandTypes.DATETIME, SqlFunctionCategory.TIMEDATE); - @LibraryOperator(libraries = {MYSQL, POSTGRESQL}) + @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL}) public static final SqlFunction LEFT = SqlBasicFunction.create("LEFT", ReturnTypes.ARG0_NULLABLE_VARYING, OperandTypes.CBSTRING_INTEGER, SqlFunctionCategory.STRING); - @LibraryOperator(libraries = {MYSQL, POSTGRESQL}) + @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL}) public static final SqlFunction REPEAT = SqlBasicFunction.create("REPEAT", ReturnTypes.ARG0_NULLABLE_VARYING, OperandTypes.STRING_INTEGER, SqlFunctionCategory.STRING); - @LibraryOperator(libraries = {MYSQL, POSTGRESQL}) + @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL}) public static final SqlFunction RIGHT = SqlBasicFunction.create("RIGHT", ReturnTypes.ARG0_NULLABLE_VARYING, OperandTypes.CBSTRING_INTEGER, SqlFunctionCategory.STRING); @@ -494,7 +494,7 @@ public abstract class SqlLibraryOperators { OperandTypes.STRING_STRING, SqlFunctionCategory.STRING); - @LibraryOperator(libraries = {MYSQL, POSTGRESQL, ORACLE}) + @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL, ORACLE}) public static final SqlFunction SOUNDEX = SqlBasicFunction.create("SOUNDEX", ReturnTypes.VARCHAR_4_NULLABLE, @@ -574,14 +574,14 @@ public abstract class SqlLibraryOperators { ReturnTypes.LEAST_RESTRICTIVE, OperandTypes.AT_LEAST_ONE_SAME_VARIADIC); - @LibraryOperator(libraries = {MYSQL}) + @LibraryOperator(libraries = {BIG_QUERY, MYSQL}) public static final SqlFunction REVERSE = SqlBasicFunction.create(SqlKind.REVERSE, ReturnTypes.ARG0_NULLABLE_VARYING, OperandTypes.CHARACTER) .withFunctionType(SqlFunctionCategory.STRING); - @LibraryOperator(libraries = {MYSQL}) + @LibraryOperator(libraries = {BIG_QUERY, MYSQL}) public static final SqlFunction FROM_BASE64 = SqlBasicFunction.create("FROM_BASE64", ReturnTypes.explicit(SqlTypeName.VARBINARY) @@ -723,28 +723,28 @@ public abstract class SqlLibraryOperators { OperandTypes.INTEGER, SqlFunctionCategory.STRING); - @LibraryOperator(libraries = {ORACLE}) + @LibraryOperator(libraries = {BIG_QUERY, ORACLE}) public static final SqlFunction TANH = SqlBasicFunction.create("TANH", ReturnTypes.DOUBLE_NULLABLE, OperandTypes.NUMERIC, SqlFunctionCategory.NUMERIC); - @LibraryOperator(libraries = {ORACLE}) + @LibraryOperator(libraries = {BIG_QUERY, ORACLE}) public static final SqlFunction COSH = SqlBasicFunction.create("COSH", ReturnTypes.DOUBLE_NULLABLE, OperandTypes.NUMERIC, SqlFunctionCategory.NUMERIC); - @LibraryOperator(libraries = {ORACLE}) + @LibraryOperator(libraries = {BIG_QUERY, ORACLE}) public static final SqlFunction SINH = SqlBasicFunction.create("SINH", ReturnTypes.DOUBLE_NULLABLE, OperandTypes.NUMERIC, SqlFunctionCategory.NUMERIC); - @LibraryOperator(libraries = {MYSQL, POSTGRESQL}) + @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL}) public static final SqlFunction MD5 = SqlBasicFunction.create("MD5", ReturnTypes.explicit(SqlTypeName.VARCHAR) @@ -752,7 +752,7 @@ public abstract class SqlLibraryOperators { OperandTypes.STRING.or(OperandTypes.BINARY), SqlFunctionCategory.STRING); - @LibraryOperator(libraries = {MYSQL, POSTGRESQL}) + @LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL}) public static final SqlFunction SHA1 = SqlBasicFunction.create("SHA1", ReturnTypes.explicit(SqlTypeName.VARCHAR) diff --git a/site/_docs/reference.md b/site/_docs/reference.md index a6817e0c5e..7dd8172c6f 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -2591,7 +2591,7 @@ semantics. | b | ARRAY_REVERSE(array) | Reverses elements of *array* | m s | CHAR(integer) | Returns the character whose ASCII code is *integer* % 256, or null if *integer* < 0 | o p | CHR(integer) | Returns the character whose UTF-8 code is *integer* -| o | COSH(numeric) | Returns the hyperbolic cosine of *numeric* +| b o | COSH(numeric) | Returns the hyperbolic cosine of *numeric* | o | CONCAT(string, string) | Concatenates two strings | b m p | CONCAT(string [, string ]*) | Concatenates two or more strings | m | COMPRESS(string) | Compresses a string using zlib compression and returns the result as a binary string. @@ -2609,7 +2609,7 @@ semantics. | 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 | 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. | m | EXTRACTVALUE(xml, xpathExpr)) | Returns the text of the first text node which is a child of the element or elements matched by the XPath expression. -| o | GREATEST(expr [, expr ]*) | Returns the greatest of the expressions +| b o | GREATEST(expr [, expr ]*) | Returns the greatest of the expressions | b h s | IF(condition, value1, value2) | Returns *value1* if *condition* is TRUE, *value2* otherwise | 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`) @@ -2620,28 +2620,28 @@ semantics. | m | JSON_KEYS(jsonValue [, path ]) | Returns a string indicating the keys of a JSON *jsonValue* | m | JSON_REMOVE(jsonValue, path[, path]) | Removes data from *jsonValue* using a series of *path* expressions and returns the result | m | JSON_STORAGE_SIZE(jsonValue) | Returns the number of bytes used to store the binary representation of *jsonValue* -| o | LEAST(expr [, expr ]* ) | Returns the least of the expressions -| m p | LEFT(string, length) | Returns the leftmost *length* characters from the *string* +| 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* | m | TO_BASE64(string) | Converts the *string* to base-64 encoded form and returns a encoded string -| m | FROM_BASE64(string) | Returns the decoded result of a base-64 *string* as a string -| o | LTRIM(string) | Returns *string* with all blanks removed from the start -| m p | MD5(string) | Calculates an MD5 128-bit checksum of *string* and returns it as a hex string +| b m | FROM_BASE64(string) | Returns the decoded result of a base-64 *string* as a string +| b o | LTRIM(string) | Returns *string* with all blanks removed from the start +| b m p | 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* | 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* means which occurrence of a match to search for (if omitted, the default is 1), *matchType* specifies how to perform matching -| 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 -| m | REVERSE(string) | Returns *string* with the order of the characters reversed -| m p | RIGHT(string, length) | Returns the rightmost *length* characters from the *string* +| 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 | 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* | 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) -| o | RTRIM(string) | Returns *string* with all blanks removed from the end -| m p | SHA1(string) | Calculates a SHA-1 hash value of *string* and returns it as a hex string -| o | SINH(numeric) | Returns the hyperbolic sine of *numeric* -| 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 o | RTRIM(string) | Returns *string* with all blanks removed from the end +| b m p | SHA1(string) | Calculates a SHA-1 hash value of *string* and returns it as a hex string +| b o | 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 | m | SPACE(integer) | Returns a string of *integer* spaces; returns an empty string if *integer* is less than 1 | 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 | 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 -| o | TANH(numeric) | Returns the hyperbolic tangent of *numeric* +| b o | TANH(numeric) | Returns the hyperbolic tangent of *numeric* | 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 @@ -2649,7 +2649,7 @@ semantics. | b | TIME_TRUNC(time, timeUnit) | Truncates a *time* value to the granularity of *timeUnit*. The *time* value is always rounded to the beginning of timeUnit, which can be one of the following: MILLISECOND, SECOND, MINUTE, HOUR. | o p | TO_DATE(string, format) | Converts *string* to a date using the format *format* | o p | TO_TIMESTAMP(string, format) | Converts *string* to a timestamp using the format *format* -| o p | TRANSLATE(expr, fromString, toString) | Returns *expr* with all occurrences of each character in *fromString* replaced by its corresponding character in *toString*. Characters in *expr* that are not in *fromString* are not replaced +| 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 | 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 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 d1275934f3..af13da2828 100644 --- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java +++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java @@ -72,6 +72,7 @@ import org.apache.calcite.util.Util; import org.apache.calcite.util.trace.CalciteTrace; import com.google.common.base.Throwables; +import com.google.common.collect.ImmutableList; import org.checkerframework.checker.nullness.qual.Nullable; import org.junit.jupiter.api.Disabled; @@ -3501,22 +3502,31 @@ public class SqlOperatorTest { } @Test void testTranslate3Func() { - final SqlOperatorFixture f = fixture() - .setFor(SqlLibraryOperators.TRANSLATE3) - .withLibrary(SqlLibrary.ORACLE); - f.checkString("translate('aabbcc', 'ab', '+-')", - "++--cc", "VARCHAR(6) NOT NULL"); - f.checkString("translate('aabbcc', 'ab', 'ba')", - "bbaacc", "VARCHAR(6) NOT NULL"); - f.checkString("translate('aabbcc', 'ab', '')", - "cc", "VARCHAR(6) NOT NULL"); - f.checkString("translate('aabbcc', '', '+-')", - "aabbcc", "VARCHAR(6) NOT NULL"); - f.checkString("translate(cast('aabbcc' as varchar(10)), 'ab', '+-')", - "++--cc", "VARCHAR(10) NOT NULL"); - f.checkNull("translate(cast(null as varchar(7)), 'ab', '+-')"); - f.checkNull("translate('aabbcc', cast(null as varchar(2)), '+-')"); - f.checkNull("translate('aabbcc', 'ab', cast(null as varchar(2)))"); + final SqlOperatorFixture f0 = fixture() + .setFor(SqlLibraryOperators.TRANSLATE3); + f0.checkFails("^translate('aabbcc', 'ab', '+-')^", + "No match found for function signature " + + "TRANSLATE3\\(<CHARACTER>, <CHARACTER>, <CHARACTER>\\)", + false); + final Consumer<SqlOperatorFixture> consumer = f -> { + f.checkString("translate('aabbcc', 'ab', '+-')", + "++--cc", "VARCHAR(6) NOT NULL"); + f.checkString("translate('aabbcc', 'ab', 'ba')", + "bbaacc", "VARCHAR(6) NOT NULL"); + f.checkString("translate('aabbcc', 'ab', '')", + "cc", "VARCHAR(6) NOT NULL"); + f.checkString("translate('aabbcc', '', '+-')", + "aabbcc", "VARCHAR(6) NOT NULL"); + f.checkString("translate(cast('aabbcc' as varchar(10)), 'ab', '+-')", + "++--cc", "VARCHAR(10) NOT NULL"); + f.checkNull("translate(cast(null as varchar(7)), 'ab', '+-')"); + f.checkNull("translate('aabbcc', cast(null as varchar(2)), '+-')"); + f.checkNull("translate('aabbcc', 'ab', cast(null as varchar(2)))"); + }; + final List<SqlLibrary> libraries = + ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, + SqlLibrary.POSTGRESQL); + f0.forEachLibrary(libraries, consumer); } @Test void testOverlayFunc() { @@ -3695,74 +3705,97 @@ public class SqlOperatorTest { } @Test void testFromBase64() { - final SqlOperatorFixture f = fixture() - .setFor(SqlLibraryOperators.FROM_BASE64) - .withLibrary(SqlLibrary.MYSQL); - f.checkString("from_base64('VGhpcyBpcyBhIHRlc3QgU3RyaW5nLg==')", - "546869732069732061207465737420537472696e672e", - "VARBINARY NOT NULL"); - f.checkString("from_base64('VGhpcyBpcyBhIHRlc\t3QgU3RyaW5nLg==')", - "546869732069732061207465737420537472696e672e", - "VARBINARY NOT NULL"); - f.checkString("from_base64('VGhpcyBpcyBhIHRlc\t3QgU3\nRyaW5nLg==')", - "546869732069732061207465737420537472696e672e", - "VARBINARY NOT NULL"); - f.checkString("from_base64('VGhpcyB pcyBhIHRlc3Qg\tU3Ry\naW5nLg==')", - "546869732069732061207465737420537472696e672e", - "VARBINARY NOT NULL"); - f.checkNull("from_base64('-1')"); - f.checkNull("from_base64('-100')"); + final SqlOperatorFixture f0 = fixture() + .setFor(SqlLibraryOperators.FROM_BASE64); + f0.checkFails("^from_base64('2fjoeiwjfoj==')^", + "No match found for function signature FROM_BASE64\\(<CHARACTER>\\)", + false); + final Consumer<SqlOperatorFixture> consumer = f -> { + f.checkString("from_base64('VGhpcyBpcyBhIHRlc3QgU3RyaW5nLg==')", + "546869732069732061207465737420537472696e672e", + "VARBINARY NOT NULL"); + f.checkString("from_base64('VGhpcyBpcyBhIHRlc\t3QgU3RyaW5nLg==')", + "546869732069732061207465737420537472696e672e", + "VARBINARY NOT NULL"); + f.checkString("from_base64('VGhpcyBpcyBhIHRlc\t3QgU3\nRyaW5nLg==')", + "546869732069732061207465737420537472696e672e", + "VARBINARY NOT NULL"); + f.checkString("from_base64('VGhpcyB pcyBhIHRlc3Qg\tU3Ry\naW5nLg==')", + "546869732069732061207465737420537472696e672e", + "VARBINARY NOT NULL"); + f.checkNull("from_base64('-1')"); + f.checkNull("from_base64('-100')"); + }; + f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL), consumer); } @Test void testMd5() { - final SqlOperatorFixture f = fixture() - .setFor(SqlLibraryOperators.MD5) - .withLibrary(SqlLibrary.MYSQL); - f.checkString("md5(x'')", - "d41d8cd98f00b204e9800998ecf8427e", - "VARCHAR NOT NULL"); - f.checkString("md5('')", - "d41d8cd98f00b204e9800998ecf8427e", - "VARCHAR NOT NULL"); - f.checkString("md5('ABC')", - "902fbdd2b1df0c4f70b4a5d23525e932", - "VARCHAR NOT NULL"); - f.checkString("md5(x'414243')", - "902fbdd2b1df0c4f70b4a5d23525e932", - "VARCHAR NOT NULL"); + final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.MD5); + f0.checkFails("^md5(x'')^", + "No match found for function signature MD5\\(<BINARY>\\)", + false); + final List<SqlLibrary> libraries = + ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL, + SqlLibrary.POSTGRESQL); + final Consumer<SqlOperatorFixture> consumer = f -> { + f.checkString("md5(x'')", + "d41d8cd98f00b204e9800998ecf8427e", + "VARCHAR NOT NULL"); + f.checkString("md5('')", + "d41d8cd98f00b204e9800998ecf8427e", + "VARCHAR NOT NULL"); + f.checkString("md5('ABC')", + "902fbdd2b1df0c4f70b4a5d23525e932", + "VARCHAR NOT NULL"); + f.checkString("md5(x'414243')", + "902fbdd2b1df0c4f70b4a5d23525e932", + "VARCHAR NOT NULL"); + }; + f0.forEachLibrary(libraries, consumer); } @Test void testSha1() { - final SqlOperatorFixture f = fixture() - .setFor(SqlLibraryOperators.SHA1) - .withLibrary(SqlLibrary.MYSQL); - f.checkString("sha1(x'')", - "da39a3ee5e6b4b0d3255bfef95601890afd80709", - "VARCHAR NOT NULL"); - f.checkString("sha1('')", - "da39a3ee5e6b4b0d3255bfef95601890afd80709", - "VARCHAR NOT NULL"); - f.checkString("sha1('ABC')", - "3c01bdbb26f358bab27f267924aa2c9a03fcfdb8", - "VARCHAR NOT NULL"); - f.checkString("sha1(x'414243')", - "3c01bdbb26f358bab27f267924aa2c9a03fcfdb8", - "VARCHAR NOT NULL"); + final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.SHA1); + f0.checkFails("^sha1(x'')^", + "No match found for function signature SHA1\\(<BINARY>\\)", + false); + final List<SqlLibrary> libraries = + ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL, + SqlLibrary.POSTGRESQL); + final Consumer<SqlOperatorFixture> consumer = f -> { + f.checkString("sha1(x'')", + "da39a3ee5e6b4b0d3255bfef95601890afd80709", + "VARCHAR NOT NULL"); + f.checkString("sha1('')", + "da39a3ee5e6b4b0d3255bfef95601890afd80709", + "VARCHAR NOT NULL"); + f.checkString("sha1('ABC')", + "3c01bdbb26f358bab27f267924aa2c9a03fcfdb8", + "VARCHAR NOT NULL"); + f.checkString("sha1(x'414243')", + "3c01bdbb26f358bab27f267924aa2c9a03fcfdb8", + "VARCHAR NOT NULL"); + }; + f0.forEachLibrary(libraries, consumer); } @Test void testRepeatFunc() { - final SqlOperatorFixture f = fixture() - .setFor(SqlLibraryOperators.REPEAT) - .withLibrary(SqlLibrary.MYSQL); - f.checkString("REPEAT('a', -100)", "", "VARCHAR(1) NOT NULL"); - f.checkString("REPEAT('a', -1)", "", "VARCHAR(1) NOT NULL"); - f.checkString("REPEAT('a', 0)", "", "VARCHAR(1) NOT NULL"); - f.checkString("REPEAT('a', 2)", "aa", "VARCHAR(1) NOT NULL"); - f.checkString("REPEAT('abc', 3)", "abcabcabc", "VARCHAR(3) NOT NULL"); - f.checkNull("REPEAT(cast(null as varchar(1)), -1)"); - f.checkNull("REPEAT(cast(null as varchar(1)), 2)"); - f.checkNull("REPEAT('abc', cast(null as integer))"); - f.checkNull("REPEAT(cast(null as varchar(1)), cast(null as integer))"); + final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.REPEAT); + f0.checkFails("^repeat('a', -100)^", + "No match found for function signature REPEAT\\(<CHARACTER>, <NUMERIC>\\)", + false); + final Consumer<SqlOperatorFixture> consumer = f -> { + f.checkString("REPEAT('a', -100)", "", "VARCHAR(1) NOT NULL"); + f.checkString("REPEAT('a', -1)", "", "VARCHAR(1) NOT NULL"); + f.checkString("REPEAT('a', 0)", "", "VARCHAR(1) NOT NULL"); + f.checkString("REPEAT('a', 2)", "aa", "VARCHAR(1) NOT NULL"); + f.checkString("REPEAT('abc', 3)", "abcabcabc", "VARCHAR(3) NOT NULL"); + f.checkNull("REPEAT(cast(null as varchar(1)), -1)"); + f.checkNull("REPEAT(cast(null as varchar(1)), 2)"); + 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); } @Test void testSpaceFunc() { @@ -3789,19 +3822,26 @@ public class SqlOperatorTest { } @Test void testSoundexFunc() { - final SqlOperatorFixture f = fixture() - .setFor(SqlLibraryOperators.SOUNDEX) - .withLibrary(SqlLibrary.ORACLE); - f.checkString("SOUNDEX('TECH ON THE NET')", "T253", "VARCHAR(4) NOT NULL"); - f.checkString("SOUNDEX('Miller')", "M460", "VARCHAR(4) NOT NULL"); - f.checkString("SOUNDEX('miler')", "M460", "VARCHAR(4) NOT NULL"); - f.checkString("SOUNDEX('myller')", "M460", "VARCHAR(4) NOT NULL"); - f.checkString("SOUNDEX('muller')", "M460", "VARCHAR(4) NOT NULL"); - f.checkString("SOUNDEX('m')", "M000", "VARCHAR(4) NOT NULL"); - f.checkString("SOUNDEX('mu')", "M000", "VARCHAR(4) NOT NULL"); - f.checkString("SOUNDEX('mile')", "M400", "VARCHAR(4) NOT NULL"); - f.checkNull("SOUNDEX(cast(null as varchar(1)))"); - f.checkFails("SOUNDEX(_UTF8'\u5B57\u5B57')", "The character is not mapped.*", true); + final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.SOUNDEX); + f0.checkFails("^soundex('tech on the net')^", + "No match found for function signature SOUNDEX\\(<CHARACTER>\\)", + false); + final List<SqlLibrary> libraries = + ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL, + SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL); + 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"); + f.checkString("SOUNDEX('miler')", "M460", "VARCHAR(4) NOT NULL"); + f.checkString("SOUNDEX('myller')", "M460", "VARCHAR(4) NOT NULL"); + f.checkString("SOUNDEX('muller')", "M460", "VARCHAR(4) NOT NULL"); + f.checkString("SOUNDEX('m')", "M000", "VARCHAR(4) NOT NULL"); + f.checkString("SOUNDEX('mu')", "M000", "VARCHAR(4) NOT NULL"); + f.checkString("SOUNDEX('mile')", "M400", "VARCHAR(4) NOT NULL"); + f.checkNull("SOUNDEX(cast(null as varchar(1)))"); + f.checkFails("SOUNDEX(_UTF8'\u5B57\u5B57')", "The character is not mapped.*", true); + }; + f0.forEachLibrary(libraries, consumer); } @Test void testDifferenceFunc() { @@ -3821,18 +3861,22 @@ public class SqlOperatorTest { } @Test void testReverseFunc() { - final SqlOperatorFixture f = fixture() - .setFor(SqlLibraryOperators.REVERSE) - .withLibrary(SqlLibrary.MYSQL); - f.checkString("reverse('')", "", "VARCHAR(0) NOT NULL"); - f.checkString("reverse('123')", "321", "VARCHAR(3) NOT NULL"); - f.checkString("reverse('abc')", "cba", "VARCHAR(3) NOT NULL"); - f.checkString("reverse('ABC')", "CBA", "VARCHAR(3) NOT NULL"); - f.checkString("reverse('Hello World')", "dlroW olleH", - "VARCHAR(11) NOT NULL"); - f.checkString("reverse(_UTF8'\u4F60\u597D')", "\u597D\u4F60", - "VARCHAR(2) NOT NULL"); - f.checkNull("reverse(cast(null as varchar(1)))"); + final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.REVERSE); + f0.checkFails("^reverse('abc')^", + "No match found for function signature REVERSE\\(<CHARACTER>\\)", + false); + final Consumer<SqlOperatorFixture> consumer = f -> { + f.checkString("reverse('')", "", "VARCHAR(0) NOT NULL"); + f.checkString("reverse('123')", "321", "VARCHAR(3) NOT NULL"); + f.checkString("reverse('abc')", "cba", "VARCHAR(3) NOT NULL"); + f.checkString("reverse('ABC')", "CBA", "VARCHAR(3) NOT NULL"); + f.checkString("reverse('Hello World')", "dlroW olleH", + "VARCHAR(11) NOT NULL"); + f.checkString("reverse(_UTF8'\u4F60\u597D')", "\u597D\u4F60", + "VARCHAR(2) NOT NULL"); + f.checkNull("reverse(cast(null as varchar(1)))"); + }; + f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL), consumer); } @Test void testIfFunc() { @@ -5148,22 +5192,23 @@ public class SqlOperatorTest { } @Test void testCoshFunc() { - final SqlOperatorFixture f0 = fixture(); - final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.ORACLE); - f.checkType("cosh(1)", "DOUBLE NOT NULL"); - f.checkType("cosh(cast(1 as float))", "DOUBLE NOT NULL"); - f.checkType("cosh(case when false then 1 else null end)", "DOUBLE"); - f0.enableTypeCoercion(false) - .checkFails("^cosh('abc')^", - "No match found for function signature COSH\\(<CHARACTER>\\)", - false); - f.checkType("cosh('abc')", "DOUBLE NOT NULL"); - f.checkScalarApprox("cosh(1)", "DOUBLE NOT NULL", - isWithin(1.5430d, 0.0001d)); - f.checkScalarApprox("cosh(cast(1 as decimal(1, 0)))", "DOUBLE NOT NULL", - isWithin(1.5430d, 0.0001d)); - f.checkNull("cosh(cast(null as integer))"); - f.checkNull("cosh(cast(null as double))"); + final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.COSH); + f0.checkFails("^cosh(1)^", + "No match found for function signature COSH\\(<NUMERIC>\\)", + false); + final Consumer<SqlOperatorFixture> consumer = f -> { + f.checkType("cosh(1)", "DOUBLE NOT NULL"); + f.checkType("cosh(cast(1 as float))", "DOUBLE NOT NULL"); + f.checkType("cosh(case when false then 1 else null end)", "DOUBLE"); + f.checkType("cosh('abc')", "DOUBLE NOT NULL"); + f.checkScalarApprox("cosh(1)", "DOUBLE NOT NULL", + isWithin(1.5430d, 0.0001d)); + f.checkScalarApprox("cosh(cast(1 as decimal(1, 0)))", "DOUBLE NOT NULL", + isWithin(1.5430d, 0.0001d)); + f.checkNull("cosh(cast(null as integer))"); + f.checkNull("cosh(cast(null as double))"); + }; + f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE), consumer); } @Test void testCotFunc() { @@ -5317,22 +5362,23 @@ public class SqlOperatorTest { } @Test void testSinhFunc() { - final SqlOperatorFixture f0 = fixture(); - final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.ORACLE); - f.checkType("sinh(1)", "DOUBLE NOT NULL"); - f.checkType("sinh(cast(1 as float))", "DOUBLE NOT NULL"); - f.checkType("sinh(case when false then 1 else null end)", "DOUBLE"); - f0.enableTypeCoercion(false) - .checkFails("^sinh('abc')^", - "No match found for function signature SINH\\(<CHARACTER>\\)", - false); - f.checkType("sinh('abc')", "DOUBLE NOT NULL"); - f.checkScalarApprox("sinh(1)", "DOUBLE NOT NULL", - isWithin(1.1752d, 0.0001d)); - f.checkScalarApprox("sinh(cast(1 as decimal(1, 0)))", "DOUBLE NOT NULL", - isWithin(1.1752d, 0.0001d)); - f.checkNull("sinh(cast(null as integer))"); - f.checkNull("sinh(cast(null as double))"); + final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.SINH); + f0.checkFails("^sinh(1)^", + "No match found for function signature SINH\\(<NUMERIC>\\)", + false); + final Consumer<SqlOperatorFixture> consumer = f -> { + f.checkType("sinh(1)", "DOUBLE NOT NULL"); + f.checkType("sinh(cast(1 as float))", "DOUBLE NOT NULL"); + f.checkType("sinh(case when false then 1 else null end)", "DOUBLE"); + f.checkType("sinh('abc')", "DOUBLE NOT NULL"); + f.checkScalarApprox("sinh(1)", "DOUBLE NOT NULL", + isWithin(1.1752d, 0.0001d)); + f.checkScalarApprox("sinh(cast(1 as decimal(1, 0)))", "DOUBLE NOT NULL", + isWithin(1.1752d, 0.0001d)); + f.checkNull("sinh(cast(null as integer))"); + f.checkNull("sinh(cast(null as double))"); + }; + f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE), consumer); } @Test void testTanFunc() { @@ -5357,22 +5403,23 @@ public class SqlOperatorTest { } @Test void testTanhFunc() { - SqlOperatorFixture f0 = fixture(); - final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.ORACLE); - f.checkType("tanh(1)", "DOUBLE NOT NULL"); - f.checkType("tanh(cast(1 as float))", "DOUBLE NOT NULL"); - f.checkType("tanh(case when false then 1 else null end)", "DOUBLE"); - f0.enableTypeCoercion(false) - .checkFails("^tanh('abc')^", - "No match found for function signature TANH\\(<CHARACTER>\\)", - false); - f.checkType("tanh('abc')", "DOUBLE NOT NULL"); - f.checkScalarApprox("tanh(1)", "DOUBLE NOT NULL", - isWithin(0.7615d, 0.0001d)); - f.checkScalarApprox("tanh(cast(1 as decimal(1, 0)))", "DOUBLE NOT NULL", - isWithin(0.7615d, 0.0001d)); - f.checkNull("tanh(cast(null as integer))"); - f.checkNull("tanh(cast(null as double))"); + SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.TANH); + f0.checkFails("^tanh(1)^", + "No match found for function signature TANH\\(<NUMERIC>\\)", + false); + final Consumer<SqlOperatorFixture> consumer = f -> { + f.checkType("tanh(1)", "DOUBLE NOT NULL"); + f.checkType("tanh(cast(1 as float))", "DOUBLE NOT NULL"); + f.checkType("tanh(case when false then 1 else null end)", "DOUBLE"); + f.checkType("tanh('abc')", "DOUBLE NOT NULL"); + f.checkScalarApprox("tanh(1)", "DOUBLE NOT NULL", + isWithin(0.7615d, 0.0001d)); + f.checkScalarApprox("tanh(cast(1 as decimal(1, 0)))", "DOUBLE NOT NULL", + isWithin(0.7615d, 0.0001d)); + f.checkNull("tanh(cast(null as integer))"); + f.checkNull("tanh(cast(null as double))"); + }; + f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE), consumer); } @Test void testTruncateFunc() { @@ -6124,53 +6171,73 @@ public class SqlOperatorTest { } @Test void testRtrimFunc() { - final SqlOperatorFixture f = fixture() - .setFor(SqlLibraryOperators.RTRIM, VmName.EXPAND) - .withLibrary(SqlLibrary.ORACLE); - f.checkString("rtrim(' aAa ')", " aAa", "VARCHAR(6) NOT NULL"); - f.checkNull("rtrim(CAST(NULL AS VARCHAR(6)))"); + final SqlOperatorFixture f0 = fixture() + .setFor(SqlLibraryOperators.RTRIM, VmName.EXPAND); + f0.checkFails("^rtrim(' aaa')^", + "No match found for function signature RTRIM\\(<CHARACTER>\\)", + false); + final Consumer<SqlOperatorFixture> consumer = f -> { + 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); } @Test void testLtrimFunc() { - final SqlOperatorFixture f = fixture() - .setFor(SqlLibraryOperators.LTRIM, VmName.EXPAND) - .withLibrary(SqlLibrary.ORACLE); - f.checkString("ltrim(' aAa ')", "aAa ", "VARCHAR(6) NOT NULL"); - f.checkNull("ltrim(CAST(NULL AS VARCHAR(6)))"); + final SqlOperatorFixture f0 = fixture() + .setFor(SqlLibraryOperators.LTRIM, VmName.EXPAND); + f0.checkFails("^ltrim(' aa')^", + "No match found for function signature LTRIM\\(<CHARACTER>\\)", + false); + final Consumer<SqlOperatorFixture> consumer = f -> { + 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); } @Test void testGreatestFunc() { - final SqlOperatorFixture f = fixture() - .setFor(SqlLibraryOperators.GREATEST, VmName.EXPAND) - .withLibrary(SqlLibrary.ORACLE); - f.checkString("greatest('on', 'earth')", "on ", "CHAR(5) NOT NULL"); - f.checkString("greatest('show', 'on', 'earth')", "show ", - "CHAR(5) NOT NULL"); - f.checkScalar("greatest(12, CAST(NULL AS INTEGER), 3)", isNullValue(), - "INTEGER"); - f.checkScalar("greatest(false, true)", true, "BOOLEAN NOT NULL"); - - final SqlOperatorFixture f12 = f.forOracle(SqlConformanceEnum.ORACLE_12); - f12.checkString("greatest('on', 'earth')", "on", "VARCHAR(5) NOT NULL"); - f12.checkString("greatest('show', 'on', 'earth')", "show", - "VARCHAR(5) NOT NULL"); + final SqlOperatorFixture f0 = + fixture().setFor(SqlLibraryOperators.GREATEST, VmName.EXPAND); + f0.checkFails("^greatest('on', 'earth')^", + "No match found for function signature GREATEST\\(<CHARACTER>, <CHARACTER>\\)", + false); + final Consumer<SqlOperatorFixture> consumer = f -> { + f.checkString("greatest('on', 'earth')", "on ", "CHAR(5) NOT NULL"); + f.checkString("greatest('show', 'on', 'earth')", "show ", + "CHAR(5) NOT NULL"); + f.checkScalar("greatest(12, CAST(NULL AS INTEGER), 3)", isNullValue(), + "INTEGER"); + f.checkScalar("greatest(false, true)", true, "BOOLEAN NOT NULL"); + + final SqlOperatorFixture f12 = f.forOracle(SqlConformanceEnum.ORACLE_12); + f12.checkString("greatest('on', 'earth')", "on", "VARCHAR(5) NOT NULL"); + f12.checkString("greatest('show', 'on', 'earth')", "show", + "VARCHAR(5) NOT NULL"); + }; + f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE), consumer); } @Test void testLeastFunc() { - final SqlOperatorFixture f = fixture() - .setFor(SqlLibraryOperators.LEAST, VmName.EXPAND) - .withLibrary(SqlLibrary.ORACLE); - f.checkString("least('on', 'earth')", "earth", "CHAR(5) NOT NULL"); - f.checkString("least('show', 'on', 'earth')", "earth", - "CHAR(5) NOT NULL"); - f.checkScalar("least(12, CAST(NULL AS INTEGER), 3)", isNullValue(), - "INTEGER"); - f.checkScalar("least(false, true)", false, "BOOLEAN NOT NULL"); - - final SqlOperatorFixture f12 = f.forOracle(SqlConformanceEnum.ORACLE_12); - f12.checkString("least('on', 'earth')", "earth", "VARCHAR(5) NOT NULL"); - f12.checkString("least('show', 'on', 'earth')", "earth", - "VARCHAR(5) NOT NULL"); + final SqlOperatorFixture f0 = fixture() + .setFor(SqlLibraryOperators.LEAST, VmName.EXPAND); + f0.checkFails("^least('on', 'earth')^", + "No match found for function signature LEAST\\(<CHARACTER>, <CHARACTER>\\)", + false); + final Consumer<SqlOperatorFixture> consumer = f -> { + f.checkString("least('on', 'earth')", "earth", "CHAR(5) NOT NULL"); + f.checkString("least('show', 'on', 'earth')", "earth", + "CHAR(5) NOT NULL"); + f.checkScalar("least(12, CAST(NULL AS INTEGER), 3)", isNullValue(), + "INTEGER"); + f.checkScalar("least(false, true)", false, "BOOLEAN NOT NULL"); + + final SqlOperatorFixture f12 = f0.forOracle(SqlConformanceEnum.ORACLE_12); + f12.checkString("least('on', 'earth')", "earth", "VARCHAR(5) NOT NULL"); + f12.checkString("least('show', 'on', 'earth')", "earth", + "VARCHAR(5) NOT NULL"); + }; + f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE), consumer); } @Test void testNvlFunc() {
