Numeric data type overflow with SUM, SUM_FLOAT, and AVG
When you use the SUM, SUM_FLOAT, and AVG functions (aggregate and analytic) to query a numeric column, overflow can occur. How Vertica responds to that overflow depends on the settings of two configuration parameters:
 AllowNumericOverflow (Boolean, default 1) allows numeric overflow. Vertica does not implicitly extend precision of numeric data types.
 NumericSumExtraPrecisionDigits (integer, default 6) determines whether to return an overflow error if a result exceeds the specified precision. This parameter is ignored if AllowNumericOverflow is set to 1 (true).
Vertica also allows numeric overflow when you use SUM or SUM_FLOAT to query preaggregated data. See Impact on PreAggregated Data Projections below.
Default overflow handling
With numeric columns, Vertica internally works with multiples of 18 digits. If specified precision is less than 18—for example, x(12,0)
—Vertica allows overflow up to and including the first multiple of 18. In some situations, if you sum a column, you can exceed the number of digits Vertica internally reserves for the result. In this case, Vertica allows silent overflow.
Turning off silent numeric overflow
You can turn off silent numeric overflow by setting AllowNumericOverflow to 0. In this case, Vertica checks the value of configuration parameter NumericSumExtraPrecisionDigits. By default, this parameter is set to 6, which means that Vertica internally adds extra digit places beyond a column's DDLspecified precision. Adding extra precision digits enables Vertica to consistently return results that overflow the column's precision. However, crossing into the second multiple of 18 internally can adversely impact performance.
For example, if AllowNumericOverflow is set to 0 :
 Column
x
is defined asx(12,0)
and NumericSumExtraPrecisionDigits is set to 6: Vertica internally stays within the first multiple of 18 digits and no additional performance impact occurs (a
).  Column
x
is defined asx(2,0)
and NumericSumExtraPrecisionDigits is set to 20: Vertica internally crosses a threshold into the second multiple of 18. In this case, performance is significantly affected (2a
). Performance beyond the second multiple of 18 continues to be2a
.
Tip
Vertica recommends that you turn off silent numeric overflow and set the parameter NumericSumExtraPrecisionDigits if you expect query results to exceed the precision specified in the DDL of numeric columns. Be aware of the following considerations:
 If you turn off AllowNumericOverflow and exceed the number of extra precision digits set by NumericSumExtraPrecisionDigits, Vertica returns an error.
 Be careful to set NumericSumExtraPrecisionDigits only as high as necessary to return the SUM of numeric columns.
Impact on preaggregated data projections
Vertica supports silent numeric overflow for queries that use SUM or SUM_FLOAT on projections with preaggregated data such as live aggregate or TopK projections. To turn off silent numeric overflow for these queries:

Set AllowNumericOverflow to 0.

Set NumericSumExtraPrecisionDigits to the desired number of implicit digits. Alternatively, use the default setting of 6.

Drop and recreate the affected projections.
If you turn off silent numeric overflow, be aware that overflow can sometimes cause rollback or errors:

Overflow occurs during load operations, such as COPY, MERGE, or INSERT:
Vertica aggregates data before loading the projection with data. If overflow occurs while data is aggregated, , Vertica rolls back the load operation.

Overflow occurs after load, while Vertica sums existing data.
Vertica computes the sum of existing data separately from the computation that it does during data load. If the projection selects a column with SUM or SUM_FLOAT and overflow occurs, Vertica produces an error message. This response resembles the way Vertica produces an error for a query that uses SUM or SUM_FLOAT.

Overflow occurs during mergeout.
Vertica logs a message during mergeout if overflow occurs while Vertica computes a final sum during the mergeout operation. If an error occurs, Vertica marks the projection as out of date and disqualifies it from further mergeout operations.