Skip to content

Commit 00db001

Browse files
zoudantanclary
zoudan
authored and
tanclary
committed
[CALCITE-5709] Add TO_BASE32 and FROM_BASE32 functions (enabled in BigQuery library)
1 parent e969f87 commit 00db001

File tree

7 files changed

+94
-4
lines changed

7 files changed

+94
-4
lines changed

‎core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -174,6 +174,7 @@
174174
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_DATETIME;
175175
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_TIME;
176176
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_TIMESTAMP;
177+
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FROM_BASE32;
177178
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FROM_BASE64;
178179
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ILIKE;
179180
import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_DEPTH;
@@ -236,6 +237,7 @@
236237
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIMESTAMP_SECONDS;
237238
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIMESTAMP_TRUNC;
238239
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIME_TRUNC;
240+
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_BASE32;
239241
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_BASE64;
240242
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_CHAR;
241243
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3;
@@ -485,6 +487,8 @@ Builder populate() {
485487
defineMethod(INITCAP, BuiltInMethod.INITCAP.method, NullPolicy.STRICT);
486488
defineMethod(TO_BASE64, BuiltInMethod.TO_BASE64.method, NullPolicy.STRICT);
487489
defineMethod(FROM_BASE64, BuiltInMethod.FROM_BASE64.method, NullPolicy.STRICT);
490+
defineMethod(TO_BASE32, BuiltInMethod.TO_BASE32.method, NullPolicy.STRICT);
491+
defineMethod(FROM_BASE32, BuiltInMethod.FROM_BASE32.method, NullPolicy.STRICT);
488492
defineMethod(MD5, BuiltInMethod.MD5.method, NullPolicy.STRICT);
489493
defineMethod(SHA1, BuiltInMethod.SHA1.method, NullPolicy.STRICT);
490494
defineMethod(SHA256, BuiltInMethod.SHA256.method, NullPolicy.STRICT);

‎core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -45,6 +45,7 @@
4545
import org.apache.calcite.util.format.FormatElement;
4646
import org.apache.calcite.util.format.FormatModels;
4747

48+
import org.apache.commons.codec.binary.Base32;
4849
import org.apache.commons.codec.digest.DigestUtils;
4950
import org.apache.commons.codec.language.Soundex;
5051

@@ -142,6 +143,8 @@ public class SqlFunctions {
142143

143144
private static final Pattern FROM_BASE64_REGEXP = Pattern.compile("[\\t\\n\\r\\s]");
144145

146+
private static final Base32 BASE_32 = new Base32();
147+
145148
private static final Function1<List<Object>, Enumerable<Object>> LIST_AS_ENUMERABLE =
146149
a0 -> a0 == null ? Linq4j.emptyEnumerable() : Linq4j.asEnumerable(a0);
147150

@@ -259,6 +262,25 @@ private static String toBase64_(byte[] bytes) {
259262
}
260263
}
261264

265+
/** SQL TO_BASE32(string) function. */
266+
public static String toBase32(String string) {
267+
return toBase32_(string.getBytes(UTF_8));
268+
}
269+
270+
/** SQL TO_BASE32(string) function for binary string. */
271+
public static String toBase32(ByteString string) {
272+
return toBase32_(string.getBytes());
273+
}
274+
275+
private static String toBase32_(byte[] bytes) {
276+
return BASE_32.encodeToString(bytes);
277+
}
278+
279+
/** SQL FROM_BASE32(string) function. */
280+
public static ByteString fromBase32(String base32) {
281+
return new ByteString(BASE_32.decode(base32));
282+
}
283+
262284
/** SQL MD5(string) function. */
263285
public static String md5(String string) {
264286
return DigestUtils.md5Hex(string.getBytes(UTF_8));

‎core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java

Lines changed: 15 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -469,8 +469,7 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
469469
@LibraryOperator(libraries = {MYSQL})
470470
public static final SqlFunction COMPRESS =
471471
SqlBasicFunction.create("COMPRESS",
472-
ReturnTypes.explicit(SqlTypeName.VARBINARY)
473-
.andThen(SqlTypeTransforms.TO_NULLABLE),
472+
ReturnTypes.VARBINARY_NULLABLE,
474473
OperandTypes.STRING, SqlFunctionCategory.STRING);
475474

476475
@LibraryOperator(libraries = {MYSQL})
@@ -1225,8 +1224,7 @@ private static RelDataType deriveTypeMapFromArrays(SqlOperatorBinding opBinding)
12251224
@LibraryOperator(libraries = {BIG_QUERY, MYSQL})
12261225
public static final SqlFunction FROM_BASE64 =
12271226
SqlBasicFunction.create("FROM_BASE64",
1228-
ReturnTypes.explicit(SqlTypeName.VARBINARY)
1229-
.andThen(SqlTypeTransforms.TO_NULLABLE),
1227+
ReturnTypes.VARBINARY_NULLABLE,
12301228
OperandTypes.STRING, SqlFunctionCategory.STRING);
12311229

12321230
@LibraryOperator(libraries = {MYSQL})
@@ -1236,6 +1234,19 @@ private static RelDataType deriveTypeMapFromArrays(SqlOperatorBinding opBinding)
12361234
OperandTypes.STRING.or(OperandTypes.BINARY),
12371235
SqlFunctionCategory.STRING);
12381236

1237+
@LibraryOperator(libraries = {BIG_QUERY})
1238+
public static final SqlFunction FROM_BASE32 =
1239+
SqlBasicFunction.create("FROM_BASE32",
1240+
ReturnTypes.VARBINARY_NULLABLE,
1241+
OperandTypes.CHARACTER, SqlFunctionCategory.STRING);
1242+
1243+
@LibraryOperator(libraries = {BIG_QUERY})
1244+
public static final SqlFunction TO_BASE32 =
1245+
SqlBasicFunction.create("TO_BASE32",
1246+
ReturnTypes.VARCHAR_NULLABLE,
1247+
OperandTypes.STRING,
1248+
SqlFunctionCategory.STRING);
1249+
12391250
/** The "TO_CHAR(timestamp, format)" function;
12401251
* converts {@code timestamp} to string according to the given {@code format}.
12411252
*/

‎core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -481,6 +481,19 @@ public static SqlCall stripSeparator(SqlCall call) {
481481
public static final SqlReturnTypeInference VARCHAR_NULLABLE =
482482
VARCHAR.andThen(SqlTypeTransforms.TO_NULLABLE);
483483

484+
/**
485+
* Type-inference strategy that always returns "VARBINARY".
486+
*/
487+
public static final SqlReturnTypeInference VARBINARY =
488+
ReturnTypes.explicit(SqlTypeName.VARBINARY);
489+
490+
/**
491+
* Type-inference strategy that always returns "VARBINARY" with nulls
492+
* allowed if any of the operands allow nulls.
493+
*/
494+
public static final SqlReturnTypeInference VARBINARY_NULLABLE =
495+
VARBINARY.andThen(SqlTypeTransforms.TO_NULLABLE);
496+
484497
/**
485498
* Type-inference strategy for Histogram agg support.
486499
*/

‎core/src/main/java/org/apache/calcite/util/BuiltInMethod.java

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -364,6 +364,8 @@ public enum BuiltInMethod {
364364
RIGHT(SqlFunctions.class, "right", String.class, int.class),
365365
TO_BASE64(SqlFunctions.class, "toBase64", String.class),
366366
FROM_BASE64(SqlFunctions.class, "fromBase64", String.class),
367+
TO_BASE32(SqlFunctions.class, "toBase32", String.class),
368+
FROM_BASE32(SqlFunctions.class, "fromBase32", String.class),
367369
MD5(SqlFunctions.class, "md5", String.class),
368370
SHA1(SqlFunctions.class, "sha1", String.class),
369371
SHA256(SqlFunctions.class, "sha256", String.class),

‎site/_docs/reference.md

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2748,6 +2748,8 @@ BigQuery's type system uses confusingly different names for types and functions:
27482748
| b | LENGTH(string) | Equivalent to `CHAR_LENGTH(string)`
27492749
| b | LOG(numeric1 [, numeric2 ]) | Returns the logarithm of *numeric1* to base *numeric2*, or base e if *numeric2* is not present
27502750
| b o | LPAD(string, length [, pattern ]) | Returns a string or bytes value that consists of *string* prepended to *length* with *pattern*
2751+
| b | TO_BASE32(string) | Converts the *string* to base-32 encoded form and returns an encoded string
2752+
| b | FROM_BASE32(string) | Returns the decoded result of a base-32 *string* as a string
27512753
| m | TO_BASE64(string) | Converts the *string* to base-64 encoded form and returns a encoded string
27522754
| b m | FROM_BASE64(string) | Returns the decoded result of a base-64 *string* as a string
27532755
| b o | LTRIM(string) | Returns *string* with all blanks removed from the start

‎testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4033,6 +4033,42 @@ static void checkRlikeFails(SqlOperatorFixture f) {
40334033
f0.forEachLibrary(list(SqlLibrary.BIG_QUERY, SqlLibrary.MYSQL), consumer);
40344034
}
40354035

4036+
@Test void testToBase32() {
4037+
final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.TO_BASE32);
4038+
f0.checkFails("^to_base32('')^",
4039+
"No match found for function signature TO_BASE32\\(<CHARACTER>\\)",
4040+
false);
4041+
final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
4042+
f.checkString("to_base32(x'436f6e766572747320612073657175656e6365206f6620425954"
4043+
+ "455320696e746f2061206261736533322d656e636f64656420535452494e472e')",
4044+
"INXW45TFOJ2HGIDBEBZWK4LVMVXGGZJAN5TCAQSZKRCVGIDJNZ2G6IDBEBRGC43FGMZC2ZLOMNXWIZ"
4045+
+ "LEEBJVIUSJJZDS4===",
4046+
"VARCHAR NOT NULL");
4047+
f.checkString("to_base32('Converts a sequence of BYTES into a base32-encoded STRING.')",
4048+
"INXW45TFOJ2HGIDBEBZWK4LVMVXGGZJAN5TCAQSZKRCVGIDJNZ2G6IDBEBRGC43FGMZC2ZLOMNXWIZ"
4049+
+ "LEEBJVIUSJJZDS4===",
4050+
"VARCHAR NOT NULL");
4051+
f.checkNull("to_base32(cast (null as varchar))");
4052+
f.checkString("to_base32(x'')", "", "VARCHAR NOT NULL");
4053+
f.checkString("to_base32('')", "", "VARCHAR NOT NULL");
4054+
}
4055+
4056+
@Test void testFromBase32() {
4057+
final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.FROM_BASE32);
4058+
f0.checkFails("^from_base32('')^",
4059+
"No match found for function signature FROM_BASE32\\(<CHARACTER>\\)",
4060+
false);
4061+
final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.BIG_QUERY);
4062+
f.checkString("from_base32('INXW45TFOJ2HGIDBEBZWK4LVMVXGGZJAN5TCAQSZKRCVGIDJNZ2"
4063+
+ "G6IDBEBRGC43FGMZC2ZLOMNXWIZLEEBJVIUSJJZDS4===')",
4064+
"436f6e766572747320612073657175656e6365206f6620425954455320696e746f206120626173"
4065+
+ "6533322d656e636f64656420535452494e472e",
4066+
"VARBINARY NOT NULL");
4067+
4068+
f.checkString("from_base32('')", "", "VARBINARY NOT NULL");
4069+
f.checkNull("from_base32(cast (null as varchar))");
4070+
}
4071+
40364072
@Test void testMd5() {
40374073
final SqlOperatorFixture f0 = fixture().setFor(SqlLibraryOperators.MD5);
40384074
f0.checkFails("^md5(x'')^",

0 commit comments

Comments
 (0)