[ https://issues.apache.org/jira/browse/FLINK-9961?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16560581#comment-16560581 ]
ASF GitHub Bot commented on FLINK-9961: --------------------------------------- yanghua closed pull request #6428: [FLINK-9961] Add CHR function for table/sql API URL: https://github.com/apache/flink/pull/6428 This is a PR merged from a forked repository. As GitHub hides the original diff on merge, it is displayed below for the sake of provenance: As this is a foreign pull request (from a fork), the diff is supplied below (as it won't show otherwise due to GitHub magic): diff --git a/docs/dev/table/sql.md b/docs/dev/table/sql.md index 1ed06f0d1c8..94c3f1c5b13 100644 --- a/docs/dev/table/sql.md +++ b/docs/dev/table/sql.md @@ -1839,6 +1839,17 @@ FROM_BASE64(text string) </td> </tr> + <tr> + <td> + {% highlight text %} +CHR(ascii integer) +{% endhighlight %} + </td> + <td> + <p>Returns string contains a character which converts from a ASCII integer. If the ASCII less then 0 or greater than 255, return null. E.g. <code>CHR(65)</code> returns <code>A</code>, <code>CHR(97)</code> returns <code>a</code>.</p> + </td> + </tr> + </tbody> </table> diff --git a/docs/dev/table/tableApi.md b/docs/dev/table/tableApi.md index b1b8f6082d8..8625a77df06 100644 --- a/docs/dev/table/tableApi.md +++ b/docs/dev/table/tableApi.md @@ -2496,6 +2496,17 @@ concat_ws(separator, string1, string2,...) <p>Returns the string that results from concatenating the arguments using a separator. The separator is added between the strings to be concatenated. Returns NULL If the separator is NULL. concat_ws() does not skip empty strings. However, it does skip any NULL argument. E.g. <code>concat_ws("~", "AA", "BB", "", "CC")</code> returns <code>AA~BB~~CC</code></p> </td> </tr> + + <tr> + <td> + {% highlight text %} +INTEGER.chr() +{% endhighlight %} + </td> + <td> + <p>Returns string contains a character which converts from a ASCII integer. If the ASCII less then 0 or greater than 255, return null. E.g. <code>97.chr()</code> returns <code>a</code></p> + </td> + </tr> </tbody> </table> diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/scala/expressionDsl.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/scala/expressionDsl.scala index 62c62b13296..46612cb79c2 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/scala/expressionDsl.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/scala/expressionDsl.scala @@ -544,6 +544,12 @@ trait ImplicitExpressionOperations { */ def fromBase64() = FromBase64(expr) + /* + * Returns string contains a character which converts from a ASCII integer. + * If the ASCII less then 0 or greater than 255, return null. + */ + def chr() = Chr(expr) + // Temporal operations /** diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/calls/BuiltInMethods.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/calls/BuiltInMethods.scala index 22298dadfd7..cd498af1c2f 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/calls/BuiltInMethods.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/calls/BuiltInMethods.scala @@ -112,4 +112,6 @@ object BuiltInMethods { val BIN = Types.lookupMethod(classOf[JLong], "toBinaryString", classOf[Long]) val FROMBASE64 = Types.lookupMethod(classOf[ScalarFunctions], "fromBase64", classOf[String]) + + val CHR = Types.lookupMethod(classOf[ScalarFunctions], "chr", classOf[Integer]) } diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/calls/FunctionGenerator.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/calls/FunctionGenerator.scala index d264cce6ddb..536b79fa49c 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/calls/FunctionGenerator.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/codegen/calls/FunctionGenerator.scala @@ -152,6 +152,13 @@ object FunctionGenerator { STRING_TYPE_INFO, BuiltInMethods.FROMBASE64) + addSqlFunctionMethod( + CHR, + Seq(INT_TYPE_INFO), + STRING_TYPE_INFO, + BuiltInMethods.CHR + ) + // ---------------------------------------------------------------------------------------------- // Arithmetic functions // ---------------------------------------------------------------------------------------------- diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/expressions/stringExpressions.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/expressions/stringExpressions.scala index 87d251deb75..3b3724b1e3f 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/expressions/stringExpressions.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/expressions/stringExpressions.scala @@ -383,3 +383,25 @@ case class FromBase64(child: Expression) extends UnaryExpression with InputTypeS override def toString: String = s"($child).fromBase64" } + +case class Chr(child: Expression) extends UnaryExpression with InputTypeSpec { + + override private[flink] def expectedTypes: Seq[TypeInformation[_]] = Seq(INT_TYPE_INFO) + + override private[flink] def resultType: TypeInformation[_] = STRING_TYPE_INFO + + override private[flink] def validateInput(): ValidationResult = { + if (child.resultType == INT_TYPE_INFO) { + ValidationSuccess + } else { + ValidationFailure(s"Chr operator requires a Integer input, " + + s"but $child is of type ${child.resultType}") + } + } + + override private[flink] def toRexNode(implicit relBuilder: RelBuilder) = { + relBuilder.call(ScalarSqlFunctions.CHR, child.toRexNode) + } + + override def toString = s"chr($child)" +} diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/functions/sql/ScalarSqlFunctions.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/functions/sql/ScalarSqlFunctions.scala index 1af1e68b2d3..d84594d02ef 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/functions/sql/ScalarSqlFunctions.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/functions/sql/ScalarSqlFunctions.scala @@ -168,4 +168,12 @@ object ScalarSqlFunctions { SqlFunctionCategory.STRING ) + val CHR = new SqlFunction( + "CHR", + SqlKind.OTHER_FUNCTION, + ReturnTypes.cascade(ReturnTypes.explicit(SqlTypeName.VARCHAR), SqlTypeTransforms.TO_NULLABLE), + null, + OperandTypes.NUMERIC, + SqlFunctionCategory.STRING + ) } diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/runtime/functions/ScalarFunctions.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/runtime/functions/ScalarFunctions.scala index 40f1ec3b746..53de3caae78 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/runtime/functions/ScalarFunctions.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/runtime/functions/ScalarFunctions.scala @@ -190,4 +190,16 @@ object ScalarFunctions { */ def fromBase64(str: String): String = new String(Base64.decodeBase64(str)) + /** + * Returns string contains a character which converts from a ASCII integer. + * If the ASCII less then 0 or greater than 255, return null. + */ + def chr(ascii: Integer): String = { + if (ascii < 0 || ascii > 255) { + return null + } + + return ascii.toChar.toString + } + } diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/validate/FunctionCatalog.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/validate/FunctionCatalog.scala index b4f04240706..c9f4afd9f09 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/validate/FunctionCatalog.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/validate/FunctionCatalog.scala @@ -203,6 +203,7 @@ object FunctionCatalog { "lpad" -> classOf[Lpad], "rpad" -> classOf[Rpad], "fromBase64" -> classOf[FromBase64], + "chr" -> classOf[Chr], // math functions "plus" -> classOf[Plus], @@ -445,6 +446,7 @@ class BasicOperatorTable extends ReflectiveSqlOperatorTable { ScalarSqlFunctions.SHA512, ScalarSqlFunctions.SHA2, ScalarSqlFunctions.FROM_BASE64, + ScalarSqlFunctions.CHR, // EXTENSIONS BasicOperatorTable.TUMBLE, BasicOperatorTable.HOP, diff --git a/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarFunctionsTest.scala b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarFunctionsTest.scala index 995762a6814..086371f107b 100644 --- a/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarFunctionsTest.scala +++ b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarFunctionsTest.scala @@ -472,6 +472,45 @@ class ScalarFunctionsTest extends ScalarTypesTestBase { "null") } + @Test + def testChr(): Unit = { + testAllApis( + 'f14.chr(), + "f14.chr()", + "CHR(f14)", + "null") + + testAllApis( + 'f34.chr(), + "f34.chr()", + "CHR(f34)", + "null") + + testAllApis( + 'f34.chr(), + "f34.chr()", + "CHR(f34)", + "null") + + testAllApis( + 'f36.chr(), + "f36.chr()", + "CHR(f36)", + "A") + + testAllApis( + 'f37.chr(), + "f37.chr()", + "CHR(f37)", + "a") + + testAllApis( + 'f38.chr(), + "f38.chr()", + "CHR(f38)", + "ÿ") + } + // ---------------------------------------------------------------------------------------------- // Math functions // ---------------------------------------------------------------------------------------------- diff --git a/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/utils/ScalarTypesTestBase.scala b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/utils/ScalarTypesTestBase.scala index 6ad59b17b73..9f97b1a9271 100644 --- a/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/utils/ScalarTypesTestBase.scala +++ b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/utils/ScalarTypesTestBase.scala @@ -28,7 +28,7 @@ import org.apache.flink.types.Row class ScalarTypesTestBase extends ExpressionTestBase { def testData: Row = { - val testData = new Row(36) + val testData = new Row(39) testData.setField(0, "This is a test String.") testData.setField(1, true) testData.setField(2, 42.toByte) @@ -65,6 +65,9 @@ class ScalarTypesTestBase extends ExpressionTestBase { testData.setField(33, null) testData.setField(34, 256) testData.setField(35, "aGVsbG8gd29ybGQ=") + testData.setField(36, 65) + testData.setField(37, 97) + testData.setField(38, 255) testData } @@ -105,6 +108,9 @@ class ScalarTypesTestBase extends ExpressionTestBase { Types.INT, Types.STRING, Types.INT, - Types.STRING).asInstanceOf[TypeInformation[Any]] + Types.STRING, + Types.INT, + Types.INT, + Types.INT).asInstanceOf[TypeInformation[Any]] } } ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org > Add CHR function for table/sql API > ---------------------------------- > > Key: FLINK-9961 > URL: https://issues.apache.org/jira/browse/FLINK-9961 > Project: Flink > Issue Type: New Feature > Components: Table API & SQL > Reporter: vinoyang > Assignee: vinoyang > Priority: Minor > Labels: pull-request-available > > This function convert ASCII code to a character, > refer to : [https://doc.ispirer.com/sqlways/Output/SQLWays-1-071.html] > Considering "CHAR" always is a keyword in many database, so we use "CHR" > keyword. -- This message was sent by Atlassian JIRA (v7.6.3#76005)