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-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org