MariaDB ColumnStore has the ability to change intermediate decimal mathematical results from decimal type to double. The decimal type has approximately 17-18 digits of precision, but a smaller maximum range. Whereas the double type has approximately 15-16 digits of precision, but a much larger maximum range. In typical mathematical and scientific applications, the ability to avoid overflow in intermediate results with double math is likely more beneficial than the additional two digits of precisions. In banking applications, however, it may be more appropriate to leave in the default decimal setting to ensure accuracy to the least significant digit.

The infinidb_double_for_decimal_math variable is used to control the data type for intermediate decimal results. This decimal for double math may be set as a default for the instance, set at the session level, or at the statement level by toggling this variable on and off.

To enable/disable the use of the decimal to double math at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

set infinidb_double_for_decimal_math = on

where n is:

- off (disabled, default)
- on (enabled)

ColumnStore has the ability to support varied internal precision on decimal calculations. *infinidb_decimal_scale* is used internally by the ColumnStore engine to control how many significant digits to the right of the decimal point are carried through in suboperations on calculated columns. If, while running a query, you receive the message ‘aggregate overflow’, try reducing *infinidb_decimal_scale* and running the query again. Note that,as you decrease *infinidb_decimal_scale*, you may see reduced accuracy in the least significant digit(s) of a returned calculated column. *infinidb_use_decimal_scale* is used internally by the ColumnStore engine to turn the use of this internal precision on and off. These two system variables may be set as a default for the instance or set at the session level.

To enable/disable the use of the decimal scale at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

set infinidb_use_decimal_scale = on

where *n* is off (disabled) or on (enabled).

To set the decimal scale at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

set infinidb_decimal_scale = n

where *n* is the amount of precision desired for calculations.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

© 2019 MariaDB

Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.

https://mariadb.com/kb/en/columnstore-decimal-math-and-scale/