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

Max Gekk reassigned SPARK-62149:
--------------------------------

    Assignee: Max Gekk

> Add try_abs and try_negative that return NULL on overflow
> ---------------------------------------------------------
>
>                 Key: SPARK-62149
>                 URL: https://issues.apache.org/jira/browse/SPARK-62149
>             Project: Spark
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 5.0.0
>            Reporter: Max Gekk
>            Assignee: Max Gekk
>            Priority: Major
>
> Spark has try_ counterparts for the operators that raise an error under ANSI 
> mode: try_add, try_subtract, try_multiply, try_divide, try_mod, try_sum and 
> try_avg. They return NULL where the ANSI form raises ARITHMETIC_OVERFLOW or a 
> division error, so a query can keep ANSI semantics everywhere else and 
> tolerate the one input it cannot represent.
> The unary operators have the same overflow input and no try_ form. Under ANSI 
> mode, abs\(x\) and negative\(x\) (-x) raise ARITHMETIC_OVERFLOW for the 
> minimum value of an integral type, for example abs(-2147483648) for INT 
> (SPARK-34742 added the ANSI error for abs). There is no function that returns 
> NULL in that case, so the only alternatives are to turn ANSI mode off for the 
> query or to write a CASE WHEN x = <minimum> THEN NULL ELSE abs\(x\) END by 
> hand per type.
> Proposal: add try_abs(expr) and try_negative(expr), with the semantics of abs 
> and negative, but returning NULL instead of raising ARITHMETIC_OVERFLOW when 
> the result is not representable (the minimum value of TINYINT, SMALLINT, INT 
> and BIGINT; for DECIMAL, when the precision overflows). The result type is 
> that of the input, and the behaviour for non-overflowing inputs, NULL, 
> floating types and interval types is unchanged. With ANSI mode off, they 
> behave as abs and negative do.
> This keeps the try_ family complete for the arithmetic expressions that can 
> raise an overflow error.



--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to