flink-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (FLINK-9961) Add CHR function for table/sql API
Date Sat, 28 Jul 2018 03:24:00 GMT

    [ 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:
users@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 &amp; 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)

Mime
View raw message