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

Max Gekk updated SPARK-62149:
-----------------------------
    Description: 
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.


  was:
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.



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