On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

## 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.

 Published under the terms of the GNU General Public License Design by Interspire