##
24.5. Precision Math Examples

This section provides some examples that show precision math query
results in MySQL 5.1.

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

mysql> `SELECT .1 + .2 = .3;`

+--------------+
| .1 + .2 = .3 |
+--------------+
| 1 |
+--------------+

For floating-point values, results are inexact:

mysql> `SELECT .1E0 + .2E0 = .3E0;`

+--------------------+
| .1E0 + .2E0 = .3E0 |
+--------------------+
| 0 |
+--------------------+

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`

that have scale
`S1`

and `S2`

,
the scale of the result is `S1`

+ `S2`

:

mysql> `SELECT .01 * .01;`

+-----------+
| .01 * .01 |
+-----------+
| 0.0001 |
+-----------+

**Example 3**. Rounding behavior 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 `DECIMAL`

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

mysql> `SELECT ROUND(2.5), ROUND(-2.5);`

+------------+-------------+
| ROUND(2.5) | ROUND(-2.5) |
+------------+-------------+
| 3 | -3 |
+------------+-------------+

However, 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> `SELECT ROUND(2.5E0), ROUND(-2.5E0);`

+--------------+---------------+
| ROUND(2.5E0) | ROUND(-2.5E0) |
+--------------+---------------+
| 2 | -2 |
+--------------+---------------+

**Example 4**. In strict mode,
inserting a value that is too large results in overflow and 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> `SET sql_mode='';`

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

Query OK, 0 rows affected (0.01 sec)
mysql> `INSERT INTO t SET i = 128;`

Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> `SELECT i FROM t;`

+------+
| i |
+------+
| 127 |
+------+
1 row in set (0.00 sec)

Howver, an overflow condition occurs if strict mode is in effect:

mysql> `SET sql_mode='STRICT_ALL_TABLES';`

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

Query OK, 0 rows affected (0.00 sec)
mysql> `INSERT INTO t SET i = 128;`

ERROR 1264 (22003): Out of range value adjusted for column 'i' at row 1
mysql> `SELECT i FROM t;`

Empty set (0.00 sec)

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

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

.

In non-strict mode, division by zero has a result of
`NULL`

:

mysql> `SET sql_mode='';`

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

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

Query OK, 1 row affected (0.00 sec)
mysql> `SELECT i FROM t;`

+------+
| i |
+------+
| NULL |
+------+
1 row in set (0.03 sec)

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

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);`

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

ERROR 1365 (22012): Division by 0
mysql> `SELECT i FROM t;`

Empty set (0.01 sec)

**Example 6**. Prior to MySQL 5.0.3
(before precision math was introduced), both exact-value and
approximate-value literals were converted to double-precision
floating-point values:

mysql> `SELECT VERSION();`

+------------+
| VERSION() |
+------------+
| 4.1.18-log |
+------------+
1 row in set (0.01 sec)
mysql> `CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;`

Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> `DESCRIBE t;`

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

As of MySQL 5.0.3, the approximate-value literal still is
converted to floating-point, but the exact-value literal is
handled as `DECIMAL`

:

mysql> `SELECT VERSION();`

+-----------------+
| VERSION() |
+-----------------+
| 5.1.6-alpha-log |
+-----------------+
1 row in set (0.11 sec)
mysql> `CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;`

Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> `DESCRIBE t;`

+-------+-----------------------+------+-----+---------+-------+
| 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)

**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> `CREATE TABLE t (i INT, d DECIMAL, f FLOAT);`

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

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

Result before MySQL 5.0.3 (prior to the introduction of precision
math in MySQL):

mysql> `DESCRIBE y;`

+--------+--------------+------+-----+---------+-------+
| 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 | |
+--------+--------------+------+-----+---------+-------+

The result is a double no matter the argument type.

Result as of MySQL 5.0.3:

mysql> `DESCRIBE y;`

+--------+---------------+------+-----+---------+-------+
| 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 | |
+--------+---------------+------+-----+---------+-------+

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