You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@flink.apache.org by di...@apache.org on 2022/06/29 01:43:41 UTC
[flink] branch master updated: [FLINK-28092][table] Support ASCII and CHR built-in function in the Table API
This is an automated email from the ASF dual-hosted git repository.
dianfu pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/flink.git
The following commit(s) were added to refs/heads/master by this push:
new e7e120acf92 [FLINK-28092][table] Support ASCII and CHR built-in function in the Table API
e7e120acf92 is described below
commit e7e120acf9258ed8ef3c416fed6ed59cc0a06855
Author: Ada Wang <wa...@gmail.com>
AuthorDate: Thu Jun 16 17:24:21 2022 +0800
[FLINK-28092][table] Support ASCII and CHR built-in function in the Table API
This closes #19988.
---
docs/data/sql_functions.yml | 2 ++
docs/data/sql_functions_zh.yml | 2 ++
flink-python/pyflink/table/expression.py | 14 +++++++++++++
.../pyflink/table/tests/test_expression.py | 2 ++
.../flink/table/api/internal/BaseExpressions.java | 12 +++++++++++
.../functions/BuiltInFunctionDefinitions.java | 16 +++++++++++++++
.../expressions/converter/DirectConvertRule.java | 2 ++
.../planner/expressions/ScalarFunctionsTest.scala | 24 ++++++++++++----------
8 files changed, 63 insertions(+), 11 deletions(-)
diff --git a/docs/data/sql_functions.yml b/docs/data/sql_functions.yml
index 91d9d0fbc16..b2d2950e243 100644
--- a/docs/data/sql_functions.yml
+++ b/docs/data/sql_functions.yml
@@ -327,8 +327,10 @@ string:
table: STRING.toBase64()
description: Returns the base64-encoded result from string; returns NULL if string is NULL. E.g., TO_BASE64('hello world') returns "aGVsbG8gd29ybGQ=".
- sql: ASCII(string)
+ table: STRING.ascii()
description: Returns the numeric value of the first character of string. Returns NULL if string is NULL. E.g., ascii('abc') returns 97, and ascii(CAST(NULL AS VARCHAR)) returns NULL.
- sql: CHR(integer)
+ table: INT.chr()
description: Returns the ASCII character having the binary equivalent to integer. If integer is larger than 255, we will get the modulus of integer divided by 255 first, and returns CHR of the modulus. Returns NULL if integer is NULL. E.g., chr(97) returns a, chr(353) returns a, and ascii(CAST(NULL AS VARCHAR)) returns NULL.
- sql: DECODE(binary, string)
description: Decodes the first argument into a String using the provided character set (one of 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'). If either argument is null, the result will also be null.
diff --git a/docs/data/sql_functions_zh.yml b/docs/data/sql_functions_zh.yml
index a05ff9694ec..061462b7d6a 100644
--- a/docs/data/sql_functions_zh.yml
+++ b/docs/data/sql_functions_zh.yml
@@ -411,10 +411,12 @@ string:
返回字符串 string 的 base64 编码的结果；如果字符串为 `NULL`，则返回 `NULL`。
例如 `TO_BASE64('hello world')` 返回 `"aGVsbG8gd29ybGQ="`。
- sql: ASCII(string)
+ table: STRING.ascii()
description: |
返回字符串 string 第一个字符的数值。如果字符串为 `NULL` 则返回 `NULL`。
例如 `ascii('abc')` 返回 `97`，`ascii(CAST(NULL AS VARCHAR))` 返回 `NULL`。
- sql: CHR(integer)
+ table: INT.chr()
description: |
返回二进制等于 integer 的 ASCII 字符。如果整数 integer 大于 255，我们先将得到整数对 255 取模数，
并返回模数的 CHR。如果整数为 `NULL`，则返回 `NULL`。例如 `chr(97)` 返回 `a`，`chr(353)` 返回 `a`，
diff --git a/flink-python/pyflink/table/expression.py b/flink-python/pyflink/table/expression.py
index 5adce3f322a..ec7d02ab793 100644
--- a/flink-python/pyflink/table/expression.py
+++ b/flink-python/pyflink/table/expression.py
@@ -1186,6 +1186,20 @@ class Expression(Generic[T]):
"""
return _unary_op("toBase64")(self)
+ @property
+ def ascii(self) -> 'Expression[int]':
+ """
+ Returns the numeric value of the first character of the input string.
+ """
+ return _unary_op("ascii")(self)
+
+ @property
+ def chr(self) -> 'Expression[str]':
+ """
+ Returns the ASCII character result of the input integer.
+ """
+ return _unary_op("chr")(self)
+
@property
def ltrim(self) -> 'Expression[str]':
"""
diff --git a/flink-python/pyflink/table/tests/test_expression.py b/flink-python/pyflink/table/tests/test_expression.py
index 06f17c458ac..559eda74a7d 100644
--- a/flink-python/pyflink/table/tests/test_expression.py
+++ b/flink-python/pyflink/table/tests/test_expression.py
@@ -143,6 +143,8 @@ class PyFlinkBatchExpressionTests(PyFlinkTestCase):
self.assertEqual('regexpExtract(a, b, 3)', str(expr1.regexp_extract(expr2, 3)))
self.assertEqual('fromBase64(a)', str(expr1.from_base64))
self.assertEqual('toBase64(a)', str(expr1.to_base64))
+ self.assertEqual('ascii(a)', str(expr1.ascii))
+ self.assertEqual('chr(a)', str(expr1.chr))
self.assertEqual('ltrim(a)', str(expr1.ltrim))
self.assertEqual('rtrim(a)', str(expr1.rtrim))
self.assertEqual('repeat(a, 3)', str(expr1.repeat(3)))
diff --git a/flink-table/flink-table-api-java/src/main/java/org/apache/flink/table/api/internal/BaseExpressions.java b/flink-table/flink-table-api-java/src/main/java/org/apache/flink/table/api/internal/BaseExpressions.java
index 800cdd8aa3f..268225d9eb8 100644
--- a/flink-table/flink-table-api-java/src/main/java/org/apache/flink/table/api/internal/BaseExpressions.java
+++ b/flink-table/flink-table-api-java/src/main/java/org/apache/flink/table/api/internal/BaseExpressions.java
@@ -55,6 +55,7 @@ import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.ACOS;
import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.AND;
import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.ARRAY_CONTAINS;
import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.ARRAY_ELEMENT;
+import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.ASCII;
import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.ASIN;
import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.AT;
import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.ATAN;
@@ -65,6 +66,7 @@ import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.CARDIN
import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.CAST;
import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.CEIL;
import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.CHAR_LENGTH;
+import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.CHR;
import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.COLLECT;
import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.COS;
import static org.apache.flink.table.functions.BuiltInFunctionDefinitions.COSH;
@@ -1022,6 +1024,16 @@ public abstract class BaseExpressions<InType, OutType> {
return toApiSpecificExpression(unresolvedCall(TO_BASE64, toExpr()));
}
+ /** Returns the numeric value of the first character of the input string. */
+ public OutType ascii() {
+ return toApiSpecificExpression(unresolvedCall(ASCII, toExpr()));
+ }
+
+ /** Returns the ASCII character result of the input integer. */
+ public OutType chr() {
+ return toApiSpecificExpression(unresolvedCall(CHR, toExpr()));
+ }
+
/** Returns a string that removes the left whitespaces from the given string. */
public OutType ltrim() {
return toApiSpecificExpression(unresolvedCall(LTRIM, toExpr()));
diff --git a/flink-table/flink-table-common/src/main/java/org/apache/flink/table/functions/BuiltInFunctionDefinitions.java b/flink-table/flink-table-common/src/main/java/org/apache/flink/table/functions/BuiltInFunctionDefinitions.java
index 32f0001a06b..c6bc4dd535f 100644
--- a/flink-table/flink-table-common/src/main/java/org/apache/flink/table/functions/BuiltInFunctionDefinitions.java
+++ b/flink-table/flink-table-common/src/main/java/org/apache/flink/table/functions/BuiltInFunctionDefinitions.java
@@ -722,6 +722,22 @@ public final class BuiltInFunctionDefinitions {
.outputTypeStrategy(nullableIfArgs(explicit(DataTypes.STRING())))
.build();
+ public static final BuiltInFunctionDefinition ASCII =
+ BuiltInFunctionDefinition.newBuilder()
+ .name("ascii")
+ .kind(SCALAR)
+ .inputTypeStrategy(sequence(logical(LogicalTypeFamily.CHARACTER_STRING)))
+ .outputTypeStrategy(nullableIfArgs(explicit(DataTypes.TINYINT())))
+ .build();
+
+ public static final BuiltInFunctionDefinition CHR =
+ BuiltInFunctionDefinition.newBuilder()
+ .name("chr")
+ .kind(SCALAR)
+ .inputTypeStrategy(sequence(logical(LogicalTypeFamily.INTEGER_NUMERIC)))
+ .outputTypeStrategy(nullableIfArgs(explicit(DataTypes.CHAR(1))))
+ .build();
+
public static final BuiltInFunctionDefinition UUID =
BuiltInFunctionDefinition.newBuilder()
.name("uuid")
diff --git a/flink-table/flink-table-planner/src/main/java/org/apache/flink/table/planner/expressions/converter/DirectConvertRule.java b/flink-table/flink-table-planner/src/main/java/org/apache/flink/table/planner/expressions/converter/DirectConvertRule.java
index 4b0023f5ca0..132e2452673 100644
--- a/flink-table/flink-table-planner/src/main/java/org/apache/flink/table/planner/expressions/converter/DirectConvertRule.java
+++ b/flink-table/flink-table-planner/src/main/java/org/apache/flink/table/planner/expressions/converter/DirectConvertRule.java
@@ -110,6 +110,8 @@ public class DirectConvertRule implements CallExpressionConvertRule {
BuiltInFunctionDefinitions.FROM_BASE64, FlinkSqlOperatorTable.FROM_BASE64);
DEFINITION_OPERATOR_MAP.put(
BuiltInFunctionDefinitions.TO_BASE64, FlinkSqlOperatorTable.TO_BASE64);
+ DEFINITION_OPERATOR_MAP.put(BuiltInFunctionDefinitions.ASCII, FlinkSqlOperatorTable.ASCII);
+ DEFINITION_OPERATOR_MAP.put(BuiltInFunctionDefinitions.CHR, FlinkSqlOperatorTable.CHR);
DEFINITION_OPERATOR_MAP.put(BuiltInFunctionDefinitions.UUID, FlinkSqlOperatorTable.UUID);
DEFINITION_OPERATOR_MAP.put(BuiltInFunctionDefinitions.LTRIM, FlinkSqlOperatorTable.LTRIM);
DEFINITION_OPERATOR_MAP.put(BuiltInFunctionDefinitions.RTRIM, FlinkSqlOperatorTable.RTRIM);
diff --git a/flink-table/flink-table-planner/src/test/scala/org/apache/flink/table/planner/expressions/ScalarFunctionsTest.scala b/flink-table/flink-table-planner/src/test/scala/org/apache/flink/table/planner/expressions/ScalarFunctionsTest.scala
index 6070b3152fe..f2b2d9a51da 100644
--- a/flink-table/flink-table-planner/src/test/scala/org/apache/flink/table/planner/expressions/ScalarFunctionsTest.scala
+++ b/flink-table/flink-table-planner/src/test/scala/org/apache/flink/table/planner/expressions/ScalarFunctionsTest.scala
@@ -178,7 +178,7 @@ class ScalarFunctionsTest extends ScalarTypesTestBase {
@Test
def testAscii(): Unit = {
- testSqlApi("ascii('efg')", "101")
+ testAllApis("efg".ascii(), "ASCII('efg')", "101")
testSqlApi("ascii('abcdef')", "97")
@@ -1594,6 +1594,18 @@ class ScalarFunctionsTest extends ScalarTypesTestBase {
@Test
def testChr(): Unit = {
+ testAllApis(
+ 65.chr(),
+ "CHR(65)",
+ "A"
+ )
+
+ testAllApis(
+ -9.chr(),
+ "CHR(-9)",
+ ""
+ )
+
testSqlApi(
"CHR(f4)",
","
@@ -1609,11 +1621,6 @@ class ScalarFunctionsTest extends ScalarTypesTestBase {
Character.MIN_VALUE.toString
)
- testSqlApi(
- "CHR(65)",
- "A"
- )
-
testSqlApi(
"CHR(CAST (-10 AS BIGINT))",
""
@@ -1634,11 +1641,6 @@ class ScalarFunctionsTest extends ScalarTypesTestBase {
"a"
)
- testSqlApi(
- "CHR(-9)",
- ""
- )
-
testSqlApi(
"CHR(0)",
Character.MIN_VALUE.toString