[spark] branch master updated: [SPARK-38590][SQL] New SQL function: try_to_binary

[ma...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

maxgekk pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git


The following commit(s) were added to refs/heads/master by this push:
     new becda333938 [SPARK-38590][SQL] New SQL function: try_to_binary
becda333938 is described below

commit becda3339381b3975ed567c156260eda036d7a1b
Author: Gengliang Wang <ge...@apache.org>
AuthorDate: Thu Apr 7 08:52:50 2022 +0300

    [SPARK-38590][SQL] New SQL function: try_to_binary
    
    ### What changes were proposed in this pull request?
    
    Add a new SQL function: `try_to_binary`. It is identical to the function `to_binary`, except that it returns NULL results instead of throwing an exception on encoding errors.
    There is a similar function in Snowflake: https://docs.snowflake.com/en/sql-reference/functions/try_to_binary.html
    
    ### Why are the changes needed?
    
    Users can manage to finish queries without interruptions by encoding errors.
    
    ### Does this PR introduce _any_ user-facing change?
    
    Yes, adding a new SQL function: `try_to_binary`. It is identical to the function `to_binary`, except that it returns NULL results instead of throwing an exception on encoding errors.
    
    ### How was this patch tested?
    
    UT
    
    Closes #35897 from gengliangwang/try_to_binary.
    
    Authored-by: Gengliang Wang <ge...@apache.org>
    Signed-off-by: Max Gekk <ma...@gmail.com>
---
 .../sql/catalyst/analysis/FunctionRegistry.scala   |   1 +
 .../spark/sql/catalyst/expressions/TryEval.scala   |  35 +++++++++++++++++++++
 .../catalyst/expressions/stringExpressions.scala   |  24 ++++++++------
 .../sql-functions/sql-expression-schema.md         |   3 +-
 .../sql-tests/inputs/string-functions.sql          |   4 ++-
 .../sql-tests/inputs/try-string-functions.sql      |  21 +++++++++++++
 .../results/ansi/string-functions.sql.out          |  11 ++++++-
 .../sql-tests/results/string-functions.sql.out     |  11 ++++++-
 .../sql-tests/results/try-string-functions.sql.out | Bin 0 -> 1925 bytes
 9 files changed, 97 insertions(+), 13 deletions(-)

diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
index bb4aa701102..5befa779d16 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
@@ -454,6 +454,7 @@ object FunctionRegistry {
     expression[TryMultiply]("try_multiply"),
     expression[TryElementAt]("try_element_at"),
     expression[TrySum]("try_sum"),
+    expression[TryToBinary]("try_to_binary"),
 
     // aggregate functions
     expression[HyperLogLogPlusPlus]("approx_count_distinct"),
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/TryEval.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/TryEval.scala
index 7a8a689a1bd..589e5801424 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/TryEval.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/TryEval.scala
@@ -181,3 +181,38 @@ case class TryMultiply(left: Expression, right: Expression, replacement: Express
   override protected def withNewChildInternal(newChild: Expression): Expression =
     this.copy(replacement = newChild)
 }
+
+// scalastyle:off line.size.limit
+@ExpressionDescription(
+  usage = "_FUNC_(str[, fmt]) - This is a special version of `to_binary` that performs the same operation, but returns a NULL value instead of raising an error if the conversion cannot be performed.",
+  examples = """
+    Examples:
+      > SELECT _FUNC_('abc', 'utf-8');
+       abc
+      > select _FUNC_('a!', 'base64');
+       NULL
+      > select _FUNC_('abc', 'invalidFormat');
+       NULL
+  """,
+  since = "3.3.0",
+  group = "string_funcs")
+// scalastyle:on line.size.limit
+case class TryToBinary(
+    expr: Expression,
+    format: Option[Expression],
+    replacement: Expression) extends RuntimeReplaceable
+  with InheritAnalysisRules {
+  def this(expr: Expression) =
+    this(expr, None, TryEval(ToBinary(expr, None, nullOnInvalidFormat = true)))
+
+  def this(expr: Expression, formatExpression: Expression) =
+    this(expr, Some(formatExpression),
+      TryEval(ToBinary(expr, Some(formatExpression), nullOnInvalidFormat = true)))
+
+  override def prettyName: String = "try_to_binary"
+
+  override def parameters: Seq[Expression] = expr +: format.toSeq
+
+  override protected def withNewChildInternal(newChild: Expression): Expression =
+    this.copy(replacement = newChild)
+}
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala
index a08ab84ac6f..88045f85bca 100755
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala
@@ -2638,7 +2638,10 @@ case class Encode(value: Expression, charset: Expression)
   since = "3.3.0",
   group = "string_funcs")
 // scalastyle:on line.size.limit
-case class ToBinary(expr: Expression, format: Option[Expression]) extends RuntimeReplaceable
+case class ToBinary(
+    expr: Expression,
+    format: Option[Expression],
+    nullOnInvalidFormat: Boolean = false) extends RuntimeReplaceable
   with ImplicitCastInputTypes {
 
   override lazy val replacement: Expression = format.map { f =>
@@ -2651,6 +2654,7 @@ case class ToBinary(expr: Expression, format: Option[Expression]) extends Runtim
         case "hex" => Unhex(expr)
         case "utf-8" => Encode(expr, Literal("UTF-8"))
         case "base64" => UnBase64(expr)
+        case _ if nullOnInvalidFormat => Literal(null, BinaryType)
         case other => throw QueryCompilationErrors.invalidStringLiteralParameter(
           "to_binary", "format", other,
           Some("The value has to be a case-insensitive string literal of " +
@@ -2659,16 +2663,18 @@ case class ToBinary(expr: Expression, format: Option[Expression]) extends Runtim
     }
   }.getOrElse(Unhex(expr))
 
-  def this(expr: Expression) = this(expr, None)
+  def this(expr: Expression) = this(expr, None, false)
 
   def this(expr: Expression, format: Expression) = this(expr, Some({
-    // We perform this check in the constructor to make it eager and not go through type coercion.
-    if (format.foldable && (format.dataType == StringType || format.dataType == NullType)) {
-      format
-    } else {
-      throw QueryCompilationErrors.requireLiteralParameter("to_binary", "format", "string")
-    }
-  }))
+      // We perform this check in the constructor to make it eager and not go through type coercion.
+      if (format.foldable && (format.dataType == StringType || format.dataType == NullType)) {
+        format
+      } else {
+        throw QueryCompilationErrors.requireLiteralParameter("to_binary", "format", "string")
+      }
+    }),
+    false
+    )
 
   override def prettyName: String = "to_binary"
 
diff --git a/sql/core/src/test/resources/sql-functions/sql-expression-schema.md b/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
index 644bfa926da..1dbf9678af9 100644
--- a/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
+++ b/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
@@ -1,6 +1,6 @@
 <!-- Automatically generated by ExpressionsSchemaSuite -->
 ## Summary
-  - Number of queries: 385
+  - Number of queries: 386
   - Number of expressions that missing example: 12
   - Expressions missing examples: bigint,binary,boolean,date,decimal,double,float,int,smallint,string,timestamp,tinyint
 ## Schema of Built-in Functions
@@ -316,6 +316,7 @@
 | org.apache.spark.sql.catalyst.expressions.TryElementAt | try_element_at | SELECT try_element_at(array(1, 2, 3), 2) | struct<try_element_at(array(1, 2, 3), 2):int> |
 | org.apache.spark.sql.catalyst.expressions.TryMultiply | try_multiply | SELECT try_multiply(2, 3) | struct<try_multiply(2, 3):int> |
 | org.apache.spark.sql.catalyst.expressions.TrySubtract | try_subtract | SELECT try_subtract(2, 1) | struct<try_subtract(2, 1):int> |
+| org.apache.spark.sql.catalyst.expressions.TryToBinary | try_to_binary | SELECT try_to_binary('abc', 'utf-8') | struct<try_to_binary(abc, utf-8):binary> |
 | org.apache.spark.sql.catalyst.expressions.TypeOf | typeof | SELECT typeof(1) | struct<typeof(1):string> |
 | org.apache.spark.sql.catalyst.expressions.UnBase64 | unbase64 | SELECT unbase64('U3BhcmsgU1FM') | struct<unbase64(U3BhcmsgU1FM):binary> |
 | org.apache.spark.sql.catalyst.expressions.UnaryMinus | negative | SELECT negative(1) | struct<negative(1):int> |
diff --git a/sql/core/src/test/resources/sql-tests/inputs/string-functions.sql b/sql/core/src/test/resources/sql-tests/inputs/string-functions.sql
index 7d22e791570..0db28ad9f3e 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/string-functions.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/string-functions.sql
@@ -184,5 +184,7 @@ select to_binary(null, cast(null as string));
 -- 'format' parameter must be string type or void type.
 select to_binary(null, cast(null as int));
 select to_binary('abc', 1);
--- invalid inputs.
+-- invalid format
 select to_binary('abc', 'invalidFormat');
+-- invalid string input
+select to_binary('a!', 'base64');
diff --git a/sql/core/src/test/resources/sql-tests/inputs/try-string-functions.sql b/sql/core/src/test/resources/sql-tests/inputs/try-string-functions.sql
new file mode 100644
index 00000000000..20f02374e78
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/try-string-functions.sql
@@ -0,0 +1,21 @@
+-- try_to_binary
+select try_to_binary('abc');
+select try_to_binary('abc', 'utf-8');
+select try_to_binary('abc', 'base64');
+select try_to_binary('abc', 'hex');
+-- 'format' parameter can be any foldable string value, not just literal.
+select try_to_binary('abc', concat('utf', '-8'));
+-- 'format' parameter is case insensitive.
+select try_to_binary('abc', 'Hex');
+-- null inputs lead to null result.
+select try_to_binary('abc', null);
+select try_to_binary(null, 'utf-8');
+select try_to_binary(null, null);
+select try_to_binary(null, cast(null as string));
+-- 'format' parameter must be string type or void type.
+select try_to_binary(null, cast(null as int));
+select try_to_binary('abc', 1);
+-- invalid format
+select try_to_binary('abc', 'invalidFormat');
+-- invalid string input
+select try_to_binary('a!', 'base64');
diff --git a/sql/core/src/test/resources/sql-tests/results/ansi/string-functions.sql.out b/sql/core/src/test/resources/sql-tests/results/ansi/string-functions.sql.out
index 01213bd57ad..52d70e22a44 100644
--- a/sql/core/src/test/resources/sql-tests/results/ansi/string-functions.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/ansi/string-functions.sql.out
@@ -1,5 +1,5 @@
 -- Automatically generated by SQLQueryTestSuite
--- Number of queries: 141
+-- Number of queries: 142
 
 
 -- !query
@@ -1140,3 +1140,12 @@ struct<>
 -- !query output
 org.apache.spark.sql.AnalysisException
 Invalid value for the 'format' parameter of function 'to_binary': invalidformat. The value has to be a case-insensitive string literal of 'hex', 'utf-8', or 'base64'.
+
+
+-- !query
+select to_binary('a!', 'base64')
+-- !query schema
+struct<>
+-- !query output
+java.lang.IllegalArgumentException
+Last unit does not have enough valid bits
diff --git a/sql/core/src/test/resources/sql-tests/results/string-functions.sql.out b/sql/core/src/test/resources/sql-tests/results/string-functions.sql.out
index 3a7f197e362..ff14da143da 100644
--- a/sql/core/src/test/resources/sql-tests/results/string-functions.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/string-functions.sql.out
@@ -1,5 +1,5 @@
 -- Automatically generated by SQLQueryTestSuite
--- Number of queries: 141
+-- Number of queries: 142
 
 
 -- !query
@@ -1136,3 +1136,12 @@ struct<>
 -- !query output
 org.apache.spark.sql.AnalysisException
 Invalid value for the 'format' parameter of function 'to_binary': invalidformat. The value has to be a case-insensitive string literal of 'hex', 'utf-8', or 'base64'.
+
+
+-- !query
+select to_binary('a!', 'base64')
+-- !query schema
+struct<>
+-- !query output
+java.lang.IllegalArgumentException
+Last unit does not have enough valid bits
diff --git a/sql/core/src/test/resources/sql-tests/results/try-string-functions.sql.out b/sql/core/src/test/resources/sql-tests/results/try-string-functions.sql.out
new file mode 100644
index 00000000000..bda723fd19e
Binary files /dev/null and b/sql/core/src/test/resources/sql-tests/results/try-string-functions.sql.out differ


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org