This section provides some examples that show precision math query results in MySQL 5.7. These examples demonstrate the principles described in Section 12.19.3, "Expression Handling", and Section 12.19.4, "Rounding Behavior".

**Example 1**. Numbers are used with their exact value as given when
possible:

mysql>+----------------+| (.1 + .2) = .3 |+----------------+| 1 |+----------------+`SELECT (.1 + .2) = .3;`

For floating-point values, results are inexact:

mysql>+----------------------+| (.1E0 + .2E0) = .3E0 |+----------------------+| 0 |+----------------------+`SELECT (.1E0 + .2E0) = .3E0;`

Another way to see the difference in exact and approximate value handling is to add a small number to a sum many
times. Consider the following stored procedure, which adds `.0001`

to a variable
1,000 times.

CREATE PROCEDURE p ()BEGIN DECLARE i INT DEFAULT 0; DECLARE d DECIMAL(10,4) DEFAULT 0; DECLARE f FLOAT DEFAULT 0; WHILE i < 10000 DO SET d = d + .0001; SET f = f + .0001E0; SET i = i + 1; END WHILE; SELECT d, f;END;

The sum for both `d`

and `f`

logically should be 1, but
that is true only for the decimal calculation. The floating-point calculation introduces small errors:

+--------+------------------+| d | f |+--------+------------------+| 1.0000 | 0.99999999999991 |+--------+------------------+

**Example 2**. Multiplication is performed with the scale required by
standard SQL. That is, for two numbers * X1* and

`X2`

`S1`

`S2`

`S1`

+ `S2`

: mysql>+-----------+| .01 * .01 |+-----------+| 0.0001 |+-----------+`SELECT .01 * .01;`

**Example 3**. Rounding behavior for exact-value numbers is well-defined:

Rounding behavior (for example, with the `ROUND()`

function) is independent of the implementation of the underlying C
library, which means that results are consistent from platform to platform.

Rounding for exact-value columns (

`DECIMAL`

and integer) and exact-valued numbers uses the "round half away from zero" rule. Values with a fractional part of .5 or greater are rounded away from zero to the nearest integer, as shown here:mysql>

+------------+-------------+| ROUND(2.5) | ROUND(-2.5) |+------------+-------------+| 3 | -3 |+------------+-------------+`SELECT ROUND(2.5), ROUND(-2.5);`

Rounding for floating-point values uses the C library, which on many systems uses the "round to nearest even" rule. Values with any fractional part on such systems are rounded to the nearest even integer:

mysql>

+--------------+---------------+| ROUND(2.5E0) | ROUND(-2.5E0) |+--------------+---------------+| 2 | -2 |+--------------+---------------+`SELECT ROUND(2.5E0), ROUND(-2.5E0);`

**Example 4**. In strict mode, inserting a value that is out of range for
a column causes an error, rather than truncation to a legal value.

When MySQL is not running in strict mode, truncation to a legal value occurs:

mysql>Query OK, 0 rows affected (0.00 sec)mysql>`SET sql_mode='';`

Query OK, 0 rows affected (0.01 sec)mysql>`CREATE TABLE t (i TINYINT);`

Query OK, 1 row affected, 1 warning (0.00 sec)mysql>`INSERT INTO t SET i = 128;`

+------+| i |+------+| 127 |+------+1 row in set (0.00 sec)`SELECT i FROM t;`

However, an error occurs if strict mode is in effect:

mysql>Query OK, 0 rows affected (0.00 sec)mysql>`SET sql_mode='STRICT_ALL_TABLES';`

Query OK, 0 rows affected (0.00 sec)mysql>`CREATE TABLE t (i TINYINT);`

ERROR 1264 (22003): Out of range value adjusted for column 'i' at row 1mysql>`INSERT INTO t SET i = 128;`

Empty set (0.00 sec)`SELECT i FROM t;`

**Example 5**: In strict mode and with `ERROR_FOR_DIVISION_BY_ZERO`

set, division by zero causes an error, not a result of
`NULL`

.

In nonstrict mode, division by zero has a result of `NULL`

:

mysql>Query OK, 0 rows affected (0.01 sec)mysql>`SET sql_mode='';`

Query OK, 0 rows affected (0.00 sec)mysql>`CREATE TABLE t (i TINYINT);`

Query OK, 1 row affected (0.00 sec)mysql>`INSERT INTO t SET i = 1 / 0;`

+------+| i |+------+| NULL |+------+1 row in set (0.03 sec)`SELECT i FROM t;`

However, division by zero is an error if the proper SQL modes are in effect:

mysql>Query OK, 0 rows affected (0.00 sec)mysql>`SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';`

Query OK, 0 rows affected (0.00 sec)mysql>`CREATE TABLE t (i TINYINT);`

ERROR 1365 (22012): Division by 0mysql>`INSERT INTO t SET i = 1 / 0;`

Empty set (0.01 sec)`SELECT i FROM t;`

**Example 6**. Exact-value literals are evaluated as exact values.

Prior to MySQL 5.0.3, exact-value and approximate-value literals both are evaluated as double-precision floating-point values:

mysql>+------------+| VERSION() |+------------+| 4.1.18-log |+------------+1 row in set (0.01 sec)mysql>`SELECT VERSION();`

Query OK, 1 row affected (0.07 sec)Records: 1 Duplicates: 0 Warnings: 0mysql>`CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;`

+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| a | double(3,1) | | | 0.0 | || b | double | | | 0 | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.04 sec)`DESCRIBE t;`

As of MySQL 5.0.3, the approximate-value literal is evaluated using floating point, but the exact-value literal
is handled as `DECIMAL`

:

mysql>+-----------------+| VERSION() |+-----------------+| 5.1.6-alpha-log |+-----------------+1 row in set (0.11 sec)mysql>`SELECT VERSION();`

Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql>`CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;`

+-------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| a | decimal(2,1) unsigned | NO | | 0.0 | || b | double | NO | | 0 | |+-------+-----------------------+------+-----+---------+-------+2 rows in set (0.01 sec)`DESCRIBE t;`

**Example 7**. If the argument to an aggregate function is an exact
numeric type, the result is also an exact numeric type, with a scale at least that of the argument.

Consider these statements:

mysql>mysql>`CREATE TABLE t (i INT, d DECIMAL, f FLOAT);`

mysql>`INSERT INTO t VALUES(1,1,1);`

`CREATE TABLE y SELECT AVG(i), AVG(d), AVG(f) FROM t;`

Before MySQL 5.0.3, the result is a double no matter the argument type:

mysql>+--------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+--------------+------+-----+---------+-------+| AVG(i) | double(17,4) | YES | | NULL | || AVG(d) | double(17,4) | YES | | NULL | || AVG(f) | double | YES | | NULL | |+--------+--------------+------+-----+---------+-------+`DESCRIBE y;`

As of MySQL 5.0.3, the result is a double only for the floating-point argument. For exact type arguments, the result is also an exact type:

mysql>+--------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+---------------+------+-----+---------+-------+| AVG(i) | decimal(14,4) | YES | | NULL | || AVG(d) | decimal(14,4) | YES | | NULL | || AVG(f) | double | YES | | NULL | |+--------+---------------+------+-----+---------+-------+`DESCRIBE y;`

The result is a double only for the floating-point argument. For exact type arguments, the result is also an exact type.

Spec-Zone.ru - all specs in one place