Disabling Numeric Overflow

Prior to version 8.0, using a column that has a numeric data type with the functions SUM, SUM_FLOAT, or AVG could result in numeric overflow. When using SUM, SUM_FLOAT, or AVG functions with a numeric data type, Vertica would silently overflow if the result exceeded the precision you set when defining the numeric column.

Now, you have the ability to turn-off numeric overflow and add implicit precision to your numeric data types.

As of 8.0, you have two options:

• Use the default overflow handling. You won’t experience performance impact, but may encounter numeric overflow.
• Turn off silent numeric overflow and specify an extra precision value to handle larger results correctly. If the specified precision is exceeded, Vertica provides an error message. This option has a modest performance impact.

The NUMERIC data type

Numeric data types are data types that store values of a specified precision and scale.

NUMERIC [ ( precision [ , scale ] ) ]

The precision is the total number of significant digits and the scale represents the number of digits to the right of the decimal point.

For example, a money value of $123.45 can be stored in a NUMERIC(5,2) field.

If you try to insert a numeric value that lies outside the numeric field, Vertica returns an error similar to the following:

=> INSERT INTO my_table VALUES (1234.56)

      ERROR 5411:  Value exceeds range of type numeric(5,2)

If you assign a value with more decimal digits than scale, the value is rounded to scale digits.

=> INSERT INTO my_table VALUES (123.456)

=> SELECT * FROM my_table;

     num

   ——–

   123.46

   (1 row)

When a value can be incorrect: NUMERIC with SUM and AVG

While Vertica provides an error when you try to insert an unacceptable value, Vertica does not automatically provide an error while using SUM, SUM_FLOAT, and AVG functions with numeric datatypes. To make Vertica return an error in these cases, use the new ability to turn numeric overflow off.

Turning silent numeric overflow off

You can turn off silent numeric overflow and instruct Vertica to implicitly include extra digit places. Adding extra precision digits allows Vertica to consistently return your expected results, even when you exceed the precision specified in your DDL.

To manage silent numeric overflow, we’ve added two new configuration parameters in version 8.0:

• AllowNumericOverflow – Default (1) allows silent numeric overflow. Set to 0 to turn off silent numeric overflow.
• NumericSumExtraPrecisionDigits – Applies when AllowNumericOverflow is off (0). NumericSumExtraPrecisionDigits can be set to values 0-20 (default is 6). The set value extends your NUMERIC field, allowing Vertica to store larger numbers without overflow. If the value exceeds the precision set by NumericSumExtraPrecisionDigits, Vertica returns an error.

With NUMERIC data types, Vertica internally works with multiples of 18 digits. If your specified precision is less than 18, Vertica allows for an overflow up to and including the first multiple of 18.

Example 1 – viewing and setting parameters

Use the following example to view and view the AllowNumericOverflow parameter and turn silent numeric overflow off:

=> SHOW CURRENT allownumericoverflow;

  level  |         name         | setting

———+———————-+———

 DEFAULT | AllowNumericOverflow | 1

(1 row)

=> ALTER DATABASE my_db SET PARAMETER allownumericoverflow=0;

ALTER DATABASE

=> SHOW CURRENT allownumericoverflow;

  level   |         name         | setting

———-+———————-+———

 DATABASE | AllowNumericOverflow | 0

(1 row)

Example 2 – overflow in action

Suppose you specify a column of type NUMERIC(11,5). If you turn silent numeric overflow off and accept the default of NumericSumExtraPrecisionDigits (6), Vertica internally add six places beyond your DDL-specified precision (11). In this case, Vertica internally stays within the first multiple of 18 digits and no additional performance impact occurs (11+6 < 18).

Given the same example, if you set NumericSumExtraPrecisionDigits to 10, Vertica internally crosses a threshold into the second multiple of 18 (11+10 > 18). This situation will result in an increased performance impact.

The following graphic shows the above two situations in reference to the multiples of 18 digits that Vertica works with:

You can see that you’ll need to balance your need for precise results with performance impact. We recommend that you turn off silent numeric overflow if you expect to exceed the precision specified in your DDL. Extending your NUMERIC field to the point where you cross into the second multiple of 18 can affect performance, so consider adding only what you need to store expected results.

Learn more

To learn more about NUMERIC overflow in Vertica, including the impact on live aggregate projections, see the links below:

Impact on Live Aggregate Projections (LAPs)

Setting NUMERIC Precision

New Features in 8.0