This is an automated email from the ASF dual-hosted git repository.
zhehu 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 9f531455f1 [CALCITE-6812] Support base64 and unbase64 functions for
Hive
9f531455f1 is described below
commit 9f531455f1de60b56b684219ef92080e16d04870
Author: xuyu <[email protected]>
AuthorDate: Sat Feb 1 17:19:22 2025 +0800
[CALCITE-6812] Support base64 and unbase64 functions for Hive
---
.../calcite/adapter/enumerable/RexImpTable.java | 4 +++
.../org/apache/calcite/runtime/SqlFunctions.java | 6 ++--
.../calcite/sql/fun/SqlLibraryOperators.java | 13 ++++++++
site/_docs/reference.md | 4 ++-
.../org/apache/calcite/test/SqlOperatorTest.java | 38 ++++++++++++++++++++++
5 files changed, 61 insertions(+), 4 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 88166ba14a..ebc9c6d9da 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
@@ -158,6 +158,7 @@
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ASINH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ATAND;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ATANH;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.BASE64;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.BITAND_AGG;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.BITOR_AGG;
import static
org.apache.calcite.sql.fun.SqlLibraryOperators.BIT_COUNT_BIG_QUERY;
@@ -335,6 +336,7 @@
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_MICROS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_MILLIS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_SECONDS;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.UN_BASE64;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.URL_DECODE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.URL_ENCODE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.XML_TRANSFORM;
@@ -685,6 +687,8 @@ void populate1() {
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(BASE64, BuiltInMethod.TO_BASE64.method, NullPolicy.STRICT);
+ defineMethod(UN_BASE64, BuiltInMethod.FROM_BASE64.method,
NullPolicy.STRICT);
defineMethod(TO_BASE32, BuiltInMethod.TO_BASE32.method,
NullPolicy.STRICT);
defineMethod(FROM_BASE32, BuiltInMethod.FROM_BASE32.method,
NullPolicy.STRICT);
defineMethod(HEX, BuiltInMethod.HEX.method, NullPolicy.STRICT);
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 2b5f9f0dee..eeee51eecd 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -300,12 +300,12 @@ public static ByteString uuidToBinary(UUID uuid) {
return new ByteString(dest);
}
- /** SQL TO_BASE64(string) function. */
+ /** SQL TO_BASE64(string)/BASE64(string) function. */
public static String toBase64(String string) {
return toBase64_(string.getBytes(UTF_8));
}
- /** SQL TO_BASE64(string) function for binary string. */
+ /** SQL TO_BASE64(string)/BASE64(string) function for binary string. */
public static String toBase64(ByteString string) {
return toBase64_(string.getBytes());
}
@@ -320,7 +320,7 @@ private static String toBase64_(byte[] bytes) {
return str.substring(0, str.length() - 1);
}
- /** SQL FROM_BASE64(string) function. */
+ /** SQL FROM_BASE64(string)/UNBASE64(string) function. */
public static @Nullable ByteString fromBase64(String base64) {
try {
base64 = FROM_BASE64_REGEXP.matcher(base64).replaceAll("");
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 14ea74815f..b3fa3585aa 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
@@ -1855,6 +1855,19 @@ private static RelDataType
deriveTypeMapFromEntries(SqlOperatorBinding opBinding
OperandTypes.STRING.or(OperandTypes.BINARY),
SqlFunctionCategory.STRING);
+ @LibraryOperator(libraries = {HIVE})
+ public static final SqlFunction BASE64 =
+ SqlBasicFunction.create("BASE64",
+ ReturnTypes.VARCHAR_NULLABLE,
+ OperandTypes.STRING.or(OperandTypes.BINARY),
+ SqlFunctionCategory.STRING);
+
+ @LibraryOperator(libraries = {HIVE})
+ public static final SqlFunction UN_BASE64 =
+ SqlBasicFunction.create("UNBASE64",
+ ReturnTypes.VARBINARY_FORCE_NULLABLE,
+ OperandTypes.STRING, SqlFunctionCategory.STRING);
+
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlFunction FROM_BASE32 =
SqlBasicFunction.create("FROM_BASE32",
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 2da0234810..2fd932e57f 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2922,7 +2922,9 @@ ### Dialect-specific Operators
| b | FROM_BASE32(string) | Returns the decoded
result of a base-32 *string* as a string
| m | TO_BASE64(string) | Converts the *string*
to base-64 encoded form and returns a encoded string
| b m | FROM_BASE64(string) | Returns the decoded
result of a base-64 *string* as a string. If the input argument is an invalid
base-64 *string* the function returns `NULL`
-| h s | HEX(string) | Converts *string* into
a hexadecimal varchar
+| h | BASE64(string) | Converts the *string*
to base-64 encoded form and returns a encoded string
+| h | UNBASE64(string) | Returns the decoded
result of a base-64 *string* as a string. If the input argument is an invalid
base-64 *string* the function returns `NULL`
+| 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 h | LTRIM(string) | Returns *string* with
all blanks removed from the start
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 f11bf01a12..dab42ffdac 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -5173,6 +5173,44 @@ void testBitGetFunc(SqlOperatorFixture f, String
functionName) {
f.checkString("to_base64(x'61')", "YQ==", "VARCHAR NOT NULL");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6812";>[CALCITE-6812]
+ * Add base64 function (enabled in Hive library)</a>. */
+ @Test void testBase64() {
+ final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.HIVE);
+ f.setFor(SqlLibraryOperators.BASE64);
+ f.checkString("base64(cast('a' as binary))", "YQ==", "VARCHAR NOT NULL");
+ f.checkString("base64('')", "", "VARCHAR NOT NULL");
+ f.checkNull("base64(null)");
+ f.checkString("base64('This is a test String.')",
+ "VGhpcyBpcyBhIHRlc3QgU3RyaW5nLg==",
+ "VARCHAR NOT NULL");
+ }
+
+ @Test void testUnBase64() {
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.UN_BASE64);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkString("unbase64('VGhpcyBpcyBhIHRlc3QgU3RyaW5nLg==')",
+ "546869732069732061207465737420537472696e672e",
+ "VARBINARY");
+ f.checkString("unbase64('VGhpcyBpcyBhIHRlc\t3QgU3RyaW5nLg==')",
+ "546869732069732061207465737420537472696e672e",
+ "VARBINARY");
+ f.checkString("unbase64('VGhpcyBpcyBhIHRlc\t3QgU3\nRyaW5nLg==')",
+ "546869732069732061207465737420537472696e672e",
+ "VARBINARY");
+ f.checkString("unbase64('VGhpcyB pcyBhIHRlc3Qg\tU3Ry\naW5nLg==')",
+ "546869732069732061207465737420537472696e672e",
+ "VARBINARY");
+ f.checkNull("unbase64('-1')");
+ f.checkNull("unbase64('-100')");
+ f.checkNull("unbase64(null)");
+ };
+ f0.forEachLibrary(list(SqlLibrary.HIVE), consumer);
+
+ }
+
@Test void testToChar() {
final SqlOperatorFixture f0 =
fixture().setFor(SqlLibraryOperators.TO_CHAR);
final Consumer<SqlOperatorFixture> consumer = f -> {
