Search
Document Center

# Floating-point numbers

Last Updated: Jun 18, 2021

Floating-point numbers can have a decimal point anywhere from the first to the last digit, or can have no decimal point at all. You can choose to optionally use an exponent following the number to increase the range, for example, 1.666 e-20. Decimal places are not applicable to floating-point numbers, because the number of digits that can appear after the decimal point is not limited.

Notice

Binary floating-point numbers differ from `NUMBER` in the way where the values are internally stored by ApsaraDB for OceanBase. All values of the `NUMBER` data type are exactly stored with decimal precision. Binary floating-point numbers are stored with binary precision by using the digits 0 and 1. Such a storage method cannot exactly represent all values that use decimal precision.

## Syntax

ApsaraDB for OceanBase provides two numeric data types for floating-point numbers:

• `BINARY_FLOAT` is a 32-bit single-precision floating-point number data type. Each `BINARY_FLOAT` value requires 4 bytes.
• `BINARY_DOUBLE` is a 64-bit double-precision floating-point number data type. Each `BINARY_DOUBLE` value requires 8 bytes.
Note

• In a `NUMBER` column, floating-point numbers have decimal precision.

• In a `BINARY_FLOAT` or `BINARY_DOUBLE` column, floating-point numbers have binary precision.

• The binary floating-point numbers do not support the special values `infinity` and `NaN` (not a number).

## Value range

You can specify floating-point numbers within the value range.

Value

BINARY_FLOAT

BINARY_DOUBLE

Maximum positive finite value

3.40282E+38F

1.79769313486231E+308

Minimum positive finite value

1.17549E-38F

2.22507485850720E-308

### IEEE754 conformance

• IEEE Standard 754-1985 (IEEE754)

The implementation of floating-point data types in ApsaraDB for OceanBase conforms substantially with the Institute of Electrical and Electronics Engineers (IEEE) Standard for Binary Floating-Point Arithmetic.

• The floating-point data types conform to `IEEE754` in the following areas:
• The SQL function `SQRT` returns the square root.

• The SQL function `REMAINDER` returns the remainder.

• Arithmetic operators conform to the standard.

• Comparison operators conform to the standard.

• Conversion operators conform to the standard.

• The default rounding mode is supported.

• The default exception handling mode is supported.

• ApsaraDB for OceanBase does not support the special constants such as `INF`, `-INF`, `NaN`, `BINARY_FLOAT_NAN`, and `BINARY_DOUBLE_NAN`.

• The SQL functions `ROUND`, `TRUNC`, `CEIL`, and `FLOOR` enable the rounding of binary floating-point numbers of `BINARY_FLOAT` and `BINARY_DOUBLE` to integer values of `BINARY_FLOAT` and `BINARY_DOUBLE`.

• The SQL functions `TO_CHAR`, `TO_NUMBER`, `TO_NCHAR`, `TO_BINARY_FLOAT`, `TO_BINARY_DOUBLE`, and `CAST` enable the rounding of binary floating-point numbers of `BINARY_FLOAT` and `BINARY_DOUBLE` to decimal values, and the rounding of decimal values to binary floating-point numbers of `BINARY_FLOAT` and `BINARY_DOUBLE`.

• The floating-point data types do not conform to IEEE754 in the following areas:
• The value of -0 is coerced to that of +0.

• Comparison with `NaN` is not supported.

• Non-default rounding modes are not supported.

• Non-default exception handling modes are not supported.