<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 22. Precision Math</title><link rel="stylesheet" href="mysql-html.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="prev" href="information-schema.html" title="Chapter 21. INFORMATION_SCHEMA Tables"><link rel="next" href="apis.html" title="Chapter 23. APIs and Libraries"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Chapter 22. Precision Math</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="information-schema.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="apis.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="precision-math"></a>Chapter 22. Precision Math</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="precision-math.html#precision-math-numbers">22.1. Types of Numeric Values</a></span></dt><dt><span class="section"><a href="precision-math.html#precision-math-decimal-changes">22.2. <code class="literal">DECIMAL</code> Data Type Changes</a></span></dt><dt><span class="section"><a href="precision-math.html#precision-math-expressions">22.3. Expression Handling</a></span></dt><dt><span class="section"><a href="precision-math.html#precision-math-rounding">22.4. Rounding Behavior</a></span></dt><dt><span class="section"><a href="precision-math.html#precision-math-examples">22.5. Precision Math Examples</a></span></dt></dl></div><a class="indexterm" name="id3018634"></a><a class="indexterm" name="id3018643"></a><a class="indexterm" name="id3018652"></a><a class="indexterm" name="id3018661"></a><a class="indexterm" name="id3018670"></a><a class="indexterm" name="id3018679"></a><a class="indexterm" name="id3018688"></a><a class="indexterm" name="id3018701"></a><a class="indexterm" name="id3018713"></a><a class="indexterm" name="id3018722"></a><a class="indexterm" name="id3018732"></a><a class="indexterm" name="id3018741"></a><p>
MySQL 5.0 introduces precision math: numeric value handling that
results in more accurate results and more control over invalid
values than in earlier versions of MySQL. Precision math is based on
two implementation changes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The introduction of SQL modes in MySQL 5.0 that control how
strict the server is about accepting or rejecting invalid data.
</p></li><li><p>
The introduction in MySQL 5.0.3 of a library for fixed-point
arithmetic.
</p></li></ul></div><p>
These changes have several implications for numeric operations:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<span class="bold"><strong>More precise calculations</strong></span>: For
exact-value numbers, calculations do not introduce
floating-point errors. Instead, exact precision is used. For
example, a number such as <code class="literal">.0001</code> is treated as
an exact value rather than as an approximation, and summing it
10,000 times produces a result of exactly <code class="literal">1</code>,
not a value that merely “<span class="quote">close</span>” to 1.
</p></li><li><p>
<span class="bold"><strong>Well-defined rounding behavior</strong></span>:
For exact-value numbers, the result of
<a href="functions.html#function_round"><code class="literal">ROUND()</code></a> depends on its argument,
not on environmental factors such as how the underlying C
library works.
</p></li><li><p>
<span class="bold"><strong>Improved platform independence</strong></span>:
Operations on exact numeric values are the same across different
platforms such as Windows and Unix.
</p></li><li><p>
<span class="bold"><strong>Control over handling of invalid
values</strong></span>: Overflow and division by zero are detectable
and can be treated as errors. For example, you can treat a value
that is too large for a column as an error rather than having
the value truncated to lie within the range of the column's data
type. Similarly, you can treat division by zero as an error
rather than as an operation that produces a result of
<code class="literal">NULL</code>. The choice of which approach to take is
determined by the setting of the <code class="literal">sql_mode</code>
system variable.
</p></li></ul></div><p>
An important result of these changes is that MySQL provides improved
compliance with standard SQL.
</p><p>
The following discussion covers several aspects of how precision
math works (including possible incompatibilities with older
applications). At the end, some examples are given that demonstrate
how MySQL 5.0 handles numeric operations precisely. For
information about using the <code class="literal">sql_mode</code> system
variable to control the SQL mode, see
<a href="server-administration.html#server-sql-mode" title="5.2.6. SQL Modes">Section 5.2.6, “SQL Modes”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="precision-math-numbers"></a>22.1. Types of Numeric Values</h2></div></div></div><p>
The scope of precision math for exact-value operations includes
the exact-value data types (<code class="literal">DECIMAL</code> and integer
types) and exact-value numeric literals. Approximate-value data
types and numeric literals still are handled as floating-point
numbers.
</p><p>
Exact-value numeric literals have an integer part or fractional
part, or both. They may be signed. Examples: <code class="literal">1</code>,
<code class="literal">.2</code>, <code class="literal">3.4</code>,
<code class="literal">-5</code>, <code class="literal">-6.78</code>,
<code class="literal">+9.10</code>.
</p><p>
Approximate-value numeric literals are represented in scientific
notation with a mantissa and exponent. Either or both parts may be
signed. Examples: <code class="literal">1.2E3</code>,
<code class="literal">1.2E-3</code>, <code class="literal">-1.2E3</code>,
<code class="literal">-1.2E-3</code>.
</p><p>
Two numbers that look similar need not be both exact-value or both
approximate-value. For example, <code class="literal">2.34</code> is an
exact-value (fixed-point) number, whereas
<code class="literal">2.34E0</code> is an approximate-value (floating-point)
number.
</p><p>
The <code class="literal">DECIMAL</code> data type is a fixed-point type and
calculations are exact. In MySQL, the <code class="literal">DECIMAL</code>
type has several synonyms: <code class="literal">NUMERIC</code>,
<code class="literal">DEC</code>, <code class="literal">FIXED</code>. The integer
types also are exact-value types.
</p><p>
The <code class="literal">FLOAT</code> and <code class="literal">DOUBLE</code> data
types are floating-point types and calculations are approximate.
In MySQL, types that are synonymous with <code class="literal">FLOAT</code>
or <code class="literal">DOUBLE</code> are <code class="literal">DOUBLE
PRECISION</code> and <code class="literal">REAL</code>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="precision-math-decimal-changes"></a>22.2. <code class="literal">DECIMAL</code> Data Type Changes</h2></div></div></div><p>
This section discusses the characteristics of the
<code class="literal">DECIMAL</code> data type (and its synonyms) as of
MySQL 5.0.3, with particular regard to the following topics:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Maximum number of digits
</p></li><li><p>
Storage format
</p></li><li><p>
Storage requirements
</p></li><li><p>
The non-standard MySQL extension to the upper range of
<code class="literal">DECIMAL</code> columns
</p></li></ul></div><p>
Some of these changes result in possible incompatibilities for
applications that are written for older versions of MySQL. These
incompatibilities are noted throughout this section.
</p><p>
The declaration syntax for a <code class="literal">DECIMAL</code> column
remains
<code class="literal">DECIMAL(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)</code>,
although the range of values for the arguments has changed
somewhat:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<em class="replaceable"><code>M</code></em> is the maximum number of digits
(the precision). It has a range of 1 to 65. This introduces a
possible incompatibility for older applications, because
previous versions of MySQL allow a range of 1 to 254. (The
precision of 65 digits actually applies as of MySQL 5.0.6.
From 5.0.3 to 5.0.5, the precision is 64 digits.)
</p></li><li><p>
<em class="replaceable"><code>D</code></em> is the number of digits to the
right of the decimal point (the scale). It has a range of 0 to
30 and must be no larger than <em class="replaceable"><code>M</code></em>.
</p></li></ul></div><p>
The maximum value of 65 for <em class="replaceable"><code>M</code></em> means
that calculations on <code class="literal">DECIMAL</code> values are
accurate up to 65 digits. This limit of 65 digits of precision
also applies to exact-value numeric literals, so the maximum range
of such literals is different from before. (Prior to MySQL 5.0.3,
decimal values could have up to 254 digits. However, calculations
were done using floating-point and thus were approximate, not
exact.) This change in the range of literal values is another
possible source of incompatibility for older applications.
</p><p>
Values for <code class="literal">DECIMAL</code> columns no longer are
represented as strings that require one byte per digit or sign
character. Instead, a binary format is used that packs nine
decimal digits into four bytes. This change to
<code class="literal">DECIMAL</code> storage format changes the storage
requirements as well. The storage requirements for the integer and
fractional parts of each value are determined separately. Each
multiple of nine digits requires four bytes, and any digits left
over require some fraction of four bytes. For example, a
<code class="literal">DECIMAL(18,9)</code> column has nine digits on either
side of the decimal point, so the integer part and the fractional
part each require four bytes. A <code class="literal">DECIMAL(20,10)</code>
column has ten digits on either side of the decimal point. Each
part requires four bytes for nine of the digits, and one byte for
the remaining digit.
</p><p>
The storage required for leftover digits is given by the following
table:
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Leftover Digits</strong></span></td><td><span class="bold"><strong>Number of Bytes</strong></span></td></tr><tr><td>0</td><td>0</td></tr><tr><td>1</td><td>1</td></tr><tr><td>2</td><td>1</td></tr><tr><td>3</td><td>2</td></tr><tr><td>4</td><td>2</td></tr><tr><td>5</td><td>3</td></tr><tr><td>6</td><td>3</td></tr><tr><td>7</td><td>4</td></tr><tr><td>8</td><td>4</td></tr><tr><td>9</td><td>4</td></tr></tbody></table></div><p>
As a result of the change from string to numeric format for
<code class="literal">DECIMAL</code> storage, <code class="literal">DECIMAL</code>
columns no longer store a leading <code class="literal">+</code> or
<code class="literal">-</code> character or leading <code class="literal">0</code>
digits. Before MySQL 5.0.3, if you inserted
<code class="literal">+0003.1</code> into a <code class="literal">DECIMAL(5,1)</code>
column, it was stored as <code class="literal">+0003.1</code>. As of MySQL
5.0.3, it is stored as <code class="literal">3.1</code>. For negative
numbers, a literal <code class="literal">-</code> character is no longer
stored. Applications that rely on the older behavior must be
modified to account for this change.
</p><p>
The change of storage format also means that
<code class="literal">DECIMAL</code> columns no longer support the
non-standard extension that allowed values larger than the range
implied by the column definition. Formerly, one byte was allocated
for storing the sign character. For positive values that needed no
sign byte, MySQL allowed an extra digit to be stored instead. For
example, a <code class="literal">DECIMAL(3,0)</code> column must support a
range of at least <code class="literal"> –999</code> to
<code class="literal">999</code>, but MySQL would allow storing values from
<code class="literal">1000</code> to <code class="literal">9999</code> as well, by
using the sign byte to store an extra digit. This extension to the
upper range of <code class="literal">DECIMAL</code> columns no longer is
allowed. In MySQL 5.0.3 and up, a
<code class="literal">DECIMAL(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)</code>
column allows at most <em class="replaceable"><code>M</code></em> -
<em class="replaceable"><code>D</code></em> digits to the left of the decimal
point. This can result in an incompatibility if an application has
a reliance on MySQL allowing “<span class="quote">too-large</span>” values.
</p><p>
The SQL standard requires that the precision of
<code class="literal">NUMERIC(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)</code>
be <span class="emphasis"><em>exactly</em></span> <em class="replaceable"><code>M</code></em>
digits. For
<code class="literal">DECIMAL(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)</code>,
the standard requires a precision of at least
<em class="replaceable"><code>M</code></em> digits but allows more. In MySQL,
<code class="literal">DECIMAL(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)</code>
and
<code class="literal">NUMERIC(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)</code>
are the same, and both have a precision of exactly
<em class="replaceable"><code>M</code></em> digits.
</p><p>
Summary of incompatibilities:
</p><p>
The following list summarizes the incompatibilities that result
from changes to <code class="literal">DECIMAL</code> column and value
handling. You can use it as guide when porting older applications
for use with MySQL 5.0.3 and up.
</p><div class="itemizedlist"><ul type="disc"><li><p>
For
<code class="literal">DECIMAL(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)</code>,
the maximum <em class="replaceable"><code>M</code></em> is 65, not 254.
</p></li><li><p>
Calculations involving exact-value decimal numbers are
accurate to 65 digits. This is fewer than the maximum number
of digits allowed before MySQL 5.0.3 (254 digits), but the
exact-value precision is greater. Calculations formerly were
done with double-precision floating-point, which has a
precision of 52 bits (about 15 decimal digits).
</p></li><li><p>
The non-standard MySQL extension to the upper range of
<code class="literal">DECIMAL</code> columns no longer is supported.
</p></li><li><p>
Leading “<span class="quote"><code class="literal">+</code></span>” and
“<span class="quote"><code class="literal">0</code></span>” characters are not stored.
</p></li></ul></div><p>
The behavior used by the server for <code class="literal">DECIMAL</code>
columns in a table depends on the version of MySQL used to create
the table. If your server is from MySQL 5.0.3 or higher, but you
have <code class="literal">DECIMAL</code> columns in tables that were
created before 5.0.3, the old behavior still applies to those
columns. To convert the tables to the newer
<code class="literal">DECIMAL</code> format, dump them with
<span><strong class="command">mysqldump</strong></span> and reload them.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="precision-math-expressions"></a>22.3. Expression Handling</h2></div></div></div><p>
With precision math, exact-value numbers are used as given
whenever possible. For example, numbers in comparisons are used
exactly as given without a change in value. In strict SQL mode,
for <code class="literal">INSERT</code> into a column with an exact data
type (<code class="literal">DECIMAL</code> or integer), a number is inserted
with its exact value if it is within the column range. When
retrieved, the value should be the same as what was inserted.
(Without strict mode, truncation for <code class="literal">INSERT</code> is
allowable.)
</p><p>
Handling of a numeric expression depends on what kind of values
the expression contains:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If any approximate values are present, the expression is
approximate and is evaluated using floating-point arithmetic.
</p></li><li><p>
If no approximate values are present, the expression contains
only exact values. If any exact value contains a fractional
part (a value following the decimal point), the expression is
evaluated using <code class="literal">DECIMAL</code> exact arithmetic
and has a precision of 65 digits. (The term
“<span class="quote">exact</span>” is subject to the limits of what can be
represented in binary. For example, <code class="literal">1.0/3.0</code>
can be approximated in decimal notation as
<code class="literal">.333...</code>, but not written as an exact
number, so <code class="literal">(1.0/3.0)*3.0</code> does not evaluate
to exactly <code class="literal">1.0</code>.)
</p></li><li><p>
Otherwise, the expression contains only integer values. The
expression is exact and is evaluated using integer arithmetic
and has a precision the same as <code class="literal">BIGINT</code> (64
bits).
</p></li></ul></div><p>
If a numeric expression contains any strings, they are converted
to double-precision floating-point values and the expression is
approximate.
</p><p>
Inserts into numeric columns are affected by the SQL mode, which
is controlled by the <code class="literal">sql_mode</code> system variable.
(See <a href="server-administration.html#server-sql-mode" title="5.2.6. SQL Modes">Section 5.2.6, “SQL Modes”</a>.) The following discussion
mentions strict mode (selected by the
<code class="literal">STRICT_ALL_TABLES</code> or
<code class="literal">STRICT_TRANS_TABLES</code> mode values) and
<code class="literal">ERROR_FOR_DIVISION_BY_ZERO</code>. To turn on all
restrictions, you can simply use <code class="literal">TRADITIONAL</code>
mode, which includes both strict mode values and
<code class="literal">ERROR_FOR_DIVISION_BY_ZERO</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET sql_mode='TRADITIONAL';</code></strong>
</pre><p>
If a number is inserted into an exact type column
(<code class="literal">DECIMAL</code> or integer), it is inserted with its
exact value if it is within the column range.
</p><p>
If the value has too many digits in the fractional part, rounding
occurs and a warning is generated. Rounding is done as described
in <a href="precision-math.html#precision-math-rounding" title="22.4. Rounding Behavior">Section 22.4, “Rounding Behavior”</a>.
</p><p>
If the value has too many digits in the integer part, it is too
large and is handled as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If strict mode is not enabled, the value is truncated to the
nearest legal value and a warning is generated.
</p></li><li><p>
If strict mode is enabled, an overflow error occurs.
</p></li></ul></div><p>
Underflow is not detected, so underflow handing is undefined.
</p><p>
By default, division by zero produces a result of
<code class="literal">NULL</code> and no warning. With the
<code class="literal">ERROR_FOR_DIVISION_BY_ZERO</code> SQL mode enabled,
MySQL handles division by zero differently:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If strict mode is not enabled, a warning occurs.
</p></li><li><p>
If strict mode is enabled, inserts and updates involving
division by zero are prohibited, and an error occurs.
</p></li></ul></div><p>
In other words, inserts and updates involving expressions that
perform division by zero can be treated as errors, but this
requires <code class="literal">ERROR_FOR_DIVISION_BY_ZERO</code> in addition
to strict mode.
</p><p>
Suppose that we have this statement:
</p><pre class="programlisting">INSERT INTO t SET i = 1/0;
</pre><p>
This is what happens for combinations of strict and
<code class="literal">ERROR_FOR_DIVISION_BY_ZERO</code> modes:
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong><code class="literal">sql_mode</code> Value</strong></span></td><td><span class="bold"><strong>Result</strong></span></td></tr><tr><td><code class="literal">''</code> (Default)</td><td>No warning, no error; <code class="literal">i</code> is set to
<code class="literal">NULL</code>.</td></tr><tr><td>strict</td><td>No warning, no error; <code class="literal">i</code> is set to
<code class="literal">NULL</code>.</td></tr><tr><td><code class="literal">ERROR_FOR_DIVISION_BY_ZERO</code></td><td>Warning, no error; <code class="literal">i</code> is set to
<code class="literal">NULL</code>.</td></tr><tr><td>strict,<code class="literal">ERROR_FOR_DIVISION_BY_ZERO</code></td><td>Error condition; no row is inserted.</td></tr></tbody></table></div><p>
For inserts of strings into numeric columns, conversion from
string to number is handled as follows if the string has
non-numeric contents:
</p><div class="itemizedlist"><ul type="disc"><li><p>
A string that does not begin with a number cannot be used as a
number and produces an error in strict mode, or a warning
otherwise. <span class="emphasis"><em>This includes the empty
string</em></span>.
</p></li><li><p>
A string that begins with a number can be converted, but the
trailing non-numeric portion is truncated. If the truncated
portion contains anything other than spaces, this produces an
error in strict mode, or a warning otherwise.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="precision-math-rounding"></a>22.4. Rounding Behavior</h2></div></div></div><p>
This section discusses precision math rounding for the
<a href="functions.html#function_round"><code class="literal">ROUND()</code></a> function and for inserts
into columns with exact-value types (<code class="literal">DECIMAL</code>
and integer).
</p><p>
The <a href="functions.html#function_round"><code class="literal">ROUND()</code></a> function rounds
differently depending on whether its argument is exact or
approximate:
</p><div class="itemizedlist"><ul type="disc"><li><p>
For exact-value numbers,
<a href="functions.html#function_round"><code class="literal">ROUND()</code></a> uses the “<span class="quote">round
half up</span>” rule: A value with a fractional part of .5 or
greater is rounded up to the next integer if positive or down
to the next integer if negative. (In other words, it is
rounded away from zero.) A value with a fractional part less
than .5 is rounded down to the next integer if positive or up
to the next integer if negative.
</p></li><li><p>
For approximate-value numbers, the result depends on the C
library. On many systems, this means that
<a href="functions.html#function_round"><code class="literal">ROUND()</code></a> uses the “<span class="quote">round
to nearest even</span>” rule: A value with any fractional part
is rounded to the nearest even integer.
</p></li></ul></div><p>
The following example shows how rounding differs for exact and
approximate values:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT ROUND(2.5), ROUND(25E-1);</code></strong>
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3 | 2 |
+------------+--------------+
</pre><p>
For inserts into a <code class="literal">DECIMAL</code> or integer column,
the target is an exact data type, so rounding uses “<span class="quote">round
half up,</span>” regardless of whether the value to be inserted is
exact or approximate:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t (d DECIMAL(10,0));</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>INSERT INTO t VALUES(2.5),(2.5E0);</code></strong>
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 2
mysql> <strong class="userinput"><code>SELECT d FROM t;</code></strong>
+------+
| d |
+------+
| 3 |
| 3 |
+------+
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="precision-math-examples"></a>22.5. Precision Math Examples</h2></div></div></div><p>
This section provides some examples that show how precision math
improves query results in MySQL 5 compared to older versions.
</p><p>
<span class="bold"><strong>Example 1</strong></span>. Numbers are used with
their exact value as given when possible.
</p><p>
Before MySQL 5.0.3, numbers that are treated as floating-point
values produce inexact results:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT .1 + .2 = .3;</code></strong>
+--------------+
| .1 + .2 = .3 |
+--------------+
| 0 |
+--------------+
</pre><p>
As of MySQL 5.0.3, numbers are used as given when possible:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT .1 + .2 = .3;</code></strong>
+--------------+
| .1 + .2 = .3 |
+--------------+
| 1 |
+--------------+
</pre><p>
For floating-point values, results are inexact:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT .1E0 + .2E0 = .3E0;</code></strong>
+--------------------+
| .1E0 + .2E0 = .3E0 |
+--------------------+
| 0 |
+--------------------+
</pre><p>
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
<code class="literal">.0001</code> to a variable 1,000 times.
</p><pre class="programlisting">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;
</pre><p>
The sum for both <code class="literal">d</code> and <code class="literal">f</code>
logically should be 1, but that is true only for the decimal
calculation. The floating-point calculation introduces small
errors:
</p><pre class="programlisting">+--------+------------------+
| d | f |
+--------+------------------+
| 1.0000 | 0.99999999999991 |
+--------+------------------+
</pre><p>
<span class="bold"><strong>Example 2</strong></span>. Multiplication is
performed with the scale required by standard SQL. That is, for
two numbers <em class="replaceable"><code>X1</code></em> and
<em class="replaceable"><code>X2</code></em> that have scale
<em class="replaceable"><code>S1</code></em> and <em class="replaceable"><code>S2</code></em>,
the scale of the result is <code class="literal"><em class="replaceable"><code>S1</code></em>
+ <em class="replaceable"><code>S2</code></em></code>:
</p><p>
Before MySQL 5.0.3, this is what happens:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT .01 * .01;</code></strong>
+-----------+
| .01 * .01 |
+-----------+
| 0.00 |
+-----------+
</pre><p>
The displayed value is incorrect. The value was calculated
correctly in this case, but not displayed to the required scale.
To see that the calculated value actually was .0001, try this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT .01 * .01 + .0000;</code></strong>
+-------------------+
| .01 * .01 + .0000 |
+-------------------+
| 0.0001 |
+-------------------+
</pre><p>
As of MySQL 5.0.3, the displayed scale is correct:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT .01 * .01;</code></strong>
+-----------+
| .01 * .01 |
+-----------+
| 0.0001 |
+-----------+
</pre><p>
<span class="bold"><strong>Example 3</strong></span>. Rounding behavior is
well-defined.
</p><p>
Before MySQL 5.0.3, rounding behavior (for example, with the
<a href="functions.html#function_round"><code class="literal">ROUND()</code></a> function) is dependent on
the implementation of the underlying C library. This results in
inconsistencies from platform to platform. For example, you might
get a different value on Windows than on Linux, or a different
value on x86 machines than on PowerPC machines.
</p><p>
As of MySQL 5.0.3, rounding happens like this:
</p><p>
Rounding for exact-value columns (<code class="literal">DECIMAL</code> and
integer) and exact-valued numbers uses the “<span class="quote">round half
up</span>” rule. Values with a fractional part of .5 or greater
are rounded away from zero to the nearest integer, as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT ROUND(2.5), ROUND(-2.5);</code></strong>
+------------+-------------+
| ROUND(2.5) | ROUND(-2.5) |
+------------+-------------+
| 3 | -3 |
+------------+-------------+
</pre><p>
However, rounding for floating-point values uses the C library,
which on many systems uses the “<span class="quote">round to nearest
even</span>” rule. Values with any fractional part on such systems
are rounded to the nearest even integer:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT ROUND(2.5E0), ROUND(-2.5E0);</code></strong>
+--------------+---------------+
| ROUND(2.5E0) | ROUND(-2.5E0) |
+--------------+---------------+
| 2 | -2 |
+--------------+---------------+
</pre><p>
<span class="bold"><strong>Example 4</strong></span>. In strict mode,
inserting a value that is too large results in overflow and causes
an error, rather than truncation to a legal value.
</p><p>
Before MySQL 5.0.2 (or in 5.0.2 and later, without strict mode),
truncation to a legal value occurs:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t (i TINYINT);</code></strong>
Query OK, 0 rows affected (0.01 sec)
mysql> <strong class="userinput"><code>INSERT INTO t SET i = 128;</code></strong>
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> <strong class="userinput"><code>SELECT i FROM t;</code></strong>
+------+
| i |
+------+
| 127 |
+------+
1 row in set (0.00 sec)
</pre><p>
As of MySQL 5.0.2, overflow occurs if strict mode is in effect:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET sql_mode='STRICT_ALL_TABLES';</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>CREATE TABLE t (i TINYINT);</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>INSERT INTO t SET i = 128;</code></strong>
ERROR 1264 (22003): Out of range value adjusted for column 'i' at row 1
mysql> <strong class="userinput"><code>SELECT i FROM t;</code></strong>
Empty set (0.00 sec)
</pre><p>
<span class="bold"><strong>Example 5</strong></span>: In strict mode and
with <code class="literal">ERROR_FOR_DIVISION_BY_ZERO</code> set, division
by zero causes an error, and not a result of
<code class="literal">NULL</code>.
</p><p>
Before MySQL 5.0.2 (or when not using strict mode in 5.0.2 or a
later version), division by zero has a result of
<code class="literal">NULL</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t (i TINYINT);</code></strong>
Query OK, 0 rows affected (0.01 sec)
mysql> <strong class="userinput"><code>INSERT INTO t SET i = 1 / 0;</code></strong>
Query OK, 1 row affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT i FROM t;</code></strong>
+------+
| i |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
</pre><p>
As of MySQL 5.0.2, division by zero is an error if the proper SQL
modes are in effect:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>CREATE TABLE t (i TINYINT);</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>INSERT INTO t SET i = 1 / 0;</code></strong>
ERROR 1365 (22012): Division by 0
mysql> <strong class="userinput"><code>SELECT i FROM t;</code></strong>
Empty set (0.01 sec)
</pre><p>
<span class="bold"><strong>Example 6</strong></span>. 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:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT VERSION();</code></strong>
+------------+
| VERSION() |
+------------+
| 4.1.18-log |
+------------+
1 row in set (0.01 sec)
mysql> <strong class="userinput"><code>CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;</code></strong>
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> <strong class="userinput"><code>DESCRIBE t;</code></strong>
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | double(3,1) | | | 0.0 | |
| b | double | | | 0 | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)
</pre><p>
As of MySQL 5.0.3, the approximate-value literal still is
converted to floating-point, but the exact-value literal is
handled as <code class="literal">DECIMAL</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT VERSION();</code></strong>
+------------+
| VERSION() |
+------------+
| 5.0.19-log |
+------------+
1 row in set (0.17 sec)
mysql> <strong class="userinput"><code>CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;</code></strong>
Query OK, 1 row affected (0.19 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> <strong class="userinput"><code>DESCRIBE t;</code></strong>
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| a | decimal(2,1) unsigned | NO | | 0.0 | |
| b | double | NO | | 0 | |
+-------+-----------------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
</pre><p>
<span class="bold"><strong>Example 7</strong></span>. 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.
</p><p>
Consider these statements:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t (i INT, d DECIMAL, f FLOAT);</code></strong>
mysql> <strong class="userinput"><code>INSERT INTO t VALUES(1,1,1);</code></strong>
mysql> <strong class="userinput"><code>CREATE TABLE y SELECT AVG(i), AVG(d), AVG(f) FROM t;</code></strong>
</pre><p>
Result before MySQL 5.0.3 (prior to the introduction of precision
math in MySQL):
</p><pre class="programlisting">mysql> <strong class="userinput"><code>DESCRIBE y;</code></strong>
+--------+--------------+------+-----+---------+-------+
| 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 | |
+--------+--------------+------+-----+---------+-------+
</pre><p>
The result is a double no matter the argument type.
</p><p>
Result as of MySQL 5.0.3:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>DESCRIBE y;</code></strong>
+--------+---------------+------+-----+---------+-------+
| 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 | |
+--------+---------------+------+-----+---------+-------+
</pre><p>
The result is a double only for the floating-point argument. For
exact type arguments, the result is also an exact type. (From
MySQL 5.0.3 to 5.0.6, the first two columns are
<code class="literal">DECIMAL(64,0)</code>.)
</p></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="information-schema.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="apis.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 21. <code class="literal">INFORMATION_SCHEMA</code> Tables </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 23. APIs and Libraries</td></tr></table></div></body></html>
distrib
>
Mandriva
>
2008.1
>
x86_64
>
media
>
main-testing
>
by-pkgid
>
b1e2421f2416edfc24c5845fbc1c5a2e
>
files
>
104
