You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by "ASF GitHub Bot (JIRA)" <ji...@apache.org> on 2018/10/24 02:37:00 UTC
[jira] [Commented] (FLINK-9999) Add ISNUMERIC supported in Table
API/SQL
[ https://issues.apache.org/jira/browse/FLINK-9999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16661627#comment-16661627 ]
ASF GitHub Bot commented on FLINK-9999:
---------------------------------------
yanghua closed pull request #6473: [FLINK-9999] [table] Add ISNUMERIC supported in Table API/SQL
URL: https://github.com/apache/flink/pull/6473
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 366e3fdcc64..76d801ca960 100644
--- a/docs/dev/table/sql.md
+++ b/docs/dev/table/sql.md
@@ -1842,6 +1842,16 @@ RPAD(text string, len integer, pad string)
<tr>
<td>
{% highlight text %}
+ISNUMERIC(text string)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns an Integer to indicate if the text string is a numeric value, supports some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($), if true, returns 1, otherwise returns 0, if text is NULL, returns NULL. E.g. <code>ISNUMERIC('123.0')</code> returns <code>1</code>.</p>
+ </td>
+ </tr>
+ <tr>
+ <td>
+ {% highlight text %}
FROM_BASE64(text string)
{% endhighlight %}
</td>
diff --git a/docs/dev/table/tableApi.md b/docs/dev/table/tableApi.md
index 6e202f19d5d..61f74d51dee 100644
--- a/docs/dev/table/tableApi.md
+++ b/docs/dev/table/tableApi.md
@@ -2477,6 +2477,17 @@ STRING.rpad(len INT, pad STRING)
<tr>
<td>
{% highlight java %}
+STRING.isNumeric()
+{% endhighlight %}
+ </td>
+
+ <td>
+ <p>Returns an Integer to indicate if the text string is a numeric value, supports some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($), if true, returns 1, otherwise returns 0, if text is NULL, returns NULL. E.g. "123".isNumeric() returns 1.</p>
+ </td>
+ </tr>
+ <tr>
+ <td>
+ {% highlight java %}
STRING.fromBase64()
{% endhighlight %}
</td>
@@ -4025,6 +4036,18 @@ STRING.initCap()
</td>
</tr>
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.isNumeric()
+{% endhighlight %}
+ </td>
+
+ <td>
+ <p>Returns an Integer to indicate if the text string is a numeric value, supports some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($), if true, returns 1, otherwise returns 0, if text is NULL, returns NULL. E.g. "123".isNumeric() returns 1.</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 35d2167848a..4c3c411bbe0 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,11 @@ trait ImplicitExpressionOperations {
def overlay(newString: Expression, starting: Expression, length: Expression) =
Overlay(expr, newString, starting, length)
+ /**
+ * Returns an Integer to indicate if the text string is a numeric value.
+ */
+ def isNumeric() = IsNumeric(expr)
+
/**
* Returns the base string decoded with base64.
*/
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 0e0f709eabc..e02dc6e8869 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
@@ -113,5 +113,7 @@ object BuiltInMethods {
val BIN = Types.lookupMethod(classOf[JLong], "toBinaryString", classOf[Long])
+ val ISNUMERIC = Types.lookupMethod(classOf[ScalarFunctions], "isNumeric", classOf[String])
+
val FROMBASE64 = Types.lookupMethod(classOf[ScalarFunctions], "fromBase64", 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 a5c275ab415..39f1f4948b1 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
@@ -146,6 +146,12 @@ object FunctionGenerator {
STRING_TYPE_INFO,
BuiltInMethod.OVERLAY.method)
+ addSqlFunctionMethod(
+ ISNUMERIC,
+ Seq(STRING_TYPE_INFO),
+ INT_TYPE_INFO,
+ BuiltInMethods.ISNUMERIC)
+
addSqlFunctionMethod(
FROM_BASE64,
Seq(STRING_TYPE_INFO),
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..885144c7422 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
@@ -358,6 +358,29 @@ case class Rpad(text: Expression, len: Expression, pad: Expression)
}
}
+case class IsNumeric(child: Expression) extends UnaryExpression with InputTypeSpec {
+
+ override private[flink] def expectedTypes: Seq[TypeInformation[_]] = Seq(STRING_TYPE_INFO)
+
+ override private[flink] def resultType: TypeInformation[_] = INT_TYPE_INFO
+
+ override private[flink] def validateInput(): ValidationResult = {
+ if (child.resultType == STRING_TYPE_INFO) {
+ ValidationSuccess
+ } else {
+ ValidationFailure(s"IsNumeric operator requires a String input, " +
+ s"but $child is of type ${child.resultType}")
+ }
+ }
+
+ override private[flink] def toRexNode(implicit relBuilder: RelBuilder): RexNode = {
+ relBuilder.call(ScalarSqlFunctions.ISNUMERIC, child.toRexNode)
+ }
+
+ override def toString: String = s"($child).isNumeric"
+
+}
+
/**
* Returns the base string decoded with base64.
* Returns NULL If the input string is NULL.
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 49fda9dd46d..ac929e6de34 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
@@ -167,6 +167,15 @@ object ScalarSqlFunctions {
SqlFunctionCategory.TIMEDATE
)
+ val ISNUMERIC = new SqlFunction(
+ "ISNUMERIC",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.INTEGER,
+ InferTypes.RETURN_TYPE,
+ OperandTypes.STRING,
+ SqlFunctionCategory.STRING
+ )
+
val FROM_BASE64 = new SqlFunction(
"FROM_BASE64",
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 50e8f9cc900..1904e8309fc 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
@@ -196,6 +196,17 @@ object ScalarFunctions {
new String(data)
}
+ /**
+ * Returns an Integer to indicate if the text string is a numeric value.
+ */
+ def isNumeric(str: String): Integer = {
+ if (str.matches("^(([0-9+-.$]{1})|([+-]?[$]?[0-9]*(([.]{1}[0-9]*)|([.]?[0-9]+))))$")) {
+ 1
+ } else {
+ 0
+ }
+ }
+
/**
* 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 8a91340d86f..be19817378f 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
@@ -202,6 +202,7 @@ object FunctionCatalog {
"concat_ws" -> classOf[ConcatWs],
"lpad" -> classOf[Lpad],
"rpad" -> classOf[Rpad],
+ "isNumeric" -> classOf[IsNumeric],
"fromBase64" -> classOf[FromBase64],
// math functions
@@ -446,6 +447,7 @@ class BasicOperatorTable extends ReflectiveSqlOperatorTable {
ScalarSqlFunctions.SHA384,
ScalarSqlFunctions.SHA512,
ScalarSqlFunctions.SHA2,
+ ScalarSqlFunctions.ISNUMERIC,
ScalarSqlFunctions.FROM_BASE64,
// EXTENSIONS
BasicOperatorTable.TUMBLE,
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 4eb2e33a8a0..c43c68d5303 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
@@ -450,6 +450,63 @@ class ScalarFunctionsTest extends ScalarTypesTestBase {
"1111111111111111111111111111111111111111111111111111111111111111")
}
+ @Test
+ def testIsNumeric(): Unit = {
+ testAllApis(
+ "123".isNumeric(),
+ "'123'.isNumeric",
+ "ISNUMERIC('123')",
+ "1")
+
+ testAllApis(
+ "0.123".isNumeric(),
+ "'123'.isNumeric",
+ "ISNUMERIC('0.123')",
+ "1")
+
+ testAllApis(
+ "123.0".isNumeric(),
+ "'123.0'.isNumeric",
+ "ISNUMERIC('123.0')",
+ "1")
+
+ testAllApis(
+ "123.0".isNumeric(),
+ "'123.0'.isNumeric",
+ "ISNUMERIC('123.0')",
+ "1")
+
+ testAllApis(
+ "+123.0".isNumeric(),
+ "'+123.0'.isNumeric",
+ "ISNUMERIC('+123.0')",
+ "1")
+
+ testAllApis(
+ "-0.123".isNumeric(),
+ "'-0.123'.isNumeric",
+ "ISNUMERIC('-0.123')",
+ "1")
+
+ testAllApis(
+ "0.123a".isNumeric(),
+ "'0.123a'.isNumeric",
+ "ISNUMERIC('0.123a')",
+ "0")
+
+ testAllApis(
+ "$0.123".isNumeric(),
+ "'$0.123'.isNumeric",
+ "ISNUMERIC('$0.123')",
+ "1")
+
+ testAllApis(
+ 'f33.isNumeric(),
+ "f33.isNumeric",
+ "ISNUMERIC(f33)",
+ "null")
+ }
+
@Test
def testFromBase64(): Unit = {
testAllApis(
----------------------------------------------------------------
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 ISNUMERIC supported in Table API/SQL
> ----------------------------------------
>
> Key: FLINK-9999
> URL: https://issues.apache.org/jira/browse/FLINK-9999
> Project: Flink
> Issue Type: Sub-task
> Components: Table API & SQL
> Reporter: vinoyang
> Assignee: vinoyang
> Priority: Major
> Labels: pull-request-available
>
> ISNUMERIC function used to verify a expression is a valid numberic type.
> documentation : https://docs.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-2017
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)