Skip to content

Latest commit

 

History

History
140 lines (95 loc) · 7.9 KB

precision-math.md

File metadata and controls

140 lines (95 loc) · 7.9 KB
Raw
title summary aliases
Precision Math
Learn about the precision math in TiDB.
/docs/dev/functions-and-operators/precision-math/
/docs/dev/reference/sql/functions-and-operators/precision-math/

Precision Math

The precision math support in TiDB is consistent with MySQL. For more information, see Precision Math in MySQL.

Numeric types

The scope of precision math for exact-value operations includes the exact-value data types (integer and DECIMAL types) and exact-value numeric literals. Approximate-value data types and numeric literals are handled as floating-point numbers.

Exact-value numeric literals have an integer part or fractional part, or both. They may be signed. Examples: 1, .2, 3.4, -5, -6.78, +9.10.

Approximate-value numeric literals are represented in scientific notation (power-of-10) with a mantissa and exponent. Either or both parts may be signed. Examples: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3.

Two numbers that look similar might be treated differently. For example, 2.34 is an exact-value (fixed-point) number, whereas 2.34E0 is an approximate-value (floating-point) number.

The DECIMAL data type is a fixed-point type and the calculations are exact. The FLOAT and DOUBLE data types are floating-point types and calculations are approximate.

DECIMAL data type characteristics

This section discusses the following topics of the characteristics of the DECIMAL data type (and its synonyms):

  • Maximum number of digits
  • Storage format
  • Storage requirements

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:

  • M is the maximum number of digits (the precision). 1<= M <= 65.
  • D is the number of digits to the right of the decimal point (the scale). 1 <= D <= 30 and D must be no larger than M.

The maximum value of 65 for M means that calculations on DECIMAL values are accurate up to 65 digits. This limit of 65 digits of precision also applies to exact-value numeric literals.

Values for DECIMAL columns are stored using a binary format that packs 9 decimal digits into 4 bytes. The storage requirements for the integer and fractional parts of each value are determined separately. Each multiple of 9 digits requires 4 bytes, and any remaining digits left over require some fraction of 4 bytes. The storage required for remaining digits is given by the following table.

Leftover Digits Number of Bytes
0 0
1–2 1
3–4 2
5–6 3
7–9 4

For example, a DECIMAL(18,9) column has 9 digits on each side of the decimal point, so the integer part and the fractional part each require 4 bytes. A DECIMAL(20,6) column has 14 integer digits and 6 fractional digits. The integer digits require 4 bytes for 9 of the digits and 3 bytes for the remaining 5 digits. The 6 fractional digits require 3 bytes.

DECIMAL columns do not store a leading + character or - character or leading 0 digits. If you insert +0003.1 into a DECIMAL(5,1) column, it is stored as 3.1. For negative numbers, a literal - character is not stored.

DECIMAL columns do not permit values larger than the range implied by the column definition. For example, a DECIMAL(3,0) column supports a range of -999 to 999. A DECIMAL(M,D) column permits at most M - D digits to the left of the decimal point.

For more information about the internal format of the DECIMAL values, see mydecimal.go in TiDB source code.

Expression handling

For expressions with precision math, TiDB uses the exact-value numbers as given whenever possible. For example, numbers in comparisons are used exactly as given without a change in value. In strict SQL mode, if you add an exact data type into a column, a number is inserted with its exact value if it is within the column range. When retrieved, the value is the same as what is inserted. If strict SQL mode is not enabled, truncation for INSERT is permitted in TiDB.

How to handle a numeric expression depends on the values of the expression:

  • If the expression contains any approximate values, the result is approximate. TiDB evaluates the expression using floating-point arithmetic.
  • If the expression contains no approximate values are present, which means only exact values are contained, and if any exact value contains a fractional part, the expression is evaluated using DECIMAL exact arithmetic and has a precision of 65 digits.
  • Otherwise, the expression contains only integer values. The expression is exact. TiDB evaluates the expression using integer arithmetic and has a precision the same as BIGINT (64 bits).

If a numeric expression contains strings, the strings are converted to double-precision floating-point values and the result of the expression is approximate.

Inserts into numeric columns are affected by the SQL mode. The following discussions mention strict mode and ERROR_FOR_DIVISION_BY_ZERO. To turn on all the restrictions, you can simply use the TRADITIONAL mode, which includes both strict mode values and ERROR_FOR_DIVISION_BY_ZERO:

SET sql_mode = 'TRADITIONAL`;

If a number is inserted into an exact type column (DECIMAL or integer), it is inserted with its exact value if it is within the column range. For this number:

  • If the value has too many digits in the fractional part, rounding occurs and a warning is generated.
  • If the value has too many digits in the integer part, it is too large and is handled as follows:
    • If strict mode is not enabled, the value is truncated to the nearest legal value and a warning is generated.
    • If strict mode is enabled, an overflow error occurs.

To insert strings into numeric columns, TiDB handles the conversion from string to number as follows if the string has nonnumeric contents:

  • In strict mode, a string (including an empty string) that does not begin with a number cannot be used as a number. An error, or a warning occurs.
  • A string that begins with a number can be converted, but the trailing nonnumeric portion is truncated. In strict mode, if the truncated portion contains anything other than spaces, an error, or a warning occurs.

By default, the result of the division by 0 is NULL and no warning. By setting the SQL mode appropriately, division by 0 can be restricted. If you enable the ERROR_FOR_DIVISION_BY_ZERO SQL mode, TiDB handles division by 0 differently:

  • In strict mode, inserts and updates are prohibited, and an error occurs.
  • If it's not in the strict mode, a warning occurs.

In the following SQL statement:

INSERT INTO t SET i = 1/0;

The following results are returned in different SQL modes:

sql_mode Value Result
'' No warning, no error; i is set to NULL.
strict No warning, no error; i is set to NULL.
ERROR_FOR_DIVISION_BY_ZERO Warning, no error; i is set to NULL.
strict, ERROR_FOR_DIVISION_BY_ZERO Error; no row is inserted.

Rounding behavior

The result of the ROUND() function depends on whether its argument is exact or approximate:

  • For exact-value numbers, the ROUND() function uses the "round half up" rule.

  • For approximate-value numbers, the results in TiDB differs from that in MySQL:

    TiDB > SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
|          3 |            3 |
+------------+--------------+
1 row in set (0.00 sec)

For inserts into a DECIMAL or integer column, the rounding uses round half away from zero.

TiDB > CREATE TABLE t (d DECIMAL(10,0));
Query OK, 0 rows affected (0.01 sec)

TiDB > INSERT INTO t VALUES(2.5),(2.5E0);
Query OK, 2 rows affected, 2 warnings (0.00 sec)

TiDB > SELECT d FROM t;
+------+
| d    |
+------+
|    3 |
|    3 |
+------+
2 rows in set (0.00 sec)