| List: | Commits | « Previous MessageNext Message » | |
| From: | paul | Date: | May 5 2006 4:35pm |
| Subject: | svn commit - mysqldoc@docsrva: r2032 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | ||
| View as plain text | |||
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 |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.644 sec. using MySQL 5.1.14-beta-log