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


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