[
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]