[SPARK-35161] Error-handling SQL functions for ANSI SQL mode migration

• Type: Umbrella
• Status: Resolved
• Priority: Major
• Resolution: Done
• Affects Version/s: 3.2.0
• Component/s: SQL
• Epic Link: ANSI SQL compliance
• Assignee: Gengliang Wang
• Reporter: Gengliang Wang
• Shepherd: Gengliang Wang
• Created: 21/Apr/21 04:47
• Updated: 10/Aug/24 21:01
• Resolved: 26/Nov/23 07:09

Description

Create new Error-handling version SQL functions for existing SQL functions/operators, which returns NULL if overflow/error occurs. So that:
1. Users can manage to finish queries without interruptions in ANSI mode.
2. Users can get NULLs instead of unreasonable results if overflow occurs when ANSI mode is off.
For example, the behavior of the following SQL operations is unreasonable:

2147483647 + 2 => -2147483647
CAST(2147483648L AS INT) => -2147483648

With the new safe version SQL functions:

TRY_ADD(2147483647, 2) => null
TRY_CAST(2147483648L AS INT) => null

Issue Links

• relates to: SPARK-35030 ANSI SQL compliance

Sub-Tasks

• New SQL Function: TRY_CAST: Resolved
• New SQL functions: TRY_ADD/TRY_DIVIDE: Resolved
• Add docs for try_cast/try_add/try_divide: Resolved
• Enhance the docs and tests of try_add/try_divide: Resolved
• New SQL function: try_element_at: Resolved
• New SQL function: try_subtract and try_multiply: Resolved
• New SQL function: try_sum: Resolved
• New SQL function: try_avg: Resolved
• New SQL function: try_to_binary: Resolved
• Show hints for try_add/try_substract/try_multiply in error messages of int/long overflow: Resolved
• New SQL function: try_to_timestamp: Resolved
• Remove unnecessary TryEval in TrySum: Resolved
• Remove unnecessary TryEval in TryCast: In Progress
• Restore the error handling syntax of try_cast(): Resolved
• New SQL function: get(): Resolved
• Numeric try_add/try_divide/try_subtract/try_multiply should throw error from their children: Resolved
• try_sum() should throw the exceptions from its child: Resolved
• try_avg() should throw the exceptions from its child: Resolved
• try_to_timestamp should handle SparkUpgradeException: Resolved