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 exact-value columns
(DECIMAL
and integer) 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)
However, 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 nonstrict 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), exact-value and approximate-value literals both are 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.
User Comments
Add your own comment.