I didn't see anyone else reply, so I will give you my thoughts. I thought one of the "list gurus" would give you a much better answer than I could so I waited.
Assuming that with two decimal places, you are doing something monetary. But this applies whenever you want fixed decimal places, too. I NEVER use float or double to store values like you appear to be doing. I always used DECIMAL so that they are stored as strings and you do not have problems associated with what you see here. I then have complete control over what is stored and the precision associated with it. I then pull the decimals out and either work with them CAREFULLY as double items (or you could use variant, I suppose) or extended math functions to add, etc. strings of infinite precision. Then, convert back to strings and store as DECIMAL items in MySQL. That my be overkill, but, I don't get bit doing it that way. I usually use DECIMAL(16,6) to store my monetary stuff. Also note that although MySQL will allow you to do something like <DECIMAL column name> = <DECIMAL column name> + 0.15 if you are beyond the precision of a double, it will truncate the result incorrectly and oddly. I played with it when I first started using MySQL and it appears that internally MySQL does it's math as doubles when working with DECIMAL items. If you stay within the range of a double, it appears that using DECIMAL data types works OK. Also, I think that you have a misunderstanding of how you are defining your float. Doesn't the first 8 mean bytes to store the float in NOT decimal places? IF you defined it as a DECIMAL type of item, then (8,2) would yield a maximum of 999999.99 like it appears that you want. I don't think that is true with float items. Check the web doc to make sure. Have Fun! Ken Hylton Programmer Analyst IV LEC Systems & Programming Billing Concepts, Inc. 7411 John Smith Drive San Antonio, Texas 78229-4898 (210) 949-7261 -----Original Message----- From: Lonny Byrd [mailto:lonny@;bakecrafters.com] Sent: Tuesday, October 22, 2002 1:18 PM To: [EMAIL PROTECTED] Subject: Mysql arbitrarily rounds large floats Description: Mysql arbitrarily rounds large floats How-To-Repeat: create table temp ( id int unsigned not null auto_increment primary key, amount float(8,2) ); insert into temp(amount) values(244409.29); insert into temp(amount) values(444409.29); insert into temp(amount) values(777409.29); insert into temp(amount) values(999409.29); select * from temp; +----+-----------+ | id | amount | +----+-----------+ | 1 | 244509.30 | | 2 | 444509.28 | | 3 | 777509.31 | | 4 | 999509.31 | +----+-----------+ 4 rows in set (0.00 sec) Fix: Not Known >Submitter-Id: <submitter ID> Originator: Lonny Byrd Organization: Bake Crafters <organization of PR author (multiple lines)> MySQL support: none Synopsis: Mysql arbitrarily rounds large floats Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.42 (Source distribution) AND mysql 3.23.53 Environment: machine, os, target, libraries (multiple lines)> System: FreeBSD secure.bakecrafters.com 4.4-RELEASE FreeBSD 4.4-RELEASE #8: Mon Apr 1 09:00:21 GMT 2002 [EMAIL PROTECTED]:/usr/src/sys/compile/SERVERBERT i386 Also tried on System: Linux squiggle.kianta.com 2.4.9-31 #1 Tue Feb 26 07:11:02 EST 2002 i686 unknown running mysql Ver 11.18 Distrib 3.23.53, for pc-linux-gnu (i686) and got identical results Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/ bin/cc GCC: Using builtin specs. gcc version 2.95.3 20010315 (release) [FreeBSD] Compilation info: CC='cc' CFLAGS='-O -pipe ' CXX='c++' CXXFLAGS='-O -pipe -f elide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1205382 Sep 18 2001 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Jan 23 2002 /usr/lib/libc.so -> libc.so.4 -r--r--r-- 1 root wheel 572876 Sep 18 2001 /usr/lib/libc.so.4 Configure command: ./configure --localstatedir=/var/db/mysql --without-perl --w ithout-debug --without-readline --without-bench --with-mit-threads=no --with -lib wrap --with-low-memory --enable-assembler --with-berkeley-db --prefix=/usr/l ocal i386--freebsd4.4 Perl: This is perl, version 5.005_03 built for i386-freebsd --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
