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

Reply via email to