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. The following two configuration parameters determine how OpenText™ Analytics Database responds to the overflow:

• AllowNumericOverflow (Boolean, default 1) allows numeric overflow. The database 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).

The database also allows numeric overflow when you use SUM or SUM_FLOAT to query pre-aggregated data. See Impact on Pre-Aggregated Data Projections below.

Default overflow handling

With numeric columns, the database internally works with multiples of 18 digits. If specified precision is less than 18—for example, x(12,0)—The database 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 the database internally reserves for the result. In this case, the database allows silent overflow.

Turning off silent numeric overflow

You can turn off silent numeric overflow by setting AllowNumericOverflow to 0. In this case, the database checks the value of configuration parameter NumericSumExtraPrecisionDigits. By default, this parameter is set to 6, which means that the database internally adds extra digit places beyond a column's DDL-specified precision. Adding extra precision digits enables the database 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 as x(12,0)and NumericSumExtraPrecisionDigits is set to 6: The database internally stays within the first multiple of 18 digits and no additional performance impact occurs (a).
• Column x is defined as x(2,0)and NumericSumExtraPrecisionDigits is set to 20: The database 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 be 2a.
  

Impact on pre-aggregated data projections

The database supports silent numeric overflow for queries that use SUM or SUM_FLOAT on projections with pre-aggregated data such as live aggregate or Top-K projections. To turn off silent numeric overflow for these queries:

1. Set AllowNumericOverflow to 0.

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

3. Drop and re-create 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:

  The database aggregates data before loading the projection with data. If overflow occurs while data is aggregated, the database rolls back the load operation.

• Overflow occurs after load operations, while the database sums existing data.

  The database 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, the database produces an error message.

• Overflow occurs during mergeout.

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