[jira] [Created] (SPARK-28229) How to implement the same functionality as presto's TRY(expr) ?

["U Shaw (JIRA)" <ji...@apache.org>]

U Shaw created SPARK-28229:
------------------------------

             Summary: How to implement the same functionality as presto's TRY(expr) ?
                 Key: SPARK-28229
                 URL: https://issues.apache.org/jira/browse/SPARK-28229
             Project: Spark
          Issue Type: Question
          Components: SQL
    Affects Versions: 2.4.3
            Reporter: U Shaw



How to implement the same functionality as presto's TRY(expr) ? 
Is there already a similar function?

----------------------------------------------------------------------------------------------
TRY
try(expression)
Evaluate an expression and handle certain types of errors by returning NULL.

In cases where it is preferable that queries produce NULL or default values instead of failing when corrupt or invalid data is encountered, the TRY function may be useful. To specify default values, the TRY function can be used in conjunction with the COALESCE function.

The following errors are handled by TRY:

Division by zero
Invalid cast or function argument
Numeric value out of range
Examples
Source table with some invalid data:

SELECT * FROM shipping;
 origin_state | origin_zip | packages | total_cost
--------------+------------+----------+------------
 California   |      94131 |       25 |        100
 California   |      P332a |        5 |         72
 California   |      94025 |        0 |        155
 New Jersey   |      08544 |      225 |        490
(4 rows)
Query failure without TRY:

SELECT CAST(origin_zip AS BIGINT) FROM shipping;
Query failed: Can not cast 'P332a' to BIGINT
NULL values with TRY:

SELECT TRY(CAST(origin_zip AS BIGINT)) FROM shipping;
 origin_zip
------------
      94131
 NULL
      94025
      08544
(4 rows)
Query failure without TRY:

SELECT total_cost / packages AS per_package FROM shipping;
Query failed: / by zero
Default values with TRY and COALESCE:

SELECT COALESCE(TRY(total_cost / packages), 0) AS per_package FROM shipping;
 per_package
-------------
          4
         14
          0
         19
(4 rows)



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

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