All Products
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

More information

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.