Author: paul
Date: 2006-05-05 16:35:33 +0200 (Fri, 05 May 2006)
New Revision: 2032
Log:
r10138@frost: paul | 2006-05-05 09:28:53 -0500
DECIMAL columns created before 5.0.3 have old behavior even
as of 5.0.3 unless converted to new format. (Bug#19200)
Modified:
trunk/
trunk/refman-4.1/problems.xml
trunk/refman-5.0/data-types.xml
trunk/refman-5.0/problems.xml
trunk/refman-5.1/problems.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:7268
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:10071
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:4886
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:7268
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:10138
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:4886
Modified: trunk/refman-4.1/problems.xml
===================================================================
--- trunk/refman-4.1/problems.xml 2006-05-05 13:17:10 UTC (rev 2031)
+++ trunk/refman-4.1/problems.xml 2006-05-05 14:35:33 UTC (rev 2032)
@@ -3921,43 +3921,45 @@
<title>&title-problems-with-float;</title>
<para>
- Note that the following section is relevant primarily for
- versions of MySQL older than 5.0.3. As of version 5.0.3, MySQL
+ Floating-point numbers sometimes cause confusion because they
+ are approximate. That is, they are not stored as exact values
+ inside computer architecture. What you can see on the screen
+ usually is not the exact value of the number. The
+ <literal>FLOAT</literal> and <literal>DOUBLE</literal> data
+ types are such, and <literal>DECIMAL</literal> operations before
+ MySQL 5.0.3 are approximate as well.
+ </para>
+
+ <para>
+ Prior to MySQL 5.0.3, <literal>DECIMAL</literal> columns store
+ values with exact precision because they are represented as
+ strings, but calculations on <literal>DECIMAL</literal> values
+ are done using floating-point operations. As of 5.0.3, MySQL
performs <literal>DECIMAL</literal> operations with a precision
of 64 decimal digits, which should solve most common inaccuracy
problems when it comes to <literal>DECIMAL</literal> columns.
- For <literal>DOUBLE</literal> and <literal>FLOAT</literal>
- columns, the problems remain because inexactness is the basic
- nature of floating point numbers.
+ (If your server is from MySQL 5.0.3 or higher, but you have
+ <literal>DECIMAL</literal> 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 <literal>DECIMAL</literal>
+ format, dump them with <command>mysqldump</command> and reload
+ them.)
</para>
- <remark role="todo">
- update for precision math
- </remark>
-
<para>
- Floating-point numbers sometimes cause confusion because they
- are not stored as exact values inside computer architecture.
- What you can see on the screen usually is not the exact value of
- the number. The data types <literal>FLOAT</literal>,
- <literal>DOUBLE</literal>, and <literal>DECIMAL</literal> are
- such. <literal>DECIMAL</literal> columns store values with exact
- precision because they are represented as strings, but
- calculations on <literal>DECIMAL</literal> values before MySQL
- 5.0.3 are done using floating-point operations.
- </para>
-
- <para>
- The following example (for older MySQL version than 5.0.3)
- demonstrate the problem. It shows that even for the
- <literal>DECIMAL</literal> data type, calculations that are done
+ The following example (for versions of MySQL older than 5.0.3)
+ demonstrates the problem. It shows that even for older
+ <literal>DECIMAL</literal> columns, calculations that are done
using floating-point operations are subject to floating-point
- error. (In all MySQL versions, you would have similar problems
- if you would replace the <literal>DECIMAL</literal> columns with
- <literal>FLOAT</literal>).
+ error. (Were you to replace the <literal>DECIMAL</literal>
+ columns with <literal>FLOAT</literal>, similar problems would
+ occur for all versions of MySQL.)
</para>
<programlisting>
+<!--
+mysql> DROP TABLE IF EXISTS t1;
+-->
mysql> <userinput>CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2));</userinput>
mysql> <userinput>INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),</userinput>
-> <userinput>(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),</userinput>
@@ -3982,11 +3984,13 @@
<para>
The result is correct. Although the first five records look like
- they shouldn't pass the comparison test (the values of
+ they should not satisfy the comparison (the values of
<literal>a</literal> and <literal>b</literal> do not appear to
be different), they may do so because the difference between the
numbers shows up around the tenth decimal or so, depending on
- computer architecture.
+ factors such as computer architecture or the compiler version or
+ optimization level. For example, different CPUs may evaluate
+ floating-point numbers differently.
</para>
<para>
@@ -4037,10 +4041,9 @@
<para>
Depending on your computer architecture, you may or may not see
- similar results. Different CPUs may evaluate floating-point
- numbers differently. For example, on some machines you may get
- the <quote>correct</quote> results by multiplying both arguments
- by 1, as the following example shows.
+ similar results. For example, on some machines you may get the
+ <quote>correct</quote> results by multiplying both arguments by
+ 1, as the following example shows.
</para>
<para>
Modified: trunk/refman-5.0/data-types.xml
===================================================================
--- trunk/refman-5.0/data-types.xml 2006-05-05 13:17:10 UTC (rev 2031)
+++ trunk/refman-5.0/data-types.xml 2006-05-05 14:35:33 UTC (rev 2032)
@@ -840,6 +840,18 @@
</para>
<remark role="help-description-end"/>
+
+ <para>
+ The behavior used by the server for
+ <literal>DECIMAL</literal> 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
+ <literal>DECIMAL</literal> 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
+ <literal>DECIMAL</literal> format, dump them with
+ <command>mysqldump</command> and reload them.
+ </para>
</listitem>
<listitem>
Modified: trunk/refman-5.0/problems.xml
===================================================================
--- trunk/refman-5.0/problems.xml 2006-05-05 13:17:10 UTC (rev 2031)
+++ trunk/refman-5.0/problems.xml 2006-05-05 14:35:33 UTC (rev 2032)
@@ -3884,43 +3884,45 @@
<title>&title-problems-with-float;</title>
<para>
- Note that the following section is relevant primarily for
- versions of MySQL older than 5.0.3. As of version 5.0.3, MySQL
+ Floating-point numbers sometimes cause confusion because they
+ are approximate. That is, they are not stored as exact values
+ inside computer architecture. What you can see on the screen
+ usually is not the exact value of the number. The
+ <literal>FLOAT</literal> and <literal>DOUBLE</literal> data
+ types are such, and <literal>DECIMAL</literal> operations before
+ MySQL 5.0.3 are approximate as well.
+ </para>
+
+ <para>
+ Prior to MySQL 5.0.3, <literal>DECIMAL</literal> columns store
+ values with exact precision because they are represented as
+ strings, but calculations on <literal>DECIMAL</literal> values
+ are done using floating-point operations. As of 5.0.3, MySQL
performs <literal>DECIMAL</literal> operations with a precision
of 64 decimal digits, which should solve most common inaccuracy
problems when it comes to <literal>DECIMAL</literal> columns.
- For <literal>DOUBLE</literal> and <literal>FLOAT</literal>
- columns, the problems remain because inexactness is the basic
- nature of floating point numbers.
+ (If your server is from MySQL 5.0.3 or higher, but you have
+ <literal>DECIMAL</literal> 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 <literal>DECIMAL</literal>
+ format, dump them with <command>mysqldump</command> and reload
+ them.)
</para>
- <remark role="todo">
- update for precision math
- </remark>
-
<para>
- Floating-point numbers sometimes cause confusion because they
- are not stored as exact values inside computer architecture.
- What you can see on the screen usually is not the exact value of
- the number. The data types <literal>FLOAT</literal>,
- <literal>DOUBLE</literal>, and <literal>DECIMAL</literal> are
- such. <literal>DECIMAL</literal> columns store values with exact
- precision because they are represented as strings, but
- calculations on <literal>DECIMAL</literal> values before MySQL
- 5.0.3 are done using floating-point operations.
- </para>
-
- <para>
- The following example (for older MySQL version than 5.0.3)
- demonstrate the problem. It shows that even for the
- <literal>DECIMAL</literal> data type, calculations that are done
+ The following example (for versions of MySQL older than 5.0.3)
+ demonstrates the problem. It shows that even for older
+ <literal>DECIMAL</literal> columns, calculations that are done
using floating-point operations are subject to floating-point
- error. (In all MySQL versions, you would have similar problems
- if you would replace the <literal>DECIMAL</literal> columns with
- <literal>FLOAT</literal>).
+ error. (Were you to replace the <literal>DECIMAL</literal>
+ columns with <literal>FLOAT</literal>, similar problems would
+ occur for all versions of MySQL.)
</para>
<programlisting>
+<!--
+mysql> DROP TABLE IF EXISTS t1;
+-->
mysql> <userinput>CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2));</userinput>
mysql> <userinput>INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),</userinput>
-> <userinput>(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),</userinput>
@@ -3945,11 +3947,13 @@
<para>
The result is correct. Although the first five records look like
- they shouldn't pass the comparison test (the values of
+ they should not satisfy the comparison (the values of
<literal>a</literal> and <literal>b</literal> do not appear to
be different), they may do so because the difference between the
numbers shows up around the tenth decimal or so, depending on
- computer architecture.
+ factors such as computer architecture or the compiler version or
+ optimization level. For example, different CPUs may evaluate
+ floating-point numbers differently.
</para>
<para>
@@ -4000,10 +4004,9 @@
<para>
Depending on your computer architecture, you may or may not see
- similar results. Different CPUs may evaluate floating-point
- numbers differently. For example, on some machines you may get
- the <quote>correct</quote> results by multiplying both arguments
- by 1, as the following example shows.
+ similar results. For example, on some machines you may get the
+ <quote>correct</quote> results by multiplying both arguments by
+ 1, as the following example shows.
</para>
<para>
Modified: trunk/refman-5.1/problems.xml
===================================================================
--- trunk/refman-5.1/problems.xml 2006-05-05 13:17:10 UTC (rev 2031)
+++ trunk/refman-5.1/problems.xml 2006-05-05 14:35:33 UTC (rev 2032)
@@ -3869,35 +3869,27 @@
<title>&title-problems-with-float;</title>
<para>
- Note that the following section is relevant primarily to working
- with <literal>DOUBLE</literal> and <literal>FLOAT</literal>
- columns, due to the inexact nature of floating point numbers.
- MySQL performs <literal>DECIMAL</literal> operations with a
- precision of 64 decimal digits, which should solve most common
- inaccuracy problems when it comes to <literal>DECIMAL</literal>
- columns.
- </para>
-
- <remark role="todo">
- update for precision math
- </remark>
-
- <para>
Floating-point numbers sometimes cause confusion because they
- are not stored as exact values inside computer architecture.
- What you can see on the screen usually is not the exact value of
- the number. The data types <literal>FLOAT</literal> and
- <literal>DOUBLE</literal> are such. <literal>DECIMAL</literal>
- columns store values with exact precision because they are
- represented as strings.
+ are approximate. That is, they are not stored as exact values
+ inside computer architecture. What you can see on the screen
+ usually is not the exact value of the number. The
+ <literal>FLOAT</literal> and <literal>DOUBLE</literal> data
+ types are such. For <literal>DECIMAL</literal> columns, MySQL
+ performs operations with a precision of 64 decimal digits, which
+ should solve most common inaccuracy problems.
</para>
<para>
The following example demonstrates the problem using
- <literal>DOUBLE</literal>:
+ <literal>DOUBLE</literal>. It shows that are calculations that
+ are done using floating-point operations are subject to
+ floating-point error.
</para>
<programlisting>
+<!--
+mysql> DROP TABLE IF EXISTS t1;
+-->
mysql> <userinput>CREATE TABLE t1 (i INT, d1 DOUBLE, d2 DOUBLE);</userinput>
mysql> <userinput>INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),</userinput>
-> <userinput>(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),</userinput>
@@ -3923,11 +3915,13 @@
<para>
The result is correct. Although the first five records look like
- they shouldn't pass the comparison test (the values of
+ they should not satisfy the comparison (the values of
<literal>a</literal> and <literal>b</literal> do not appear to
be different), they may do so because the difference between the
numbers shows up around the tenth decimal or so, depending on
- computer architecture.
+ factors such as computer architecture or the compiler version or
+ optimization level. For example, different CPUs may evaluate
+ floating-point numbers differently.
</para>
<para>
@@ -3938,71 +3932,7 @@
row — the last one shown above.
</para>
- <remark role="todo">
- [js] Need to rewrite the remainder of this section, which I have
- commented out because it does not apply in 5.1
- </remark>
-
-<!--
<para>
- This is what the numbers in column <literal>a</literal> look
- like when displayed with more decimal places:
- </para>
-
-<programlisting>
-mysql> <userinput>SELECT i, ROUND(SUM(d1), 2)*1.0000000000000000 AS a,</userinput>
- -> <userinput>ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i HAVING a <> b;</userinput>
--->
-
- <remark>
- +------+----------------------+-------+ | i | a | b |
- +------+----------------------+-------+ | 1 |
- 21.3999999999999986 | 21.40 | | 2 | 76.7999999999999972 | 76.80
- | | 3 | 7.4000000000000004 | 7.40 | | 4 | 15.4000000000000004 |
- 15.40 | | 5 | 7.2000000000000002 | 7.20 | | 6 |
- -51.3999999999999986 | 0.00 |
- +------+----------------------+-------+
- </remark>
-
-<!--
-</programlisting>
-
- <para>
- Depending on your computer architecture, you may or may not see
- similar results. Different CPUs may evaluate floating-point
- numbers differently. For example, on some machines you may get
- the <quote>correct</quote> results by multiplying both arguments
- by 1, as the following example shows.
- </para>
-
- <para>
- <emphasis role="bold">Warning:</emphasis> Never use this method
- in your applications. It is not an example of a trustworthy
- method!
- </para>
-
-<programlisting>
-mysql> <userinput>SELECT i, ROUND(SUM(d1), 2)*1 AS a, ROUND(SUM(d2), 2)*1 AS b</userinput>
- -> <userinput>FROM t1 GROUP BY i HAVING a <> b;</userinput>
--->
-
- <remark>
- +------+--------+------+ | i | a | b | +------+--------+------+
- | 6 | -51.40 | 0.00 | +------+--------+------+
- </remark>
-
-<!--
-</programlisting>
-
- <para>
- The reason that the preceding example seems to work is that on
- the particular machine where the test was done, CPU
- floating-point arithmetic happens to round the numbers to the
- same value. However, there is no rule that any CPU should do so,
- so this method cannot be trusted.
- </para>
-
- <para>
The correct way to do floating-point number comparison is to
first decide on an acceptable tolerance for differences between
the numbers and then do the comparison against the tolerance
@@ -4015,14 +3945,11 @@
<programlisting>
mysql> <userinput>SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1</userinput>
-> <userinput>GROUP BY i HAVING ABS(a - b) > 0.0001;</userinput>
--->
-
- <remark>
- +------+--------+------+ | i | a | b | +------+--------+------+
- | 6 | -51.40 | 0.00 | +------+--------+------+
- </remark>
-
-<!--
++------+-------+------+
+| i | a | b |
++------+-------+------+
+| 6 | -51.4 | 0 |
++------+-------+------+
1 row in set (0.00 sec)
</programlisting>
@@ -4034,18 +3961,17 @@
<programlisting>
mysql> <userinput>SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1</userinput>
-> <userinput>GROUP BY i HAVING ABS(a - b) <= 0.0001;</userinput>
--->
-
- <remark>
- +------+-------+-------+ | i | a | b | +------+-------+-------+
- | 1 | 21.40 | 21.40 | | 2 | 76.80 | 76.80 | | 3 | 7.40 | 7.40 |
- | 4 | 15.40 | 15.40 | | 5 | 7.20 | 7.20 |
- +------+-------+-------+
- </remark>
-
-<!--
++------+------+------+
+| i | a | b |
++------+------+------+
+| 1 | 21.4 | 21.4 |
+| 2 | 76.8 | 76.8 |
+| 3 | 7.4 | 7.4 |
+| 4 | 15.4 | 15.4 |
+| 5 | 7.2 | 7.2 |
++------+------+------+
+5 rows in set (0.03 sec)
</programlisting>
--->
</section>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r2032 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 5 May |
