| Bug #10781 | Precision value exceeds the maximum range when multiplied. | ||
|---|---|---|---|
| Submitted: | 20 May 2005 22:20 | Modified: | 21 May 2005 17:14 |
| Reporter: | Disha | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.5-beta-nt | OS: | Windows (Windows 2003 Standard Edition) |
| Assigned to: | CPU Architecture: | Any | |
[21 May 2005 1:57]
MySQL Verification Team
miguel@hegel:~/dbs/5.0$ bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 5.0.6-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> delimiter // mysql> use test1// Database changed mysql> Create table t1 (f1 dec(64,30),f2 dec(64,30))// Query OK, 0 rows affected (0.01 sec) mysql> Insert into t1 values (-2.234,2)// Query OK, 1 row affected (0.00 sec) mysql> Select f1*f2 from t1// +-----------------------------------+ | f1*f2 | +-----------------------------------+ | -4.468000000000000000000000000000 | +-----------------------------------+ 1 row in set (0.00 sec) c:\mysql\bin>mysqladmin -uroot create test1 c:\mysql\bin>mysql -uroot test1 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.6-beta-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> delimiter // mysql> use test1 // Database changed mysql> Create table t1 (f1 dec(64,30),f2 dec(64,30))// Query OK, 0 rows affected (0.09 sec) mysql> Insert into t1 values (-2.234,2)// Query OK, 1 row affected (0.02 sec) mysql> Select f1*f2 from t1// +-----------------------------------+ | f1*f2 | +-----------------------------------+ | -4.468000000000000000000000000000 | +-----------------------------------+ 1 row in set (0.00 sec) mysql>
[21 May 2005 17:14]
Sergei Golubchik
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: It should not "display the output in the decimal range (M,D) ie (64,30)" According to the SQL:2003, part 2, Foundation, paragraph 6.26 <numeric value expression>: c) The precision of the result of multiplication is implementation-defined, and the scale is S1 + S2. So the result should be at least (61, 60). But as MySQL 5.0 does not support scale higher than 32, it truncates the number and returns DECIMAL (32,31).
Description: We create a table 't1' with 2 columns of decimal datatype with (M,D) as (64,30), and insert some value in table 't1'. When we multiply the two columns, it displays the output where 'D' value exceeds the range of '30' and displays 'M' as '-4' and 'D' as '468000000000000000000000000000000000000000000000000000' Testcase: 4.7.8. Ensure that the result of implicitly or explicitly multiplying a negative number with a positive number is always correct. How to repeat: 1. Set the delimiter ie execute the following SQL statement: delimiter// 2. Change the database ie execute the following SQL statement: use test1// 3. Create a table 't1' ie execute the following SQL statement: Create table t1 (f1 dec(64,30),f2 dec(64,30))// 4. Insert values in table 't1' ie execute the following SQL statement: Insert into t1 values (-2.234,2)// 5. Execute the 'select' statement: Select f1*f2 from t1// Observed : It gives the output as below. +-------------------------------------------------------------+ | f1*f2 | +-------------------------------------------------------------+ | -4.468000000000000000000000000000000000000000000000000000 | +-------------------------------------------------------------+ Expected Result: It should display the output in the decimal range (M,D) ie (64,30). Actual Result:It displays value with M =1 and D=54 i.e. M value is '-4' and D value is '468000000000000000000000000000000000000000000000000000 '