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