I figured it out. The between clause needs lower then upper, and I had upper then lower. The extraneous values I found must have been artifacts from the abs comparison, which was clearly not working anyway. I am just going to use the between clause. It seems to return the results I expect.
Rich -----Original Message----- From: Rich Schramm [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 9:07 AM To: 'Alexander Keremidarski'; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Arithmetic Bug in 4.0.12/13-max OK, I have been working on this for the last day. I read the manual sections that were recommended on floating point calculations and I am still seeing some weird things. I simply want to see if the average value of two columns named upper and lower (both are decimal(20,8) unsigned) is within the range of two decimal numbers I pass to the statement. The manual says in BIG CAPITAL LETTERS that you should NOT round and the only precise way of getting this to go is to do use the abs(a - b) < some tolerance, such as .00001. Having read that, I changed my code so that as follows: and ( ( abs(upper - 0.039) < 0.00001 ) or ( abs(lower - 0.037) < 0.00001 ) or ( ( (upper + lower) / 2 ) between 0.039 and 0.037 ) ) Which I assume would find anything where the upper column is .039, the lower column is .037 or the average between the columns is between .039 and .037. However, all I am returned is one record where the average ((upper + lower)/2) = 0.040 !!! I have plenty of records where the average is 0.038. Why is it not finding them?? Also, when my range is 0.038 - 0.038 so that the query looks like: and ( ( abs(upper - 0.038) < 0.00001 ) or ( abs(lower - 0.038) < 0.00001 ) or ( ( (upper + lower) / 2 ) between 0.038 and 0.038 ) ) It brings back ALL columns were the average is 0.038 AND it brings back records where the average is .040 and .036. In short, I am seeing exactly the OPPOSITE behavior I would expect from this. Any help would be much appreciated! Thanks, Rich -----Original Message----- From: Alexander Keremidarski [mailto:[EMAIL PROTECTED] Sent: Monday, June 16, 2003 5:51 PM To: Rich Schramm Cc: [EMAIL PROTECTED] Subject: Re: Arithmetic Bug in 4.0.12/13-max Hello, Rich Schramm wrote: > I have tried the following SQL script on 4.0.12-max-nt on Windows XP > and 4.0.13-max on Red Hat Linux 9. In both cases, the 4th and 5th > select statements (and only those statements) return false (0). They > return true in Oracle and Access: > > select (0.040000 + 0.040000)/2 = 0.040; > select (0.041000 + 0.039000)/2 = 0.040; > select (0.042000 + 0.038000)/2 = 0.040; > select (0.043000 + 0.037000)/2 = 0.040; > select (0.044000 + 0.036000)/2 = 0.040; > select (0.045000 + 0.035000)/2 = 0.040; > select (0.046000 + 0.034000)/2 = 0.040; > select (0.047000 + 0.033000)/2 = 0.040; > select (0.048000 + 0.032000)/2 = 0.040; > select (0.049000 + 0.031000)/2 = 0.040; > select (0.050000 + 0.030000)/2 = 0.040; > > The selects with 0.043 and 0.044 as the first number return false for > some unknown reason. I discovered this in code as I am trying to > select records whose average of two columns equals the number I pass > to it. MySQL uses floating point arithmetic in this case which as you know is not precise. To see what happens modify your query as: select (0.040000 + 0.040000)/2 = 0.040, round((0.040000 + 0.040000)/2, 20); select (0.041000 + 0.039000)/2 = 0.040, round((0.041000 + 0.039000)/2, 20); ... Possible way to do above comparison is to use ROUND() function on one or both columns. select (0.040000 + 0.040000)/2 = 0.040, round((0.040000 + 0.040000)/2, 3) = 0.040; You will get trues in right column for obvious reason. However most precise way is to do convert = to as a Range comparison 0.400+/- tolerance select (0.040000 + 0.040000)/2 BETWEEN 0.040 - 0.000001 AND 0.040 + 0.000001; This is well docummented in Manual: A.5.6 Solving Problems with No Matching Rows ... # If you are comparing FLOAT or DOUBLE columns with numbers that have decimals, you can't use '='. This problem is common in most computer languages because floating-point values are not exact values. In most cases, changing the FLOAT to a DOUBLE will fix this. See section A.5.7 Problems with Floating-Point Comparison. Read also whole A.5.7 Problems with Floating-Point Comparison > I have worked around this by modifying my select statement to multiply > each number by 1000, essential making each a whole number. When this > is done, it work as it should. Yes as then MySQL uses integer arithmetics which is precise. > Anyone have any ideas? > > Thanks, > > Richard Schramm > Director, Strategic Technology Services > Aerospace International Materials, Inc. Best regards -- Are you MySQL certified? -> http://www.mysql.com/certification For technical support contracts, visit https://order.mysql.com/?ref=msal __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria <___/ www.mysql.com -- MySQL Bugs Mailing List For list archives: http://lists.mysql.com/bugs To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
