ARITHMETIC_OVERFLOW error class

  • Article

SQLSTATE: 22003

<message>.<alternative> If necessary set <config> to "false" to bypass this error.

Parameters

  • message: A description of the expression causing the overflow.
  • alternative: Advise on how to avoid the error.
  • config: The configuration setting to alter ANSI mode.

Explanation

An arithmetic overflow occurs when Azure Databricks performs a mathematical operation that exceeds the maximum range of the data type in which the operation is performed.

In many cases math is performed in the least-common type of the operands of an operator, or the least-common type of the arguments of a function.

Adding two numbers of type TINYINT can quickly exceed the types range which is limited from -128 to +127. Other types such as TIMESTAMP and INTERVAL also have a large, but finite range.

For a definition of the domain of a type see the definition for the data type.

Mitigation

The mitigation for this error depends on the cause:

  • Are the math or any of the input arguments incorrect?

    Correct the functions used or the input data as appropriate.

    You may also consider reordering operations to keep intermediate results in the desired range.

  • Is the data type not the widest type?

    Widen the type by casting one of the arguments to a type sufficient to complete the operation.

    Choosing DOUBLE or DECIMAL(38, s) with an appropriate s provides a lot of range at the cost of rounding.

  • Can you tolerate overflow conditions and replace them with NULL?

    Change the expression to use the function proposed in alternative. For example use try_sum instead of sum.

  • You cannot change the expression and you rather get wrapped results than return an error?

    As a last resort, disable ANSI mode by setting the ansiConfig to false.

Examples

-- An overflow of a small numeric
> SELECT 100Y * 100Y;
 [ARITHMETIC_OVERFLOW] 100S * 100S caused overflow.
 If necessary set ansi_mode to "false" (except for ANSI interval type) to bypass this error.

-- Use a wider numeric to perform the operation by casting one of the operands
> SELECT 100Y * cast(100Y AS INTEGER);
 10000

-- An overflow of a complex expression which can be rewritten
> SELECT 100Y * 10Y / 5;
 [ARITHMETIC_OVERFLOW] 100S * 10S caused overflow.
 If necessary set spark.sql.ansi.enabled to "false" (except for ANSI interval type) to bypass this error.

-- Rewrite the expression
> SELECT 100Y / 5 * 10Y;
 200.0

-- An occasional overfklow that should be tolerated
> SELECT arg1 * arg2 FROM VALUES(100Y, 100Y), (20Y, 5Y) AS t(arg1, arg2);
 [ARITHMETIC_OVERFLOW] 100S * 100S caused overflow.
 If necessary set ansi_mode to "false" (except for ANSI interval type) to bypass this error.

-- Allowing overflows to be treated as NULL
> SELECT try_multiply(arg1, arg2) FROM VALUES(100Y, 100Y), (20Y, 5Y) AS t(arg1, arg2);
  NULL
  100

-- In Databricks SQL temporarily disable ANSI mode to tolerate incorrect overflow.
> SET ANSI_MODE = false;
> SELECT arg1 * arg2 FROM VALUES(100Y, 100Y), (20Y, 5Y) AS t(arg1, arg2);
  16
  100
> SET ANSI_MODE = true;

-- In Databricks Runtime temporarily disable ANSI mode to tolerate incorrect overflow.
> SET spark.sql.ansi.enabled = false;
> SELECT arg1 * arg2 FROM VALUES(100Y, 100Y), (20Y, 5Y) AS t(arg1, arg2);
  16
  100
> SET spark.sql.ansi.enabled = true;