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

["Hyukjin Kwon (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/SPARK-28229?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Hyukjin Kwon resolved SPARK-28229.
----------------------------------
    Resolution: Invalid

Questions should better be asked to mailing list.

> 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
>            Priority: Major
>
> 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