Re: Floor Decimal Math
On May 16, 2008, at 2:42 AM, Adam de Zoete wrote: Thanks for your responses, i thought it was a float problem so i was trying to CAST as a DECIMAL to fix it. It turns out (and the manual does not document this) that casting as decimals doesn't actually work in mysql 4.1.20. ROUND() is needed instead. DECIMAL is not listed for CAST() in the 4.1 manual because it is not supported in 4.1. DECIMAL is listed for CAST() in the 5.0 as supported from 5.0.8 on. mysql> select ROUND(11.11-FLOOR(11.11),2)>=0.11; +---+ | ROUND(11.11-FLOOR(11.11),2)>=0.11 | +---+ | 1 | +---+ Thanks for all your help, Adam Jerry Schwartz wrote: Don't feel bad, many an experience programmer has been bitten by this. The problem is that many decimal fractions do not have exact representations as binary fractions. .01 is an example of this. I'm not sure how MySQL does arithmetic internally, but (11.11 - 11) is just a hair under .11: mysql> SELECT (11.11 - 11) >= .10; +-+ | (11.11 - 11) >= .10 | +-+ | 1 | +-+ You need to allow for a slight fudge factor. This is even a problem at the hardware design level. The best way to handle this is to make sure all of your operators are type DECIMAL. You might have to cast them. Apparently MySQL 5+ introduced some algorithms that helped with these problems, although not with yours. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Floor Decimal Math
Thanks for your responses, i thought it was a float problem so i was trying to CAST as a DECIMAL to fix it. It turns out (and the manual does not document this) that casting as decimals doesn't actually work in mysql 4.1.20. ROUND() is needed instead. mysql> select ROUND(11.11-FLOOR(11.11),2)>=0.11; +---+ | ROUND(11.11-FLOOR(11.11),2)>=0.11 | +---+ | 1 | +---+ Thanks for all your help, Adam Jerry Schwartz wrote: Don't feel bad, many an experience programmer has been bitten by this. The problem is that many decimal fractions do not have exact representations as binary fractions. .01 is an example of this. I'm not sure how MySQL does arithmetic internally, but (11.11 - 11) is just a hair under .11: mysql> SELECT (11.11 - 11) >= .10; +-+ | (11.11 - 11) >= .10 | +-+ | 1 | +-+ You need to allow for a slight fudge factor. This is even a problem at the hardware design level. The best way to handle this is to make sure all of your operators are type DECIMAL. You might have to cast them. Apparently MySQL 5+ introduced some algorithms that helped with these problems, although not with yours. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Floor Decimal Math
> -Original Message- > From: Adam de Zoete [mailto:[EMAIL PROTECTED] On Behalf Of > Adam de Zoete > Sent: Thursday, May 15, 2008 6:24 AM > To: mysql@lists.mysql.com > Subject: Floor Decimal Math > > Hi, > > I'm running mysql 4.1.20 (Mac OS 10.4.11) and wondering why this ain't > returning the right answer? Maybe i'm missing something or am i doing > something wrong? > > mysql> select (11.11-FLOOR(11.11))>=0.11; > ++ > | (11.11-FLOOR(11.11))>=0.11 | > ++ > | 0 | > ++ mysql> set @x = 11.11 - FLOOR(11.11); Query OK, 0 rows affected (0.00 sec) mysql> select @x; +--+ | @x | +--+ | 0.11 | +--+ 1 row in set (0.00 sec) mysql> select @x - 0.11; +--+ | @x - 0.11| +--+ | -5.6898930012039e-16 | +--+ 1 row in set (0.00 sec) mysql> select version(); ++ | version() | ++ | 4.1.20-log | ++ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Floor Decimal Math
Don't feel bad, many an experience programmer has been bitten by this. The problem is that many decimal fractions do not have exact representations as binary fractions. .01 is an example of this. I'm not sure how MySQL does arithmetic internally, but (11.11 - 11) is just a hair under .11: mysql> SELECT (11.11 - 11) >= .10; +-+ | (11.11 - 11) >= .10 | +-+ | 1 | +-+ You need to allow for a slight fudge factor. This is even a problem at the hardware design level. The best way to handle this is to make sure all of your operators are type DECIMAL. You might have to cast them. Apparently MySQL 5+ introduced some algorithms that helped with these problems, although not with yours. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com >-Original Message- >From: Adam de Zoete [mailto:[EMAIL PROTECTED] On Behalf Of >Adam de Zoete >Sent: Thursday, May 15, 2008 7:24 AM >To: mysql@lists.mysql.com >Subject: Floor Decimal Math > >Hi, > >I'm running mysql 4.1.20 (Mac OS 10.4.11) and wondering why this ain't >returning the right answer? Maybe i'm missing something or am i doing >something wrong? > >mysql> select (11.11-FLOOR(11.11))>=0.11; >++ >| (11.11-FLOOR(11.11))>=0.11 | >++ >| 0 | >++ > >Any help would be appreciated, > >Many thanks, > >Adam > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Floor Decimal Math
On Thursday 15 May 2008 13:24:10 Adam de Zoete wrote: > Hi, > > I'm running mysql 4.1.20 (Mac OS 10.4.11) and wondering why this ain't > returning the right answer? Maybe i'm missing something or am i doing > something wrong? Perhaps it is performing FLOAT and not DECIMAL math. 11.11 is represented in binary differently that 0.11 (bit-rounding errors). W > > mysql> select (11.11-FLOOR(11.11))>=0.11; > ++ > > | (11.11-FLOOR(11.11))>=0.11 | > > ++ > > | 0 | > > ++ > > Any help would be appreciated, > > Many thanks, > > Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Floor Decimal Math
Hi, I'm running mysql 4.1.20 (Mac OS 10.4.11) and wondering why this ain't returning the right answer? Maybe i'm missing something or am i doing something wrong? mysql> select (11.11-FLOOR(11.11))>=0.11; ++ | (11.11-FLOOR(11.11))>=0.11 | ++ | 0 | ++ Any help would be appreciated, Many thanks, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
