Dear MySQL Support List: Way back in early 2003 two questions and answers appeared on this list and bug list RE: DECIMAL precision. See both at http://lists.mysql.com/mysql/141268 and http://bugs.mysql.com/bug.php?id=559
1) Did anyone else notice that the 'INSERT INTO' value DID NOT MATCH the 'SELECT * FROM' value in both examples? Shouldn't it truncate/round to the place it's exact (or one digit below) and not offer back bogus digits starting after the 9th decimal place? INSERT: 12345678.123456789012345678901234 SELECT: 12345678.12345678918063640594482 IMO answer should be 12345678.123456789 which is "more technically correct" than MySQL's answer. 2) Is MySQL doing true "unpacked" BCD math on the DECIMAL datatype? 3) Are there plans to pack the data in the DECIMAL datatype to save space someday? 4) This has been fixed in MySQL server 4.1, but why wasn't this caveat explicitly WELL DOCUMENTED in the documentation so developers could avoid this land mine along with the additional gotcha under Windows? Could someone please revise the documentation to make these issues clear? 5) I'm sucking data from Oracle into MySQL using Perl and would like to propose a behavior change for DECIMAL datatypes without width and precision to mimic the Oracle behavior to just store the answer/number without rounding. This may require yet another flavor of DECIMAL datatype (perhaps call it NUMBER ?) where the current DECIMAL/NUMERIC retain the SQL-92 behavior where DECIMAL by itself default to DECIMAL(10,0), but NUMBER would store the "raw" value as best it could. Oracle NUMBER type without parens width/precision values defaults to 22 bytes as a packed decimal. Please see http://www.ixora.com.au/notes/number_representation.htm?number=3.14159265358987354 for more info. 6) It would seem that overflow/underflow numeric data is stored as all 9's in MySQL databases. This is difficult since 999.9999999999, for example, still looks like a valid number. I don't get a warning/error result on INSERTs/UPDATEs of out-of-range data. How do I detect out-of-range values on INSERTs/UPDATEs? 7) The article http://www.ixora.com.au/notes/number_representation.htm?number=3.14159265358987354 shows how Oracle represents +/- infinity values as "special values" of -~ or ~ (+/- infinity). Could MySQL provide some representation in the database for storage of this data so I can retain it from Oracle data? I've been using MySQL since version 3.2 (now in 4.0) in Windows 2000. I now realize the OS and math lib issues, but issues above should be consistent in their handling/treatment of precision. Could DECIMAL types in MySQL use a true BCD math library functions and not have (as many) rounding issues and be more precise? Perhaps these issues have been vetted somewhere else. Please redirect me if so. I love MySQL and have been using at work since October 2001 and will continue to use it. Thankfully I didn't have any data until now that REQUIRED higher precision data storage and was not adversely impacted by the precision issue, but I'd like someone at MySQL to better address these issues for all, since working with Oracle and Oracle data is still a fact of life for some of us. Thanks, Larry Hixson
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
