[jira] [Commented] (FLINK-9999) Add ISNUMERIC supported in Table API/SQL
[
https://issues.apache.org/jira/browse/FLINK-?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16661627#comment-16661627
]
ASF GitHub Bot commented on FLINK-:
---
yanghua closed pull request #6473: [FLINK-] [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)
{% highlight text %}
+ISNUMERIC(text string)
+{% endhighlight %}
+
+
+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.
ISNUMERIC('123.0') returns 1.
+
+
+
+
+{% highlight text %}
FROM_BASE64(text string)
{% endhighlight %}
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)
{% highlight java %}
+STRING.isNumeric()
+{% endhighlight %}
+
+
+
+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.
+
+
+
+
+{% highlight java %}
STRING.fromBase64()
{% endhighlight %}
@@ -4025,6 +4036,18 @@ STRING.initCap()
+
+
+{% highlight scala %}
+STRING.isNumeric()
+{% endhighlight %}
+
+
+
+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.
+
+
+
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),
+
[jira] [Commented] (FLINK-9999) Add ISNUMERIC supported in Table API/SQL
[ https://issues.apache.org/jira/browse/FLINK-?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16623770#comment-16623770 ] Dawid Wysakowicz commented on FLINK-: - I would be against adding this function. It is not part of SQL 2016 standard and even in the only RDBMS that provides this function it is discouraged to use. Therefore I would vote for closing this issue and PR. > Add ISNUMERIC supported in Table API/SQL > > > Key: FLINK- > URL: https://issues.apache.org/jira/browse/FLINK- > 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)
[jira] [Commented] (FLINK-9999) Add ISNUMERIC supported in Table API/SQL
[ https://issues.apache.org/jira/browse/FLINK-?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16580627#comment-16580627 ] vinoyang commented on FLINK-: - [~walterddr] I don't know if this is the SQL standard Scalar function, but there are databases that provide this function. I don't know if the functions in Flink Table/SQL are all SQL standard functions. It seems that some are not, such as "similar to". Where can I see which standard functions are there? I think it's reasonable to add some very common functions, I am going to try to make this function support more types. [~twalthr] > Add ISNUMERIC supported in Table API/SQL > > > Key: FLINK- > URL: https://issues.apache.org/jira/browse/FLINK- > 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)
[jira] [Commented] (FLINK-9999) Add ISNUMERIC supported in Table API/SQL
[
https://issues.apache.org/jira/browse/FLINK-?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16580197#comment-16580197
]
Rong Rong commented on FLINK-:
--
I do not have access to the newest SQL 2016 standard, but is {{IS_NUMERIC}} in
it? Seems like multiple DBMS utilizes it in different way. Some takes more than
just String/VARCHAR type as inputs.
> Add ISNUMERIC supported in Table API/SQL
>
>
> Key: FLINK-
> URL: https://issues.apache.org/jira/browse/FLINK-
> 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)
[jira] [Commented] (FLINK-9999) Add ISNUMERIC supported in Table API/SQL
[
https://issues.apache.org/jira/browse/FLINK-?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16578781#comment-16578781
]
ASF GitHub Bot commented on FLINK-:
---
walterddr commented on a change in pull request #6473: [FLINK-] [table] Add
ISNUMERIC supported in Table API/SQL
URL: https://github.com/apache/flink/pull/6473#discussion_r209719307
##
File path:
flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarFunctionsTest.scala
##
@@ -450,6 +450,63 @@ class ScalarFunctionsTest extends ScalarTypesTestBase {
"")
}
+ @Test
+ def testIsNumeric(): Unit = {
Review comment:
Originally what I meant is, since this function only supports
string/varchar, let's have a test that specifies `ISNUMERIC(1L)` throws
`ValidationException`.
Regarding the usage of this in general, I think this is useful to chained
with many other operators with strict type constrains. such as `CASE WHEN
ISNUMERIC(...) THEN ... ELSE ...`, where the `THEN` clause requires some strict
numeric vaues. That's why I was wondering if we should do a better support
beyond just STRING/VARCHAR type.
I will comment on the JIRA actually. thanks for bring this up.
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 ISNUMERIC supported in Table API/SQL
>
>
> Key: FLINK-
> URL: https://issues.apache.org/jira/browse/FLINK-
> 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)
[jira] [Commented] (FLINK-9999) Add ISNUMERIC supported in Table API/SQL
[
https://issues.apache.org/jira/browse/FLINK-?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16577392#comment-16577392
]
ASF GitHub Bot commented on FLINK-:
---
yanghua commented on a change in pull request #6473: [FLINK-] [table] Add
ISNUMERIC supported in Table API/SQL
URL: https://github.com/apache/flink/pull/6473#discussion_r209441697
##
File path:
flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarFunctionsTest.scala
##
@@ -450,6 +450,63 @@ class ScalarFunctionsTest extends ScalarTypesTestBase {
"")
}
+ @Test
+ def testIsNumeric(): Unit = {
Review comment:
There is one here:
```
testAllApis(
"0.123a".isNumeric(),
"'0.123a'.isNumeric",
"ISNUMERIC('0.123a')",
"0")
```
I don't add too much at the moment, but more coverage is always necessary.
I want to wait until I can confirm that this function is necessary.
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 ISNUMERIC supported in Table API/SQL
>
>
> Key: FLINK-
> URL: https://issues.apache.org/jira/browse/FLINK-
> 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)
[jira] [Commented] (FLINK-9999) Add ISNUMERIC supported in Table API/SQL
[
https://issues.apache.org/jira/browse/FLINK-?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16577390#comment-16577390
]
ASF GitHub Bot commented on FLINK-:
---
yanghua commented on a change in pull request #6473: [FLINK-] [table] Add
ISNUMERIC supported in Table API/SQL
URL: https://github.com/apache/flink/pull/6473#discussion_r209441509
##
File path:
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) {
Review comment:
Here I think of it as a string type scalar function, the value represented
by this string can be int/bigInt/double and so on.
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 ISNUMERIC supported in Table API/SQL
>
>
> Key: FLINK-
> URL: https://issues.apache.org/jira/browse/FLINK-
> 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)
[jira] [Commented] (FLINK-9999) Add ISNUMERIC supported in Table API/SQL
[
https://issues.apache.org/jira/browse/FLINK-?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16577231#comment-16577231
]
ASF GitHub Bot commented on FLINK-:
---
walterddr commented on a change in pull request #6473: [FLINK-] [table] Add
ISNUMERIC supported in Table API/SQL
URL: https://github.com/apache/flink/pull/6473#discussion_r209430968
##
File path:
flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarFunctionsTest.scala
##
@@ -450,6 +450,63 @@ class ScalarFunctionsTest extends ScalarTypesTestBase {
"")
}
+ @Test
+ def testIsNumeric(): Unit = {
Review comment:
Maybe add invalid argument cases to `ScalarFunctionsValidationTest` as well?
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 ISNUMERIC supported in Table API/SQL
>
>
> Key: FLINK-
> URL: https://issues.apache.org/jira/browse/FLINK-
> 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)
[jira] [Commented] (FLINK-9999) Add ISNUMERIC supported in Table API/SQL
[
https://issues.apache.org/jira/browse/FLINK-?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16577232#comment-16577232
]
ASF GitHub Bot commented on FLINK-:
---
walterddr commented on a change in pull request #6473: [FLINK-] [table] Add
ISNUMERIC supported in Table API/SQL
URL: https://github.com/apache/flink/pull/6473#discussion_r209430879
##
File path:
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) {
Review comment:
Wasn't sure if there is a SQL standard for `isNumeric` but some SQL servers
such as MS and Transact do support `isNumeric(expression)` where expression can
actually be a Numeric type (INT, BIGINT, DOUBLE, etc). Maybe @twalthr have more
context ?
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 ISNUMERIC supported in Table API/SQL
>
>
> Key: FLINK-
> URL: https://issues.apache.org/jira/browse/FLINK-
> 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)
[jira] [Commented] (FLINK-9999) Add ISNUMERIC supported in Table API/SQL
[ https://issues.apache.org/jira/browse/FLINK-?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16569139#comment-16569139 ] ASF GitHub Bot commented on FLINK-: --- yanghua commented on issue #6473: [FLINK-] [table] Add ISNUMERIC supported in Table API/SQL URL: https://github.com/apache/flink/pull/6473#issuecomment-410439596 @walterddr can you review this PR? 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 ISNUMERIC supported in Table API/SQL > > > Key: FLINK- > URL: https://issues.apache.org/jira/browse/FLINK- > 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)
[jira] [Commented] (FLINK-9999) Add ISNUMERIC supported in Table API/SQL
[ https://issues.apache.org/jira/browse/FLINK-?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16566418#comment-16566418 ] ASF GitHub Bot commented on FLINK-: --- yanghua opened a new pull request #6473: [FLINK-] Add ISNUMERIC supported in Table API/SQL URL: https://github.com/apache/flink/pull/6473 ## What is the purpose of the change *This pull request add ISNUMERIC supported in Table API/SQL* ## Brief change log - *Add ISNUMERIC supported in Table API/SQL* ## Verifying this change This change is already covered by existing tests, such as *ScalarFunctionsTest#testIsNumeric*. ## Does this pull request potentially affect one of the following parts: - Dependencies (does it add or upgrade a dependency): (yes / **no**) - The public API, i.e., is any changed class annotated with `@Public(Evolving)`: (yes / **no**) - The serializers: (yes / **no** / don't know) - The runtime per-record code paths (performance sensitive): (yes / **no** / don't know) - Anything that affects deployment or recovery: JobManager (and its components), Checkpointing, Yarn/Mesos, ZooKeeper: (yes / **no** / don't know) - The S3 file system connector: (yes / **no** / don't know) ## Documentation - Does this pull request introduce a new feature? (**yes** / no) - If yes, how is the feature documented? (not applicable / **docs** / JavaDocs / not documented) 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 ISNUMERIC supported in Table API/SQL > > > Key: FLINK- > URL: https://issues.apache.org/jira/browse/FLINK- > 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)
