This section provides some examples that show precision math query results in MySQL 5.1. These examples demonstrate the principles described in Section 12.18.3, “Expression Handling”, and Section 12.18.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 1 mysql>`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 0 mysql>`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: 0 mysql>`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: 0 mysql>`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.