[
https://issues.apache.org/jira/browse/FLINK-9853?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16579543#comment-16579543
]
ASF GitHub Bot commented on FLINK-9853:
---------------------------------------
asfgit closed pull request #6337: [FLINK-9853] [table] Add HEX support
URL: https://github.com/apache/flink/pull/6337
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 148f30727d9..09b9b261b68 100644
--- a/docs/dev/table/sql.md
+++ b/docs/dev/table/sql.md
@@ -1675,6 +1675,17 @@ BIN(numeric)
</td>
</tr>
+ <tr>
+ <td>
+{% highlight text %}
+HEX(numeric)
+HEX(string)
+ {% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string representation of an integer numeric value or a
string in hex format. Returns null if numeric is null. E.g. For numeric "20"
leads to "14", "100" leads to "64", and for string "hello,world" leads to
"68656c6c6f2c776f726c64".</p>
+ </td>
+ </tr>
</tbody>
</table>
diff --git a/docs/dev/table/tableApi.md b/docs/dev/table/tableApi.md
index b702dddcde5..b5dd4164a7d 100644
--- a/docs/dev/table/tableApi.md
+++ b/docs/dev/table/tableApi.md
@@ -2333,6 +2333,17 @@ NUMERIC.bin()
</td>
</tr>
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.hex()
+STRING.hex()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string representation of an integer numeric value or a
string in hex format. Returns null if numeric is null. E.g. For numeric "20"
leads to "14", "100" leads to "64", and for string "hello,world" leads to
"68656c6c6f2c776f726c64".</p>
+ </td>
+ </tr>
</tbody>
</table>
@@ -3921,6 +3932,17 @@ NUMERIC.bin()
</td>
</tr>
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.hex()
+STRING.hex()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string representation of an integer numeric value or a
string in hex format. Returns null if numeric is null. E.g. For numeric "20"
leads to "14", "100" leads to "64", and for string "hello,world" leads to
"68656c6c6f2c776f726c64".</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 c7c805f6743..1df30d98d11 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
@@ -406,6 +406,13 @@ trait ImplicitExpressionOperations {
*/
def bin() = Bin(expr)
+ /**
+ * Returns a string representation of an integer numeric value or a string
in hex format.
+ * Returns null if numeric or string is null. E.g. For numeric "20" leads
to "14",
+ * "100" leads to "64", and for string "hello,world" leads to
"68656c6c6f2c776f726c64".
+ */
+ def hex() = Hex(expr)
+
// String 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 f5ed9b387de..1e21bfe7830 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
@@ -132,6 +132,11 @@ object BuiltInMethods {
val BIN = Types.lookupMethod(classOf[JLong], "toBinaryString", classOf[Long])
+ val HEX = Types.lookupMethod(classOf[ScalarFunctions], "hex", classOf[Long])
+
+ val HEX_STRING =
+ Types.lookupMethod(classOf[ScalarFunctions], "hexString", classOf[String])
+
val FROMBASE64 = Types.lookupMethod(classOf[ScalarFunctions], "fromBase64",
classOf[String])
val TOBASE64 = Types.lookupMethod(classOf[ScalarFunctions], "toBase64",
classOf[String])
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 74b69d6afcc..47f54fb229c 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
@@ -455,6 +455,18 @@ object FunctionGenerator {
STRING_TYPE_INFO,
BuiltInMethods.BIN)
+ addSqlFunctionMethod(
+ ScalarSqlFunctions.HEX,
+ Seq(LONG_TYPE_INFO),
+ STRING_TYPE_INFO,
+ BuiltInMethods.HEX)
+
+ addSqlFunctionMethod(
+ ScalarSqlFunctions.HEX,
+ Seq(STRING_TYPE_INFO),
+ STRING_TYPE_INFO,
+ BuiltInMethods.HEX_STRING)
+
//
----------------------------------------------------------------------------------------------
// Temporal functions
//
----------------------------------------------------------------------------------------------
diff --git
a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/expressions/mathExpressions.scala
b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/expressions/mathExpressions.scala
index cf3efa938a6..728eac62e71 100644
---
a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/expressions/mathExpressions.scala
+++
b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/expressions/mathExpressions.scala
@@ -422,3 +422,21 @@ case class Bin(child: Expression) extends UnaryExpression {
relBuilder.call(ScalarSqlFunctions.BIN, child.toRexNode)
}
}
+
+case class Hex(child: Expression) extends UnaryExpression {
+ override private[flink] def resultType: TypeInformation[_] =
BasicTypeInfo.STRING_TYPE_INFO
+
+ override private[flink] def validateInput(): ValidationResult = {
+ if (TypeCheckUtils.isIntegerFamily(child.resultType) ||
+ TypeCheckUtils.isString(child.resultType)) {
+ ValidationSuccess
+ } else {
+ ValidationFailure(s"hex requires integer or string types but was
'${child.resultType}'.")
+ }
+ }
+ override def toString: String = s"hex($child)"
+
+ override private[flink] def toRexNode(implicit relBuilder: RelBuilder):
RexNode = {
+ relBuilder.call(ScalarSqlFunctions.HEX, child.toRexNode)
+ }
+}
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 21793e3351c..a0b6c9c67cf 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
@@ -42,6 +42,15 @@ object ScalarSqlFunctions {
OperandTypes.family(SqlTypeFamily.INTEGER),
SqlFunctionCategory.NUMERIC)
+ val HEX = new SqlFunction(
+ "HEX",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.cascade(ReturnTypes.explicit(SqlTypeName.VARCHAR),
SqlTypeTransforms.TO_NULLABLE),
+ InferTypes.RETURN_TYPE,
+ OperandTypes.or(OperandTypes.family(SqlTypeFamily.INTEGER),
+ OperandTypes.family(SqlTypeFamily.STRING)),
+ SqlFunctionCategory.NUMERIC)
+
val CONCAT = new SqlFunction(
"CONCAT",
SqlKind.OTHER_FUNCTION,
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 03ee62ce224..491fdd7b405 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
@@ -19,8 +19,9 @@ package org.apache.flink.table.runtime.functions
import scala.annotation.varargs
import java.math.{BigDecimal => JBigDecimal}
-import java.lang.StringBuilder
+import java.lang.{Long => JLong, StringBuilder}
+import org.apache.commons.codec.binary.Hex
import org.apache.commons.codec.binary.Base64
/**
@@ -202,6 +203,13 @@ object ScalarFunctions {
new String(data)
}
+ /**
+ * Returns the string str that is encoded as hex string of x.
+ */
+ def hex(x: Long): String = JLong.toHexString(x).toUpperCase()
+
+ def hexString(x: String): String =
Hex.encodeHexString(x.getBytes).toUpperCase()
+
/**
* Returns the base string decoded with base64.
*/
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 bca156c261a..a446401f31c 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
@@ -239,6 +239,7 @@ object FunctionCatalog {
"rand" -> classOf[Rand],
"randInteger" -> classOf[RandInteger],
"bin" -> classOf[Bin],
+ "hex" -> classOf[Hex],
// temporal functions
"extract" -> classOf[Extract],
@@ -438,6 +439,7 @@ class BasicOperatorTable extends ReflectiveSqlOperatorTable
{
ScalarSqlFunctions.CONCAT,
ScalarSqlFunctions.CONCAT_WS,
ScalarSqlFunctions.BIN,
+ ScalarSqlFunctions.HEX,
SqlStdOperatorTable.TIMESTAMP_ADD,
ScalarSqlFunctions.LOG,
ScalarSqlFunctions.LPAD,
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 6f9a9ae81b9..7f5b156202a 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
@@ -392,6 +392,93 @@ class ScalarFunctionsTest extends ScalarTypesTestBase {
"äää1234512345")
}
+ @Test
+ def testHex(): Unit = {
+ testAllApis(
+ 100.hex(),
+ "100.hex()",
+ "HEX(100)",
+ "64")
+
+ testAllApis(
+ 'f2.hex(),
+ "f2.hex()",
+ "HEX(f2)",
+ "2A")
+
+ testAllApis(
+ Null(Types.BYTE).hex(),
+ "hex(Null(BYTE))",
+ "HEX(CAST(NULL AS TINYINT))",
+ "null")
+
+ testAllApis(
+ 'f3.hex(),
+ "f3.hex()",
+ "HEX(f3)",
+ "2B")
+
+ testAllApis(
+ 'f4.hex(),
+ "f4.hex()",
+ "HEX(f4)",
+ "2C")
+
+ testAllApis(
+ 'f7.hex(),
+ "f7.hex()",
+ "HEX(f7)",
+ "3")
+
+ testAllApis(
+ 12.hex(),
+ "12.hex()",
+ "HEX(12)",
+ "C")
+
+ testAllApis(
+ 10.hex(),
+ "10.hex()",
+ "HEX(10)",
+ "A")
+
+ testAllApis(
+ 0.hex(),
+ "0.hex()",
+ "HEX(0)",
+ "0")
+
+ testAllApis(
+ 'f32.hex(),
+ "f32.hex()",
+ "HEX(f32)",
+ "FFFFFFFFFFFFFFFF")
+
+ testAllApis(
+ 'f0.hex(),
+ "f0.hex()",
+ "HEX(f0)",
+ "546869732069732061207465737420537472696E672E")
+
+ testAllApis(
+ 'f8.hex(),
+ "f8.hex()",
+ "HEX(f8)",
+ "20546869732069732061207465737420537472696E672E20")
+
+ testAllApis(
+ 'f23.hex(),
+ "f23.hex()",
+ "HEX(f23)",
+ "25546869732069732061207465737420537472696E672E")
+
+ testAllApis(
+ 'f24.hex(),
+ "f24.hex()",
+ "HEX(f24)",
+ "2A5F546869732069732061207465737420537472696E672E")
+ }
+
@Test
def testBin(): Unit = {
----------------------------------------------------------------
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 hex support in table api and sql
> ------------------------------------
>
> Key: FLINK-9853
> URL: https://issues.apache.org/jira/browse/FLINK-9853
> Project: Flink
> Issue Type: Improvement
> Components: Table API & SQL
> Reporter: xueyu
> Assignee: xueyu
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.7.0
>
>
> like in mysql, HEX could take int or string arguments, For a integer argument
> N, it returns a hexadecimal string representation of the value of N. For a
> string argument str, it returns a hexadecimal string representation of str
> where each byte of each character in str is converted to two hexadecimal
> digits.
> Syntax:
> HEX(100) = 64
> HEX('This is a test String.') = '546869732069732061207465737420537472696e672e'
> See more: [link
> MySQL|https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_hex]
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
