This is an automated email from the ASF dual-hosted git repository.
rubenql 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 febd06b2cc [CALCITE-5642] Add SHA256, SHA512 functions (enabled in
BigQuery and PostgreSQL libraries)
febd06b2cc is described below
commit febd06b2cc7815895c4d6aafc3712a63fe1c335f
Author: zoudan <zou...@bytedance.com>
AuthorDate: Thu Apr 13 22:38:43 2023 +0800
[CALCITE-5642] Add SHA256, SHA512 functions (enabled in BigQuery and
PostgreSQL libraries)
---
.../calcite/adapter/enumerable/RexImpTable.java | 4 ++
.../org/apache/calcite/runtime/SqlFunctions.java | 20 ++++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 16 ++++++
.../org/apache/calcite/util/BuiltInMethod.java | 2 +
.../org/apache/calcite/test/SqlFunctionsTest.java | 40 +++++++++++++++
site/_docs/reference.md | 2 +
.../org/apache/calcite/test/SqlOperatorTest.java | 58 ++++++++++++++++++++++
7 files changed, 142 insertions(+)
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 02525cd38f..92b874ee11 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
@@ -170,6 +170,8 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.RLIKE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.RPAD;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SAFE_CAST;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SHA1;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.SHA256;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.SHA512;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SINH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SOUNDEX;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SPACE;
@@ -432,6 +434,8 @@ public class RexImpTable {
defineMethod(FROM_BASE64, BuiltInMethod.FROM_BASE64.method,
NullPolicy.STRICT);
defineMethod(MD5, BuiltInMethod.MD5.method, NullPolicy.STRICT);
defineMethod(SHA1, BuiltInMethod.SHA1.method, NullPolicy.STRICT);
+ defineMethod(SHA256, BuiltInMethod.SHA256.method, NullPolicy.STRICT);
+ defineMethod(SHA512, BuiltInMethod.SHA512.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 cfd462ceaf..8f726822fd 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -273,6 +273,26 @@ public class SqlFunctions {
return DigestUtils.sha1Hex(string.getBytes());
}
+ /** SQL SHA256(string) function. */
+ public static String sha256(String string) {
+ return DigestUtils.sha256Hex(string.getBytes(UTF_8));
+ }
+
+ /** SQL SHA256(string) function for binary string. */
+ public static String sha256(ByteString string) {
+ return DigestUtils.sha256Hex(string.getBytes());
+ }
+
+ /** SQL SHA512(string) function. */
+ public static String sha512(String string) {
+ return DigestUtils.sha512Hex(string.getBytes(UTF_8));
+ }
+
+ /** SQL SHA512(string) function for binary string. */
+ public static String sha512(ByteString string) {
+ return DigestUtils.sha512Hex(string.getBytes());
+ }
+
/** SQL {@code REGEXP_REPLACE} function with 3 arguments. */
public static String regexpReplace(String s, String regex,
String replacement) {
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 c090845f58..ed3ebd5ff8 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
@@ -1188,6 +1188,22 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING.or(OperandTypes.BINARY),
SqlFunctionCategory.STRING);
+ @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL})
+ public static final SqlFunction SHA256 =
+ SqlBasicFunction.create("SHA256",
+ ReturnTypes.explicit(SqlTypeName.VARCHAR)
+ .andThen(SqlTypeTransforms.TO_NULLABLE),
+ OperandTypes.STRING.or(OperandTypes.BINARY),
+ SqlFunctionCategory.STRING);
+
+ @LibraryOperator(libraries = {BIG_QUERY, POSTGRESQL})
+ public static final SqlFunction SHA512 =
+ SqlBasicFunction.create("SHA512",
+ ReturnTypes.explicit(SqlTypeName.VARCHAR)
+ .andThen(SqlTypeTransforms.TO_NULLABLE),
+ OperandTypes.STRING.or(OperandTypes.BINARY),
+ SqlFunctionCategory.STRING);
+
/** The "LOG(value [, value2])" function.
*
* @see SqlStdOperatorTable#LN
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 251cdcfce3..afc56eb0d0 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -361,6 +361,8 @@ public enum BuiltInMethod {
FROM_BASE64(SqlFunctions.class, "fromBase64", String.class),
MD5(SqlFunctions.class, "md5", String.class),
SHA1(SqlFunctions.class, "sha1", String.class),
+ SHA256(SqlFunctions.class, "sha256", String.class),
+ SHA512(SqlFunctions.class, "sha512", String.class),
THROW_UNLESS(SqlFunctions.class, "throwUnless", boolean.class, String.class),
COMPRESS(CompressionFunctions.class, "compress", String.class),
EXTRACT_VALUE(XmlFunctions.class, "extractValue", String.class,
String.class),
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 700b2a5944..2d44371722 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -57,6 +57,8 @@ import static
org.apache.calcite.runtime.SqlFunctions.posixRegex;
import static org.apache.calcite.runtime.SqlFunctions.regexpReplace;
import static org.apache.calcite.runtime.SqlFunctions.rtrim;
import static org.apache.calcite.runtime.SqlFunctions.sha1;
+import static org.apache.calcite.runtime.SqlFunctions.sha256;
+import static org.apache.calcite.runtime.SqlFunctions.sha512;
import static org.apache.calcite.runtime.SqlFunctions.toBase64;
import static org.apache.calcite.runtime.SqlFunctions.toChar;
import static org.apache.calcite.runtime.SqlFunctions.toInt;
@@ -1015,6 +1017,44 @@ class SqlFunctionsTest {
}
}
+ @Test void testSha256() {
+
assertThat("e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855",
+ is(sha256("")));
+
assertThat("e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855",
+ is(sha256(ByteString.of("", 16))));
+
assertThat("a591a6d40bf420404a011733cfb7b190d62c65bf0bcda32b57b277d9ad9f146e",
+ is(sha256("Hello World")));
+
assertThat("a591a6d40bf420404a011733cfb7b190d62c65bf0bcda32b57b277d9ad9f146e",
+ is(sha256(new ByteString("Hello World".getBytes(UTF_8)))));
+ try {
+ String o = sha256((String) null);
+ fail("Expected NPE, got " + o);
+ } catch (NullPointerException e) {
+ // ok
+ }
+ }
+
+ @Test void testSha512() {
+
assertThat("cf83e1357eefb8bdf1542850d66d8007d620e4050b5715dc83f4a921d36ce9ce47d0d13c5"
+ + "d85f2b0ff8318d2877eec2f63b931bd47417a81a538327af927da3e",
+ is(sha512("")));
+
assertThat("cf83e1357eefb8bdf1542850d66d8007d620e4050b5715dc83f4a921d36ce9ce47d0d13c5"
+ + "d85f2b0ff8318d2877eec2f63b931bd47417a81a538327af927da3e",
+ is(sha512(ByteString.of("", 16))));
+
assertThat("2c74fd17edafd80e8447b0d46741ee243b7eb74dd2149a0ab1b9246fb30382f27e853d858"
+ + "5719e0e67cbda0daa8f51671064615d645ae27acb15bfb1447f459b",
+ is(sha512("Hello World")));
+
assertThat("2c74fd17edafd80e8447b0d46741ee243b7eb74dd2149a0ab1b9246fb30382f27e853d858"
+ + "5719e0e67cbda0daa8f51671064615d645ae27acb15bfb1447f459b",
+ is(sha512(new ByteString("Hello World".getBytes(UTF_8)))));
+ try {
+ String o = sha512((String) null);
+ fail("Expected NPE, got " + o);
+ } catch (NullPointerException e) {
+ // ok
+ }
+ }
+
/**
* Tests that a date in the local time zone converts to a Unix timestamp in
* UTC.
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 5507fbe476..d90178f42c 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2727,6 +2727,8 @@ BigQuery's type system uses confusingly different names
for types and functions:
| b o | RTRIM(string) | Returns *string* with
all blanks removed from the end
| b | SAFE_CAST(value AS type) | Converts *value* to
*type*, returning NULL if conversion fails
| b m p | 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
| m | SPACE(integer) | Returns a string of
*integer* spaces; returns an empty string if *integer* is less than 1
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 9d4c2a424a..43b322a19f 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -16,6 +16,7 @@
*/
package org.apache.calcite.test;
+import org.apache.calcite.avatica.util.ByteString;
import org.apache.calcite.avatica.util.DateTimeUtils;
import org.apache.calcite.config.CalciteConnectionProperty;
import org.apache.calcite.linq4j.Linq4j;
@@ -135,6 +136,8 @@ import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.fail;
import static org.junit.jupiter.api.Assumptions.assumeTrue;
+import static java.nio.charset.StandardCharsets.UTF_8;
+
/**
* Contains unit tests for all operators. Each of the methods is named after an
* operator.
@@ -3899,6 +3902,61 @@ public class SqlOperatorTest {
f0.forEachLibrary(libraries, consumer);
}
+ @Test void testSha256() {
+ final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.SHA1);
+ f0.checkFails("^sha256(x'')^",
+ "No match found for function signature SHA256\\(<BINARY>\\)",
+ false);
+
+ final List<SqlLibrary> libraries =
+ ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.POSTGRESQL);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkString("sha256('')",
+ "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855",
+ "VARCHAR NOT NULL");
+ f.checkString("sha256(x'')",
+ "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855",
+ "VARCHAR NOT NULL");
+ f.checkString("sha256('Hello World')",
+ "a591a6d40bf420404a011733cfb7b190d62c65bf0bcda32b57b277d9ad9f146e",
+ "VARCHAR NOT NULL");
+ f.checkString("sha256(x'48656c6c6f20576f726c64')",
+ "a591a6d40bf420404a011733cfb7b190d62c65bf0bcda32b57b277d9ad9f146e",
+ "VARCHAR NOT NULL");
+ };
+ f0.forEachLibrary(libraries, consumer);
+ }
+
+ @Test void testSha512() {
+ final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.SHA1);
+ f0.checkFails("^sha512(x'')^",
+ "No match found for function signature SHA512\\(<BINARY>\\)",
+ false);
+
+ final List<SqlLibrary> libraries =
+ ImmutableList.of(SqlLibrary.BIG_QUERY, SqlLibrary.POSTGRESQL);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkString("sha512('')",
+
"cf83e1357eefb8bdf1542850d66d8007d620e4050b5715dc83f4a921d36ce9ce47d0d13c5d85f2"
+ + "b0ff8318d2877eec2f63b931bd47417a81a538327af927da3e",
+ "VARCHAR NOT NULL");
+ f.checkString("sha512(x'')",
+
"cf83e1357eefb8bdf1542850d66d8007d620e4050b5715dc83f4a921d36ce9ce47d0d13c5d85f2b"
+ + "0ff8318d2877eec2f63b931bd47417a81a538327af927da3e",
+ "VARCHAR NOT NULL");
+ f.checkString("sha512('Hello World')",
+
"2c74fd17edafd80e8447b0d46741ee243b7eb74dd2149a0ab1b9246fb30382f27e853d8585719e0"
+ + "e67cbda0daa8f51671064615d645ae27acb15bfb1447f459b",
+ "VARCHAR NOT NULL");
+ String hexString = new ByteString("Hello
World".getBytes(UTF_8)).toString();
+ f.checkString("sha512(x'" + hexString + "')",
+
"2c74fd17edafd80e8447b0d46741ee243b7eb74dd2149a0ab1b9246fb30382f27e853d8585719e0"
+ + "e67cbda0daa8f51671064615d645ae27acb15bfb1447f459b",
+ "VARCHAR NOT NULL");
+ };
+ f0.forEachLibrary(libraries, consumer);
+ }
+
@Test void testRepeatFunc() {
final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.REPEAT);
f0.checkFails("^repeat('a', -100)^",
