This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 00ad79b6bc95b68e02129b31be8a3a656517f8a8 Author: ShuMingLi <[email protected]> AuthorDate: Tue Aug 20 13:50:38 2019 +0800 [CALCITE-3263] Add MD5, SHA1 SQL functions (Shuming Li) The functions are consistent with PostgreSQL, MySQL, Redshift; we do not at this time implement the similar functions in BigQuery, which allow BINARY or CHAR arguments and return a BINARY result. Close apache/calcite#1390 --- .../calcite/adapter/enumerable/RexImpTable.java | 4 +++ .../org/apache/calcite/runtime/SqlFunctions.java | 21 +++++++++++++ .../calcite/sql/fun/SqlLibraryOperators.java | 20 +++++++++++++ .../org/apache/calcite/util/BuiltInMethod.java | 2 ++ .../calcite/sql/test/SqlOperatorBaseTest.java | 34 ++++++++++++++++++++++ .../org/apache/calcite/test/SqlFunctionsTest.java | 30 +++++++++++++++++++ site/_docs/reference.md | 22 +++++++------- 7 files changed, 123 insertions(+), 10 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java index 42fd16c..6e0ad74 100644 --- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java +++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java @@ -104,10 +104,12 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_REMOVE; import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_STORAGE_SIZE; import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_TYPE; import static org.apache.calcite.sql.fun.SqlLibraryOperators.LEFT; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.MD5; import static org.apache.calcite.sql.fun.SqlLibraryOperators.MONTHNAME; import static org.apache.calcite.sql.fun.SqlLibraryOperators.REPEAT; import static org.apache.calcite.sql.fun.SqlLibraryOperators.REVERSE; import static org.apache.calcite.sql.fun.SqlLibraryOperators.RIGHT; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.SHA1; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SOUNDEX; import static org.apache.calcite.sql.fun.SqlLibraryOperators.SPACE; import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_BASE64; @@ -292,6 +294,8 @@ public class RexImpTable { defineMethod(INITCAP, BuiltInMethod.INITCAP.method, NullPolicy.STRICT); defineMethod(TO_BASE64, BuiltInMethod.TO_BASE64.method, NullPolicy.STRICT); defineMethod(FROM_BASE64, BuiltInMethod.FROM_BASE64.method, NullPolicy.STRICT); + defineMethod(MD5, BuiltInMethod.MD5.method, NullPolicy.STRICT); + defineMethod(SHA1, BuiltInMethod.SHA1.method, NullPolicy.STRICT); defineMethod(SUBSTRING, BuiltInMethod.SUBSTRING.method, NullPolicy.STRICT); defineMethod(LEFT, BuiltInMethod.LEFT.method, NullPolicy.ANY); defineMethod(RIGHT, BuiltInMethod.RIGHT.method, NullPolicy.ANY); diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java index 68f6d94..2e23b66 100644 --- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java +++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java @@ -39,6 +39,7 @@ import org.apache.calcite.util.TimeWithTimeZoneString; import org.apache.calcite.util.TimestampWithTimeZoneString; import org.apache.calcite.util.Util; +import org.apache.commons.codec.digest.DigestUtils; import org.apache.commons.codec.language.Soundex; import com.google.common.base.Splitter; @@ -172,6 +173,26 @@ public class SqlFunctions { } } + /** SQL MD5(string) function. */ + public static @Nonnull String md5(@Nonnull String string) { + return DigestUtils.md5Hex(string.getBytes(UTF_8)); + } + + /** SQL MD5(string) function for binary string. */ + public static @Nonnull String md5(@Nonnull ByteString string) { + return DigestUtils.md5Hex(string.getBytes()); + } + + /** SQL SHA1(string) function. */ + public static @Nonnull String sha1(@Nonnull String string) { + return DigestUtils.sha1Hex(string.getBytes(UTF_8)); + } + + /** SQL SHA1(string) function for binary string. */ + public static @Nonnull String sha1(@Nonnull ByteString string) { + return DigestUtils.sha1Hex(string.getBytes()); + } + /** SQL SUBSTRING(string FROM ... FOR ...) function. */ public static String substring(String c, int s, int l) { int lc = c.length(); 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 896b9f7..3487ab9 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 @@ -313,6 +313,26 @@ public abstract class SqlLibraryOperators { OperandTypes.INTEGER, SqlFunctionCategory.STRING); + @LibraryOperator(libraries = {MYSQL, POSTGRESQL}) + public static final SqlFunction MD5 = + new SqlFunction("MD5", + SqlKind.OTHER_FUNCTION, + ReturnTypes.cascade(ReturnTypes.explicit(SqlTypeName.VARCHAR), + SqlTypeTransforms.TO_NULLABLE), + null, + OperandTypes.or(OperandTypes.STRING, OperandTypes.BINARY), + SqlFunctionCategory.STRING); + + @LibraryOperator(libraries = {MYSQL, POSTGRESQL}) + public static final SqlFunction SHA1 = + new SqlFunction("SHA1", + SqlKind.OTHER_FUNCTION, + ReturnTypes.cascade(ReturnTypes.explicit(SqlTypeName.VARCHAR), + SqlTypeTransforms.TO_NULLABLE), + null, + OperandTypes.or(OperandTypes.STRING, OperandTypes.BINARY), + SqlFunctionCategory.STRING); + /** Infix "::" cast operator used by PostgreSQL, for example * {@code '100'::INTEGER}. */ @LibraryOperator(libraries = { POSTGRESQL }) diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java index dd3ae8b..48b69ac 100644 --- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java +++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java @@ -309,6 +309,8 @@ public enum BuiltInMethod { RIGHT(SqlFunctions.class, "right", String.class, int.class), TO_BASE64(SqlFunctions.class, "toBase64", String.class), FROM_BASE64(SqlFunctions.class, "fromBase64", String.class), + MD5(SqlFunctions.class, "md5", String.class), + SHA1(SqlFunctions.class, "sha1", String.class), JSONIZE(JsonFunctions.class, "jsonize", Object.class), DEJSONIZE(JsonFunctions.class, "dejsonize", String.class), JSON_VALUE_EXPRESSION(JsonFunctions.class, "jsonValueExpression", diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index 76847e7..e6cf965 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -4365,6 +4365,40 @@ public abstract class SqlOperatorBaseTest { tester1.checkNull("from_base64('-100')"); } + @Test public void testMd5() { + final SqlTester tester1 = tester(SqlLibrary.MYSQL); + tester1.setFor(SqlLibraryOperators.MD5); + tester1.checkString("md5(x'')", + "d41d8cd98f00b204e9800998ecf8427e", + "VARCHAR NOT NULL"); + tester1.checkString("md5('')", + "d41d8cd98f00b204e9800998ecf8427e", + "VARCHAR NOT NULL"); + tester1.checkString("md5('ABC')", + "902fbdd2b1df0c4f70b4a5d23525e932", + "VARCHAR NOT NULL"); + tester1.checkString("md5(x'414243')", + "902fbdd2b1df0c4f70b4a5d23525e932", + "VARCHAR NOT NULL"); + } + + @Test public void testSha1() { + final SqlTester tester1 = tester(SqlLibrary.MYSQL); + tester1.setFor(SqlLibraryOperators.SHA1); + tester1.checkString("sha1(x'')", + "da39a3ee5e6b4b0d3255bfef95601890afd80709", + "VARCHAR NOT NULL"); + tester1.checkString("sha1('')", + "da39a3ee5e6b4b0d3255bfef95601890afd80709", + "VARCHAR NOT NULL"); + tester1.checkString("sha1('ABC')", + "3c01bdbb26f358bab27f267924aa2c9a03fcfdb8", + "VARCHAR NOT NULL"); + tester1.checkString("sha1(x'414243')", + "3c01bdbb26f358bab27f267924aa2c9a03fcfdb8", + "VARCHAR NOT NULL"); + } + @Test public void testRepeatFunc() { final SqlTester tester1 = tester(SqlLibrary.MYSQL); tester1.setFor(SqlLibraryOperators.REPEAT); diff --git a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java index 7d2840b..8f788d7 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java @@ -40,8 +40,10 @@ import static org.apache.calcite.runtime.SqlFunctions.initcap; import static org.apache.calcite.runtime.SqlFunctions.lesser; import static org.apache.calcite.runtime.SqlFunctions.lower; import static org.apache.calcite.runtime.SqlFunctions.ltrim; +import static org.apache.calcite.runtime.SqlFunctions.md5; import static org.apache.calcite.runtime.SqlFunctions.posixRegex; import static org.apache.calcite.runtime.SqlFunctions.rtrim; +import static org.apache.calcite.runtime.SqlFunctions.sha1; import static org.apache.calcite.runtime.SqlFunctions.subtractMonths; import static org.apache.calcite.runtime.SqlFunctions.toBase64; import static org.apache.calcite.runtime.SqlFunctions.trim; @@ -869,6 +871,34 @@ public class SqlFunctionsTest { assertThat(SqlFunctions.multisetUnionDistinct(z, addc), is(Arrays.asList("a", "c", "d"))); } + + @Test public void testMd5() { + assertThat("d41d8cd98f00b204e9800998ecf8427e", is(md5(""))); + assertThat("d41d8cd98f00b204e9800998ecf8427e", is(md5(ByteString.of("", 16)))); + assertThat("902fbdd2b1df0c4f70b4a5d23525e932", is(md5("ABC"))); + assertThat("902fbdd2b1df0c4f70b4a5d23525e932", + is(md5(new ByteString("ABC".getBytes(UTF_8))))); + try { + String o = md5((String) null); + fail("Expected NPE, got " + o); + } catch (NullPointerException e) { + // ok + } + } + + @Test public void testSha1() { + assertThat("da39a3ee5e6b4b0d3255bfef95601890afd80709", is(sha1(""))); + assertThat("da39a3ee5e6b4b0d3255bfef95601890afd80709", is(sha1(ByteString.of("", 16)))); + assertThat("3c01bdbb26f358bab27f267924aa2c9a03fcfdb8", is(sha1("ABC"))); + assertThat("3c01bdbb26f358bab27f267924aa2c9a03fcfdb8", + is(sha1(new ByteString("ABC".getBytes(UTF_8))))); + try { + String o = sha1((String) null); + fail("Expected NPE, got " + o); + } catch (NullPointerException e) { + // ok + } + } } // End SqlFunctionsTest.java diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 72de0f5..0ae90ef 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -1431,7 +1431,7 @@ See also: the UNNEST relational operator converts a collection to a relation. <th>Description</th> </tr> <tr> - <td>period1 CONTAINS dateTime</td> + <td>period1 CONTAINS datetime</td> <td> <div class="container"> <div class="gray"><div class="r15"></div><div class="r2"></div></div> @@ -1513,10 +1513,10 @@ Where *period1* and *period2* are period expressions: {% highlight sql %} period: - (dateTime, dateTime) - | (dateTime, interval) - | PERIOD (dateTime, dateTime) - | PERIOD (dateTime, interval) + (datetime, datetime) + | (datetime, interval) + | PERIOD (datetime, datetime) + | PERIOD (datetime, interval) {% endhighlight %} ### JDBC function escape @@ -1745,9 +1745,9 @@ For example, if a query is grouped using | Operator syntax | Description |:-------------------- |:----------- -| HOP(dateTime, slide, size [, time ]) | Indicates a hopping window for *dateTime*, covering rows within the interval of *size*, shifting every *slide*, and optionally aligned at *time* -| SESSION(dateTime, interval [, time ]) | Indicates a session window of *interval* for *dateTime*, optionally aligned at *time* -| TUMBLE(dateTime, interval [, time ]) | Indicates a tumbling window of *interval* for *dateTime*, optionally aligned at *time* +| HOP(datetime, slide, size [, time ]) | Indicates a hopping window for *datetime*, covering rows within the interval of *size*, shifting every *slide*, and optionally aligned at *time* +| SESSION(datetime, interval [, time ]) | Indicates a session window of *interval* for *datetime*, optionally aligned at *time* +| TUMBLE(datetime, interval [, time ]) | Indicates a tumbling window of *interval* for *datetime*, optionally aligned at *time* ### Grouped auxiliary functions @@ -2174,6 +2174,7 @@ semantics. | o | CHR(integer) | Returns the character having the binary equivalent to *integer* as a CHAR value | m o p | CONCAT(string [, string ]*) | Concatenates two or more strings | p | CONVERT_TIMEZONE(tz1, tz2, datetime) | Converts the timezone of *datetime* from *tz1* to *tz2* +| m | DAYNAME(datetime) | Returns the name, in the connection's locale, of the weekday in *datetime*; for example, it returns '星期日' for both DATE '2020-02-10' and TIMESTAMP '2020-02-10 10:10:10' | 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 | p | DIFFERENCE(string, string) | Returns a measure of the similarity of two strings, namely the number of character positions that their `SOUNDEX` values have in common: 4 if the `SOUNDEX` values are same and 0 if the `SOUNDEX` values are totally different | o | GREATEST(expr [, expr ]*) | Returns the greatest of the expressions @@ -2188,14 +2189,15 @@ semantics. | 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 -| m | {fn DAYNAME(date)} | Returns the date of the name of the weekday in a value of datatype DATE; For example, it returns '星期日' for both DATE'2020-02-10' and TIMESTAMP'2020-02-10 10:10:10' -| m | {fn MONTHNAME(date)} | Returns the date of the name of the month in a value of datatype DATE; For example, it returns '二月' for both DATE'2020-02-10' and TIMESTAMP'2020-02-10 10:10:10' | 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 +| 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 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* | 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 | 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 | o | SUBSTR(string, position [, substring_length ]) | Returns a portion of *string*, beginning at character *position*, *substring_length* characters long. SUBSTR calculates lengths using characters as defined by the input character set
