[
https://issues.apache.org/jira/browse/SPARK-62149?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-62149:
-----------------------------------
Labels: pull-request-available (was: )
> 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
> Labels: pull-request-available
>
> 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 an overflow error when
> the result is not representable, as try_add, try_subtract and try_multiply
> do: the minimum value of TINYINT, SMALLINT, INT and BIGINT, the minimum value
> of a YEAR TO MONTH or DAY TO SECOND interval, and, for try_negative, a legacy
> CALENDAR INTERVAL with a field at its minimum. They return NULL there
> whatever spark.sql.ansi.enabled is, as the other try_ arithmetic functions
> do. The result type is that of abs and negative, and the behaviour for
> non-overflowing inputs, NULL, floating-point and decimal types (which cannot
> overflow) is unchanged. An error raised by the argument itself, such as an
> ANSI overflow inside it, is not caught.
> 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]